Interview Q and A for Oracle Backup and Recovery Part - 1

1. How would you decide your backup strategy and timing for backup?

In fact, backup strategy is purely depending upon your organization business need.
If no downtime then database must be run on archivelog mode and you should take frequently or daily backup.
If sufficient downtime is there and loss of data would not affect your business then you can run your database in noarchivelog mode and backup can be taken in-frequently or weekly or monthly.
In most of the case in an organization when no downtime then frequent inconsistent backup needed (daily backup), multiplex online redo log files (multiple copies), different location for redo log files, database must run in archivelog mode and dataguard can be implemented for extra bit of protection.

2. What is difference between Restoring and Recovery of database?
Restoring means copying the database object from the backup media to the destination where it is required where as recovery means to apply the database object copied earlier (roll forward) to bring the database into consistent state.

3. What is the difference between complete and incomplete recovery?

An incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery can be either point of time or particular SCN or Particular archive log specially incase of missing archive log or redolog failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup.

4. What is the benefit of running the DB in archivelog mode over no archivelog mode?
When a database is in no archivelog mode whenever log switch happens there will be a loss of some redoes log information in order to avoid this, redo logs must be archived. This can be achieved by configuring the database in archivelog mode.

5. If an oracle database is crashed? How would you recover that transaction which is not in backup?

If the database is in archivelog we can recover that transaction otherwise we cannot recover that transaction which is not in backup.

6. What is the difference between HOTBACKUP and RMAN backup?
For hotbackup we have to put database in begin backup mode, then take backup where as RMAN would not put database in begin backup mode. RMAN is faster can perform incremental (changes only) backup, and does not place tablespace in hotbackup mode.

7. Can we use Same target database as Catalog database?
No, the recovery catalog should not reside in the target database (database to be backed up) because the database can not be recovered in the mounted state.

8. Incremental backup levels:

Level 0 – full backup that can be used for subsequent incrementals

RMAN> backup incremental level 0 database;

Differential Level 1–only the blocks that have changed since the last backup (whether it is level 0 or level 1) 

RMAN> backup incremental level 1 differential database;

Cumulative Level 1 – all changes since the last level 0 incremental backup

RMAN> backup incremental level 1 cumulative database;

A full backup cannot be used for a cumulative level 1 backup. 

A cumulative level 1 backup must be done on top of an incremental level 0 backup.

9. Why RMAN incremental backup fails even though full backup exists?

If you have taken the RMAN full backup using the command ‘Backup database’, where as a level 0 backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository so it can be used as the parent for a level 1 backup. Simply the ‘full backup without level 0’ cannot be considered as a parent backup from which you can take level 1 backup.

10. Can we perform RMAN level 1 backup without level 0?

If no level 0 is available, then the behavior depends upon the compatibility mode setting (oracle version). If the compatibility mode less than 10.0.0, RMAN generates a level 0 backup of files contents at the time of backup.  If the compatibility is greater than 10.0.0, RMAN copies all block changes since the file was created, and stores the results as level 1 backup.
 
11. How to Catalog backup? OR

     How to put manual/user-managed backup in RMAN (recovery catalog)?

In case of recovery catalog, you can put by using catalog command:
RMAN> CATALOG START WITH ‘/oracle/backup.ctl’;

12. How to check RMAN version in oracle?

If you want to check RMAN catalog version then use the below query from SQL*plus
SQL> Select * from rcver;

13. What happens in case of instance Recovery?

While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started. Instance recovery occurs in 2 steps:
Cache recovery: Changes being made to a database are recorded in the database buffer cache as well as redo log files simultaneously. When there are enough data in the database buffer cache, they are written to data files. If an Oracle instance fails before these data are written to data files, Oracle uses online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery.
Transaction recovery: When a transaction modifies data in a database (the before image of the modified data is stored in an undo segment which is used to restore the original values in case the transaction is rolled back). At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.

14. How do we Set A Retention Policy For Tape Backups And Disk Backups Differently?

