分类目录归档：数据库

ORACLE用户重命名

发表于 2012 年 8 月 20 日由惜分飞

从oracle 11.2.0.2开始提供了用户重命名的新特性，在以前的版本中，如果想对用户重命名，一般来说是先创建一个新的用户并授权，然后将原用户下的所有对象导入，然后删除旧的用户！
数据库版本信息

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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

创建测试环境

SQL> create user xifenfei identified by xifenfei;

User created.

SQL> grant connect,resource to xifenfei;

Grant succeeded.

SQL> conn xifenfei/xifenfei
Connected.

SQL> create table t_xifenfei   as select * from user_users;

Table created.

SQL> create index ind_t_xifenfei on t_xifenfei(user_id);

Index created.

SQL> conn / as sysdba
Connected.
SQL> select object_type,object_name from dba_objects where owner='XIFENFEI';

OBJECT_TYPE         OBJECT_NAME
------------------- ---------------------------------------------------------
TABLE               T_XIFENFEI
INDEX               IND_T_XIFENFEI

尝试修改用户名

SQL> alter user xifenfei rename to xff identified by xifenfei;  
alter user xifenfei rename to xff identified by xifenfei
                    *
ERROR at line 1:
ORA-00922: missing or invalid option

--默认值是false
SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize 150
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
  4    5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _enable_rename_user
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_enable_rename_user%')

NAME                             VALUE                    DESCRIPTION
-------------------------------- ------------------------ ------------------------------------------------
_enable_rename_user              FALSE                    enable RENAME-clause using ALTER USER statement

SQL> startup force restrict
ORACLE instance started.

Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.

--_enable_rename_user=false,在restrict模式也不能修改用户名
SQL> ALTER user XFF RENAME TO xffei IDENTIFIED BY xifenfei;
ALTER user XFF RENAME TO xffei IDENTIFIED BY xifenfei
               *
ERROR at line 1:
ORA-00922: missing or invalid option

设置隐含参数

SQL> alter system set "_enable_rename_user"=true scope=spfile;

System altered.

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

SQL> startup restrict                                     
ORACLE instance started.

Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.
SQL> ALTER user xifenfei RENAME TO xff IDENTIFIED BY xifenfei;

User altered.

测试结果

SQL> startup force
ORACLE instance started.

Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.
SQL> select object_type,object_name from dba_objects where owner='XIFENFEI';

no rows selected

SQL> select object_type,object_name from dba_objects where owner='XFF';

OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------------------
TABLE               T_XIFENFEI
INDEX               IND_T_XIFENFEI

SQL> conn xff/xifenfei
Connected.
SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
         1

相关文档和上面的测试,得出结论:数据库版本在11.2.0.2及其以上版本,_enable_rename_user设置为true,数据库启动到restrict模式可以修改用户名

发表在 Oracle | 2 条评论

bbed 使用实现 drop index 操作

发表于 2012 年 8 月 14 日由惜分飞

这里个bbed的测试是为了实现通过bbed来实现删除index,该方法有两个用途:
1.数据库因为index出了问题不能启动,使用该方法可以屏蔽index,来实现数据库正常启动
2.bootstrap$中的某个index异常
准备环境

SQL> conn chf/xifenfei
Connected.
SQL>  create table t_xifenfei
  2  as
  3  select * from dba_objects;

Table created.

SQL> create index ind_t_xifenfei on t_xifenfei(object_id);

Index created.

SQL> SET LINES 150
SQL> col owner for a5 
SQL> select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='IND_T_XIFENFEI';

OWNER INDEX_NAME                     TABLE_NAME                     STATUS
----- ------------------------------ ------------------------------ --------
CHF   IND_T_XIFENFEI                 T_XIFENFEI                     VALID

SQL> select object_id from dba_objects where object_name='IND_T_XIFENFEI';

 OBJECT_ID
----------
     75558

SQL> select obj#,dataobj#,ts#,file#,block#,bo#,FLAGS from sys.ind$ where obj#=75558;

      OBJ#   DATAOBJ#        TS#      FILE#     BLOCK#        BO#      FLAGS
---------- ---------- ---------- ---------- ---------- ---------- ----------
     75558      75558          4          4        298      75557          2

SQL> select   rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from sys.ind$ where obj#=75558;

ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAAACAABAAAT50AAA          1      81524          0

SQL>  alter system checkpoint;

System altered.

SQL> select dump(75558,'16') from dual;

DUMP(75558,'16')
-----------------------
Typ=2 Len=4: c3,8,38,3b

SQL> select dump(4,'16') from dual;

DUMP(4,'16')
-----------------
Typ=2 Len=2: c1,5

SQL> select dump(298,'16') from dual;

