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
Copyright (c) 1982, 2019, Oracle and/or
its affiliates. All rights reserved.
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
- Automatic and Manual
Channel Allocation
- Automatic
Channel Device Configuration
- Automatic
Channel Default Device Types
- Automatic
Channel Naming Conventions
- Automatic
Channel Generic Configurations
- Automatic
Channel Specific Configurations
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
Copyright
(c) 1982, 2019, Oracle and/or its affiliates.
All rights reserved.
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:
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.
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
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
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
