你的位置:首页 > 数据库

[数据库]oracle数据库rman备份计划及恢复


1.rman完全恢复的前提条件:历史的datafile,controlfile和spfile备份,加上完整的archivelog和完好的redolog。

2.rman备份脚本:

  a.RMAN 0级备份命令:

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\rfdb\AUTOBACKUP\rman_dir\RFDB_level_0_%u_%s_%p" as compressed backupsetdatabase;  sql "alter system archive log current";  backup filesperset 3 format "E:\recovery_area\rfdb\rfdb\AUTOBACKUP\rman_dir\arch_%u_%s_%p_%c"  archivelog all delete input; #备份归档可选,可以单独定期备份  release channel c1;  release channel c2;  release channel c3;  } 

rman_level_0

  b.RMAN 1级备份命令:

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\rfdb\AUTOBACKUP\rman_dir\RFDB_level_1_%u_%s_%p'  as compressed backupsetdatabase;  sql 'alter system archive log current';  backup filesperset 3 format 'E:\recovery_area\rfdb\rfdb\AUTOBACKUP\rman_dir\arch_%u_%s_%p'  archivelog all delete input; #备份归档可选,可以单独定期备份  release channel c1;  release channel c2;  release channel c3;  } 

rman_level_1

  c.rman删除备份命令(在保留最近一天备份的情况下,删除其他备份):

DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 1 DAYS;  

  d.操作系统层面运行rman备份或删除命令:

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

View Code

 3.rman恢复

a.拷贝datafile,controlfile和spfile的rman备份,以及完整的archivelog和完好的redolog文件到新的数据库。

b.切换至oracle用户,进入rman(先设置sid):

export ORACLE_SID=rfdb
rlwrap rman target /

c.启动一个伪实例:

RMAN> startup nomountconnected to target database (not started)startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db1/dbs/initrfdb.ora'starting Oracle instance without parameter file for retrieval of spfileOracle instance startedTotal System Global Area  1068937216 bytesFixed Size           2260088 bytesVariable Size        281019272 bytesDatabase Buffers       780140544 bytesRedo Buffers          5517312 bytes

d.在伪实例下恢复spfile文件(必须要指定rman的备份片):

RMAN> restore spfile from "/u01/ora_bak/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq_.bkp";Starting restore at 14-DEC-15using channel ORA_DISK_1channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/ora_bak/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq_.bkpchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP completeFinished restore at 14-DEC-15

e.关闭伪实例,用spfile文件启动至nomount状态:

RMAN> shutdown abortOracle instance shut downRMAN> startup nomountconnected to target database (not started)Oracle instance startedTotal System Global Area  1068937216 bytesFixed Size           2260088 bytesVariable Size        910164872 bytesDatabase Buffers       150994944 bytesRedo Buffers          5517312 bytes

f.恢复控制文件(必须要指定rman的备份片,备份片应该和spfile的恢复片是同一个):

RMAN> restore controlfile from "/u01/ora_bak/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq_.bkp";Starting restore at 14-DEC-15allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=1146 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/app/oracle/oradata/ATEST/controlfile/o1_mf_c5fr9b0h_.ctloutput file name=/u01/app/oracle/fast_recovery_area/ATEST/controlfile/o1_mf_c5fr9b6n_.ctlFinished restore at 14-DEC-15

g.启动数据库至mount状态:

RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1

h.把拷贝到新机器的备份文件注册到(刚恢复的)控制文件中(redolog不能被注册,所以最后有报错,没有关系):

