Oracle Database rman backup plan and recovery

Oracle Database rman backup plan and recovery

1. Prerequisites for completely restoring rman: Historical backup of datafile, controlfile, and spfile, plus full archivelog and complete redolog.

2. rman backup script:

A. RMAN level 0 BACKUP command:

Run {
Allocate channel c1 type disk;
Allocate channel c2 type disk;
Allocate channel c3 type disk;
Backup incremental level 0 tag 'level0' format "E: \ recovery_area \ rfdb \ AUTOBACKUP \ rman_dir \ RFDB_level_0 _ % u _ % s _ % p" as compressed backupset
Database;
SQL "alter system archive log current ";
Backup filesperset 3 format "E: \ recovery_area \ rfdb \ AUTOBACKUP \ rman_dir \ arch _ % u _ % s _ % p _ % c"
Archivelog all delete input; # backup archiving is optional and can be backed up regularly separately
Release channel c1;
Release channel c2;
Release channel c3;
}

B. RMAN Level 1 BACKUP command:

Run {
Allocate channel c1 type disk;
Allocate channel c2 type disk;
Allocate channel c3 type disk;
Backup incremental level 1 tag 'level1' format 'e: \ recovery_area \ rfdb \ AUTOBACKUP \ rman_dir \ RFDB_level_1 _ % u _ % s _ % P' as compressed backupset
Database;
SQL 'alter system archive log current ';
Backup filesperset 3 format 'e: \ recovery_area \ rfdb \ AUTOBACKUP \ rman_dir \ arch _ % u _ % s _ % P'
Archivelog all delete input; # backup archiving is optional and can be backed up regularly separately
Release channel c1;
Release channel c2;
Release channel c3;
}

C. rman Delete backup command (delete other backups when the last day of backup is retained ):

Delete noprompt obsolete recovery window of 1 DAYS;

D. Run the rman backup or delete command at the operating system level:

Rman target sys/logs @ rfdb nocatalog CMDFILE 'd: \ app \ rman \ rman_file \ level_0.txt 'log = E: \ recovery_area \ rfdb \ AUTOBACKUP \ rman_dir \ log \ rman_level_0.log

3. rman recovery

A. Copy the rman backups of datafile, controlfile, and spfile, and complete archivelog and intact redolog files to the new database.

B. Switch to the Oracle user and enter rman (set sid first ):

Export ORACLE_SID = rfdb
Rlwrap rman target/

C. Start a pseudo instance:

RMAN> startup nomount

Connected to target database (not started)
Startup failure: ORA-01078: failure in processing system parameters
LRM-00109: cocould not open parameter file '/u01/app/oracle/product/11.2.0/db1/dbs/initrfdb. ora'

Starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes

D. Restore the spfile file under a pseudo instance (you must specify the snapshot of rman ):

RMAN> restore spfile from "/u01/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq _. bkp ";

Starting restore at 14-DEC-15
Using channel ORA_DISK_1

Channel ORA_DISK_1: restoring spfile from AUTOBACKUP/u01/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq _. bkp
Channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 14-DEC-15

E. Close the pseudo instance and start it to nomount with the spfile file:

RMAN> shutdown abort

Oracle instance shut down

RMAN> startup nomount

Connected to target database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 910164872 bytes
Database Buffers 150994944 bytes
Redo Buffers 5517312 bytes

F. Recovery control file (you must specify the snapshot of rman. the snapshot must be the same as the snapshot of the spfile ):

RMAN> restore controlfile from "/u01/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq _. bkp ";

Starting restore at 14-DEC-15
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 1146 device type = DISK

Channel ORA_DISK_1: restoring control file
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Output file name =/u01/app/oracle/oradata/ATEST/controlfile/o1_mf_c5fr9b0h _. ctl
Output file name =/u01/app/oracle/fast_recovery_area/ATEST/controlfile/o1_mf_c5fr9b6n _. ctl
Finished restore at 14-DEC-15

G. Start the database to the mount status:

