一、版本和数据库文件信息
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.1 SYSTEM /u01/oradata/sydb/system01.dbf 13.2 ONLINE /u01/oradata/sydb/sysaux01.dbf 14.3 ONLINE /u01/oradata/sydb/undotbs01.dbf 15.4 ONLINE /u01/oradata/sydb/users01.dbf 16.5 ONLINE /u01/oradata/sydb/tbs01.dbf 17. 18.SQL> column member 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 fortablespace tbsname 的表空间是不会进行备份的,恢复表空间备份configure exclude for tablespacetbsname clear;如果没有设置自动备份控制文件和参数文件,要在备份脚本最后手动添加备份控制文件脚本;设置自动备份控制文件和参数文件:
1.CONFIGURE CONTROLFILE AUTOBACKUP On; 2.CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK 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 archivelog delete 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 dev: SID=181 device type=DISK 14. 15.allocated channel: dev2 16.channel dev2: SID=18 device type=DISK 17. 18. 19.Starting backup at 29-MAY-15 20.current log archived 21.channel dev: starting archived log backup set 22.channel dev: specifying archived log(s) in backup set 23.input archived log thread=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 log thread=1 sequence=18 RECID=2 STAMP=880994016 28.input archived log thread=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.piece handle=/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 file name=/u01/app/product/11.2.3/db_1/dbs/arch1_17_880905808.dbf RECID=1 STAMP=880994007 35.channel dev2: finished piece 1 at 29-MAY-15 36.piece handle=/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 file name=/u01/app/product/11.2.3/db_1/dbs/arch1_18_880905808.dbf RECID=2 STAMP=880994016 40.archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_19_880905808.dbf RECID=3 STAMP=880994311 41.Finished backup at 29-MAY-15 42. 43.Starting backup at 29-MAY-15 44.channel dev: starting incremental level 0 datafile backup set 45.channel dev: specifying datafile(s) in backup set 46.input datafile file number=00001 name=/u01/oradata/sydb/system01.dbf 47.input datafile file number=00004 name=/u01/oradata/sydb/users01.dbf 48.input datafile file 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 datafile file number=00003 name=/u01/oradata/sydb/undotbs01.dbf 53.input datafile file 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.piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839 comment=NONE 57.channel dev: backup set complete, elapsed time: 00:00:35 58.channel dev2: finished piece 1 at 29-MAY-15 59.piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839 comment=NONE 60.channel dev2: backup set complete, elapsed time: 00:00:35 61.Finished backup at 29-MAY-15 62. 63.Starting backup at 29-MAY-15 64.current log archived 65.channel dev: starting archived log backup set 66.channel dev: specifying archived log(s) in backup set 67.input archived log thread=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_1 tag=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 file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf RECID=4 STAMP=880994354 74.Finished backup at 29-MAY-15 75. 76.Starting Control File and SPFILE Autobackup at 29-MAY-15 77.piece handle=/u01/backup/c-3634177744-20150529-00_control.bkp comment=NONE 78.Finished Control File 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 先创建一个基本的init pfile;
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 92274688 bytes 8.Redo Buffers 5214208 bytes 9. 10.$ rman target / 11.RMAN> restore spfile from '/u01/backup/c-3634177744-20150529-00_control.bkp'; 12. 13.Starting restore at 29-MAY-15 14.using channel ORA_DISK_1 15. 16.channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/c-3634177744-20150529-00_control.bkp 17.channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete 18.Finished restore at 29-MAY-15 19. 20.RMAN> restore controlfile from '/u01/backup/c-3634177744-20150529-00_control.bkp'; 21. 22.Starting restore at 29-MAY-15 23.using channel ORA_DISK_1 24. 25.channel ORA_DISK_1: restoring control file 26.channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 27.output file name=/u01/oradata/sydb/control01.ctl 28.Finished restore at 29-MAY-15 29.RMAN> alter database 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. List of Datafiles in backup set 3 14. File LV 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.BS Key Type LV Size Device Type Elapsed Time Completion 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.List of Datafiles in backup set 4 25.File LV 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> list backup of archivelog all; 32. 33. 34.List of Backup Sets 35.=================== 36. 37. 38.BS Key Size Device Type Elapsed Time Completion 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. List of Archived Logs in backup set 1 45. Thrd Seq Low SCN Low Time Next SCN Next Time 46. ---- ------- ---------- -------- 47.1 17 427739 29-MAY-15 436110 29-MAY-15 48. 49.BS Key Size Device Type Elapsed Time Completion 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. List of Archived Logs in backup set 2 56. Thrd Seq Low SCN Low Time Next SCN Next Time 57. ---- ------- ---------- ------- 58.1 18 436110 29-MAY-15 436484 29-MAY-15 59.1 19 436484 29-MAY-15 436643 29-MAY-15 60. 61.BS Key Size Device Type Elapsed Time Completion 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.List of Archived Logs in backup set 5 68.Thrd Seq Low SCN Low Time Next SCN Next Time 69. ---- ------- ---------- --------- 70.1 20 436643 29-MAY-15 436756 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 restore from 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.channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/sydb/tbs01.dbf 11.channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 12.channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839 13.channel ORA_DISK_1: restored backup piece 1 14.channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 15.channel ORA_DISK_1: starting datafile backup set restore 16.channel ORA_DISK_1: specifying datafile(s) to restore from backup set 17.channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/sydb/sysaux01.dbf 18.channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/sydb/undotbs01.dbf 19.channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 20.channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839 21.channel ORA_DISK_1: restored backup piece 1 22.channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 23.Finished restore at 29-MAY-15 24. 25.RMAN> recover database until sequence 21; 26. 27.Starting recover at 29-MAY-15 28.using channel ORA_DISK_1 29. 30.starting media recovery 31. 32.channel ORA_DISK_1: starting archived log restore to default destination 33.channel ORA_DISK_1: restoring archived log 34.archived log thread=1 sequence=20 35.channel ORA_DISK_1: reading from backup piece /u01/backup/SYDB_5_05q85q1i_1_1 36.channel ORA_DISK_1: piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0 37.channel ORA_DISK_1: restored backup piece 1 38.channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 39.archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf thread=1 sequence=20 40.media recovery complete, elapsed time: 00:00:01 41.Finished recover at 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 join v$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.dbf UNDOTBS01 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 WRITE 436756 29-MAY-15 98 3136 10 32768 13. 14.Elapsed: 00:00:00.02 15.SYS@sydb>alter database open resetlogs; 16. 17.Database altered. 18. 19.Elapsed: 00:00:07.41五、总结
任何时候数据库的还原和恢复因环境和错误的不一而不同,认真有效的阅读文档,理解工作原理,然后在加上重复的实验方能起到良好的效果,实验本身是动手实践的步骤,但同时也是理论实践结合的强有效方式。
本文来自云栖社区合作伙伴“DBGEEK”