RMAN> catalog start with "/u01/ora_bak";Starting implicit crosscheck backup at 14-DEC-15allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=1146 device type=DISKCrosschecked 1 objectsFinished implicit crosscheck backup at 14-DEC-15Starting implicit crosscheck copy at 14-DEC-15using channel ORA_DISK_1Finished implicit crosscheck copy at 14-DEC-15searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files=======================File Name: /u01/app/oracle/fast_recovery_area/ATEST/backupset/2015_12_09/o1_mf_nnndf_TAG20151209T161546_c6hrslnq_.bkpFile Name: /u01/app/oracle/fast_recovery_area/ATEST/autobackup/2015_12_09/o1_mf_s_898014415_c6hsghgm_.bkpFile Name: /u01/app/oracle/fast_recovery_area/ATEST/autobackup/2015_12_09/o1_mf_s_898013812_c6hrvo18_.bkpsearching for all files that match the pattern /u01/ora_bakList of Files Unknown to the Database=====================================File Name: /u01/ora_bak/O1_MF_S_895896351_C4KSF2YN_.BKPFile Name: /u01/ora_bak/ora_bak/arch/1_11_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_23_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_19_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_33_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_8_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_9_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_17_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_21_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_20_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_12_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_6_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_14_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_26_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_24_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_30_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_16_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_22_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_7_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_27_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_31_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_25_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_29_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_28_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_13_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_18_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_10_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_15_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_32_896812716.dbfFile Name: /u01/ora_bak/ora_bak/controlfile/o1_mf_c5fr9b6n_.ctlFile Name: /u01/ora_bak/ora_bak/onlinelog_f/o1_mf_2_c5fr9h1f_.logFile Name: /u01/ora_bak/ora_bak/onlinelog_f/o1_mf_3_c5fr9kfo_.logFile Name: /u01/ora_bak/ora_bak/onlinelog_f/o1_mf_1_c5fr9ds4_.logFile Name: /u01/ora_bak/ora_bak/onlinelog/o1_mf_3_c5fr9k9d_.logFile Name: /u01/ora_bak/ora_bak/onlinelog/o1_mf_2_c5fr9gy5_.logFile Name: /u01/ora_bak/ora_bak/onlinelog/o1_mf_1_c5fr9dmk_.logFile Name: /u01/ora_bak/ora_bak/backupset/2015_12_07/o1_mf_nnndf_TAG20151207T173421_c6bnnxsl_.bkpFile Name: /u01/ora_bak/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq_.bkpFile Name: /u01/ora_bak/O1_MF_S_895896118_C4KS5S1R_.BKPDo you really want to catalog the above files (enter YES or NO)? yescataloging files...cataloging doneList of Cataloged Files=======================File Name: /u01/ora_bak/ora_bak/arch/1_11_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_23_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_19_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_33_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_8_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_9_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_17_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_21_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_20_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_12_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_6_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_14_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_26_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_24_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_30_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_16_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_22_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_7_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_27_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_31_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_25_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_29_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_28_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_13_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_18_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_10_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_15_896812716.dbfFile Name: /u01/ora_bak/ora_bak/arch/1_32_896812716.dbfFile Name: /u01/ora_bak/ora_bak/backupset/2015_12_07/o1_mf_nnndf_TAG20151207T173421_c6bnnxsl_.bkpFile Name: /u01/ora_bak/ora_bak/autobackup/2015_12_07/o1_mf_s_897845728_c6bnq1rq_.bkpList 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: RFDBFile Name: /u01/ora_bak/ora_bak/controlfile/o1_mf_c5fr9b6n_.ctl RMAN-07519: Reason: Error while cataloging. See alert.log.File Name: /u01/ora_bak/ora_bak/onlinelog_f/o1_mf_2_c5fr9h1f_.log RMAN-07529: Reason: catalog is not supported for this file typeFile Name: /u01/ora_bak/ora_bak/onlinelog_f/o1_mf_3_c5fr9kfo_.log RMAN-07529: Reason: catalog is not supported for this file typeFile Name: /u01/ora_bak/ora_bak/onlinelog_f/o1_mf_1_c5fr9ds4_.log RMAN-07529: Reason: catalog is not supported for this file typeFile Name: /u01/ora_bak/ora_bak/onlinelog/o1_mf_3_c5fr9k9d_.log RMAN-07529: Reason: catalog is not supported for this file typeFile Name: /u01/ora_bak/ora_bak/onlinelog/o1_mf_2_c5fr9gy5_.log RMAN-07529: Reason: catalog is not supported for this file typeFile Name: /u01/ora_bak/ora_bak/onlinelog/o1_mf_1_c5fr9dmk_.log RMAN-07529: Reason: catalog is not supported for this file typeFile Name: /u01/ora_bak/O1_MF_S_895896118_C4KS5S1R_.BKP RMAN-07518: Reason: Foreign database file DBID: 966107096 Database Name: RFDB

i.开始restore数据文件:

RMAN> restore database;Starting restore at 14-DEC-15using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ATEST/datafile/o1_mf_system_c5fr6s3v_.dbfchannel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ATEST/datafile/o1_mf_sysaux_c5fr6s6d_.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ATEST/datafile/o1_mf_undotbs1_c5fr6s7n_.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ATEST/datafile/o1_mf_users_c5fr6s88_.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ATEST/backupset/2015_12_09/o1_mf_nnndf_TAG20151209T161546_c6hrslnq_.bkpchannel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ATEST/backupset/2015_12_09/o1_mf_nnndf_TAG20151209T161546_c6hrslnq_.bkp tag=TAG20151209T161546channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:45Finished restore at 14-DEC-15

j.开始recover数据(在此之前,需要先拷贝redolog到控制文件默认的路径下):

redolog默认路径:

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_.log6 rows selected.

开始recover数据库:

