在现实的情况中，有些人因为某种原因重建控制文件(丢失undo[有意或者无意])，然后又resetlogs库尝试恢复,这样的操作可能导致各种比较麻烦的恢复,这里模拟ORA-600[25025]异常恢复
模拟ORA-600[25025]错误
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> alter database backup controlfile to trace as '/tmp/ctl';
Database altered.
SQL> create table chf.t_xifenfei_www as select * from dba_objects where 1=0;
Table created.
SQL> insert into chf.t_xifenfei_www select * from dba_objects;
74749 rows created.
--另外一个会话abort
SQL> shutdown abort;
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 175775744 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database Buffers 50331648 bytes
Redo Buffers 6656000 bytes
SQL> !vi /tmp/ctl.sql
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oracle/oradata/ora11g/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oracle/oradata/ora11g/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/oracle/oradata/ora11g/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/oracle/oradata/ora11g/system01.dbf',
'/u01/oracle/oradata/ora11g/sysaux01.dbf',
-- '/u01/oracle/oradata/ora11g/undo02.dbf',
'/u01/oracle/oradata/ora11g/users01.dbf',
'/u01/oracle/oradata/ora11g/dbfs01.dbf',
'/u01/oracle/oradata/ora11g/tts_xifenfei02.dbf',
'/u01/oracle/oradata/ora11g/tts_xifenfei01.dbf',
'/u01/oracle/oradata/ora11g/system02.dbf',
'/u01/oracle/oradata/ora11g/czum01.dbf',
'/u01/oracle/oradata/ora11g/undotbs02.dbf',
'/u01/oracle/oradata/sp2008',
'/u01/oracle/oradata/sp_2009',
'/u01/oracle/oradata/sp_2010',
'/u01/oracle/oradata/sp_2011',
'/u01/oracle/oradata/sp_2012',
'/u01/oracle/oradata/sp_2013',
'/u01/oracle/oradata/sp_2014',
'/u01/oracle/oradata/sp_2015',
'/u01/oracle/oradata/sp_2016',
'/u01/oracle/oradata/sp_2017',
'/u01/oracle/oradata/sp_2018',
'/u01/oracle/oradata/sp_2019',
'/u01/oracle/oradata/sp_2020',
'/u01/oracle/oradata/sp_2021',
'/u01/oracle/oradata/sp_2022',
'/u01/oracle/oradata/sp_2023',
'/u01/oracle/oradata/sp_2024',
'/u01/oracle/oradata/sp_2025',
'/u01/oracle/oradata/sp_20max'
CHARACTER SET ZHS16GBK
;
"/tmp/ctl.sql" [New] 43L, 1519C written
SQL> @/tmp/ctl.sql
Control file created.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12696930343864 generated at 05/18/2013 01:17:54 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_38_805394597.dbf
ORA-00280: change 12696930343864 for thread 1 is in sequence #38
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo01.log
ORA-00310: archived log contains sequence 37; sequence 38 required
ORA-00334: archived log: '/u01/oracle/oradata/ora11g/redo01.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12696930343864 generated at 05/18/2013 01:17:54 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_38_805394597.dbf
ORA-00280: change 12696930343864 for thread 1 is in sequence #38
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo03.log
ORA-00310: archived log contains sequence 39; sequence 38 required
ORA-00334: archived log: '/u01/oracle/oradata/ora11g/redo03.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12696930343864 generated at 05/18/2013 01:17:54 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_38_805394597.dbf
ORA-00280: change 12696930343864 for thread 1 is in sequence #38
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo02.log
ORA-00279: change 12696930370956 generated at 08/26/2013 13:00:25 needed for
thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/ora11g/archivelog/1_39_805394597.dbf
ORA-00280: change 12696930370956 for thread 1 is in sequence #39
ORA-00278: log file '/u01/oracle/oradata/ora11g/redo02.log' no longer needed
for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [],
[], [], [], [], []
Process ID: 12603
Session ID: 125 Serial number: 3
alert日志信息
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ...
ALTER DATABASE RECOVER LOGFILE '/u01/oracle/oradata/ora11g/redo02.log'
Media Recovery Log /u01/oracle/oradata/ora11g/redo02.log
Mon Aug 26 13:05:00 2013
ORA-279 signalled during: ALTER DATABASE RECOVER LOGFILE '/u01/oracle/oradata/ora11g/redo02.log' ...
Mon Aug 26 13:05:10 2013
ALTER DATABASE RECOVER LOGFILE '/u01/oracle/oradata/ora11g/redo03.log'
Media Recovery Log /u01/oracle/oradata/ora11g/redo03.log
Mon Aug 26 13:05:10 2013
Incomplete recovery applied all redo ever generated.
Recovery completed through change 12696930370973 time 08/26/2013 13:00:56
Media Recovery Complete (ora11g)
Completed: ALTER DATABASE RECOVER LOGFILE '/u01/oracle/oradata/ora11g/redo03.log'
alter database open resetlogs
RESETLOGS after complete recovery through change 12696930370973
Archived Log entry 1 added for thread 1 sequence 37 ID 0xfa6fa6cb dest 1:
Archived Log entry 2 added for thread 1 sequence 38 ID 0xfa6fa6cb dest 1:
Archived Log entry 3 added for thread 1 sequence 39 ID 0xfa6fa6cb dest 1:
Clearing online redo logfile 1 /u01/oracle/oradata/ora11g/redo01.log
Clearing online log 1 of thread 1 sequence number 37
Mon Aug 26 13:05:22 2013
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/oracle/oradata/ora11g/redo02.log
Clearing online log 2 of thread 1 sequence number 38
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/oracle/oradata/ora11g/redo03.log
Clearing online log 3 of thread 1 sequence number 39
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 4201621195 (0xfa6fa6cb)
Online log /u01/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared
Mon Aug 26 13:05:33 2013
Setting recovery target incarnation to 2
Mon Aug 26 13:05:33 2013
Using SCN growth rate of 16384 per second
Mon Aug 26 13:05:33 2013
Assigning activation ID 4220644150 (0xfb91eb36)
LGWR: STARTING ARCH PROCESSES
Mon Aug 26 13:05:33 2013
ARC0 started with pid=20, OS id=12679
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Aug 26 13:05:35 2013
ARC1 started with pid=21, OS id=12683
Mon Aug 26 13:05:35 2013
ARC2 started with pid=22, OS id=12687
Mon Aug 26 13:05:36 2013
ARC3 started with pid=24, OS id=12691
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/oracle/oradata/ora11g/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Aug 26 13:05:36 2013
SMON: enabling cache recovery
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12603.trc (incident=146705):
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_146705/ora11g_ora_12603_i146705.trc
Mon Aug 26 13:05:45 2013
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Undo initialization errored: err:600 serial:0 start:57601994 end:57610584 diff:8590 (85 seconds)
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12603.trc:
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_12603.trc:
ORA-00600: internal error code, arguments: [25025], [3], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 12603): terminating the instance due to error 600
Instance terminated by USER, pid = 12603
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (12603) as a result of ORA-1092
Mon Aug 26 13:05:47 2013
ORA-1092 : opitsk aborting process
trace文件
*** 2013-08-26 13:05:38.945
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7j16t46cacjt9) -----
alter database open resetlogs
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+41 call kgdsdst() BF8A34E4 ? 2 ?
ksedst1()+77 call skdstdst() BF8A34E4 ? 0 ? 1 ? 8592C38 ?
8592AB6 ? 8592C38 ?
ksedst()+33 call ksedst1() 0 ? 1 ?
dbkedDefDump()+2704 call ksedst() 0 ? BF8A40E7 ? 47EF1FF4 ?
BF8A3D66 ? 0 ? BF8A3BF4 ?
ksedmp()+47 call dbkedDefDump() 3 ? 2 ?
ksfdmp()+59 call ksedmp() 3EB ? BF8A5458 ? F1DADED ?
116056E0 ? 3EB ? 116056E0 ?
dbgexPhaseII()+1725 call 00000000 116056E0 ? 3EB ?
dbgexProcessError() call dbgexPhaseII() B6C515AC ? B6A0C890 ?
+2550 BF8A8D30 ?
dbgeExecuteForError call dbgexProcessError() B6C515AC ? B6A0C890 ? 1 ? 0 ?
()+65 B6C515AC ? B6A0C890 ?
dbgePostErrorKGE()+ call dbgeExecuteForError B6C515AC ? B6A0C890 ? 0 ? 1 ?
1794 () 0 ?
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 116056E0 ? B6C330D4 ? 258 ?
50
kgeade()+324 call 00000000 116056E0 ? B6C330D4 ? 258 ?
kgeriv_int()+107 call kgeade() 116056E0 ? 11605808 ?
B6C330D4 ? 258 ? 0 ? 61C1 ?
kgeriv()+25 call kgeriv_int() 116056E0 ? B6C330D4 ? 61C1 ?
0 ? 1 ? BF8A96B8 ?
kgesiv()+98 call kgeriv() 116056E0 ? B6C330D4 ? 61C1 ?
1 ? BF8A96B8 ?
ksesic1()+48 call kgesiv() 116056E0 ? B6C330D4 ? 61C1 ?
1 ? BF8A96B8 ? 61C1 ? 1 ?krta2rfn()+78
call ksesic1() 61C1 ? 0 ? 3 ? 0 ?
kttsinfo()+496 call krta2rfn() 3 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ktusmout_online_ut( call kttsinfo() 9 ? 0 ? 0 ? BF8A9948 ?
)+810
ktusmiut_init_ut()+ call ktusmout_online_ut( B000 ? 89E57F8 ? 9 ?
BF8A96B8 ?
348 )
ktuini()+518 call ktusmiut_init_ut() 0 ? 0 ? 0 ? 0 ? 1 ? 0 ?
adbdrv()+46345 call ktuini() 0 ? BF8A9DE0 ? 1004BF42 ?
BF8AA258 ? B6A0BFFC ?
10EA1F20 ?
opiexe()+16835 call adbdrv() 25C8F1F8 ? 0 ? 0 ? 2A34F528 ?
2A0400C8 ? BF8AF07C ?
opiosq0()+3437 call opiexe() 4 ? 0 ? BF8AFE8C ?
kpooprx()+239 call opiosq0() 3 ? E ? BF8B0184 ? A4 ? 0 ?
kpoal8()+607 call kpooprx() BF8B2D6C ? BF8B10AC ? 1D ?
1 ? 0 ? A4 ?
opiodr()+962 call 00000000 5E ? 1C ? BF8B2D68 ?
ttcpip()+1930 call 00000000 5E ? 1C ? BF8B2D68 ? 0 ?
opitsk()+1355 call ttcpip() 11616580 ? 5E ? BF8B2D68 ?
0 ? BF8B29F8 ? BF8B2E90 ?
FDEBA80 ? 0 ? BF8B2EBC ?
opiino()+827 call opitsk() 0 ? 0 ?
opiodr()+962 call 00000000 3C ? 4 ? BF8B3E2C ?
opidrv()+479 call opiodr() 3C ? 4 ? BF8B3E2C ? 0 ?
sou2o()+80 call opidrv() 3C ? 4 ? BF8B3E2C ?
opimai_real()+109 call sou2o() BF8B3E10 ? 3C ? 4 ?
BF8B3E2C ?
ssthrdmain()+212 call 00000000 2 ? BF8B3F58 ? 0 ? 47DA6F14 ?
BF8B3F14 ? 47D9A670 ?
main()+147 call ssthrdmain() 2 ? BF8B3F58 ?
__libc_start_main() call 00000000 2 ? BF8B4054 ? BF8B4060 ?
+220 47D9A828 ? 0 ? 1 ?
_start()+33 call __libc_start_main() 856F3B4 ? 2 ? BF8B4054 ?
BCC1EA0 ? BCC1E90 ?
47D8C790 ?
--------------------- Binary Stack Dump ---------------------
MOS中有类似描述ORA-600 [25025] [25] While Opening the Clone Database in Resetlog Mode (Doc ID 603100.1),该解决方案是重建控制文件增加所有数据文件，在本次测试中，我就是人为除掉了undo，模拟undo丢失[其实数据库已经resetlogs过了，就算加入undo重建控制文件也不会成功(人工修改undo文件头除外)]，又做了不正确的重建控制文件操作的故障，我提供解决方案如下
解决办法
--参数文件修改
undo_management='manual'
--尝试open数据库
recover database;
alter database open;
--新建undo
create undo tablespace undo_new datafile '' size 100m autoextend on next 10m maxsize 30G;
--屏蔽需要恢复回滚段
select tablespace_name,segment_name,status from dba_rollback_segs;
_corrupted_rollback_segments
--重启数据库使得_corrupted_rollback_segments生效
shutdown immediate;
startup
--删除老undo
drop tablespace old_undo
--修改参数
shutdonw immediate
undo_management='auto'
undo_tablespace='unod_new'
--启动数据库
startup
--导出数据，导入新库
姊妹篇:重建控制文件丢失undo异常恢复—ORA-01173模拟与恢复
