标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,597)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (17)
- PostgreSQL恢复 (5)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
月归档:八月 2013
模拟基表事务未提交数据库crash,undo丢失恢复异常恢复
对于基表特别是bootstrap$中的表,如果进行了dml操作,但是没有提交,这个时候数据库crash,而且undo异常.对于类似的场景,都是很多数据库恢复的人都有畏惧,因为_corrupted_rollback_segments参数不能起作用.本实验dml修改seq$表,最终却引起了undo$和I_UNDO1异常(他们都在bootstrap$中)
模拟seq$表事务未提交
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> select sysdate "www.xifenfei.com" from dual; www.xifenfei ------------ 31-AUG-13 SQL> CREATE SEQUENCE seq_xifenfei INCREMENT BY 1 START WITH 1 CACHE 3; Sequence created. SQL> select object_id from dba_objects where object_name='SEQ_XIFENFEI'; OBJECT_ID ---------- 77282 SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=77282; MINVALUE HIGHWATER INCREMENT$ CYCLE# ORDER$ CACHE ---------- ---------- ---------- ---------- ---------- ---------- 1 1 1 0 0 3 SQL> update seq$ set CYCLE#=3 where obj#=77282; 1 row updated. --另外一个会话abort数据库 SQL> shutdown abort; ORACLE instance shut down. --人工rm掉undotbs01.dbf文件 --启动数据库 SQL> startup 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 Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' --离线数据文件 SQL> alter database datafile 3 offline; Database altered. --尝试open数据库 SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 17864 Session ID: 125 Serial number: 5
alert日志
SMON: enabling cache recovery Mon Aug 26 17:08:49 2013 ARC3 started with pid=25, OS id=17902 ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH Archived Log entry 13 added for thread 1 sequence 13 ID 0xfb920b77 dest 1: Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17864.trc: ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17864.trc: ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Error 704 happened during db open, shutting down database USER (ospid: 17864): terminating the instance due to error 704 Instance terminated by USER, pid = 17864 ORA-1092 signalled during: alter database open... opiodr aborting process unknown ospid (17864) as a result of ORA-1092
做10046跟踪
SQL> conn / as sysdba Connected to an idle instance. 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> alter database mount; Database altered. SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. SQL> alter session set db_file_multiblocK_read_count=1; Session altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 17979 Session ID: 125 Serial number: 3
trace文件内容
PARSING IN CURSOR #3064524800 len=142 dep=3 uid=0 oct=3 lid=0 tim=1377508386082495 hv=361892850 ad='265c66d0' sqlid='7bd391hat42zk' select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1 END OF STMT PARSE #3064524800:c=2999,e=2162,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=3,plh=0,tim=1377508386082492 BINDS #3064524800: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=b6a8ebd0 bln=22 avl=02 flg=05 value=27 EXEC #3064524800:c=1999,e=2030,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=3,plh=906473769,tim=1377508386084833 WAIT #3064524800: nam='db file sequential read' ela= 65 file#=1 block#=321 blocks=1 obj#=34 tim=1377508386085083 WAIT #3064524800: nam='db file sequential read' ela= 21 file#=1 block#=225 blocks=1 obj#=15 tim=1377508386085208 FETCH #3064524800:c=0,e=375,p=2,cr=2,cu=0,mis=0,r=1,dep=3,og=3,plh=906473769,tim=1377508386085319 STAT #3064524800 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=2 pw=0 time=362 us)' STAT #3064524800 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=1 pw=0 time=223 us)' CLOSE #3064524800:c=0,e=12,dep=3,type=0,tim=1377508386085657 WAIT #3064511164: nam='control file sequential read' ela= 33 file#=0 block#=1 blocks=1 obj#=15 tim=1377508386085816 WAIT #3064511164: nam='control file sequential read' ela= 18 file#=0 block#=15 blocks=1 obj#=15 tim=1377508386085889 WAIT #3064511164: nam='control file sequential read' ela= 18 file#=0 block#=17 blocks=1 obj#=15 tim=1377508386085994 WAIT #3064511164: nam='control file sequential read' ela= 18 file#=0 block#=23 blocks=1 obj#=15 tim=1377508386086058 WAIT #3064511164: nam='control file sequential read' ela= 17 file#=0 block#=1 blocks=1 obj#=15 tim=1377508386086135 WAIT #3064511164: nam='control file sequential read' ela= 15 file#=0 block#=15 blocks=1 obj#=15 tim=1377508386086196 WAIT #3064511164: nam='control file sequential read' ela= 15 file#=0 block#=17 blocks=1 obj#=15 tim=1377508386086255 WAIT #3064511164: nam='control file sequential read' ela= 18 file#=0 block#=32 blocks=1 obj#=15 tim=1377508386086317 DDE rules only execution for: ORA 1110 ----- START Event Driven Actions Dump ---- ---- END Event Driven Actions Dump ---- ----- START DDE Actions Dump ----- Executing SYNC actions ----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) ----- Successfully dispatched ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) ----- Executing ASYNC actions ----- END DDE Actions Dump (total 0 csec) ----- FETCH #3064511164:c=8998,e=9239,p=5,cr=6,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1377508386088336 STAT #3064511164 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=82 us)' STAT #3064511164 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=3 pw=0 time=1166 us)' ===================== PARSE ERROR #3064512008:len=60 dep=1 uid=0 oct=3 lid=0 tim=1377508386088729 err=604 SELECT NULL FROM PROPS$ WHERE NAME='BOOTSTRAP_UPGRADE_ERROR' ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
_corrupted_rollback_segments无法打开数据库
SQL> show parameter _corrupted_rollback_segments; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _corrupted_rollback_segments string _SYSSMU22_1366820418$, _SYSSMU 23_2165404374$, _SYSSMU24_1842 376483$, _SYSSMU25_2373003110$ , _SYSSMU26_3635237253$, _SYSS MU27_1730246918$, _SYSSMU28_31 59173804$, _SYSSMU29_141006113 4$, _SYSSMU30_2299069988$, _SY SSMU31_290584066$ SQL> startup pfile='/tmp/pfile' mount 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 Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 17979 Session ID: 125 Serial number: 3
dump block225
Block header dump: 0x004000e1 Object id on Block? Y seg/obj: 0xf csc: 0xb8c.3c0c297b itc: 1 flg: O typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0000.001.00000034 0x00400221.0034.08 --U- 1 fsc 0x0000.3c0c297c bdba: 0x004000e1 data_block_dump,data header at 0xb6b8a644
bbed 提交225事务
[oracle@xifenfei ~]$ bbed password=blockedit filename='/u01/oracle/oradata/ora11g/system01.dbf' blocksize=8192 BBED: Release 2.0.0.0.0 - Limited Production on Mon Aug 26 17:31:09 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set block 225 BLOCK# 225 BBED> map File: /u01/oracle/oradata/ora11g/system01.dbf (0) Block: 225 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 48 bytes @20 struct kdbh, 14 bytes @68 struct kdbt[1], 4 bytes @82 sb2 kdbr[32] @86 ub1 freespace[59] @150 ub1 rowdata[7979] @209 ub4 tailchk @8188 BBED> p ktbbh struct ktbbh, 48 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x0000000f ub4 ktbbhod1 @24 0x0000000f struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x3c0c297b ub2 kscnwrp @32 0x0b8c b2 ktbbhict @36 -2047 ub1 ktbbhflg @38 0x03 (KTBFONFL) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x00000000 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0000 ub2 kxidslt @46 0x0001 ub4 kxidsqn @48 0x00000034 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00400221 ub2 kubaseq @56 0x0034 ub1 kubarec @58 0x08 ub2 ktbitflg @60 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @62 b2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x3c0c297c BBED> set mode edit MODE Edit BBED> m /x 0180 offset 60 File: /u01/oracle/oradata/ora11g/system01.dbf (0) Block: 225 Offsets: 60 to 91 Dba:0x00000000 ------------------------------------------------------------------------ 01800000 7c290c3c 00012000 ffff5200 8d003017 30170000 20008e1f 4106f805 <32 bytes per line> BBED> sum apply Check value for File 0, Block 225: current = 0x55db, required = 0x55db BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = 225 Block Checking: DBA = 4194529, Block Type = KTB-managed data block data header at 0xb6773244 kdbchk: row locked by non-existent transaction table=0 slot=20 lockid=1 ktbbhitc=1 Block 225 failed with check code 6101 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 BBED> p *kdbr[20] rowdata[608] ------------ ub1 rowdata[608] @817 0x2c BBED> dump File: /u01/oracle/oradata/ora11g/system01.dbf (0) Block: 225 Offsets: 817 to 848 Dba:0x00000000 ------------------------------------------------------------------------ 2c011102 c115145f 53595353 4d553230 5f343035 34303430 31322402 c10202c1 <32 bytes per line> BBED> m /x 002c File: /u01/oracle/oradata/ora11g/system01.dbf (0) Block: 225 Offsets: 817 to 848 Dba:0x00000000 ------------------------------------------------------------------------ 002c1102 c115145f 53595353 4d553230 5f343035 34303430 31322402 c10202c1 <32 bytes per line> BBED> sum apply Check value for File 0, Block 225: current = 0x79f6, required = 0x79f6 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = 225 Block Checking: DBA = 4194529, Block Type = KTB-managed data block data header at 0xb6773244 kdbchk: row locked by non-existent transaction table=0 slot=20 lockid=44 ktbbhitc=1 Block 225 failed with check code 6101 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 BBED> p *kdbr[20] rowdata[608] ------------ ub1 rowdata[608] @817 0x00 BBED> d File: /u01/oracle/oradata/ora11g/system01.dbf (0) Block: 225 Offsets: 817 to 848 Dba:0x00000000 ------------------------------------------------------------------------ 002c1102 c115145f 53595353 4d553230 5f343035 34303430 31322402 c10202c1 <32 bytes per line> BBED> m /x 2c00 File: /u01/oracle/oradata/ora11g/system01.dbf (0) Block: 225 Offsets: 817 to 848 Dba:0x00000000 ------------------------------------------------------------------------ 2c001102 c115145f 53595353 4d553230 5f343035 34303430 31322402 c10202c1 <32 bytes per line> BBED> sum apply Check value for File 0, Block 225: current = 0x55da, required = 0x55da BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = 225 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0
dump 321 block
SQL> alter system dump datafile '/u01/oracle/oradata/ora11g/system01.dbf' block 321; System altered. SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19704.trc --trace文件 Block header dump: 0x00400141 Object id on Block? Y seg/obj: 0x22 csc: 0xb8c.3c0c28ec itc: 2 flg: - typ: 2 - INDEX fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0000.038.00000033 0x0040021e.0033.3d --U- 1 fsc 0x0000.3c0c28ed
bbed提交321 block 事务
BBED> set block 321 BLOCK# 321 BBED> map File: /u01/oracle/oradata/ora11g/system01.dbf (0) Block: 321 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Index Leaf) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdxle, 32 bytes @92 b2 kd_off[32] @124 ub1 freespace[7581] @188 ub1 rowdata[351] @7769 ub4 tailchk @8188 BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x02 (KDDBTINDEX) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00000022 ub4 ktbbhod1 @24 0x00000022 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x3c0c28ec ub2 kscnwrp @32 0x0b8c b2 ktbbhict @36 -2046 ub1 ktbbhflg @38 0x02 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x00000000 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0000 ub2 kxidslt @46 0x0000 ub4 kxidsqn @48 0x00000000 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00000000 ub2 kubaseq @56 0x0000 ub1 kubarec @58 0x00 ub2 ktbitflg @60 0x0000 (NONE) union _ktbitun, 2 bytes @62 b2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00000000 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0000 ub2 kxidslt @70 0x0038 ub4 kxidsqn @72 0x00000033 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x0040021e ub2 kubaseq @80 0x0033 ub1 kubarec @82 0x3d ub2 ktbitflg @84 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @86 b2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x3c0c28ed BBED> m /x 0180 offset 84 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/oracle/oradata/ora11g/system01.dbf (0) Block: 321 Offsets: 84 to 595 Dba:0x00000000 ------------------------------------------------------------------------ 01800000 ed280c3c 00008001 00000000 20006400 011e9d1d 00000000 00000000 00000000 06000000 601f0000 561f4b1f 401f351f 2a1f1f1f 141f091f fe1ef31e e81edd1e d21ec71e bc1eb11e a61e9b1e 901e851e 7a1e6f1e 641e591e 4e1e431e 381e2d1e 221e171e 0c1e011e 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 0, Block 321: current = 0x990e, required = 0x990e BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = 321 Block Checking: DBA = 4194625, Block Type = KTB-managed data block **** actual rows locked by itl 2 = 1 != # in trans. header = 0 ---- end index block validation Block 321 failed with check code 6401 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 1 Total Blocks Failing (Index): 1 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 BBED> p kdxle struct kdxle, 32 bytes @92 struct kdxlexco, 16 bytes @92 ub1 kdxcolev @92 0x00 ub1 kdxcolok @93 0x00 ub1 kdxcoopc @94 0x80 ub1 kdxconco @95 0x01 ub4 kdxcosdc @96 0x00000000 sb2 kdxconro @100 32 b2 kdxcofbo @102 100 b2 kdxcofeo @104 7681 b2 kdxcoavs @106 7581 b2 kdxlespl @108 0 sb2 kdxlende @110 0 ub4 kdxlenxt @112 0x00000000 ub4 kdxleprv @116 0x00000000 ub1 kdxledsz @120 0x06 ub1 kdxleunuse @121 0x00 BBED> m /x 00 offset 95 File: /u01/oracle/oradata/ora11g/system01.dbf (0) Block: 321 Offsets: 95 to 606 Dba:0x00000000 ------------------------------------------------------------------------ 00000000 00200064 00011e9d 1d000000 00000000 00000000 00060000 00601f00 00561f4b 1f401f35 1f2a1f1f 1f141f09 1ffe1ef3 1ee81edd 1ed21ec7 1ebc1eb1 1ea61e9b 1e901e85 1e7a1e6f 1e641e59 1e4e1e43 1e381e2d 1e221e17 1e0c1e01 1e000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 0, Block 321: current = 0x380e, required = 0x380e BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/system01.dbf BLOCK = 321 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 1 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0
bbed提交事务open依旧报错
SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 20229 Session ID: 125 Serial number: 5
做10046的trace文件错误依旧
===================== PARSING IN CURSOR #3065238148 len=142 dep=3 uid=0 oct=3 lid=0 tim=1377519352653391 hv=361892850 ad='269a8064' sqlid='7bd391hat42zk' select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1 END OF STMT PARSE #3065238148:c=2999,e=3152,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=3,plh=0,tim=1377519352653388 BINDS #3065238148: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=b6b32268 bln=22 avl=02 flg=05 value=27 EXEC #3065238148:c=2999,e=3182,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=3,plh=906473769,tim=1377519352657030 WAIT #3065238148: nam='db file sequential read' ela= 169 file#=1 block#=321 blocks=1 obj#=34 tim=1377519352657400 WAIT #3065238148: nam='db file sequential read' ela= 25 file#=1 block#=225 blocks=1 obj#=15 tim=1377519352657546 FETCH #3065238148:c=1000,e=578,p=2,cr=2,cu=0,mis=0,r=1,dep=3,og=3,plh=906473769,tim=1377519352657738 STAT #3065238148 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=2 pw=0 time=538 us)' STAT #3065238148 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=1 pw=0 time=338 us)' CLOSE #3065238148:c=0,e=13,dep=3,type=0,tim=1377519352657942 WAIT #3065201888: nam='control file sequential read' ela= 111 file#=0 block#=1 blocks=1 obj#=15 tim=1377519352658170 WAIT #3065201888: nam='control file sequential read' ela= 19 file#=0 block#=16 blocks=1 obj#=15 tim=1377519352658269 WAIT #3065201888: nam='control file sequential read' ela= 18 file#=0 block#=18 blocks=1 obj#=15 tim=1377519352658329 WAIT #3065201888: nam='control file sequential read' ela= 19 file#=0 block#=24 blocks=1 obj#=15 tim=1377519352658391 WAIT #3065201888: nam='control file sequential read' ela= 17 file#=0 block#=1 blocks=1 obj#=15 tim=1377519352658469 WAIT #3065201888: nam='control file sequential read' ela= 16 file#=0 block#=16 blocks=1 obj#=15 tim=1377519352658528 WAIT #3065201888: nam='control file sequential read' ela= 28 file#=0 block#=18 blocks=1 obj#=15 tim=1377519352658787 WAIT #3065201888: nam='control file sequential read' ela= 20 file#=0 block#=32 blocks=1 obj#=15 tim=1377519352658883 DDE rules only execution for: ORA 1110 ----- START Event Driven Actions Dump ---- ---- END Event Driven Actions Dump ---- ----- START DDE Actions Dump ----- Executing SYNC actions ----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) ----- Successfully dispatched ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) ----- Executing ASYNC actions ----- END DDE Actions Dump (total 0 csec) ----- FETCH #3065201888:c=11998,e=12032,p=5,cr=6,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1377519352661044 STAT #3065201888 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=19 us)' STAT #3065201888 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=3 pw=0 time=1001 us)'
通过观察每次的10046文件,发现总是在查询select /*+ rule */ name,file#, block#,status$,user#,undosqn,xactsqn, scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1并且总是us#=27号回滚段上过不去,通过使用bbed或者dul修改数据文件,让数据库启动时候不再扫描27号回滚段(主要是修改undo$.status$=1,block位置可以通过10046确定,也可以通过odu来确定,亦或者找个相同版本的库查看)
继续open数据库
SQL> startup pfile='/tmp/pfile' mount 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 Database mounted. SQL> alter database open; Database altered.
删除有问题undo,重建新undo
SQL> drop tablespace undotbs1; drop tablespace undotbs1 * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified SQL> !oerr ora 01561 01561, 00000, "failed to remove all objects in the tablespace specified" // *Cause: Failed to remove all objects when dropping a tablespace // *Action: Retry the drop tablespace until all objects are dropped SQL> drop tablespace undotbs1 including contents; drop tablespace undotbs1 including contents * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified SQL> drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs1 including contents and datafiles * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified ------------------------------------------------- 因为前面使用了bbed/dul之类的工具,修改了undo$表, 导致undo$中的标记27号回滚段与seg$中不一致 解决方法使得修改undo$.status$为3或者2 ------------------------------------------------- SQL> update undo$ set status$=3 where us#=27; 1 row updated. SQL> commit; Commit complete. SQL> drop tablespace undotbs1 ; drop tablespace undotbs1 * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified SQL> shutdown abort; ORACLE instance shut down. SQL> startup pfile='/tmp/pfile' 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 Database mounted. Database opened. SQL> drop tablespace undotbs1 ; Tablespace dropped. SQL> create undo tablespace undotbs1 datafile '/u01/oracle/oradata/ora11g/undotbs1.dbf' size 10M; Tablespace created.
直接修改基表,bbed/dul修改数据文件等操作都是危险级别非常高的操作,而且oracle not support,在没有十足把握之前,一定不要在生产环境中执行,如果数据库挂了需要类似方法恢复,也请一定保留现场,如果你搞不定能够还原现场
部分操作可以参考:通过bbed修改回滚段状态解决ORA-00704故障
重建控制文件丢失undo异常恢复—ORA-01173模拟与恢复
数据库异常关闭,使用resetlogs方式重建控制文件,不包含undo表空间相关数据库,然后尝试resetlogs打开数据库,非常容易出现ORA-01173: data dictionary indicates missing data file from system tablespace之类的错误,本文通过模拟该错误,并简单提供解决方法
模拟ORA-01173错误
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> shutdown abort; ORACLE instance shut down. --除掉undo表空间相关数据库然后使用resetlogs模式重建控制文件 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> @/tmp/ctl.sql Control file created. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf' 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 until cancel using backup controlfile; ORA-00279: change 12696930512547 generated at 08/26/2013 14:43:13 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/ora11g/archivelog/1_8_824475918.dbf ORA-00280: change 12696930512547 for thread 1 is in sequence #8 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel; ORA-00308: cannot open archived log 'cancel;' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel 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' ORA-01112: media recovery not started SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf' SQL> recover database until cancel using backup controlfile; ORA-00279: change 12696930512547 generated at 08/26/2013 14:43:13 needed for thread 1 ORA-00289: suggestion : /u01/oracle/oradata/ora11g/archivelog/1_8_824475918.dbf ORA-00280: change 12696930512547 for thread 1 is in sequence #8 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/oracle/oradata/ora11g/redo02.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-01173: data dictionary indicates missing data file from system tablespace Process ID: 15053 Session ID: 125 Serial number: 3
alert日志
Mon Aug 26 15:22:20 2013 alter database open resetlogs RESETLOGS after complete recovery through change 12696930514503 Archived Log entry 1 added for thread 1 sequence 7 ID 0xfb91eb36 dest 1: Archived Log entry 2 added for thread 1 sequence 8 ID 0xfb91eb36 dest 1: Archived Log entry 3 added for thread 1 sequence 6 ID 0xfb91eb36 dest 1: Clearing online redo logfile 1 /u01/oracle/oradata/ora11g/redo01.log Clearing online log 1 of thread 1 sequence number 7 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 8 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 6 Clearing online redo logfile 3 complete Resetting resetlogs activation ID 4220644150 (0xfb91eb36) 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 15:22:26 2013 Setting recovery target incarnation to 2 Using SCN growth rate of 16384 per second Mon Aug 26 15:22:26 2013 Assigning activation ID 4220652407 (0xfb920b77) LGWR: STARTING ARCH PROCESSES Mon Aug 26 15:22:26 2013 ARC0 started with pid=20, OS id=15131 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Mon Aug 26 15:22:27 2013 ARC1 started with pid=21, OS id=15135 Mon Aug 26 15:22:27 2013 ARC2 started with pid=22, OS id=15139 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 15:22:27 2013 SMON: enabling cache recovery Mon Aug 26 15:22:27 2013 ARC3 started with pid=23, OS id=15143 ARC1: Archival started ARC2: Archival started ARC2: Becoming the 'no FAL' ARCH ARC2: Becoming the 'no SRL' ARCH ARC1: Becoming the heartbeat ARCH Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15053.trc: ORA-01173: data dictionary indicates missing data file from system tablespace Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15053.trc: ORA-01173: data dictionary indicates missing data file from system tablespace Error 1173 happened during db open, shutting down database USER (ospid: 15053): terminating the instance due to error 1173 Instance terminated by USER, pid = 15053 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (15053) as a result of ORA-1092 Mon Aug 26 15:22:29 2013 ORA-1092 : opitsk aborting process
解决方案
解决方案设置相关异常回滚段,然后屏蔽掉,数据库即可正常起来
--参数文件修改(使用event或者工具,系统命令等) undo_management='manual' _corrupted_rollback_segments --重启数据库使得_corrupted_rollback_segments生效 shutdown immediate; startup --尝试open数据库 recover database; alter database open; --新建undo create undo tablespace undo_new datafile '' size 100m autoextend on next 10m maxsize 30G; --删除老undo drop tablespace old_undo --修改参数 shutdonw immediate undo_management='auto' undo_tablespace='unod_new' --启动数据库 startup --逻辑导出数据,导入新库
重建控制文件丢失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 --导出数据,导入新库