RMAN> recover database;Starting recover at 14-DEC-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=1137 device type=DISKstarting media recoveryarchived log for thread 1 with sequence 15 is already on disk as file /u01/ora_bak/ora_bak/arch/1_15_896812716.dbfarchived log for thread 1 with sequence 16 is already on disk as file /u01/ora_bak/ora_bak/arch/1_16_896812716.dbfarchived log for thread 1 with sequence 17 is already on disk as file /u01/ora_bak/ora_bak/arch/1_17_896812716.dbfarchived log for thread 1 with sequence 18 is already on disk as file /u01/ora_bak/ora_bak/arch/1_18_896812716.dbfarchived log for thread 1 with sequence 19 is already on disk as file /u01/ora_bak/ora_bak/arch/1_19_896812716.dbfarchived log for thread 1 with sequence 20 is already on disk as file /u01/ora_bak/ora_bak/arch/1_20_896812716.dbfarchived log for thread 1 with sequence 21 is already on disk as file /u01/ora_bak/ora_bak/arch/1_21_896812716.dbfarchived log for thread 1 with sequence 22 is already on disk as file /u01/ora_bak/ora_bak/arch/1_22_896812716.dbfarchived log for thread 1 with sequence 23 is already on disk as file /u01/ora_bak/ora_bak/arch/1_23_896812716.dbfarchived log for thread 1 with sequence 24 is already on disk as file /u01/ora_bak/ora_bak/arch/1_24_896812716.dbfarchived log for thread 1 with sequence 25 is already on disk as file /u01/ora_bak/ora_bak/arch/1_25_896812716.dbfarchived log for thread 1 with sequence 26 is already on disk as file /u01/ora_bak/ora_bak/arch/1_26_896812716.dbfarchived log for thread 1 with sequence 27 is already on disk as file /u01/ora_bak/ora_bak/arch/1_27_896812716.dbfarchived log for thread 1 with sequence 28 is already on disk as file /u01/ora_bak/ora_bak/arch/1_28_896812716.dbfarchived log for thread 1 with sequence 29 is already on disk as file /u01/ora_bak/ora_bak/arch/1_29_896812716.dbfarchived log for thread 1 with sequence 30 is already on disk as file /u01/ora_bak/ora_bak/arch/1_30_896812716.dbfarchived log for thread 1 with sequence 31 is already on disk as file /u01/ora_bak/ora_bak/arch/1_31_896812716.dbfarchived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_2_c5fr9gy5_.logarchived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_3_c5fr9k9d_.logarchived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_1_c5fr9dmk_.logarchived log file name=/u01/ora_bak/ora_bak/arch/1_15_896812716.dbf thread=1 sequence=15archived log file name=/u01/ora_bak/ora_bak/arch/1_16_896812716.dbf thread=1 sequence=16archived log file name=/u01/ora_bak/ora_bak/arch/1_17_896812716.dbf thread=1 sequence=17archived log file name=/u01/ora_bak/ora_bak/arch/1_18_896812716.dbf thread=1 sequence=18archived log file name=/u01/ora_bak/ora_bak/arch/1_19_896812716.dbf thread=1 sequence=19archived log file name=/u01/ora_bak/ora_bak/arch/1_20_896812716.dbf thread=1 sequence=20archived log file name=/u01/ora_bak/ora_bak/arch/1_21_896812716.dbf thread=1 sequence=21archived log file name=/u01/ora_bak/ora_bak/arch/1_22_896812716.dbf thread=1 sequence=22archived log file name=/u01/ora_bak/ora_bak/arch/1_23_896812716.dbf thread=1 sequence=23archived log file name=/u01/ora_bak/ora_bak/arch/1_24_896812716.dbf thread=1 sequence=24archived log file name=/u01/ora_bak/ora_bak/arch/1_25_896812716.dbf thread=1 sequence=25archived log file name=/u01/ora_bak/ora_bak/arch/1_26_896812716.dbf thread=1 sequence=26archived log file name=/u01/ora_bak/ora_bak/arch/1_27_896812716.dbf thread=1 sequence=27archived log file name=/u01/ora_bak/ora_bak/arch/1_28_896812716.dbf thread=1 sequence=28archived log file name=/u01/ora_bak/ora_bak/arch/1_29_896812716.dbf thread=1 sequence=29archived log file name=/u01/ora_bak/ora_bak/arch/1_30_896812716.dbf thread=1 sequence=30archived log file name=/u01/ora_bak/ora_bak/arch/1_31_896812716.dbf thread=1 sequence=31archived log file name=/u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_2_c5fr9gy5_.log thread=1 sequence=32archived log file name=/u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_3_c5fr9k9d_.log thread=1 sequence=33archived log file name=/u01/app/oracle/oradata/ATEST/onlinelog/o1_mf_1_c5fr9dmk_.log thread=1 sequence=34media recovery complete, elapsed time: 00:00:04Finished recover at 14-DEC-15

k.以resetlogs打开数据库(在此之前,先删除原来的redolog,因为数据库会重新创建一组redolog):

删除原来的redo:

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

以resetlogs打开数据库:

RMAN> alter database open resetlogs;using target database control file instead of recovery catalogdatabase opened

至此,数据库恢复全部完成!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---恢复内容结束---