備份恢復原理的實踐

看到warehouse的精華，做了點實驗，記錄一下。

http://www.itpub.net/thread-1065138-1-1.html

[@more@]

checkpoint_change#記錄在不同的地方,當checkpoint完成後,這幾個地方的checkpoint_change#就會更新為一致,如出現不一致的情況下,就需要進行恢復,先看看哪些地方儲存著checkpoint_change#

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> set linesize 1000
SQL> alter system checkpoint;

System altered.

--controlfile記錄的checkpoint_change#

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1477123

--datafile中記錄的checkpoint_change#

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ -------------------
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30

9 rows selected.

--controlfile中記錄的每一個datafile的checkpoint_change#

SQL> select checkpoint_change#,checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ -------------------
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30
1477123 2008-11-13 12:23:30

9 rows selected.

--controlfile中記錄的redo的checkpoint_change#

SQL> select checkpoint_change#,checkpoint_time from v$thread;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ -------------------
1477123 2008-11-13 12:23:30

下面shutdown 掉進行一次冷備份

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1477624

SQL> alter database open;

Database altered.

SQL> create table tt(
2 scnno number,
3 des varchar2(1));

Table created.

SQL> insert into tt values(dbms_flashback.get_system_change_number,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select group#,status,sequence#,archived,first_change# from v$log;

GROUP# STATUS SEQUENCE# ARC FIRST_CHANGE#
---------- ---------------- ---------- --- -------------
1 INACTIVE 44 YES 1334848
2 CURRENT 46 NO 1353177
3 INACTIVE 45 YES 1343921

x$kcvfh是v$datafile_header的源,可以透過v$fixed_view_definition得知.

x$kcvfh.FHRBA_SEQ--the log file sequence number
x$kcvfh.FHRBA_BNO--the log file block number
x$kcvfh.FHRBA_BOF--the bytes offset into the block at which the redo record start
經常看到的rba就是由這三部分組成的,透過rba在恢復時就能準備的知道需要哪個日誌檔案,哪個block以及在這個日誌block上從哪個byte開始讀取恢復

SQL> select hxfil,fhrba_seq,fhrba_bno,fhrba_bof from x$kcvfh;

HXFIL FHRBA_SEQ FHRBA_BNO FHRBA_BOF
---------- ---------- ---------- ----------
1 46 94580 16
2 46 94580 16
3 46 94580 16
4 46 94580 16
5 46 94580 16
6 46 94580 16
7 46 94580 16
8 46 94580 16
9 46 94580 16

SQL> alter system switch logfile;

System altered.

SQL> select group#,status,sequence#,archived,first_change# from v$log;

GROUP# STATUS SEQUENCE# ARC FIRST_CHANGE#
---------- ---------------- ---------- --- -------------
1 CURRENT 47 NO 1478141
2 ACTIVE 46 YES 1353177
3 INACTIVE 45 YES 1343921

SQL> select hxfil,fhrba_seq,fhrba_bno,fhrba_bof from x$kcvfh;

HXFIL FHRBA_SEQ FHRBA_BNO FHRBA_BOF
---------- ---------- ---------- ----------
1 46 94580 16
2 46 94580 16
3 46 94580 16
4 46 94580 16
5 46 94580 16
6 46 94580 16
7 46 94580 16
8 46 94580 16
9 46 94580 16

9 rows selected.

切換過logfile後發現資料檔案頭資訊還沒有更改,是因為還沒有checkpoint操作強制checkpoint過後,發現v$log的group 2的狀態從ACTIVE改為INACTIVE了,說明此redo log已應用到datafile,檢視x$kcvfh同樣可以看到變化.

SQL> alter system checkpoint;

System altered.

SQL> select group#,status,sequence#,archived,first_change# from v$log;

GROUP# STATUS SEQUENCE# ARC FIRST_CHANGE#
---------- ---------------- ---------- --- -------------
1 CURRENT 47 NO 1478141
2 INACTIVE 46 YES 1353177
3 INACTIVE 45 YES 1343921

SQL> select hxfil,fhrba_seq,fhrba_bno,fhrba_bof from x$kcvfh;

HXFIL FHRBA_SEQ FHRBA_BNO FHRBA_BOF
---------- ---------- ---------- ----------
1 47 75 16
2 47 75 16
3 47 75 16
4 47 75 16
5 47 75 16
6 47 75 16
7 47 75 16
8 47 75 16
9 47 75 16

9 rows selected.

SQL> insert into tt values(dbms_flashback.get_system_change_number,'b');

1 row created.

SQL> commit;

Commit complete.

SQL> select group#,status,sequence#,archived,first_change# from v$log;

GROUP# STATUS SEQUENCE# ARC FIRST_CHANGE#
---------- ---------------- ---------- --- -------------
1 CURRENT 47 NO 1478141
2 INACTIVE 46 YES 1353177
3 INACTIVE 45 YES 1343921

SQL> alter system switch logfile;

System altered.

SQL> select group#,status,sequence#,archived,first_change# from v$log;

GROUP# STATUS SEQUENCE# ARC FIRST_CHANGE#
---------- ---------------- ---------- --- -------------
1 ACTIVE 47 YES 1478141
2 INACTIVE 46 YES 1353177
3 CURRENT 48 NO 1478379

SQL> insert into tt values(dbms_flashback.get_system_change_number,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> select group#,status,sequence#,archived,first_change# from v$log;

GROUP# STATUS SEQUENCE# ARC FIRST_CHANGE#
---------- ---------------- ---------- --- -------------
1 ACTIVE 47 YES 1478141
2 INACTIVE 46 YES 1353177
3 CURRENT 48 NO 1478379

SQL> alter system switch logfile;

System altered.

上次分別插入三條記錄,每條記錄都在不同的log中.

SQL> select * from tt;

SCNNO D
---------- -
1478103 a
1478364 b
1478381 c

按照透過理解備份恢復的原理,事務對應的scn落在哪個sequence#的FIRST_CHANGE#和NEXT_CHANGE#中就會應用哪個日誌.
所以記錄a在恢復時應該應用到sequence#為46的logfile,b對應47,c對應48.

SQL> select sequence#,first_change#,next_change# from v$archived_log
2 where sequence# in (46,47,48);

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
46 1353177 1478141
47 1478141 1478379
48 1478379 1478394

SQL> select group#,status,sequence#,archived,first_change# from v$log;

GROUP# STATUS SEQUENCE# ARC FIRST_CHANGE#
---------- ---------------- ---------- --- -------------
1 INACTIVE 47 YES 1478141
2 CURRENT 49 NO 1478394
3 INACTIVE 48 YES 1478379

SQL> select hxfil,fhrba_seq,fhrba_bno,fhrba_bof from x$kcvfh;

HXFIL FHRBA_SEQ FHRBA_BNO FHRBA_BOF
---------- ---------- ---------- ----------
1 49 2 16
2 49 2 16
3 49 2 16
4 49 2 16
5 49 2 16
6 49 2 16
7 49 2 16
8 49 2 16
9 49 2 16

9 rows selected.

下面先看dump一下datafile header的內容,看看記錄了哪些內容

SQL> alter session set events 'immediate trace name FILE_HDRS level 12';

內容摘錄如下:

DATA FILE #1:
(name #12) /u01/app/oradata/orcl/system01.dbf
...
Checkpointed at scn: 0x0000.00168efa 11/13/2008 12:58:51
thread:1 rba:(0x31.2.10)

DATA FILE #2:
(name #11) /u01/app/oradata/orcl/undotbs01.dbf
...
Checkpointed at scn: 0x0000.00168efa 11/13/2008 12:58:51
thread:1 rba:(0x31.2.10)

DATA FILE #3:
(name #10) /u01/app/oradata/orcl/sysaux01.dbf
...
Checkpointed at scn: 0x0000.00168efa 11/13/2008 12:58:51
thread:1 rba:(0x31.2.10)
....
....

datafile header不僅記錄了scn而且記錄了rba的資訊.scn: 0x0000.00168efa轉換成十進位制是1478394,正好是48號日誌檔案的結尾下一個

logfile的起始位置.而 rba:(0x31.2.10)的ox31轉換成十進位制後為49,2轉換為十進位制是2,10轉換後為16,正好是log的sequence,block和從塊的哪個位置開始讀的資訊.

再dump出control file的內容看看:

SQL> alter session set events 'immediate trace name CONTROLF level 12';

log file的記錄如下,記錄了三個redo file的情況,注意LOG FILE #2是當前的redo,Low scn:為00168efa, Next Scn為無窮大,它上一個redo為

Log File #3.

***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
(name #2) /u01/app/oradata/orcl/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x0000002f hws: 0x2 bsz: 512 nab: 0xd3 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0014a5d9
Low scn: 0x0000.00168dfd 11/13/2008 12:50:32
Next scn: 0x0000.00168eeb 11/13/2008 12:58:32
LOG FILE #2:
(name #3) /u01/app/oradata/orcl/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000031 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00168eeb
Low scn: 0x0000.00168efa 11/13/2008 12:58:51
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
(name #1) /u01/app/oradata/orcl/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000030 hws: 0x2 bsz: 512 nab: 0xb flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00168dfd
Low scn: 0x0000.00168eeb 11/13/2008 12:58:32
Next scn: 0x0000.00168efa 11/13/2008 12:58:51

***************************************************************************

***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 428, compat size = 428, section max = 1600, section in-use = 10,
last-recid= 16, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 1600)
DATA FILE #1:
(name #12) /u01/app/oradata/orcl/system01.dbf
creation size=0 block size=8192 status=0xe head=12 tail=12 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:194 scn: 0x0000.00168efa 11/13/2008 12:58:51
Stop scn: 0xffff.ffffffff 11/13/2008 12:27:32
Creation Checkpointed at scn: 0x0000.00000009 06/30/2005 19:10:11
thread:0 rba:(0x0.0.0)

DATA FILE #2:
(name #11) /u01/app/oradata/orcl/undotbs01.dbf
creation size=25600 block size=8192 status=0xe head=11 tail=11 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:11 scn: 0x0000.00168efa 11/13/2008 12:58:51
Stop scn: 0xffff.ffffffff 11/13/2008 12:27:32
Creation Checkpointed at scn: 0x0000.0014403b 10/27/2008 20:24:26
thread:1 rba:(0x2b.5b7f.10)

......

checkpoint的scn是00168efa,和datafile header是一致的,由於datafile是online的,所以stop Scn 是無窮大.
可以猜測出如果datafile header的資訊於control file的資訊不一致的話,就需要進行恢復了.

正常關閉資料庫,將所有備份的datafile複製回來覆蓋現在的datafile.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@rhel131 orcl]$ cp ../backup/*.dbf .

SQL> startup
ORACLE instance started.

Total System Global Area 322961408 bytes
Fixed Size 1267284 bytes
Variable Size 159386028 bytes
Database Buffers 159383552 bytes
Redo Buffers 2924544 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oradata/orcl/system01.dbf'

由於控制檔案和redo log是新的,而datafile是舊的備份,而control file中記錄的checkpoint_change#是1481926,而datafile header中記錄的

checkpoint_change#是1477624

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1481926

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOIN
------------------ ---------
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08

9 rows selected.

SQL> select checkpoint_change#,checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# CHECKPOIN
------------------ ---------
1481926 13-NOV-08
1481926 13-NOV-08
1481926 13-NOV-08
1481926 13-NOV-08
1481926 13-NOV-08
1481926 13-NOV-08
1481926 13-NOV-08
1481926 13-NOV-08
1481926 13-NOV-08

9 rows selected.

SQL> select checkpoint_change#,checkpoint_time from v$thread;

CHECKPOINT_CHANGE# CHECKPOIN
------------------ ---------
1481926 13-NOV-08

由於x$kcvfh是v$datafile_header的源,所以此檢視記錄的也是舊的值
SQL> select hxfil,fhrba_seq,fhrba_bno,fhrba_bof from x$kcvfh;

HXFIL FHRBA_SEQ FHRBA_BNO FHRBA_BOF
---------- ---------- ---------- ----------
1 46 94580 16
2 46 94580 16
3 46 94580 16
4 46 94580 16
5 46 94580 16
6 46 94580 16
7 46 94580 16
8 46 94580 16
9 46 94580 16

9 rows selected.

試著恢復一下:

SQL> recovery database;
SP2-0734: unknown command beginning "recovery d..." - rest of line ignored.
SQL> recover database;
ORA-00279: change 1477624 generated at 11/13/2008 12:27:32 needed for thread 1
ORA-00289: suggestion :
/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_46_%u_.arc
ORA-00280: change 1477624 for thread 1 is in sequence #46

Specify log: {=suggested | filename | AUTO | CANCEL}

這裡為什麼會從1477624開始恢復db,原因就是備份時db發生檢查點對應的checkpoint_change#就是1477624,這個值在上面可以找得到.
那sequence #46是如何得來的呢?

再來dump出現在的datafile header看看:
SQL> alter session set events 'immediate trace name FILE_HDRS level 12';

下面只以datafile 1 為例.

DATA FILE #1:
(name #12) /u01/app/oradata/orcl/system01.dbf
creation size=0 block size=8192 status=0xe head=12 tail=12 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:195 scn: 0x0000.00169cc6 11/13/2008 13:35:44
Stop scn: 0x0000.00169cc6 11/13/2008 13:35:44

這裡的scn: 0x0000.00169cc6轉成十進位制是1481926,發現這個值是來自控制檔案,dump出控制檔案看看,資訊完全一致.

SQL> alter session set events 'immediate trace name CONTROLF level 12';

DATA FILE #1:
(name #12) /u01/app/oradata/orcl/system01.dbf
creation size=0 block size=8192 status=0xe head=12 tail=12 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:195 scn: 0x0000.00169cc6 11/13/2008 13:35:44
Stop scn: 0x0000.00169cc6 11/13/2008 13:35:44

而下面的一段才是真正的datafile header資訊:

Checkpointed at scn: 0x0000.00168bf8 11/13/2008 12:27:32
thread:1 rba:(0x2e.17174.10)

0x2e轉成十進位制就是46,17174轉成十進位制是94580,說明從sequence為46的redo的94580塊開始恢復

SQL> recover database;
ORA-00279: change 1477624 generated at 11/13/2008 12:27:32 needed for thread 1
ORA-00289: suggestion :
/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_46_%u_.arc
ORA-00280: change 1477624 for thread 1 is in sequence #46

pecify log: {=suggested | filename | AUTO | CANCEL}
Auto
Log applied.
Media recovery complete.

輸入auto或filename的名字後介質恢復就完成了.這裡不僅自動找到已歸檔的redo file而且也能自動找到redo log,這是因為controlfile中記

錄了redo的資訊.

有點吶悶,為何就直接完成了,應該還要繼續恢復47和48後才完成.可能是10g版本的升級吧.

SQL> alter database open;

Database altered.

開啟資料庫後,所用的checkpoint_change#都是一致的了.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1482650

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ -------------------
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08

9 rows selected.

SQL> select checkpoint_change#,checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ -------------------
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08
1482650 2008-11-13 14:19:08

9 rows selected.

SQL> select checkpoint_change#,checkpoint_time from v$thread;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ -------------------
1482650 2008-11-13 14:19:08

如果datafile和controlfile是一樣的,只有redo log是新的情況下,會怎麼樣?

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@rhel131 orcl]$ cp ../backup/*.dbf .
[oracle@rhel131 orcl]$ cp ../backup/*.ctl .

SQL> startup
ORACLE instance started.

Total System Global Area 322961408 bytes
Fixed Size 1267284 bytes
Variable Size 159386028 bytes
Database Buffers 159383552 bytes
Redo Buffers 2924544 bytes
Database mounted.
ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: '/u01/app/oradata/orcl/redo01.log'

為何會出現這樣的提示?看看controlfile和datafile header中記錄的checkpoint_change#都是一致和,且v$thread和v$log的資訊也是舊的,說明這些資訊都是從控制檔案中讀到的.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1477624

SQL> select checkpoint_change#,checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# CHECKPOIN
------------------ ---------
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08

9 rows selected.

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOIN
------------------ ---------
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08
1477624 13-NOV-08

9 rows selected.

SQL> select checkpoint_change#,checkpoint_time from v$thread;

CHECKPOINT_CHANGE# CHECKPOIN
------------------ ---------
1477624 13-NOV-08

SQL> select group#,status,sequence#,archived,first_change# from v$log;

GROUP# STATUS SEQUENCE# ARC FIRST_CHANGE#
---------- ---------------- ---------- --- -------------
1 INACTIVE 44 YES 1334848
3 INACTIVE 45 YES 1343921
2 CURRENT 46 NO 1353177

SQL> alter session set events 'immediate trace name REDOHDR level 12';

Session altered.

dump出來的內容如下,每段的開頭部分看到三個seq分別為0x0000002c ,0x0000002e,0x0000002d,轉成十進位制後為44,46,45,low scn和next scn說明scn的區間,log file #2的next snc為ffffffff說明是當前的redo.這些資訊和當前的control完全一致,是從controlfile 中得到的,並不是真正的redo資訊.
從FILE HEADER開始後的資訊才是真正的redo資訊.
以 log file #1為例,Low scn: 0x0000.00168dfd ,說明開始的scn為1478141,Next scn: 0x0000.00168eeb表示結束的scn為1478379.

LOG FILE #1:
(name #2) /u01/app/oradata/orcl/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x0000002c hws: 0x2 bsz: 512 nab: 0x186d6 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.001407e5
Low scn: 0x0000.00145e40 10/27/2008 21:19:50
Next scn: 0x0000.001481b1 10/28/2008 00:00:53
FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=1193448104=0x472292a8, Db Name='ORCL'
Activation ID=1196000613=0x47498565
Control Seq=10512=0x2910, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000047, SCN 0x000000168dfd-0x000000168eeb"
thread: 1 nab: 0xd3 seq: 0x0000002f hws: 0x4 eot: 0 dis: 0
reset logs count: 0x27d46e4a scn: 0x0000.000e429b
Low scn: 0x0000.00168dfd 11/13/2008 12:50:32
Next scn: 0x0000.00168eeb 11/13/2008 12:58:32
Enabled scn: 0x0000.000e429b 10/16/2008 04:21:30
Thread closed scn: 0x0000.00168dfd 11/13/2008 12:50:32
Disk cksum: 0x8305 Calc cksum: 0x8305
Terminal Recovery Stop scn: 0x0000.00000000
Terminal Recovery Stamp 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 19 blocks
Miscellaneous flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
LOG FILE #2:
(name #3) /u01/app/oradata/orcl/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x0000002e hws: 0x2 bsz: 512 nab: 0x17174 flg: 0x8 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.001481b1
Low scn: 0x0000.0014a5d9 10/28/2008 03:58:13
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=1193448104=0x472292a8, Db Name='ORCL'
Activation ID=1196000613=0x47498565
Control Seq=10514=0x2912, File size=102400=0x19000
File Number=2, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000049, SCN 0x000000168efa-0xffffffffffff"
thread: 1 nab: 0x5400 seq: 0x00000031 hws: 0x8 eot: 1 dis: 0
reset logs count: 0x27d46e4a scn: 0x0000.000e429b
Low scn: 0x0000.00168efa 11/13/2008 12:58:51
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.000e429b 10/16/2008 04:21:30
Thread closed scn: 0x0000.0016a113 11/13/2008 14:21:27
Disk cksum: 0x1b82 Calc cksum: 0x1b82
Terminal Recovery Stop scn: 0x0000.00000000
Terminal Recovery Stamp 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 1874 blocks
Miscellaneous flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
LOG FILE #3:
(name #1) /u01/app/oradata/orcl/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x0000002d hws: 0x2 bsz: 512 nab: 0x3d0e flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00145e40
Low scn: 0x0000.001481b1 10/28/2008 00:00:53
Next scn: 0x0000.0014a5d9 10/28/2008 03:58:13
FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=1193448104=0x472292a8, Db Name='ORCL'
Activation ID=1196000613=0x47498565
Control Seq=10512=0x2910, File size=102400=0x19000
File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000048, SCN 0x000000168eeb-0x000000168efa"
thread: 1 nab: 0xb seq: 0x00000030 hws: 0x4 eot: 0 dis: 0
reset logs count: 0x27d46e4a scn: 0x0000.000e429b
Low scn: 0x0000.00168eeb 11/13/2008 12:58:32
Next scn: 0x0000.00168efa 11/13/2008 12:58:51
Enabled scn: 0x0000.000e429b 10/16/2008 04:21:30
Thread closed scn: 0x0000.00168eeb 11/13/2008 12:58:32
Disk cksum: 0x8832 Calc cksum: 0x8832
Terminal Recovery Stop scn: 0x0000.00000000
Terminal Recovery Stamp 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 3 blocks
Miscellaneous flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000

那麼在redo的header上是否記錄controlfile的資訊?應該不記錄.上面的轉儲redo header資訊實際上是從controlfile裡讀redo的路徑以及及一些相關資訊再到redo的header上讀真真的redo內容.
由於controlfile中記錄的redo資訊與redo header上記錄的資訊不符,所以開啟db時出現了上面的提示.

SQL> recover database using backup controlfile;
ORA-00279: change 1477624 generated at 11/13/2008 12:27:32 needed for thread 1
ORA-00289: suggestion :
/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_46_%u_.arc
ORA-00280: change 1477624 for thread 1 is in sequence #46

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1478141 generated at 11/13/2008 12:50:32 needed for thread 1
ORA-00289: suggestion :
/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_47_%u_.arc
ORA-00280: change 1478141 for thread 1 is in sequence #47
ORA-00278: log file
'/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_46_4kqdjrt4_.ar
c' no longer needed for this recovery

ORA-00279: change 1478379 generated at 11/13/2008 12:58:32 needed for thread 1
ORA-00289: suggestion :
/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_48_%u_.arc
ORA-00280: change 1478379 for thread 1 is in sequence #48
ORA-00278: log file
'/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_47_4kqdzs0j_.ar
c' no longer needed for this recovery

ORA-00279: change 1478394 generated at 11/13/2008 12:58:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_49_%u_.arc
ORA-00280: change 1478394 for thread 1 is in sequence #49
ORA-00278: log file
'/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_48_4kqf0csk_.ar
c' no longer needed for this recovery

ORA-00308: cannot open archived log
'/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_49_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

恢復還沒有全部完成,現在datafile header上的sequence#是49,而controlfile中記錄的是46.因當controlfile不是最新時,此時恢復是無法自

動應用redo的,需要我們挨個輸入redo來嘗試恢復.

SQL> select hxfil,fhrba_seq,fhrba_bno,fhrba_bof from x$kcvfh;

HXFIL FHRBA_SEQ FHRBA_BNO FHRBA_BOF
---------- ---------- ---------- ----------
1 49 2 0
2 49 2 0
3 49 2 0
4 49 2 0
5 49 2 0
6 49 2 0
7 49 2 0
8 49 2 0
9 49 2 0

9 rows selected.

SQL> select sequence#,last_redo_sequence# from v$thread;

SEQUENCE# LAST_REDO_SEQUENCE#
---------- -------------------
46 46

SQL> recover database using backup controlfile;
ORA-00279: change 1478394 generated at 11/13/2008 12:58:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_49_%u_.arc
ORA-00280: change 1478394 for thread 1 is in sequence #49

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oradata/orcl/redo01.log
ORA-00310: archived log contains sequence 47; sequence 49 required
ORA-00334: archived log: '/u01/app/oradata/orcl/redo01.log'

SQL> recover database using backup controlfile;
ORA-00279: change 1478394 generated at 11/13/2008 12:58:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/flash_recovery_area/ORCL/archivelog/2008_11_13/o1_mf_1_49_%u_.arc
ORA-00280: change 1478394 for thread 1 is in sequence #49

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oradata/orcl/redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.