标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 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-600 kcratr_nab_less_than_odr 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 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,884)
- DB2 (22)
- MySQL (82)
- Oracle (1,709)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (73)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (662)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 记录19c ASM磁盘掉线触发ORA-59048故障处理
- aix平台Oracle数据库由于断电无法启动故障处理
- 断电引起ORA-600 3005故障处理
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁(RUs+OJVM)-19.33
- 由于con$.con#值达到极限,触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
- OBET-Oracle Block Editor Tool使用说明
标签归档:ORA-00704
记录一次ORA-00316 ORA-00312 redo异常恢复
正常运行的数据库报突然报ORA-00316: log 1 of thread 1, type 286 in header is not log file,异常终止
Fri Feb 21 08:44:42 2014 Thread 1 advanced to log sequence 591 (LGWR switch) Current log# 1 seq# 591 mem# 0: J:\ORADATA\ORCL\REDO01.LOG Fri Feb 21 15:31:20 2014 Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_10312.trc: ORA-00316: log 1 of thread 1, type 286 in header is not log file ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG' Fri Feb 21 15:31:20 2014 Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_10312.trc: ORA-00316: log 1 of thread 1, type 286 in header is not log file ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG' Fri Feb 21 15:31:20 2014 LGWR: terminating instance due to error 316 Fri Feb 21 15:31:20 2014 Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j001_11328.trc: ORA-00316: log of thread , type in header is not log file Fri Feb 21 15:31:20 2014 Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_14116.trc: ORA-00316: 日志 (用于线程 ) 标头中的类型 不是日志文件 Fri Feb 21 15:31:20 2014 Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw1_8964.trc: ORA-00316: log of thread , type in header is not log file Fri Feb 21 15:31:22 2014 Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_10592.trc: ORA-00316: log of thread , type in header is not log file ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) 系统找不到指定的文件。 ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) 系统找不到指定的文件。 Fri Feb 21 15:31:41 2014 Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_11112.trc: ORA-00316: log of thread , type in header is not log file Fri Feb 21 15:31:41 2014 Instance terminated by LGWR, pid = 10312
数据库启动报ORA-00316错误
SQL> alter database open; alter database open * ERROR at line 1: ORA-00316: log 1 of thread 1, type 286 in header is not log file ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG'
alert日志信息,报ORA-00316 ORA-00312
Sun Feb 23 13:54:08 2014 Started redo scan Sun Feb 23 13:54:08 2014 Errors in file e:\oracle\product\10.2.0\admin\ora10g\udump\orcl_ora_5544.trc: ORA-00316: log 1 of thread 1, type 286 in header is not log file ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG' Sun Feb 23 13:54:08 2014 Aborting crash recovery due to error 316 Sun Feb 23 13:54:08 2014 Errors in file e:\oracle\product\10.2.0\admin\ora10g\udump\orcl_ora_5544.trc: ORA-00316: log 1 of thread 1, type 286 in header is not log file ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG' ORA-316 signalled during: alter database open...
通过dump redo header可以看出来redo header完全混乱了,里面很多数据文件内容在里面,初步估计系统或者硬件有问题(不稳定)导致该问题
LOG FILE #1: (name #3) J:\ORADATA\ORCL\REDO01.LOG Thread 1 redo log links: forward: 2 backward: 0 siz: 0x7d000 seq: 0x0000024f hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.4f4a1951 Low scn: 0x0000.4f4e5400 02/21/2014 08:44:42 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 FILE HEADER: Software vsn=4280360459=0xff211e0b, Compatibility Vsn=103886850=0x6313002 Db ID=842019892=0x32303434, Db Name='01a0001' Activation ID=1107558657=0x42040101 Control Seq=842019892=0x32303434, File size=808464688=0x30303130 File Number=2417, Blksiz=2013738803, File Type=286 UNKNOWN descrip:"00-440201|广东省xxxxxx研究院|00014402010037" thread: 767 nab: 0x534e4906 seq: 0xbcfebcbd hws: 0xffffffff eot: 55 dis: 66 reset logs count: 0x7545245 scn: 0x0101.1e097178 Low scn: 0x4537.2022002c 01/15/2022 20:59:37 Next scn: 0x3734.46463135 04/30/2017 17:34:49 Enabled scn: 0x4345.37333038 10/22/2015 02:04:16 Thread closed scn: 0x3939.43303143 08/25/2024 00:11:31 Log format vsn: 0x46343835 Disk cksum: 0x1d09 Calc cksum: 0x70ed Terminal Recovery Stop scn: 0x3734.35304141 Terminal Recovery Stamp 03/17/2014 19:59:48 Most recent redo scn: 0x3636.31303134 Largest LWN: 758788710 blocks Miscellaneous flags: 0x41444534 Thread internal enable indicator: thr: 263902399, seq: 808464496 scn: 0x3032.30343431
因为当前redo完全损坏,尝试不完全恢复并结合隐含参数(_allow_resetlogs_corruption)拉库,出现错误ORA-00704 ORA-00604 ORA-01555
Sun Feb 23 14:03:37 2014 SMON: enabling cache recovery Sun Feb 23 14:03:39 2014 ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.4f4e5405): Sun Feb 23 14:03:39 2014 select ctime, mtime, stime from obj$ where obj# = :1 Sun Feb 23 14:03:39 2014 Errors in file e:\oracle\product\10.2.0\admin\ora10g\udump\orcl_ora_5504.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small Error 704 happened during db open, shutting down database USER: terminating instance due to error 704
通过修改scn,让数据库顺利open
发表在 Oracle备份恢复
标签为 ORA-00312, ORA-00316, ORA-00704, ORA-01555, Oracle 恢复, ORACLE恢复, ORACLE数据库恢复
评论关闭
模拟基表事务未提交数据库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故障
使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
以前写过一篇乱用_allow_resetlogs_corruption参数导致悲剧的文章,昨天晚上又遇到一个朋友不谨慎使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
环境描述
系统环境:solaris
数据库版本:10.2.0.5.7
数据存储方式:ASM
数据量:15T以上
补充事宜:数据库SCN距离headroom只有54天
报ORA-00020错误,实例crash
数据库因为超过了系统的进程数,出现dbwn进程写数据文件异常
Sun Aug 25 16:00:41 CST 2013 Errors in file /opt/oracle/admin/orcl/bdump/orcl_dbw0_7490.trc: ORA-01148: 无法刷新数据文件 22 的文件大小 ORA-01110: 数据文件 22: '+DATA/orcl/datafile/index_jh.dbf' ORA-00020: 超出最大进程数 () Sun Aug 25 16:00:41 CST 2013 Errors in file /opt/oracle/admin/orcl/bdump/orcl_dbw0_7490.trc: ORA-01242: 数据文件出现介质故障: 数据库处于 NOARCHIVELOG 模式 ORA-01110: 数据文件 22: '+DATA/orcl/datafile/index_jh.dbf' Sun Aug 25 16:00:41 CST 2013 DBW0: terminating instance due to error 1242 Termination issued to instance processes. Waiting for the processes to exit Sun Aug 25 16:00:51 CST 2013 Instance termination failed to kill one or more processes Instance terminated by DBW0, pid = 7490
ORA-00600[kcbtema_10]
实例恢复出现ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], []
Sun Aug 25 19:19:23 CST 2013 ALTER DATABASE OPEN Sun Aug 25 19:19:38 CST 2013 Beginning crash recovery of 1 threads parallel recovery started with 16 processes Sun Aug 25 19:19:40 CST 2013 Started redo scan Sun Aug 25 19:20:07 CST 2013 Completed redo scan 12016413 redo blocks read, 93405 data blocks need recovery Sun Aug 25 19:20:19 CST 2013 Started redo application at Thread 1: logseq 53681, block 1091966 Sun Aug 25 19:20:19 CST 2013 Recovery of Online Redo Log: Thread 1 Group 1 Seq 53681 Reading mem 0 Mem# 0: +DATA/orcl/onlinelog/redo_1_1.log Mem# 1: +DATA/orcl/onlinelog/redo_1_2.log Sun Aug 25 19:20:21 CST 2013 Errors in file /opt/oracle/admin/orcl/bdump/orcl_p011_16944.trc: ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], [] Sun Aug 25 19:20:23 CST 2013 Errors in file /opt/oracle/admin/orcl/bdump/orcl_p011_16944.trc: ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], [] Sun Aug 25 19:20:23 CST 2013 Aborting crash recovery due to slave death, attempting serial crash recovery Sun Aug 25 19:20:23 CST 2013 Beginning crash recovery of 1 threads Sun Aug 25 19:20:23 CST 2013 Started redo scan Sun Aug 25 19:20:47 CST 2013 Completed redo scan 12016413 redo blocks read, 93405 data blocks need recovery Sun Aug 25 19:20:54 CST 2013 Started redo application at Thread 1: logseq 53681, block 1091966 Sun Aug 25 19:20:54 CST 2013 Recovery of Online Redo Log: Thread 1 Group 1 Seq 53681 Reading mem 0 Mem# 0: +DATA/orcl/onlinelog/redo_1_1.log Mem# 1: +DATA/orcl/onlinelog/redo_1_2.log Sun Aug 25 19:20:54 CST 2013 Errors in file /opt/oracle/admin/orcl/udump/orcl_ora_16751.trc: ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], [] Sun Aug 25 19:20:56 CST 2013 Aborting crash recovery due to error 600 Sun Aug 25 19:20:56 CST 2013 Errors in file /opt/oracle/admin/orcl/udump/orcl_ora_16751.trc: ORA-00600: 内部错误代码, 参数: [kcbtema_10], [1], [], [], [], [], [], [] ORA-600 signalled during: ALTER DATABASE OPEN...
使用隐含参数
ALTER SYSTEM SET _allow_resetlogs_corruption=TRUE SCOPE=SPFILE;
报ORA-00704/ORA-01555
因为在前面的恢复中进行了不完全恢复,因此这里加入隐含参数,然后尝试resetlogs,然后报如下错误
Sun Aug 25 20:11:54 CST 2013 alter database open resetlogs Sun Aug 25 20:12:10 CST 2013 RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 13429649847189 Resetting resetlogs activation ID 1312390734 (0x4e397e4e) Sun Aug 25 20:16:25 CST 2013 Setting recovery target incarnation to 2 Sun Aug 25 20:16:42 CST 2013 ************************************************************ Warning: The SCN headroom for this database is only 54 days! ************************************************************ Sun Aug 25 20:16:43 CST 2013 Assigning activation ID 1352200163 (0x5098efe3) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: +DATA/orcl/onlinelog/redo_1_1.log Current log# 1 seq# 1 mem# 1: +DATA/orcl/onlinelog/redo_1_2.log Successful open of redo thread 1 Sun Aug 25 20:16:43 CST 2013 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sun Aug 25 20:16:52 CST 2013 SMON: enabling cache recovery Sun Aug 25 20:16:52 CST 2013 ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0c36.d582339b): Sun Aug 25 20:16:52 CST 2013 select ctime, mtime, stime from obj$ where obj# = :1 Sun Aug 25 20:16:52 CST 2013 Errors in file /opt/oracle/admin/orcl/udump/orcl_ora_2859.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 143 (名称为 "_SYSSMU143$") 过小 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Termination issued to instance processes. Waiting for the processes to exit Sun Aug 25 20:17:02 CST 2013 Instance termination failed to kill one or more processes Instance terminated by USER, pid = 2859 ORA-1092 signalled during: alter database open resetlogs...
数据库当前SCN
SQL > select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
13429649947222
SQL > select distinct CHECKPOINT_CHANGE# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
13429649947222
解决方法
因为该数据库版本为10.2.0.5.7,已经包含了scn patch,因此不能使用event或者隐含参数来修改scn,而且该库容量15T以上(asm),因此也无法使用bbed修改数据文件头,最后决定使用ordebug来解决该问题
使用oradebug DUMPvar SGA kcsgscn_
使用oradebug poke
sqlplus / as sysdba startup mount oradebug setmypid oradebug DUMPvar SGA kcsgscn_ oradebug poke recover database; alter database open;
事后总结
查询MOS,发现ORA-00600[kcbtema_10] Raised During Recovery Operations (Doc ID 472282.1)
--故障原因 The cause of this problem has been identified and verified in unpublished Bug 5184359 ORA-600 [KCBTEMA_10]. Due to this bug, during recovery, the class designation of a data block has changed. --处理方法 SQL>startup mount SQL>recover database; SQL>alter database open;
因为MOS上给的解决思路在该数据库中已经无法尝试,不能确定该方法一定可行,但是对于本次的恢复过程中,没有任何直接recover database操作(只有一次不完全恢复)确实让人有无限的遗憾和可惜。对于本次应该先查询MOS,尝试该种方法,慎重使用_allow_resetlogs_corruption参数

加我微信(17813235971)
加我QQ(107644445)

