RMAN -Interview Based Questions

1. What is RMAN:-

Oracle Recovery Manager (RMAN) satisfies the most pressing demands of performant, manageable backup and recovery, for all Oracle data formats.

RMAN provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems.

Rman connect the live database also we connect auxiliary database

[oracle@vignesh ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Oct 2 06:52:04 2021

Version 19.3.0.0.0

connected to target database: LIVEDB (DBID=3038906043)

RMAN>

2. What are the architectural components of RMAN:-

RMAN Architecture

Recovery Manager (RMAN) is a utility that can manage all of your Oracle backup and recovery activities. DBAs are often wary of using RMAN because of its perceived complexity and its control over performing critical tasks. The traditional backup and recovery methods are tried-and-true. Thus, when your livelihood depends on your ability to back up and recover the database, why implement a technology like RMAN? The reason is that RMAN comes with several benefits:

Target database:

It is a database, which contains data files, controls files and redo files that are needed to be backed up or recovered. The target database control file is used by RMAN to collect metadata about the target database. Server sessions running on the target database perform all the backup and recovery work. It is a mandatory component for RMAN.

RMAN client:

It is the client application that performs all the backup and recovery operations for the target database. It uses Oracle net to connect to the target database so that its location can be found on any host that is connected to the target host using Oracle Net. It is a command line interface which helps in issuing the backup, recover, SQL and special RMAN commands. It is a mandatory component for RMAN.

Recovery catalog schema:

It is the user present in the recovery catalog database that has the metadata tables made by RMAN. RMAN periodically shifts metadata from the control file of the target database to the recovery catalog. It is an optional component.

Recovery catalog database:

It is a database that contains the recovery catalog that contains metadata which is used by RMAN to perform backup and recovery tasks. One recovery catalog can be created for containing metadata of multiple target databases. It is also an optional component.

Physical standby database:

It is a copy of the primary database which is updated with archived redo logs. It has the same Database id and database name as the primary database, but it has different DB_UNIQUE_NAME. Standby databases can be created, backed up and recovered by RMAN. It is not a mandatory component.

Duplicate database:

It is also a copy of the primary database but it is used for testing purposes. Its DB_ID is also different from the primary database.

Fast recovery area:

It is a disk location used to store files related to recovery such as control file, online redo files flashback logs, RMAN backups, and archived redo files. Files present in the fast recovery area are automatically managed by the Oracle database and RMAN.

Media Manager:

It is a vendor-specific application that allows RMAN to back up the storage system such as tape. It is an optional component.

Media Management catalog:

It is a vendor-specific repository of metadata related to media management application. It is also an optional component.

Oracle Enterprise Manager:

It is a browser-based interface that includes backup and recovery through RMAN. It is also an optional component

3. What are Channels :-

A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics such as:

Type of I/O device being read or written to, either a disk or an sbt_tape

Number of processes simultaneously accessing an I/O device

Maximum size of files created on I/O devices

Maximum rate at which database files are read Maximum number of files open at a time

Some models for chanel allocation in RMAN

4. Why is the catalog optional :-

RMAN considers all user-managed backups as image copies. While cataloging, RMAN does not check whether the file was correctly copied by the operating system utility: it just checks the header.

A recovery catalog is required when using RMAN in a Data Guard environment. The recovery catalog supports a unified file namespace for all primary and standby databases with the same DBID but different DB_UNIQUE_NAME values. Thus, the recovery catalog keeps track of database file names for all primary and standby databases, and also where online redo logs, standby redo logs, temp files, archived redo log files, backup sets, and image copies were created.

"RMAN Backups in a Data Guard Environment" explains how RMAN handles backups made on a different primary and standby databases. In general, tape backups made on one database are accessible to any database in the environment, whereas disk backups are accessible only to the database that created them.

If backups are accessible to the connected target database, RMAN commands such as RESTORE and RECOVER behave transparently across different databases. You can manually transfer a disk backup from one host in the environment to another host and then catalog the backup. If a backup is on shared disk, then you can use CHANGE RESET DB_UNIQUE_NAME to associate the backup with a new database.

5. What is a Backup set :-

A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

6. What is a Backup piece :-

A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files. When RMAN constructs a backup piece from datafiles, there are a several rules that it follows:

· A datafile cannot span backup sets

· A datafile can span backup pieces as long as it stays within one backup set

· Datafiles and control files can coexist in the same backup sets

· Archived redo log files are never in the same backup set as datafiles or control files RMAN is the only tool that can operate on backup pieces. If you need to restore a file from an RMAN backup, you must use RMAN to do it. There's no way for you to manually reconstruct database files from the backup pieces. You must use RMAN to restore files from a backup piece.

7. What are the benefits of using RMAN :-

Selects the most appropriate backup for database recovery.
Uses simple commands.
User can automatically backup the database on tape.
A database can be recovered in the data block level.
Only changed data blocks can be recovered using incremental backup functionality which reduces the backup time.
Secure backups can be created using the Encryption feature.
Clone of the database can be created on the remote host by using the duplicate command of RMAN.
Physical standby databases can be created simply.
1. Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backup

8. The difference between nocatalog and catalog :-

The nocatalog method is to use the control file as the catalog. For each backup, a lot of backup information must be written into the control file, and there will be more and more backup information in the control file. If it is a catalog, you must first create a catalog backup database and establish a recovery catalog. When backing up Oracle through rman nocatalog, Oracle uses controlfile to store backup information. Therefore, when using rman nocatalog backup, it is very important to back up the controlfile.

The initialization parameter control_file__record_keep_time sets the storage time of the backup information, and the previous backup information is automatically cleared at the specified time:

SQL> alter sysem set control_file_record_keep_time=7 scope=spfile;

There is a problem. When using rman nocatalog to restore, the database must be in "mount" State. The prerequisite for Oracle startup mount is that control must exist. Therefore, you must restore the controlfile before restoring the datafile. When using rman catalog mode, you can start nomount and then restore controlfile; but when using rman nocatalog, you must first restore controlfile in file mode.
Let’s compare the recovery steps of rman nocatalog and rman catalog in order to establish the correct backup strategy (the following recovery are all backups in the online state):
rman nocatalog recovery:

1) Establish oracle operating environment (including init or sp file)
2) Restore controlfile to the location specified by init file in file mode
3) startup mount
4) rman, restore datafile
5) alter database open resetlogs
rman catalog restoration:
1) Establish oracle operation Environment (including init or sp file)
2) rman, restore controfile
3) alter database mount
4) rman, restore datafile
5) alter database open resetlogs
can be seen that when rman nocatalog is backed up, the controlfile must be backed up by file.
In addition, because nocatalog uses the controlfile to store the backup information, it is recommended to increase the CONTROL_FILE_RECORD_KEEP_TIME value in the Oracle parameter file (the default is 7 days), the parameter is in $ORACLE_HOME/dbs/initSID.ora (it may also be in the spfile after 9i) , Can only be changed through Oracle statements).

