sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle global_name设置为空的研究笔记

时间:2016-12-12 21:36   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库设置global_name为空的笔记。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: update global_name为空的恢复

今天突然看到惜分飞的网站上描述关于global_name设置为空的文章,想起以前老熊和dbsnake也都写过一篇,其中老熊是参考的mos文档1431334.1。我这里也是模仿的他们使用bbed的方式,当是自己随便玩玩了。大家就不要模仿了。

SQL> SELECT * FROM v$version WHERE rownum <3;

BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production

SQL> SELECT * FROM global_name;

GLOBAL_NAME
---------------------------------------------------------------------
ROGER

SQL> SELECT owner,object_name,object_type FROM dba_objects WHERE object_name=UPPER('global_name');

OWNER                          OBJECT_NAME               OBJECT_TYPE
------------------------------ ------------------------- -------------------------
SYS                            GLOBAL_NAME               VIEW
PUBLIC                         GLOBAL_NAME               SYNONYM

SQL> SELECT dbms_metadata.get_ddl('VIEW','GLOBAL_NAME','SYS') FROM dual;

DBMS_METADATA.GET_DDL('VIEW','GLOBAL_NAME','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."GLOBAL_NAME" ("GLOBAL_NAME") AS
  SELECT VALUE$ FROM sys.props$ WHERE name = 'GLOBAL_DB_NAME'

SQL> SELECT * FROM props$ WHERE VALUE$=UPPER('roger');

NAME                           VALUE$                         COMMENT$
------------------------------ ------------------------------ --------------------
GLOBAL_DB_NAME                 ROGER                          Global DATABASE name

SQL>
SQL> SET pagesize 100
SQL> l
  1* SELECT * FROM props$
SQL> /

NAME                           VALUE$                                        COMMENT$
------------------------------ --------------------------------------------- ------------------------------------------------------------
DICT.BASE                      2                                             dictionary base TABLES version #
DEFAULT_TEMP_TABLESPACE        TEMP                                          Name OF DEFAULT TEMPORARY tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                                         Name OF DEFAULT permanent tablespace
DEFAULT_TBS_TYPE               SMALLFILE                                     DEFAULT tablespace TYPE
NLS_LANGUAGE                   AMERICAN                                      LANGUAGE
NLS_TERRITORY                  AMERICA                                       Territory
NLS_CURRENCY                   $                                             LOCAL currency
NLS_ISO_CURRENCY               AMERICA                                       ISO currency
NLS_NUMERIC_CHARACTERS         .,                                            NUMERIC characters
NLS_CHARACTERSET               ZHS16GBK                                      CHARACTER SET
NLS_CALENDAR                   GREGORIAN                                     Calendar system
NLS_DATE_FORMAT                DD-MON-RR                                     DATE format
NLS_DATE_LANGUAGE              AMERICAN                                      DATE LANGUAGE
NLS_SORT                       BINARY                                        Linguistic definition
NLS_TIME_FORMAT                HH.MI.SSXFF AM                                TIME format
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                      TIME stamp format
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                            TIME WITH timezone format
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR                  TIMESTAMP WITH timezone format
NLS_DUAL_CURRENCY              $                                             Dual currency symbol
NLS_COMP                       BINARY                                        NLS comparison
NLS_LENGTH_SEMANTICS           BYTE                                          NLS LENGTH semantics
NLS_NCHAR_CONV_EXCP            FALSE                                         NLS conversion exception
NLS_NCHAR_CHARACTERSET         AL16UTF16                                     NCHAR CHARACTER SET
NLS_RDBMS_VERSION              10.2.0.5.0                                    RDBMS version FOR NLS parameters
GLOBAL_DB_NAME                 ROGER                                         Global DATABASE name
EXPORT_VIEWS_VERSION           8                                             Export views revision #
WORKLOAD_CAPTURE_MODE                                                        CAPTURE implies workload capture IS IN progress
NO_USERID_VERIFIER_SALT        0F63020413CDBA8538451B60EA36171E
DBTIMEZONE                     00:00                                         DB TIME zone

29 ROWS selected.

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) blk#
  3    FROM global_name;

     FILE#       BLK#
---------- ----------
         1        722

SQL>
SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) blk#
  3    FROM sys.props$
  4   WHERE name = 'GLOBAL_DB_NAME'
  5  /

     FILE#       BLK#
