RMAN INTERVIEW QUESTIONS AND ANSWERS

 RMAN

 

Benefits of RMAN in oracle database high level steps only

 Here are the high-level benefits of RMAN (Recovery Manager) in an Oracle database environment:

 Automated Backup and Recovery: RMAN simplifies backup and recovery operations with automatic management of files, reducing the need for manual scripts.

 Incremental Backups: RMAN supports block-level incremental backups, backing up only changed data blocks, which reduces backup time and storage requirements.

Reduced Redo Log Generation: Unlike traditional hot backups, RMAN does not require putting the database in backup mode, thus minimizing redo log generation.

Backup Integrity Validation: RMAN verifies the integrity of data during backup, ensuring that only valid and non-corrupted blocks are included.

Fast Recovery: RMAN optimizes recovery processes by automating the restoration and applying necessary archived redo logs or incremental backups.

Automated Management of Archived Logs: RMAN backs up and manages archived redo logs, ensuring that all required logs are available for recovery.

Compression and Deduplication: RMAN supports backup compression and deduplication, helping to save storage space.

Database Duplication and Cloning: RMAN facilitates easy database cloning and duplication for testing or standby database creation.

Centralized Backup Catalog: RMAN can store metadata in a centralized recovery catalog, enabling better management and reporting of backup and recovery operations.

Integration with Data Guard: RMAN works seamlessly with Oracle Data Guard for disaster recovery and standby database synchronization.

 

Can we recover a schema using rman backups?

Using RMAN we cannot restore a schema from the existing backups. But we can duplicate database in another server and take the export of that schema and we can import it in the database. Here we can exclude other tablespaces which are belongs to dropped user. To completed restore fast.

 

FOR REF: Using RMAN backups we can only recover the.

Ø  A block-Only to the current point in time. Block level recovery is used for media failures that corrupted a single or small set of blocks.

Ø  A file - to the current point in time.

Ø  A tablespace - to any point in time - current or past.

Ø  A database - to any point in time - current or past.

 

What are the different types of backups supported by RMAN?

Full Database Backup: Backs up the entire database at a specific point in time.

Incremental Backup: Backs up changes made to data files since the last full or incremental backup.

Cumulative Incremental Backup: Backs up all changes made since the last full backup in a single backup set.

Image Copy Backup: Creates a copy of the entire database or specific datafiles.

Level 0 Incremental Backup: Similar to a full backup but with a different backup level.

 

Cumulative Backup?

A cumulative backup is a type of incremental backup that backs up all changes made since the last full backup.

It captures all data modifications from the previous full backup to the current backup.  

Each cumulative backup contains all changes since the last full backup, making it larger than a differential backup (which only backs up changes since the last incremental backup).

 

Incremental Backup in Oracle?

An incremental backup in Oracle copies only the data blocks that have changed since the previous backup. This makes it more efficient than a full backup, which copies all data blocks regardless of changes.  

 

RMAN tracks which blocks have been modified since the last backup.  

During an incremental backup, only the modified blocks are copied to the backup media.  

Types of Incremental Backups:

Level 0 Incremental Backup: Essentially a full backup, but labeled as level 0 for consistency in backup strategies.  

Level 1 Incremental Backup: Backs up all blocks changed since the last level 0 or level 1 backup.  

Level N Incremental Backup: Backs up all blocks changed since the last level (N-1) backup.

 

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

 

Image Copy Backup in Oracle?

An image copy backup is an exact copy of a datafile or control file. Unlike traditional backups that are stored in RMAN backup pieces, image copy backups are stored as actual datafiles.

 

Ø  RMAN creates a complete copy of the specified datafile or control file.

Ø  The copy is stored in a designated location.

Ø  RMAN records metadata about the image copy in the control file.

 

Ø  Connect to the RMAN instance: rman target /

                RMAN> backup database image copy to 'backup_set_name.img';

 

 

Backup of an Entire Database as Image Copy:

RMAN> BACKUP AS COPY DATABASE;

 

Backup of Specific Datafiles as Image Copy:

RMAN> BACKUP AS COPY DATAFILE <file_number>

 

FORMAT clause: You can use the FORMAT clause to specify a custom location for the image copy.

 

Backup of Specific Datafiles as Image Copy:

RMAN> BACKUP AS COPY DATAFILE <file_number> FORMAT '/path/to/image_copy/datafile_<file_number>.dbf';

 

What is recovery catalog in RMAN?

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 the records are deleted by the user.

 