DUMP(298,'16')
--------------------
Typ=2 Len=3: c2,3,63

SQL> select dump(75557,'16') from dual;

DUMP(75557,'16')
-----------------------
Typ=2 Len=4: c3,8,38,3a

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

删除ind$中记录

[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 17:09:55 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> info all
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oracle/oradata/ora11g/system01.dbf                              0
     2  /u01/oracle/oradata/ora11g/sysaux01.dbf                              0
     3  /u01/oracle/oradata/ora11g/undotbs01.dbf                             0
     4  /u01/oracle/oradata/ora11g/users01.dbf                               0

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

BBED> map
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 81524                                 Dba:0x00413e74
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @92      

 struct kdbt[6], 24 bytes                   @106     

 sb2 kdbr[33]                               @130     

 ub1 freespace[5420]                        @196     

 ub1 rowdata[2572]                          @5616    

 ub4 tailchk                                @8188    


BBED> p kdbr
sb2 kdbr[0]                                 @130      8074
sb2 kdbr[1]                                 @132      7987
sb2 kdbr[2]                                 @134      7896
sb2 kdbr[3]                                 @136      7618
sb2 kdbr[4]                                 @138      7523
sb2 kdbr[5]                                 @140      6700
sb2 kdbr[6]                                 @142      6573
sb2 kdbr[7]                                 @144      5524
sb2 kdbr[8]                                 @146      5633
sb2 kdbr[9]                                 @148     -1
sb2 kdbr[10]                                @150      7771
sb2 kdbr[11]                                @152      7703
sb2 kdbr[12]                                @154      7642
sb2 kdbr[13]                                @156      7546
sb2 kdbr[14]                                @158      7459
sb2 kdbr[15]                                @160      7397
sb2 kdbr[16]                                @162      7330
sb2 kdbr[17]                                @164      7267
sb2 kdbr[18]                                @166      6516
sb2 kdbr[19]                                @168      6450
sb2 kdbr[20]                                @170      6384
sb2 kdbr[21]                                @172      6327
sb2 kdbr[22]                                @174      6265
sb2 kdbr[23]                                @176      6202
sb2 kdbr[24]                                @178      6147
sb2 kdbr[25]                                @180      6086
sb2 kdbr[26]                                @182      6025
sb2 kdbr[27]                                @184      5967
sb2 kdbr[28]                                @186      5906
sb2 kdbr[29]                                @188      5845
sb2 kdbr[30]                                @190      5784
sb2 kdbr[31]                                @192      5727
sb2 kdbr[32]                                @194      5663

--这里使用直接查看的方法,来找出来ind$中相关记录,实际中方法很多find/第三方工具都可以
BBED> p *kdbr[0]
rowdata[2550]
-------------
ub1 rowdata[2550]                           @8166     0xac

BBED> x /rn
rowdata[2550]                               @8166    
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    1
mref@8171:    1
hrid@8173:0x00400095.1
nrid@8179:0x00400095.1

col    0[2] @8185: 80 


BBED> p *kdbr[1]
rowdata[2463]
-------------
ub1 rowdata[2463]                           @8079     0xac

BBED> x /rn
rowdata[2463]                               @8079    
-------------
flag@8079: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8080: 0x00
cols@8081:    1
kref@8082:    1
mref@8084:    1
hrid@8086:0x004000a1.1
nrid@8092:0x004000a1.1

col    0[3] @8098: 330 


BBED> p *kdbr[2]
rowdata[2372]
-------------
ub1 rowdata[2372]                           @7988     0xac


BBED> x /rn
rowdata[2372]                               @7988    
-------------
flag@7988: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7989: 0x00
cols@7990:    1
kref@7991:    1
mref@7993:    1
hrid@7995:0x004000a7.6
nrid@8001:0x004000a7.6

col    0[3] @8007: 471 


BBED> p *kdbr[3]
rowdata[2094]
-------------
ub1 rowdata[2094]                           @7710     0xac

BBED> x /rn
rowdata[2094]                               @7710    
-------------
flag@7710: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7711: 0x00
cols@7712:    1
kref@7713:    1
mref@7715:    1
hrid@7717:0x0040eb9a.6
nrid@7723:0x0040eb9a.6

col    0[4] @7729: 59484 


BBED>  p *kdbr[4]
rowdata[1999]
-------------
ub1 rowdata[1999]                           @7615     0xac

BBED> x /rn
rowdata[1999]                               @7615    
-------------
flag@7615: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7616: 0x00
cols@7617:    1
kref@7618:    4
mref@7620:    4
hrid@7622:0x00403371.6
nrid@7628:0x00403371.6

col    0[3] @7634: 8871 


BBED> p *kdbr[5]
rowdata[1176]
-------------
ub1 rowdata[1176]                           @6792     0xac

BBED> x /rn
rowdata[1176]                               @6792    
-------------
flag@6792: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@6793: 0x00
cols@6794:    1
kref@6795:   18
mref@6797:   18
hrid@6799:0x00413e74.5
nrid@6805:0x00413e74.5

col    0[4] @6811: 75557 


BBED>  p *kdbr[6]
rowdata[1049]
-------------
ub1 rowdata[1049]                           @6665     0x6c

BBED> x /rn
rowdata[1049]                               @6665    
-------------
flag@6665: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@6666: 0x00
cols@6667:   36
ckix@6668:    5

col    0[4] @6669: 75557 
col    1[2] @6674: 4 
col    2[2] @6677: 4 
col    3[3] @6680: 170 
col    4[0] @6684: *NULL*
col    5[0] @6685: *NULL*
col    6[2] @6686: 15 
col    7[0] @6689: *NULL*
col    8[2] @6690: 10 
col    9[2] @6693: 40 
col   10[2] @6696: 1 
col   11[3] @6699: 255 
col   12[6] @6703: 1073741825 
col  13[38] @6710:  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d 
 0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d 
 0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d  0x2d 
 0x2d  0x2d 
col   14[0] @6749: *NULL*
col   15[0] @6750: *NULL*
col   16[0] @6751: *NULL*
col   17[0] @6752: *NULL*
col   18[0] @6753: *NULL*
col   19[0] @6754: *NULL*
col   20[0] @6755: *NULL*
col   21[0] @6756: *NULL*
col   22[0] @6757: *NULL*
col   23[0] @6758: *NULL*
col   24[0] @6759: *NULL*
col   25[0] @6760: *NULL*
col   26[2] @6761: 15 
col   27[2] @6764: 15 
col   28[6] @6767: 536870912 
col   29[1] @6774: 0 
col   30[3] @6776: 736 
col   31[0] @6780: *NULL*
col   32[0] @6781: *NULL*
col   33[0] @6782: *NULL*
col   34[0] @6783: *NULL*
col   35[7] @6784: ######################################### 


BBED> p *kdbr[7]
rowdata[0]
----------
ub1 rowdata[0]                              @5616     0x6c

BBED> x /rn
rowdata[0]                                  @5616    
----------
flag@5616: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@5617: 0x02
cols@5618:   33
ckix@5619:    5

col    0[4] @5620: 75558 
col    1[4] @5625: 75558 
col    2[2] @5630: 4 
col    3[2] @5633: 4 
col    4[3] @5636: 298 
col    5[1] @5640: 0 
col    6[2] @5642: 1 
col    7[2] @5645: 10 
col    8[2] @5648: 2 
col    9[3] @5651: 255 
col   10[0] @5655: *NULL*
col   11[2] @5656: 1 
col   12[2] @5659: 2 
col   13[1] @5662: 0 
col   14[2] @5664: 1 
col   15[3] @5667: 165 
col   16[4] @5671: 74491 
col   17[2] @5676: 1 
col   18[2] @5679: 1 
col   19[3] @5682: 1720 
col   20[7] @5686: ######################################### 
col   21[4] @5694: 74491 
col   22[4] @5699: 74491 
col   23[2] @5704: 1 
col   24[0] @5707: *NULL*
col   25[0] @5708: *NULL*
col   26[0] @5709: *NULL*
col   27[2] @5710: 1 
col   28[0] @5713: *NULL*
col   29[0] @5714: *NULL*
col   30[0] @5715: *NULL*
col   31[0] @5716: *NULL*
col   32[7] @5717: ######################################### 


BBED> set count 64
        COUNT           64

BBED> d
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 81524            Offsets: 5616 to 5679           Dba:0x00413e74
------------------------------------------------------------------------
 6c022105 04c30838 3b04c308 383b02c1 0502c105 03c20363 018002c1 0202c10b 
 02c10303 c20338ff 02c10202 c1030180 02c10203 c2024204 c3082d5c 02c10202 

 <32 bytes per line>

BBED> m /x 7c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 81524            Offsets: 5616 to 5679           Dba:0x00413e74
------------------------------------------------------------------------
 7c022105 04c30838 3b04c308 383b02c1 0502c105 03c20363 018002c1 0202c10b 
 02c10303 c20338ff 02c10202 c1030180 02c10203 c2024204 c3082d5c 02c10202 

 <32 bytes per line>

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

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 81524

Block Checking: DBA = 4275828, Block Type = KTB-managed data block
data header at 0xb53c625c
kdbchk:  key comref count wrong
         keyslot=5
Block 81524 failed with check code 6121

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
Message 531 not found;  product=RDBMS; facility=BBED


BBED> p *kdbr[5]
rowdata[1176]
-------------
ub1 rowdata[1176]                           @6792     0xac

BBED> x /rn
rowdata[1176]                               @6792    
-------------
flag@6792: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@6793: 0x00
cols@6794:    1
kref@6795:   18
mref@6797:   18
hrid@6799:0x00413e74.5
nrid@6805:0x00413e74.5

col    0[4] @6811: 75557 


BBED> d offset 6797
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 81524            Offsets: 6797 to 6860           Dba:0x00413e74
------------------------------------------------------------------------
 12000041 3e740005 00413e74 000504c3 08383a7c 02140502 c10602c1 0602c102 
 01800d52 4f4c4c42 41434b5f 4f4e4c59 02c16102 c1020180 ffff0180 ffff02c1 

 <32 bytes per line>

BBED> m /x 11 
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 81524            Offsets: 6797 to 6860           Dba:0x00413e74
------------------------------------------------------------------------
 11000041 3e740005 00413e74 000504c3 08383a7c 02140502 c10602c1 0602c102 
 01800d52 4f4c4c42 41434b5f 4f4e4c59 02c16102 c1020180 ffff0180 ffff02c1 

 <32 bytes per line>

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

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 81524

Block Checking: DBA = 4275828, Block Type = KTB-managed data block
data header at 0xb53c625c
kdbchk: the amount of space used is not equal to block size
        used=1835 fsc=0 avsp=6156 dtl=8096
Block 81524 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
Message 531 not found;  product=RDBMS; facility=BBED


BBED> p kdbh
struct kdbh, 14 bytes                       @92      
   ub1 kdbhflag                             @92       0x00 (NONE)
   sb1 kdbhntab                             @93       6
   sb2 kdbhnrow                             @94       33
   sb2 kdbhfrre                             @96       9
   sb2 kdbhfsbo                             @98       104
   sb2 kdbhfseo                             @100      5524
   sb2 kdbhavsp                             @102      6156
   sb2 kdbhtosp                             @104      6156

BBED> d offset 102
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 81524            Offsets:  102 to  165           Dba:0x00413e74
------------------------------------------------------------------------
 0c180c18 00000600 06000100 07000000 07000100 08000200 0a001700 8a1f331f 
 d81ec21d 631d2c1a ad199415 0116ffff 5b1e171e da1d7a1d 231de51c a21c631c 

 <32 bytes per line>

BBED> m /x 7518
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 81524            Offsets:  102 to  165           Dba:0x00413e74
------------------------------------------------------------------------
 75180c18 00000600 06000100 07000000 07000100 08000200 0a001700 8a1f331f 
 d81ec21d 631d2c1a ad199415 0116ffff 5b1e171e da1d7a1d 231de51c a21c631c 

 <32 bytes per line>

BBED> m /x 7518 offset 104
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 81524            Offsets:  104 to  167           Dba:0x00413e74
------------------------------------------------------------------------
 75180000 06000600 01000700 00000700 01000800 02000a00 17008a1f 331fd81e 
 c21d631d 2c1aad19 94150116 ffff5b1e 171eda1d 7a1d231d e51ca21c 631c7419 

 <32 bytes per line>

BBED> p kdbh
struct kdbh, 14 bytes                       @92      
   ub1 kdbhflag                             @92       0x00 (NONE)
   sb1 kdbhntab                             @93       6
   sb2 kdbhnrow                             @94       33
   sb2 kdbhfrre                             @96       9
   sb2 kdbhfsbo                             @98       104
   sb2 kdbhfseo                             @100      5524
   sb2 kdbhavsp                             @102      6261
   sb2 kdbhtosp                             @104      6261

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

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 81524

Block Checking: DBA = 4275828, Block Type = KTB-managed data block
data header at 0xb53c625c
kdbchk: space available on commit is incorrect
        tosp=6261 fsc=0 stb=4 avsp=6261
Block 81524 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
Message 531 not found;  product=RDBMS; facility=BBED


BBED> m /x 7918 offset 104
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 81524            Offsets:  104 to  167           Dba:0x00413e74
------------------------------------------------------------------------
 79180000 06000600 01000700 00000700 01000800 02000a00 17008a1f 331fd81e 
 c21d631d 2c1aad19 94150116 ffff5b1e 171eda1d 7a1d231d e51ca21c 631c7419 

 <32 bytes per line>

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

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 81524


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
Message 531 not found;  product=RDBMS; facility=BBED

启动数据库测试ind$是否修改成功

SQL> startup
ORACLE instance started.

Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.
SQL> select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='IND_T_XIFENFEI';
select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='IND_T_XIFENFEI'
                                               *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[], [], [], [], []

SQL> set autot trace exp
SQL> set lines 150
SQL> select /*+ full(t) */obj#,dataobj#,ts#,file#,block#,bo#,FLAGS,rowid from sys.ind$ t where obj#=75558;

Execution Plan
----------------------------------------------------------
Plan hash value: 3378156415

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    41 |   206   (0)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| IND$ |     1 |    41 |   206   (0)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJ#"=75558)

SQL> select obj#,dataobj#,ts#,file#,block#,bo#,FLAGS,rowid from sys.ind$ t where obj#=75558;

Execution Plan
----------------------------------------------------------
Plan hash value: 3312860272

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    41 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| IND$   |     1 |    41 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | I_IND1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJ#"=75558)

SQL> select count(*) from ind$ where obj#=75558;

Execution Plan
----------------------------------------------------------
Plan hash value: 4150977594

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |
|*  2 |   INDEX UNIQUE SCAN| I_IND1 |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJ#"=75558)

SQL> select /*+ full(t) */ count(*)  from sys.ind$ t where obj#=75558;

Execution Plan
----------------------------------------------------------
Plan hash value: 809192456

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   206   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| IND$ |     1 |     5 |   206   (0)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJ#"=75558)


SQL> set autot off
SQL> select /*+ full(t) */obj#,dataobj#,ts#,file#,block#,bo#,FLAGS,rowid from sys.ind$ t where obj#=75558;

no rows selected

SQL>  select obj#,dataobj#,ts#,file#,block#,bo#,FLAGS,rowid from sys.ind$ t where obj#=75558;

      OBJ#   DATAOBJ#        TS#      FILE#     BLOCK#        BO#      FLAGS ROWID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------
     75558      75558          4          4        298      75557          2 AAAAACAABAAAT50AAA

SQL> select count(*) from ind$ where obj#=75558;

  COUNT(*)
----------
         1

SQL> select /*+ full(t) */ count(*)  from sys.ind$ t where obj#=75558;

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

SQL> COL COLUMN_NAME FOR A15
SQL> SELECT INDEX_OWNER,COLUMN_NAME,INDEX_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='IND$' AND TABLE_OWNER='SYS';

INDEX_OWNER                    COLUMN_NAME     INDEX_NAME
------------------------------ --------------- ------------------------------
SYS                            OBJ#            I_IND1

通过上面的查询我们可以知道ind$本身有一个关于obj#列的index,当我们查询使用该index的时候出现上面的ora-600[kdsgrp1]错误.而因为ind$相关index是bootstarp$中对象,不能直接或者upgrade,甚至event 38003都不能drop或者rebuid

分析I_IND1 索引信息

SQL>  select obj#,dataobj# from obj$ where name='I_IND1';

      OBJ#   DATAOBJ#
---------- ----------
        41         41

SQL> alter session set events 'immediate trace name treedump level 41';

Session altered.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
---------------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17321.trc

--通过这个dump出来的rdba信息,结合我们dump出来其他数据块信息可以找到叶子节点的值用来匹配我们需要delete值在I_IND1中位置
----- begin tree dump
branch: 0x400179 4194681 (0: nrow: 10, level: 1)
   leaf: 0x40017a 4194682 (-1: nrow: 575 rrow: 575)
   leaf: 0x40017b 4194683 (0: nrow: 569 rrow: 567)
   leaf: 0x40017c 4194684 (1: nrow: 540 rrow: 540)
   leaf: 0x40017d 4194685 (2: nrow: 533 rrow: 533)
   leaf: 0x40017e 4194686 (3: nrow: 362 rrow: 361)
   leaf: 0x40017f 4194687 (4: nrow: 533 rrow: 533)
   leaf: 0x411d98 4267416 (5: nrow: 533 rrow: 532)
   leaf: 0x411d99 4267417 (6: nrow: 533 rrow: 533)
   leaf: 0x411d9a 4267418 (7: nrow: 533 rrow: 533)
   leaf: 0x411d9b 4267419 (8: nrow: 386 rrow: 386)
----- end tree dump

SQL> set serveroutput on
SQL> declare
  2     p_dba   VARCHAR2 (255) :='0x00411d9b';
  3     l_str   VARCHAR2 (255) DEFAULT NULL;
  4  BEGIN
  5      l_str :=
  6           'datafile# is:'
  7        || DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'))
  8        || chr(10)||'datablock is:'
  9        || DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'));
 10     dbms_output.put_line(l_str);
 11  END;
 12  /
datafile# is:1
datablock is:73115

PL/SQL procedure successfully completed.


SQL> alter system dump datafile 1 block 73115;

System altered.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17583.trc

--找到对应块在叶子节点中的块的信息
Block header dump:  0x00411d9b
 Object id on Block? Y
 seg/obj: 0x29  csc: 0x00.c92c9  itc: 2  flg: O  typ: 2 - INDEX
     fsl: 0  fnx: 0x411d9c ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.002.000001c6  0x00c0483f.004a.01  CB--    0  scn 0x0000.000a66a1
0x02   0x0006.009.000002b3  0x00c02389.0075.2e  --U-    1  fsc 0x0000.000c92cb

row#385[2538] flag: ------, lock: 2, len=13, data:(6):  00 41 3e 74 00 00
col 0; len 4; (4):  c3 08 38 3b

--对于ASSM：76+（itc-1)*24
--对于MSSM：68+（itc-1)*24

