联系：手机/微信(+86 17813235971) QQ(107644445)
标题：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之后的所有日志
加我QQ
通过这里可以看出来:
datafile header的Checkpointed scn(00205545)>controfile datafile的Checkpoint scn(002053b4) controfile datafile的checkpiont count(752)
所以在数据库open的时候会报ORA-01207错误
==================================
是不是这样？
datafile header的Checkpointed scn(002053b4)>controfile datafile的Checkpoint scn(002053b1)