FORMAT:

The most common substitution variable is %U, which generates a unique name. Others include %d for the DB_NAME, %t for the backup set time stamp, %s for the backup set number, and %p for the backup piece number.

 

TAG:

Specifies a user-defined string as a label for the backup. If you do not specify a tag , then RMAN assigns a default tag with the date and time. Tags are always stored in the RMAN repository in uppercase.

 

WHAT IS INCREMENTAL BACKUP IN RMAN?

An incremental backup is a type of backup that only copies files that have changed since the last backup but an incremental backup at level 0 is identical in content to a full backup. A level 1 incremental backup contains only blocks changed after a previous incremental backup OR Level 0 backup.

If we specify BACKUP INCREMENTAL, then RMAN creates an incremental backup of a database. Incremental backups capture block-level changes to a database made after a previous incremental backup. Incremental backups are generally smaller and faster to make than full database backups. Recovery with incremental backups is faster than using redo logs alone.

NOTE: If no level 0 backup exists in either the current or parent database incarnation when you run a level 1 backup, then RMAN makes a level 0 backup automatically.

NOTE: You cannot make incremental backups when a NOARCHIVELOG database is open, although you can make incremental backups when the database is mounted after a consistent shutdown.

 

RMAN> backup incremental level 0 database; - Consider as a full database backup.

RMAN> backup incremental level 1 database; - Here it will back up only the modified blocks after level 0 backup.

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; - A level 1 backup can be a cumulative incremental backup, which includes all blocks changed since the most recent level 0 backup.

 

NOTE: When restoring incremental backups, RMAN uses the level 0 backup as the starting point, then updates changed blocks based on level 1 backups where possible to avoid reapplying changes from redo one at a time. Recovering with incremental backups requires no additional effort on your part. If incremental backups are available, then RMAN uses them during recovery.

 

Expired vs. Obsolete Backups in RMAN?

 

Expired Backup

An expired backup is a backup that RMAN cannot locate on the specified media device.

This typically occurs when the backup file has been deleted or moved without RMAN's knowledge.  

 

Cause: Backup file missing from the backup destination.  

Identification: RMAN's CROSSCHECK command identifies expired backups.

 

Obsolete Backup

An obsolete backup is a backup that is no longer needed based on the configured retention policy. This policy defines how long backups are kept and how many copies are retained.  

 

Cause: Backup exceeds retention policy.

Identification: RMAN's REPORT OBSOLETE command identifies obsolete backups.

 

Possible to restore the database using Obsolete backups?

If an obsolete backup has not been deleted yet and still physically exists on the disk or tape, you might be able to manually restore it, but it is not guaranteed. RMAN won’t automatically use obsolete backups in its normal recovery process. You would need to perform manual steps and ensure that all required components (data files, control files, archived logs) are available.

Steps to Restore an Obsolete Backup (Manually):

Locate the Backup:

Check if the obsolete backup is still available using the LIST BACKUP command.

RMAN> LIST BACKUP;

Crosscheck Backups:

Use CROSSCHECK to verify if the backups are still present on disk.

RMAN> CROSSCHECK BACKUP;

Catalog the Backup (if necessary):

If the backup is still physically available but not cataloged in RMAN, you can catalog it using the CATALOG BACKUP command.

RMAN> CATALOG START WITH '/path/to/backup';

Restore the Backup:

Once the backup is cataloged, you can attempt to restore it.

RMAN> RESTORE DATABASE;

Recover the Database:

After restoring, you will need to apply archived logs or use incremental backups to bring the database up to date.

RMAN> RECOVER DATABASE;

 

Is it possible to take RMAN Backup When Database is Shutdown?

 

No, it is not possible to take an RMAN (Recovery Manager) backup of an Oracle database when it is completely shut down. RMAN backups require the database to be in an online or mount state to perform the backup operations.

 

ONLINE: The database is open and available for use. RMAN can take a hot backup (also known as an online backup) of the database while it is online.

 

MOUNT: The database is not open for user access, but the control file and data files are accessible. RMAN can take a backup of the database in this state, which is often referred to as an offline backup or a cold backup.

 

SHUTDOWN: The database is not available for backup or any other operations. Since the database is completely off, RMAN cannot interact with it to perform a backup.

 

How to list the RMAN backups?

Run the LIST BACKUP and LIST COPY commands to display information about backups and data file copies listed in the RMAN repository.

 

RMAN> LIST BACKUP OF DATABASE BY BACKUP; - Organizes the output by backup set. This is the default mode of presentation.

 

