controlfile与备份恢复

时间:2023-03-09 00:10:13
controlfile与备份恢复
controlfile与备份恢复
 
 
数据库正常关闭,trace controlfile信息.
 
***************************************************************************
DATABASE ENTRY
***************************************************************************
Database checkpoint: Thread=1 scn: 0x0000.000d39f4
.
.
***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
 (size = 8180, compat size = 8180, section max = 11, section in-use = 0,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x1 flags:0x0 dirty:0
low cache rba:(0xffffffff.ffffffff.ffff) on disk rba:(0x2.1289.0)
on disk scn: 0x0000.000d39c9 09/20/2013 01:16:34
.
.
***************************************************************************
DATA FILE RECORDS
**************************************************************************
Checkpoint cnt:101 scn: 0x0000.000d39f4 09/20/2013 01:16:34
 Stop scn: 0x0000.000d39f4 09/20/2013 01:16:34
 
 
此时数据文件状态为一致的,无须进行介质恢复.
 
我们知道重建的控制文件能够从当前的日志文件获得正确的SCN及时间点等信息。同样地,控制文件也能够从数据文件中获得详细的检查点信息等。
 
测试版本恢复前后控制文件的变化。
 
--当前control file的检查点信息  2013-09-20 05:19:58.492
DUMP OF CONTROL FILES, Seq # 1210 = 0x4ba
 V10 STYLE FILE HEADER:
      Compatibility Vsn = 186646528=0xb200000
      Db ID=1395399339=0x532c1aab, Db Name='AUGUST'
      Activation ID=0=0x0
      Control Seq=1210=0x4ba, File size=594=0x252
      File Number=0, Blksiz=16384, File Type=1 CONTROL
.
***************************************************************************
DATABASE ENTRY
***************************************************************************
Database checkpoint: Thread=1 scn: 0x0000.000d39f7
***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
low cache rba:(0x2.4593.0) on disk rba:(0x2.45d2.0)
on disk scn: 0x0000.000d4621 09/20/2013 05:19:16
***************************************************************************
DATA FILE RECORDS
***************************************************************************
Checkpoint cnt:102 scn: 0x0000.000d39f7 09/20/2013 01:22:31
 Stop scn: 0xffff.ffffffff 09/20/2013 01:16:34
 
--版本恢复
 
RMAN> shutdown abort
RMAN> startup mount;
RMAN> list backup of database;
 
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    964.65M    DISK        00:02:07     19-SEP-13     
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20130919T011640
        Piece Name: /u01/app/oracle/flash_recovery_area/august/AUGUST/backupset/2013_09_19/o1_mf_nnndf_TAG20130919T011640_93odqb65_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 838482     19-SEP-13 /u01/app/oracle/oradata/august/august/system01.dbf
  2       Full 838482     19-SEP-13 /u01/app/oracle/oradata/august/august/sysaux01.dbf
  3       Full 838482     19-SEP-13 /u01/app/oracle/oradata/august/august/undotbs01.dbf
  4       Full 838482     19-SEP-13 /u01/app/oracle/oradata/august/august/users01.dbf
 
RMAN> restore database;
 
Starting restore at 20-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/august/august/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/august/august/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/august/august/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/august/august/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/august/AUGUST/backupset/2013_09_19/o1_mf_nnndf_TAG20130919T011640_93odqb65_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/august/AUGUST/backupset/2013_09_19/o1_mf_nnndf_TAG20130919T011640_93odqb65_.bkp tag=TAG20130919T011640
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:47
Finished restore at 20-SEP-13
 
--版本恢复后的control file信息  Fri Sep 20 05:47:45 PDT 2013
 
DUMP OF CONTROL FILES, Seq # 1221 = 0x4c5
 V10 STYLE FILE HEADER:
      Compatibility Vsn = 186646528=0xb200000
      Db ID=1395399339=0x532c1aab, Db Name='AUGUST'
      Activation ID=0=0x0
      Control Seq=1221=0x4c5, File size=594=0x252
      File Number=0, Blksiz=16384, File Type=1 CONTROL
 
***************************************************************************
DATABASE ENTRY
***************************************************************************
Database checkpoint: Thread=1 scn: 0x0000.000d39f7
***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
low cache rba:(0x2.49d1.0) on disk rba:(0x2.4a0c.0)
on disk scn: 0x0000.000d4925 09/20/2013 05:30:19
***************************************************************************
DATA FILE RECORDS
***************************************************************************
Checkpoint cnt:102 scn: 0x0000.000d39f7 09/20/2013 01:22:31
 Stop scn: 0xffff.ffffffff 09/20/2013 01:16:34
 
看上去控制文件中的信息似乎并没有更新数据文件的检查点信息。
似乎这些信息,与数据库恢复关系不大。用着崩溃恢复时,确定恢复点。(自动进行恢复)
 
如果是版本恢复,restore database之后,需要手动进行前滚恢复 recover database,这个检查点似乎理解起来没什么关系。
 
OK,那么怎么确定从哪个归档日志开始恢复呢?
 
查看restore database后的数据文件头文件
 
SQL> alter session set events ‘ immediate trace name file_hdrs level 10’
 
Tablespace #2 - UNDOTBS1  rel_fn:3
Creation   at   scn: 0x0000.000b7982 08/13/2009 23:56:54
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x313e782e scn: 0x0000.000b8338
 prev reset logs count:0x296a3120 scn: 0x0000.00000001
 recovered at 09/20/2013 05:35:23
 status:0x0 root dba:0x00000000 chkpt cnt: 19 ctl cnt:18
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000ccb52 09/19/2013 01:16:41      /*换算成10进制:838482*/
 thread:1 rba:(0x6.1f1d.10)
 
查看归档日志信息。
 
SQL>  select sequence#,first_change#,next_change#,to_char(next_time,'yy-mm-dd hh24:MM:ss') next_time from v$archived_log;
 
 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# NEXT_TIME
---------- ------------- ------------ ----------------------------------
         2        757280       767227 13-09-15 05:09:34
         3        767227       780016 13-09-15 07:09:34
         4        780016       811911 13-09-16 04:09:24
         5        811911       835483 13-09-19 00:09:56
         6        835483       840784 13-09-19 20:09:40
         7        840784       841197 13-09-19 20:09:53
         8        841197       845016 13-09-19 23:09:25
         1        841198       864921 13-09-20 00:09:36
 
我们可以清楚的得知,需要从sequence 6的归档日志开始恢复。
 
注意我们的日志是reset过的,通过until sequence好像没有得到我们预期的结果。
 
RMAN> recover database until sequence 7;
 
Starting recover at 20-SEP-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_6_93qgls17_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_7_93qh7sqx_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_20/o1_mf_1_1_93qzp568_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_6_93qgls17_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_7_93qh7sqx_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_20/o1_mf_1_1_93qzp568_.arc thread=1 sequence=1
unable to find archived log
archived log thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/20/2013 06:27:33
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2 and starting SCN of 864921
 
可以看出,RMAN找的是reset之后的sequence 6,但是这个log并没有产生,所以报错了。
 
那试一试,通过until time的方式进行恢复。
 
关于until time有两点要说明:
1. 时间格式的设置,如果不设置,RMAN很可能不认识你所输入的格式。
因为RMAN以环境变量来读取时间格式,这点与sqlplus的固定格式不同,需要进行设置。
 
RMAN> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
 
2. RMAN-20207错误。
RMAN> recover database until time '2013-09-19 08:09:00';
 
Starting recover at 20-SEP-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/20/2013 06:54:46
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
 
默认认为until time的时间不能早于RESETLOGS的时间。
 
查看日志生命周期版本信息。
RMAN> list incarnation of database 'august';
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       AUGUST   1395399339       PARENT  1          13-AUG-09
2       2       AUGUST   1395399339       PARENT  754488     15-SEP-13
3       3       AUGUST   1395399339       CURRENT 841198     19-SEP-13
 
切换到周期2中,也就是我们resetlogs之前的一个周期。
 
RMAN> reset database to incarnation 2; 
 
database reset to incarnation 2
 
重新尝试recover database until time,一直提示RMAN-06556错误,改用scn。
 
RMAN> recover database until scn 845016;
 
Starting recover at 20-SEP-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/20/2013 07:48:30
RMAN-06556: datafile 1 must be restored from backup older than SCN 845016
 
依然报错。
 
重新restore后设置incarnation然后recover
 
RMAN> recover database until scn 845016;
 
Starting recover at 20-SEP-13
using channel ORA_DISK_1
 
starting media recovery
 
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_6_93qgls17_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_7_93qh7sqx_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_8_93qs3s52_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_6_93qgls17_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_7_93qh7sqx_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/flash_recovery_area/august/AUGUST/archivelog/2013_09_19/o1_mf_1_8_93qs3s52_.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:11
Finished recover at 20-SEP-13
 
成功,看来这样恢复是没有问题的。
 
RMAN> alter database open resetlogs;
 
database opened
 
 
--测试:新的sequence时候会将旧的归档覆盖掉?不会!
 
SQL> select sequence#,first_change#,next_change#,to_char(next_time,'yy-mm-dd hh24:MM:ss') next_time from v$archived_log;
 
 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# NEXT_TIME
---------- ------------- ------------ ----------------------------------
         2        757280       767227 13-09-15 05:09:34
         3        767227       780016 13-09-15 07:09:34
         4        780016       811911 13-09-16 04:09:24
         5        811911       835483 13-09-19 00:09:56
         6        835483       840784 13-09-19 20:09:40
         7        840784       841197 13-09-19 20:09:53
         8        841197       845016 13-09-19 23:09:25
         1        841198       864921 13-09-20 00:09:36
         1        841198       864921 13-09-20 00:09:36
         2        864921   2.8147E+14
         0             0            0
 
查看当前incarnation情况。
 
RMAN> list incarnation of database 'august';
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       AUGUST   1395399339       PARENT  1          13-AUG-09
2       2       AUGUST   1395399339       PARENT  754488     15-SEP-13
3       3       AUGUST   1395399339       ORPHAN  841198     19-SEP-13
4       4       AUGUST   1395399339       CURRENT 845017     20-SEP-13