2022年探索ORACLE之RMAN_非一致性备份归类 .pdf

《2022年探索ORACLE之RMAN_非一致性备份归类 .pdf》由会员分享，可在线阅读，更多相关《2022年探索ORACLE之RMAN_非一致性备份归类 .pdf（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、探索 ORACLE 之 RMAN_04非一致性备份在上一篇博文中提到了数据库的一致性备份，操作非常的简单。 只需要一条很短的命令即可完成，但是如果要创建一正式库的备份，一般不建议用一致性备份，也不建议用很简单的名来完成。而是更多的采用脚本实现非一致性备份，这样将可通过backup+archive log+redo有效的将数据恢复到最近一次改变的状态，可以达到数据的丢失最小化。创建非一致性备份创建非一致性备份数据库必须处于归档(archivelog)模式，因为非一致性备份的数据库文件和控制文件的 SCN 号可能会不一致。并且可以在数据库打开并不影响业务的情况下完成数据的备份工作；那么这样的备份将

2、是不一致性的备份，那么如果要恢复可以通过backup+archive log+redo来恢复到最近一次日志切换时候的数据，而不是最后一次备份时候的数据。1.1 检查归档状态：SQL archive log list;Database log mode No Archive ModeAutomaticarchival DisabledArchivedestination USE_DB_RECOVERY_FILE_DESTOldest onlinelog sequence 9Current logsequence 11SQL必须打开归档归档SQL shutdown immediate -打开归档必

3、须将数据库重启到mount 状态Databaseclosed.Databasedismounted.ORACLE instanceshut down.SQL startup mount;ORACLE instancestarted.Total SystemGlobal Area 285212672 bytesFixed Size 1218968 bytesVariableSize 79693416 bytesDatabaseBuffers 197132288 bytesRedoBuffers 7168000 bytesDatabasemounted.SQL alter database arc

4、hivelog; -执行开启归档命令Databasealtered.名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 15 页 - - - - - - - - - SQL alter database open; -打开数据库Databasealtered.SQL colinstance_name format a15SQL select instance_name,status from v$instance; -查看数据库状态INSTANCE_NAME STATUS -

5、WWL OPEN SQLSQL archive log list; -查看归档状态，已经是归档状态了Database log mode Archive Mode Automatic archival Enabled Archivedestination USE_DB_RECOVERY_FILE_DESTOldest onlinelog sequence 9Next logsequence to archive 11Current logsequence 11SQL1.2 执行备份(执行备份命令实际上是跟一致性备份是一样的)1.2.1备份数据库RMAN backup database;Start

6、ing backupat 23-MAY-12allocatedchannel: ORA_DISK_1channelORA_DISK_1: sid=145 devtype=DISKchannelORA_DISK_1: starting full datafile backupsetchannelORA_DISK_1: specifying datafile(s) in backupsetinput datafilefno=00001 name=/DBData/WWL/system01.dbfinput datafilefno=00003 name=/DBData/WWL/sysaux01.dbf

7、input datafilefno=00002 name=/DBData/WWL/undotbs01.dbfinput datafilefno=00004 name=/DBData/WWL/users01.dbfchannelORA_DISK_1: starting piece 1 at 23-MAY-12channelORA_DISK_1: finished piece 1 at 23-MAY-12piecehandle=/DBBak/bak_WWL_05_23_0anbmtkr_1_1 tag=TAG20120523T121210 comment=NONEchannelORA_DISK_1

8、: backup set complete, elapsed time: 00:01:46Finished backupat 23-MAY-12名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 15 页 - - - - - - - - - Starting ControlFile and SPFILE Autobackup at 23-MAY-12piecehandle=/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120523-0

9、0 comment=NONEFinished ControlFile and SPFILE Autobackup at 23-MAY-12RMAN1.2.2备份 USER表空间RMAN backuptablespace users; Starting backupat 23-MAY-12using channelORA_DISK_1channelORA_DISK_1: starting full datafile backupsetchannelORA_DISK_1: specifying datafile(s) in backupsetinput datafilefno=00004 name

