联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一、模拟跨越resetlog恢复环境
[oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:09:29 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA11G (DBID=4162194039) RMAN> list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORA11G 4162194039 PARENT 1 2011-09-17 09:46:04 2 2 ORA11G 4162194039 CURRENT 995548 2011-10-31 16:05:14 RMAN> backup database format '/tmp/ora11g_%U.rman'; Starting backup at 2011-12-12 13:10:49 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=161 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:10:50 channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:17 piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:27 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:13:20 channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:22 piece handle=/tmp/ora11g_02mu1avd_1_1.rman tag=TAG20111212T131049 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 2011-12-12 13:13:22 RMAN> shutdown immediate using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> startup mount; connected to target database (not started) Oracle instance started database mounted Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes RMAN> recover database; Starting recover at 2011-12-12 13:14:56 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 2011-12-12 13:14:58 RMAN> alter database open resetlogs; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 12/12/2011 13:15:14 ORA-01139: RESETLOGS option only valid after an incomplete database recovery RMAN> exit Recovery Manager complete. [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:16:02 2011 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, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options [oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:17:47 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA11G (DBID=4162194039) RMAN> list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORA11G 4162194039 PARENT 1 2011-09-17 09:46:04 2 2 ORA11G 4162194039 PARENT 995548 2011-10-31 16:05:14 3 3 ORA11G 4162194039 CURRENT 12881971 2011-12-12 13:17:30 [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:18:34 2011 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, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options [oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:18:53 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA11G (DBID=4162194039) RMAN> backup database format '/tmp/ora11g_new_%U'; Starting backup at 2011-12-12 13:19:30 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=160 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:19:31 channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:56 piece handle=/tmp/ora11g—_new_03mu1bb3_1_1 tag=TAG20111212T131931 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:20:57 channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:58 piece handle=/tmp/ora11g—_new_04mu1bdo_1_1 tag=TAG20111212T131931 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2011-12-12 13:20:58 RMAN> sql 'alter system switch logfile'; sql statement: alter system switch logfile RMAN> sql 'alter system switch logfile'; sql statement: alter system switch logfile --备份成功后还切换日志，为了证明可以利用这些日志恢复 RMAN> shutdown immediate using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> exit Recovery Manager complete. [oracle@node1 ~]$ cd /opt/oracle/oradata/ora11g/ [oracle@node1 ora11g]$ ll 总计 2568524 -rw-r----- 1 oracle oinstall 9846784 12-12 13:27 control01.ctl -rw-r----- 1 oracle oinstall 362422272 12-12 13:22 example01.dbf -rw-r----- 1 oracle oinstall 52429312 12-12 13:22 redo01.log -rw-r----- 1 oracle oinstall 52429312 12-12 13:22 redo02.log -rw-r----- 1 oracle oinstall 52429312 12-12 13:26 redo03.log -rw-r----- 1 oracle oinstall 828383232 12-12 13:22 sysaux01.dbf -rw-r----- 1 oracle oinstall 765468672 12-12 13:22 system01.dbf -rw-r----- 1 oracle oinstall 235937792 12-11 18:05 temp01.dbf -rw-r----- 1 oracle oinstall 99622912 12-12 13:22 undotbs01.dbf -rw-r----- 1 oracle oinstall 169091072 12-12 13:22 users01.dbf [oracle@node1 ora11g]$ rm redo0* [oracle@node1 ora11g]$ rm *.dbf [oracle@node1 ora11g]$ ll 总计 9636 -rw-r----- 1 oracle oinstall 9846784 12-12 13:31 control01.ctl --删除除控制文件外的所有文件 --今天产生的归档日志，从这个里面也可以看到resetlogs操作的界限 [oracle@node1 ora11g]$ ll /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/ 总计 152120 -rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_104_7gc3cogp_.arc -rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_105_7gc3co97_.arc -rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_106_7gc3cv1w_.arc -rw-r----- 1 oracle oinstall 11425792 12-12 13:17 o1_mf_1_107_7gc3mbpr_.arc -rw-r----- 1 oracle oinstall 984576 12-12 13:18 o1_mf_1_1_7gc3ojqw_.arc -rw-r----- 1 oracle oinstall 1024 12-12 13:18 o1_mf_1_2_7gc3okx8_.arc -rw-r----- 1 oracle oinstall 4608 12-12 13:18 o1_mf_1_3_7gc3onnq_.arc -rw-r----- 1 oracle oinstall 1536 12-12 13:22 o1_mf_1_4_7gc3wnvf_.arc -rw-r----- 1 oracle oinstall 1024 12-12 13:22 o1_mf_1_5_7gc3wt48_.arc
二、跨越resetlogs恢复
[oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:22:50 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORA11G 4162194039 PARENT 1 2011-09-17 09:46:04 2 2 ORA11G 4162194039 PARENT 995548 2011-10-31 16:05:14 3 3 ORA11G 4162194039 CURRENT 12881971 2011-12-12 13:17:30 --指定incarnation恢复 RMAN> reset database to incarnation 2; database reset to incarnation 2 RMAN> restore database; Starting restore at 2011-12-12 13:33:25 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/ora11g/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/ora11g/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ora11g/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ora11g/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/ora11g/example01.dbf channel ORA_DISK_1: reading from backup piece /tmp/ora11g_01mu1aqq_1_1.rman channel ORA_DISK_1: piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:25 Finished restore at 2011-12-12 13:34:51 --证明恢复会使用incarnation 2对应的备份集 RMAN> recover database; Starting recover at 2011-12-12 13:35:13 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 106 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc archived log for thread 1 with sequence 107 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc thread=1 sequence=106 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc thread=1 sequence=107 media recovery complete, elapsed time: 00:00:03 Finished recover at 2011-12-12 13:35:18 --incarnation 2数据还原恢复完成 --指定恢复incarnation 3归档日志 RMAN> reset database to incarnation 3; database reset to incarnation 3 RMAN> recover database; Starting recover at 2011-12-12 13:49:36 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 1 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc archived log for thread 1 with sequence 2 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc archived log for thread 1 with sequence 3 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc archived log for thread 1 with sequence 4 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc archived log for thread 1 with sequence 5 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc thread=1 sequence=1 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc thread=1 sequence=2 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc thread=1 sequence=3 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc thread=1 sequence=4 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc thread=1 sequence=5 unable to find archived log archived log thread=1 sequence=6 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 12/12/2011 13:49:39 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 12882851 --缺少归档日志，恢复完seq=5的归档日志，属于正常情况 --因为没有redo，进行的是不完全恢复，使用resetlogs开打数据库 RMAN> alter database open resetlogs; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 12/12/2011 14:06:04 ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Process ID: 26406 Session ID: 96 Serial number: 7 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== ORA-03114: not connected to ORACLE RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 12/12/2011 14:06:04 ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Process ID: 26406 Session ID: 96 Serial number: 7 --发现意外出现ORA-00600[2898]错误，打开数据库终止
三、查看alert日志
Mon Dec 12 14:05:59 2011 SMON: enabling cache recovery [26406] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:1208679594 end:1208679684 diff:90 (0 seconds) Dictionary check beginning File #5 is offline, but is part of an online tablespace. data file 5: '/opt/oracle/oradata/ora11g/example01.dbf' Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc (incident=4953): ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_4953/ora11g_ora_26406_i4953.trc Mon Dec 12 14:06:02 2011 Dumping diagnostic data in directory=[cdmp_20111212140602], requested by (instance=1, osid=26406), summary=[incident=4953]. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc: ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc: ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 26406): terminating the instance due to error 600 Mon Dec 12 14:06:03 2011 Instance terminated by USER, pid = 26406 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (26406) as a result of ORA-1092 Mon Dec 12 14:06:04 2011 ORA-1092 : opitsk aborting process
发现ORA-00600[2898]错误，导致数据库abort，因为这个错误暂时未找到权威说明。初步怀疑是因为在resetlogs时候，遇到File #5 is offline, but is part of an online tablespace导致
四、重新开启数据库
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 14:08:28 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1493174472 bytes
Database Buffers 637534208 bytes
Redo Buffers 4947968 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
5 OFFLINE 12881970
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECK
---------- -------------
1 12902896
2 12902896
3 12902896
4 12902896
5 0
SQL> select FILE#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;
FILE# TO_CHAR(CHECK
---------- -------------
1 12902896
2 12902896
3 12902896
4 12902896
5 12881970
意外的发现数据库竟然open成功，从这里可以看到datafile 5处于offline状态，而且其数据文件头部scn比其他文件小，怀疑没有恢复到一致状态
五、查看重启数据库后alert日志
Archived Log entry 109 added for thread 1 sequence 1 ID 0xf84e7829 dest 1: File #5 is offline, but is part of an online tablespace. data file 5: '/opt/oracle/oradata/ora11g/example01.dbf' Mon Dec 12 14:09:01 2011 Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc: ORA-01157: ????/?????? 201 - ??? DBWR ???? ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf' ORA-27037: ???????? Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc: ORA-01186: ?? 201 ?????? ORA-01157: ????/?????? 201 - ??? DBWR ???? ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf' File 201 not verified due to error ORA-01157 Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Re-creating tempfile /opt/oracle/oradata/ora11g/temp01.dbf ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Database Characterset is ZHS16GBK No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28225.trc (incident=6153): ORA-25319: Queue table repartitioning aborted Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6153/ora11g_ora_28225_i6153.trc error 25319 happened during Queue table repartitioning Starting background process QMNC Mon Dec 12 14:09:03 2011 QMNC started with pid=31, OS id=28288 LOGSTDBY: Validating controlfile with logical metadata Mon Dec 12 14:09:04 2011 Dumping diagnostic data in directory=[cdmp_20111212140904], requested by (instance=1, osid=28225), summary=[incident=6153]. LOGSTDBY: Validation complete Completed: alter database open Mon Dec 12 14:09:04 2011 Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc (incident=6121): ORA-25319: 队列表重新分区已中止 Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6121/ora11g_mmon_28190_i6121.trc error 25319 happened during Queue table repartitioning Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc (incident=6122): ORA-25319: 队列表重新分区已中止 Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6122/ora11g_mmon_28190_i6122.trc Dumping diagnostic data in directory=[cdmp_20111212140906], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6121]. error 25319 happened during Queue table repartitioning Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc (incident=6123): ORA-25319: 队列表重新分区已中止 Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6123/ora11g_mmon_28190_i6123.trc Dumping diagnostic data in directory=[cdmp_20111212140907], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6122]. error 25319 happened during Queue table repartitioning Dumping diagnostic data in directory=[cdmp_20111212140908], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6123]. Mon Dec 12 14:09:08 2011 db_recovery_file_dest_size of 4122 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Mon Dec 12 14:09:08 2011 Starting background process CJQ0 Mon Dec 12 14:09:08 2011 CJQ0 started with pid=32, OS id=28308 Mon Dec 12 14:09:39 2011 Sweep [inc][6153]: completed Sweep [inc][6123]: completed Sweep [inc][6122]: completed
因为第一个resetlogs没有成功，所以temp文件没有创建，这里先创建了temp文件.还有个ORA-25319的错误，和datafile 5 offline有关系
六、trace文件中内容
*** 2011-12-12 14:17:46.627 Started Serial Media Recovery Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea Media Recovery apply resetlogs offline range for datafile 5, incarnation : 1 Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea Dumping database incarnation table: Resetlogs 0 scn and time: 0x0000.00c493a4 12/12/2011 14:05:53 Resetlogs 1 scn and time: 0x0000.00c49033 12/12/2011 13:17:30 Recovery target incarnation = 4, activation ID = 0 Influx buffer limit = 37449 min(50% x 74898, 100000) Start recovery at thread 1 ckpt scn 12881971 logseq 1 block 2 Initial buffer sizes: read 1024K, overflow 832K, change 805K *** 2011-12-12 14:17:46.725 Media Recovery add redo thread 1 *** 2011-12-12 14:18:47.348 Media Recovery Log 2011_12_12/o1_mf_1_1_7gc3ojqw_.arc *** 2011-12-12 14:19:00.198 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea *** 2011-12-12 14:19:15.911 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! *** 2011-12-12 14:19:22.638 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! *** 2011-12-12 14:19:31.007 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea *** 2011-12-12 14:19:37.116 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! Initial buffer sizes: read 1024K, overflow 832K, change 805K Thread 1 initialized for new incarnation 1 at scn 12882852 Media Recovery current incarnation depth : 0 File 5 (stop scn 12882852) completed recovery at checkpoint scn 12882852
从这里可以知道，datafile 5，没有应用o1_mf_1_5_7gc3wt48_.arc日志恢复，那么恢复datafile 5 需要o1_mf_1_5_7gc3wt48_.arc日志文件
七、恢复数据文件5，打开数据库
SQL> recover datafile 5 ;
ORA-00279: change 12881971 generated at 12/12/2011 13:14:05 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.
arc
ORA-00280: change 12881971 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
Log applied.
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1493174472 bytes
Database Buffers 637534208 bytes
Redo Buffers 4947968 bytes
Database mounted.
Database opened.
SQL>