SQL> select 2538+68+(2-1)*24 from dual;

2538+68+(2-1)*24
----------------
            2630

bbed修改I_IND1中记录

[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 17:36:59 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set block 73115
        BLOCK#          73115

BBED> set offset 2630
        OFFSET          2630

BBED> x /rn
rowdata[4]                                  @2630    
----------
flag@2630:     0x00 (NONE)
lock@2631:     0x02
keydata[6]:    0x00  0x41  0x3e  0x74  0x00  0x00 
data key:
col    0[4] @2639: 75558 


BBED> set count 64
        COUNT           64

BBED> d
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 73115            Offsets: 2630 to 2693           Dba:0x00411d9b
------------------------------------------------------------------------
 00020041 3e740000 04c30838 3b000000 40264a00 0104c308 38380100 0040264a 
 000004c3 08383701 00004026 4a000004 c3083832 01000040 264a0001 04c30838 

 <32 bytes per line>

BBED> m /x 01
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 73115            Offsets: 2630 to 2693           Dba:0x00411d9b
------------------------------------------------------------------------
 01020041 3e740000 04c30838 3b000000 40264a00 0104c308 38380100 0040264a 
 000004c3 08383701 00004026 4a000004 c3083832 01000040 264a0001 04c30838 

 <32 bytes per line>


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

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 73115

Block Checking: DBA = 4267419, Block Type = KTB-managed data block
**** actual free space credit for itl 2 = 15 != # in trans. hdr = 0  <----修改_ktbitfsc信息
**** actual rows marked deleted = 1 != kdxlende = 0 <----修改kdxlende信息
---- end index block validation
Block 73115 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> map
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 73115                                 Dba:0x00411d9b
------------------------------------------------------------
 KTB Data Block (Index Leaf)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdxle, 32 bytes                     @92      

 sb2 kd_off[386]                            @124     

 ub1 freespace[1730]                        @896     

 ub1 rowdata[5494]                          @2626    

 ub4 tailchk                                @8188    


BBED> p kdxle
struct kdxle, 32 bytes                      @92      
   struct kdxlexco, 16 bytes                @92      
      ub1 kdxcolev                          @92       0x00
      ub1 kdxcolok                          @93       0x00
      ub1 kdxcoopc                          @94       0x80
      ub1 kdxconco                          @95       0x01
      ub4 kdxcosdc                          @96       0x00000001
      sb2 kdxconro                          @100      386
      sb2 kdxcofbo                          @102      808
      sb2 kdxcofeo                          @104      2538
      sb2 kdxcoavs                          @106      2210
   sb2 kdxlespl                             @108      0
   sb2 kdxlende                             @110      0   <----需要修改
   ub4 kdxlenxt                             @112      0x00000000
   ub4 kdxleprv                             @116      0x00411d9a
   ub1 kdxledsz                             @120      0x06
   ub1 kdxleflg                             @121      0x00 (NONE)

BBED> d offset 110
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 73115            Offsets:  110 to  173           Dba:0x00411d9b
------------------------------------------------------------------------
 00000000 00009a1d 41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f 
 f81eeb1e de1ed11e c41eb71e aa1e9d1e 901e831e 761e691e 5c1e4f1e 421e351e 

 <32 bytes per line>

BBED> m /x 01
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 73115            Offsets:  110 to  173           Dba:0x00411d9b
------------------------------------------------------------------------
 01000000 00009a1d 41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f 
 f81eeb1e de1ed11e c41eb71e aa1e9d1e 901e831e 761e691e 5c1e4f1e 421e351e 

 <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       0x00000029
      ub4 ktbbhod1                          @24       0x00000029
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x000c92c9
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       2
   ub1 ktbbhflg                             @38       0x03 (KTBFONFL)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00411d9c
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0001
         ub2 kxidslt                        @46       0x0002
         ub4 kxidsqn                        @48       0x000001c6
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00c0483f
         ub2 kubaseq                        @56       0x004a
         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       0x000a66a1
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x0006
         ub2 kxidslt                        @70       0x0009
         ub4 kxidsqn                        @72       0x000002b3
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x00c02389
         ub2 kubaseq                        @80       0x0075
         ub1 kubarec                        @82       0x2e
      ub2 ktbitflg                          @84       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @86      
         sb2 _ktbitfsc                      @86       0    <----需要修改
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x000c92cb

BBED> d offset 86
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 73115            Offsets:   86 to  149           Dba:0x00411d9b
------------------------------------------------------------------------
 0000cb92 0c000000 80010100 00008201 2803ea09 a2080000 01000000 00009a1d 
 41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e 

 <32 bytes per line>

BBED> m /x 0f
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 73115            Offsets:   86 to  149           Dba:0x00411d9b
------------------------------------------------------------------------
 0f00cb92 0c000000 80010100 00008201 2803ea09 a2080000 01000000 00009a1d 
 41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e 

 <32 bytes per line>

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

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 73115


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

启动数据库测试

SQL> startup
ORACLE instance started.

Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.
SQL> select count(*) from ind$ where obj#=75558;

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

SQL> select /*+ full(t) */ count(*)  from sys.ind$ t where obj#=75558;

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

SQL> select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='IND_T_XIFENFEI';

no rows selected

扫尾和测试工作

SQL> delete from obj$ where obj# =75558;

1 row deleted.

SQL> delete from icol$ where obj#=75558;

1 row deleted.

SQL> delete from seg$ where ts#=4 and file#=4 and block#=298;

1 row deleted.

SQL> commit;

Commit complete.

--重新创建/删除一个同名的index成功
SQL> create index chf.ind_t_xifenfei on chf.t_xifenfei(object_id);

Index created.

SQL> drop index chf.ind_t_xifenfei;

Index dropped.

通过以上对于ind$和I_IND1操作大体上完成对于ind_t_xifenfei索引的手工删除,比较完美的实现了bbed drop index操作过程.

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

对应cluster table使用bbed删除其中的部分记录,一直没有被攻克的难关,今天突发灵感,解决了cluster table 通过bbed删除记录后验证不能通过的难题.主要修改操作:oracle在index cluster中删除一条记录实际上只是把这条记录的行头由0x6c修改为0x7c,并且把这条记录所对应的聚簇键所在行的行头中记录的comc减1;修改验证信息
模拟cluster table 环境

SQL> create cluster clu_xff(id number(4));

Cluster created.

SQL> create table t_xifenfei
  2  (id number(4) 
  3  ,name      varchar2(25)
  4  )CLUSTER clu_xff (id);

Table created.

SQL> create index ind_clu_xff ON CLUSTER clu_xff;

Index created.

SQL> insert into t_xifenfei values(1,'xifenfei');

1 row created.

SQL> insert into t_xifenfei values(2,'www.xifenfei.com');

1 row created.

SQL> insert into t_xifenfei values(3,'XIFENFEI');

1 row created.

SQL> insert into t_xifenfei values(4,'WWW.XIFENFEI.COM');

1 row created.

SQL> insert into t_xifenfei values(2,'WWW.xifenfei.COM');

1 row created.

SQL> insert into t_xifenfei values(3,'XFF_CHF');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select t.*,
  2  dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location 
  3  from t_xifenfei t;

        ID NAME                      LOCATION
---------- ------------------------- ----------
         2 www.xifenfei.com          4_171
         2 WWW.xifenfei.COM          4_171
         3 XIFENFEI                  4_172
         3 XFF_CHF                   4_172    <----需要删除记录
         4 WWW.XIFENFEI.COM          4_174
         1 xifenfei                  4_175

6 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

bbed删除记录操作

[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit 

BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 09:33:58 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 4 block 172
        FILE#           4
        BLOCK#          172

BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172                                   Dba:0x010000ac
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[2], 8 bytes                    @114     

 sb2 kdbr[3]                                @122     

 ub1 freespace[8013]                        @128     

 ub1 rowdata[47]                            @8141    

 ub4 tailchk                                @8188    

--查看该块的相关数据值
BBED> p kdbr
sb2 kdbr[0]                                 @122      8066
sb2 kdbr[1]                                 @124      8053
sb2 kdbr[2]                                 @126      8041

BBED> p *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25]                             @8166     0xac

BBED> x /rn
rowdata[25]                                 @8166    
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    2
mref@8171:    2
hrid@8173:0x010000ac.0
nrid@8179:0x010000ac.0

col    0[2] @8185: 3 


BBED> p *kdbr[1]
rowdata[12]
-----------
ub1 rowdata[12]                             @8153     0x6c

BBED> x /rc
rowdata[12]                                 @8153    
-----------
flag@8153: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8154: 0x00
cols@8155:    1

col    0[8] @8157: XIFENFEI


BBED> p *kdbr[2]
rowdata[0]
----------
ub1 rowdata[0]                              @8141     0x6c

BBED> x /rc
rowdata[0]                                  @8141    
----------
flag@8141: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8142: 0x02
cols@8143:    1

col    0[7] @8145: XFF_CHF   <----需要删除记录


BBED> m /x 7c offset 8141
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets: 8141 to 8191           Dba:0x010000ac
------------------------------------------------------------------------
 7c020100 07584646 5f434846 6c000100 08584946 454e4645 49ac0001 02000200 
 010000ac 00000100 00ac0000 02c10402 066c1d 

 <32 bytes per line>

BBED> m /x 01 offset 8171
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets: 8171 to 8191           Dba:0x010000ac
------------------------------------------------------------------------
 01000100 00ac0000 010000ac 000002c1 0402066c 1d 

 <32 bytes per line>

BBED> p *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25]                             @8166     0xac

