能得到eygle大师的指点真是幸运!
谢谢大师!
checkpoint cnt是检查数据文件是否来自与同一个版本,这个我明白了。
但是如果某个数据文件需要恢复时,我怎么才能看到他为什么要恢复,也就是要看到要恢复时候的现象,一下是我的测试:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
[oracle@www cicro]$ ls
cicrodb.dbf control03.ctl example01.dbf redo02.log test undotbstemp.dbf
cicrodb.dbf.old cwmlite01.dbf indx01.dbf redo03.log tools01.dbf users01.dbf
control01.ctl cws33db.dbf odm01.dbf system01.dbf undotbs01.dbf xdb01.dbf
control02.ctl drsys01.dbf redo01.log temp01.dbf undotbs02.dbf
[oracle@www cicro]$ cp cicrodb.dbf cicrodb.dbf.gjf
[oracle@www cicro]$ sqlplus "/as sysdba"
startup
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 9 17:38:48 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> ORACLE instance started.
Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter session set events 'immediate trace name CONTROLF level 10';
Session altered.
此时转存控制文件,信息如下:
(name #15)/opt/oracle/oradata/cicro/cicrodb.dbf
creation size=1280 block size=8192 status=0xe head=15 tail=15 dup=1
tablespace 12, index=12 krfil=11 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:126 scn: 0x0800.01952813 08/09/2006 17:38:53
Stop scn: 0xffff.ffffffff 08/09/2006 17:38:07
SQL>alter session set events 'immediate trace name file_hdrs level 10';
Session altered.
转存数据文件头,信息如下:
(name #15) /opt/oracle/oradata/cicro/cicrodb.dbf
creation size=1280 block size=8192 status=0xe head=15 tail=15 dup=1
tablespace 12, index=12 krfil=11 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:126 scn: 0x0800.01952813 08/09/2006 17:38:53
Stop scn: 0xffff.ffffffff 08/09/2006 17:38:07
此时, /opt/oracle/oradata/cicro/cicrodb.dbf文件的Checkpoint scn在控制文件
和数据文件头相同。
[oracle@www cicro]$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 9 17:39:33 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
SQL> alter system switch logfile;
System altered.
SQL> alter session set events 'immediate trace name CONTROLF level 10';
Session altered.
dump控制文件,信息如下
DATA FILE #1:
(name #15)/opt/oracle/oradata/cicro/cicrodb.dbf
creation size=0 block size=8192 status=0xe head=8 tail=8 dup=1
tablespace 0, index=6 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:312 scn: 0x0800.019529e4 08/09/2006 17:40:51
Stop scn: 0xffff.ffffffff 08/09/2006 17:38:07
SQL>alter session set events 'immediate trace name file_hdrs level 10';
Session altered.
转存数据文件头,信息如下:
DATA FILE #1:
(name #15) /opt/oracle/oradata/cicro/cicrodb.dbf
creation size=0 block size=8192 status=0xe head=8 tail=8 dup=1
tablespace 0, index=6 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:312 scn: 0x0800.019529e4 08/09/2006 17:40:51
Stop scn: 0xffff.ffffffff 08/09/2006 17:38:07
日志切换完成后, /opt/oracle/oradata/cicro/cicrodb.dbf文件的Checkpoint scn在控制文件和数据文件头相同。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
[oracle@www cicro]$ ls
cicrodb.dbf control02.ctl drsys01.dbf redo01.log temp01.dbf undotbs02.dbf
cicrodb.dbf.gjf control03.ctl example01.dbf redo02.log test undotbstemp.dbf
cicrodb.dbf.old cwmlite01.dbf indx01.dbf redo03.log tools01.dbf users01.dbf
control01.ctl cws33db.dbf odm01.dbf system01.dbf undotbs01.dbf xdb01.dbf
[oracle@www cicro]$ mv cicrodb.dbf cicrodb.dbf.bak
[oracle@www cicro]$ mv cicrodb.dbf.gjf cicrodb.dbf
更换cicrodb.dbf为原来备份的文件
[oracle@www cicro]$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 9 17:56:04 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter session set events 'immediate trace name file_hdrs level 10';
Session altered.
转存数据文件头,信息如下:
(name #15) /opt/oracle/oradata/cicro/cicrodb.dbf
creation size=1280 block size=8192 status=0xe head=15 tail=15 dup=1
tablespace 12, index=12 krfil=11 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:128 scn: 0x0800.01953281 08/09/2006 17:55:32
Stop scn: 0x0800.01953281 08/09/2006 17:55:32
[oracle@www cicro]$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 9 17:58:19 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
SQL> alter session set events 'immediate trace name CONTROLF level 10';
Session altered.
转存控制文件,信息如下:
(name #15) /opt/oracle/oradata/cicro/cicrodb.dbf
creation size=1280 block size=8192 status=0xe head=15 tail=15 dup=1
tablespace 12, index=12 krfil=11 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:128 scn: 0x0800.01953281 08/09/2006 17:55:32
Stop scn: 0x0800.01953281 08/09/2006 17:55:32
Creation Checkpointed at scn: 0x0000.01213333 12/28/2005 15:28:58
可以看到,/opt/oracle/oradata/cicro/cicrodb.dbf的scn在数据文件头和控制文件
中仍然相同,
这样的话,应该可以打开数据库。
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: '/opt/oracle/oradata/cicro/cicrodb.dbf'
却报/opt/oracle/oradata/cicro/cicrodb.dbf需要恢复,这个是怎么回事,请大师指点!
谢谢eygle!