For example, setting the tape retention policy to 14 days while setting it to 3 days for the disk backups

Step 1: Set the retention policy.

RMAN currently only allows one retention policy. The retention policy should be set to 14 days.

RMAN> configure retention policy to recovery window of 14 days;

Step 2: Move the backups from disk to tape to meet the specified disk retention policy. This must be done manually.

The following command will move the backups from disk to tape and delete the original backup copy from disk.

RMAN> backup device type sbtbackupset completed before 'sysdate-2' delete input;

The time specified may be changed depending which backupsets will be moved to tape and deleted from disk. The retention policy guarantees that the backups will not be marked as obsolete unless they meet the specified retention policy.

15. How does the RMAN Retention Policy Obsolete Incremental Backupsets ?

The report/delete obsolete commands works in two steps.

First, we identify the oldest full backup (i.e. full or level 0 backupset, or image copy) of every file that is not obsolete,as defined by the retention policy (either REDUNDANCY or RECOVERY WINDOW). Every older full backup is obsolete.

Then, all archived logs and level > 0 incremental backups that are older than the oldest non-obsolete full backup are also considered to be obsolete, because there is no longer any full backup to which they could possibly apply.

Note that a level > 0 backup performs much the same function as an archived log - it causes a full backup to move forward in time, so those backups are treated in the same manner as archived logs for the purposes of retention.

Incremental backups are not restored using the restore command, they are restored using the recover command if it will cause fewer archive logs to be needed during recovery. When you restore it is always only from full or level 0 incremental backups of the datafiles.

16. How can we have different retention policies for different backups?

We cannot have different retention policies for different backups. The retention policy is always same for all backups. But there could be situations where you would be looking for different kind of retention policies. Such kind of a situation is explained below.

QUESTION -- There are 3 types of backup, daily, weekly and monthly, so we would like to have 3 retention policies 1 for retention of 32 days, 2 for retention of 365 days and other for 700 days for example

ANSWER --- For all your daily backups maintain a normal retention policy i.e

Rman> configure retention policy for recovery window of 32 days;

This retention policy is always common for all backups. But since you have to retain the weekly backups for 365 days and monthly backups for 700 days .

Therefore, for the weekly and monthly backups we will be going for " KEEP " option.

You can exempt a backup from the retention policy by using the KEEP option with the BACKUP command. when you create the backup, or the KEEP option of the CHANGE command to exempt an existing backup.

Note that backups exempted from the retention policy are still fully valid backups, which can be

used in restore and recovery operations like any other if RMAN judges them to be the best choice

available.

You can change the exempt status of a backup using the CHANGE... KEEP and CHANGE... NOKEEP

commands. The NOKEEP option (default) indicates that the backup is not immune from the configured

retention policy.

You can specify the LOGS option to save archived logs for a possible incomplete recovery of the

long-term backup. When LOGS are specified, all logs more recent than the backup are kept as long as

the backup is kept. In other words, KEEP UNTIL TIME... LOGS mean that RMAN will keep all logs

required to recover the backup as long as the backup is kept. If you specify NOLOGS, then RMAN

does not keep the logs required to recover the backup. Note that if you use KEEP UNTIL TIME...

with an inconsistent backup, you must use the LOGS option, or that backup will become unusable

when the logs required to recover, it is deleted as obsolete. In fact rman does not allow us to take a inconsistent backup with KEEP NOLOGS option.

You can specify an end date using the UNTIL clause, or either specify that the backup should be

kept FOREVER. If you specify UNTIL, then RMAN will not mark the backup as obsolete until after the

UNTIL date has passed. Note that it is an error to specify KEEP FOREVER with the LOGS option, as

this would require keeping all redo logs forever.

EXAMPLE:

=========

+ For weekly and monthly backups you can take the backups in this way :

Rman> BACKUP DATABASE KEEP UNTIL TIME "TO_DATE('31-DEC-2007' 'dd-mon-yyyy')" NOLOGS;

OR