RMAN> LIST BACKUP BY FILE; - List the backup according to which file was backed up.

RMAN> LIST BACKUP SUMMARY; - Displays summary output.

 

RMAN> REPORT SCHEMA; - Reports the tablespaces and data files in the database at the current time (default) or a different time.

 

RMAN> REPORT SCHEMA;

Report of database schema

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    350      SYSTEM               ***     D:\XE\ORADATA\XE\SYSTEM.DBF

2    90       UNDO                 ***     D:\XE\ORADATA\XE\UNDO.DBF

3    500      SYSAUX               ***     D:\XE\ORADATA\XE\SYSAUX.DBF

4    100      USERS                ***     D:\XE\ORADATA\XE\USERS.DBF

5    5        RISHI                ***     D:\DATA_XE\RISHI_1.DBF

6    5        RISHI                ***     D:\DATA_XE\RISHI.DBF

7    10       RMAN                 ***     D:\DATA_XE\DF\RMAN_01.DBF

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    20       TEMP                 32767       D:\XE\ORADATA\XE\TEMP.DBF

 

How to check obsolete backups for RMAN?

Lists backups that are obsolete under the configured backup retention policy. Use the optional REDUNDANCY and RECOVERY WINDOW parameters to override the default.

 

RMAN> REPORT OBSOLETE;

 

RMAN> REPORT OBSOLETE;

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 0 days

Report of obsolete backups and copies

Type                 Key    Completion Time    Filename/Handle

-------------------- ------ ------------------ --------------------

Backup Set           60     03-JUN-15

  Backup Piece       58     03-JUN-15          D:\DATA_XE\RMANC-2761439438-20150603-00

Backup Set           61     08-SEP-15

  Backup Piece       59     08-SEP-15          D:\DATA_XE\RMANC-2761439438-20150908-00

Backup Set           62     08-SEP-15

  Backup Piece       60     08-SEP-15          D:\DATA_XE\RMANC-2761439438-20150908-01

Backup Set           63     08-SEP-15

  Backup Piece       61     08-SEP-15          D:\DATA_XE\RMANC-2761439438-20150908-02  

 

How to Cross-checking Backups in RMAN?

The CROSSCHECK command determines whether the header of the file is valid. If the backup is on tape, then the command simply checks that the backup exists. The possible status values for backups are AVAILABLE, UNAVAILABLE, and EXPIRED.

 

RMAN> crosscheck backup;

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid=115 devtype=DISK

 

RMAN> crosscheck copy;

Released channel: ORA_DISK_1

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid=115 devtype=DISK

Specification does not match any archive log in the recovery catalog

 

What is RMAN Retention Policy?

RMAN retention policy automates the management of backup retention. It defines how long backups are kept and how many copies of each backup are retained.

 

Redundancy: Specifies the number of copies of a backup to retain.  

RMAN> configure retention policy to redundancy 2;

 

Recovery Window: Specifies the time period for which backups should be retained.

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

 

RMAN> SHOW OBSOLETE; - To view the obsolete backups. 

 

WHAT IS RECOVERY WINDOW IN RMAN??

The recovery window retention policy is used to ensure that a backup is available that will facilitate a recovery within the defined time window. For example, if the recovery window is set to seven days, then no backup will be reported as obsolete unless it is older than 7 days, the defined recovery window retention time.

 

To set a recovery window retention policy, use the CONFIGURE RETENTION POLICY command, as shown in the following example:

 

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; - Default value

 

NOTE: In this case, we have configured a retention policy of seven days. When the DELETE OBSOLETE command is executed, only backups older than seven days will be reported as obsolete.

 

RECOVERY WINDOW: The number of days for which you want to keep the backup. We can have N number of copies within the specified period of the recovery window. Any backup that is older than the recovery window is marked as obsolete.

 

REDUNDANCY RETENTION POLICY: Redundancy stands for the number of copies that must be available. Once you have the chosen number of backup copies, any older backups are marked as obsolete.  This does not put any sort of restriction on how long the backups can be kept. 

 

RMAN TSPITR?

What 11gR2 does is that it performs a fully automated RMAN managed TSPITR. It creates and starts the auxiliary instance and restores just the datafiles it requires – SYSTEM, SYSAUX, UNDO and the files pertaining to the dropped tablespace – in this case datafiles 1,2,3 and 7- in the location which we specify as the ‘Auxiliary Destination’. It will first perform a recovery of the tablespace on the auxiliary instance and then use Data Pump and Transportable Tablespace technology to extract and import the tablespace meta data into the original source database.. Once recovery completed it will drop the auxiliary instance.

 

