当前位置: 首页 > BACKUP & RESTORE, BBED, ORA > 正文

ORA-00604 ORA-01555故障处理过程

下面故障来至于群面一位兄弟,日志文件被重制后,打开数据库遇到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获取详细的日志内容

下面我们配置了10046errorstackevent来获取详细的信息

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,使用的插槽号为14trap#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段头中index14的事务状态是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中没有任何报错,一般情况下,取消参数是可以正常打开数据库的。

 

整个测试结局

本文固定链接: http://www.htz.pw/2014/08/30/ora-00604-ora-01555%e6%95%85%e9%9a%9c%e5%a4%84%e7%90%86%e8%bf%87%e7%a8%8b.html | 认真就输

该日志由 huangtingzhong 于2014年08月30日发表在 BACKUP & RESTORE, BBED, ORA 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: ORA-00604 ORA-01555故障处理过程 | 认真就输
关键字: ,