---------- ----------
         1        722

从上来看来,global_name信息来源于props$数据字典表,而该数据字典表的信息都集中在一个block上,那就是file 1,block 722.

—-下面是整个模拟过程

SQL> show user                        
USER is "SYS"
SQL> update global_name set global_name='';

1 row updated.

SQL> commit;
Commit complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             146801896 bytes
Database Buffers           16777216 bytes
Redo Buffers                2920448 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

此时alert log如下:
Tue Aug 14 00:40:05 PDT 2012
Errors in file /home/ora10g/admin/roger/udump/roger_ora_20863.trc:
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], []
Tue Aug 14 00:40:05 PDT 2012
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 20863
ORA-1092 signalled during: ALTER DATABASE OPEN...

分析trace,搜索nrow= 发现如下信息;

Block header dump:  0x004002d2
Object id on Block  Y
seg/obj: 0x60  csc: 0x00.60d2a  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.00f.0000010c  0x008005dd.00ef.4e  C---    0  scn 0x0000.00060d29
0x02   0x0003.01e.00000384  0x0080b676.024c.06  --U-    1  fsc 0x0005.004750b8

data_block_dump,data header at 0x212ba05c
===============
tsiz: 0x1fa0
hsiz: 0x4e
pbl: 0x212ba05c
bdba: 0x004002d2
     76543210
flag=--------
ntab=1
nrow=30
frre=3
fsbo=0x4e
fseo=0x197e
avsp=0x198a
tosp=0x198f
0xe:pti[0]      nrow=30 ohttp://www.oracleplus.netffs=0
.......省略部分信息
tab 0, row 25, @0x197e   ---这里是offset 6526
tl: 40 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [14]  47 4c 4f 42 41 4c 5f 44 42 5f 4e 41 4d 45
col  1: *NULL*   ---第2列被我们updtae为空了
col  2: [20]  47 6c 6f 62 61 6c 20 64 61 74 61 62 61 73 65 20 6e 61 6d 65
tab 0, row 26, @0x1a7a

我们来看看第25行是不是我们所需要的信息:
SQL> select hextostr('47 4c 4f 42 41 4c 5f 44 42 5f 4e 41 4d 45') colname from dual;

COLNAME
--------------------------------------------------------------------------------
GLOBAL_DB_NAME

SQL> select hextostr('47 6c 6f 62 61 6c 20 64 61 74 61 62 61 73 65 20 6e 61 6d 65') colname from dual;

COLNAME
--------------------------------------------------------------------------------
Global database name

SQL>
那么我们需要定位的这一行记录的准确的位置应该是多少呢?
那根据算法来看,我们这里第25行记录的实际位置应该是:6526+76+(2-1)*24=6626

下面我们开始使用bbed来进行操作:

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

BBED> d /v offset 6626
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722     Offsets: 6626 to 7137  Dba:0x004002d2
-------------------------------------------------------
414c5f44 425f4e41 4d45ff14 476c6f62 l AL_DB_NAME..Glob
616c2064 61746162 61736520 6e616d65 l al database name
2c00030e 474c4f42 414c5f44 425f4e41 l ,...GLOBAL_DB_NA
4d450552 4f474552 14476c6f 62616c20 l ME.ROGER.Global
64617461 62617365 206e616d 652c0003 l database name,..
0a444254 494d455a 4f4e4505 30303a30 l .DBTIMEZONE.00:0
300c4442 2074696d 65207a6f 6e652c00 l 0.DB time zone,.
02174e4f 5f555345 5249445f 56455249 l ..NO_USERID_VERI
46494552 5f53414c 54203046 36333032 l FIER_SALT 0F6302
30343133 43444241 38353338 34353142 l 0413CDBA8538451B
36304541 33363137 31452c00 0315574f l 60EA36171E,...WO
524b4c4f 41445f43 41505455 52455f4d l RKLOAD_CAPTURE_M
4f4445ff 2f434150 54555245 20696d70 l ODE./CAPTURE imp
6c696573 20776f72 6b6c6f61 64206361 l lies workload ca
70747572 65206973 20696e20 70726f67 l pture is in prog
72657373 2c000314 4558504f 52545f56 l ress,...EXPORT_V
49455753 5f564552 53494f4e 01381745 l IEWS_VERSION.8.E
78706f72 74207669 65777320 72657669 l xport views revi
73696f6e 20232c00 030e474c 4f42414c l sion #,...GLOBAL
5f44425f 4e414d45 08534545 44444154 l _DB_NAME.SEEDDAT
4114476c 6f62616c 20646174 61626173 l A.Global databas
65206e61 6d652c00 03114e4c 535f5244 l e name,...NLS_RD
424d535f 56455253 494f4e0a 31302e32 l BMS_VERSION.10.2
2e302e35 2e302052 44424d53 20766572 l .0.5.0 RDBMS ver
73696f6e 20666f72 204e4c53 20706172 l sion for NLS par
616d6574 6572732c 0003164e 4c535f4e l ameters,...NLS_N
43484152 5f434841 52414354 45525345 l CHAR_CHARACTERSE
5409414c 31365554 46313613 4e434841 l T.AL16UTF16.NCHA
52204368 61726163 74657220 7365742c l R Character set,
0003134e 4c535f4e 43484152 5f434f4e l ...NLS_NCHAR_CON
565f4558 43500546 414c5345 184e4c53 l V_EXCP.FALSE.NLS
20636f6e 76657273 696f6e20 65786365 l  conversion exce