10、=/DBData/WWL/users01.dbfchannelORA_DISK_1: starting piece 1 at 23-MAY-12channelORA_DISK_1: finished piece 1 at 23-MAY-12piecehandle=/DBBak/bak_WWL_05_23_0cnbn2a3_1_1 tag=TAG20120523T133147 comment=NONEchannelORA_DISK_1: backup set complete, elapsed time: 00:00:04Finished backupat 23-MAY-12Starting C

11、ontrolFile and SPFILE Autobackup at 23-MAY-12piece handle=/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120523-01comment=NONEFinished ControlFile and SPFILE Autobackup at 23-MAY-12RMAN1.2.3备份数据文件RMAN backup datafile 1,2,3;Starting backupat 23-MAY-12using channelORA_DISK_1channelORA_DISK_1: starting fu

12、ll datafile backupsetchannelORA_DISK_1: specifying datafile(s) in backupsetinput datafilefno=00001 name=/DBData/WWL/system01.dbfinput datafilefno=00003 name=/DBData/WWL/sysaux01.dbfinput datafile fno=00002name=/DBData/WWL/undotbs01.dbfchannelORA_DISK_1: starting piece 1 at 23-MAY-12channelORA_DISK_1

13、: finished piece 1 at 23-MAY-12名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 15 页 - - - - - - - - - piecehandle=/DBBak/bak_WWL_05_23_0enbn2bk_1_1 tag=TAG20120523T133235 comment=NONEchannelORA_DISK_1: backup set complete, elapsed time: 00:01:48Finished backupat

14、 23-MAY-12Starting ControlFile and SPFILE Autobackup at 23-MAY-12piecehandle=/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120523-02 comment=NONEFinished ControlFile and SPFILE Autobackup at 23-MAY-12RMAN1.2.4备份控制文件RMAN backup current controlfile;Starting backupat 23-MAY-12using channelORA_DISK_1chann

15、elORA_DISK_1: starting full datafile backupsetchannelORA_DISK_1: specifying datafile(s) in backupsetincludingcurrent control file in backupsetchannelORA_DISK_1: starting piece 1 at 23-MAY-12channelORA_DISK_1: finished piece 1 at 23-MAY-12piecehandle=/DBBak/bak_WWL_05_23_0gnbn2je_1_1 tag=TAG20120523T

16、133645 comment=NONEchannelORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backupat 23-MAY-12Starting ControlFile and SPFILE Autobackup at 23-MAY-12piecehandle=/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120523-03 comment=NONEFinished ControlFile and SPFILE Autobackup at 23-MAY-12RMAN

17、1.2.5备份 spfile文件RMAN backup spfile;Starting backupat 23-MAY-12using channelORA_DISK_1channelORA_DISK_1: starting full datafile backupsetchannelORA_DISK_1: specifying datafile(s) in backupsetincludingcurrent SPFILE in backupsetchannelORA_DISK_1: starting piece 1 at 23-MAY-12名师资料总结 - - -精品资料欢迎下载 - - -

18、 - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 15 页 - - - - - - - - - channelORA_DISK_1: finished piece 1 at 23-MAY-12piecehandle=/DBBak/bak_WWL_05_23_0inbn2l2_1_1 tag=TAG20120523T133737 comment=NONEchannelORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backupat 23-MAY-1

19、2Starting ControlFile and SPFILE Autobackup at 23-MAY-12piecehandle=/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120523-04 comment=NONEFinished ControlFile and SPFILE Autobackup at 23-MAY-12RMAN1.2.6查看我们刚才备份的信息：RMAN list backup;如下信息是之前做的备份：using target database control file instead of recoverycatalog

20、List of Backup Sets=BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -2 Full 522.59M DISK 00:00:59 18-MAY-12 BP Key:2 Status: AVAILABLE Compressed: NO Tag: TAG20120518T185700 Piece Name:/DBBak/bak_WWL_05_18_04nbaffs_1_1 List of Datafilesin backup set 2 File LV Type CkpSCN Ckp Tim

