Recovery Manager (RMAN) is a command-line tool provided by Oracle for managing the backup and recovery of Oracle databases. RMAN automates the process of creating backups, restoring datafiles, and recovering the database. Here are some key concepts and commands related to Oracle RMAN.
- Target DB : The target Database is the database which you back up, and in the case of a duplicate database it would be the source database.
- Catalog DB : The catalog database is when you use an RMAN catalog instead of using the control file. In this case the RMAN information is stored in both the control file and the catalog database.
- Auxiliary DB : The auxiliary database is the database which in the case of a duplicate database would be the database you are duplicating to. So for example if you setup a standby database using duplicate database (and maybe duplicate database from active database) the primary database would be the target and the standby database would be the auxiliary..
CONNECT command:
Connect command used to establish a connection between RMAN and a target, auxiliary, or recovery catalog database.
RMAN> CONNECT TARGET / RMAN> CONNECT TARGET user_name@target_db_tns; RMAN> CONNECT CATALOG rman/pwd@catdb_tns; RMAN> CONNECT AUXILIARY rman/pwd@auxdb_tns;
Allocate Channel: Example of allocate channel for using in different purpose.
RMAN> ALLOCATE CHANNEL c1 DEVICE TYPE 'SBT_TAPE'; RMAN> ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '\rman\ORCL\backup\db_u%'; RMAN> ALLOCATE CHANNEL t1 DEVICE TYPE DISK CONNECT 'sys/pwd@target_db_tns’; RMAN> ALLOCATE AUXILIARY CHANNEL c1 TYPE DISK; RMAN> ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE SBT;
Backup Command:
RMAN> BACKUP DATABASE; RMAN> BACKUP DATABASE TAG=’full backup’; RMAN> BACKUP TAG 'Weekly_Full_DB_Backup' DATABASE MAXSETSIZE 50G; RMAN> BACKUP MAXSETSIZE 10G DATABASE PLUS ARCHIVELOG; DURATION: Specifies a maximum time for a backup command to run. If a backup command does not complete in the specified duration, then the backup stops. With disk backups, you can use MINIMIZE TIME run the backup at maximum speed (default), or MINIMIZE LOAD to slow the rate of backup to lessen the load on the system. With MINIMIZE LOAD the backup takes the full specified duration. RMAN> BACKUP DURATION 02:00 DATABASE; RMAN> BACKUP DURATION 00:30 MINIMIZE TIME DATABASE; RMAN> BACKUP DURATION 00:45 MINIMIZE LOAD DATABASE; RMAN> BACKUP DATABASE PLUS ARCHIVELOG; RMAN> BACKUP DATABASE KEEP FOREVER; RMAN> BACKUP DATABASE KEEP UNTIL TIME=’SYSDATE+30’; RMAN> BACKUP DATABASE UNTIL 'SYSDATE+365' NOLOGS; NOEXCLUDE: With NOEXCLUDE, RMAN backs up all tablespaces, including any for which a CONFIGURE EXCLUDE command has been entered. This option does not override SKIP OFFLINE or SKIP READONLY. RMAN> BACKUP DATABASE NOEXCLUDE; RMAN> BACKUP DATABASE NOEXCLUDE KEEP FOREVER TAG=’abc’; RMAN> BACKUP DATABASE SKIP READONLY; RMAN> BACKUP DATABASE SKIP OFFLINE; RMAN> BACKUP DATABASE SKIP INACCESSIBLE; RMAN> BACKUP DATABASE SKIP READONLY SKIP OFFLINE SKIP INACCESSIBLE;
Backup Teablespace or Datafile:
RMAN> BACKUP TABLESPACE TEST; RMAN> BACKUP TABLESPACE SYSTEM, USERS, TOOLS; RMAN> BACKUP TABLESPACE 1; RMAN> BACKUP DATAFILE 4; RMAN> BACKUP DATAFILE '/u01/datafile/system01.dbf';
Backup Archivelog File:
Note: DELETE INPUT: Deletes the input files after successfully backing them up.
Specify this option only when backing up archived redo log files, data file copies (COPY OF or DATAFILECOPY), or backup sets. Specifying the DELETE INPUT option is equivalent to issuing the DELETE command for the input files.
RMAN> BACKUP ARCHIVELOG ALL; RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT; RMAN> BACKUP ARCHIVELOG LIKE '/arch%' DELETE ALL INPUT; RMAN> BACKUP ARCHIVELOG FROM TIME ‘SYSDATE–3’; RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 2350; RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 2300 DELETE INPUT; RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 50 DELETE ALL INPUT; RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 26779 UNTIL SEQUENCE 26800 FORMAT '/rman/orcl/backup/archive_backup.bkp'; RMAN> BACKUP ARCHIVELOG ALL FROM SEQUENCE 1200 DELETE ALL INPUT; RMAN> BACKUP CHECK LOGICAL DATABASE; RMAN> BACKUP ARCHIVELOG NOT BACKED UP 2 TIMES; RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-14' AND 'SYSDATE-7';
Incremental Backup Command:
Incremental backups in Oracle RMAN allow you to back up only the data that has changed since the last backup, reducing the amount of data that needs to be transferred and stored. Here’s an example of how you might perform an incremental backup in Oracle RMAN:
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE; RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE; RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
RMAN Backup optimization:
RMAN backup optimization is another feature to minimize backup size. Enabling this feature, RMAN BACKUP will skip backup of files that comes in certain condition or exactly identical to the file already backup up.
RMAN> SHOW BACKUP OPTIMIZATION; RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE BACKUP OPTIMIZATION OFF; # default RMAN> CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters are successfully stored
Restore and Recover:
Restore: Restore refers to the process of copying data from a backup to its original location. This involves replacing damaged or lost files with the copies from a backup.
Recover: Recover involves applying the changes from redo logs or incremental backups to bring a database forward in time to a specific point. It ensures that the database is consistent and up-to-date.
Switch Command:
RMAN> SWITCH DATABASE TO COPY; RMAN> SWITCH TABLESPACE users to copy; RMAN> SWITCH DATAFILE ALL; RMAN> SWITCH DATAFILE '/disk1/tols.dbf' TO DATAFILECOPY '/disk2/tols.copy'; RMAN> SWITCH TEMPFILE 1; RMAN> SWITCH TEMPFILE ALL; RMAN> SWITCH CLONE DATAFILE ALL;
Restore Command:
Restore Database: RMAN> RESTORE DATABASE; RMAN> RESTORE DATABASE SKIP TABLESPACE temp, history; RMAN> RESTORE DATABASE UNTIL SCN 154876; Restore Tablespace or Datafile: RMAN> RESTORE TABLESPACE users; RMAN> RESTORE TABLESPACE data1, data2; RMAN> RESTORE TABLESPACE tbs1 PREVIEW; RMAN> RESTORE TABLESPACE users VALIDATE; RMAN> RESTORE DATAFILE 45; RMAN> RESTORE DATAFILE 23 PREVIEW; RMAN> RESTORE DATAFILE 12 VALIDATE; Restore Control file: RMAN> RESTORE CONTROLFILE; RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; RMAN> RESTORE CONTROLFILE FROM TAG 'monday_cf_backup'; RMAN> RESTORE CONTROLFILE FROM '/u01/control01.ctl';
Recover Command:
RMAN> RECOVER DATABASE; RMAN> RECOVER DATABASE NOREDO; RMAN> RECOVER DATABASE SKIP TABLESPACE TBSTEST; RMAN> RECOVER DATABASE SKIP FOREVER TABLESPACE TBSDATA03; RMAN> RECOVER DATABASE UNTIL SCN 154876; RMAN> RECOVER TABLESPACE users;
Database restoration validate command:
Control file restoration:
RMAN> RESTORE CONTROLFILE VALIDATE; Starting restore at 13-NOV-23 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile backup set channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/ORCL/autobackup/2023_10_26/o1_mf_s_1151243216_lmof10o5_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/ORCL/autobackup/2023_10_26/o1_mf_s_1151243216_lmof10o5_.bkp tag=TAG20231026T134656 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 Finished restore at 13-NOV-23
Database restore validation:
RMAN> RESTORE DATABASE PREVIEW SUMMARY;
Starting restore at 13-NOV-23
using channel ORA_DISK_1
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
4 B F A DISK 26-OCT-23 1 1 NO TAG20231026T134639
3 B F A DISK 26-OCT-23 1 1 NO TAG20231026T134639
2 B F A DISK 26-OCT-23 1 1 NO TAG20231026T134639
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
2 1 8 A 26-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_8_lmof0z3s_.arc
3 1 9 A 26-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_9_lmok2q6g_.arc
4 1 10 A 26-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_10_lmok38gn_.arc
5 1 11 A 26-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_27/o1_mf_1_11_lmqqtcgt_.arc
6 1 12 A 27-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_11_12/o1_mf_1_12_lo34xznv_.arc
7 1 13 A 12-NOV-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_11_13/o1_mf_1_13_lo4rgb9t_.arc
recovery will be done up to SCN 2204760
Media recovery start SCN is 2204760
Recovery must be done beyond SCN 2204762 to clear datafile fuzziness
Finished restore at 13-NOV-23
RMAN> RESTORE DATABASE VALIDATE; Starting restore at 13-NOV-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=85 device type=DISK channel ORA_DISK_1: starting validation of datafile backup set channel ORA_DISK_1: reading from backup piece /u01/backup/c3_1_4_1.rman channel ORA_DISK_1: piece handle=/u01/backup/c3_1_4_1.rman tag=TAG20231026T134639 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting validation of datafile backup set channel ORA_DISK_1: reading from backup piece /u01/backup/c2_1_3_1.rman channel ORA_DISK_1: piece handle=/u01/backup/c2_1_3_1.rman tag=TAG20231026T134639 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: validation complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting validation of datafile backup set channel ORA_DISK_1: reading from backup piece /u01/backup/c1_1_2_1.rman channel ORA_DISK_1: piece handle=/u01/backup/c1_1_2_1.rman tag=TAG20231026T134639 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: validation complete, elapsed time: 00:00:03 Finished restore at 13-NOV-23
Datafile restore validation:
RMAN> RESTORE DATAFILE 3 VALIDATE;
Starting restore at 13-NOV-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/backup/c2_1_3_1.rman
channel ORA_DISK_1: piece handle=/u01/backup/c2_1_3_1.rman tag=TAG20231026T134639
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 13-NOV-23
RMAN> RESTORE DATABASE PREVIEW;
Starting restore at 13-NOV-23
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 791.03M DISK 00:00:10 26-OCT-23
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20231026T134639
Piece Name: /u01/backup/c1_1_2_1.rman
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2204760 26-OCT-23 NO /u01/oradata/ORCL/system01.dbf
7 Full 2204760 26-OCT-23 NO /u01/oradata/ORCL/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 406.88M DISK 00:00:06 26-OCT-23
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20231026T134639
Piece Name: /u01/backup/c2_1_3_1.rman
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
2 Full 2204761 26-OCT-23 NO /u01/oradata/ORCL/tbsapp01.dbf
3 Full 2204761 26-OCT-23 NO /u01/oradata/ORCL/sysaux01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 29.95M DISK 00:00:01 26-OCT-23
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20231026T134639
Piece Name: /u01/backup/c3_1_4_1.rman
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
4 Full 2204762 26-OCT-23 NO /u01/oradata/ORCL/undotbs01.dbf
5 Full 2204762 26-OCT-23 NO /u01/oradata/ORCL/tbstest01.dbf
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
2 1 8 A 26-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_8_lmof0z3s_.arc
3 1 9 A 26-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_9_lmok2q6g_.arc
4 1 10 A 26-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_26/o1_mf_1_10_lmok38gn_.arc
5 1 11 A 26-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_10_27/o1_mf_1_11_lmqqtcgt_.arc
6 1 12 A 27-OCT-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_11_12/o1_mf_1_12_lo34xznv_.arc
7 1 13 A 12-NOV-23
Name: /u01/app/oracle/recovery_area/ORCL/archivelog/2023_11_13/o1_mf_1_13_lo4rgb9t_.arc
recovery will be done up to SCN 2204760
Media recovery start SCN is 2204760
Recovery must be done beyond SCN 2204762 to clear datafile fuzziness
Finished restore at 13-NOV-23
Database backup validation:
RMAN> VALIDATE DATABASE; Starting validate at 13-NOV-23 using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00001 name=/u01/oradata/ORCL/system01.dbf input datafile file number=00003 name=/u01/oradata/ORCL/sysaux01.dbf input datafile file number=00004 name=/u01/oradata/ORCL/undotbs01.dbf input datafile file number=00005 name=/u01/oradata/ORCL/tbstest01.dbf input datafile file number=00002 name=/u01/oradata/ORCL/tbsapp01.dbf input datafile file number=00007 name=/u01/oradata/ORCL/users01.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:07 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 1 OK 0 17136 116487 2584830 File Name: /u01/oradata/ORCL/system01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 81074 Index 0 13177 Other 0 5093 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 2 OK 0 2433 2560 2202900 File Name: /u01/oradata/ORCL/tbsapp01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 0 Other 0 127 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 3 OK 0 23491 79360 2584991 File Name: /u01/oradata/ORCL/sysaux01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 8225 Index 0 5722 Other 0 41922 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 4 OK 0 513 44160 2584991 File Name: /u01/oradata/ORCL/undotbs01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 0 Other 0 43647 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 5 OK 0 24016 25600 2235158 File Name: /u01/oradata/ORCL/tbstest01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 1413 Index 0 0 Other 0 171 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 7 OK 0 101 641 1252497 File Name: /u01/oradata/ORCL/users01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 60 Index 0 15 Other 0 464 channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation including current control file for validation including current SPFILE in backup set channel ORA_DISK_1: validation complete, elapsed time: 00:00:01 List of Control File and SPFILE =============================== File Type Status Blocks Failing Blocks Examined ------------ ------ -------------- --------------- SPFILE OK 0 2 Control File OK 0 646 Finished validate at 13-NOV-23
RMAN> VALIDATE CURRENT CONTROLFILE; RMAN> VALIDATE SPFILE; RMAN> VALIDATE CHECK LOGICAL DATABASE; RMAN> VALIDATE SKIP INACCESSIBLE DATABASE; RMAN> VALIDATE COPY OF DATABASE; RMAN> VALIDATE TABLESPACE DATA; RMAN> VALIDATE DATAFILE 2; RMAN> VALIDATE DATAFILE 14,18; RMAN> VALIDATE DATAFILE 3 BLOCK 84;
Block level recover commands:
RMAN> RECOVER CORRUPTION LIST; RMAN> RECOVER DATAFILE 8 block 22; RMAN> RECOVER DATAFILE 7 block 233,235 DATAFILE 4 block 101; RMAN> RECOVER TABLESPACE DATA DBA 4194404,4194405 FROM TAG "fullbackup"; RMAN> RECOVER TABLESPACE DATA DBA 94404 RESTORE UNTIL TIME 'SYSDATE-2'; RMAN> RECOVER DATAFILE 40 block 1081008 NOFILEUPDATE SAVE all blocks;
List Command:
RMAN> LIST BACKUP SUMMARY; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 1 B A A DISK 26-OCT-23 1 1 NO TAG20231026T134638 2 B F A DISK 26-OCT-23 1 1 NO TAG20231026T134639 3 B F A DISK 26-OCT-23 1 1 NO TAG20231026T134639 4 B F A DISK 26-OCT-23 1 1 NO TAG20231026T134639 5 B A A DISK 26-OCT-23 1 1 NO TAG20231026T134655 6 B F A DISK 26-OCT-23 1 1 NO TAG20231026T134656
RMAN> LIST BACKUP OF DATABASE; RMAN> LIST BACKUP; RMAN> LIST BACKUP OF PLUGGABLE DATABASE pdb1, pdb2; RMAN> LIST COPY OF DATAFILE 2, 3, 6; RMAN> LIST BACKUP OF DATAFILE 4 SUMMARY; RMAN> LIST ARCHIVELOG ALL; RMAN> LIST BACKUP OF ARCHIVELOG ALL; RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL; RMAN> LIST INCARNATION; RMAN> LIST BACKUP OF CONTROLFILE; RMAN> LIST BACKUP OF SPFILE; RMAN> LIST BACKUP BY FILE; RMAN> LIST BACKUP OF DATAFILE 3; RMAN> LIST EXPIRED BACKUP; RMAN> LIST EXPIRED BACKUP SUMMARY; RMAN> LIST EXPIRED BACKUP OF ARCHIVELOG ALL; RMAN> LIST EXPIRED BACKUP OF ARCHIVELOG ALL SUMMARY; RMAN> LIST FAILURE;