<16 bytes per line>

BBED> p kdbr
sb2 kdbr[0]                                 @110      8048
sb2 kdbr[1]                                 @112      7979
sb2 kdbr[2]                                 @114      7904
sb2 kdbr[3]                                 @116     -1
sb2 kdbr[4]                                 @118      7848
sb2 kdbr[5]                                 @120      7814
sb2 kdbr[6]                                 @122      7779
sb2 kdbr[7]                                 @124      7746
sb2 kdbr[8]                                 @126      7705
sb2 kdbr[9]                                 @128      7657
sb2 kdbr[10]                                @130      7614
sb2 kdbr[11]                                @132      7572
sb2 kdbr[12]                                @134      7531
sb2 kdbr[13]                                @136      7487
sb2 kdbr[14]                                @138      7446
sb2 kdbr[15]                                @140      7400
sb2 kdbr[16]                                @142      7333
sb2 kdbr[17]                                @144      7266
sb2 kdbr[18]                                @146      7179
sb2 kdbr[19]                                @148      7135
sb2 kdbr[20]                                @150      7101
sb2 kdbr[21]                                @152      7051
sb2 kdbr[22]                                @154      6997
sb2 kdbr[23]                                @156      6941
sb2 kdbr[24]                                @158      6876
sb2 kdbr[25]                                @160      6526
sb2 kdbr[26]                                @162      6778
sb2 kdbr[27]                                @164      6704
sb2 kdbr[28]                                @166      6644
sb2 kdbr[29]                                @168      6611

BBED> p *kdbr[25]
rowdata[0]
----------
ub1 rowdata[0]                              @6618     0x2c

BBED> x /rccccccccc
rowdata[0]                                  @6618   
----------
flag@6618: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6619: 0x02
cols@6620:    3

col   0[14] @6621: GLOBAL_DB_NAME
col    1[0] @6636: *NULL*
col   2[20] @6637: Global database name



