Top 33 Oracle RMAN Interview Questions

Last updated on November 7th, 2019 at 03:15 am


Oracle RMAN Interview Questions


Oracle RMAN Interview Questions

Question 1

What is RMAN?
Answer
RMAN stands for recovery manager.it is a utility that can manage your entire Oracle backup and recovery activities.

Question 2

What is the Difference between catalog and nocatalog mode in RMAN?
Answer
Few differences
a.Recovery catalog is central and can have information of many databases.
While in case of nocatalog, control file is used to store the information and it can store for that database only

b. When new incarnation happens, the old backup information in control file will be lost. It will be preserved in recovery catalog.

c. In recovery catalog, we can store scripts.

Question 3

How do u know how much RMAN task has been completed?
Answer 
By querying v$rman_status or v$session_longops

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
/

COLUMN EVENT FORMAT a10
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30

SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 'sbt%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR
;

Question 4

How did the list and report commands of RMAN get the data?
Answer
RMAN commands reports the data by querying v$ in case of nocatalog and recovery catalog (RC) view in case of Catalog mode.
Example
V$DATAFILE_COPY in case of Control file
RC_DATAFILE_COPY in case of Recovery Catalog

Question 5

Will RMAN put the database/tablespace/datafile in backup mode? or What is the difference between manual hot backup and RMAN backup?

Answer
RMAN does not put database/tablespace/datafile in backup mode like manual backup .
For hot backup, we have to put database in begin backup mode, then take backup. but this is not the case with RMAN backup.

Mechanism Following by Oracle when we taking hot backup

Question 6

What is the RMAN Command to delete archive logs older than 1 days?
Answer

RMAN> delete archivelog all completed before sysdate-1;

Question 7

Name few tables and views in the Recovery catalog schema

Answer

Tables


NAME DESCRIPTION


AL contains archived logs. archived logs are uniquely identified by
dbinc_key, recid and stamp.

BCB contains corrupt block ranges in datafile backups.

BCF contains control file backups (in backup sets).

BDF contains all datafile backups (in backup sets).

BP contains all backup pieces of backup sets.

BRL contains backup redo logs (in backup sets).

BS contains all backup sets for all database incarnations.

CCB contains corrupt block ranges in datafile copies.

CCF contains control file copies.

CDF contains all datafile copies.

CKP records all recovery catalog checkpoints.

DB contains all target databases that have been registered in this
recovery catalog.

DBINC contains all incarnations of the target databases registered in this
recovery catalog.

DF contains all datafiles of all database incarnations.

Views:

NAME                      DESCRIPTION


RC_ARCHIVED_LOG information about all archivelogs.

RC_BACKUP_CONTROLFILE backup control files in backup sets.

RC_BACKUP_CORRUPTION corrupt blocks in datafile backups and copies.

RC_BACKUP_DATAFILE datafile backups (in backup sets).

RC_BACKUP_PIECE backup pieces.

RC_BACKUP_REDOLOG redo log backups (in backup sets).

RC_BACKUP_SET backup sets.

RC_CHECKPOINT rc_checkpoint is replaced by rc_resync, but is still
used by some tests.

RC_CONTROLFILE_COPY controlfile copies.

Question 8

Give us the broad level steps on how to create the recovery catalog schema?
Answer

How to create RMAN recovery catalog and register database in catalog

Question 9

What is the use of crosscheck command in RMAN?
Answer
Crosscheck will be useful to check whether the catalog information is intact with OS level information.

Question 10

What are the differences between crosscheck and validate commands?
Answer 
Validate command is to examine a backup set and report whether it can be restored. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksum to verify that the contents are intact so that backup can be successfully restored if necessary.

Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The crosscheck command only processes files created on the same device type as the channel running crosscheck.

Question 11

What are the benefits of using RMAN?
Answer
Important Benefits of using RMAN are
a. Built-in reporting and listing commands.
b. Tablespaces or database are not put in backup mode unlike manual backup, So there is no extra redo log generation during online backups.
c. Parallelization of I/O operations using channels
d. Automatic logging of all backup and recovery operations.
e. Incremental backups that only copy data blocks that have changed since the last backup using change block tracking
f. Detection of corrupt blocks during backups.

