using backup controlfile 两种使用情况区别

使用备份控制文件和重建控制文件恢复,都需要使用到using backup controlfile命令,但是两种情况下却有着本质的区别
试验准备条件

SQL> select * from v$version;   BANNER ------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE    11.2.0.3.0      Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production   SQL> select to_char(sysdate, 'yyyy-mm-dd' ) "www.xifenfei.com" from dual;   www.xifenfei.com -------------------- 2012-07-13   SQL> alter database open resetlogs;   Database altered.   SQL> select name from v$controlfile;   NAME ---------------------------------------------------- /u01/oracle/oradata/ora11g/control01.ctl   SQL> !cp /u01/oracle/oradata/ora11g/control01.ctl /tmp/xff.ctl   SQL> alter system checkpoint ;   System altered.   SQL> /   System altered.   SQL> /   System altered.   SQL> alter system switch logfile;       System altered.   SQL> /   System altered.   SQL> /   System altered.   SQL> /   System altered.   SQL> alter system checkpoint ;   System altered.   SQL> shutdown abort; ORACLE instance shut down. SQL> !rm /u01/oracle/oradata/ora11g/control01.ctl   SQL> ! cp /tmp/xff.ctl /u01/oracle/oradata/ora11g/control01.ctl   SQL> startup ORACLE instance started.   Total System Global Area  523108352 bytes Fixed Size                  1346052 bytes Variable Size             432014844 bytes Database Buffers           83886080 bytes Redo Buffers                5861376 bytes Database mounted. ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf' ORA-01207: file is more recent than control file - old control file

相关说明：
1.通过resetlogs使得试验更加清晰
2.通过多次的checkpoint实现增加scn,switch logfile实现日志组切换
3.通过模拟备份控制文件恢复

查询相关SCN

SQL> set linesize 150 SQL> select file#,to_char(checkpoint_change#, '9999999999999999' ) "SCN" ,    2  to_char(RESETLOGS_CHANGE#, '9999999999999999' ) "RESETLOGS SCN" ,FUZZY    3  from v$datafile_header;         FILE# SCN                                RESETLOGS SCN                      FUZZY ---------- ---------------------------------- ---------------------------------- ------           1           2118981                            2118577                  YES           2           2118981                            2118577                  YES           3           2118981                            2118577                  YES           4           2118981                            2118577                  YES           6           2118981                            2118577                  YES   SQL> select file#,to_char(checkpoint_change#, '999999999999999' ) "SCN" ,    2  to_char(last_change#, '999999999999999' ) "STOP_SCN" from v$datafile;         FILE# SCN                              STOP_SCN ---------- -------------------------------- --------------------------------           1          2118580           2          2118580           3          2118580           4          2118580           6          2118580   SQL> select CONTROLFILE_CHANGE#   from v$ database ;   CONTROLFILE_CHANGE# -------------------              2118713

做关于控制文件和数据文件dump

SQL> oradebug setmypid; Statement processed. SQL> oradebug dump file_hdrs 3; Statement processed. SQL> oradebug tracefile_name; /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_593.trc SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@xifenfei ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 13 03:05:48 2012   Copyright (c) 1982, 2011, Oracle.  All rights reserved.   Connected to : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> oradebug setmypid; Statement processed. SQL>  oradebug dump controlf 3; Statement processed. SQL> oradebug tracefile_name; /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_775.trc

分析file_hdrs 3 dump文件

--datafile 1的datafile header信息 Tablespace #0 - SYSTEM  rel_fn:1 Creation   at   scn: 0x0000.00000007 09 /18/2011 17:33:47 Backup taken at scn: 0x0000.00000000 01 /01/1988 00:00:00 thread:0   reset logs count:0x2eff82e3 scn: 0x0000.002053b1   prev reset logs count:0x2e9e8451 scn: 0x0000.0016eaab   recovered at 07 /13/2012 02:56:18   status:0x2004 root dba:0x00400208 chkpt cnt: 760 ctl cnt:759 begin-hot-backup file size: 0 Checkpointed at scn:  0x0000.00205545 07 /13/2012 03:01:42   --datafile 1的控制文件中信息 DATA FILE #1:    name #7: /u01/oracle/oradata/ora11g/system01.dbf creation size=0 block size=8192 status=0xe head =7 tail =7 dup=1   tablespace 0, index=1 krfil=1 prev_file=0   unrecoverable scn: 0x0000.00000000 01 /01/1988 00:00:00   Checkpoint cnt:752 scn: 0x0000.002053b4 07 /13/2012 02:59:18   Stop scn: 0xffff.ffffffff 07 /13/2012 02:58:43   Creation Checkpointed at scn:  0x0000.00000007 09 /18/2011 17:33:47