BBED>
BBED> d /v offset 6636
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722     Offsets: 6636 to 7147  Dba:0x004002d2
-------------------------------------------------------
ff14476c 6f62616c 20646174 61626173 l ..Global databas   ---ff14 就是对应的null(应该是'')
65206e61 6d652c00 030e474c 4f42414c l e name,...GLOBAL
5f44425f 4e414d45 05524f47 45521447 l _DB_NAME.ROGER.G
6c6f6261 6c206461 74616261 7365206e l lobal database n
616d652c 00030a44 4254494d 455a4f4e l ame,...DBTIMEZON
45053030 3a30300c 44422074 696d6520 l E.00:00.DB time
7a6f6e65 2c000217 4e4f5f55 53455249 l zone,...NO_USERI
445f5645 52494649 45525f53 414c5420 l D_VERIFIER_SALT
30463633 30323034 31334344 42413835 l 0F63020413CDBA85
33383435 31423630 45413336 31373145 l 38451B60EA36171E
2c000315 574f524b 4c4f4144 5f434150 l ,...WORKLOAD_CAP
54555245 5f4d4f44 45ff2f43 41505455 l TURE_MODE./CAPTU
52452069 6d706c69 65732077 6f726b6c l RE implies workl
6f616420 63617074 75726520 69732069 l oad capture is i
6e207072 6f677265 73732c00 03144558 l n progress,...EX
504f5254 5f564945 57535f56 45525349 l PORT_VIEWS_VERSI
4f4e0138 17457870 6f727420 76696577 l ON.8.Export view
73207265 76697369 6f6e2023 2c00030e l s revision #,...
474c4f42 414c5f44 425f4e41 4d450853 l GLOBAL_DB_NAME.S
45454444 41544114 476c6f62 616c2064 l EEDDATA.Global d
61746162 61736520 6e616d65 2c000311 l atabase name,...
4e4c535f 5244424d 535f5645 5253494f l NLS_RDBMS_VERSIO
4e0a3130 2e322e30 2e352e30 20524442 l N.10.2.0.5.0 RDB
4d532076 65727369 6f6e2066 6f72204e l MS version for N
4c532070 6172616d 65746572 732c0003 l LS parameters,..
164e4c53 5f4e4348 41525f43 48415241 l .NLS_NCHAR_CHARA
43544552 53455409 414c3136 55544631 l CTERSET.AL16UTF1
36134e43 48415220 43686172 61637465 l 6.NCHAR Characte
72207365 742c0003 134e4c53 5f4e4348 l r set,...NLS_NCH
41525f43 4f4e565f 45584350 0546414c l AR_CONV_EXCP.FAL
5345184e 4c532063 6f6e7665 7273696f l SE.NLS conversio
6e206578 63657074 696f6e2c 0003144e l n exception,...N

<16 bytes per line>


由于update的原理实际是修改row directory指针,也就是说原值仍然是存在的,我们使用
find 进行搜索下字符串:474c4f42
BBED> f /x 474c4f42
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722              Offsets: 6622 to 7133           Dba:0x004002d2
------------------------------------------------------------------------
474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162 61736520 ---刚刚我们手工update的值,ff14 对应''
6e616d65 2c00030e 474c4f42 414c5f44 425f4e41 4d450552 4f474552 14476c6f
62616c20 64617461 62617365 206e616d 652c0003 0a444254 494d455a 4f4e4505
30303a30 300c4442 2074696d 65207a6f 6e652c00 02174e4f 5f555345 5249445f
56455249 46494552 5f53414c 54203046 36333032 30343133 43444241 38353338
34353142 36304541 33363137 31452c00 0315574f 524b4c4f 41445f43 41505455
52455f4d 4f4445ff 2f434150 54555245 20696d70 6c696573 20776f72 6b6c6f61
64206361 70747572 65206973 20696e20 70726f67 72657373 2c000314 4558504f
52545f56 49455753 5f564552 53494f4e 01381745 78706f72 74207669 65777320
72657669 73696f6e 20232c00 030e474c 4f42414c 5f44425f 4e414d45 08534545
44444154 4114476c 6f62616c 20646174 61626173 65206e61 6d652c00 03114e4c
535f5244 424d535f 56455253 494f4e0a 31302e32 2e302e35 2e302052 44424d53
20766572 73696f6e 20666f72 204e4c53 20706172 616d6574 6572732c 0003164e
4c535f4e 43484152 5f434841 52414354 45525345 5409414c 31365554 46313613
4e434841 52204368 61726163 74657220 7365742c 0003134e 4c535f4e 43484152
5f434f4e 565f4558 43500546 414c5345 184e4c53 20636f6e 76657273 696f6e20

<32 bytes per line>

BBED> f
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722              Offsets: 6662 to 7173           Dba:0x004002d2
------------------------------------------------------------------------
474c4f42 414c5f44 425f4e41 4d450552 4f474552 14476c6f 62616c20 64617461 --第一次update的值:0552 4f474552 14为原值roger,如下:
62617365 206e616d 652c0003 0a444254 494d455a 4f4e4505 30303a30 300c4442
2074696d 65207a6f 6e652c00 02174e4f 5f555345 5249445f 56455249 46494552
5f53414c 54203046 36333032 30343133 43444241 38353338 34353142 36304541
33363137 31452c00 0315574f 524b4c4f 41445f43 41505455 52455f4d 4f4445ff
2f434150 54555245 20696d70 6c696573 20776f72 6b6c6f61 64206361 70747572
65206973 20696e20 70726f67 72657373 2c000314 4558504f 52545f56 49455753
5f564552 53494f4e 01381745 78706f72 74207669 65777320 72657669 73696f6e
20232c00 030e474c 4f42414c 5f44425f 4e414d45 08534545 44444154 4114476c
6f62616c 20646174 61626173 65206e61 6d652c00 03114e4c 535f5244 424d535f
56455253 494f4e0a 31302e32 2e302e35 2e302052 44424d53 20766572 73696f6e
20666f72 204e4c53 20706172 616d6574 6572732c 0003164e 4c535f4e 43484152
5f434841 52414354 45525345 5409414c 31365554 46313613 4e434841 52204368
61726163 74657220 7365742c 0003134e 4c535f4e 43484152 5f434f4e 565f4558
43500546 414c5345 184e4c53 20636f6e 76657273 696f6e20 65786365 7074696f
6e2c0003 144e4c53 5f4c454e 4754485f 53454d41 4e544943 53044259 5445144e