Question 12

What is snapshot control file?
Answer
The snapshot control file is a copy of a database control file created in an operating system-specific location by RMAN. RMAN creates the snapshot control file so that it has a consistent version of a control file to use when either re synchronizing the recovery catalog or backing up the control file.

Question 13

What is the difference between backup set and backup piece?
Answer
Backup set is logical and backup piece is physical.

Question 14

How do we create the standby database using RMAN ?
Answer
We can use RMAN backup to create the standby database or use Active database option to directly create standby from Primary without taking backup
With backup

RMAN> duplicate target database to standby database

With Active database

RMAN >RUN
{
CONFIGURE DEVICE TYPE DISK PARALLELISM 6;
duplicate target database for standby from active database;
}

Question 15

How to do Database cloning by using RMAN?
Answer
We can use RMAN backup to create the database or use Active database option to directory create standby from Primary without taking backup
With backup

RMAN> duplicate target database to TEST;

Oracle RMAN Interview Questions

 

With Active database

RMAN >RUN
{
CONFIGURE DEVICE TYPE DISK PARALLELISM 6;
duplicate target database to TEST from active database;
}

Oracle RMAN Interview Questions

 

 

Question 16

How to catalog manual/user-managed backup in RMAN ?
Answer
By using catalog command.
i. disk backup pieces you can use:

CATALOG BACKUPPIECE '<handle>';

ii. You can also catalog all the pieces inside of a directory:

CATALOG START WITH '<directory>';

iii.If backups are in recovery area, we can catalog the whole recovery area with :

CATALOG RECOVERY AREA NOPROMPT;

iv. For tape backup pieces follow:

This is an example using Oracle Secure Backup (OSB):

Define a tape channel in the RMAN automatic configuration:

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE'
PARMS 'SBT_LIBRARY=/usr/local/oracle/backup/lib/libobk.so,ENV=(OB_MEDIA_FAMILY=RMAN-DEFAULT)';
*. Catalog the tape backup piece using handle:
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '0pivagf8_1_1';

Question 17

What is the use of nofilenamecheck in RMAN ?
Answer
When NOFILENAMECHECK is used with the DUPLICATE command, RMAN does not validate the filenames during restoration. If the primary database and the standby database are on the same host, The DUPLICATE NOFILENAMECHECK option should not be used.

Question 18

Do we need to shutdown db to change block change tracking file?
Answer
No need. It can be enabled and disabled online

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Alternatively, you can specify location of block change tracking file:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/<path>/bct.ora';

To disable:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

View V$BLOCK_CHANGE_TRACKING can be queried to find out the status of change tracking in the database.

Question 19

How would you start the Oracle database if you lose your SPFILE and PFILE and your DB crashed with some error?

Answer
In that case you have remaining option to create new pfile using the non-default parameters recorded in the the alert.log file. You can find this in the previous successful startup entries.

You can then start the database using this pfile (init.ora)

SQL> STARTUP PFILE=’location/init<SID>.ora?’;
Then finally create spfile from this pfile. The below command will automatically create spfile with its original name.

SQL> Create spfile from pfile=’location/init<SID>.ora?’;

IF RMAN auto-backup is configured in any of the above situations, you can restore the same.

RMAN> RESTORE SPFILE FROM AUTOBACKUP;

Question 20

What is a backup retention policy

Answer
A retention policy describes which backups will be kept and for how long.You can use the CONFIGURE RETENTION POLICY command to create a persistent and automatic backup retention policy. When a backup retention policy is in effect, RMAN considers backups of datafiles and control files as obsolete, that is, no longer needed for recovery, according to criteria that you specify in the CONFIGURE command. You can then use the REPORT OBSOLETE command to view obsolete files and DELETE OBSOLETE to delete them.

Question 21

