ITPub博客

恢复测试:拥有当时的全部归档,控制文件,恢复丢失的数据文件。

原创 Linux操作系统 作者:oracle_ace 时间:2007-12-27 13:47:50 0 删除 编辑

测试如下:

SQL> archive log list
数据库日志模式            存档模式
自动存档             启用
存档终点            D:\oracle\archived_dest
最早的概要日志序列     0
下一个存档日志序列   1
当前日志序列           1

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\ICMNLSDB\SYSTEM01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\UNDOTBS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\INDX01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\TOOLS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\USERS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\ALAN01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\MY_BLOCK.DBF
D:\ORACLE\ORADATA\ICMNLSDB\MY_BLOCK02.DBF

已选择8行。

SQL> create tablespace mytest datafile 'D:\ORACLE\ORADATA\ICMNLSDB\test01.dbf' size 5m;

表空间已创建。

SQL> alter system switch logfile;

系统已更改。

SQL> create table my_t tablespace mytest as select * from dba_users;

表已创建。

SQL> alter system switch logfile;

系统已更改。

SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> exit

---将我们新建立的TEST01.DBF文件改名,来模拟文件丢失

之后........
D:\oracle\oradata\icmnlsdb>sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on 星期四 12月 27 13:33:35 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba;
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  118562308 bytes
Fixed Size                   454148 bytes
Variable Size              75497472 bytes
Database Buffers           41943040 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 9 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 9: 'D:\ORACLE\ORADATA\ICMNLSDB\TEST01.DBF'

SQL> select name from v$datafile;

NAME
--------------------------------------------
D:\ORACLE\ORADATA\ICMNLSDB\SYSTEM01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\UNDOTBS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\INDX01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\TOOLS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\USERS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\ALAN01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\MY_BLOCK.DBF
D:\ORACLE\ORADATA\ICMNLSDB\MY_BLOCK02.DBF
D:\ORACLE\ORADATA\ICMNLSDB\TEST01.DBF

已选择9行。

SQL> alter database create datafile 'D:\ORACLE\ORADATA\ICMNLSDB\TEST01.DBF';

数据库已更改。

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> recover datafile 'D:\ORACLE\ORADATA\ICMNLSDB\TEST01.DBF';
完成介质恢复。

SQL> alter database open;

数据库已更改。

分析:
此次测试的过程,因为有当前的控制文件,而且控制文件中包含了所丢失的额外数据文件信息test01.dbf,所以可以通过alter database create datafile ''PATH" 方式重新创建数据文件,同时我们还可以通过控制文件中记录的数据文件信息、SCN、checkpoint检查点等信息,应用归档日志进行恢复,因此可以全部完成完全恢复.

截取四段的file hdrs的信息,供参考(大家可以注意一下标记的字体信息):