<32 bytes per line>

BBED> f
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722              Offsets: 6924 to 7435           Dba:0x004002d2
------------------------------------------------------------------------
474c4f42 414c5f44 425f4e41 4d450853 45454444 41544114 476c6f62 616c2064   ---初始化值 0853 45454444 41544114
61746162 61736520 6e616d65 2c000311 4e4c535f 5244424d 535f5645 5253494f
4e0a3130 2e322e30 2e352e30 20524442 4d532076 65727369 6f6e2066 6f72204e
4c532070 6172616d 65746572 732c0003 164e4c53 5f4e4348 41525f43 48415241
43544552 53455409 414c3136 55544631 36134e43 48415220 43686172 61637465
72207365 742c0003 134e4c53 5f4e4348 41525f43 4f4e565f 45584350 0546414c
5345184e 4c532063 6f6e7665 7273696f 6e206578 63657074 696f6e2c 0003144e
4c535f4c 454e4754 485f5345 4d414e54 49435304 42595445 144e4c53 206c656e
67746820 73656d61 6e746963 732c0003 084e4c53 5f434f4d 50064249 4e415259
0e4e4c53 20636f6d 70617269 736f6e2c 0003114e 4c535f44 55414c5f 43555252
454e4359 01241444 75616c20 63757272 656e6379 2073796d 626f6c2c 0003174e
4c535f54 494d4553 54414d50 5f545a5f 464f524d 41541c44 442d4d4f 4e2d5252
2048482e 4d492e53 53584646 20414d20 545a521e 54696d65 7374616d 70207769
74682074 696d657a 6f6e6520 666f726d 61742c00 03124e4c 535f5449 4d455f54
5a5f464f 524d4154 1248482e 4d492e53 53584646 20414d20 545a5219 54696d65
20776974 68207469 6d657a6f 6e652066 6f726d61 742c0003 144e4c53 5f54494d

<32 bytes per line>

BBED> f
BBED-00212: search string not found


BBED>
下面将上面的字符串进行转换:

SQL> select hextostr('05 52 4f 47 45 52 14') colname from dual;

COLNAME
--------------------------------------------------------------------------------
ROGER

SQL>
SQL> select hextostr('08 53 45 45 44 44 41 54 41 14') colname from dual;

COLNAME
--------------------------------------------------------------------------------
SEEDDATA

SQL>
可以看到global name一共是update了2次,转换以后分别是SEEDDATA,和roger,当然最后我们更新为”了。

我们来看下offset的变化:
初始化:SEEDDATA offset 6938
第一次update: ROGER offset 6676
第二次update: ” offset 6636

我们知道update语句的实质,oracle并没有将原始记录删除,而是是修改了row directory 指针而已。
比如这里,从6676改到了6636. 按照dbsnake的描述,我们只需要将指针修改为回去即可。

那么,能否就在远处修改呢?比如尝试将ff14 直接修改为05 52 4f 47 45 52 14呢? 很显然不行。
因为ff14 对于2个offset,而后面对于7个offset了。

BBED> d /v offset 6610 count 100
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722     Offsets: 6610 to 6709  Dba:0x004002d2
-------------------------------------------------------
00000000 00000000 2c02030e 474c4f42 l ........,...GLOB
414c5f44 425f4e41 4d45ff14 476c6f62 l AL_DB_NAME..Glob
616c2064 61746162 61736520 6e616d65 l al database name
2c00030e 474c4f42 414c5f44 425f4e41 l ,...GLOBAL_DB_NA
4d450552 4f474552 14476c6f 62616c20 l ME.ROGER.Global
64617461 62617365 206e616d 652c0003 l database name,..
0a444254                            l .DBT