NOTE: After the recovery is complete, we need to bring the tablespace and datafile online as well.

 

RMAN TSPITR WITHOUT RMAN?

We can restore the dropped tablespace without using RMAN. To achieve this process we should a latest cold (OR) hot backup database files including control file.

 

First we need to find when we have latest control file backup and database files backup.

 

Steps to be followed.

 

Ø  Shut down the database

Ø  Startup nomount

Ø  Restore control from the backup – Replace the current control files with the backup control files. Copy and replace

Ø  Mount the database.

Ø  Now replace all the database files with the backed up database files.

Ø  Once done issue the below command

Ø  SQL>recover database using backup controlfile until time ’01-jul-2015 18:39:12’;

Ø  Alter database open reset logs;

Ø  Completed.

 

RMAN Block corruption?

Using dbv utility we can find out corrupted blocks in the datafile.

DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/orcl/test_corrupt01.dbf

Page 142 is marked corrupt

Corrupt block relative dba: 0x0100008e (file 4, block 142)

For example, you may discover the following messages in a user trace file:

 

ORA-01578: ORACLE data block corrupted (file # 7, block # 3)

ORA-01110: data file 7: '/oracle/oradata/trgt/tools01.dbf'

SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;                         

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 12;

RMAN> recover datafile 4 block 142;

RMAN> BLOCKRECOVER CORRUPTION LIST FROM TAG "HEALTHY";

NOTE: CORRUPTION LIST means all the blocks reported corrupt in v$backup_corruption.

WE CAN CHECK THE ALERT LOG OR TRACE FILE FOR THE CORRUTED BLOCKS.

Recovering a specific block using RMAN is possible, but it's a complex and risky operation. It's generally recommended to recover the entire datafile or tablespace if possible.

Use tools like dbverify or query V$DATABASE_BLOCK_CORRUPTION to pinpoint the exact datafile and block number.

SQL> RECOVER DATAFILE <file_number> BLOCK <block_number>;

 

Example:

SQL> RECOVER DATAFILE 4 BLOCK 142;

 

Recover Multiple Blocks:

SQL> RECOVER DATAFILE 4 BLOCK 142 DATAFILE 5 BLOCK 234;

 

This will Recover All Corruted Blocks:

SQL> RECOVER CORRUPTION LIST;

 

Recovering a lost datafile?

 

Steps for Database in OPEN mode

1] TAKE DATAFILE OFFLINE WITH IMMEDIATE OPTION;

SQL>ALTER DATABASE DATAFILE '<file_name>' OFFLINE;

 

2] SQL> ALTER DATABASE CREATE DATAFILE ‘<DATAFILE_NAME>’;

 

3] RECOVER THE DATAFILE

 

4] SQL>Recover datafile 'D:\DATA_XE\DF\lost_1.dbf';

 

5] ONLINE THE DATAFILE

 

SQL>ALTER DATABASE DATAFILE '<file_name>' ONLINE;

 

Steps for Database in SHUTDOWN

 

1] MOUNT THE DATABASE

 

2] OFFLINE THE DATAFILE [SQL>ALTER DATABASE DATAFILE 5 OFFLINE;]

 

3] OPEN THE DATABASE

4] SQL> ALTER DATABASE CREATE DATAFILE ‘<DATAFILE_NAME>’;

5] SQL>Recover datafile 'D:\DATA_XE\DF\lost_1.dbf';

 

6] ONLINE THE DATAFILE

SQL>ALTER DATABASE DATAFILE '<file_name>' ONLINE;

 

Example

Assuming the datafile is accidentally deleted and renamed to lost_datafile.dbf:

SQL > ALTER TABLESPACE USERS OFFLINE;

ALTER DATABASE RENAME FILE '/path/to/lost_datafile.dbf' TO '/path/to/original_datafile.dbf';

STARTUP MOUNT;

RECOVER DATAFILE <file_number>;

ALTER TABLESPACE USERS ONLINE;

1. What is RMAN?

Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.

2. Which Files must be backed up?

Ø  Database Files (with RMAN)

Ø  Control Files (with RMAN)

Ø  Offline Redolog Files (with RMAN)

Ø  INIT.ORA (manually)

Ø  Password Files (manually)

Ø  Archive log files(with RMAN)

 

 

 

