分类目录归档：数据库

记录8.0.5数据库恢复过程

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

某银行需要恢复2004年磁带备份数据库 8.0.5,当时因为开发商使用begin backup命令进行热备,然后压缩到磁带上.在硬盘紧张,rman不成熟的时代,使用这样的方法备份本身没有大问题,可是因为没有备份归档日志,给现在的恢复带来了很多的问题.
尝试不完全恢复启动库

SVRMGR> startup pfile='init.ora' mount
ORACLE instance started.
Total System Global Area                        141826208 bytes
Fixed Size                                          47264 bytes
Variable Size                                   124829696 bytes
Database Buffers                                 16777216 bytes
Redo Buffers                                       172032 bytes
Database mounted.
SVRMGR> recover database using backup controlfile until cancel;
ORA-00279: change 613561556 generated at 08/21/04 22:42:48 needed for thread 1
ORA-00289: suggestion : /oracle/product/8.0.5/dbs/arch1_55329.dbf
ORA-00280: change 613561556 for thread 1 is in sequence #55329
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-00600: internal error code, arguments: [4146], [31400], [31370], [], [], [], [], []

这个错误是因为回滚段corruption导致,客户已经没有了一致性要求,所以解决办法是通过一些手段找出回滚段并屏蔽

屏蔽回滚段重启库

SVRMGR> shutdown abort
ORACLE instance shut down.
SVRMGR> startup  pfile='init.ora' mount
ORACLE instance started.
Total System Global Area                        141826208 bytes
Fixed Size                                          47264 bytes
Variable Size                                   124829696 bytes
Database Buffers                                 16777216 bytes
Redo Buffers                                       172032 bytes
Database mounted.
SVRMGR> alter database open;
alter database open
*
ORA-00600: internal error code, arguments: [3668], [1], [2], [55992], [55992], [4], [], []

根据ORA-00600[3668]错误的提示,因为重建控制文件,有一个数据文件因为在磁带恢复丢失,所以控制文件在上次重建的时候没有包含进去,根据经验在8.0中可以通过resetlogs解决

resetlogs重新打开数据库

SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SVRMGR> recover datatabase using backup controlfile until cancel;
ORA-00274: illegal recovery option DATATABASE
SVRMGR> recover database using backup controlfile until cancel;
ORA-00279: change 613561558 generated at 07/30/12 09:14:49 needed for thread 1
ORA-00289: suggestion : /oracle/product/8.0.5/dbs/arch1_1.dbf
ORA-00280: change 613561558 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number  with name "" too small

根据经验可能是屏蔽了undo或者undo损坏,然后有事务可能需要读undo,无法读到对应记录,可能出现此错误,因为无trace文件,尝试推进scn解决.当然这个问题可以通过open时做10046然后深入分析也许可以找到原因.

open数据库成功
根据上面的分析,重启库,推进scn,resetlogs库解决问题

SVRMGR> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup pfile='init.ora' mount;
ORACLE instance started.
Total System Global Area                        141826208 bytes
Fixed Size                                          47264 bytes
Variable Size                                   124829696 bytes
Database Buffers                                 16777216 bytes
Redo Buffers                                       172032 bytes
Database mounted.
SVRMGR> alter database open ;
alter database open 
*
ORA-00600: internal error code, arguments: [3668], [1], [2], [55994], [55994], [4], [], []
SVRMGR> recover database using backup controlfile until cancel;
ORA-00279: change 613561560 generated at 07/30/12 09:17:11 needed for thread 1
ORA-00289: suggestion : /oracle/product/8.0.5/dbs/arch1_1.dbf
ORA-00280: change 613561560 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
Statement processed.

这次恢复比较幸运,在备份到磁带过程中被覆盖的一个同名的数据文件,因为是index,通过查询dba_extents,然后删除并重建相关index,s数据库算恢复完全.有些时候,对数据库多一份备份,可能成为最后的救命稻草,哪怕是一份不是十分完整的备份.再次重复:对dba而言,数据库备份重于一切

发表在 Oracle备份恢复 | 标签为 ORA-600[3668]/ORA-600[4146] | 一条评论

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处理过的回滚段

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

因非常规操作导致删除表空间提示ORA-01561解决办法

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