9. Difference beteween using recovery catalog vs control file :-

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

In recovery catalog we can store scripts.

Recovery catalog is central and can have information of many databases.

10. how do yo know how much RMAN activity completed :-

V$RMAN_BACKUP_JOB_DETAILS in that data dictionary view we can monitor RMAN activities.

Login as sysdba and issue the following script:

This script will report on all currently running RMAN backups like full, incremental & archivelog backups:

SQL> col STATUS format a9

SQL> col hrs format 999.99

SQL> select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS

order by session_key; 2 3 4 5

SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS

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

1 DB FULL FAILED 09/15/21 03:56 09/15/21 03:56 .00

3 DB FULL COMPLETED 09/15/21 03:59 09/15/21 04:00 .02

30 DB FULL COMPLETED 09/18/21 01:43 09/18/21 01:44 .01

33 DB INCR FAILED 09/18/21 01:48 09/18/21 01:49 .02

47 DB INCR COMPLETED 09/18/21 02:01 09/18/21 02:02 .01

55 DB INCR COMPLETED 09/18/21 02:22 09/18/21 02:22 .01

60 DB INCR COMPLETED 09/18/21 02:52 09/18/21 02:53 .01

65 DB INCR COMPLETED 09/18/21 03:36 09/18/21 03:37 .01

SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS

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

85 DB INCR COMPLETED 09/27/21 05:10 09/27/21 05:10 .01

98 DB INCR COMPLETED 09/27/21 06:08 09/27/21 06:08 .01

103 DB INCR COMPLETED 09/27/21 06:09 09/27/21 06:10 .01

108 DB INCR COMPLETED 09/28/21 06:08 09/28/21 06:09 .02

18 rows selected.

11. How to delete archive logs older than 3 days :-

