UPDATE GLOBAL_NAME为空之后的恢复

老熊在“UPDATE GLOBAL_NAME为空之后的恢复”这篇文章里用的恢复方法中用到了gdbkokiasg的特性,这种方法虽然很简洁,但是过于internal

我一时技痒,这里用到了常规的方法恢复,朋友们可参考老熊和我的这篇文章,以后应对这种在没有备份情况下UPDATE GLOBAL_NAME为空之后的恢复就心中有数了。

 

首先要说明一点的是,UPDATE GLOBAL_NAME为空之后库起不来只会存在于10g之后的版本,9i里不存在这样的问题,9i里可以随便改:

SQL_testdb>select * from props$;

rows will be truncated

NAME                           VALUE$

——————————           —————————————–

……省略显示部分内容

GLOBAL_DB_NAME                 TESTDB

……省略显示部分内容

NLS_RDBMS_VERSION              9.2.0.6.0

 

25 rows selected.

 

SQL_testdb>update global_name set global_name=”;                                  

 

1 row updated.

 

SQL_testdb>commit;

 

Commit complete.

 

SQL_testdb>shutdown abort

ORACLE instance shut down.

 

SQL_testdb>startup pfile=/dras20/testdb/inittestdb.ora

ORACLE instance started.

 

Total System Global Area  504858456 bytes

Fixed Size                   743256 bytes

Variable Size             285212672 bytes

Database Buffers          218103808 bytes

Redo Buffers                 798720 bytes

Database mounted.

Database opened.

 

10g及其以后的版本你如果还是这么做,库就起不来了。

我们首先在10g里重现一下上述问题:

$ sqlplus ‘/ as sysdba’;

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 7 10:04:55 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

 

sys@TESTDB>select * from global_name;

 

GLOBAL_NAME

—————————————————————————————————-

TESTDB

 

sys@TESTDB>update global_name set global_name=”;

 

1 row updated.

 

sys@TESTDB>commit;

 

Commit complete.

 

shutdown abort增加恢复的难度:

sys@TESTDB>shutdown abort

ORACLE instance shut down.

 

此时上述库已经起不来了:

sys@TESTDB>startup pfile=/ipratest2/oradata/testdb/inittestdb.ora

ORACLE instance started.

 

Total System Global Area  419430400 bytes

Fixed Size                  2021320 bytes

Variable Size             130025528 bytes

Database Buffers          281018368 bytes

Redo Buffers                6365184 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

 

此时alert log里会报错:

Errors in file /u01/app/oracle/admin/testdb/udump/testdb_ora_839892.trc:

ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], []

Tue Sep  7 10:11:40 2010

Errors in file /u01/app/oracle/admin/testdb/udump/testdb_ora_839892.trc:

ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], []

Tue Sep  7 10:11:40 2010

Errors in file /u01/app/oracle/admin/testdb/udump/testdb_ora_839892.trc:

ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], []

 

好了,现在我们来恢复。

这里我在修改数据字典props$的过程中用到了老熊写的ODU 3.5ODU 3.5在初步支持ASM的基础上增加了一个功能点——就是在unload table的过程中可以把这行记录所在的RDBArow directory里的行号给print出来,这一举解决了原先我在修改数据字典的过程中不太方便定位的问题,从此以后我可以随心所欲。

ODU 4.0即将推出,它里面涵盖了这样一些功能点,敬请期待:

1、  全面支持ASM

2、  全面支持在缺失lob index情况下lob的恢复,支持带lob字段的表在truncate后的恢复;

3、  全面内嵌BBED——从此以后我们有了属于我们自己的跨平台的BBED

4、  ……

附:ODU最新的版本是4.1.5,除了支持上述功能点外,还支持从损坏的文件系统里恢复数据。

 

好了,回到我们刚才的那个问题,我这里记录下我详细的修改过程,略显枯燥,但其实并不复杂:

ODU> unload table sys.props$

 

Unloading table: PROPS$,object ID: 96