<16 bytes per line>

BBED> d /v offset 6650 count 100
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722     Offsets: 6650 to 6749  Dba:0x004002d2
-------------------------------------------------------
61736520 6e616d65 2c00030e 474c4f42 l ase name,...GLOB
414c5f44 425f4e41 4d450552 4f474552 l AL_DB_NAME.ROGER
14476c6f 62616c20 64617461 62617365 l .Global database
206e616d 652c0003 0a444254 494d455a l  name,...DBTIMEZ
4f4e4505 30303a30 300c4442 2074696d l ONE.00:00.DB tim
65207a6f 6e652c00 02174e4f 5f555345 l e zone,...NO_USE
5249445f                            l RID_

<16 bytes per line>

BBED>
由于2c00030e 是行前面的信息,2c是行头,所以我们在进行修改的时候,offset要向前推进4个offset。
那么应该是offset应该是 6662-4=6658. 此时你观察下面信息你就发现和rowdata的offset是相差了92个offset的。

sb2 kdbr[25]                                @160      6526

BBED> p *kdbr[25]
rowdata[0]
----------
ub1 rowdata[0]                              @6618     0x2c

再观察另外一条数据看看,是否一样呢?
BBED> p kdbr[24]
sb2 kdbr[24]                                @158      6876

BBED> p *kdbr[24]
rowdata[350]
------------
ub1 rowdata[350]                            @6968     0x2c

显然是一致的,那么,也就是说在offset 6658的基础之上,我们还得向前推进92个offset,即:

SQL> select 6658-92 from dual;

   6658-92
----------
      6566
SQL> select to_char('6566','xxxx') from dual;

TO_CH
-----
19a6

注意:32位系统上,字节序是反的。
—- 下面开始进行修改

BBED> modify /x a619 offset 160
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722              Offsets:  160 to  259           Dba:0x004002d2
------------------------------------------------------------------------
a6197a1a 301af419 d3190000 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 = 0xf85a, required = 0xf85a

BBED> p kdbr[25]
sb2 kdbr[25]                                @160      6566

BBED> p *kdbr[25]
rowdata[40]
-----------
ub1 rowdata[40]                             @6658     0x2c

BBED> x /rcccc
rowdata[40]                                 @6658   
-----------
flag@6658: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6659: 0x00
cols@6660:    3

col   0[14] @6661: GLOBAL_DB_NAME
col    1[5] @6676: ROGER
col   2[20] @6682: Global database name


BBED>
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/system01.dbf
BLOCK = 722

Block Checking: DBA = 4195026, Block Type = KTB-managed data block
data header at 0xb7e4b25c
kdbchk: xaction header lock count mismatch
        trans=2 ilk=1 nlo=0
Block 722 failed with check code 6108

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED> modify /x 02 offset  6659
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722              Offsets: 6659 to 6758           Dba:0x004002d2
------------------------------------------------------------------------
02030e47 4c4f4241 4c5f4442 5f4e414d 4505524f 47455214 476c6f62 616c2064
61746162 61736520 6e616d65 2c00030a 44425449 4d455a4f 4e450530 303a3030
0c444220 74696d65 207a6f6e 652c0002 174e4f5f 55534552 49445f56 45524946
4945525f

<32 bytes per line>

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

BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/system01.dbf
BLOCK = 722

Block Checking: DBA = 4195026, Block Type = KTB-managed data block
data header at 0xb7e4b25c
kdbchk: the amount of space used is not equal to block size
        used=1558 fsc=5 avsp=6538 dtl=8096  ---很显然,这里used+fsc+avsp 已经大于8096了。
Block 722 failed with check code 6110

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED>   可以看到,used+fsc+avsp=8101,而8101-8096=5 也就是fsc的值,下面将fsc该为0.

由于lock的一行信息在第一个itl里面,所以我直接进入到第2个itl,如下:
BBED> p ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68     
   struct ktbitxid, 8 bytes                 @68     
      ub2 kxidusn                           @68       0x0003
      ub2 kxidslt                           @70       0x001e
      ub4 kxidsqn                           @72       0x00000384
   struct ktbituba, 8 bytes                 @76     
      ub4 kubadba                           @76       0x0080b676
      ub2 kubaseq                           @80       0x024c
      ub1 kubarec                           @82       0x06
   ub2 ktbitflg                             @84       0x2001 (KTBFUPB)
   union _ktbitun, 2 bytes                  @86     
      b2 _ktbitfsc                          @86       5  --修改这里即可
      ub2 _ktbitwrp                         @86       0x0005
   ub4 ktbitbas                             @88       0x004750b8