通过这里可以看出来:
datafile header的Checkpointed scn(00205545)>controfile datafile的Checkpoint scn(002053b4)
datafile header的checkpiont count(760)>controfile datafile的checkpiont count(752)
所以在数据库open的时候会报ORA-01207错误

尝试恢复数据库

SQL> recover database using backup controlfile; ORA-00279: change 2118713 generated at 07/13/2012 02:58:43 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_1_788497123.dbf ORA-00280: change 2118713 for thread 1 is in sequence #1   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}   ORA-00308: cannot open archived log '/u01/oracle/oradata/archivelog/ora11g/1_1_788497123.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3   SQL> SQL> select to_char(2118713, 'xxxxxxx' ) from dual;   TO_CHAR(2118713, ----------------    205439

分析controlf 3 dump文件

*************************************************************************** DATABASE ENTRY ***************************************************************************   (size = 316, compat size = 316, section max = 1, section in -use = 1,    last-recid= 0, old-recno = 0, last-recno = 0)   (extent = 1, blkno = 1, numrecs = 1)   03 /12/2012 22:17:06   DB Name "ORA11G"   Database flags = 0x00404000 0x00001000   Controlfile Creation Timestamp  03 /12/2012 22:17:07   Incmplt recovery scn: 0x0000.00000000   Resetlogs scn: 0x0000.002053b1 Resetlogs Timestamp  07 /13/2012 02:58:43   Prior resetlogs scn: 0x0000.0016eaab Prior resetlogs Timestamp  05 /01/2012 13:14:57   Redo Version: compatible=0xb200000   #Data files = 5, #Online files = 5   Database checkpoint: Thread=1 scn: 0x0000.002053b4   Threads: #Enabled=1, #Open=1, Head=1, Tail=1   Max log members = 3, Max data members = 1   Arch list: Head=0, Tail=0, Force scn: 0x0000.00000000scn: 0x0000.00000000   Activation ID: 4184707968   Controlfile Checkpointed at scn:  0x0000.00205439 07 /13/2012 02:59:25 <==控制文件checkpiont,控制文件每3秒的一次checkpiont   thread:0 rba:(0x0.0.0)

通过recover的提示和对于controlf 3 dump文件的分析,可以确定数据库使用备份控制文件恢复,需要改控制文件备份之时开始的所有归档日志

查询数据库当前redo情况

SQL> select member from v$logfile;   MEMBER ------------------------------------------------------------- /u01/oracle/oradata/ora11g/redo03.log /u01/oracle/oradata/ora11g/redo02.log /u01/oracle/oradata/ora11g/redo01.log   SQL> select SEQUENCE #,STATUS,FIRST_CHANGE# ,NEXT_CHANGE# from v$log;     SEQUENCE # STATUS                           FIRST_CHANGE# NEXT_CHANGE# ---------- -------------------------------- ------------- ------------           1 CURRENT                                2118577   2.8147E+14           0 UNUSED                                       0            0           0 UNUSED                                       0            0   SQL> archive log list; Database log mode              No Archive Mode Automatic archival             Disabled Archive destination            /u01/oracle/oradata/archivelog/ora11g Oldest online log sequence     1 Current log sequence           1   SQL> alter system dump logfile '/u01/oracle/oradata/ora11g/redo03.log' ;   System altered.   SQL> alter system dump logfile '/u01/oracle/oradata/ora11g/redo02.log' ;   System altered.   SQL> alter system dump logfile '/u01/oracle/oradata/ora11g/redo01.log' ;   System altered.

因为数据库处于非归档模式,而得到的redo信息主要都是来自控制文件,所以只能通过dump redo来分析当前redo的情况

分析redo log dump