Once you are connected, then you can use the following to delete archivelogs to remove last 3 days before archivelogs

[oracle@vignesh ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Oct 2 07:43:15 2021

Version 19.3.0.0.0

connected to target database: LIVEDB (DBID=3038906043)

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE "sysdate-3";

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=74 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=72 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=68 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=64 device type=DISK

List of Archived Log Copies for database with db_unique_name LIVEDB

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

Key Thrd Seq S Low Time

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

1 1 18 A 15-SEP-21

Name: /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/o1_mf_1_18_jn28r8l8_.arc

2 1 19 A 15-SEP-21

Name: /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/o1_mf_1_19_jn290qn7_.arc

4 1 1 A 15-SEP-21

Name: /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/o1_mf_1_1_jn35xn84_.arc

5 1 2 A 15-SEP-21

Name: /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_16/o1_mf_1_2_jn5o9mrb_.arc

6 1 3 A 16-SEP-21

Name: /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_17/o1_mf_1_3_jn9ccw1k_.arc

Do you really want to delete the above objects (enter YES or NO)? yes

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_28/o1_mf_1_51_jo4rzgb6_.arc RECID=54 STAMP=1084428490

Deleted 15 objects

deleted archived log

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/o1_mf_1_1_jn29bmmh_.arc RECID=3 STAMP=1083298347

Deleted 13 objects

12. What is the use of crosscheck command in RMAN :-

Crosscheck is a very useful command in RMAN which automatically checks all the backups whose information is store in the RMAN repository and whether they are available on the disk or not.

If a corresponding backup is not available on the disk then Crosscheck command marks the backup as Expired otherwise it marks it as Available

To check status of all backupsets we can give the following command

RMAN> crosscheck backup;

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/oracle/rman_bkp/c-3038906043-20210928-00 RECID=118 STAMP=1084428557

Crosschecked 30 objects

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/oracle/rman_bkp/3038906043-20210928-3k0a646t_1_1 RECID=116 STAMP=1084428509

Crosschecked 34 objects

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/oracle/rman_bkp/3038906043-20210928-3l0a646t_1_1 RECID=115 STAMP=1084428513

Crosschecked 28 objects

Rman> Crosscheck copy

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_10_01/o1_mf_1_56_jof9zpmp_.arc RECID=59 STAMP=1084794448

Crosschecked 1 objects

13 .which is good differential or cumulative in RMAN backup :-

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

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

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

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because during recovery each differential backup must be applied in succession. Use cumulative incremental backups instead of differential, if enough disk space is available to store cumulative incremental backups.

This is command for taking Level 0 backup.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

14 .What is level 0 and level 1 backup :-

Level 0 and Level 1 Incremental Backups in Oracle RMAN

Incremental backups can be either level 0 or level 1. 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. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

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

Incremental backups are differential by default.

Note:

The size of the backup file depends solely upon the number of blocks modified and the incremental backup level.

The following commands performs a level 0 backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL=0 DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

Cumulative Incremental Backups

In a cumulative level 1 backup, RMAN backs up all the blocks used since the most recent level 0 incremental backup. Cumulative incremental backups reduce the work needed for a restore by ensuring that you only need one incremental backup from any particular level. Cumulative backups require more space and time than differential backups, because they duplicate the work done by previous backups at the same level.

The following command performs a cumulative level 1 incremental backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE SKIP INACCESSIBLE DATABASE;

In the example shown, the following occurs:
On Sunday - An incremental level 0 backup backs up all blocks that have ever been in use in this database.

On Monday - Saturday - A cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday, the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup.
The cycle is repeated for the next week.

15. Can we perform level 1 backup with out level 0 backup :-

In a differential level 1 backup, RMAN backs up all blocks that have changed since the most recent cumulative or differential incremental backup, whether at level 1 or level 0. RMAN determines which level 1 backup occurred most recently and backs up all blocks modified after that backup. If no level 1 is available, RMAN copies all blocks changed since the level 0 backup.

The following command performs a level 1 differential incremental backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

If no level 0 backup is available, then the behavior depends upon the compatibility mode setting. If compatibility is >=10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup. In other words, the SCN at the time the incremental backup is taken is the file creation SCN. If compatibility <10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup, to be consistent with the behavior in previous releases.

In the example shown in, the following occurs:

On Sunday - An incremental level 0 backup backs up all blocks that have ever been in use in this database.
On Monday - Saturday - On each day from Monday through Saturday, a differential incremental level 1 backup backs up all blocks that have changed since the most recent incremental backup at level 1 or 0. So, the Monday backup copies blocks changed since Sunday level 0 backup, the Tuesday backup copies blocks changed since the Monday level 1 backup, and so forth.

The cycle is repeated for the next week.

16. What is Snapshot Control file in Oracle :-

When RMAN needs to re-synchronize from a read-consistent version of the control file, it creates a temporary snapshot control file. RMAN needs a snapshot control file only when re-synchronizing with the recovery catalog or when making a backup of the current control file.

If you are not using recovery catalog in the your environment ,you will not see the snapshot control file unless you make a backup of current control file

Location for Snapshot Control file

The default value for the snapshot control file is platform-specific and depends on the Oracle home.

For example, the default filename on some UNIX platforms in Oracle Database 10g is

$ORACLE_HOME/dbs/snapcf_@.f

17. What is obsolete backup and expired backup in RMAN :-

Two terms that are very important when it comes to RMAN backups validation they are

· Expired Backups

· Obsolete Backups

Expired backups

Let's take you trigger an RMAN backup and someone deleted backup set or backup pieces at OS level. The database CONTROFILE has the details of the backup on disk but at OS level the backup file does not exists.

We can run RMAN crosscheck command to check if backup files exists at OS level or not. If the backup files are not found, RMAN will mark it as EXPIRED.

RMAN> crosscheck backup

Obsolete backups

The general meaning of OBSOLETE is no longer used or required.

RMAN considers backups as OBSOLETE when they are no longer required for database recovery. This is done by one of the RMAN CONFIGURATION parameters.

For example, we set RMAN retention policy to redundancy 4. This means that if we have 5 database backups, the first one will be marked as OBSOLETE because we want to keep 4 copies of RMAN backups.

We use below command to list all the obsolete backups inside RMAN

RMAN> report obsolete;

18. 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.

19. Difference between delete input and delete all input in RMAN :-

To utilize storage , we can remove archive log with RMAN command delete input or delete all input for archive log backed up by RMAN.

1. backup archivelog all delete input.

Let us consider I have 3 archiving detination ‘/dest1′,’/dest2’ and ‘/dest3’ .

When we use delete input command , RMAN would delete the specific archived redo log files that it backed up.

For example RMAN would delete archived logs from ‘/dest1’ if those were the files used as the source of the backup, but it would leave archive log of the ‘/dest2’ and ‘/dest3’ intact.

2. backup archivelog all delete all input.

As per above consideration of archiving destination , In this case RMAN backs up only one copy of each log sequence number in these directories, and then deletes all copies of any log that it backed up from the archiving destinations.

20. ORA-28000 account locked error :-

Wait for PASSWORD_LOCK_TIME or contact DBA

ORA-28000 will be thrown. If this happens, the Agent account will be locked because it has attempted to logon to the database incorrectly a certain amount of times, and ORA-28000 will become a problem.

resolve ORA-28000, keep in mind that FAILED_LOGIN_ATTEMPTS is maxed at 10 because DBSNMP has a default profile, after the 10 have been maxed, the account will lock.

ORA-28000 continues occurring after the credentials have been correctly set, make sure that all services which are connecting as DBSNMP have the correct passwords. You might want to even make a different DBDNMP monitoring configuration profile using the following command after you have logged on to the Database as sysdba:

CREATE PROFILE MONITORING_PROFILE LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

ALTER USER DBSNMP PROFILE MONITORING_PROFILE;

To resolve ORA-28000 you can request that the administrator unlocks the account using something like the below, the user name is 'scott' and password is 'tiger' as an example:

SQL> alter user scott identified by tiger account unlock;

ORA-28000 has been thrown because during the installation, all of the existing accounts, with exception to sys and system, are defaulted as locked. This being so, you can also try unlocking these accounts to resolve ORA-28000

Vignesh Oracle DBA

Wednesday, October 6, 2021

RMAN -Interview Based Questions & Answers

16. What is Snapshot Control file in Oracle :-

Location for Snapshot Control file

17. What is obsolete backup and expired backup in RMAN :-

Expired backups

Obsolete backups

1 comment:

ORA-38824 for trigger FLOWS_FILES.wwv_biu_flow_file_objects.

My Blog List

Performance Tuning

Oracle EBS Queries

Oracle DBA Scripts

Backup and Recovery

Oracle DBA Articles