RMAN> alter database mount;

Database mounted
Released channel: ORA_DISK_1

H. register the backup file copied to the new machine to the (recovered) control file (redolog cannot be registered, so an error is reported at the end. It does not matter ):

RMAN> catalog start with "/u01/ora_bak ";

Starting implicit crosscheck backup at 14-DEC-15
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 1146 device type = DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 14-DEC-15

Starting implicit crosscheck copy at 14-DEC-15
Using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-DEC-15

Searching for all files in the recovery area
Cataloging files...
Cataloging done

List of Cataloged Files
======================================
File Name:/u01/app/oracle/fast_recovery_area/ATEST/backupset/2015_12_09/o1_mf_nnndf_TAG20151209T161546_c6hrslnq _. bkp
File Name:/u01/app/oracle/fast_recovery_area/ATEST/autobackup/2015_12_09/o1_mf_s_8980141__c6hsghgm _. bkp
File Name:/u01/app/oracle/fast_recovery_area/ATEST/autobackup/2015_12_09/o1_mf_s_898013812_c6hrvo18 _. bkp

Searching for all files that match the pattern/u01/ora_bak

List of Files Unknown to the Database
============================================
File Name:/u01/ora_bak/O1_MF_S_895896351_C4KSF2YN_.BKP
File Name:/u01/ora_bak/arch/12711_89681_16.dbf
File Name:/u01/ora_bak/arch/g023_89681_16.dbf
File Name:/u01/ora_bak/arch/1_19_89681_16.dbf
File Name:/u01/ora_bak/arch/1_33_89681_16.dbf
File Name:/u01/ora_bak/arch/4158_89681_16.dbf
File Name:/u01/ora_bak/arch/1_9_89681_16.dbf
File Name:/u01/ora_bak/arch/1_17_89681_16.dbf
File Name:/u01/ora_bak/arch/1_21_89681_16.dbf
File Name:/u01/ora_bak/arch/1_20_89681_16.dbf
File Name:/u01/ora_bak/arch/201712_896820.16.dbf
File Name:/u01/ora_bak/arch/4156_89681_16.dbf
File Name:/u01/ora_bak/arch/1_14_89681_16.dbf
File Name:/u01/ora_bak/arch/41526_89681_16.dbf
File Name:/u01/ora_bak/arch/1_24_89681_16.dbf
File Name:/u01/ora_bak/arch/1_30_89681_16.dbf
File Name:/u01/ora_bak/arch/12716_89681_16.dbf
File Name:/u01/ora_bak/arch/1_22_89681_16.dbf
File Name:/u01/ora_bak/arch/4107_896820.16.dbf
File Name:/u01/ora_bak/arch/41527_896820.16.dbf
File Name:/u01/ora_bak/arch/12731_89681_16.dbf
File Name:/u01/ora_bak/arch/255.25_896820.16.dbf
File Name:/u01/ora_bak/arch/1_29_89681_16.dbf
File Name:/u01/ora_bak/arch/1_28_89681_16.dbf
File Name:/u01/ora_bak/arch/1_13_89681_16.dbf
File Name:/u01/ora_bak/arch/41518_896820.16.dbf
File Name:/u01/ora_bak/arch/41510_89681_16.dbf
File Name:/u01/ora_bak/arch/41515_89681_16.dbf
File Name:/u01/ora_bak/arch/1_32_89681_16.dbf
File Name:/u01/ora_bak/controlfile/o1_mf_c5fr9b6n _. ctl
File Name:/u01/ora_bak/onlinelog_f/o1_mf_2_c5fr9h1f _. log
File Name:/u01/ora_bak/onlinelog_f/o1_mf_3_c5fr9kfo _. log
File Name:/u01/ora_bak/onlinelog_f/o1_mf_1_c5fr9ds4 _. log
File Name:/u01/ora_bak/onlinelog/o1_mf_3_c5fr9k9d _. log
File Name:/u01/ora_bak/onlinelog/o1_mf_2_c5fr9gy5 _. log
File Name:/u01/ora_bak/onlinelog/o1_mf_1_c5fr9dmk _. log
File Name:/u01/ora_bak/backupset/2015_12_07/o1_mf_nnndf_TAG20151207T173421_c6bnnxsl _. bkp
File Name:/u01/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq _. bkp
File Name:/u01/ora_bak/O1_MF_S_895896118_C4KS5S1R_.BKP