Rman> BACKUP DATABASE KEEP UNTIL TIME 'sysdate +365' NOLOGS;

Specify the until time as needed depending upon whether the backup is weekly or monthly.

For the rest of your daily backups you can have the normal retention policy of 32 days.

17.  Why do the backups do not become obsolete even if they are out of retention policy?

We can have different conditions due which the backups would not become obsolete. They are:

1) There is an offline datafile in the database because of which the all the archivelogs or the archivelog backups needed to recover the datafile will not become obsolete.

2) The archivelog / 'archivelog backups' / 'incremental backups' do not become obsolete, if there are no full backups of the datafiles.

3) The backups were done with KEEP option.

4) If the above conditions do not satisfy , then there could be a bug due to which the backups are not becoming obsolete.

18. What is the recommended retention policy for incremental merge backups?

To implement the retention policy for the incremental merge backups, please use the following guidelines.

+ Retain the default retention policy to redundancy 1. This enables all the incremental backups to become obsolete as soon as they are applied to the copy.

+ To implement the retention policy based on recovery window, do not use the CONFIGURE command. Instead use the UNTIL CLAUSE in the RECOVER COMMAND.

Example --     run {

allocate channel oem_disk_backup device type disk;

RECOVER COPY OF DATABASE with tag 'ORA$OEM_LEVEL_0' until time 'sysdate-8';

backup incremental level 1 cumulative copies=1 for recover of copy with tag 'ORA$OEM_LEVEL_0' database;

}

NOTE --- For incremental merge backups, please ensure that the retention policy is retained to its default value of redundancy

1.However if you want to configure retention policy based on recovery window of n days , then ensure that you include the UNTIL TIME clause in the RECOVER command to 'sysdate-(n+1)'.

19. Neither list backup or report/delete obsolete display the obsolete backup pieces. But the backup

pieces are very much available on the disk.

It is possible that the information about these backups are removed/overwritten in the controlfile.

To prevent this, ensure the control_file_record_keep_time initialization parameter value is set to a value higher than the retention policy of recovery window OR consider using a recovery catalog.

20. What is RMAN and How to configure it?

RMAN is an Oracle Database client that performs backup and recovery tasks on your databases and automates administration of your backup strategies. It greatly simplifies the dba jobs by managing the production database's backing up, restoring, and recovering database files.

This tool integrates with sessions running on an Oracle database to perform a range of backup and recovery activities, including maintaining an RMAN repository of historical data about backups. There is no additional installation required for this tool. It’s by default get installed with the oracle database installation. The RMAN environment consists of the utilities and databases that play a role in backing up your data. You can access RMAN through the command line or through Oracle Enterprise Manager.

21. Why to use RMAN?

RMAN gives you access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:

-- Automatic specification of files to include in a backup: Establishes the name and locations of all files to be backed up.

-- Maintain backup repository: Backups are recorded in the control file, which is the main repository of RMAN metadata. Additionally, you can store this metadata in a recovery catalog,

-- Incremental backups: An incremental backup stores only blocks changed since a previous backup. Thus, they provide more compact backups and faster recovery, thereby reducing the need to apply redo during datafile media recovery.

-- Unused block compression: In unused block compression, RMAN can skip data blocks that have never been used

-- Block media recovery: You can repair a datafile with only a small number of corrupt data blocks without taking it offline or restoring it from backup.

-- Binary compression: A binary compression mechanism integrated into Oracle Database reduces the size of backups.

-- Encrypted backups: RMAN uses backup encryption capabilities integrated into Oracle Database to store backup sets in an encrypted format.

-- Corrupt block detection: RMAN checks for the block corruption before taking its backup.

22. How RMAN works?

RMAN backup and recovery operation for a target database are managed by RMAN client. RMAN uses the target database control file to gather metadata about the target database and to store information about its own operations.

