数据库灾难性环境下恢复

所谓灾难性是指磁盘故障或其它原因导致数据库相关的文件全部丢失或损坏无法进行修复,必须通过备份进行还原恢复;以下实验模拟丢失所有数据库文件,然后通过备份进行还原恢复。

版本和数据库文件信息

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE  11.2.0.3.0   ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL> column name format a50SQL> select file#,status,name from v$datafile;  FILE# STATUS NAME---------- ------- --------------------------------------------------     1 SYSTEM /u01/oradata/sydb/system01.dbf     2 ONLINE /u01/oradata/sydb/sysaux01.dbf     3 ONLINE /u01/oradata/sydb/undotbs01.dbf     4 ONLINE /u01/oradata/sydb/users01.dbf     5 ONLINE /u01/oradata/sydb/tbs01.dbfSQL> column member format a50SQL> select * from v$Logfile;  GROUP# STATUS TYPE  MEMBER                       IS_---------- ------- ------- -------------------------------------------------- ---     1     ONLINE /u01/oradata/sydb/REDO01.LOG            NO     2     ONLINE /u01/oradata/sydb/REDO02.LOG            NOSQL> select * from v$controlfile;STATUS NAME                        IS_ BLOCK_SIZE FILE_SIZE_BLKS------- -------------------------------------------------- --- ---------- --------------    /u01/oradata/sydb/control01.ctl          NO    16384      668

备份数据库

注意:备份数据库时如果配置了 configure exclude for tablespace tbsname 的表空间是不会进行备份的,恢复表空间备份configure exclude for tablespace tbsname clear;如果没有设置自动备份控制文件和参数文件,要在备份脚本最后手动添加备份控制文件脚本;设置自动备份控制文件和参数文件:

 CONFIGURE CONTROLFILE AUTOBACKUP On; 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。开始备份数据

 run { allocate channel dev type disk; allocate channel dev2 type disk; backup incremental level 0 database plus archivelog delete input tag 'sydb_incr_level0' format '/u01/backup/%d_%s_%U'; release channel dev; release channel dev2; }allocated channel: devchannel dev: SID=181 device type=DISKallocated channel: dev2channel dev2: SID=18 device type=DISKStarting backup at 29-MAY-15current log archivedchannel dev: starting archived log backup setchannel dev: specifying archived log(s) in backup setinput archived log thread=1 sequence=17 RECID=1 STAMP=880994007channel dev: starting piece 1 at 29-MAY-15channel dev2: starting archived log backup setchannel dev2: specifying archived log(s) in backup setinput archived log thread=1 sequence=18 RECID=2 STAMP=880994016input archived log thread=1 sequence=19 RECID=3 STAMP=880994311channel dev2: starting piece 1 at 29-MAY-15channel dev: finished piece 1 at 29-MAY-15piece handle=/u01/backup/SYDB_1_01q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONEchannel dev: backup set complete, elapsed time: 00:00:07channel dev: deleting archived log(s)archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_17_880905808.dbf RECID=1 STAMP=880994007channel dev2: finished piece 1 at 29-MAY-15piece handle=/u01/backup/SYDB_2_02q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONEchannel dev2: backup set complete, elapsed time: 00:00:08channel dev2: deleting archived log(s)archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_18_880905808.dbf RECID=2 STAMP=880994016archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_19_880905808.dbf RECID=3 STAMP=880994311Finished backup at 29-MAY-15Starting backup at 29-MAY-15channel dev: starting incremental level 0 datafile backup setchannel dev: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/oradata/sydb/system01.dbfinput datafile file number=00004 name=/u01/oradata/sydb/users01.dbfinput datafile file number=00005 name=/u01/oradata/sydb/tbs01.dbfchannel dev: starting piece 1 at 29-MAY-15channel dev2: starting incremental level 0 datafile backup setchannel dev2: specifying datafile(s) in backup setinput datafile file number=00003 name=/u01/oradata/sydb/undotbs01.dbfinput datafile file number=00002 name=/u01/oradata/sydb/sysaux01.dbfchannel dev2: starting piece 1 at 29-MAY-15channel dev: finished piece 1 at 29-MAY-15piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839 comment=NONEchannel dev: backup set complete, elapsed time: 00:00:35channel dev2: finished piece 1 at 29-MAY-15piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839 comment=NONEchannel dev2: backup set complete, elapsed time: 00:00:35Finished backup at 29-MAY-15Starting backup at 29-MAY-15current log archivedchannel dev: starting archived log backup setchannel dev: specifying archived log(s) in backup setinput archived log thread=1 sequence=20 RECID=4 STAMP=880994354channel dev: starting piece 1 at 29-MAY-15channel dev: finished piece 1 at 29-MAY-15piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0 comment=NONEchannel dev: backup set complete, elapsed time: 00:00:01channel dev: deleting archived log(s)archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf RECID=4 STAMP=880994354Finished backup at 29-MAY-15Starting Control File and SPFILE Autobackup at 29-MAY-15piece handle=/u01/backup/c-3634177744-20150529-00_control.bkp comment=NONEFinished Control File and SPFILE Autobackup at 29-MAY-15released channel: devreleased channel: dev2