Unloading segment,storage(Obj#=96 DataObj#=96 TS#=0 File#=1 Block#=721 Cluster=0)

27 rows unloaded

 

$ cat SYS_PROPS$.txt

DICT.BASE|2|dictionary base tables version #|0x004002d2|0

……省略显示部分内容

GLOBAL_DB_NAME||Global database name|0x004002d2|25

EXPORT_VIEWS_VERSION|8|Export views revision #|0x004002d2|26

 

BBED> set dba 0x004002d2

        DBA             0x004002d2 (4195026 1,722)

 

BBED> find /x 474c4f42

 File: /ipratest2/oradata/testdb/system01.dbf (1)

 Block: 722              Offsets: 6755 to 7266           Dba:0x004002d2

————————————————————————

 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162 61736520

 6e616d65 2c00030e 474c4f42 414c5f44 425f4e41 4d450654 45535444 4214476c

 ……省略显示部分内容

 796d626f 6c2c0003 174e4c53 5f54494d 45535441 4d505f54 5a5f464f 524d4154

 

 <32 bytes per line>

 

BBED> f

 File: /ipratest2/oradata/testdb/system01.dbf (1)

 Block: 722              Offsets: 6795 to 7306           Dba:0x004002d2

————————————————————————

 474c4f42 414c5f44 425f4e41 4d450654 45535444 4214476c 6f62616c 20646174

 61626173 65206e61 6d653c01 2c000314 4558504f 52545f56 49455753 5f564552

……省略显示部分内容

52522048 482e4d49 2e535358 46462041 4d20545a 521e5469 6d657374 616d7020

 

 <32 bytes per line>

 

BBED> f

 File: /ipratest2/oradata/testdb/system01.dbf (1)

 Block: 722              Offsets: 6893 to 7404           Dba:0x004002d2

————————————————————————

 474c4f42 414c5f44 425f4e41 4d450853 45454444 41544114 476c6f62 616c2064

 61746162 61736520 6e616d65 2c000311 4e4c535f 5244424d 535f5645 5253494f

 ……省略显示部分内容

 20776974 68207469 6d657a6f 6e652066 6f726d61 742c0003 144e4c53 5f54494d

 

 <32 bytes per line>

 

BBED> f

BBED-00212: search string not found

 

BBED> p kdbr

sb2 kdbr[0]                                 @110      8048

……省略显示部分内容

sb2 kdbr[25]                                @160      6659

sb2 kdbr[26]                                @162      6747

sb2 kdbr[27]                                @164     -1

 

BBED> set offset 160

        OFFSET          160

 

BBED> dump

 File: /ipratest2/oradata/testdb/system01.dbf (1)

 Block: 722              Offsets:  160 to  671           Dba:0x004002d2

————————————————————————

 1a031a5b ffff0000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 ……省略显示部分内容

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

 <32 bytes per line>

 

核心的修改就这只有如下这句,也就是修改了row directory里的指针:

BBED> modify /x 1a2b

 File: /ipratest2/oradata/testdb/system01.dbf (1)

 Block: 722              Offsets:  160 to  671           Dba:0x004002d2

————————————————————————

 1a2b1a5b ffff0000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 ……省略显示部分内容

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 1, Block 722:

current = 0x0365, required = 0x0365

 

BBED> verify

DBVERIFY – Verification starting

FILE = /ipratest2/oradata/testdb/system01.dbf

BLOCK = 722

 

Block Checking: DBA = 4195026, Block Type = KTB-managed data block

data header at 0x1101b905c

kdbchk: xaction header lock count mismatch

        trans=1 ilk=1 nlo=0     

Block 722 failed with check code 6108

……省略显示部分内容

Total Blocks Influx           : 0

 

后续的修正上述verify错误的过程中还会碰到如下错误,限于篇幅,修正这些错误的过程省略,很容易的,毫无难度:

kdbchk: the amount of space used is not equal to block size

        used=1421 fsc=6 avsp=6675 dtl=8096

 

kdbchk: row locked by non-existent transaction

        table=0   slot=25

        lockid=1   ktbbhitc=2

 

kdbchk: space available on commit is incorrect

        tosp=6681 fsc=0 stb=0 avsp=6675

 

修正完上述错误后此时verify已经不报错:

BBED> verify

DBVERIFY – Verification starting

FILE = /ipratest2/oradata/testdb/system01.dbf

BLOCK = 722

DBVERIFY – Verification complete

 

Total Blocks Examined         : 1

Total Blocks Processed (Data) : 1

Total Blocks Failing   (Data) : 0

Total Blocks Processed (Index): 0

Total Blocks Failing   (Index): 0

Total Blocks Empty            : 0

Total Blocks Marked Corrupt   : 0

Total Blocks Influx           : 0

 

上述起不来的库现在已经可以open

$ sqlplus ‘/ as sysdba’;

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 7 10:49:49 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

 

idle>startup pfile=/ipratest2/oradata/testdb/inittestdb.ora

ORACLE instance started.

 

Total System Global Area  419430400 bytes

Fixed Size                  2021320 bytes

Variable Size             130025528 bytes

Database Buffers          281018368 bytes

Redo Buffers                6365184 bytes

Database mounted.

Database opened.

 

idle>select * from global_name;

 

GLOBAL_NAME

—————————————————————————————————-

TESTDB