我们都知道Oracle 10g引入了跨resetlogs特性，这个特性确实很棒。在10g之前，如果我们要进行跨resetlogs恢复，
那么只能手工去修改datafile header的checkpoint 信息. 然而这一切在10g发现了改变，引入了跨resetlogs恢复。
但是，Oracle 10g引入的跨resetlogs恢复，仅仅是database 级别，如是某个datafile的checkpoint信息跟其他文件
不一致，那么仍然是不能进行正常恢复的，除非通过restore+archivelog的方式去恢复。下面来看我的测试：
1)Test For 10gR2
然而这一切，在oracle 11gR2中发现改变了，经过测试,在11.2.0.3以及以后的版本中可以进行直接恢复的，下面是我的测试.
2)Test For 11.2.0.3
其他版本并未测试，欢迎大家测试！
那么只能手工去修改datafile header的checkpoint 信息. 然而这一切在10g发现了改变，引入了跨resetlogs恢复。
但是，Oracle 10g引入的跨resetlogs恢复，仅仅是database 级别，如是某个datafile的checkpoint信息跟其他文件
不一致，那么仍然是不能进行正常恢复的，除非通过restore+archivelog的方式去恢复。下面来看我的测试：
1)Test For 10gR2
[ora10g@killdb ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 13 01:39:52 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/ora10g/archivelog
Oldest online log sequence 149
Next log sequence to archive 151
Current log sequence 151
SQL> oradebug setmypid
Statement processed.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug tracefile_name
/home/ora10g/admin/roger/udump/roger_ora_10179.trc
SQL>
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 1275272 bytes
Variable Size 255855224 bytes
Database Buffers 369098752 bytes
Redo Buffers 2916352 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ROGER" RESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/home/ora10g/oradata/roger/redo01.log' SIZE 50M,
9 GROUP 2 '/home/ora10g/oradata/roger/redo02.log' SIZE 50M,
10 GROUP 3 '/home/ora10g/oradata/roger/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/home/ora10g/oradata/roger/system01.dbf',
14 '/home/ora10g/oradata/roger/undotbs01.dbf',
15 '/home/ora10g/oradata/roger/sysaux01.dbf',
16 '/home/ora10g/oradata/roger/users01.dbf',
17 '/home/ora10g/oradata/roger/system02.dbf',
18 '/home/ora10g/oradata/roger/undotbs2.dbf',
19 '/home/ora10g/oradata/roger/streams01.dbf'
20 CHARACTER SET ZHS16GBK
21 ;
Control file created.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12360262 generated at 12/13/2013 01:47:16 needed for thread 1
ORA-00289: suggestion : /home/ora10g/archivelog/1_152_822878558.dbf
ORA-00280: change 12360262 for thread 1 is in sequence #152
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/home/ora10g/archivelog/1_152_822878558.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/home/ora10g/archivelog/1_152_822878558.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
1 /home/ora10g/oradata/roger/system01.dbf SYSTEM
2 /home/ora10g/oradata/roger/undotbs01.dbf ONLINE
3 /home/ora10g/oradata/roger/sysaux01.dbf ONLINE
4 /home/ora10g/oradata/roger/users01.dbf ONLINE
5 /home/ora10g/product/10.2/dbs/MISSING00005 RECOVER
6 /home/ora10g/oradata/roger/system02.dbf SYSTEM
7 /home/ora10g/oradata/roger/undotbs2.dbf ONLINE
8 /home/ora10g/oradata/roger/streams01.dbf ONLINE
8 rows selected.
SQL> alter database rename file '/home/ora10g/product/10.2/dbs/MISSING00005' to '/home/ora10g/oradata/roger/roger01.dbf';
Database altered.
SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [krhpfh_03-1202], [fno =], [5], [fhcrt =], [821580206], [cptim =], [0], []
ORA-01110: data file 5: '/home/ora10g/oradata/roger/roger01.dbf'
大家可以看到，这是不能直接进行recover的，需要手工去修改checkpoint信息才能恢复，如下：
BBED> info all
File# Name Size(blks)
----- ---- ----------
1 /home/ora10g/oradata/roger/system01.dbf 0
2 /home/ora10g/oradata/roger/undotbs01.dbf 0
3 /home/ora10g/oradata/roger/sysaux01.dbf 0
4 /home/ora10g/oradata/roger/users01.dbf 0
5 /home/ora10g/oradata/roger/roger01.dbf 0
6 /home/ora10g/oradata/roger/system02.dbf 0
7 /home/ora10g/oradata/roger/undotbs2.dbf 0
8 /home/ora10g/oradata/roger/streams01.dbf 0
BBED> set file 5 block 1
FILE# 5
BLOCK# 1
BBED> modify /x e238b6
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 1 Offsets: 112 to 115 Dba:0x01400001
------------------------------------------------------------------------
e238b631
<32 bytes per line>
BBED> modify /x 479abc
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 1 Offsets: 116 to 119 Dba:0x01400001
------------------------------------------------------------------------
479abc00
<32 bytes per line>
BBED> modify /x 4a offset 484
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 1 Offsets: 484 to 487 Dba:0x01400001
------------------------------------------------------------------------
4a9abc00
<32 bytes per line>
BBED> modify /x e9 offset 492
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 1 Offsets: 492 to 495 Dba:0x01400001
------------------------------------------------------------------------
e938b631
<32 bytes per line>
BBED> sum apply
Check value for File 5, Block 1:
current = 0x37d1, required = 0x37d1
修改完成之后，我们flush 一下buffer cache 即可进行恢复并将datafile online，如下：
SQL> alter system flush buffer_cache;
System altered.
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL>
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
1 /home/ora10g/oradata/roger/system01.dbf SYSTEM
2 /home/ora10g/oradata/roger/undotbs01.dbf ONLINE
3 /home/ora10g/oradata/roger/sysaux01.dbf ONLINE
4 /home/ora10g/oradata/roger/users01.dbf ONLINE
5 /home/ora10g/oradata/roger/roger01.dbf ONLINE
6 /home/ora10g/oradata/roger/system02.dbf SYSTEM
7 /home/ora10g/oradata/roger/undotbs2.dbf ONLINE
8 /home/ora10g/oradata/roger/streams01.dbf ONLINE
8 rows selected.
SQL>
然而这一切，在oracle 11gR2中发现改变了，经过测试,在11.2.0.3以及以后的版本中可以进行直接恢复的，下面是我的测试.
2)Test For 11.2.0.3
[oracle@11gR2texing ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 17 11:46:37 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Real Application Testing options
SQL> oradebug setmypid
Statement processed.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/roger/roger/trace/roger_ora_13278.trc
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2231952 bytes
Variable Size 452985200 bytes
Database Buffers 272629760 bytes
Redo Buffers 2867200 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ROGER" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/oracle/oradata/roger/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/oracle/oradata/roger/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/oracle/oradata/roger/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/oracle/oradata/roger/system01.dbf',
14 '/oracle/oradata/roger/sysaux01.dbf',
15 '/oracle/oradata/roger/undotbs01.dbf',
16 '/oracle/oradata/roger/users01.dbf',
17 '/oracle/oradata/roger/dbtk.dbf'
18 CHARACTER SET WE8MSWIN1252
19 ;
Control file created.
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12093166 generated at 10/17/2013 11:47:11 needed for thread 1
ORA-00289: suggestion : /arch/1_163_793419533.dbf
ORA-00280: change 12093166 for thread 1 is in sequence #163
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/arch/1_163_793419533.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/arch/1_163_793419533.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
SQL> set lines 200
SQL> col name for a65
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ----------------------------------------------------------------- -------
1 /oracle/oradata/roger/system01.dbf SYSTEM
2 /oracle/oradata/roger/sysaux01.dbf ONLINE
3 /oracle/oradata/roger/undotbs01.dbf ONLINE
4 /oracle/oradata/roger/users01.dbf ONLINE
5 /oracle/oradata/roger/dbtk.dbf ONLINE
6 /oracle/product/11.2.0/db_1/dbs/MISSING00006 RECOVER
6 rows selected.
SQL> alter database rename file '/oracle/product/11.2.0/db_1/dbs/MISSING00006' to '/oracle/oradata/roger/roger01.dbf';
Database altered.
SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: '/oracle/oradata/roger/roger01.dbf'
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ----------------------------------------------------------------- -------
1 /oracle/oradata/roger/system01.dbf SYSTEM
2 /oracle/oradata/roger/sysaux01.dbf ONLINE
3 /oracle/oradata/roger/undotbs01.dbf ONLINE
4 /oracle/oradata/roger/users01.dbf ONLINE
5 /oracle/oradata/roger/dbtk.dbf ONLINE
6 /oracle/oradata/roger/roger01.dbf ONLINE
6 rows selected.
SQL>
其他版本并未测试，欢迎大家测试！
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
