Oracle 数据库灾难性环境下恢复实例
所谓灾难性是指磁盘故障或其它原因导致数据库相关的文件全部丢失或损坏无法进行修复,必须通过备份进行还原恢复;以下实验模拟丢失所有数据库文件,然后通过备份进行还原恢复。版本和数据库文件信息1. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0- 64bit Production2. PL/SQL Release 11.2.0.3.0 - Production3. CORE 11.2.0.3.0 Production4. TNS for Linux: Version 11.2.0.3.0 - Production5. NLSRTL Version 11.2.0.3.0 - Production6. 7. SQL> column name format a508. SQL> select file#,status,name from v$datafile;9. 10. FILE#STATUSNAME11.---------- ---------------------------------------------------------12. 1SYSTEM/u01/oradata/sydb/system01.dbf13. 2ONLINE/u01/oradata/sydb/sysaux01.dbf14. 3ONLINE/u01/oradata/sydb/undotbs01.dbf15. 4 ONLINE/u01/oradata/sydb/users01.dbf16. 5 ONLINE/u01/oradata/sydb/tbs01.dbf17. 18.SQL> columnmember format a5019.SQL> select *from v$Logfile;20. 21. GROUP# STATUSTYPE MEMBER IS_22.---------- -------------- -------------------------------------------------- ---23. 1 ONLINE/u01/oradata/sydb/REDO01.LOG NO24. 2 ONLINE/u01/oradata/sydb/REDO02.LOG NO25. 26.SQL> select *from v$controlfile;27. 28.STATUSNAME IS_ BLOCK_SIZE FILE_SIZE_BLKS29.--------------------------------------------------------- --- ------------------------30. /u01/oradata/sydb/control01.ctl NO 16384 668 备份数据库注意:备份数据库时如果配置了 configure exclude for tablespacetbsname 的表空间是不会进行备份的,恢复表空间备份configure exclude fortablespacetbsname clear;如果没有设置自动备份控制文件和参数文件,要在备份脚本最后手动添加备份控制文件脚本;设置自动备份控制文件和参数文件:1. CONFIGURE CONTROLFILE AUTOBACKUP On;2. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPEDISK TO '/u01/backup/%F_%d_controlfile.bkp';控制文件名格式必须包含%F,%F的格式为:c-IIIIIIIIII-YYYYMMDD-QQ,IIIIIIIIII(10位)代表DBID发生灾难时可以通过它知道数据库ID,YYYYMMDD代表自动备份时的timestamp,QQ代表是十六进制的序列号,起始值为:00,最大值为:FF。 开始备份数据1. run2. {3. allocate channel dev type disk;4. allocate channel dev2 type disk;5. backup incremental level 0 database plus archivelogdelete input6. tag 'sydb_incr_level0'7. format '/u01/backup/%d_%s_%U';8. release channel dev;9. release channel dev2;10.}11. 12.allocated channel:dev13.channel de"EN-US" style="mso-bidi-font-size:14px;font-family:"Tahoma","sans-serif";mso-fareast-font-family:宋体;color:#444444;mso-font-kerning:0">14. 15.allocated channel:dev216.channel dev2:SID=18 device type=DISK17. 18. 19.Starting backup at29-MAY-1520.current logarchived21.channel dev:starting archived log backup set22.channel dev:specifying archived log(s) in backup set23.input archived logthread=1 sequence=17 RECID=1 STAMP=88099400724.channel dev:starting piece 1 at 29-MAY-1525.channel dev2:starting archived log backup set26.channel dev2:specifying archived log(s) in backup set27.input archived logthread=1 sequence=18 RECID=2 STAMP=88099401628.input archived logthread=1 sequence=19 RECID=3 STAMP=88099431129.channel dev2:starting piece 1 at 29-MAY-1530.channel dev:finished piece 1 at 29-MAY-1531.piecehandle=/u01/backup/SYDB_1_01q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE32.channel dev:backup set complete, elapsed time: 00:00:0733.channel dev:deleting archived log(s)34.archived log filename=/u01/app/product/11.2.3/db_1/dbs/arch1_17_880905808.dbf RECID=1STAMP=88099400735.channel dev2:finished piece 1 at 29-MAY-1536.piecehandle=/u01/backup/SYDB_2_02q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE37.channel dev2:backup set complete, elapsed time: 00:00:0838.channel dev2:deleting archived log(s)39.archived log filename=/u01/app/product/11.2.3/db_1/dbs/arch1_18_880905808.dbf RECID=2STAMP=88099401640.archived log filename=/u01/app/product/11.2.3/db_1/dbs/arch1_19_880905808.dbf RECID=3STAMP=88099431141.Finished backup at29-MAY-1542. 43.Starting backup at29-MAY-1544.channel dev:starting incremental level 0 datafile backup set45.channel dev:specifying datafile(s) in backup set46.input datafilefile number=00001 name=/u01/oradata/sydb/system01.dbf47.input datafilefile number=00004 name=/u01/oradata/sydb/users01.dbf48.input datafilefile number=00005 name=/u01/oradata/sydb/tbs01.dbf49.channel dev:starting piece 1 at 29-MAY-1550.channel dev2:starting incremental level 0 datafile backup set51.channel dev2:specifying datafile(s) in backup set52.input datafilefile number=00003 name=/u01/oradata/sydb/undotbs01.dbf53.input datafilefile number=00002 name=/u01/oradata/sydb/sysaux01.dbf54.channel dev2:starting piece 1 at 29-MAY-1555.channel dev:finished piece 1 at 29-MAY-1556.piecehandle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839comment=NONE57.channel dev:backup set complete, elapsed time: 00:00:3558.channel dev2:finished piece 1 at 29-MAY-1559.piecehandle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839comment=NONE60.channel dev2:backup set complete, elapsed time: 00:00:3561.Finished backup at29-MAY-1562. 63.Starting backup at29-MAY-1564.current logarchived65.channel dev:starting archived log backup set66.channel dev:specifying archived log(s) in backup set67.input archived logthread=1 sequence=20 RECID=4 STAMP=88099435468.channel dev:starting piece 1 at 29-MAY-1569.channel dev:finished piece 1 at 29-MAY-1570.piece handle=/u01/backup/SYDB_5_05q85q1i_1_1tag=SYDB_INCR_LEVEL0 comment=NONE71.channel dev:backup set complete, elapsed time: 00:00:0172.channel dev:deleting archived log(s)73.archived log filename=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf RECID=4 STAMP=88099435474.Finished backup at29-MAY-1575. 76.Starting ControlFile and SPFILE Autobackup at 29-MAY-1577.piecehandle=/u01/backup/c-3634177744-20150529-00_control.bkp comment=NONE78.Finished ControlFile and SPFILE Autobackup at 29-MAY-1579. 80.released channel:dev81. 82.released channel:dev2 通过备份日志可以知道哪些数据文件被备份,哪些没有被备份或没有备份成功,比如:发现较多数据块损坏、表空间被排除备份(exclude)、表空间被skip;关注这些信息非常重要,因为一旦灾难发生,它意味着你的数据库恢复成功率,也有助于对备份脚本的优化和调整。验证数据库可恢复性验证可恢复性可以发现一些忽略的问题,及时处理;1. rm /u01/oradata/sydb/system01.dbf2. rm /u01/oradata/sydb/sysaux01.dbf3. rm /u01/oradata/sydb/undotbs01.dbf4. rm /u01/oradata/sydb/tbs01.dbf5. rm /u01/oradata/sydb/control01.ctl6. rm /u01/oradata/sydb/REDO01.LOG7. rm /u01/oradata/sydb/REDO02.LOG8. rm /u01/app/product/11.2.3/db_1/dbs/spfilesydb.ora 数据库恢复恢复参数文件和控制文件数据库启动时会先查找spfile,然后在找spfile.ora,再找文件initpfile,如果这些文件都没有找到报错并停止启动;so 先创建一个基本的initpfile;1. $ vim /u01/app/product/11.2.3/db_1/dbs/initsydb.ora2. db_name='sydb'3. memory_target=200m4. control_files='/u01/oradata/sydb/control01.ctl'5. db_block_size=32768 如果你的数据块大小为非8kb,你又想在恢复控制文件后完成其它操作,如mount控制文件,则你必须在init 文件中指定数据块大小;1. SQL> startup nomount2. ORACLE instance started.3. 4. Total System Global Area208769024 bytes5. Fixed Size 2226936 bytes6. Variable Size 109053192 bytes7. Database Buffers 92274688bytes8. Redo Buffers 5214208 bytes9. 10.$ rman target /11.RMAN> restorespfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';12. 13.Starting restoreat 29-MAY-1514.using channelORA_DISK_115. 16.channelORA_DISK_1: restoring spfile from AUTOBACKUP/u01/backup/c-3634177744-20150529-00_control.bkp17.channelORA_DISK_1: SPFILE restore from AUTOBACKUP complete18.Finished restoreat 29-MAY-1519. 20.RMAN> restorecontrolfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';21. 22.Starting restoreat 29-MAY-1523.using channelORA_DISK_124. 25.channelORA_DISK_1: restoring control file26.channelORA_DISK_1: restore complete, elapsed time: 00:00:0127.output filename=/u01/oradata/sydb/control01.ctl28.Finished restoreat 29-MAY-1529.RMAN> alterdatabase mount;30. 31.database mounted32.released channel:ORA_DISK_1 查看备份文件和确定可恢复的最大归档日志序列注:如果目录名发生改变或文件发生位置移动,先使用 catalog start with '/dir' 将文件catalog;1. RMAN> list backup of database;2. 3. 4. List of Backup Sets5. ===================6. 7. 8. BS KeyType LV Size Device Type Elapsed Time Completion Time9. ------- ---- -- ---------- ----------- ---------------------------10.3 Incr 0180.53M DISK 00:00:29 29-MAY-15 11. BP Key: 3 Status:AVAILABLECompressed: NOTag: TAG20150529T16383912. Piece Name: /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_113. Listof Datafiles in backup set 314. FileLV Type Ckp SCN Ckp TimeName15. ------ ---- ---------- --------- ----16. 2 0Incr 436655 29-MAY-15/u01/oradata/sydb/sysaux01.dbf17. 3 0Incr 436655 29-MAY-15/u01/oradata/sydb/undotbs01.dbf18. 19.BSKeyType LV Size Device Type Elapsed TimeCompletion Time20.------- ---- ------------ ----------- ------------ ---------------21.4 Incr 0380.94M DISK 00:00:29 29-MAY-15 22. BP Key: 4 Status:AVAILABLECompressed: NOTag: TAG20150529T16383923. Piece Name:/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_124. Listof Datafiles in backup set 425. FileLV Type Ckp SCN Ckp TimeName26. ------ ---- ---------- --------- ----27. 1 0Incr 436654 29-MAY-15/u01/oradata/sydb/system01.dbf28. 4 0Incr 436654 29-MAY-15/u01/oradata/sydb/users01.dbf29. 5 0Incr 436654 29-MAY-15/u01/oradata/sydb/tbs01.dbf30. 31.RMAN> listbackup of archivelog all;32. 33. 34.List of BackupSets35.===================36. 37. 38.BSKeySize Device Type Elapsed TimeCompletion Time39.------- --------------------- ------------ ---------------40.1 45.49M DISK 00:00:04 29-MAY-15 41. BP Key: 1 Status:AVAILABLECompressed: NOTag: SYDB_INCR_LEVEL042. Piece Name: /u01/backup/SYDB_1_01q85q07_1_143. 44. Listof Archived Logs in backup set 145. ThrdSeq Low SCN Low TimeNext SCNNext Time46. ----------- ---------- --------- ---------- ---------47. 1 17 427739 29-MAY-15436110 29-MAY-1548. 49.BSKeySize Device Type Elapsed TimeCompletion Time50.------- --------------------- ------------ ---------------51.2 43.37M DISK 00:00:04 29-MAY-15 52. BP Key: 2 Status:AVAILABLECompressed: NOTag: SYDB_INCR_LEVEL053. Piece Name: /u01/backup/SYDB_2_02q85q07_1_154. 55. Listof Archived Logs in backup set 256. ThrdSeq Low SCN Low TimeNext SCNNext Time57. ----------- ---------- --------- ---------- ---------58. 1 18 436110 29-MAY-15436484 29-MAY-1559. 1 19 436484 29-MAY-15436643 29-MAY-1560. 61.BSKeySize Device Type Elapsed TimeCompletion Time62.------- --------------------- ------------ ---------------63.5 90.00K DISK 00:00:00 29-MAY-15 64. BP Key: 5 Status:AVAILABLECompressed: NOTag: SYDB_INCR_LEVEL065. Piece Name: /u01/backup/SYDB_5_05q85q1i_1_166. 67. Listof Archived Logs in backup set 568. ThrdSeq Low SCN Low TimeNext SCNNext Time69. ----------- ---------- --------- ---------- ---------70. 1 20 436643 29-MAY-15436756 29-MAY-15
从上面的信息中可以看出最大可恢复到的日志序列为20,恢复时设置until sequence 21,数据库即可应用所有的归档日志;1. RMAN> restore database until sequence 21;2. 3. Starting restore at 29-MAY-154. using channel ORA_DISK_15. 6. channel ORA_DISK_1: starting datafile backup set restore7. channel ORA_DISK_1: specifying datafile(s) to restorefrom backup set8. channel ORA_DISK_1: restoring datafile 00001 to/u01/oradata/sydb/system01.dbf9. channel ORA_DISK_1: restoring datafile 00004 to/u01/oradata/sydb/users01.dbf10.channelORA_DISK_1: restoring datafile 00005 to /u01/oradata/sydb/tbs01.dbf11.channelORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_112.channelORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1tag=TAG20150529T16383913.channelORA_DISK_1: restored backup piece 114.channelORA_DISK_1: restore complete, elapsed time: 00:00:2515.channelORA_DISK_1: starting datafile backup set restore16.channelORA_DISK_1: specifying datafile(s) to restore from backup set17.channelORA_DISK_1: restoring datafile 00002 to /u01/oradata/sydb/sysaux01.dbf18.channelORA_DISK_1: restoring datafile 00003 to /u01/oradata/sydb/undotbs01.dbf19.channelORA_DISK_1: reading from backup piece/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_120.channelORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1tag=TAG20150529T16383921.channelORA_DISK_1: restored backup piece 122.channelORA_DISK_1: restore complete, elapsed time: 00:00:3523.Finished restoreat 29-MAY-1524. 25.RMAN> recoverdatabase until sequence 21;26. 27.Starting recoverat 29-MAY-1528.using channelORA_DISK_129. 30.starting mediarecovery31. 32.channelORA_DISK_1: starting archived log restore to default destination33.channelORA_DISK_1: restoring archived log34.archived logthread=1 sequence=2035.channelORA_DISK_1: reading from backup piece /u01/backup/SYDB_5_05q85q1i_1_136.channelORA_DISK_1: piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL037.channelORA_DISK_1: restored backup piece 138.channelORA_DISK_1: restore complete, elapsed time: 00:00:0139.archived log filename=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf thread=1 sequence=2040.media recoverycomplete, elapsed time: 00:00:0141.Finished recoverat 29-MAY-15 使用resetlogs 方式打开数据库1. SYS@sydb>select file#,d.name as"FILE_NAME",t.name as"TABLESPACE_NAME",status,enabled,checkpoint_change#,checkpoint_time,(bytes/1024/1024)"BYTES_MB",blocks,2. 2(create_bytes/1024/1024)create_bytes_mb,block_size3. 3from v$datafile d left joinv$tablespace t4. 4on d.ts#=t.ts#;5. 6. FILE# FILE_NAME TABLESPACE_NAME STATUSENABLEDCHECKPOINT_CHANGE#CHECKPOINBYTES_MBBLOCKS CREATE_BYTES_MB BLOCK_SIZE7. ------------------------------ ------- ---------------------------- --------- ---------- ---------- --------------- ----------8. 1/u01/oradata/sydb/system01.dbfSYSTEM SYSTEMREAD WRITE436756 29-MAY-15 400.812512826 100 327689. 2/u01/oradata/sydb/sysaux01.dbfSYSAUX ONLINEREAD WRITE 436756 29-MAY-15 227.6875 7286 100 3276810.3/u01/oradata/sydb/undotbs01.dbfUNDOTBS01ONLINEREAD WRITE 436756 29-MAY-15 310 9920 100 3276811.4/u01/oradata/sydb/users01.dbf USERS ONLINEREAD WRITE 436756 29-MAY-15 100 3200 100 3276812.5/u01/oradata/sydb/tbs01.dbf TBS01 ONLINEREAD WRITE436756 29-MAY-15 98 3136 10 3276813. 14.Elapsed:00:00:00.0215.SYS@sydb>alterdatabase open resetlogs;16. 17.Database altered.18. 19.Elapsed:00:00:07.41 总结任何时候数据库的还原和恢复因环境和错误的不一而不同,认真有效的阅读文档,理解工作原理,然后在加上重复的实验方能起到良好的效果,实验本身是动手实践的步骤,但同时也是理论实践结合的强有效方式。来源：微信公众号 技术为王
{:6_267:}{:6_267:}{:6_267:}
页:
[1]