|
所谓灾难性是指磁盘故障或其它原因导致数据库相关的文件全部丢失或损坏无法进行修复,必须通过备份进行还原恢复;以下实验模拟丢失所有数据库文件,然后通过备份进行还原恢复。 版本和数据库文件信息 1. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0- 64bit Production 2. PL/SQL Release 11.2.0.3.0 - Production 3. CORE 11.2.0.3.0 Production 4. TNS for Linux: Version 11.2.0.3.0 - Production 5. NLSRTL Version 11.2.0.3.0 - Production 6. 7. SQL> column name format a50 8. SQL> select file#,status,name from v$datafile; 9. 10. FILE#STATUS NAME 11. ---------- --------------------------------------------------------- 12. 1SYSTEM /u01/oradata/sydb/system01.dbf 13. 2ONLINE /u01/oradata/sydb/sysaux01.dbf 14. 3ONLINE /u01/oradata/sydb/undotbs01.dbf 15. 4 ONLINE /u01/oradata/sydb/users01.dbf 16. 5 ONLINE /u01/oradata/sydb/tbs01.dbf 17. 18. SQL> columnmember format a50 19. SQL> select *from v$Logfile; 20. 21. GROUP# STATUS TYPE MEMBER IS_ 22. ---------- -------------- -------------------------------------------------- --- 23. 1 ONLINE /u01/oradata/sydb/REDO01.LOG NO 24. 2 ONLINE /u01/oradata/sydb/REDO02.LOG NO 25. 26. SQL> select *from v$controlfile; 27. 28. STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS 29. --------------------------------------------------------- --- ------------------------ 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. run 2. { 3. allocate channel dev type disk; 4. allocate channel dev2 type disk; 5. backup incremental level 0 database plus archivelogdelete input 6. tag 'sydb_incr_level0' 7. format '/u01/backup/%d_%s_%U'; 8. release channel dev; 9. release channel dev2; 10. } 11. 12. allocated channel:dev 13. 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:dev2 16. channel dev2:SID=18 device type=DISK 17. 18. 19. Starting backup at29-MAY-15 20. current logarchived 21. channel dev:starting archived log backup set 22. channel dev:specifying archived log(s) in backup set 23. input archived logthread=1 sequence=17 RECID=1 STAMP=880994007 24. channel dev:starting piece 1 at 29-MAY-15 25. channel dev2:starting archived log backup set 26. channel dev2:specifying archived log(s) in backup set 27. input archived logthread=1 sequence=18 RECID=2 STAMP=880994016 28. input archived logthread=1 sequence=19 RECID=3 STAMP=880994311 29. channel dev2:starting piece 1 at 29-MAY-15 30. channel dev:finished piece 1 at 29-MAY-15 31. piecehandle=/u01/backup/SYDB_1_01q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE 32. channel dev:backup set complete, elapsed time: 00:00:07 33. 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=880994007 35. channel dev2:finished piece 1 at 29-MAY-15 36. piecehandle=/u01/backup/SYDB_2_02q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE 37. channel dev2:backup set complete, elapsed time: 00:00:08 38. 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=880994016 40. archived log filename=/u01/app/product/11.2.3/db_1/dbs/arch1_19_880905808.dbf RECID=3STAMP=880994311 41. Finished backup at29-MAY-15 42. 43. Starting backup at29-MAY-15 44. channel dev:starting incremental level 0 datafile backup set 45. channel dev:specifying datafile(s) in backup set 46. input datafilefile number=00001 name=/u01/oradata/sydb/system01.dbf 47. input datafilefile number=00004 name=/u01/oradata/sydb/users01.dbf 48. input datafilefile number=00005 name=/u01/oradata/sydb/tbs01.dbf 49. channel dev:starting piece 1 at 29-MAY-15 50. channel dev2:starting incremental level 0 datafile backup set 51. channel dev2:specifying datafile(s) in backup set 52. input datafilefile number=00003 name=/u01/oradata/sydb/undotbs01.dbf 53. input datafilefile number=00002 name=/u01/oradata/sydb/sysaux01.dbf 54. channel dev2:starting piece 1 at 29-MAY-15 55. channel dev:finished piece 1 at 29-MAY-15 56. piecehandle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839comment=NONE 57. channel dev:backup set complete, elapsed time: 00:00:35 58. channel dev2:finished piece 1 at 29-MAY-15 59. piecehandle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839comment=NONE 60. channel dev2:backup set complete, elapsed time: 00:00:35 61. Finished backup at29-MAY-15 62. 63. Starting backup at29-MAY-15 64. current logarchived 65. channel dev:starting archived log backup set 66. channel dev:specifying archived log(s) in backup set 67. input archived logthread=1 sequence=20 RECID=4 STAMP=880994354 68. channel dev:starting piece 1 at 29-MAY-15 69. channel dev:finished piece 1 at 29-MAY-15 70. piece handle=/u01/backup/SYDB_5_05q85q1i_1_1tag=SYDB_INCR_LEVEL0 comment=NONE 71. channel dev:backup set complete, elapsed time: 00:00:01 72. 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=880994354 74. Finished backup at29-MAY-15 75. 76. Starting ControlFile and SPFILE Autobackup at 29-MAY-15 77. piecehandle=/u01/backup/c-3634177744-20150529-00_control.bkp comment=NONE 78. Finished ControlFile and SPFILE Autobackup at 29-MAY-15 79. 80. released channel:dev 81. 82. released channel:dev2 通过备份日志可以知道哪些数据文件被备份,哪些没有被备份或没有备份成功,比如:发现较多数据块损坏、表空间被排除备份(exclude)、表空间被skip;关注这些信息非常重要,因为一旦灾难发生,它意味着你的数据库恢复成功率,也有助于对备份脚本的优化和调整。
验证数据库可恢复性 验证可恢复性可以发现一些忽略的问题,及时处理; 1. rm /u01/oradata/sydb/system01.dbf 2. rm /u01/oradata/sydb/sysaux01.dbf 3. rm /u01/oradata/sydb/undotbs01.dbf 4. rm /u01/oradata/sydb/tbs01.dbf 5. rm /u01/oradata/sydb/control01.ctl 6. rm /u01/oradata/sydb/REDO01.LOG 7. rm /u01/oradata/sydb/REDO02.LOG 8. 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.ora 2. db_name='sydb' 3. memory_target=200m 4. control_files='/u01/oradata/sydb/control01.ctl' 5. db_block_size=32768 如果你的数据块大小为非8kb,你又想在恢复控制文件后完成其它操作,如mount控制文件,则你必须在init 文件中指定数据块大小; 1. SQL> startup nomount 2. ORACLE instance started. 3. 4. Total System Global Area 208769024 bytes 5. Fixed Size 2226936 bytes 6. Variable Size 109053192 bytes 7. Database Buffers 92274688bytes 8. Redo Buffers 5214208 bytes 9. 10. $ rman target / 11. RMAN> restorespfile from '/u01/backup/c-3634177744-20150529-00_control.bkp'; 12. 13. Starting restoreat 29-MAY-15 14. using channelORA_DISK_1 15. 16. channelORA_DISK_1: restoring spfile from AUTOBACKUP/u01/backup/c-3634177744-20150529-00_control.bkp 17. channelORA_DISK_1: SPFILE restore from AUTOBACKUP complete 18. Finished restoreat 29-MAY-15 19. 20. RMAN> restorecontrolfile from '/u01/backup/c-3634177744-20150529-00_control.bkp'; 21. 22. Starting restoreat 29-MAY-15 23. using channelORA_DISK_1 24. 25. channelORA_DISK_1: restoring control file 26. channelORA_DISK_1: restore complete, elapsed time: 00:00:01 27. output filename=/u01/oradata/sydb/control01.ctl 28. Finished restoreat 29-MAY-15 29. RMAN> alterdatabase mount; 30. 31. database mounted 32. released channel:ORA_DISK_1 查看备份文件和确定可恢复的最大归档日志序列 注:如果目录名发生改变或文件发生位置移动,先使用 catalog start with '/dir' 将文件catalog; 1. RMAN> list backup of database; 2. 3. 4. List of Backup Sets 5. =================== 6. 7. 8. BS Key Type LV Size Device Type Elapsed Time Completion Time 9. ------- ---- -- ---------- ----------- --------------------------- 10. 3 Incr 0 180.53M DISK 00:00:29 29-MAY-15 11. BP Key: 3 Status:AVAILABLE Compressed: NO Tag: TAG20150529T163839 12. Piece Name: /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 13. Listof Datafiles in backup set 3 14. FileLV Type Ckp SCN Ckp Time Name 15. ------ ---- ---------- --------- ---- 16. 2 0 Incr 436655 29-MAY-15/u01/oradata/sydb/sysaux01.dbf 17. 3 0 Incr 436655 29-MAY-15/u01/oradata/sydb/undotbs01.dbf 18. 19. BSKey Type LV Size Device Type Elapsed TimeCompletion Time 20. ------- ---- ------------ ----------- ------------ --------------- 21. 4 Incr 0 380.94M DISK 00:00:29 29-MAY-15 22. BP Key: 4 Status:AVAILABLE Compressed: NO Tag: TAG20150529T163839 23. Piece Name:/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 24. Listof Datafiles in backup set 4 25. FileLV Type Ckp SCN Ckp Time Name 26. ------ ---- ---------- --------- ---- 27. 1 0 Incr 436654 29-MAY-15/u01/oradata/sydb/system01.dbf 28. 4 0 Incr 436654 29-MAY-15/u01/oradata/sydb/users01.dbf 29. 5 0 Incr 436654 29-MAY-15/u01/oradata/sydb/tbs01.dbf 30. 31. RMAN> listbackup of archivelog all; 32. 33. 34. List of BackupSets 35. =================== 36. 37. 38. BSKey Size Device Type Elapsed TimeCompletion Time 39. ------- --------------------- ------------ --------------- 40. 1 45.49M DISK 00:00:04 29-MAY-15 41. BP Key: 1 Status:AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0 42. Piece Name: /u01/backup/SYDB_1_01q85q07_1_1 43. 44. Listof Archived Logs in backup set 1 45. ThrdSeq Low SCN Low Time Next SCN Next Time 46. ----------- ---------- --------- ---------- --------- 47. 1 17 427739 29-MAY-15436110 29-MAY-15 48. 49. BSKey Size Device Type Elapsed TimeCompletion Time 50. ------- --------------------- ------------ --------------- 51. 2 43.37M DISK 00:00:04 29-MAY-15 52. BP Key: 2 Status:AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0 53. Piece Name: /u01/backup/SYDB_2_02q85q07_1_1 54. 55. Listof Archived Logs in backup set 2 56. ThrdSeq Low SCN Low Time Next SCN Next Time 57. ----------- ---------- --------- ---------- --------- 58. 1 18 436110 29-MAY-15436484 29-MAY-15 59. 1 19 436484 29-MAY-15436643 29-MAY-15 60. 61. BSKey Size Device Type Elapsed TimeCompletion Time 62. ------- --------------------- ------------ --------------- 63. 5 90.00K DISK 00:00:00 29-MAY-15 64. BP Key: 5 Status:AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0 65. Piece Name: /u01/backup/SYDB_5_05q85q1i_1_1 66. 67. Listof Archived Logs in backup set 5 68. ThrdSeq Low SCN Low Time Next SCN Next Time 69. ----------- ---------- --------- ---------- --------- 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-15 4. using channel ORA_DISK_1 5. 6. channel ORA_DISK_1: starting datafile backup set restore 7. channel ORA_DISK_1: specifying datafile(s) to restorefrom backup set 8. channel ORA_DISK_1: restoring datafile 00001 to/u01/oradata/sydb/system01.dbf 9. channel ORA_DISK_1: restoring datafile 00004 to/u01/oradata/sydb/users01.dbf 10. channelORA_DISK_1: restoring datafile 00005 to /u01/oradata/sydb/tbs01.dbf 11. channelORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 12. channelORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1tag=TAG20150529T163839 13. channelORA_DISK_1: restored backup piece 1 14. channelORA_DISK_1: restore complete, elapsed time: 00:00:25 15. channelORA_DISK_1: starting datafile backup set restore 16. channelORA_DISK_1: specifying datafile(s) to restore from backup set 17. channelORA_DISK_1: restoring datafile 00002 to /u01/oradata/sydb/sysaux01.dbf 18. channelORA_DISK_1: restoring datafile 00003 to /u01/oradata/sydb/undotbs01.dbf 19. channelORA_DISK_1: reading from backup piece/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 20. channelORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1tag=TAG20150529T163839 21. channelORA_DISK_1: restored backup piece 1 22. channelORA_DISK_1: restore complete, elapsed time: 00:00:35 23. Finished restoreat 29-MAY-15 24. 25. RMAN> recoverdatabase until sequence 21; 26. 27. Starting recoverat 29-MAY-15 28. using channelORA_DISK_1 29. 30. starting mediarecovery 31. 32. channelORA_DISK_1: starting archived log restore to default destination 33. channelORA_DISK_1: restoring archived log 34. archived logthread=1 sequence=20 35. channelORA_DISK_1: reading from backup piece /u01/backup/SYDB_5_05q85q1i_1_1 36. channelORA_DISK_1: piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0 37. channelORA_DISK_1: restored backup piece 1 38. channelORA_DISK_1: restore complete, elapsed time: 00:00:01 39. archived log filename=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf thread=1 sequence=20 40. media recoverycomplete, elapsed time: 00:00:01 41. 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_size 3. 3 from v$datafile d left joinv$tablespace t 4. 4 on d.ts#=t.ts#; 5. 6. FILE# FILE_NAME TABLESPACE_NAME STATUS ENABLED CHECKPOINT_CHANGE#CHECKPOIN BYTES_MB BLOCKS CREATE_BYTES_MB BLOCK_SIZE 7. ------------------------------ ------- ---------------------------- --------- ---------- ---------- --------------- ---------- 8. 1 /u01/oradata/sydb/system01.dbf SYSTEM SYSTEM READ WRITE 436756 29-MAY-15 400.8125 12826 100 32768 9. 2 /u01/oradata/sydb/sysaux01.dbf SYSAUX ONLINE READ WRITE 436756 29-MAY-15 227.6875 7286 100 32768 10. 3 /u01/oradata/sydb/undotbs01.dbfUNDOTBS01 ONLINE READ WRITE 436756 29-MAY-15 310 9920 100 32768 11. 4 /u01/oradata/sydb/users01.dbf USERS ONLINE READ WRITE 436756 29-MAY-15 100 3200 100 32768 12. 5 /u01/oradata/sydb/tbs01.dbf TBS01 ONLINE READ WRITE436756 29-MAY-15 98 3136 10 32768 13. 14. Elapsed:00:00:00.02 15. SYS@sydb>alterdatabase open resetlogs; 16. 17. Database altered. 18. 19. Elapsed:00:00:07.41 总结 任何时候数据库的还原和恢复因环境和错误的不一而不同,认真有效的阅读文档,理解工作原理,然后在加上重复的实验方能起到良好的效果,实验本身是动手实践的步骤,但同时也是理论实践结合的强有效方式。 来源：微信公众号 技术为王
| 
简体中文
英语
日语
韩语
法语
西班牙语
越南语
泰语
阿拉伯语
俄语
葡萄牙语
德语
意大利语
希腊语
荷兰语
波兰语
保加利亚语
爱沙尼亚语
丹麦语
芬兰语
捷克语
罗马尼亚语
斯洛文尼亚语
瑞典语
匈牙利语
繁体中文
文言文
发表于 2016-8-16 15:10:21
置顶卡
喧嚣卡
变色卡
千斤顶
楼主