DUMP OF REDO FROM FILE '/u01/oracle/oradata/ora11g/redo03.log'   Opcodes *.*   RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff   SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff   Times: creation thru eternity   FILE HEADER:      Compatibility Vsn = 186646528=0xb200000      Db ID=4173966754=0xf8c9ada2, Db Name= 'ORA11G'      Activation ID=4184707968=0xf96d9380      Control Seq=7660=0x1dec, File size=30720=0x7800      File Number=3, Blksiz=512, File Type=2 LOG   descrip: "Thread 0001, Seq# 0000000003, SCN 0x00000020553d-0x000000205540"   thread: 1 nab: 0x2 seq : 0x00000003 hws: 0x2 eot: 0 dis: 0   resetlogs count: 0x2eff82e3 scn: 0x0000.002053b1 (2118577)   prev resetlogs count: 0x2e9e8451 scn: 0x0000.0016eaab (1501867)   Low  scn: 0x0000.0020553d (2118973) 07 /13/2012 03:01:34   Next scn: 0x0000.00205540 (2118976) 07 /13/2012 03:01:35   Enabled scn: 0x0000.002053b1 (2118577) 07 /13/2012 02:58:43   Thread closed scn: 0x0000.0020553d (2118973) 07 /13/2012 03:01:34   Disk cksum: 0xa716 Calc cksum: 0xa716   DUMP OF REDO FROM FILE '/u01/oracle/oradata/ora11g/redo02.log'   Opcodes *.*   RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff   SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff   Times: creation thru eternity   FILE HEADER:      Compatibility Vsn = 186646528=0xb200000      Db ID=4173966754=0xf8c9ada2, Db Name= 'ORA11G'      Activation ID=4184707968=0xf96d9380      Control Seq=7663=0x1def, File size=30720=0x7800      File Number=2, Blksiz=512, File Type=2 LOG   descrip: "Thread 0001, Seq# 0000000005, SCN 0x000000205543-0xffffffffffff"   thread: 1 nab: 0xffffffff seq : 0x00000005 hws: 0x1 eot: 1 dis: 0   resetlogs count: 0x2eff82e3 scn: 0x0000.002053b1 (2118577)   prev resetlogs count: 0x2e9e8451 scn: 0x0000.0016eaab (1501867)   Low  scn: 0x0000.00205543 (2118979) 07 /13/2012 03:01:36   Next scn: 0xffff.ffffffff 01 /01/1988 00:00:00   Enabled scn: 0x0000.002053b1 (2118577) 07 /13/2012 02:58:43   Thread closed scn: 0x0000.00205543 (2118979) 07 /13/2012 03:01:36   Disk cksum: 0xc3f9 Calc cksum: 0xc3f9   Terminal recovery stop scn: 0x0000.00000000   Terminal recovery  01 /01/1988 00:00:00   Most recent redo scn: 0x0000.00000000   DUMP OF REDO FROM FILE '/u01/oracle/oradata/ora11g/redo01.log'   Opcodes *.*   RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff   SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff   Times: creation thru eternity   FILE HEADER:      Compatibility Vsn = 186646528=0xb200000      Db ID=4173966754=0xf8c9ada2, Db Name= 'ORA11G'      Activation ID=4184707968=0xf96d9380      Control Seq=7663=0x1def, File size=30720=0x7800      File Number=1, Blksiz=512, File Type=2 LOG   descrip: "Thread 0001, Seq# 0000000004, SCN 0x000000205540-0x000000205543"   thread: 1 nab: 0x2 seq : 0x00000004 hws: 0x2 eot: 0 dis: 0   resetlogs count: 0x2eff82e3 scn: 0x0000.002053b1 (2118577)   prev resetlogs count: 0x2e9e8451 scn: 0x0000.0016eaab (1501867)   Low  scn: 0x0000.00205540 (2118976) 07 /13/2012 03:01:35   Next scn: 0x0000.00205543 (2118979) 07 /13/2012 03:01:36   Enabled scn: 0x0000.002053b1 (2118577) 07 /13/2012 02:58:43   Thread closed scn: 0x0000.00205540 (2118976) 07 /13/2012 03:01:35   Disk cksum: 0xaa26 Calc cksum: 0xaa26   Terminal recovery stop scn: 0x0000.00000000   Terminal recovery  01 /01/1988 00:00:00   Most recent redo scn: 0x0000.00000000

通过对redo dump的分析可以得到：
1.最小的sequence#=3是redo03.log
2.current redo为redo02.log

继续尝试恢复

SQL> recover database using backup controlfile; ORA-00279: change 2118713 generated at 07/13/2012 02:58:43 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_1_788497123.dbf ORA-00280: change 2118713 for thread 1 is in sequence #1   Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/oracle/oradata/ora11g/redo02.log ORA-00326: log begins at change 2118979, need earlier change 2118713 ORA-00334: archived log: '/u01/oracle/oradata/ora11g/redo02.log'

关于重建控制文件后使用using backup controlfile总结:恢复的启动控制文件备份的scn,需要该控制文件备份后的所有归档日志.对于当前这个非归档,而且redo被覆盖的库,该方法无法正常恢复

重建控制文件并做controlf 3 dump

