昨天有人问到重新创建控制文件后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综合这一系列信息得出了需要归档文件.