今天测试手工通过bbed修改undo$中回滚段状态(从status$=5修改为1)[NEEDS RECOVERY 修改为 DELETE],然后尝试删除表空间,发现不能删除
删除表空间提示ORA-01561

SQL> drop tablespace undotbs;
drop tablespace undotbs
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified


SQL> drop tablespace undotbs including contents; 
drop tablespace undotbs including contents
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified

查询相关信息

SQL> select ts#,name from v$tablespace;

       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 SYSAUX
         4 USERS
         6 UNDOTBS
         3 TEMP

SQL> select name,ts#,status$ from undo$;

NAME                                  TS#    STATUS$
------------------------------ ---------- ----------
SYSTEM                                  0          3
_SYSSMU1_3138885392$                    2          1
_SYSSMU2_4228238222$                    2          1
_SYSSMU3_2210742642$                    2          1
_SYSSMU4_1455318006$                    2          1
_SYSSMU5_3787622316$                    2          1
_SYSSMU6_2460248069$                    2          1
_SYSSMU7_1924883037$                    2          1
_SYSSMU8_1909280886$                    2          1
_SYSSMU9_3593450615$                    2          1
_SYSSMU10_2490256178$                   2          1

NAME                                  TS#    STATUS$
------------------------------ ---------- ----------
_SYSSMU11_253524401$                    6          1
_SYSSMU12_842775869$                    6          1
_SYSSMU13_2794767139$                   6          1
_SYSSMU14_2067649841$                   6          1
_SYSSMU15_3270221471$                   6          1
_SYSSMU16_4094338609$                   6          1
_SYSSMU17_709661646$                    6          1
_SYSSMU18_699588262$                    6          1
_SYSSMU19_718640828$                    6          1
_SYSSMU20_3516920665$                   6          1
_SYSSMU21_793796797$                    6          1

NAME                                  TS#    STATUS$
------------------------------ ---------- ----------
_SYSSMU22_3988785920$                   6          1
_SYSSMU23_1828333848$                   6          1
_SYSSMU24_1223218862$                   6          1
_SYSSMU25_2939844199$                   6          1
_SYSSMU26_1317300205$                   6          1
_SYSSMU27_1654033223$                   6          1
_SYSSMU28_3748619502$                   6          1
_SYSSMU29_1868765904$                   6          1
_SYSSMU30_3379578723$                   6          1

31 rows selected.

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE

通过这里可以看出,通过bbed的修改,除system回滚段外,其他均已经被标志为delete状态,对于这样的情况,很本能的怀疑是extent或者segment未被清理掉导致

查询EXTENT和SEGMENT

SQL> select SEGMENT_NAME from dba_extents where TABLESPACE_NAME='UNDOTBS';

no rows selected

SQL> select segment_name from dba_segments where  TABLESPACE_NAME='UNDOTBS';

no rows selected

SQL> select count(*) from seg$ where ts#=6;

  COUNT(*)
----------
        10

SQL> select count(*) from seg$ where ts#=2;

  COUNT(*)
----------
         0

SQL> select file#,type# from  seg$ where ts#=6;

     FILE#      TYPE#
---------- ----------
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10

10 rows selected.

通过查询我们发现SEG$中含有10条记录,而通过dbms_metadata.get_ddl分析DBA_SEGMENTS是的,得出type为10恰好是TYPE2 UNDO信息.

解决办法
删除掉这些因为手工修改undo$信息导致遗留下来的后遗症对象

SQL> delete from seg$ where ts#=6;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> drop tablespace undotbs ;

Tablespace dropped.

这样的直接修改基表的做法,在一般的情况下非常不建议使用,可能带来系统不稳定.但是在数据库异常处理过程中,可能将成为一个法宝

发表在 Oracle | 标签为 ORA-01561 | 一条评论

17813235971

QQ 咨询

分类目录归档：数据库

记录8.0.5数据库恢复过程

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

因非常规操作导致删除表空间提示ORA-01561解决办法

文章分类

最近发表

分类目录归档：数据库

记录8.0.5数据库恢复过程

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

因非常规操作导致删除表空间提示ORA-01561解决办法

标签云

文章分类

最近发表