3.   When you take a hot backup putting Tablespace in begin backup mode, Oracle records SCN # from header of a database file.  What happens when you issue hot backup database in RMAN at block level backup? How does RMAN mark the record that the block has been backed up ?  How does RMAN know what blocks were backed up so that it doesn't have to scan them again?

In 11g, there is Oracle Block Change Tracking feature.  Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole data files to detect changed blocks.

Logging of changed blocks is performed by the Change Tracking Writer (CTWR) process which is also responsible for writing data to the block change tracking file. RMAN uses SCNs on the block level and the archived redo logs to resolve any inconsistencies in the data files from a hot backup. What RMAN does not require is to put the tablespace in BACKUP mode, thus freezing the SCN in the header. Rather, RMAN keeps this information in either your control files or in the RMAN repository (i.e., Recovery Catalog). 

Enabling and Disabling Change Tracking

We can enable or disable change tracking when the database is either open or mounted. To alter the change tracking setting, we must use SQL*Plus to connect to the target database with administrator privileges.

To store the change tracking file in the database area, set DB_CREATE_FILE_DEST in the target database. Then issue the following SQL statement to enable change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING; 

We can also create the change tracking file in a desired location, using the following SQL statement:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f';

The REUSE option tells Oracle to overwrite any existing file with the specified name.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f' REUSE;

To disable change tracking, use this SQL statement:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; 

If the change tracking file was stored in the database area, then it will be deleted when we disable change tracking.

Checking Whether Change Tracking is enabled

From SQL*Plus, we can query V$BLOCK_CHANGE_TRACKING to determine whether change tracking is enabled or not.

What happens during hot backup?

ALTER TABLESPACE tbs_name BEGIN BACKUP;

command, at that point a checkpoint is performed against the target tablespace and the datafile header is frozen, so no more updates are allowed on it (the datafile header), this is for the database to know which was the last time the tablespace had a consistent image of the data.

In case of redo log generation, each block will be recorded into the redo log files, the first time it the block is changed. So if a row is modified for the first time inside date block since hot backup started the complete block image is recorded in the redo log files but subsequent transactions on the block will only record the transaction just as normal.

Oracle guarantees that any blocks changed in the datafile while in hot backup mode will also be available in the redologs in case they are ever used for a recovery.

Now many one claims that during hot backup process there is excessive redo log generation than in normal mode. It actually depends on the amount of blocks changes during hot backup process. Because the first time a block is changed logging of full images of changed blocks in these tablespaces are recorded to the redo logs. Normally, Oracle logs an entry in the redologs for every change in the database, but it does not log the whole image of the database block. But during the hot backup process by logging full images of changed DB blocks to the redologs, Oracle eliminates the possibility of the backup containing irresolvable split blocks. To understand this reasoning, you must first understand what a split block is.

Typically, Oracle database blocks are a multiple of O/S blocks. For instance, most windows filesystems have a default block size of 512 bytes and unix filesystems have a default blocksize 2k, while Oracle’s default block size is 8k. This means that the filesystem stores data in 512 byte chunks, while Oracle performs reads and writes in 2k chunks, or multiples thereof. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd, cpio, or OCOPY. As it is making this copy, it is reading in O/S-block sized increments. If the database writer happens to be writing a DB block into the datafile at the same time that your script is reading that block’s constituent O/S blocks, your backup copy of the DB block could contain some O/S blocks from before the database performed the write, and some from after. This would be a split block.

By logging the full block image of the changed block to the redologs, it guarantees that in the event of a recovery, any split blocks that might be in the backup copy of the datafile will be resolved by overlaying them with the full legitimate image of the block from the redologs. Upon completion of a recovery, any blocks that got copied in a split state into the backup will have been resolved through application of full block images from the redologs.

Can I restore a database from obsolete backup?

Yes we can recover, If the backup still exists (on disk, tape, or other media) and is readable, it is possible to restore the Oracle database, even if RMAN considers it obsolete.

RMAN will not automatically suggest restoring from an obsolete backup, but you can manually direct RMAN to use it.

Oracle Zero Data Loss Recovery Appliance (ZDLRA)?

The Oracle Zero Data Loss Recovery Appliance (ZDLRA) is a powerful solution tailored for Oracle databases to ensure zero data loss, efficient backup, and rapid recovery. Its tight integration with Oracle RMAN, real-time redo transport, and ability to scale across thousands of databases makes it an essential tool for enterprises requiring robust data protection and disaster recovery strategies.