21、e Name - - - - - 1 Full 618100 18-MAY-12 /DBData/WWL/system01.dbf 2 Full 618100 18-MAY-12 /DBData/WWL/undotbs01.dbf 3 Full 618100 18-MAY-12 /DBData/WWL/sysaux01.dbf 4 Full 618100 18-MAY-12 /DBData/WWL/users01.dbf BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -3 Full 6.80M DISK

22、 00:00:02 18-MAY-12 BP Key:3 Status: AVAILABLE Compressed: NO Tag: TAG20120518T185806 Piece Name:/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120518-01 Control FileIncluded: Ckp SCN: 618100 Ckp time:18-MAY-12 SPFILE Included:Modification time: 18-MAY-12 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - -

23、- - - - - 名师精心整理 - - - - - - - 第 5 页，共 15 页 - - - - - - - - - BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -4 Full 523.54M DISK 00:01:09 21-MAY-12 BP Key:4 Status: AVAILABLE Compressed: NO Tag: TAG20120521T145403 Piece Name:/DBBak/bak_WWL_05_21_06nbhucb_1_1 List of Datafilesi

24、n backup set 4 File LV Type CkpSCN Ckp Time Name - - - - - 1 Full 622304 21-MAY-12 /DBData/WWL/system01.dbf 2 Full 622304 21-MAY-12 /DBData/WWL/undotbs01.dbf 3 Full 622304 21-MAY-12 /DBData/WWL/sysaux01.dbf 4 Full 622304 21-MAY-12 /DBData/WWL/users01.dbf BS Key Type LVSize Device Type Elapsed TimeCo

25、mpletion Time - - - - - -5 Full 6.80M DISK 00:00:01 21-MAY-12 BP Key:5 Status: AVAILABLE Compressed: NO Tag: TAG20120521T145520 Piece Name:/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120521-00 Control FileIncluded: Ckp SCN: 622304 Ckp time:21-MAY-12 SPFILE Included:Modification time: 21-MAY-12 BS Ke

26、y Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -6 Full 368.00K DISK 00:00:00 21-MAY-12 BP Key:6 Status: AVAILABLE Compressed: NO Tag: TAG20120521T151349 Piece Name:/DBBak/bak_WWL_05_21_08nbhvhe_1_1 List of Datafilesin backup set 6 File LV Type CkpSCN Ckp Time Name - - - - - 4 Full 6

27、22968 21-MAY-12 /DBData/WWL/users01.dbf BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -7 Full 6.80M DISK 00:00:01 21-MAY-12 BP Key:7 Status: AVAILABLE Compressed: NO Tag: TAG20120521T151351 Piece Name:/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120521-01 Control FileIncluded:

28、Ckp SCN: 622968 Ckp time:21-MAY-12 SPFILE Included:Modification time: 21-MAY-12 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 15 页 - - - - - - - - - 今天的备份信息如下：如下是全库备份信息：BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -8 Full 524.66M DISK 0

29、0:01:36 23-MAY-12 BP Key:8 Status: AVAILABLE Compressed: NO Tag: TAG20120523T121210 Piece Name:/DBBak/bak_WWL_05_23_0anbmtkr_1_1 List of Datafilesin backup set 8 File LV Type CkpSCN Ckp Time Name - - - - - 1 Full 649219 23-MAY-12 /DBData/WWL/system01.dbf 2 Full 649219 23-MAY-12 /DBData/WWL/undotbs01

30、.dbf 3 Full 649219 23-MAY-12 /DBData/WWL/sysaux01.dbf 4 Full 649219 23-MAY-12 /DBData/WWL/users01.dbf BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -9 Full 6.80M DISK 00:00:01 23-MAY-12 BP Key:9 Status: AVAILABLE Compressed: NO Tag: TAG20120523T121358 Piece Name:/DBSoft/produc

31、t/10.2.0/db_1/dbs/c-5520179-20120523-00 Control FileIncluded: Ckp SCN: 649233 Ckp time:23-MAY-12 SPFILE Included:Modification time: 23-MAY-12 如下是备份USER表空间信息：BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -10 Full 432.00K DISK 00:00:01 23-MAY-12 BP Key: 10 Status: AVAILABLE Comp