The RMAN client itself does not perform backup, restore, or recovery operations. When you connect the RMAN client to a target database, RMAN allocates server sessions on the target instance and directs them to perform the operations. The work of backup and recovery is performed by server sessions running on the target database. A channel establishes a connection from the RMAN client to a target or auxiliary database instance by starting a server session on the instance. The channel reads data into memory, processes it, and writes it to the output device.

When you take a database backup using RMAN, you need to connect to the target database using RMAN Client.TheRMAN client can use Oracle Net to connect to a target database, so it can be located on any host that is connected to the target host through Oracle Net. For backup you need to allocate explicit or implicit channel to the target database.

An RMAN channel represents one stream of data to a device, and corresponds to one database server session. This session dynamically collect information of the files from the target database control file before taking the backup or while restoring.

For example, If you give ' Backup database ' from RMAN, it will first get all the datafiles information from the controlfile. Then it will divide all the datafiles among the allocated channels. (roughly equal size of work as per the datafile size). Then it takes the backup in 2 steps. In the first step the channel will read all the Blocks of the entire datafile to find out all the formatted blocks to backup. Note: RMAN do not take backup of the un formatted blocks. In the second step it take backup of the formatted blocks. This is the best advantage of using RMAN as it only takes backup of the required blocks. Let’s say in a datafile of 100 MB size, there may be only 10 MB of use full data and rest 90 MB is free then RMAN will only take backup of those 10 MB.

23. What O/S and oracle user privilege required to use RMAN?

RMAN always connect to the target or auxiliary database using the SYSDBA privilege. In fact, the SYSDBA keywords are implied and cannot be explicitly specified. Its connections to a database are specified and authenticated in the same way as SQL*Plus connections to a database.

The O/S user should be part of the DBA group. For remote connection, it needs the password file Authentication. Target database should have the initialization parameter REMOTE_LOGIN_PASSWORDFILE set to EXCLUSIVE or SHARED.

24. RMAN terminology:

target database: An Oracle database to which RMAN is connected with the TARGET keyword. A target database is a database on which RMAN is performing backup and recovery operations. RMAN always maintains metadata about its operations on a database in the control file of the database.

A recovery Catalog: A separate database schema used to record RMAN activity against one or more target databases. A recovery catalog preserves RMAN repository metadata if the control file is lost, making it much easier to restore and recover following the loss of the control file. The database may overwrite older records in the control file, but RMAN maintains records forever in the catalog unless deleted by the user.

Backup sets: RMAN can store backup data in a logical structure called a backup set, which is the smallest unit of an RMAN backup. One backup set contains one or more datafiles a section of datafile or archivelogs.

Backup Piece: A backup set contains one or more binary files in an RMAN-specific format. This file is known as a backup piece. Each backup piece is a single output file. The size of a backup piece can be restricted; if the size is not restricted, the backup set will comprise one backup piece. Backup piece size should be restricted to no larger than the maximum file size that your filesystem will support.

Image copies : An image copy is a copy of a single file (datafile, archivelog, or controlfile). It is very similar to an O/Scopy of the file. It is not a backupset or a backup piece. No compression is performed.

Snapshot Controlfile : When RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file. The default name for the snapshot control file is port-specific.

Database Incarnation : Whenever you perform incomplete recovery or perform recovery using a backup control file,you must reset the online redo logs when you open the database. The new version of the reset database is called a new incarnation. The reset database command directs RMAN to create a new database incarnation record in the recovery catalog. This new incarnation record indicates the current incarnation.

25. What is RMAN Configuration and how to Configure it ?

The RMAN backup and recovery environment is preconfigured for each target database. The configuration is persistent and applies to all subsequent operations on this target database, even if you exit and restart RMAN. RMAN configured settings can specify backup devices, configure a connection to a backup device , policies affecting backup strategy, encryption algorithm, snap shot controlfile location and others.

By default there are few default configuration are set when you login to RMAN. You can customize them as per your requirement. Any time you can check the current setting by using the "Show all " command. CONFIGURE command is used to create persistent settings in the RMAN environment, which apply to all subsequent operations, even if you exit and restart RMAN.