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# STATUS  NAME11.---------- ------- ------------------12.1 SYSTEM  /u01/oradata/sydb/system01.dbf13.2 ONLINE  /u01/oradata/sydb/sysaux01.dbf14.3 ONLINE  /u01/oradata/sydb/undotbs01.dbf15.4 ONLINE  /u01/oradata/sydb/users01.dbf16.5 ONLINE  /u01/oradata/sydb/tbs01.dbf17.18.SQL> column member format a5019.SQL> select * from v$Logfile;20.21.    GROUP# STATUS  TYPE  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.STATUS  NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS29.---------------------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. run2. {3. allocate channel dev type disk;4. allocate channel dev2 type disk;5. backup incremental level 0 database plus archivelog delete 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 dev: SID=181 device type=DISK14.15.allocated channel: dev216.channel dev2: SID=18 device type=DISK17.18.19.Starting backup at 29-MAY-1520.current log archived21.channel dev: starting archived log backup set22.channel dev: specifying archived log(s) in backup set23.input archived log thread=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 log thread=1 sequence=18 RECID=2 STAMP=88099401628.input archived log thread=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.piece handle=/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 file name=/u01/app/product/11.2.3/db_1/dbs/arch1_17_880905808.dbf RECID=1 STAMP=88099400735.channel dev2: finished piece 1 at 29-MAY-1536.piece handle=/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 file name=/u01/app/product/11.2.3/db_1/dbs/arch1_18_880905808.dbf RECID=2 STAMP=88099401640.archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_19_880905808.dbf RECID=3 STAMP=88099431141.Finished backup at 29-MAY-1542.43.Starting backup at 29-MAY-1544.channel dev: starting incremental level 0 datafile backup set45.channel dev: specifying datafile(s) in backup set46.input datafile file number=00001 name=/u01/oradata/sydb/system01.dbf47.input datafile file number=00004 name=/u01/oradata/sydb/users01.dbf48.input datafile file 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 datafile file number=00003 name=/u01/oradata/sydb/undotbs01.dbf53.input datafile file 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.piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839 comment=NONE57.channel dev: backup set complete, elapsed time: 00:00:3558.channel dev2: finished piece 1 at 29-MAY-1559.piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839 comment=NONE60.channel dev2: backup set complete, elapsed time: 00:00:3561.Finished backup at 29-MAY-1562.63.Starting backup at 29-MAY-1564.current log archived65.channel dev: starting archived log backup set66.channel dev: specifying archived log(s) in backup set67.input archived log thread=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_1 tag=SYDB_INCR_LEVEL0 comment=NONE71.channel dev: backup set complete, elapsed time: 00:00:0172.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=88099435474.Finished backup at 29-MAY-1575.76.Starting Control File and SPFILE Autobackup at 29-MAY-1577.piece handle=/u01/backup/c-3634177744-20150529-00_control.bkp comment=NONE78.Finished Control File 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 先创建一个基本的init pfile;

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 Area  208769024 bytes5.Fixed Size  2226936 bytes6.Variable Size   109053192 bytes7.Database Buffers  92274688 bytes8.Redo Buffers  5214208 bytes9.10.$ rman target /11.RMAN> restore spfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';12.13.Starting restore at 29-MAY-1514.using channel ORA_DISK_115.16.channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/c-3634177744-20150529-00_control.bkp17.channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete18.Finished restore at 29-MAY-1519.20.RMAN> restore controlfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';21.22.Starting restore at 29-MAY-1523.using channel ORA_DISK_124.25.channel ORA_DISK_1: restoring control file26.channel ORA_DISK_1: restore complete, elapsed time: 00:00:0127.output file name=/u01/oradata/sydb/control01.ctl28.Finished restore at 29-MAY-1529.RMAN> alter database 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 Key  Type LV Size Device Type Elapsed Time Completion Time9.------- ---- -- ---------- -----------10.3 Incr 0  180.53M    DISK        00:00:29     29-MAY-1511.BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20150529T16383912.Piece Name:/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_113.  List of Datafiles in backup set 314.  File LV Type Ckp SCN    Ckp Time  Name15.  ---- -- ---- ---------- ---------16.2 0 Incr 436655 29-MAY-15 /u01/oradata/sydb/sysaux01.dbf17.3 0 Incr 436655 29-MAY-15 /u01/oradata/sydb/undotbs01.dbf18.19.BS Key  Type LV Size Device Type Elapsed Time Completion Time20.------- ---- -- ---------- -----------21.4 Incr 0  380.94M  DISK 00:00:29     29-MAY-1522.BP Key: 4 Status: AVAILABLE  Compressed: NO  Tag: TAG20150529T16383923.Piece Name: /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_124.List of Datafiles in backup set 425.File LV Type Ckp SCN    Ckp Time  Name26.  ---- -- ---- ---------- ---------27.1 0 Incr 436654 29-MAY-15 /u01/oradata/sydb/system01.dbf28.4 0 Incr 436654 29-MAY-15 /u01/oradata/sydb/users01.dbf29.5   0 Incr 436654 29-MAY-15 /u01/oradata/sydb/tbs01.dbf30.31.RMAN> list backup of archivelog all;32.33.34.List of Backup Sets35.===================36.37.38.BS Key Size Device Type Elapsed Time Completion Time39.------- ---------- -----------40.1 45.49M  DISK    00:00:04  29-MAY-1541.BP Key: 1 Status: AVAILABLE  Compressed: NO  Tag: SYDB_INCR_LEVEL042.Piece Name: /u01/backup/SYDB_1_01q85q07_1_143.44.  List of Archived Logs in backup set 145.  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time46.  ---- ------- ---------- --------47.1 17  427739  29-MAY-15 436110  29-MAY-1548.49.BS Key Size Device Type Elapsed Time Completion Time50.------- ---------- -----------51.2 43.37M DISK  00:00:04 29-MAY-1552.BP Key: 2 Status: AVAILABLE  Compressed: NO  Tag: SYDB_INCR_LEVEL053.Piece Name: /u01/backup/SYDB_2_02q85q07_1_154.55.  List of Archived Logs in backup set 256.  Thrd Seq Low SCN Low Time  Next SCN   Next Time57.  ---- ------- ---------- -------58.1 18  436110  29-MAY-15 436484  29-MAY-1559.1 19  436484  29-MAY-15 436643  29-MAY-1560.61.BS Key Size Device Type Elapsed Time Completion Time62.------- ---------- ----------- ---63.5 90.00K DISK 00:00:00  29-MAY-1564.BP Key: 5  Status: AVAILABLE  Compressed: NO  Tag: SYDB_INCR_LEVEL065.Piece Name: /u01/backup/SYDB_5_05q85q1i_1_166.67.List of Archived Logs in backup set 568.Thrd Seq  Low SCN  Low Time  Next SCN   Next Time69.  ---- ------- ---------- ---------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-154.using channel ORA_DISK_15.6.channel ORA_DISK_1: starting datafile backup set restore7.channel ORA_DISK_1: specifying datafile(s) to restore from 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.channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/sydb/tbs01.dbf11.channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_112.channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T16383913.channel ORA_DISK_1: restored backup piece 114.channel ORA_DISK_1: restore complete, elapsed time: 00:00:2515.channel ORA_DISK_1: starting datafile backup set restore16.channel ORA_DISK_1: specifying datafile(s) to restore from backup set17.channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/sydb/sysaux01.dbf18.channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/sydb/undotbs01.dbf19.channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_120.channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T16383921.channel ORA_DISK_1: restored backup piece 122.channel ORA_DISK_1: restore complete, elapsed time: 00:00:3523.Finished restore at 29-MAY-1524.25.RMAN> recover database until sequence 21;26.27.Starting recover at 29-MAY-1528.using channel ORA_DISK_129.30.starting media recovery31.32.channel ORA_DISK_1: starting archived log restore to default destination33.channel ORA_DISK_1: restoring archived log34.archived log thread=1 sequence=2035.channel ORA_DISK_1: reading from backup piece /u01/backup/SYDB_5_05q85q1i_1_136.channel ORA_DISK_1: piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL037.channel ORA_DISK_1: restored backup piece 138.channel ORA_DISK_1: restore complete, elapsed time: 00:00:0139.archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf thread=1 sequence=2040.media recovery complete, elapsed time: 00:00:0141.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_size3.3  from v$datafile d left join v$tablespace t4.4  on d.ts#=t.ts#;5.6.FILE# FILE_NAME TABLESPACE_NAME STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINBYTES_MB BLOCKS CREATE_BYTES_MB BLOCK_SIZE7.------------------------------ -------8.1 /u01/oradata/sydb/system01.dbf  SYSTEM    SYSTEM  READ WRITE  436756      29-MAY-15   400.8125  12826   100  327689.2  /u01/oradata/sydb/sysaux01.dbf  SYSAUX    ONLINE  READ WRITE 436756       29-MAY-15   227.6875 7286     100   3276810.3 /u01/oradata/sydb/undotbs01.dbf UNDOTBS01  ONLINE  READ WRITE 436756      29-MAY-15   310       9920    100    3276811.4  /u01/oradata/sydb/users01.dbf   USERS ONLINE  READ WRITE 436756  29-MAY-15 100  3200 100     3276812.5  /u01/oradata/sydb/tbs01.dbf TBS01 ONLINE  READ WRITE 436756   29-MAY-15   98  3136  10  3276813.14.Elapsed: 00:00:00.0215.SYS@sydb>alter database open resetlogs;16.17.Database altered.18.19.Elapsed: 00:00:07.41

五、总结

任何时候数据库的还原和恢复因环境和错误的不一而不同,认真有效的阅读文档,理解工作原理,然后在加上重复的实验方能起到良好的效果,实验本身是动手实践的步骤,但同时也是理论实践结合的强有效方式。

本文来自云栖社区合作伙伴“DBGEEK”