SQL> alter database backup controlfile to trace as '/tmp/ctl.trace' ;   Database altered.   SQL> shutdown immediate ORA-01109: database not open   Database dismounted. ORACLE instance shut down. SQL> STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  NOARCHIVELOG      MAXLOGFILES 16      MAXLOGMEMBERS 3      MAXDATAFILES 100      MAXINSTANCES 8      MAXLOGHISTORY 292 LOGFILE    GROUP 1 '/u01/oracle/oradata/ora11g/redo01.log'  SIZE 15M BLOCKSIZE 512,    GROUP 2 '/u01/oracle/oradata/ora11g/redo02.log'  SIZE 15M BLOCKSIZE 512,    GROUP 3 '/u01/oracle/oradata/ora11g/redo03.log'  SIZE 15M BLOCKSIZE 512 DATAFILE    '/u01/oracle/oradata/ora11g/system01.dbf' ,    '/u01/oracle/oradata/ora11g/sysaux01.dbf' ,    '/u01/oracle/oradata/ora11g/undotbs01.dbf' ,    '/u01/oracle/oradata/ora11g/users01.dbf' ,    '/u01/oracle/oradata/ora11g/xifenfei02.dbf' CHARACTER SET AL32UTF8 ; ORACLE instance started.   Total System Global Area  523108352 bytes Fixed Size                  1346052 bytes Variable Size             432014844 bytes Database Buffers           83886080 bytes Redo Buffers                5861376 bytes SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18 Control file created.   SQL> oradebug setmypid; Statement processed. SQL> oradebug dump controlf 3; Statement processed. SQL> oradebug tracefile_name; /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_1867.trc

尝试数据库恢复

SQL>  recover database using backup controlfile; ORA-00279: change 2118981 generated at 07/13/2012 03:01:42 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_5_788497123.dbf ORA-00280: change 2118981 for thread 1 is in sequence #5   Specify log: {<RET>=suggested | filename | AUTO | CANCEL}   ORA-00308: cannot open archived log '/u01/oracle/oradata/archivelog/ora11g/1_5_788497123.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3   SQL> SQL> select to_char(2118981, 'xxxxxxx' ) from dual;   TO_CHAR(2118981, ----------------    205545   SQL> set linesize 150 SQL> select file#,to_char(checkpoint_change#, '9999999999999999' ) "SCN" ,    2  to_char(RESETLOGS_CHANGE#, '9999999999999999' ) "RESETLOGS SCN" ,FUZZY    3  from v$datafile_header;         FILE# SCN                                RESETLOGS SCN                      FUZZY ---------- ---------------------------------- ---------------------------------- ------           1           2118981                            2118577                  YES           2           2118981                            2118577                  YES           3           2118981                            2118577                  YES           4           2118981                            2118577                  YES           6           2118981                            2118577                  YES   SQL> select file#,to_char(checkpoint_change#, '999999999999999' ) "SCN" ,    2  to_char(last_change#, '999999999999999' ) "STOP_SCN" from v$datafile;         FILE# SCN                              STOP_SCN ---------- -------------------------------- --------------------------------           1          2118981           2          2118981           3          2118981           4          2118981           6          2118981

分析 controlf 3 dump文件

*************************************************************************** DATABASE ENTRY ***************************************************************************   ( size = 316, compat size = 316, section max = 1, section in -use = 1,    last -recid= 0, old-recno = 0, last -recno = 0)   (extent = 1, blkno = 1, numrecs = 1)   07/13/2012 03:24:51   DB Name "ORA11G"   Database flags = 0x00400102 0x00001000   Controlfile Creation Timestamp  07/13/2012 03:24:51   Incmplt recovery scn: 0x0000.00000000   Resetlogs scn: 0x0000.002053b1 Resetlogs Timestamp  07/13/2012 02:58:43   Prior resetlogs scn: 0x0000.0016eaab Prior resetlogs Timestamp  05/01/2012 13:14:57   Redo Version: compatible=0xb200000   #Data files = 5, #Online files = 5   Database checkpoint : Thread=1 scn: 0x0000.00205543     *************************************************************************** DATA FILE RECORDS ***************************************************************************   ( size = 520, compat size = 520, section max = 100, section in -use = 6,    last -recid= 0, old-recno = 0, last -recno = 0)   (extent = 1, blkno = 11, numrecs = 100) DATA FILE #1:    name #8: /u01/oracle/oradata/ora11g/system01.dbf creation size =0 block size =8192 status=0x12 head=8 tail=8 dup=1   tablespace 0, index =1 krfil=1 prev_file=0   unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00   Checkpoint cnt:760 scn: 0x0000.00205545 07/13/2012 03:01:42   Stop scn: 0xffff.ffffffff 07/13/2012 03:24:51   Creation Checkpointed at scn:  0x0000.00000007 09/18/2011 17:33:47

完成恢复

SQL> recover database using backup controlfile; ORA-00279: change 2118981 generated at 07/13/2012 03:01:42 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/archivelog/ora11g/1_5_788497123.dbf ORA-00280: change 2118981 for thread 1 is in sequence #5   Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/oracle/oradata/ora11g/redo02.log Log applied. Media recovery complete.

关于重建控制文件后使用using backup总结:重建控制文件后,恢复的起点是datafile header scn 最小值,需要改scn之后的所有日志

转载：http://www.xifenfei.com/3487.html