文件改名前的状态信息
-----------------------------
DATA FILE #9:
  (name #13) D:\ORACLE\ORADATA\ICMNLSDB\TEST01.DBF
creation size=640 block size=8192 status=0xe head=13 tail=13 dup=1
 tablespace 8, index=9 krfil=9 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:5 scn: 0x0000.000299f7 12/27/2007 13:56:32
 Stop scn: 0xffff.ffffffff 12/27/2007 13:54:53
 Creation Checkpointed at scn:  0x0000.00024ba6 12/27/2007 13:54:24
 thread:1 rba:(0x1.3d5.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
 Offline scn: 0x0000.00000000 prev_range: 0
 Online Checkpointed at scn:  0x0000.00000000
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
 Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED
 FILE HEADER:
 Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000
 Db ID=862305779=0x3365bdf3, Db Name='ICMNLSDB'
 Activation ID=0=0x0
 Control Seq=229=0xe5, File size=640=0x280
 File Number=9, Blksiz=8192, File Type=3 DATA
Tablespace #8 - TEST  rel_fn:9
Creation   at   scn: 0x0000.00024ba6 12/27/2007 13:54:24
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2649a79a scn: 0x0000.0001edd2 recovered at 12/27/2007 13:56:29
 status:0x4 root dba:0x00000000 chkpt cnt: 5 ctl cnt:4
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.000299f7 12/27/2007 13:56:32
 thread:1 rba:(0x4.2.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
Backup Checkpointed at scn:  0x0000.00000000
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000.00000000
Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00
Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00
DUMP OF TEMP FILES: 1 files in database

文件改名后,数据处于mounted状态的信息
---------------------------------------------------------
DATA FILE #9:
  (name #13) D:\ORACLE\ORADATA\ICMNLSDB\TEST01.DBF
creation size=640 block size=8192 status=0xe head=13 tail=13 dup=1
 tablespace 8, index=9 krfil=9 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:6 scn: 0x0000.00029a91 12/27/2007 13:58:03
 Stop scn: 0x0000.00029a91 12/27/2007 13:58:03
 Creation Checkpointed at scn:  0x0000.00024ba6 12/27/2007 13:54:24
 thread:1 rba:(0x1.3d5.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
 Offline scn: 0x0000.00000000 prev_range: 0
 Online Checkpointed at scn:  0x0000.00000000
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
 Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED
ORA-01157: 无法标识/锁定数据文件 9 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 9: 'D:\ORACLE\ORADATA\ICMNLSDB\TEST01.DBF'
*** Error 1157 in open/read file # 9 ***
DUMP OF TEMP FILES: 1 files in database

文件重新通过create datafile重建过后的信息
----------------------------------------------------------
DATA FILE #9:
  (name #13) D:\ORACLE\ORADATA\ICMNLSDB\TEST01.DBF
creation size=640 block size=8192 status=0xe head=13 tail=13 dup=1
 tablespace 8, index=9 krfil=9 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:6 scn: 0x0000.00029a91 12/27/2007 13:58:03
 Stop scn: 0x0000.00029a91 12/27/2007 13:58:03
 Creation Checkpointed at scn:  0x0000.00024ba6 12/27/2007 13:54:24
 thread:1 rba:(0x1.3d5.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
 Offline scn: 0x0000.00000000 prev_range: 0
 Online Checkpointed at scn:  0x0000.00000000
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
 Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED
 FILE HEADER:
 Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000
 Db ID=862305779=0x3365bdf3, Db Name='ICMNLSDB'
 Activation ID=0=0x0
 Control Seq=233=0xe9, File size=640=0x280
 File Number=9, Blksiz=8192, File Type=3 DATA
Tablespace #8 - TEST  rel_fn:9
Creation   at   scn: 0x0000.00024ba6 12/27/2007 13:54:24
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2649a79a scn: 0x0000.0001edd2 recovered at 01/01/1988 00:00:00
 status:0x0 root dba:0x004001a1 chkpt cnt: 1 ctl cnt:1
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.00024ba6 12/27/2007 13:54:24
 thread:1 rba:(0x1.3d5.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
Backup Checkpointed at scn:  0x0000.00000000
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000.00000000
Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00
Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00
DUMP OF TEMP FILES: 1 files in database

完全介质恢复后的信息
------------------------------
DATA FILE #9:
  (name #13) D:\ORACLE\ORADATA\ICMNLSDB\TEST01.DBF
creation size=640 block size=8192 status=0xe head=13 tail=13 dup=1
 tablespace 8, index=9 krfil=9 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:8 scn: 0x0000.00029a92 12/27/2007 14:01:09
 Stop scn: 0xffff.ffffffff 12/27/2007 13:58:03
 Creation Checkpointed at scn:  0x0000.00024ba6 12/27/2007 13:54:24
 thread:1 rba:(0x1.3d5.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
 Offline scn: 0x0000.00000000 prev_range: 0
 Online Checkpointed at scn:  0x0000.00000000
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
 Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED
 FILE HEADER:
 Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000
 Db ID=862305779=0x3365bdf3, Db Name='ICMNLSDB'
 Activation ID=0=0x0
 Control Seq=238=0xee, File size=640=0x280
 File Number=9, Blksiz=8192, File Type=3 DATA
Tablespace #8 - TEST  rel_fn:9
Creation   at   scn: 0x0000.00024ba6 12/27/2007 13:54:24
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
 reset logs count:0x2649a79a scn: 0x0000.0001edd2 recovered at 12/27/2007 14:00:56
 status:0x4 root dba:0x004001a1 chkpt cnt: 8 ctl cnt:7
begin-hot-backup file size: 0
Checkpointed at scn:  0x0000.00029a92 12/27/2007 14:01:09
 thread:1 rba:(0x4.75.10)
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
Backup Checkpointed at scn:  0x0000.00000000
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000.00000000
Recovery fuzzy scn: 0x0000.00000000 12/27/2007 13:58:03
Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00
DUMP OF TEMP FILES: 1 files in database

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

请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    284
  • 访问量
    783393