Do you really want to catalog the above files (enter YES or NO )? Yes
Cataloging files...
Cataloging done

List of Cataloged Files
======================================
File Name:/u01/ora_bak/arch/12711_89681_16.dbf
File Name:/u01/ora_bak/arch/g023_89681_16.dbf
File Name:/u01/ora_bak/arch/1_19_89681_16.dbf
File Name:/u01/ora_bak/arch/1_33_89681_16.dbf
File Name:/u01/ora_bak/arch/4158_89681_16.dbf
File Name:/u01/ora_bak/arch/1_9_89681_16.dbf
File Name:/u01/ora_bak/arch/1_17_89681_16.dbf
File Name:/u01/ora_bak/arch/1_21_89681_16.dbf
File Name:/u01/ora_bak/arch/1_20_89681_16.dbf
File Name:/u01/ora_bak/arch/201712_896820.16.dbf
File Name:/u01/ora_bak/arch/4156_89681_16.dbf
File Name:/u01/ora_bak/arch/1_14_89681_16.dbf
File Name:/u01/ora_bak/arch/41526_89681_16.dbf
File Name:/u01/ora_bak/arch/1_24_89681_16.dbf
File Name:/u01/ora_bak/arch/1_30_89681_16.dbf
File Name:/u01/ora_bak/arch/12716_89681_16.dbf
File Name:/u01/ora_bak/arch/1_22_89681_16.dbf
File Name:/u01/ora_bak/arch/4107_896820.16.dbf
File Name:/u01/ora_bak/arch/41527_896820.16.dbf
File Name:/u01/ora_bak/arch/12731_89681_16.dbf
File Name:/u01/ora_bak/arch/255.25_896820.16.dbf
File Name:/u01/ora_bak/arch/1_29_89681_16.dbf
File Name:/u01/ora_bak/arch/1_28_89681_16.dbf
File Name:/u01/ora_bak/arch/1_13_89681_16.dbf
File Name:/u01/ora_bak/arch/41518_896820.16.dbf
File Name:/u01/ora_bak/arch/41510_89681_16.dbf
File Name:/u01/ora_bak/arch/41515_89681_16.dbf
File Name:/u01/ora_bak/arch/1_32_89681_16.dbf
File Name:/u01/ora_bak/backupset/2015_12_07/o1_mf_nnndf_TAG20151207T173421_c6bnnxsl _. bkp
File Name:/u01/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq _. bkp

List of Files Which Where Not Cataloged
========================================================
File Name:/u01/ora_bak/O1_MF_S_895896351_C4KSF2YN_.BKP
RMAN-07518: Reason: Foreign database file DBID: 966107096 Database Name: RFDB
File Name:/u01/ora_bak/controlfile/o1_mf_c5fr9b6n _. ctl
RMAN-07519: Reason: Error while cataloging. See alert. log.
File Name:/u01/ora_bak/onlinelog_f/o1_mf_2_c5fr9h1f _. log
RMAN-07529: Reason: catalog is not supported for this file type
File Name:/u01/ora_bak/onlinelog_f/o1_mf_3_c5fr9kfo _. log
RMAN-07529: Reason: catalog is not supported for this file type
File Name:/u01/ora_bak/onlinelog_f/o1_mf_1_c5fr9ds4 _. log
RMAN-07529: Reason: catalog is not supported for this file type
File Name:/u01/ora_bak/onlinelog/o1_mf_3_c5fr9k9d _. log
RMAN-07529: Reason: catalog is not supported for this file type
File Name:/u01/ora_bak/onlinelog/o1_mf_2_c5fr9gy5 _. log
RMAN-07529: Reason: catalog is not supported for this file type
File Name:/u01/ora_bak/onlinelog/o1_mf_1_c5fr9dmk _. log
RMAN-07529: Reason: catalog is not supported for this file type
File Name:/u01/ora_bak/O1_MF_S_895896118_C4KS5S1R_.BKP
RMAN-07518: Reason: Foreign database file DBID: 966107096 Database Name: RFDB

