标签归档：UNDO$

在数据库open过程中常遇到ORA-01555汇总

发表于 2015 年 7 月 3 日 由 惜分飞

在数据库open的过程中，select ctime, mtime, stime from obj$ where obj# = :1语句报ORA-01555错误,数据库无法正常open
一般情况下会报某个回滚段,但是这里ORA-01555: snapshot too old: rollback segment number 0 with name “SYSTEM” too small这里直接报了system(系统回滚段),属于少见情况

Fri Jun 26 11:47:31 2015
SMON: enabling cache recovery
Fri Jun 26 11:47:31 2015
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0b41.37629378):
Fri Jun 26 11:47:31 2015
select ctime, mtime, stime from obj$ where obj# = :1
Fri Jun 26 11:47:31 2015
Errors in file /orabin/admin/doocrm/udump/doocrm_ora_5046722.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 0 with name "SYSTEM" too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 5046722
ORA-1092 signalled during: alter database open resetlogs...

在数据库open的过程中,select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1语句上报ORA-01555,导致数据库open失败
ORA-01555


在数据库open过程中,select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1语句导致数据库open失败.

ARC0: Becoming the 'no SRL' ARCH
Sun Jun 28 16:08:22 2015
ARC1: Becoming the heartbeat ARCH
Sun Jun 28 16:08:22 2015
SMON: enabling cache recovery
Sun Jun 28 16:08:22 2015
ORA-01555 caused by SQL statement below (SQL ID: 7bd391hat42zk, Query Duration=0 sec, SCN: 0x0d27.0a1ce29d):
Sun Jun 28 16:08:22 2015
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,
   DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
Sun Jun 28 16:08:22 2015
Errors in file /oracle/app/oracle/admin/ibsscrm/udump/xxxx_ora_30212428.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 30212428
ORA-1092 signalled during: alter database open...

出现这类问题,一般是由于obj$,seq$,undo$等基表上对象scn大于数据库当前scn或者是由于这些表上有事务未提交,出现上述两种情况,数据库需要找对应的undo的回滚段中记录,而此时对应的回滚段异常(或者是由于redo未进行正常前滚,导致上述对象或者回滚段记录不正常),从而出现类似情况,一般出现此类情况,可以通过10046定位到block，然后故障原因采用bbed修改scn或者bbed提交事务来解决此类问题.
最近两年的恢复中又遇到一些ora-00704 ora-00604 ora-01555的错误,导致数据库无法正常open,对其进行补充,具体参见:数据库open过程遭遇ORA-1555对应sql语句补充

发表在 Oracle | 标签为 , , , , , | 评论关闭

bbed修改undo$(回滚段)状态

发表于 2012 年 7 月 29 日 由 惜分飞

指定undo$对应数据块(11g 1_225,10g 1_106)[具体时候需要查询]

BBED> set file 1 block 225
        FILE#           1
        BLOCK#          225

查询数据内容,主要关注kdbr

BBED> map
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 225                                   Dba:0x004000e1
------------------------------------------------------------
 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[31]                               @86      <===表示31条记录,从0开始 

 ub1 freespace[3644]                        @148     

 ub1 rowdata[4396]                          @3792    

 ub4 tailchk                                @8188

查看30号回滚段内容(列举其中一个)

BBED> p *kdbr[30]
rowdata[0]
----------
ub1 rowdata[0]                              @3792     0x2c

BBED> x /rncnnnnnnn
rowdata[0]                                  @3792    
----------
flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@3793: 0x01
cols@3794:   17

col    0[2] @3795: 30 
col   1[21] @3798: _SYSSMU30_3379578723$
col    2[2] @3820: 1 
col    3[2] @3823: 3 
col    4[3] @3826: 432 
col    5[1] @3830: 0 
col    6[1] @3832: 0 
col    7[1] @3834: 0 
col    8[1] @3836: 0 
col    9[1] @3838: 0 
col   10[2] @3840: 5     <===修改前对应值undo$.status$
col   11[2] @3843: 6 
col   12[0] @3846: *NULL*
col   13[0] @3847: *NULL*
col   14[0] @3848: *NULL*
col   15[0] @3849: *NULL*
col   16[2] @3850: 2 

BBED> x /r
rowdata[0]                                  @3792    
----------
flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@3793: 0x01
cols@3794:   17