通过备份日志可以知道哪些数据文件被备份,哪些没有被备份或没有备份成功,比如:发现较多数据块损坏、表空间被排除备份(exclude)、表空间被skip;关注这些信息非常重要,因为一旦灾难发生,它意味着你的数据库恢复成功率,也有助于对备份脚本的优化和调整。

验证数据库可恢复性

验证可恢复性可以发现一些忽略的问题,及时处理;

RMAN> restore database validate;Starting restore at 29-MAY-15allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=181 device type=DISKchannel ORA_DISK_1: starting validation of datafile backup setchannel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: validation complete, elapsed time: 00:00:03channel ORA_DISK_1: starting validation of datafile backup setchannel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: validation complete, elapsed time: 00:00:01Finished restore at 29-MAY-15

删除数据库相关文件模拟灾难

rm /u01/oradata/sydb/system01.dbfrm /u01/oradata/sydb/sysaux01.dbfrm /u01/oradata/sydb/undotbs01.dbfrm /u01/oradata/sydb/tbs01.dbfrm /u01/oradata/sydb/control01.ctlrm /u01/oradata/sydb/REDO01.LOG rm /u01/oradata/sydb/REDO02.LOG rm /u01/app/product/11.2.3/db_1/dbs/spfilesydb.ora

数据库恢复

恢复参数文件和控制文件

数据库启动时会先查找spfile,然后在找spfile.ora,再找文件initpfile,如果这些文件都没有找到报错并停止启动;so 先创建一个基本的init pfile;

$ vim /u01/app/product/11.2.3/db_1/dbs/initsydb.oradb_name='sydb'memory_target=200mcontrol_files='/u01/oradata/sydb/control01.ctl'db_block_size=32768

如果你的数据块大小为非8kb,你又想在恢复控制文件后完成其它操作,如mount控制文件,则你必须在init 文件中指定数据块大小;

SQL> startup nomountORACLE instance started.Total System Global Area 208769024 bytesFixed Size         2226936 bytesVariable Size       109053192 bytesDatabase Buffers      92274688 bytesRedo Buffers        5214208 bytes$ rman target /RMAN> restore spfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';Starting restore at 29-MAY-15using channel ORA_DISK_1channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/c-3634177744-20150529-00_control.bkpchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP completeFinished restore at 29-MAY-15RMAN> restore controlfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';Starting restore at 29-MAY-15using channel ORA_DISK_1channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/oradata/sydb/control01.ctlFinished restore at 29-MAY-15RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1

查看备份文件和确定可恢复的最大归档日志序列

注:如果目录名发生改变或文件发生位置移动,先使用 catalog start with '/dir' 将文件catalog;