I. Start restore data files:

RMAN> restore database;

Starting restore at 14-DEC-15
Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile backup set restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Channel ORA_DISK_1: restoring datafile 00001 to/u01/app/oracle/oradata/ATEST/datafile/o1_mf_system_c5fr6s3v _. dbf
Channel ORA_DISK_1: restoring datafile 00002 to/u01/app/oracle/oradata/ATEST/datafile/o1_mf_sysaux_c5fr6s6d _. dbf
Channel ORA_DISK_1: restoring datafile 00003 to/u01/app/oracle/oradata/ATEST/datafile/o1_mf_undotbs1_c5fr6s7n _. dbf
Channel ORA_DISK_1: restoring datafile 00004 to/u01/app/oracle/oradata/ATEST/datafile/o1_mf_users_c5fr6s88 _. dbf
Channel ORA_DISK_1: reading from backup piece/u01/app/oracle/fast_recovery_area/ATEST/backupset/2015_12_09/o1_mf_nnndf_TAG20151209T161546_c6hrslnq _. bkp
Channel ORA_DISK_1: piece handle =/u01/app/oracle/fast_recovery_area/ATEST/backupset/2015_12_09/tags _. bkp tag = TAG20151209T161546
Channel ORA_DISK_1: restored backup piece 1
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 14-DEC-15

J. Start recover data (before that, copy redolog to the default path of the control file ):

Redolog default path:

SQL> select member from v $ logfile;

MEMBER
--------------------------------------------------------------------------------
/U01/app/oracle/oradata/ATEST/onlinelog/o1_mf_3_c5fr9k9d _. log
/U01/app/oracle/fast_recovery_area/ATEST/onlinelog/o1_mf_3_c5fr9kfo _. log
/U01/app/oracle/oradata/ATEST/onlinelog/o1_mf_2_c5fr9gy5 _. log
/U01/app/oracle/fast_recovery_area/ATEST/onlinelog/o1_mf_2_c5fr9h1f _. log
/U01/app/oracle/oradata/ATEST/onlinelog/o1_mf_1_c5fr9dmk _. log
/U01/app/oracle/fast_recovery_area/ATEST/onlinelog/o1_mf_1_c5fr9ds4 _. log

6 rows selected.

 

Start recover database:

 

RMAN> recover database;

Starting recover at 14-DEC-15
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 1137 device type = DISK

Starting media recovery