BBED> modify /x 00 offset 86
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722              Offsets:   86 to  185           Dba:0x004002d2
------------------------------------------------------------------------
0000b850 47000001 1e000300 4e007e19 8a198f19 00001e00 701f2b1f e01effff
a81e861e 631e421e 191ee91d be1d941d 6b1d3f1d 161de81c a51c621c 0b1cdf1b
bd1b8b1b 551b1d1b dc1aa619 7a1a301a f419d319 00000000 00000000 00000000
00000000

<32 bytes per line>

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

BBED>
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/system01.dbf
BLOCK = 722

Block Checking: DBA = 4195026, Block Type = KTB-managed data block
data header at 0xb7e4b25c
kdbchk: space available on commit is incorrect
        tosp=6543 fsc=0 stb=0 avsp=6538
Block 722 failed with check code 6111

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED>

再次修改tosp的值,使其等于avsp.
BBED> p kdbh
struct kdbh, 14 bytes                       @92     
   ub1 kdbhflag                             @92       0x00 (NONE)
   b1 kdbhntab                              @93       1
   b2 kdbhnrow                              @94       30
   sb2 kdbhfrre                             @96       3
   sb2 kdbhfsbo                             @98       78
   sb2 kdbhfseo                             @100      6526
   b2 kdbhavsp                              @102      6538
   b2 kdbhtosp                              @104      6543

BBED> modify /x 8a19 offset 104
File: /home/ora10g/oradata/roger/system01.dbf (1)
Block: 722              Offsets:  104 to  203           Dba:0x004002d2
------------------------------------------------------------------------
8a190000 1e00701f 2b1fe01e ffffa81e 861e631e 421e191e e91dbe1d 941d6b1d
3f1d161d e81ca51c 621c0b1c df1bbd1b 8b1b551b 1d1bdc1a a6197a1a 301af419
d3190000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000

<32 bytes per line>

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

BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/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

BBED>
至此,整个block 校验不再出现任何问题了,最后启动数据库。

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size             146801896 bytes
Database Buffers           16777216 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.
SQL>

alert log中也无任何报错信息,如下:
Tue Aug 14 03:35:18 PDT 2012
Completed redo application
Tue Aug 14 03:35:18 PDT 2012
Completed crash recovery at
Thread 1: logseq 49, block 80429, scn 4694027
45 data blocks read, 45 data blocks written, 101 redo blocks read
Tue Aug 14 03:35:18 PDT 2012
LGWR: STARTING ARCH PROCESSES
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=26323
ARC0 started with pid=16, OS id=26321
Tue Aug 14 03:35:18 PDT 2012
Thread 1 advanced to log sequence 50 (thread open)
Thread 1 opened at log sequence 50
  Current log# 2 seq# 50 mem# 0: /home/ora10g/oradata/roger/redo02.log
Successful open of redo thread 1
Tue Aug 14 03:35:18 PDT 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Aug 14 03:35:18 PDT 2012
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Tue Aug 14 03:35:18 PDT 2012
ARC0: Becoming the heartbeat ARCH
Tue Aug 14 03:35:18 PDT 2012
SMON: enabling cache recovery
Tue Aug 14 03:35:18 PDT 2012
Successfully onlined Undo Tablespace 1.
Tue Aug 14 03:35:18 PDT 2012
SMON: enabling tx recovery
Tue Aug 14 03:35:18 PDT 2012
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=18, OS id=26325
Tue Aug 14 03:35:20 PDT 2012
Completed: ALTER DATABASE OPEN
Tue Aug 14 03:35:46 PDT 2012
Thread 1 advanced to log sequence 51 (LGWR switch)
  Current log# 3 seq# 51 mem# 0: /home/ora10g/oradata/roger/redo03.log
再说声明,这样的恢复没有什么实际意义,纯粹是玩玩。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle global_name设置为空的研究笔记

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1394.html

Oracle研究中心

关键词:

global_name

Oracle bbed