联系：手机/微信(+86 17813235971) QQ(107644445)
标题：重建控制文件丢失undo异常恢复—ORA-600 25025模拟与恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在现实的情况中，有些人因为某种原因重建控制文件(丢失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 --导出数据，导入新库