col    0[2] @3795:  0xc1  0x1f 
col   1[21] @3798:  0x5f  0x53  0x59  0x53  0x53  0x4d  0x55  0x33  0x30  0x5f 
 0x33  0x33  0x37  0x39  0x35  0x37  0x38  0x37  0x32  0x33  0x24 
col    2[2] @3820:  0xc1  0x02 
col    3[2] @3823:  0xc1  0x04 
col    4[3] @3826:  0xc2  0x05  0x21 
col    5[1] @3830:  0x80 
col    6[1] @3832:  0x80 
col    7[1] @3834:  0x80 
col    8[1] @3836:  0x80 
col    9[1] @3838:  0x80 
col   10[2] @3840:  0xc1  0x06   <===修改前16进制值 
col   11[2] @3843:  0xc1  0x07 
col   12[0] @3846: *NULL*
col   13[0] @3847: *NULL*
col   14[0] @3848: *NULL*
col   15[0] @3849: *NULL*
col   16[2] @3850:  0xc1  0x03

修改回滚段状态

BBED> m /x 02 offset 3842    <===注意修改为02
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 225              Offsets: 3842 to 4353           Dba:0x004000e1
------------------------------------------------------------------------
 0202c107 ffffffff 02c1032c 001102c1 


 <32 bytes per line>

BBED> p *kdbr[30]
rowdata[0]
----------
ub1 rowdata[0]                              @3792     0x2c

BBED> x /r
rowdata[0]                                  @3792    
----------
flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@3793: 0x01
cols@3794:   17

col    0[2] @3795:  0xc1  0x1f 
col   1[21] @3798:  0x5f  0x53  0x59  0x53  0x53  0x4d  0x55  0x33  0x30  0x5f 
 0x33  0x33  0x37  0x39  0x35  0x37  0x38  0x37  0x32  0x33  0x24 
col    2[2] @3820:  0xc1  0x02 
col    3[2] @3823:  0xc1  0x04 
col    4[3] @3826:  0xc2  0x05  0x21 
col    5[1] @3830:  0x80 
col    6[1] @3832:  0x80 
col    7[1] @3834:  0x80 
col    8[1] @3836:  0x80 
col    9[1] @3838:  0x80 
col   10[2] @3840:  0xc1  0x02   <===修改后16进制值
col   11[2] @3843:  0xc1  0x07 
col   12[0] @3846: *NULL*
col   13[0] @3847: *NULL*
col   14[0] @3848: *NULL*
col   15[0] @3849: *NULL*
col   16[2] @3850:  0xc1  0x03 

BBED>  x /rncnnnnnnn
rowdata[0]                                  @3792    
----------
flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@3793: 0x01
cols@3794:   17

col    0[2] @3795: 30 
col   1[21] @3798: _SYSSMU30_3379578723$
col    2[2] @3820: 1 
col    3[2] @3823: 3 
col    4[3] @3826: 432 
col    5[1] @3830: 0 
col    6[1] @3832: 0 
col    7[1] @3834: 0 
col    8[1] @3836: 0 
col    9[1] @3838: 0 
col   10[2] @3840: 1    <===实际展示值undo$.status$
col   11[2] @3843: 6 
col   12[0] @3846: *NULL*
col   13[0] @3847: *NULL*
col   14[0] @3848: *NULL*
col   15[0] @3849: *NULL*
col   16[2] @3850: 2

1.修改为02(表示的实际值比修改值大1[1:DELETE,2:OFFLINE, 3:ONLINE,4:UNDEFINED,5:NEEDS RECOVERY,6:PARTLY AVAILABLE,其他表示:UNDEFINED])
2.Offset需要在10列漂移量上+2(或者11列偏移量-1)

修改验证值

BBED> sum apply
Check value for File 1, Block 225:
current = 0x9708, required = 0x9708

补充说明
1)通过dbms_metadata.get_ddl分析DBA_ROLLBACK_SEGS可以知道seg$.status$表示含义

2, 'OFFLINE'
3, 'ONLINE',
4, 'UNDEFINED'
5, 'NEEDS RECOVERY',
6, 'PARTLY AVAILABLE'
other, 'UNDEFINED'

2)该方法一般出现在使用隐含参数屏蔽回滚段无效之后使用,一般不到最后,不建议使用该方法,可能引起不可预知的后果
3)使用该方法启动数据库后,需要手工删除seg$中被bbed处理过的回滚段

发表在 非常规恢复 | 标签为 , , | 一条评论