分类目录归档：数据库

一、模拟跨越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>