如果你的系统有完善的备份，那么我下面写的内容你就可以直接跳过，完善的备份永远是针对此类问题的最有效措施！
这篇文章仅仅用于在没有有效备份，或者虽然有备份但归档日志有缺失的情况下尽全力挽救数据----这是通过我们骗过oracle，让其不应用缺失的归档，转而应用剩余的归档来实现的。
请注意，实际的恢复过程中，出现的问题可能比你相像的要复杂太多！如果有可能，我会在恩墨科技的一个关于备份与恢复的培训中与大家详细分享这个案例。
我们来看这个例子：
SQL_testdb>create tablespace testrecover datafile '/dras20/testdb/testrecover_01.dbf' size
Tablespace created.
SQL_testdb>create table t1 tablespace testrecover as select * from dba_objects;
Table created.
SQL_testdb>create table t2 tablespace testrecover as select * from dba_users;
Table created.
SQL_testdb>create table t3 tablespace testrecover as select * from dba_users;
Table created.
SQL_testdb>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dras20/testdb
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL_testdb>alter system switch logfile;
System altered.
SQL_testdb>select count(*) from t1;
COUNT(*)
----------
29391
SQL_testdb>select count(*) from t2;
COUNT(*)
----------
29
SQL_testdb>select count(*) from t3;
COUNT(*)
----------
29
SQL_testdb>delete from t2 where rownum<2;
1 row deleted.
SQL_testdb>delete from t3 where rownum<2;
1 row deleted.
SQL_testdb>commit;
Commit complete.
SQL_testdb>alter system switch logfile;
System altered.
SQL_testdb>delete from t1 where rownum<10001;
10000 rows deleted.
SQL_testdb>delete from t2 where rownum<2;
1 row deleted.
SQL_testdb>delete from t3 where rownum<2;
1 row deleted.
SQL_testdb>commit;
Commit complete.
SQL_testdb>select count(*) from t1;
COUNT(*)
----------
19391
SQL_testdb>select count(*) from t2;
COUNT(*)
----------
27
SQL_testdb>select count(*) from t3;
COUNT(*)
----------
27
SQL_testdb>alter system switch logfile;
System altered.
SQL_testdb>alter system switch logfile;
System altered.
SQL_testdb>alter system switch logfile;
System altered.
SQL_testdb>alter system switch logfile;
System altered.
这个时候我们如果把归档日志/dras20/testdb/1_21.dbf和数据文件/dras20/testdb/testrecover_01.dbf给rm掉，再恢复的时候，oracle一定会报错1_21.dbf找不到，这时候如果我们能骗过oracle，让其忽略掉1_21.dbf，且继续应用剩余的归档日志，则最后恢复出来的结果一定是表t1中有19391条记录，表t2中有28条记录，表t3中有28条记录：
ORA-00308: cannot open archived log '/dras20/testdb/1_21.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
运气好的话，我们只需要改一下RBA和checkpoint SCN就行了：
SQL_testdb>recover datafile 2;
ORA-00279: change 560013 generated at 04/21/2010 08:30:29 needed for thread 1
ORA-00289: suggestion : /dras20/testdb/1_22.dbf
ORA-00280: change 560013 for thread 1 is in sequence #22
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dras20/testdb/1_22.dbf
ORA-00279: change 560524 generated at 04/21/2010 08:36:22 needed for thread 1
ORA-00289: suggestion : /dras20/testdb/1_23.dbf
ORA-00280: change 560524 for thread 1 is in sequence #23
ORA-00278: log file '/dras20/testdb/1_22.dbf' no longer needed for this
recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dras20/testdb/1_23.dbf
Log applied.
Media recovery complete.
SQL_testdb>alter database open;
Database altered.
SQL_testdb>select count(*) from t1;
COUNT(*)
----------
19391
SQL_testdb>select count(*) from t2;
COUNT(*)
----------
28
SQL_testdb>select count(*) from t3;
COUNT(*)
----------
28
如果运气不好，你可能会碰到多种错误，比如：
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dras20/testdb/1_16.dbf
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [8388621], [1], [17], [431],
[16], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 13)
ORA-10564: tablespace TESTRECOVER1
ORA-01110: data file 2: '/dras20/testdb/testrecover1_01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 30277
ORA-01112: media recovery not started
SQL_testdb>recover datafile 2 test;
ORA-10589: Test recovery had to corrupt 277 data blocks in order to proceed
ORA-10573: Test recovery tested redo from change 557485 to 558213
ORA-10570: Test recovery complete
这种情况下处理起来相对来说会麻烦一些。
Leave a comment