32、ressed: NO Tag: TAG20120523T133147 Piece Name:/DBBak/bak_WWL_05_23_0cnbn2a3_1_1 List of Datafilesin backup set 10 File LV Type CkpSCN Ckp Time Name - - - - - 4 Full 651081 23-MAY-12 /DBData/WWL/users01.dbf BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -11 Full 6.80M DISK 00:00

33、:03 23-MAY-12 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 15 页 - - - - - - - - - BP Key:11 Status: AVAILABLE Compressed: NO Tag: TAG20120523T133151 Piece Name:/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120523-01 Control FileIncluded: Ckp SCN: 651086 Ckp tim

34、e:23-MAY-12 SPFILE Included:Modification time: 23-MAY-12 如下是备份数据文件1， 2， 3的信息：BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -12 Full 524.61M DISK 00:01:42 23-MAY-12 BP Key:12 Status: AVAILABLE Compressed: NO Tag: TAG20120523T133235 Piece Name:/DBBak/bak_WWL_05_23_0enbn2bk_1_1 L

35、ist of Datafilesin backup set 12 File LV Type CkpSCN Ckp Time Name - - - - - 1 Full 651104 23-MAY-12 /DBData/WWL/system01.dbf 2 Full 651104 23-MAY-12 /DBData/WWL/undotbs01.dbf 3 Full 651104 23-MAY-12 /DBData/WWL/sysaux01.dbf BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -13 Fu

36、ll 6.80M DISK 00:00:01 23-MAY-12 BP Key:13 Status: AVAILABLE Compressed: NO Tag: TAG20120523T133424 Piece Name:/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120523-02 Control FileIncluded: Ckp SCN: 651119 Ckp time:23-MAY-12 SPFILE Included:Modification time: 23-MAY-12 如下备份的是控制文件信息：BS Key Type LVSize D

37、evice Type Elapsed TimeCompletion Time - - - - - -14 Full 6.77M DISK 00:00:01 23-MAY-12 BP Key:14 Status: AVAILABLE Compressed: NO Tag: TAG20120523T133645 Piece Name:/DBBak/bak_WWL_05_23_0gnbn2je_1_1 Control FileIncluded: Ckp SCN: 651150 Ckp time:23-MAY-12 BS Key Type LVSize Device Type Elapsed Time

38、Completion Time - - - - - -15 Full 6.80M DISK 00:00:01 23-MAY-12 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 15 页 - - - - - - - - - BP Key:15 Status: AVAILABLE Compressed: NO Tag: TAG20120523T133648 Piece Name:/DBSoft/product/10.2.0/db_1/dbs/c-5520179-201205

39、23-03 Control FileIncluded: Ckp SCN: 651154 Ckp time:23-MAY-12 SPFILE Included:Modification time: 23-MAY-12 如下是备份spfile 文件的信息：BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -16 Full 80.00K DISK 00:00:01 23-MAY-12 BP Key:16 Status: AVAILABLE Compressed: NO Tag: TAG20120523T13373

40、7 Piece Name:/DBBak/bak_WWL_05_23_0inbn2l2_1_1 SPFILE Included:Modification time: 23-MAY-12 BS Key Type LVSize Device Type Elapsed TimeCompletion Time - - - - - -17 Full 6.80M DISK 00:00:02 23-MAY-12 BP Key:17 Status: AVAILABLE Compressed: NO Tag: TAG20120523T133740 Piece Name:/DBSoft/product/10.2.0

41、/db_1/dbs/c-5520179-20120523-04 Control FileIncluded: Ckp SCN: 651175 Ckp time:23-MAY-12 SPFILE Included:Modification time: 23-MAY-12 RMAN恢复大综合：介于 Rman 恢复的自动性， 我们刚才对数据库的全库，单表空间， 数据文件控制文件和参数文件都做了备份，这些备份的内容是数据库运行不可缺少的必要元素，通常情况下我们在生产库中只需要备份这些内容即可。有了这些备份，即使数据库的的任何信息丢失，我们都可以得以恢复使得数据库的正常运行，当然如果我们将archive