BBED> x /rn
rowdata[25]                                 @8166    
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    2
mref@8171:    1
hrid@8173:0x010000ac.0
nrid@8179:0x010000ac.0

col    0[2] @8185: 3 

BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f87, required = 0x8f87

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172

Block Checking: DBA = 16777388, Block Type = KTB-managed data block
data header at 0xb53ed264
kdbchk: the amount of space used is not equal to block size   <----数据块使用空间错误
        used=67 fsc=0 avsp=8013 dtl=8088
Block 172 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
Message 531 not found;  product=RDBMS; facility=BBED


BBED> p kdbh
struct kdbh, 14 bytes                       @100     
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      2
   sb2 kdbhnrow                             @102      3
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      28
   sb2 kdbhfseo                             @108      8041
   sb2 kdbhavsp                             @110      8013
   sb2 kdbhtosp                             @112      8013

BBED> m /x 551f offset 110
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets:  110 to  126           Dba:0x010000ac
------------------------------------------------------------------------
 551f4d1f 00000100 01000200 821f751f  

 <32 bytes per line>

BBED>  p kdbh
struct kdbh, 14 bytes                       @100     
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      2
   sb2 kdbhnrow                             @102      3
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      28
   sb2 kdbhfseo                             @108      8041
   sb2 kdbhavsp                             @110      8021
   sb2 kdbhtosp                             @112      8013

BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f9f, required = 0x8f9f

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172

Block Checking: DBA = 16777388, Block Type = KTB-managed data block
data header at 0xb53ed264
kdbchk: avsp(8021) > tosp(8013)           <----avsp 不能大于tosp
Block 172 failed with check code 6128

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
Message 531 not found;  product=RDBMS; facility=BBED


BBED>  m /x 551f offset 112     
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets:  112 to  128           Dba:0x010000ac
------------------------------------------------------------------------
 551f0000 01000100 0200821f 751f691f 

 <32 bytes per line>

BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f87, required = 0x8f87

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172

Block Checking: DBA = 16777388, Block Type = KTB-managed data block
data header at 0xb53ed264
kdbchk: space available on commit is incorrect
        tosp=8021 fsc=0 stb=4 avsp=8021        <----tosp值不合适
Block 172 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
Message 531 not found;  product=RDBMS; facility=BBED


BBED> set count 64
        COUNT           64

BBED> m /x 591f offset 112
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets:  112 to  175           Dba:0x010000ac
------------------------------------------------------------------------
 591f0000 01000100 0200821f 751f691f 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f8b, required = 0x8f8b

--修改块工作完成
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172


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
Message 531 not found;  product=RDBMS; facility=BBED

启动库验证

SQL> startup
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             260049780 bytes
Database Buffers           46137344 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> select * from t_xifenfei;

        ID NAME
---------- -------------------------
         2 www.xifenfei.com
         2 WWW.xifenfei.COM
         3 XIFENFEI
         4 WWW.XIFENFEI.COM
         1 xifenfei
--XFF_CHF记录被删除

SQL> insert into t_xifenfei values(3,'惜分飞');

1 row created.

SQL> delete from t_xifenfei where name='XIFENFEI';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t_xifenfei;

        ID NAME
---------- -------------------------
         2 www.xifenfei.com
         2 WWW.xifenfei.COM
         3 惜分飞
         4 WWW.XIFENFEI.COM
         1 xifenfei
--证明XFF_CHF所在数据块其他dml操作正常,证明修改正确

发表在 Oracle | 标签为 bbed | 一条评论

17813235971

QQ 咨询

分类目录归档：数据库

ORACLE用户重命名

bbed 使用实现 drop index 操作

bbed 删除 cluster table 记录

文章分类

最近发表

分类目录归档：数据库

ORACLE用户重命名

bbed 使用实现 drop index 操作

bbed 删除 cluster table 记录

标签云

文章分类

最近发表