RMAN> list backup of database;List of Backup Sets===================BS Key Type LV Size    Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------3    Incr 0 180.53M  DISK    00:00:29   29-MAY-15       BP Key: 3  Status: AVAILABLE Compressed: NO Tag: TAG20150529T163839    Piece Name: /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 List of Datafiles in backup set 3 File LV Type Ckp SCN  Ckp Time Name ---- -- ---- ---------- --------- ---- 2  0 Incr 436655   29-MAY-15 /u01/oradata/sydb/sysaux01.dbf 3  0 Incr 436655   29-MAY-15 /u01/oradata/sydb/undotbs01.dbfBS Key Type LV Size    Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------4    Incr 0 380.94M  DISK    00:00:29   29-MAY-15       BP Key: 4  Status: AVAILABLE Compressed: NO Tag: TAG20150529T163839    Piece Name: /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 List of Datafiles in backup set 4 File LV Type Ckp SCN  Ckp Time Name ---- -- ---- ---------- --------- ---- 1  0 Incr 436654   29-MAY-15 /u01/oradata/sydb/system01.dbf 4  0 Incr 436654   29-MAY-15 /u01/oradata/sydb/users01.dbf 5  0 Incr 436654   29-MAY-15 /u01/oradata/sydb/tbs01.dbfRMAN> list backup of archivelog all;List of Backup Sets===================BS Key Size    Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------1    45.49M   DISK    00:00:04   29-MAY-15       BP Key: 1  Status: AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0    Piece Name: /u01/backup/SYDB_1_01q85q07_1_1 List of Archived Logs in backup set 1 Thrd Seq   Low SCN  Low Time Next SCN  Next Time ---- ------- ---------- --------- ---------- --------- 1  17   427739   29-MAY-15 436110   29-MAY-15BS Key Size    Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------2    43.37M   DISK    00:00:04   29-MAY-15       BP Key: 2  Status: AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0    Piece Name: /u01/backup/SYDB_2_02q85q07_1_1 List of Archived Logs in backup set 2 Thrd Seq   Low SCN  Low Time Next SCN  Next Time ---- ------- ---------- --------- ---------- --------- 1  18   436110   29-MAY-15 436484   29-MAY-15 1  19   436484   29-MAY-15 436643   29-MAY-15BS Key Size    Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------5    90.00K   DISK    00:00:00   29-MAY-15       BP Key: 5  Status: AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0    Piece Name: /u01/backup/SYDB_5_05q85q1i_1_1 List of Archived Logs in backup set 5 Thrd Seq   Low SCN  Low Time Next SCN  Next Time ---- ------- ---------- --------- ---------- --------- 1  20   436643   29-MAY-15 436756   29-MAY-15

从上面的信息中可以看出最大可恢复到的日志序列为20,恢复时设置until sequence 21,数据库即可应用所有的归档日志;

RMAN> restore database until sequence 21;Starting restore at 29-MAY-15using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/sydb/system01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/sydb/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/sydb/tbs01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:25channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/sydb/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/sydb/undotbs01.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:35Finished restore at 29-MAY-15RMAN> recover database until sequence 21;Starting recover at 29-MAY-15using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=20channel ORA_DISK_1: reading from backup piece /u01/backup/SYDB_5_05q85q1i_1_1channel ORA_DISK_1: piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf thread=1 sequence=20media recovery complete, elapsed time: 00:00:01Finished recover at 29-MAY-15

使用resetlogs 方式打开数据库

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 (create_bytes/1024/1024)create_bytes_mb,block_size  3 from v$datafile d left join v$tablespace t 4 on d.ts#=t.ts#;FILE#  FILE_NAME TABLESPACE_NAME  STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOIN BYTES_MB BLOCKS CREATE_BYTES_MB BLOCK_SIZE------------------------------ ------- ---------- ------------------ --------- ---------- ---------- --------------- ----------1 /u01/oradata/sydb/system01.dbf SYSTEM  SYSTEM READ WRITE 436756   29-MAY-15  400.8125 12826  100   327682 /u01/oradata/sydb/sysaux01.dbf SYSAUX  ONLINE READ WRITE 436756    29-MAY-15  227.6875 7286   100   327683 /u01/oradata/sydb/undotbs01.dbf UNDOTBS01 ONLINE READ WRITE 436756   29-MAY-15  310    9920  100  327684 /u01/oradata/sydb/users01.dbf  USERS   ONLINE READ WRITE 436756   29-MAY-15  100    3200  100   327685 /u01/oradata/sydb/tbs01.dbf   TBS01   ONLINE READ WRITE 436756   29-MAY-15  98    3136   10   32768Elapsed: 00:00:00.02SYS@sydb>alter database open resetlogs;Database altered.Elapsed: 00:00:07.41