oracle如何判断应用哪个archived log

    昨天有人问到重新创建控制文件后oracle如何知道该从那么日志开始恢复,这里牵涉到一些概念,我这里一并做答了.

    首先,我们知道重新创建了控制文件后所有的归档信息都无法在控制文件里面找到了,那么oracle怎么判断从哪个日志开始恢复呢.

    我们做一个controlfile dump来看一下

*** 2005-07-27 10:52:25.931
*** SERVICE NAME:() 2005-07-27 10:52:25.931
*** SESSION ID:(159.5) 2005-07-27 10:52:25.931
DUMP OF CONTROL FILES, Seq # 439 = 0x1b7
 V10 STYLE FILE HEADER:
        Compatibility Vsn = 169869568=0xa200100
        Db ID=956585232=0x39045510, Db Name='DBTEST'
        Activation ID=0=0x0
        Control Seq=439=0x1b7, File size=450=0x1c2
        File Number=0, Blksiz=16384, File Type=4 BACKUP CONTROL

***************************************************************************
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/27/2005 10:39:06
 DB Name "DBTEST"
 Database flags = 0x00400107 0x00001000
 Controlfile Creation Timestamp  07/27/2005 10:39:07
 Incmplt recovery scn: 0x0000.000a75f8
 Resetlogs scn: 0x0000.0006ce7b Resetlogs Timestamp  07/15/2005 13:33:07
 Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp  06/30/2005 19:09:40
 Redo Version: compatible=0xa200100
 #Data files = 4, #Online files = 4
 Database checkpoint: Thread=0 scn: 0x0000.00000000
 Threads: #Enabled=1, #Open=0, Head=0, Tail=0

    省略一些内容......

***************************************************************************
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:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00
heartbeat: 564752585 mount id: 957645365

    可以看到重新创建控制文件后CHECKPOINT PROGRESS RECORDS这一块内容都被清零了

***************************************************************************
ARCHIVED LOG RECORDS
***************************************************************************
 (size = 584, compat size = 584, section max = 308, section in-use = 0,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 97, numrecs = 308)

    归档信息也被清除了

    我们试一下recover database

SQL> recover database using backup controlfile;
ORA-00279: change 685560 generated at 07/25/2005 16:12:30 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archive/dbtest/1_7_563722387.dbf
ORA-00280: change 685560 for thread 1 is in sequence #7

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    当我们进行recover时oracle提示需要1_7_563722387.dbf这个归档,scn为685560 ,但是控制文件里面并没有任何关于归档的记载.答案在数据文件头里面,来做一个datafile header dump

    ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 3;

    可以在datafile header dump里面发现

Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000009 06/30/2005 19:10:11
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2199b893 scn: 0x0000.0006ce7b reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2184ef74 scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 07/27/2005 11:25:03
 status:0x2000 root dba:0x00400179 chkpt cnt: 55 ctl cnt:54
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000a75f8 07/25/2005 16:12:30
thread:1 rba:(0x7.384.10)

    这里的rba代表了redo block addres,分为3段

    分别由

    the log file sequence number (4 bytes)

    the log file block number (4 bytes)

    the byte offset into the block at which the redo record starts (2 bytes)

    组成,从这里我们也可以看出datafile#1需要从sequence 7的日志文件开始恢复,所以oracle可以知道归档名字是"1_7_563722387.dbf",至于这里的563722387则代表了reset logs count,由于10g默认的log_archive_format为 %t_%s_%r.arc,所以这里多了一个563722387,在datafile header dump里面可以发现.

    reset logs count:0x2199b893

    oracle综合这一系列信息得出了需要归档文件.

查看本文来源