What is obsolete backup and expired backup?
Answer
A status of “expired” means that the backup piece or backup set is not found in the backup destination.
A status of “obsolete” means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.

obsolete means “not needed,” whereas expired means “not found.”

Question 22

What are the database file’s that RMAN cannot take backup ?
Answer
RMAN cannot take backup of the pfile, redo logs, tns or network configuration files, password files, external tables and the contents of the Oracle home files.

Question 23

Can I take RMAN backup when the database is down ?
Answer 
No, we can take RMAN backup only when the target database is open or in mount stage.

Question 24

what happens if the retention policy is configured to NONE
Answer
REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete

Question 25

How to see the configuration of the RMAN?
Answer
RMAN > show all;

Question 26

What are the types of retention polices in RMAN?
Answer
There are two mutually exclusive options for implementing a retention policy: redundancy and recovery window. If no retention policy is configured by the user, then the REPORT OBSOLETE and DELETE OBSOLETE commands use a default retention policy of REDUNDANCY 1.

To configure a retention policy based on a recovery window, use the following command:

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF x DAYS;

To configure a retention policy based on redundancy, use the following command:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY x;

You can also disable the retention policy completely, meaning that RMAN does not consider any backup to be obsolete. To do so, use the following command:

RMAN> CONFIGURE RETENTION POLICY TO NONE;

 

Question 27

What is differential (incremental) backup or cumulative (incremental) backup? and which one is good from the recovery time point of view

Answer
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

Cumulative backup are better from the recovery time point of view

Question 28

What is Level 0, Level 1 backup?
Answer
A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. A level 1 incremental backup can be either of the following types:
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

Question 29

Suppose you lost one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have backup of this (newly created) datafile. How do you restore/recover file?
Answer 
create the datafile and recover that datafile.
SQL> alter database create datafile ‘…path..’ size n;
RMAN> recover datafile file_id;

Question 30

Can we use same target database as catalog?
Answer
No. The recovery catalog should not reside in the target database (database to be backed up), because the database can’t be recovered in the mounted state.

Question 31

What are new features in Oracle 12c RMAN?
Answer
a.Fine grained recovery:
With Oracle Database 12c, you can use a simple RECOVER TABLE command to perform a point-in-time recovery of a table/partition without having to go through a manual point-in-time recovery process. This command automatically performs the following steps: creation of the auxiliary instance, table recovery, exporting of the object, and importing it into the production database.b.Support for multi-tenant databases
c. Improved RMAN duplication (cloning) performance: Now Duplicate database with active database used backup set instead of image copies and hence improving the performance
d. Separation of Duty using SYSBACKUP role
e. SQL interface in RMAN
f.Expansion of Multi-section support
g. Simplified cross platform migration

Question 32

How to do Listing of backups in RMAN?
Answer
Use LIST to display information about backup sets, proxy copies, and image copies recorded in the repository. The LIST command displays the files against which you can run CROSSCHECK and DELETE commands. Use this command to list:

Backups and copies that do not have the status AVAILABLE in the RMAN repository
Backups and copies of datafiles that are available and can possibly be used in a restore operation
Specified archived logs, backup sets, backup pieces, control file copies, datafile copies, and proxy copies
Backups and copies restricted by tag, completion time, recoverability, or device
Incarnations of a specified database or of all databases known to the repository
Stored scripts in the recovery catalog
Reporting on Database Files and Backups

Question 33

What is the difference between LIST and REPORT command
Answer
Report command performs more complex analysis than LIST. Use the REPORT command to answer questions such as the following:

Which files need a backup?
Which files have not had a backup for some time?
Which files are not recoverable due to unrecoverable operations?
Which backup files can be deleted?
What was the physical schema of the database at a previous time

I hope you like the Oracle RMAN interview questions.


Related Articles

Top 49 Oracle Interview questions : Basics , Oracle SQL

Top 40 oracle dba interview questions and answers

Top 46 Oracle ASM Interview Questions

Most commonly asked 25 oracle plsql interview questions

40 Important Weblogic Interview questions


 

Leave a Reply