Archived log for thread 1 with sequence 15 is already on disk as file/u01/ora_bak/arch/12715_89681_16.dbf
Archived log for thread 1 with sequence 16 is already on disk as file/u01/ora_bak/arch/12716_89681_16.dbf
Archived log for thread 1 with sequence 17 is already on disk as file/u01/ora_bak/arch/10917_89681_16.dbf
Archived log for thread 1 with sequence 18 is already on disk as file/u01/ora_bak/arch/12718_89681_16.dbf
Archived log for thread 1 with sequence 19 is already on disk as file/u01/ora_bak/arch/1_19_89681_16.dbf
Archived log for thread 1 with sequence 20 is already on disk as file/u01/ora_bak/arch/12720_89681_16.dbf
Archived log for thread 1 with sequence 21 is already on disk as file/u01/ora_bak/arch/12721_89681_16.dbf
Archived log for thread 1 with sequence 22 is already on disk as file/u01/ora_bak/arch/12722_89681_16.dbf
Archived log for thread 1 with sequence 23 is already on disk as file/u01/ora_bak/arch/12723_89681_16.dbf
Archived log for thread 1 with sequence 24 is already on disk as file/u01/ora_bak/arch/12724_89681_16.dbf
Archived log for thread 1 with sequence 25 is already on disk as file/u01/ora_bak/arch/255.25_89681_16.dbf
Archived log for thread 1 with sequence 26 is already on disk as file/u01/ora_bak/arch/12726_89681_16.dbf
Archived log for thread 1 with sequence 27 is already on disk as file/u01/ora_bak/arch/12727_89681_16.dbf
Archived log for thread 1 with sequence 28 is already on disk as file/u01/ora_bak/arch/1_28_89681_16.dbf
Archived log for thread 1 with sequence 29 is already on disk as file/u01/ora_bak/arch/12729_89681_16.dbf
Archived log for thread 1 with sequence 30 is already on disk as file/u01/ora_bak/arch/12730_89681_16.dbf
Archived log for thread 1 with sequence 31 is already on disk as file/u01/ora_bak/arch/12731_89681_16.dbf
Archived log for thread 1 with sequence 32 is already on disk as file/u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_2_c5fr9gy5 _. log
Archived log for thread 1 with sequence 33 is already on disk as file/u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_3_c5fr9k9d _. log
Archived log for thread 1 with sequence 34 is already on disk as file/u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_1_c5fr9dmk _. log
Archived log file name =/u01/ora_bak/arch/41515_89681_16.dbf thread = 1 sequence = 15
Archived log file name =/u01/ora_bak/arch/41016_89681_16.dbf thread = 1 sequence = 16
Archived log file name =/u01/ora_bak/arch/41517_89681_16.dbf thread = 1 sequence = 17
Archived log file name =/u01/ora_bak/arch/41518_89681_16.dbf thread = 1 sequence = 18
Archived log file name =/u01/ora_bak/arch/1_19_89681_16.dbf thread = 1 sequence = 19
Archived log file name =/u01/ora_bak/arch/41520_89681_16.dbf thread = 1 sequence = 20
Archived log file name =/u01/ora_bak/arch/1_21_89681_16.dbf thread = 1 sequence = 21
Archived log file name =/u01/ora_bak/arch/41522_89681_16.dbf thread = 1 sequence = 22
Archived log file name =/u01/ora_bak/arch/FIG 23_89681_16.dbf thread = 1 sequence = 23
Archived log file name =/u01/ora_bak/arch/41024_89681_16.dbf thread = 1 sequence = 24
Archived log file name =/u01/ora_bak/arch/41525_89681_16.dbf thread = 1 sequence = 25
Archived log file name =/u01/ora_bak/arch/41526_89681_16.dbf thread = 1 sequence = 26
Archived log file name =/u01/ora_bak/arch/41527_89681_16.dbf thread = 1 sequence = 27
Archived log file name =/u01/ora_bak/arch/logs 28_89681_16.dbf thread = 1 sequence = 28
Archived log file name =/u01/ora_bak/arch/1_29_89681_16.dbf thread = 1 sequence = 29
Archived log file name =/u01/ora_bak/arch/41530_89681_16.dbf thread = 1 sequence = 30
Archived log file name =/u01/ora_bak/arch/12731_89681_16.dbf thread = 1 sequence = 31
Archived log file name =/u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_2_c5fr9gy5 _. log thread = 1 sequence = 32
Archived log file name =/u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_3_c5fr9k9d _. log thread = 1 sequence = 33
Archived log file name =/u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_1_c5fr9dmk _. log thread = 1 sequence = 34
Media recovery complete, elapsed time: 00:00:04
Finished recover at 14-DEC-15

K. Open the database with resetlogs (delete the original redolog before this, because the database will re-create a redolog Group ):

Delete the original redo:

Rm-rf/u01/app/oracle/oradata/ATEST/onlinelog /*

Use resetlogs to open a database:

RMAN> alter database open resetlogs;

Using target database control file instead of recovery catalog
Database opened

So far, all database recovery has been completed!