42、log 也备份，当然就会更好了，一般情况下这个可以不用备份，备份archive log 占用空间非常大。如果有海量的储存，当然备份也无妨咯。Egg:2.1 删除数据库的所有文件，仅保留redo 日志和 archivelog 文件删除控制文件：oraclewwldb WWL$ pwd/DBData/oradata/WWLoraclewwldb WWL$ lscontrol02.ctl control03.ctl名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 15 页 - -

43、 - - - - - - - oraclewwldb WWL$ rm -rf *.ctloraclewwldbWWL$ oraclewwldb WWL$ pwd/DBSoft/oradata/WWLoraclewwldb WWL$ lscontrol01.ctloraclewwldb WWL$ rm -rf control01.ctloraclewwldbWWL$删除数据文件：oraclewwldb WWL$ lsredo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01

44、.dbforaclewwldb WWL$ rm -rf *.dbforaclewwldb WWL$ lsredo01.log redo02.log redo03.logoraclewwldbWWL$ 删除参数文件：oraclewwldb dbs$ cd $ORACLE_HOME/dbsoraclewwldb dbs$ rm -rf spfileWWL.oraoraclewwldbdbs$执行大恢复1、现在数据库是关闭状态oraclewwldb dbs$ ps -ef|grep oraroot 2910 2888 0 10:02 ? 00:00:15 hald-addon-storage: po

45、lling/dev/hdcroot 28954 3478 0 11:38 pts/1 00:00:00 su - oracleoracle 28955 28954 0 11:38 pts/1 00:00:00 -bashroot 29361 3438 0 13:41 pts/2 00:00:00 su - oracleoracle 29362 29361 0 13:41 pts/2 00:00:01 -bashoracle 29988 29362 0 16:06 pts/2 00:00:00sqlplus as sysdbaoracle 30102 28955 0 17:03 pts/1 00

46、:00:00 ps -eforacle 30103 28955 0 17:03 pts/1 00:00:00 grep oraoraclewwldbdbs$2、要恢复首先要将数据库启动到mount 状态才能恢复oraclewwldb dbs$ sqlplus / as sysdbaSQL*Plus:Release 10.2.0.1.0 - Production on Wed May 23 17:03:42 2012名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 15 页

47、 - - - - - - - - - Copyright (c)1982, 2005, Oracle. All rights reserved.Connected to anidle instance.SQL startupORA-01078:failure in processing system parametersLRM-00109: couldnot open parameter file /DBSoft/product/10.2.0/db_1/dbs/initWWL.oraSQL3、因为数据库没有参数文件，起不来，只能能启动到nomount 状态oraclewwldb dbs$ rm

48、an target /RecoveryManager: Release 10.2.0.1.0 - Production on Wed May 23 17:06:32 2012Copyright (c)1982, 2005, Oracle. All rights reserved.connected totarget database (not started)RMAN startup force nomount;startup failed:ORA-01078: failure in processing system parametersLRM-00109: couldnot open pa

49、rameter file /DBSoft/product/10.2.0/db_1/dbs/initWWL.orastarting Oracleinstance without parameter file for retrival of spfileOracle instancestartedTotal System GlobalArea 159383552 bytesFixed Size 1218244 bytesVariableSize 58722620 bytesDatabaseBuffers 92274688 bytesRedoBuffers 7168000 bytesRMAN4、开始

50、恢复参数文件，只有恢复了参数文件和控制文件数据库才能到mount 状态来恢复数据文件：RMAN restorespfile from autobackup;Starting restoreat 23-MAY-12using targetdatabase control file instead of recovery catalogallocatedchannel: ORA_DISK_1channelORA_DISK_1: sid=36 devtype=DISKRMAN-00571:=RMAN-00569:= ERROR MESSAGE STACK FOLLOWS =名师资料总结 - - -精