ITPub博客

首页 > 数据库 > Oracle > 备份恢复原理的实践

备份恢复原理的实践

原创 Oracle 作者:jolly10 时间:2008-11-13 16:55:41 0 删除 编辑

看到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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/271283/viewspace-1013338/,如需转载,请注明出处,否则将追究法律责任。

上一篇: undo的初步研究
下一篇: Oracle常用dump命令
请登录后发表评论 登录
全部评论

注册时间:2008-02-20

  • 博文量
    263
  • 访问量
    769325