下面故障来至于群面一位兄弟，日志文件被重制后，打开数据库遇到ORA-00604,ORA-01555报错，朋友通过网盘将数据库共享给我，最后的解决该当很简单，关键在于分析过程，此分析过程是通用的，能用于其它的一些报错。
1，平台与版本
|
www.htz.pw > select * from v$version;
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit 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
www.htz.pw > !lsb_release -a LSB Version: :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch Distributor ID: RedHatEnterpriseAS Description: Red Hat Enterprise Linux AS release 4 (Nahant Update 8) Release: 4 Codename: NahantUpdate8 |
2，修改数据文件位置
这里由于两个环境的路径不一致，所以这里需要修改数据文件的位置与一些参数
|
[oracle@www.htz.pw temp]$cp database.zip /oracle/app/oracle/oradata/ [oracle@www.htz.pw temp]$cd !$ cd /oracle/app/oracle/oradata/ [oracle@www.htz.pw oradata]$unzip database.zip 修改后的参数文件 [oracle@www.htz.pw oradata]$cat /tmp/123.ora cos11g.__db_cache_size=201326592 cos11g.__java_pool_size=4194304 cos11g.__large_pool_size=4194304 cos11g.__oracle_base=’/oracle/app/oracle’#ORACLE_BASE set from environment cos11g.__pga_aggregate_target=272629760 cos11g.__sga_target=511705088 cos11g.__shared_io_pool_size=0 cos11g.__shared_pool_size=289406976 cos11g.__streams_pool_size=4194304 *.audit_file_dest=’/oracle/app/oracle/admin/cos11g/adump’ *.audit_trail=’NONE’ *.compatible=’11.2.0.0.0′ *.control_files=’/oracle/app/oracle/oradata/database/cos11g/control01.ctl’ *.db_block_size=8192 *.db_domain=” *.db_name=’cos11g’ *.db_recovery_file_dest=’/oracle/app/oracle/fast_recovery_area’ *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest=’/oracle/app/oracle’ *.dispatchers='(PROTOCOL=TCP) (SERVICE=cos11gXDB)’ *.memory_target=783286272 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile=’EXCLUSIVE’ *.undo_tablespace=’UNDOTBS1′ *._allow_resetlogs_corruption=TRUE *._allow_error_simulation=TRUE *.undo_management=’manual’
这里我使用的重建控制文件来修改数据文件的位置，方法很多，如alter database rename file的方式都可以的
[oracle@www.htz.pw ~]$cd rs/sql 这里使用了create_controlfile_sql.sh脚本来自动生成控制文件的内容，其实就是调用了alter database backup controflile to trace as ‘/tmp/control.ctl’ [oracle@www.htz.pw sql]$sh ./create_controlfile_sql.sh please input direcotry default /tmp: please input file name default control.ctl: Database altered. www.htz.pw > create spfile from pfile=’/tmp/123.ora’; File created. www.htz.pw > shutdown abort; ORACLE instance shut down. www.htz.pw > @/tmp/control.ctl ORACLE instance started.
Total System Global Area 780824576 bytes Fixed Size 2232432 bytes Variable Size 574623632 bytes Database Buffers 201326592 bytes Redo Buffers 2641920 bytes 下面是控制文件的内容 STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "COS11G" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘/oracle/app/oracle/oradata/database/cos11g/redo01.log’ SIZE 50M BLOCKSIZE 512, GROUP 2 ‘/oracle/app/oracle/oradata/database/cos11g/redo02.log’ SIZE 50M BLOCKSIZE 512, GROUP 3 ‘/oracle/app/oracle/oradata/database/cos11g/redo03.log’ SIZE 50M BLOCKSIZE 512 DATAFILE ‘/oracle/app/oracle/oradata/database/cos11g/system01.dbf’, ‘/oracle/app/oracle/oradata/database/cos11g/sysaux01.dbf’, ‘/oracle/app/oracle/oradata/database/cos11g/undotbs01.dbf’, ‘/oracle/app/oracle/oradata/database/cos11g/users01.dbf’, ‘/oracle/app/oracle/oradata/database/cos11g/example01.dbf’ CHARACTER SET AL32UTF8 ; RECOVER DATABASE ALTER DATABASE OPEN;
www.htz.pw > shutdown abort; ORACLE instance shut down. www.htz.pw > @/tmp/control.ctl ORACLE instance started.
Total System Global Area 780824576 bytes Fixed Size 2232432 bytes Variable Size 574623632 bytes Database Buffers 201326592 bytes Redo Buffers 2641920 bytes CREATE CONTROLFILE REUSE DATABASE "COS11G" NORESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-00367: checksum error in log file header ORA-01517: log member: ‘/oracle/app/oracle/oradata/database/cos11g/redo01.log’
这里看到了，报日志文件的checksum错误，因为原来的日志文件已经被重建，所以这里只需要将控制文件的NORESETLOGS更改为RESETLOGS就可以了
ORA-01507: database not mounted
ALTER DATABASE OPEN * ERROR at line 1: ORA-01507: database not mounted
更改控制文件 www.htz.pw > !vi /tmp/control.ctl STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "COS11G" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘/oracle/app/oracle/oradata/database/cos11g/redo01.log’ SIZE 50M BLOCKSIZE 512, GROUP 2 ‘/oracle/app/oracle/oradata/database/cos11g/redo02.log’ SIZE 50M BLOCKSIZE 512, GROUP 3 ‘/oracle/app/oracle/oradata/database/cos11g/redo03.log’ SIZE 50M BLOCKSIZE 512 DATAFILE ‘/oracle/app/oracle/oradata/database/cos11g/system01.dbf’, ‘/oracle/app/oracle/oradata/database/cos11g/sysaux01.dbf’, ‘/oracle/app/oracle/oradata/database/cos11g/undotbs01.dbf’, ‘/oracle/app/oracle/oradata/database/cos11g/users01.dbf’, ‘/oracle/app/oracle/oradata/database/cos11g/example01.dbf’ CHARACTER SET AL32UTF8 ; RECOVER DATABASE ALTER DATABASE OPEN; www.htz.pw > shutdown abort; ORACLE instance shut down. www.htz.pw > @/tmp/control.ctl ORACLE instance started.
Total System Global Area 780824576 bytes Fixed Size 2232432 bytes Variable Size 574623632 bytes Database Buffers 201326592 bytes Redo Buffers 2641920 bytes
Control file created.
ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
ALTER DATABASE OPEN * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open |
3，开始正常的恢复
|
www.htz.pw > select open_mode from v$database;
OPEN_MODE ——————– MOUNTED
www.htz.pw > recover database using backup controlfile until cancel; ORA-00279: change 2223501 generated at 08/28/2014 11:22:42 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/COS11G/archivelog/2014_08_28/o1_mf_1_1_%u_ .arc ORA-00280: change 2223501 for thread 1 is in sequence #1
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: ‘/oracle/app/oracle/oradata/database/cos11g/system01.dbf’
ORA-01112: media recovery not started
www.htz.pw > alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_3654194381$" too small Process ID: 9202 Session ID: 1 Serial number: 3 |
这里我们看到了触发了ORA-00704,ORA-00604,ORA-01555的报错，原因是由于oracle2进制中的一条sql语句执行，执行了ORA-01555报错，ORA-01555报错常见原因如下：
1，延迟块清除，这种情况我们通过增加SCN就可以解决。
2，事务回退，这种情况我们通过BBED来处理就可以了。
此故障在故障中，是很常见的，但是有些特殊情况下，恢复起来不是那么容易，曾经遇到过一个数据库，bbed修改了2个小时的块，最后才将数据库打开
4，初步故障处理过程
4.1 修改undo_tablespace参数
这种修改undo_tablespace参数只是为了试一下
|
*.undo_tablespace=’SYSTEM’ |
故障现象仍然存在
4.2 修改undo段的参数
|
_offline_rollback_segments=(_SYSSMU6_3654194381$) _corrupted_rollback_segments=(_SYSSMU6_3654194381$) |
此两个参数的作用是不一样的，一个是将整个UNDO段认识是损坏的，一个是可以正常读取UNDO段头的。更多关于此参数的作用可以见
|
ROLLBACK SEGMENT CORRUPTION with _OFFLINE & _CORRUPTED_ROLLBACK_SEGMENTS options (Doc ID 152085.1) |
通过上面的2个参数，故障现象仍然存在。
5，故障分析过程
通过上面简单的2步解决，故障仍然存在，所以需要进一步详细的分析一下报错的详细过程与原因
5.1 查看alert文件
下面查看一个alert中的日志内容，获取更多详细的信息
|
SMON: enabling cache recovery ORA-01555 caused by SQL statement below (SQL ID: 3nkd3g3ju5ph1, SCN: 0x0000.0021ed9c): select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null Errors in file /oracle/app/oracle/diag/rdbms/cos11g/cos11g/trace/cos11g_ora_10655.trc: ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_3654194381$" too small Errors in file /oracle/app/oracle/diag/rdbms/cos11g/cos11g/trace/cos11g_ora_10655.trc: ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_3654194381$" too small Error 704 happened during db open, shutting down database USER (ospid: 10655): terminating the instance due to error 704
从10655.trc日志中可以发现下面的信息 KCBR: Influx buffers flushed = 1 times
*** 2014-08-28 12:21:49.070 Completed Media Recovery
*** 2014-08-28 12:21:54.225 Prior to RESETLOGS processing… ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE start Database is not in archivelog mode ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE complete *** 2014-08-28 12:21:54.226 4320 krsh.c Clearing online redo logfile 1 /oracle/app/oracle/oradata/database/cos11g/redo01.log *** 2014-08-28 12:21:54.379 4320 krsh.c Clearing online redo logfile 1 complete
*** 2014-08-28 12:21:54.380 ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_3654194381$" too small ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_3654194381$" too small
*** 2014-08-28 12:21:54.458 USER (ospid: 10655): terminating the instance due to error 704 |
不知道为什么，这么在报错的时候触发errorstack，日志中无任何有用的信息
5.2 配置event获取详细的日志内容
下面我们配置了10046与errorstack的event来获取详细的信息
|
www.htz.pw > oradebug setmypid Statement processed. www.htz.pw > oradebug event 1555 trace name errorstack level 12; Statement processed. www.htz.pw > oradebug event 10046 trace name context forever,level 12; Statement processed. www.htz.pw > recover database using backup controlfile until cancel; ORA-00279: change 2223517 generated at 08/28/2014 12:26:58 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/COS11G/archivelog/2014_08_28/o1_mf_1_1_%u_ .arc ORA-00280: change 2223517 for thread 1 is in sequence #1
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: ‘/oracle/app/oracle/oradata/database/cos11g/system01.dbf’
ORA-01112: media recovery not started
www.htz.pw > alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_3654194381$" too small Process ID: 12125 Session ID: 1 Serial number: 3 |
5.3 详细分析trace文件
下面详细的分析trace文件中的信息，找到ORA-01555报错的原因
5.3.1 获取trace文件中的块的信息
因为是ORA-01555的报错，所以这里首先看的是块的信息，能大概知道那个块导致的问题。通过块的信息，我们估计是由于0x00405144这个块导致报错，不过这里没有直接去看块的内容，继续从trace文件中按正常的分析思路走。
|
[root@www.htz.pw ~]#grep -E "^Block he|^0x0" /oracle/app/oracle/diag/rdbms/cos11g/cos11g/trace/cos11g_ora_12125.trc Block header dump: 0x00405144 0x01 0x0002.015.0000009f 0x00c03a8d.001c.01 CB– 0 scn 0x0000.0002a810 0x02 0x0006.014.00000442 0x00c001b8.010e.11 —- 1 fsc 0x0000.00000000 Block header dump: 0x00400132 0x01 0x0006.006.00000056 0x00c01899.0013.01 CB– 0 scn 0x0000.0001784d 0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000 Block header dump: 0x0040012b 0x01 0x0000.050.00000007 0x00400083.000c.27 –U- 1 fsc 0x0000.000005c8 0x02 0x0000.045.00000007 0x00400081.000c.46 –U- 5 fsc 0x0000.000005ca Block header dump: 0x00400181 0x01 0x000a.00d.00000270 0x00c00531.0078.02 C— 0 scn 0x0000.000f8ac8 Block header dump: 0x00400182 0x01 0x0008.009.0000000e 0x00c00252.0005.01 CBU- 0 scn 0x0000.00004576 0x02 0x0008.002.0000000e 0x00c001f7.0004.0f C-U- 0 scn 0x0000.00004576 Block header dump: 0x00400095 0x01 0x0007.00c.00000271 0x00c00a98.0080.04 C— 0 scn 0x0000.000fbb30 0x02 0x0007.00b.00000271 0x00c00df9.0083.1d –U- 11 fsc 0x0027.00105067 Block header dump: 0x004000a9 0x01 0x0004.001.00000266 0x00c15c4a.0066.02 C— 0 scn 0x0000.000ec0d5 Block header dump: 0x004000aa 0x01 0x0004.01a.00000013 0x00c00450.000b.01 CBU- 0 scn 0x0000.00004f16 0x02 0x0007.00e.00000271 0x00c00a98.0080.0f –U- 1 fsc 0x0000.000fbb44 Block header dump: 0x00400159 0x01 0x0006.015.00000398 0x00c10fec.0085.02 C— 0 scn 0x0000.000d8b9a Block header dump: 0x0040b2eb 0x01 0x0006.009.000003cd 0x00c000de.0099.03 C— 0 scn 0x0000.000f3adb Block header dump: 0x00405144 0x01 0x0002.015.0000009f 0x00c03a8d.001c.01 CB– 0 scn 0x0000.0002a810 0x02 0x0006.014.00000442 0x00c001b8.010e.11 —- 1 fsc 0x0000.00000000 Block header dump: 0x00400141 0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000 0x02 0x0000.019.00000012 0x00400217.001a.06 –U- 1 fsc 0x0000.000f24ae Block header dump: 0x004000e1 0x01 0x0000.018.00000014 0x00400220.001c.01 –U- 1 fsc 0x0000.00105ea7 |
5.3.2 获取当前会话的游标信息
因为是SQL语句触发的ORA-01555，所以这里直接去查询的当前会话的游标的信息，可以一步定位，由于版本不同，可能这一部分的信息会不一样。
|
****************************************************** —– Session Open Cursors —–
—————————————- Cursor#2(0x2a97e11bb8) state=FETCH curiob=0x2a97e25ea0 curflg=a007 fl2=6200000 par=0x2a97e11c48 ses=0x8dc4bd60 —– Dump Cursor sql_id=3nkd3g3ju5ph1 xsc=0x2a97e25ea0 cur=0x2a97e11bb8 —–
LibraryHandle: Address=0x8e53caa0 Hash=e3a2d601 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD ObjectName: Name=select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null FullHashValue=cef0429a290691c83a49a378e3a2d601 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3819099649 OwnerIdn=0 Statistics: InvalidationCount=0 ExecutionCount=7 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1 Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0 Concurrency: DependencyMutex=0x8e53cb50(0, 1, 0, 0) Mutex=0x8e53cbd0(1, 24, 0, 6) Flags=RON/PIN/TIM/PN0/DBN/[10012841] WaitersLists: Lock=0x8e53cb30[0x8e53cb30,0x8e53cb30] Pin=0x8e53cb10[0x8e53cb10,0x8e53cb10] LoadLock=0x8e53cb88[0x8e53cb88,0x8e53cb88] Timestamp: Current=08-28-2014 12:30:54 HandleReference: Address=0x8e53cd08 Handle=(nil) Flags=[00] LibraryObject: Address=0x89ac40b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] ChildTable: size=’16’ Child: id=’0′ Table=0x89ac4f60 Reference=0x89ac4998 Handle=0x8e53c640 Children: Child: childNum=’0′ LibraryHandle: Address=0x8e53c640 Hash=0 LockMode=N PinMode=S LoadLockMode=0 Status=VALD Name: Namespace=SQL AREA(00) Type=CURSOR(00) Statistics: InvalidationCount=0 ExecutionCount=7 LoadCount=1 ActiveLocks=1 TotalLockCount=2 TotalPinCount=12 Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0 Concurrency: DependencyMutex=0x8e53c6f0(0, 0, 0, 0) Mutex=0x8e53cbd0(1, 24, 0, 6) Flags=RON/PIN/PN0/EXP/CHD/[10012111] WaitersLists: Lock=0x8e53c6d0[0x8e53c6d0,0x8e53c6d0] Pin=0x8e53c6b0[0x8e53c6b0,0x8e53c6b0] LoadLock=0x8e53c728[0x8e53c728,0x8e53c728] LibraryObject: Address=0x89ac30b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] DataBlocks: Block: #=’0′ name=KGLH0^e3a2d601 pins=0 Change=NONE Heap=0x8e53c588 Pointer=0x89ac3150 Extent=0x89ac3030 Flags=I/-/P/A/-/- FreedLocation=0 Alloc=3.398438 Size=3.976562 LoadTime=4295814490 Block: #=’6′ name=SQLA^e3a2d601 pins=0 Change=NONE Heap=0x89ac4768 Pointer=0x893b19b8 Extent=0x893b0d60 Flags=I/-/P/A/-/E FreedLocation=0 Alloc=18.210938 Size=19.781250 LoadTime=0 NamespaceDump: Child Cursor: Heap0=0x89ac3150 Heap6=0x893b19b8 Heap0 Load Time=08-28-2014 12:30:54 Heap6 Load Time=08-28-2014 12:30:54 NamespaceDump: Parent Cursor: sql_id=3nkd3g3ju5ph1 parent=0x89ac4150 maxchild=1 plk=y ppn=n kkscs=0x89ac4628 nxt=(nil) flg=18 cld=0 hd=0x8e53c640 par=0x89ac4150 Mutex 0x89ac4628(0, 0) idn 3000000000 ct=0 hsh=0 unp=(nil) unn=0 hvl=89ac4ff8 nhv=0 ses=(nil) hep=0x89ac46c0 flg=80 ld=1 ob=0x89ac30b0 ptr=0x893b19b8 fex=0x893b0d60 cursor instantiation=0x2a97e25ea0 used=1409200254 exec_id=16777222 exec=7 child#0(0x8e53c640) pcs=0x89ac4628 clk=0x8e5ae280 ci=0x89ac3150 pn=0x8e5af620 ctx=0x893b19b8 kgsccflg=9 llk[0x2a97e25ea8,0x2a97e25ea8] idx=2 xscflg=c03504f6 fl2=5040001 fl3=40222108 fl4=100 —– Bind Byte Code (IN) —– Opcode = 1 Unoptimized Offsi = 48, Offsi = 0 Opcode = 1 Unoptimized Offsi = 48, Offsi = 32 Opcode = 1 Unoptimized Offsi = 48, Offsi = 64 —– Bind Info (kkscoacd) —– Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0 kxsbbbfp=2a98203d48 bln=22 avl=01 flg=05 value=0 Bind#1 oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=01 csi=873 siz=0 off=24 kxsbbbfp=2a98203d60 bln=32 avl=10 flg=01 value="TAB_STATS$" Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56 kxsbbbfp=2a98203d80 bln=22 avl=02 flg=01 value=1 Frames pfr 0x2a97e9a578 siz=7208 efr 0x2a97e9a5d0 siz=7192 Cursor frame dump enxt: 5.0×00000010 enxt: 4.0x00000bf8 enxt: 3.0×00000588 enxt: 2.0×00000060 enxt: 1.0x00000a38 pnxt: 1.0×00000010 |
在这一部分，我们发现了当前正在执行的SQL语句，绑定变量的信息。
其实当前SQL在，在trace文件最开始部分就有，这一点是所有版本通用的
|
—– Current SQL Statement for this session (sql_id=3nkd3g3ju5ph1) —– select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null sql_text_length=203 sql=select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null |
5.3.3 10046中分析访问的块
这里我们以之前发现的SQL语句在trace中搜10046event的输出信息，找到等待事件等信息，但是不知道为什么，在10046那一部分中，没有找到相应的SQL，可能是trace文件有那里有问题导致的，但是发现了绑定变量与等待事件的信息
|
BINDS #182936821408: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0 kxsbbbfp=2a98203d48 bln=22 avl=01 flg=05 value=0 Bind#1 oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=01 csi=873 siz=0 off=24 kxsbbbfp=2a98203d60 bln=32 avl=10 flg=01 value="TAB_STATS$" Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56 kxsbbbfp=2a98203d80 bln=22 avl=02 flg=01 value=1 EXEC #182936821408:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2853959010,tim=1409200255032571 WAIT #182936821408: nam=’db file sequential read’ ela= 8 file#=1 block#=20804 blocks=1 obj#=37 tim=1409200255032603 ===================== PARSING IN CURSOR #182936803264 len=142 dep=2 uid=0 oct=3 lid=0 tim=1409200255032997 hv=361892850 ad=’8e534360′ 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 #182936803264:c=0,e=352,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,plh=0,tim=1409200255032996 BINDS #182936803264: 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=2a98202538 bln=22 avl=02 flg=05 value=6 EXEC #182936803264:c=1000,e=507,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,plh=906473769,tim=1409200255033560 WAIT #182936803264: nam=’db file sequential read’ ela= 10 file#=1 block#=321 blocks=1 obj#=34 tim=1409200255033606 WAIT #182936803264: nam=’db file sequential read’ ela= 13 file#=1 block#=225 blocks=1 obj#=15 tim=1409200255033655 FETCH #182936803264:c=0,e=99,p=2,cr=2,cu=0,mis=0,r=1,dep=2,og=3,plh=906473769,tim=1409200255033676 STAT #182936803264 id=1 cnt=1 pid=0 pos=1 obj=15 op=’TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=2 pw=0 time=96 us)’ STAT #182936803264 id=2 cnt=1 pid=1 pos=1 obj=34 op=’INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=1 pw=0 time=51 us)’ CLOSE #182936803264:c=0,e=4,dep=2,type=0,tim=1409200255033718 WAIT #182936821408: nam=’db file sequential read’ ela= 13 file#=3 block#=208 blocks=1 obj#=0 tim=1409200255033756 WAIT #182936821408: nam=’db file sequential read’ ela= 6 file#=3 block#=440 blocks=1 obj#=0 tim=1409200255033873 |
从上面信息我们可以看到182936821408执行的时候，去访问 file#=1 block#=20804 blocks=1 obj#=37 信息。下一条SQL语句就是去访问报错的UNDO段的信息，跟之前的报错信息也是一致的。
通过上面的信息，我们报到报错的SQL，绑定变量，访问的块的信息
5.3.4 查看块dump的信息
这里以1/20804来搜索，可以发现trace文件中已经dump了块的信息，这里也看到块的rdba地址，跟5.3.1那里怀疑的块是一致的。
|
BH (0x7cbf9548) file#: 1 rdba: 0x00405144 (1/20804) class: 1 ba: 0x7cb84000 set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0 dbwrid: 0 obj: 37 objn: 37 tsn: 0 afn: 1 hint: f hash: [0x8d7e0908,0x8d7e0908] lru: [0x7cbf9760,0x7cbf9500] obj-flags: object_ckpt_list ckptq: [0x8d19c920,0x8d19c920] fileq: [0x8d19c940,0x8d19c940] objq: [0x8a1ae3c8,0x8a1ae3c8] objaq: [0x7cbf98c8,0x8a1ae3a8] use: [0x8d1633b0,0x8d1633b0] wait: [NULL] st: XCURRENT md: SHR fpin: ‘qeilwhnp: qeilbk’ tch: 0 flags: buffer_dirty redo_since_read LRBA: [0x1.3.0] LSCN: [0x0.21eda4] HSCN: [0x0.21eda4] HSUB: [1] Using State Objects —————————————- SO: 0x8d163330, type: 38, owner: 0x8dc90ea0, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x8e098608, name=buffer handle, file=kcb2.h LINE:2677, pg=0 (buffer) (CR) PR: 0x8e098608 FLG: 0x100000 class bit: 0x0 cr[0]: sh[0]: kcbbfbp: [BH: 0x7cbf9548, LINK: 0x8d1633b0] type: normal pin where: qeilwhnp: qeilbk, why: 54104 buffer tsn: 0 rdba: 0x00405144 (1/20804) scn: 0x0000.0021eda4 seq: 0x01 flg: 0x00 tail: 0xeda40601 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data |
下面继续查看块的信息
|
Block header dump: 0x00405144 Object id on Block? Y seg/obj: 0x25 csc: 0x00.21eda4 itc: 2 flg: – typ: 2 – INDEX fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.015.0000009f 0x00c03a8d.001c.01 CB– 0 scn 0x0000.0002a810 0x02 0x0006.014.00000442 0x00c001b8.010e.11 —- 1 fsc 0x0000.00000000 这里我们可以看到XID的信息，ITL 02上面有一行的数据正在被修改，使用的undo段为6，使用的uba地址为00c001b8，使用的插槽号为14，trap#为442，块为索引块。其实这里看到是索引块，如果正常情况下，我们重建索引就可以解决问题了。或者是不走索引也是可以的。下面是将uba中的地址转为数据文件与块号 www.htz.pw > @dba_to_fno_bno.sql Enter value for dba: 00c001b8
FILE BLOCK ———- ———- 3 440 |
下面继续查看块中行记录的信息
|
row#102[1300] flag: ——, lock: 2, len=32, data:(6): 00 41 42 1e 00 3d col 0; len 1; (1): 80 col 1; len 5; (5): 54 45 53 54 32 col 2; len 2; (2): c1 02 col 3; NULL col 4; NULL col 5; NULL col 6; len 2; (2): c1 03 col 7; len 1; (1): 80 col 8; len 4; (4): c3 08 45 2e |
这里看到了索引中的行记录。
下面继续查看块中对象的信息
|
www.htz.pw > set echo off Enter Search Object Id (i.e. 1235) : 37
Object Create Last_Ddl OWNEROBJECT_NAME SUBOBJECT_NAME Type Time Time STATUS —————– —————————— ——————– ——————– ———- SYS I_OBJ2 INDEX 2013-08-24 11:37:35 2013-08-24 11:37:35 VALID |
这里可以看到是索引的名字等详细的信息，
5.3.5 查看undo块与头的信息
从5.3.4中我们查看了undo块的信息，下面我们来看看undo的信息
|
BH (0x7cbf91b8) file#: 3 rdba: 0x00c000d0 (3/208) class: 27 ba: 0x7cb7e000 set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0 dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f hash: [0x8d040fa0,0x8d040fa0] lru: [0x7cbf93d0,0x7cbf9170] ckptq: [NULL] fileq: [NULL] objq: [0x8a1adeb8,0x8a1adeb8] objaq: [0x8a1adea8,0x8a1adea8] st: XCURRENT md: NULL fpin: ‘ktuwh05: ktugct’ tch: 1 flags: LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] buffer tsn: 2 rdba: 0x00c000d0 (3/208) scn: 0x0000.0012a779 seq: 0x02 flg: 0x04 tail: 0xa7792602 frmt: 0x02 chkval: 0x33bf type: 0x26=KTU SMU HEADER BLOCK
07CB7FFF0 00000000 00000000 00000000 A7792602 [………….&y.] Extent Control Header —————————————————————– Extent Header:: spare1: 0 spare2: 0 #extents: 5 #blocks: 39 last map 0x00000000 #maps: 0 offset: 4080 Highwater:: 0x00c001a5 ext#: 1 blk#: 5 ext size: 8 #blocks in seg. hdr’s freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 1 Unlocked Map Header:: next 0x00000000 #extents: 5 obj#: 0 flag: 0x40000000 Extent Map —————————————————————– 0x00c000d1 length: 7 0x00c001a0 length: 8 0x00c001b8 length: 8 0x00c000d8 length: 8 0x00c00138 length: 8
Retention Table ———————————————————– Extent Number:0 Commit Time: 1409188509 Extent Number:1 Commit Time: 1409188509 Extent Number:2 Commit Time: 0 Extent Number:3 Commit Time: 1409188428 Extent Number:4 Commit Time: 1409188492
TRN CTL:: seq: 0x010d chd: 0x000d ctl: 0x0015 inc: 0x00000000 nfb: 0x0001 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x00c001a5.010d.25 scn: 0x0000.0012a420 Version: 0x01 FREE BLOCK POOL:: uba: 0x00c001a5.010d.25 ext: 0x1 spc: 0xe10 uba: 0x00000000.0109.07 ext: 0x0 spc: 0x1a20 uba: 0x00000000.00d1.25 ext: 0x5 spc: 0x608 uba: 0x00000000.00b0.01 ext: 0x6 spc: 0x1ed0 uba: 0x00000000.00b0.01 ext: 0x6 spc: 0x1ed0 TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ———————————————————————————————— 0x00 9 0x00 0x0442 0x001f 0x0000.0012a6b9 0x00c001a4 0x0000.000.00000000 0x00000001 0x00000000 1409188524 0x01 9 0x00 0x0442 0x0015 0x0000.0012a760 0x00c001a5 0x0000.000.00000000 0x00000001 0x00000000 1409188658 0x02 9 0x00 0x0441 0x0014 0x0000.0012a515 0x00c0013b 0x0000.000.00000000 0x00000003 0x00000000 1409188485 0x03 9 0x00 0x0441 0x000b 0x0000.0012a4b5 0x00c00138 0x0000.000.00000000 0x00000001 0x00000000 1409188478 0x04 9 0x00 0x0441 0x0003 0x0000.0012a4a1 0x00c00138 0x0000.000.00000000 0x00000001 0x00000000 1409188478 0x05 9 0x00 0x0441 0x0007 0x0000.0012a451 0x00c00138 0x0000.000.00000000 0x00000001 0x00000000 1409188477 0x06 9 0x00 0x0442 0x0008 0x0000.0012a71f 0x00c001a5 0x0000.000.00000000 0x00000002 0x00000000 1409188606 0x07 9 0x00 0x0441 0x0010 0x0000.0012a477 0x00c00138 0x0000.000.00000000 0x00000001 0x00000000 1409188478 0x08 9 0x00 0x0441 0x0001 0x0000.0012a73d 0x00c001a5 0x0000.000.00000000 0x00000001 0x00000000 1409188608 0x09 9 0x00 0x0441 0x0002 0x0000.0012a502 0x00c00139 0x0000.000.00000000 0x00000001 0x00000000 1409188485 0x0a 9 0x00 0x0442 0x0020 0x0000.0012a6de 0x00c001a4 0x0000.000.00000000 0x00000001 0x00000000 1409188545 0x0b 9 0x00 0x0441 0x0011 0x0000.0012a4cc 0x00c00138 0x0000.000.00000000 0x00000001 0x00000000 1409188481 0x0c 9 0x00 0x0441 0x001a 0x0000.0012a5af 0x00c000d3 0x0000.000.00000000 0x00000006 0x00000000 1409188492 0x0d 9 0x00 0x0440 0x0005 0x0000.0012a43b 0x00c00138 0x0000.000.00000000 0x00000001 0x00000000 1409188471 0x0e 9 0x00 0x0441 0x0013 0x0000.0012a552 0x00c0013b 0x0000.000.00000000 0x00000001 0x00000000 1409188486 0x0f 9 0x00 0x0441 0x0009 0x0000.0012a4f4 0x00c00139 0x0000.000.00000000 0x00000002 0x00000000 1409188485 0x10 9 0x00 0x0441 0x0004 0x0000.0012a48a 0x00c00138 0x0000.000.00000000 0x00000001 0x00000000 1409188478 0x11 9 0x00 0x0441 0x000f 0x0000.0012a4e2 0x00c00138 0x0000.000.00000000 0x00000001 0x00000000 1409188485 0x12 9 0x00 0x0441 0x0000 0x0000.0012a6a9 0x00c001a4 0x0000.000.00000000 0x00000001 0x00000000 1409188523 0x13 9 0x00 0x0441 0x0016 0x0000.0012a56a 0x00c0013c 0x0000.000.00000000 0x00000002 0x00000000 1409188487 0x14 9 0x00 0x0441 0x0017 0x0000.0012a52a 0x00c0013b 0x0000.000.00000000 0x00000001 0x00000000 1409188485 0x15 9 0x00 0x0441 0xffff 0x0000.0012a779 0x00c001a5 0x0000.000.00000000 0x00000001 0x00000000 1409188665 0x16 9 0x00 0x0441 0x0019 0x0000.0012a57e 0x00c0013c 0x0000.000.00000000 0x00000001 0x00000000 1409188489 0x17 9 0x00 0x0441 0x000e 0x0000.0012a541 0x00c0013b 0x0000.000.00000000 0x00000001 0x00000000 1409188486 0x18 9 0x00 0x0441 0x001c 0x0000.0012a67e 0x00c001a1 0x0000.000.00000000 0x00000007 0x00000000 1409188509 0x19 9 0x00 0x0441 0x000c 0x0000.0012a59b 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1409188490 0x1a 9 0x00 0x0441 0x001b 0x0000.0012a5c5 0x00c000d3 0x0000.000.00000000 0x00000001 0x00000000 1409188494 0x1b 9 0x00 0x0441 0x0021 0x0000.0012a5eb 0x00c000d3 0x0000.000.00000000 0x00000001 0x00000000 1409188494 0x1c 9 0x00 0x0441 0x0012 0x0000.0012a689 0x00c001a4 0x0000.000.00000000 0x00000003 0x00000000 1409188509 0x1d 9 0x00 0x0441 0x001e 0x0000.0012a617 0x00c000d3 0x0000.000.00000000 0x00000001 0x00000000 1409188499 0x1e 9 0x00 0x0441 0x0018 0x0000.0012a632 0x00c000d3 0x0000.000.00000000 0x00000001 0x00000000 1409188507 0x1f 9 0x00 0x0441 0x000a 0x0000.0012a6cb 0x00c001a4 0x0000.000.00000000 0x00000001 0x00000000 1409188538 0x20 9 0x00 0x0441 0x0006 0x0000.0012a701 0x00c001a4 0x0000.000.00000000 0x00000001 0x00000000 1409188586 0x21 9 0x00 0x0440 0x001d 0x0000.0012a601 0x00c000d3 0x0000.000.00000000 0x00000001 0x00000000 1409188499 |
这里可以看到undo段头中index为14的事务状态是9(非活动的)，wrap#值为0441，比之前的块中的442还小。所以此时的undo段头块比我们所需要的段头块还要旧。
下面继续查看undo块的信息
|
******************************************************************************** UNDO BLK: xid: 0x0008.007.00000389 seq: 0xdb cnt: 0x4 irb: 0x4 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset ————————————————————————— 0x01 0x1f38 0x02 0x1eb0 0x03 0x1e50 0x04 0x1dc8
*—————————– * Rec #0x1 slt: 0x18 objn: 457(0x000001c9) objd: 457 tblspc: 0(0x00000000) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *—————————– uba: 0x00c0014f.00da.3d ctl max scn: 0x0000.00126bb5 prv tx scn: 0x0000.00126bcd txn start scn: scn: 0x0000.00126f93 logon user: 0 prev brb: 12583242 prev bcl: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x0005.020.00000375 uba: 0x00c001ae.00ba.2e flg: C— lkc: 0 scn: 0x0000.00126b8b KDO Op code: URP row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00400c21 hdba: 0x00400c18 itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 173(0xad) flag: 0x2c lock: 0 ckix: 11 ncol: 9 nnew: 7 size: 0 Vector content: col 2: [ 2] c1 07 col 3: [ 2] c1 04 col 4: [ 1] 80 col 5: [ 1] 80 col 6: [ 1] 80 col 7: [ 1] 80 col 8: [ 7] 78 72 08 1a 0c 01 10
*—————————– * Rec #0x2 slt: 0x1b objn: 461(0x000001cd) objd: 461 tblspc: 0(0x00000000) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *—————————– uba: 0x00c001b8.00db.01 ctl max scn: 0x0000.00126bcd prv tx scn: 0x0000.00126bd9 txn start scn: scn: 0x0000.00126f9d logon user: 0 prev brb: 12583244 prev bcl: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x0001.00c.000002b9 uba: 0x00c0023a.00cc.36 flg: C— lkc: 0 scn: 0x0000.00126f9b KDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00400c72 hdba: 0x00400c70 itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 171(0xab)
*—————————– * Rec #0x3 slt: 0x1b objn: 462(0x000001ce) objd: 462 tblspc: 0(0x00000000) * Layer: 10 (Index) opc: 22 rci 0x02 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *—————————– index undo for leaf key operations KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x0009.006.00000394 uba: 0x00c00155.00e6.29 flg: C— lkc: 0 scn: 0x0000.00126f9c Dump kdilk : itl=2, kdxlkflg=0x1 sdc=32655 indexid=0x400c90 block=0x00400c91 (kdxlpu): purge leaf row key :(5): 04 c3 08 13 29
*—————————– * Rec #0x4 slt: 0x07 objn: 71834(0x0001189a) objd: 71834 tblspc: 1(0x00000001) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *—————————– uba: 0x00c001b8.00db.02 ctl max scn: 0x0000.00126bd9 prv tx scn: 0x0000.00126c09 txn start scn: scn: 0x0000.00126ec1 logon user: 71 prev brb: 12583244 prev bcl: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x000a.009.000002d3 uba: 0x00c00356.009a.0c flg: C— lkc: 0 scn: 0x0000.0012666e KDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00810933 hdba: 0x00810932 itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 1(0x1) |
在undo数据块中，也没有查看我们所需要的事务的UNDO记录数
5.3.6 分析结果
由于undo中没有包含事务所需要的undo记录，导致事务rollback的时候，触发了ORA-01555报错。这里知道原因后，解决问题就很简单了
5.3.7 bbed验证一下块中数据
|
[oracle@www.htz.pw trace]$bbed Password:
BBED: Release 2.0.0.0.0 – Limited Production on Thu Aug 28 12:39:42 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename ‘/oracle/app/oracle/oradata/database/cos11g/system01.dbf’; FILENAME /oracle/app/oracle/oradata/database/cos11g/system01.dbf
BBED> set block 20804 BLOCK# 20804
BBED> map File: /oracle/app/oracle/oradata/database/cos11g/system01.dbf (0) Block: 20804 Dba:0x00000000 ———————————————————— KTB Data Block (Index Leaf)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdxle, 32 bytes @92
sb2 kd_off[156] @124
ub1 freespace[952] @436
ub1 rowdata[6732] @1388
ub4 tailchk @8188
BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x02 (KDDBTINDEX) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00000025 ub4 ktbbhod1 @24 0x00000025 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x0012a91c ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 7938 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 0x0002 ub2 kxidslt @46 0x0015 ub4 kxidsqn @48 0x0000009f struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c03a8d ub2 kubaseq @56 0x001c ub1 kubarec @58 0x01 ub2 ktbitflg @60 0xc000 (KTBFIBI, KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x0002a810 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0006 ub2 kxidslt @70 0x0014 ub4 kxidsqn @72 0x00000442 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c001b8 ub2 kubaseq @80 0x010e ub1 kubarec @82 0x11 ub2 ktbitflg @84 0x0001 (NONE) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000
BBED> x /rncnn *kd_off[104] rowdata[4] @1392 ———- flag@1392: 0x00 (NONE) lock@1393: 0x02 keydata[6]: 0x00 0x41 0x42 0x1e 0x00 0x3d data key: col 0[1] @1401: 0 col 1[5] @1403: TEST2 col 2[2] @1409: 1 col 3[0] @1412: *NULL* col 4[0] @1413: *NULL* col 5[0] @1414: *NULL* col 6[2] @1415: 2 col 7[1] @1418: 0 col 8[4] @1420: 76845 |
6 故障处理过程
此故障处理的方法一般有2种
1，走全表扫描
2，手动提交事务信息
6.1 走全表扫描
由于这里是oracle2进制中的sql触发的报错，所以要走全表扫描，需要修改oracle2进制文件，见曾经的笔记ORA-08103，使用ue修改oracle2进制文件来完美解决，连接ue修改oracle文件
6.2 手动提交事务
相信这种情况下，大家一般会选择bbed的方法来解决，因为这种方法更简单，但是这种方法风险更高。如果要修改oracle2进制文件，需要在sql中增加full提示或者修改where后面列的信息，上面sql中引用的列都是字符集，增加更改起来比较麻烦，不仅需要改SQL内容，还需要更改其它地方，不然会open的时候会触发ORA-07445报错。
|
BED> x /rncnn *kd_off[104] rowdata[4] @1392 ———- flag@1392: 0x00 (NONE) lock@1393: 0x02 keydata[6]: 0x00 0x41 0x42 0x1e 0x00 0x3d data key: col 0[1] @1401: 0 col 1[5] @1403: TEST2 col 2[2] @1409: 1 col 3[0] @1412: *NULL* col 4[0] @1413: *NULL* col 5[0] @1414: *NULL* col 6[2] @1415: 2 col 7[1] @1418: 0 col 8[4] @1420: 76845
BBED> set offset 1393 OFFSET 1393
BBED> set count 10 COUNT 10
BBED> set mode edit MODE Edit
BBED> dump File: /oracle/app/oracle/oradata/database/cos11g/system01.dbf (0) Block: 20804 Offsets: 1393 to 1402 Dba:0x00000000 ———————————————————————— 02004142 1e003d01 8005
<32 bytes per line>
BBED> modify /x 00 offset 1393 File: /oracle/app/oracle/oradata/database/cos11g/system01.dbf (0) Block: 20804 Offsets: 1393 to 1402 Dba:0x00000000 ———————————————————————— 00004142 1e003d01 8005
<32 bytes per line>
BBED> verify DBVERIFY – Verification starting FILE = /oracle/app/oracle/oradata/database/cos11g/system01.dbf BLOCK = 20804
Block Checking: DBA = 4215108, Block Type = KTB-managed data block **** actual rows locked by itl 2 = 0 != # in trans. header = 1 —- end index block validation Block 20804 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 Message 531 not found; product=RDBMS; facility=BBED
BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x02 (KDDBTINDEX) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00000025 ub4 ktbbhod1 @24 0x00000025 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x0012a91c ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 7938 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 0x0002 ub2 kxidslt @46 0x0015 ub4 kxidsqn @48 0x0000009f struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c03a8d ub2 kubaseq @56 0x001c ub1 kubarec @58 0x01 ub2 ktbitflg @60 0xc000 (KTBFIBI, KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x0002a810 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0006 ub2 kxidslt @70 0x0014 ub4 kxidsqn @72 0x00000442 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c001b8 ub2 kubaseq @80 0x010e ub1 kubarec @82 0x11 ub2 ktbitflg @84 0x0001 (NONE) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000
BBED> modify /x offset 84 BBED-00209: invalid number (offset)
BBED> modify /x 0080 offset 84 File: /oracle/app/oracle/oradata/database/cos11g/system01.dbf (0) Block: 20804 Offsets: 84 to 93 Dba:0x00000000 ———————————————————————— 00800000 00000000 0000
<32 bytes per line>
BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x02 (KDDBTINDEX) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00000025 ub4 ktbbhod1 @24 0x00000025 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x0012a91c ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 7938 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 0x0002 ub2 kxidslt @46 0x0015 ub4 kxidsqn @48 0x0000009f struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c03a8d ub2 kubaseq @56 0x001c ub1 kubarec @58 0x01 ub2 ktbitflg @60 0xc000 (KTBFIBI, KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x0002a810 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0006 ub2 kxidslt @70 0x0014 ub4 kxidsqn @72 0x00000442 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c001b8 ub2 kubaseq @80 0x010e ub1 kubarec @82 0x11 ub2 ktbitflg @84 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00000000
BBED> verify DBVERIFY – Verification starting FILE = /oracle/app/oracle/oradata/database/cos11g/system01.dbf BLOCK = 20804
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 Message 531 not found; product=RDBMS; facility=BBED |
7，数据库打开
|
www.htz.pw > select open_mode from v$database;
OPEN_MODE ——————– MOUNTED
www.htz.pw > recover database using backup controlfile until cancel; ORA-00279: change 2223521 generated at 08/28/2014 12:30:54 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/COS11G/archivelog/2014_08_28/o1_mf_1_1_%u_ .arc ORA-00280: change 2223521 for thread 1 is in sequence #1
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: ‘/oracle/app/oracle/oradata/database/cos11g/system01.dbf’
ORA-01112: media recovery not started
www.htz.pw > alter database open resetlogs;
Database altered. |
这里看到数据库已经打开了，下面我们需要增加TEMP文件，观察ALERT中是否有报错，如果没有报错，取消参数，以正常的方式打开数据库。如果alert中没有任何报错，一般情况下，取消参数是可以正常打开数据库的。
整个测试结局
ORA-00604 ORA-01555故障处理过程：等您坐沙发呢！