How to use embedded BBED in ODU to manually fix missing offset in row directory

Note: The latest ODU version (ODU 4.3.3) supports recover accidentally deleted data automatically even if all the deleted rows’ offsets in corresponding row directory are completely cleared by Oracle. Due to Jun’s amazing work, you don’t need to manually fix missing offset if you use ODU 4.3.3, ODU 4.3.3 can identify all the missing offsets in row directory automatically! Thank God!

 

In this article, I demonstrate how to use embedded BBED in ODU 4.3.2 to manually fix missing offset in row directory.

 

First of all, let’s construct an example of missing offset in row directory.

Create table T1 in user scott and insert more than 580,000 test rows:

SQL> conn scott/tiger@cuihua112;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

 

SQL> create table t1 as select * from dba_objects;

 

Table created

 

SQL> insert into t1 select * from t1;

 

72532 rows inserted

 

SQL> insert into t1 select * from t1;

 

145064 rows inserted

 

SQL> insert into t1 select * from t1;

 

290128 rows inserted

 

SQL> commit;

 

Commit complete

 

SQL> select count(*) from t1;

 

  COUNT(*)

——————-

  580256

 

SQL> create sequence seq_t1 minvalue 1 maxvalue 999999999999999999999 start with 1 increment by 1 cache 200;

 

Sequence created

 

SQL> alter table t1 add (id number);

 

Table altered

 

SQL> update t1 set id = seq_t1.nextval;

 

580256 rows updated

 

SQL> commit;

 

Commit complete

 

 

Execute the following SQL, you can see that the 580,256 records of table T1 are distributed among 8231 data blocks:

SQL> select min(id) min_id,max(id) max_id,dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) location from t1 group by dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) order by max_id;

 

    MIN_ID     MAX_ID LOCATION

———- ———- ——————————————————————————–

         1         88 4_619

        89        171 4_620

       172        251 4_621

    ……omit some display contents

      4845       4924 4_684

 

    MIN_ID     MAX_ID LOCATION

———- ———- ——————————————————————————–

      4925       5000 4_685

      5001       5080 4_686

    ……omit some display contents

    574626     574694 4_9483

    574695     574763 4_9484

    574764     574833 4_9485

    ……omit some display contents

    580111     580181 4_9599

    580182     580256 4_9626

 

8231 rows selected

 

 

Create a backup table T1_BACKUP to record the columns ID of table T1:

SQL> create table t1_backup as select id from t1;

 

Table created

 

SQL> select count(*) from t1_backup;

 

  COUNT(*)

———-

    580256

 

 

Perform a delete operation on table T1, but do not commit:

SQL> delete from t1;

 

580256 rows deleted

 

 

In another session execute flush buffer_cache in order to construct a delay block cleanout:

E:\Recovery_Test\ODU\data>sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 3 16:17:07 2014

 

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

 

SQL> conn / as sysdba;

Connected.

 

SQL> alter system flush buffer_cache;

 

System altered.

 

 

Go back to the session where execute delete opration towards table T1, execute commit, followed by a select and checkpoint operations:

SQL> commit;

 

Commit complete

 

SQL> select * from t1;

 

OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME                           ID

—————————— ——————————————————————————– —————————— ———- ————– ——————- ———– ————- ——————- ——- ——— ——— ——— ———- —————————— ———-

 

SQL> select count(*) from t1;

 

  COUNT(*)

——————

     0

 

SQL> alter system checkpoint;

 

System altered

 

 

Now we use ODU 4.3.2 to recover all the deleted rows of table T1:

E:\Recovery_Test\ODU>odu

 

Oracle Data Unloader:Release 4.3.2

 

Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.

 

Web: http://www.oracleodu.com

Email: magic007cn@gmail.com

 

loading default config…….

 

byte_order little

block_size  8192

data_path   data

lob_path    lob

db_timezone -7

Invalid db timezone:-7

client_timezone 8

Invalid client timezone:8

asmfile_extract_path   asmfile

charset_name ZHS16GBK

ncharset_name AL16UTF16

output_format dmp

lob_storage infile

clob_byte_order big

trace_level 2

delimiter |

unload_deleted yes

file_header_offset 0

is_tru64 no

record_row_addr no

convert_clob_charset yes

use_scanned_lob  yes

trim_scanned_blob yes

lob_switch_dir_rows 1000

db_block_checksum yes

db_block_checking yes

rdba_file_bits 10

compatible 10

load config file ‘config.txt’ successful

loading default asm disk file ……

 

can not open file ‘asmdisk.txt’, error message:No such file or directory.

loading default control file ……

 

 

 ts#   fn  rfn bsize   blocks bf offset filename

—- —- —- —– ——– — —— ——————————————–

   0    1    1  8192   101120 N       0 D:\app\cuihua\oradata\cuihua112\SYSTEM01.DBF

   1    2    2  8192    69120 N       0 D:\app\cuihua\oradata\cuihua112\SYSAUX01.DBF

   2    3    3  8192    72960 N       0 D:\app\cuihua\oradata\cuihua112\UNDOTBS01.DBF

   6    5    5  8192    12800 N       0 D:\app\cuihua\oradata\cuihua112\EXAMPLE01.DBF

   4    4    4  8192   157760 N       0 D:\app\cuihua\oradata\cuihua112\USERS01.DBF

load control file ‘oductl.dat’ successful

loading dictionary data……done

 

loading scanned data……done

 

ODU> unload dict

CLUSTER C_USER# file_no: 1 block_no: 208

TABLE OBJ$ obj_no: 18 file_no: 1 block_no: 240

CLUSTER C_OBJ# file_no: 1 block_no: 144

CLUSTER C_OBJ# file_no: 1 block_no: 144

found IND$’s obj# 19

found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3

found TABPART$’s obj# 576

found TABPART$’s dataobj#:576,ts#:0,file#:1,block#:3824,tab#:0

found INDPART$’s obj# 581

found INDPART$’s dataobj#:581,ts#:0,file#:1,block#:3872,tab#:0

found TABSUBPART$’s obj# 588

found TABSUBPART$’s dataobj#:588,ts#:0,file#:1,block#:3928,tab#:0

found INDSUBPART$’s obj# 593

found INDSUBPART$’s dataobj#:593,ts#:0,file#:1,block#:3968,tab#:0

found IND$’s obj# 19

found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3

found LOB$’s obj# 80

found LOB$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6

found LOBFRAG$’s obj# 609

found LOBFRAG$’s dataobj#:609,ts#:0,file#:1,block#:4096,tab#:0

 

ODU> unload table scott.t1

 

Unloading table: T1,object ID: 74955 at 2014-04-03 16:22:08

Unloading segment,storage(Obj#=74955 DataObj#=74955 TS#=4 File#=4 Block#=618 Cluster=0)

 

Table T1 575558 rows unloaded

At 2014-04-03 16:22:23

 

You can see that ODU only recovered 575,558 records (number of records that has been deleted in table T1 is 580,256).

 

Import the above 575,558 records into the user SYS:

E:\Recovery_Test\ODU\data>imp \”sys/oracle@cuihua112 as sysdba\” file=SCOTT_T1.dmp tables=t1 ignore=true

 

Import: Release 11.2.0.1.0 – Production on Thu Apr 3 16:26:42 2014

 

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

 

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Export file created by EXPORT:V08.01.07 via conventional path

 

Warning: the objects were exported by SCOTT, not by you

 

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTT’s objects into SYS

. importing SCOTT’s objects into SYS

. . importing table                           “T1″     575558 rows imported

Import terminated successfully without warnings.

 

 

From the following query results, we can see that there are 4698 deleted records of the table T1 that ODU did not successfully recover:

SQL> conn scott/tiger@cuihua112;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

 

SQL> select id from (select id from t1_backup minus select id from sys.t1) order by id;

 

        ID

———-

      4925

      4926

      4927

    ……omit some display contents

    574692

    574693

    574694

 

4698 rows selected

 

 

Now we choose two blocks (where id is 4925 and 574694) of the table T1 to dump:

SQL> select min(id) from (select id from t1_backup minus select id from sys.t1);

 

   MIN(ID)

——————

    4925

 

SQL> select max(id) from (select id from t1_backup minus select id from sys.t1);

 

   MAX(ID)

——————

   574694

 

 

The record where ID equals to 4925 stored in datafile 4, block 685:

    MIN_ID      MAX_ID      LOCATION

——————   —————- ——————————————————————————–

      4925       5000         4_685

     

 

The record where ID equals to 574694 stored in datafile 4, block 9483:

    MIN_ID      MAX_ID      LOCATION

——————   —————- ——————————————————————————–

    574626       574694      4_9483

   

   

Now we use ODU to dump the above two blocks:

ODU> dump datafile 4 block 685

Block Header:

block type=0×06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x010002ad (file#=4, block#=685)

scn=0×0000.001ae559, seq=1, tail=0xe5590601

block checksum value=0xa9a2=43426, flag=4

Data Block Header Dump:

 Object id on Block? Y

 seg/obj: 0x124cb=74955  csc: 0×00.1ae559  itc: 3  flg: E  typ: 1 (data)

     brn: 0  bdba: 0x10002a8 ver: 0×01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0×01   0xffff.000.00000000  0×00000000.0000.00  C—    0  scn 0×0000.001a30aa

0×02   0×0003.004.00000478  0x00c1168c.0180.51  C—    0  scn 0×0000.001a7899

0×03   0×0004.00f.00000398  0x00c11452.00bb.20  C—    0  scn 0×0000.001ae557

Data Block Dump:

================

flag=0×0 ——–

ntab=1

nrow=76

frre=0

fsbo=0xaa

ffeo=0×170

avsp=0x1ed6

tosp=0x1ed6

0xe:pti[0] nrow=76  offs=0

0×12:pri[0]  sfll=1

0×14:pri[1]  sfll=2

0×16:pri[2]  sfll=3

0×18:pri[3]  sfll=4

0x1a:pri[4]  sfll=5

0x1c:pri[5]  sfll=6

0x1e:pri[6]  sfll=7

0×20:pri[7]  sfll=8

0×22:pri[8]  sfll=9

0×24:pri[9]  sfll=10

0×26:pri[10]  sfll=11

0×28:pri[11]  sfll=12

0x2a:pri[12]  sfll=13

0x2c:pri[13]  sfll=14

0x2e:pri[14]  sfll=15

0×30:pri[15]  sfll=16

0×32:pri[16]  sfll=17

0×34:pri[17]  sfll=18

0×36:pri[18]  sfll=19

0×38:pri[19]  sfll=20

0x3a:pri[20]  sfll=21

0x3c:pri[21]  sfll=22

0x3e:pri[22]  sfll=23

0×40:pri[23]  sfll=24

0×42:pri[24]  sfll=25

0×44:pri[25]  sfll=26

0×46:pri[26]  sfll=27

0×48:pri[27]  sfll=28

0x4a:pri[28]  sfll=29

0x4c:pri[29]  sfll=30

0x4e:pri[30]  sfll=31

0×50:pri[31]  sfll=32

0×52:pri[32]  sfll=33

0×54:pri[33]  sfll=34

0×56:pri[34]  sfll=35

0×58:pri[35]  sfll=36

0x5a:pri[36]  sfll=37

0x5c:pri[37]  sfll=38

0x5e:pri[38]  sfll=39

0×60:pri[39]  sfll=40

0×62:pri[40]  sfll=41

0×64:pri[41]  sfll=42

0×66:pri[42]  sfll=43

0×68:pri[43]  sfll=44

0x6a:pri[44]  sfll=45

0x6c:pri[45]  sfll=46

0x6e:pri[46]  sfll=47

0×70:pri[47]  sfll=48

0×72:pri[48]  sfll=49

0×74:pri[49]  sfll=50

0×76:pri[50]  sfll=51

0×78:pri[51]  sfll=52

0x7a:pri[52]  sfll=53

0x7c:pri[53]  sfll=54

0x7e:pri[54]  sfll=55

0×80:pri[55]  sfll=56

0×82:pri[56]  sfll=57

0×84:pri[57]  sfll=58

0×86:pri[58]  sfll=59

0×88:pri[59]  sfll=60

0x8a:pri[60]  sfll=61

0x8c:pri[61]  sfll=62

0x8e:pri[62]  sfll=63

0×90:pri[63]  sfll=64

0×92:pri[64]  sfll=65

0×94:pri[65]  sfll=66

0×96:pri[66]  sfll=67

0×98:pri[67]  sfll=68

0x9a:pri[68]  sfll=69

0x9c:pri[69]  sfll=70

0x9e:pri[70]  sfll=71

0xa0:pri[71]  sfll=72

0xa2:pri[72]  sfll=73

0xa4:pri[73]  sfll=74

0xa6:pri[74]  sfll=75

0xa8:pri[75]  sfll=-1

Block Rows Dump:

 

ODU> dump datafile 4 block 9483

Block Header:

block type=0×06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x0100250b (file#=4, block#=9483)

scn=0×0000.001ae559, seq=1, tail=0xe5590601

block checksum value=0xa625=42533, flag=4

Data Block Header Dump:

 Object id on Block? Y

 seg/obj: 0x124cb=74955  csc: 0×00.1ae559  itc: 2  flg: E  typ: 1 (data)

     brn: 0  bdba: 0×1002282 ver: 0×01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0×01   0×0004.00f.00000398  0x00c0cf15.0104.16  C—    0  scn 0×0000.001ae557

0×02   0×0003.004.00000478  0x00c08e1b.01b5.3f  C-U-    0  scn 0×0000.001a7af8

Data Block Dump:

================

flag=0×0 ——–

ntab=1

nrow=69

frre=0

fsbo=0x9c

ffeo=0×204

avsp=0x1efc

tosp=0x1efc

0xe:pti[0] nrow=69  offs=0

0×12:pri[0]  sfll=1

0×14:pri[1]  sfll=2

0×16:pri[2]  sfll=3

0×18:pri[3]  sfll=4

0x1a:pri[4]  sfll=5

0x1c:pri[5]  sfll=6

0x1e:pri[6]  sfll=7

0×20:pri[7]  sfll=8

0×22:pri[8]  sfll=9

0×24:pri[9]  sfll=10

0×26:pri[10]  sfll=11

0×28:pri[11]  sfll=12

0x2a:pri[12]  sfll=13

0x2c:pri[13]  sfll=14

0x2e:pri[14]  sfll=15

0×30:pri[15]  sfll=16

0×32:pri[16]  sfll=17

0×34:pri[17]  sfll=18

0×36:pri[18]  sfll=19

0×38:pri[19]  sfll=20

0x3a:pri[20]  sfll=21

0x3c:pri[21]  sfll=22

0x3e:pri[22]  sfll=23

0×40:pri[23]  sfll=24

0×42:pri[24]  sfll=25

0×44:pri[25]  sfll=26

0×46:pri[26]  sfll=27

0×48:pri[27]  sfll=28

0x4a:pri[28]  sfll=29

0x4c:pri[29]  sfll=30

0x4e:pri[30]  sfll=31

0×50:pri[31]  sfll=32

0×52:pri[32]  sfll=33

0×54:pri[33]  sfll=34

0×56:pri[34]  sfll=35

0×58:pri[35]  sfll=36

0x5a:pri[36]  sfll=37

0x5c:pri[37]  sfll=38

0x5e:pri[38]  sfll=39

0×60:pri[39]  sfll=40

0×62:pri[40]  sfll=41

0×64:pri[41]  sfll=42

0×66:pri[42]  sfll=43

0×68:pri[43]  sfll=44

0x6a:pri[44]  sfll=45

0x6c:pri[45]  sfll=46

0x6e:pri[46]  sfll=47

0×70:pri[47]  sfll=48

0×72:pri[48]  sfll=49

0×74:pri[49]  sfll=50

0×76:pri[50]  sfll=51

0×78:pri[51]  sfll=52

0x7a:pri[52]  sfll=53

0x7c:pri[53]  sfll=54

0x7e:pri[54]  sfll=55

0×80:pri[55]  sfll=56

0×82:pri[56]  sfll=57

0×84:pri[57]  sfll=58

0×86:pri[58]  sfll=59

0×88:pri[59]  sfll=60

0x8a:pri[60]  sfll=61

0x8c:pri[61]  sfll=62

0x8e:pri[62]  sfll=63

0×90:pri[63]  sfll=64

0×92:pri[64]  sfll=65

0×94:pri[65]  sfll=66

0×96:pri[66]  sfll=67

0×98:pri[67]  sfll=68

0x9a:pri[68]  sfll=-1

Block Rows Dump:

 

From the above dump results, we can see that all the offsets in above two blocks’ row directory are totally cleared by Oracle.

That means we successfully construct an example of missing offset in row directory.

 

ODU integrates BBED, you can directly modify Oracle data block in ODU.

Input ‘blockedit’ or ‘exit’ to enter into or quit from the embedded BBED in ODU.

ODU> blockedit

 

Entering block edit module.

 

BBED> help

 

help      —-   get command list

spool     —-   spool information to file

host      —-   enter os terminal

rowid     —-   decode rowid components

rdba      —-   decode RDBA to rfile# and block#

time      —-   convert number to timestamp

exit      —-   exit from blockedit module

set       —-   set file,block,offset for edit

show      —-   show enviroment and variables

dump      —-   dump block

modify    —-   modify block data

sum       —-   calculate checksum and write block to file

rollback  —-   rollback the change

find      —-   find hex string

verify    —-   verify the block

mark      —-   mark undo segments corruption

clone     —-   copy block from another block

clean     —-   clean free block pool or transaction

 

BBED> exit

 

Exiting block edit module.

 

ODU>

 

From the following results, we can see that the deleted rows in the above two blocks are still in fact, Oracle just cleared out all the offset in these two blocks’ row directory:

ODU> blockedit

 

Entering block edit module.

 

BBED> set datafile 4

BBED> set block 685

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0000   06 a2 00 00 ad 02 00 01 59 e5 1a 00 00 00 01 04  ……..Y…….

0010   a2 a9 00 00 01 00 00 00 cb 24 01 00 59 e5 1a 00  ⅸ…….$..Y…

0020   00 00 00 00 03 00 32 00 a8 02 00 01 ff ff 00 00  ……2………

0030   00 00 00 00 00 00 00 00 00 00 00 00 00 80 00 00  …………….

0040   aa 30 1a 00 03 00 04 00 78 04 00 00 8c 16 c1 00  .0……x…….

0050   80 01 51 00 00 80 00 00 99 78 1a 00 04 00 0f 00  ..Q……x……

0060   98 03 00 00 52 14 c1 00 bb 00 20 00 00 80 00 00  ….R….. …..

0070   57 e5 1a 00 00 00 00 00 00 00 00 00 00 01 4c 00  W………….L.

0080   00 00 aa 00 70 01 d6 1e d6 1e 00 00 4c 00 01 00  ….p…….L…

0090   02 00 03 00 04 00 05 00 06 00 07 00 08 00 09 00  …………….

 

BBED> set offset 512

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0200   4e 54 45 58 54 ff 03 c2 33 48 ff 07 53 59 4e 4f  NTEXT…3H..SYNO

0210   4e 59 4d 07 78 6e 03 1e 0b 0a 2f 07 78 6e 03 1e  NYM.xn…./.xn..

0220   0b 0a 2f 13 32 30 31 30 2d 30 33 2d 33 30 3a 31  ../.2010-03-30:1

0230   30 3a 30 39 3a 34 36 05 56 41 4c 49 44 01 4e 01  0:09:46.VALID.N.

0240   4e 01 4e 02 c1 02 ff 02 c2 33 3c 03 10 03 53 59  N.N……3<…SY

0250   53 0e 47 4c 4f 42 41 4c 5f 43 4f 4e 54 45 58 54  S.GLOBAL_CONTEXT

0260   ff 03 c2 33 47 ff 04 56 49 45 57 07 78 6e 03 1e  …3G..VIEW.xn..

0270   0b 0a 2f 07 78 6e 03 1e 0b 0a 2f 13 32 30 31 30  ../.xn…./.2010

0280   2d 30 33 2d 33 30 3a 31 30 3a 30 39 3a 34 36 05  -03-30:10:09:46.

0290   56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 03  VALID.N.N.N…..

 

BBED> set offset 1024

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0400   56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 2c 00  VALID.N.N.N…,.

0410   0e 06 50 55 42 4c 49 43 10 47 56 24 47 4c 4f 42  ..PUBLIC.GV$GLOB

0420   41 4c 43 4f 4e 54 45 58 54 ff 03 c2 33 46 ff 07  ALCONTEXT…3F..

0430   53 59 4e 4f 4e 59 4d 07 78 6e 03 1e 0b 0a 2f 07  SYNONYM.xn…./.

0440   78 6e 03 1e 0b 0a 2f 13 32 30 31 30 2d 30 33 2d  xn…./.2010-03-

0450   33 30 3a 31 30 3a 30 39 3a 34 36 05 56 41 4c 49  30:10:09:46.VALI

0460   44 01 4e 01 4e 01 4e 02 c1 02 3c 03 10 06 50 55  D.N.N.N…<…PU

0470   42 4c 49 43 0f 56 24 47 4c 4f 42 41 4c 43 4f 4e  BLIC.V$GLOBALCON

0480   54 45 58 54 ff 03 c2 33 44 ff 07 53 59 4e 4f 4e  TEXT…3D..SYNON

0490   59 4d 07 78 6e 03 1e 0b 0a 2f 07 78 6e 03 1e 0b  YM.xn…./.xn…

 

BBED> set offset 2048

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0800   24 43 4f 4e 54 45 58 54 ff 03 c2 33 3a ff 07 53  $CONTEXT…3:..S

0810   59 4e 4f 4e 59 4d 07 78 6e 03 1e 0b 0a 2f 07 78  YNONYM.xn…./.x

0820   6e 03 1e 0b 0a 2f 13 32 30 31 30 2d 30 33 2d 33  n…./.2010-03-3

0830   30 3a 31 30 3a 30 39 3a 34 36 05 56 41 4c 49 44  0:10:09:46.VALID

0840   01 4e 01 4e 01 4e 02 c1 02 ff 03 c2 32 57 3c 03  .N.N.N……2W<.

0850   10 03 53 59 53 0a 56 5f 24 43 4f 4e 54 45 58 54  ..SYS.V_$CONTEXT

0860   ff 03 c2 33 39 ff 04 56 49 45 57 07 78 6e 03 1e  …39..VIEW.xn..

0870   0b 0a 2f 07 78 6e 03 1e 0b 0a 2f 13 32 30 31 30  ../.xn…./.2010

0880   2d 30 33 2d 33 30 3a 31 30 3a 30 39 3a 34 36 05  -03-30:10:09:46.

0890   56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 03  VALID.N.N.N…..

 

BBED> set offset 4096

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

1000   49 44 01 4e 01 4e 01 4e 02 c1 02 ff 03 c2 32 43  ID.N.N.N……2C

1010   3c 03 10 03 53 59 53 17 54 58 4e 5f 42 41 43 4b  <…SYS.TXN_BACK

1020   4f 55 54 5f 53 54 41 54 45 5f 49 44 58 33 24 ff  OUT_STATE_IDX3$.

1030   03 c2 33 24 03 c2 33 24 05 49 4e 44 45 58 07 78  ..3$..3$.INDEX.x

1040   6e 03 1e 0b 0a 2e 07 78 6e 03 1e 0b 0a 2e 13 32  n……xn……2

1050   30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 39 3a  010-03-30:10:09:

1060   34 35 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1  45.VALID.N.N.N..

1070   05 ff 03 c2 32 42 3c 03 10 03 53 59 53 17 54 58  ….2B<…SYS.TX

1080   4e 5f 42 41 43 4b 4f 55 54 5f 53 54 41 54 45 5f  N_BACKOUT_STATE_

1090   49 44 58 32 24 ff 03 c2 33 23 03 c2 33 23 05 49  IDX2$…3#..3#.I

 

BBED> set datafile 4

BBED> set block 9483

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0000   06 a2 00 00 0b 25 00 01 59 e5 1a 00 00 00 01 04  …..%..Y…….

0010   25 a6 00 00 01 00 00 00 cb 24 01 00 59 e5 1a 00  %……..$..Y…

0020   00 00 00 00 02 00 32 00 82 22 00 01 04 00 0f 00  ……2..”……

0030   98 03 00 00 15 cf c0 00 04 01 16 00 00 80 00 00  …..………

0040   57 e5 1a 00 03 00 04 00 78 04 00 00 1b 8e c0 00  W…….x…….

0050   b5 01 3f 00 00 a0 00 00 f8 7a 1a 00 00 00 00 00  ..?……z……

0060   00 00 00 00 00 01 45 00 00 00 9c 00 04 02 fc 1e  ……E………

0070   fc 1e 00 00 45 00 01 00 02 00 03 00 04 00 05 00  ….E………..

0080   06 00 07 00 08 00 09 00 0a 00 0b 00 0c 00 0d 00  …………….

0090   0e 00 0f 00 10 00 11 00 12 00 13 00 14 00 15 00  …………….

 

BBED> set offset 1024

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0400   01 4e 01 59 01 4e 02 c1 02 ff 04 c3 3a 2f 5d 3c  .N.Y.N……:/]<

0410   01 10 06 53 59 53 4d 41 4e 12 50 4b 5f 4d 47 4d  …SYSMAN.PK_MGM

0420   54 5f 48 4f 53 54 5f 43 52 45 44 53 ff 04 c3 07  T_HOST_CREDS….

0430   4a 02 04 c3 07 4a 02 05 49 4e 44 45 58 07 78 6e  J….J..INDEX.xn

0440   03 1e 0b 2b 19 07 78 6e 03 1e 0b 2b 19 13 32 30  …+..xn…+..20

0450   31 30 2d 30 33 2d 33 30 3a 31 30 3a 34 32 3a 32  10-03-30:10:42:2

0460   34 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 05  4.VALID.N.N.N…

0470   ff 04 c3 3a 2f 5c 3c 01 10 06 53 59 53 4d 41 4e  …:/\<…SYSMAN

0480   15 4d 47 4d 54 5f 48 4f 53 54 5f 43 52 45 44 45  .MGMT_HOST_CREDE

0490   4e 54 49 41 4c 53 ff 04 c3 07 49 64 ff 05 54 41  NTIALS….Id..TA

 

BBED> set offset 2048

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0800   33 30 3a 31 30 3a 34 31 3a 34 32 05 56 41 4c 49  30:10:41:42.VALI

0810   44 01 4e 01 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 53  D.N.N.N……:/S

0820   3c 01 10 06 50 55 42 4c 49 43 1a 55 53 45 52 5f  <…PUBLIC.USER_

0830   53 44 4f 5f 4e 45 54 57 4f 52 4b 5f 55 53 45 52  SDO_NETWORK_USER

0840   5f 44 41 54 41 ff 04 c3 07 3d 28 ff 07 53 59 4e  _DATA….=(..SYN

0850   4f 4e 59 4d 07 78 6e 03 1e 0b 28 14 07 78 6e 03  ONYM.xn…(..xn.

0860   1e 0b 2a 2e 13 32 30 31 30 2d 30 33 2d 33 30 3a  ..*..2010-03-30:

0870   31 30 3a 34 31 3a 34 35 05 56 41 4c 49 44 01 4e  10:41:45.VALID.N

0880   01 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 52 3c 01 10  .N.N……:/R<..

0890   05 4d 44 53 59 53 19 41 4c 4c 5f 53 44 4f 5f 4e  .MDSYS.ALL_SDO_N

 

BBED> set offset 4096

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

1000   2f 38 38 33 35 61 33 63 61 5f 44 69 63 6f 6d 4f  /8835a3ca_DicomO

1010   62 6a 32 ff 04 c3 07 0d 3b ff 0a 4a 41 56 41 20  bj2…..;..JAVA

1020   43 4c 41 53 53 07 78 6e 03 1e 0b 20 2c 07 78 6e  CLASS.xn… ,.xn

1030   03 1e 0b 20 36 13 32 30 31 30 2d 30 33 2d 33 30  … 6.2010-03-30

1040   3a 31 30 3a 33 31 3a 34 33 05 56 41 4c 49 44 01  :10:31:43.VALID.

1050   4e 01 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 3f 3c 01  N.N.N……:/?<.

1060   10 06 4f 52 44 53 59 53 13 2f 37 39 62 35 34 66  ..ORDSYS./79b54f

1070   65 34 5f 44 69 63 6f 6d 4f 62 6a 31 ff 04 c3 07  e4_DicomObj1….

1080   0d 3a ff 0a 4a 41 56 41 20 43 4c 41 53 53 07 78  .:..JAVA CLASS.x

1090   6e 03 1e 0b 20 2c 07 78 6e 03 1e 0b 20 36 13 32  n… ,.xn… 6.2

 

 

Now we choose a normal (the associated row directory offset is not cleared by Oracle) data block (datafile 4, block 619) from table T1 to dump:

    MIN_ID     MAX_ID    LOCATION

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

       1          88        4_619

 

ODU> dump datafile 4 block 619

Block Header:

block type=0×06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x0100026b (file#=4, block#=619)

scn=0×0000.001a8061, seq=30, tail=0x8061061e

block checksum value=0xef07=61191, flag=4

Data Block Header Dump:

 Object id on Block? Y

 seg/obj: 0x124cb=74955  csc: 0×00.1a805f  itc: 3  flg: E  typ: 1 (data)

     brn: 0  bdba: 0×1000268 ver: 0×01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0×01   0xffff.000.00000000  0×00000000.0000.00  C—    0  scn 0×0000.001a30aa

0×02   0×0003.004.00000478  0x00c003dd.017f.39  C—    0  scn 0×0000.001a7899

0×03   0×0004.00f.00000398  0x00c016c7.00ba.1e  —-   88  fsc 0x1bfe.00000000

Data Block Dump:

================

flag=0×0 ——–

ntab=1

nrow=88

frre=-1

fsbo=0xc2

ffeo=0x13c

avsp=0×210

tosp=0x1ebe

0xe:pti[0] nrow=88  offs=0

0×12:pri[0]  offs=0x1f2f

0×14:pri[1]  offs=0x1edc

0×16:pri[2]  offs=0x1e8c

0×18:pri[3]  offs=0x1e3b

0x1a:pri[4]  offs=0x1de6

0x1c:pri[5]  offs=0x1d94

0x1e:pri[6]  offs=0x1d38

0×20:pri[7]  offs=0x1ce6

0×22:pri[8]  offs=0x1c93

0×24:pri[9]  offs=0x1c41

0×26:pri[10]  offs=0x1be1

0×28:pri[11]  offs=0x1b90

0x2a:pri[12]  offs=0x1b40

0x2c:pri[13]  offs=0x1ae6

0x2e:pri[14]  offs=0x1a93

0×30:pri[15]  offs=0x1a41

0×32:pri[16]  offs=0x19ef

0×34:pri[17]  offs=0x199e

0×36:pri[18]  offs=0x194b

0×38:pri[19]  offs=0x18fb

0x3a:pri[20]  offs=0x18ab

0x3c:pri[21]  offs=0×1858

0x3e:pri[22]  offs=0×1805

0×40:pri[23]  offs=0x17b5

0×42:pri[24]  offs=0×1764

0×44:pri[25]  offs=0×1711

0×46:pri[26]  offs=0x16c1

0×48:pri[27]  offs=0x166a

0x4a:pri[28]  offs=0×1617

0x4c:pri[29]  offs=0x15be

0x4e:pri[30]  offs=0x156c

0×50:pri[31]  offs=0x151a

0×52:pri[32]  offs=0x14c8

0×54:pri[33]  offs=0×1476

0×56:pri[34]  offs=0×1423

0×58:pri[35]  offs=0x13d4

0x5a:pri[36]  offs=0×1378

0x5c:pri[37]  offs=0×1323

0x5e:pri[38]  offs=0x12d0

0×60:pri[39]  offs=0x127a

0×62:pri[40]  offs=0x122a

0×64:pri[41]  offs=0x11d8

0×66:pri[42]  offs=0×1187

0×68:pri[43]  offs=0×1136

0x6a:pri[44]  offs=0x10e4

0x6c:pri[45]  offs=0×1091

0x6e:pri[46]  offs=0×1041

0×70:pri[47]  offs=0xfef

0×72:pri[48]  offs=0xf9b

0×74:pri[49]  offs=0xf4b

0×76:pri[50]  offs=0xefa

0×78:pri[51]  offs=0xea7

0x7a:pri[52]  offs=0xe54

0x7c:pri[53]  offs=0xe01

0x7e:pri[54]  offs=0xdae

0×80:pri[55]  offs=0xd5c

0×82:pri[56]  offs=0xd09

0×84:pri[57]  offs=0xca9

0×86:pri[58]  offs=0xc54

0×88:pri[59]  offs=0xc00

0x8a:pri[60]  offs=0xbaa

0x8c:pri[61]  offs=0xb54

0x8e:pri[62]  offs=0xb01

0×90:pri[63]  offs=0xaac

0×92:pri[64]  offs=0xa57

0×94:pri[65]  offs=0xa07

0×96:pri[66]  offs=0x9b7

0×98:pri[67]  offs=0×966

0x9a:pri[68]  offs=0x90f

0x9c:pri[69]  offs=0x8ba

0x9e:pri[70]  offs=0×863

0xa0:pri[71]  offs=0x80c

0xa2:pri[72]  offs=0x7bc

0xa4:pri[73]  offs=0×769

0xa6:pri[74]  offs=0×710

0xa8:pri[75]  offs=0x6be

0xaa:pri[76]  offs=0x66c

0xac:pri[77]  offs=0x61a

0xae:pri[78]  offs=0x5ca

0xb0:pri[79]  offs=0×578

0xb2:pri[80]  offs=0×526

0xb4:pri[81]  offs=0x4d2

0xb6:pri[82]  offs=0x2e6

0xb8:pri[83]  offs=0×290

0xba:pri[84]  offs=0×239

0xbc:pri[85]  offs=0x1e0

0xbe:pri[86]  offs=0x18f

0xc0:pri[87]  offs=0x13c

Block Rows Dump:

tab 0, row 0, @0x1f2f

fb: –HDFL– lb: 0×3  cc: 16

col   0: [  3]  53 59 53

col   1: [  5]  49 43 4f 4c 24

col   2: *NULL*

col   3: [  2]  c1 15

col   4: [  2]  c1 03

col   5: [  5]  54 41 42 4c 45

col   6: [  7]  78 6e 03 1e 0b 08 31

col   7: [  7]  78 6e 03 1e 0b 1c 16

col   8: [ 19]  32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 34 38

col   9: [  5]  56 41 4c 49 44

col  10: [  1]  4e

col  11: [  1]  4e

col  12: [  1]  4e

col  13: [  2]  c1 02

col  14: *NULL*

col  15: [  2]  c1 02

tab 0, row 1, @0x1edc

fb: –HDFL– lb: 0×3  cc: 16

col   0: [  3]  53 59 53

col   1: [  7]  49 5f 55 53 45 52 31

col   2: *NULL*

col   3: [  2]  c1 2f

col   4: [  2]  c1 2f

col   5: [  5]  49 4e 44 45 58

col   6: [  7]  78 6e 03 1e 0b 08 31

col   7: [  7]  78 6e 03 1e 0b 08 31

col   8: [ 19]  32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 34 38

col   9: [  5]  56 41 4c 49 44

col  10: [  1]  4e

col  11: [  1]  4e

col  12: [  1]  4e

col  13: [  2]  c1 05

col  14: *NULL*

col  15: [  2]  c1 03

 

……omit some display contents

 

tab 0, row 86, @0x18f

fb: –HDFL– lb: 0×3  cc: 16

col   0: [  3]  53 59 53

col   1: [  5]  4e 54 41 42 24

col   2: *NULL*

col   3: [  2]  c1 59

col   4: [  2]  c1 03

col   5: [  5]  54 41 42 4c 45

col   6: [  7]  78 6e 03 1e 0b 08 33

col   7: [  7]  78 6e 03 1e 0b 08 33

col   8: [ 19]  32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30

col   9: [  5]  56 41 4c 49 44

col  10: [  1]  4e

col  11: [  1]  4e

col  12: [  1]  4e

col  13: [  2]  c1 02

col  14: *NULL*

col  15: [  2]  c1 58

tab 0, row 87, @0x13c

fb: –HDFL– lb: 0×3  cc: 16

col   0: [  3]  53 59 53

col   1: [  7]  49 5f 4e 54 41 42 31

col   2: *NULL*

col   3: [  2]  c1 5a

col   4: [  2]  c1 5a

col   5: [  5]  49 4e 44 45 58

col   6: [  7]  78 6e 03 1e 0b 08 33

col   7: [  7]  78 6e 03 1e 0b 08 33

col   8: [ 19]  32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30

col   9: [  5]  56 41 4c 49 44

col  10: [  1]  4e

col  11: [  1]  4e

col  12: [  1]  4e

col  13: [  2]  c1 05

col  14: *NULL*

col  15: [  2]  c1 59

 

 

Let’s see the last two rows’ relative offset in the above block (datafile 4 block 619)’s row directory.

tab 0, row 86, @0x18f

tab 0, row 87, @0x13c

 

Now you can see that row 86′s relative offset is 0x18f, row 87′s relative offset is 0x13c.

We must add some base to relative offset in order to get the above two rows’ actual offset.

The algorithm of base is as follows:

For ASSMbase = 76+itc-1)*24

For MSSMbase = 68+itc-1)*24

 

From the following contents, we can see that the value of the above block’s itc is 3:

seg/obj: 0x124cb=74955  csc: 0×00.1a805f  itc: 3  flg: E  typ: 1 (data)

 

The tablespace where the table T1 resides use ASSM, so the actual offset of row 86 is 523:

SQL> select to_number(’18f’,’XXXX’) + 76 + (3-1)*24 from dual;

 

TO_NUMBER(’18F’,’XXXX’)+76+(3-

——————————

           523

 

The actual offset of row 87 is 440:

SQL> select to_number(’13c’,’XXXX’) + 76 + (3-1)*24 from dual;

 

TO_NUMBER(’13C’,’XXXX’)+76+(3-

——————————

           440

 

Let’s locate the row 86:                          

BBED> set datafile 4

BBED> set block 619

BBED> set offset 523

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

020b   3c 03 10 03 53 59 53 05 4e 54 41 42 24 ff 02 c1  <…SYS.NTAB$…

021b   59 02 c1 03 05 54 41 42 4c 45 07 78 6e 03 1e 0b  Y….TABLE.xn…

022b   08 33 07 78 6e 03 1e 0b 08 33 13 32 30 31 30 2d  .3.xn….3.2010-

023b   30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30 05 56  03-30:10:07:50.V

024b   41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 02 c1  ALID.N.N.N……

025b   58 3c 03 10 03 53 59 53 0d 49 5f 53 55 42 43 4f  X<…SYS.I_SUBCO

026b   4c 54 59 50 45 31 ff 02 c1 58 02 c1 58 05 49 4e  LTYPE1…X..X.IN

027b   44 45 58 07 78 6e 03 1e 0b 08 33 07 78 6e 03 1e  DEX.xn….3.xn..

028b   0b 08 33 13 32 30 31 30 2d 30 33 2d 33 30 3a 31  ..3.2010-03-30:1

029b   30 3a 30 37 3a 35 30 05 56 41 4c 49 44 01 4e 01  0:07:50.VALID.N.

 

Let’s locate the row 87: 

BBED> set offset 440

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

01b8   3c 03 10 03 53 59 53 07 49 5f 4e 54 41 42 31 ff  <…SYS.I_NTAB1.

01c8   02 c1 5a 02 c1 5a 05 49 4e 44 45 58 07 78 6e 03  ..Z..Z.INDEX.xn.

01d8   1e 0b 08 33 07 78 6e 03 1e 0b 08 33 13 32 30 31  …3.xn….3.201

01e8   30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30  0-03-30:10:07:50

01f8   05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 05 ff  .VALID.N.N.N….

0208   02 c1 59 3c 03 10 03 53 59 53 05 4e 54 41 42 24  ..Y<…SYS.NTAB$

0218   ff 02 c1 59 02 c1 03 05 54 41 42 4c 45 07 78 6e  …Y….TABLE.xn

0228   03 1e 0b 08 33 07 78 6e 03 1e 0b 08 33 13 32 30  ….3.xn….3.20

0238   31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35  10-03-30:10:07:5

0248   30 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02  0.VALID.N.N.N…

 

We can see that the value of these two rows’ row header are both 0x3c, that means Oracle actually label a delete flag on these two rows’ row header.

 

 

Before we manually fix missing offset in datafile 4 block 9483′s row directory, even we change the value of ODU config parameter unload_deleted from ‘no’ to ‘yes’, we still can’t unload all the deleted rows from that block:

ODU> unload table scott.t1 datafile 4 block 9483 blocks 1

unload specific block mode.

 

Unloading table: T1,object ID: 74955 at 2014-04-05 11:33:51

Unloading segment,storage(Obj#=74955 DataObj#=74955 TS#=4 File#=4 Block#=618 Cluster=0)

 

Table T1 0 rows unloaded

At 2014-04-05 11:33:51

 

 

Now we start to use embedded BBED in ODU to manually fix missing offset in datafile 4 block 9483′s row directory.

For a normal table (not cluster), its’ data block must have these five structures before row directory structure kdbr:

SQL> select * from v$type_size where type in (‘KCBH’,’KTBBH’,’KTBIT’,’KDBH’,’KDBT’);

 

COMPONENT TYPE     DESCRIPTION                       TYPE_SIZE

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

KCB       KCBH     BLOCK COMMON HEADER                     20

KTB       KTBIT    TRANSACTION VARIABLE HEADER              24

KTB       KTBBH    TRANSACTION FIXED HEADER                 48

KDB       KDBH     DATA HEADER                                14

KDB       KDBT     TABLE DIRECTORY ENTRY                     4

 

For a normal table (not cluster) in ASSM, when you compute the actual offset start from KDBH of a data block, you must add extra 8 bytes.

So for a normal table (not cluster) in ASSM, the algorithm of row directory structure kdbr’s actual offset is as follows:

actual offset of kdbr = 20 + 48 + 24*(itc-1) + 14 + 4 + 8 = 94 + 24*(itc-1)

 

From the following query result, we know that datafile 4 block 9483 has 69 rows:

    MIN_ID      MAX_ID      LOCATION

——————-   ————– ——————————————————————————–

    574626       574694       4_9483       /* 574694 – 574626 + 1 = 69 */

 

From the following dump result, we see that the value of datafile 4 block 9483′s itc is 2 and it has 69 rows in its’ row directory:   

ODU> dump datafile 4 block 9483

Block Header:

block type=0×06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x0100250b (file#=4, block#=9483)

scn=0×0000.001ae559, seq=1, tail=0xe5590601

block checksum value=0xa625=42533, flag=4

Data Block Header Dump:

 Object id on Block? Y

 seg/obj: 0x124cb=74955  csc: 0×00.1ae559  itc: 2  flg: E  typ: 1 (data)

     brn: 0  bdba: 0×1002282 ver: 0×01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0×01   0×0004.00f.00000398  0x00c0cf15.0104.16  C—    0  scn 0×0000.001ae557

0×02   0×0003.004.00000478  0x00c08e1b.01b5.3f  C-U-    0  scn 0×0000.001a7af8

Data Block Dump:

================

flag=0×0 ——–

ntab=1

nrow=69

frre=0

fsbo=0x9c

ffeo=0×204

avsp=0x1efc

tosp=0x1efc

0xe:pti[0] nrow=69  offs=0

0×12:pri[0]  sfll=1

0×14:pri[1]  sfll=2

0×16:pri[2]  sfll=3

0×18:pri[3]  sfll=4

0x1a:pri[4]  sfll=5

0x1c:pri[5]  sfll=6

0x1e:pri[6]  sfll=7

0×20:pri[7]  sfll=8

0×22:pri[8]  sfll=9

0×24:pri[9]  sfll=10

0×26:pri[10]  sfll=11

0×28:pri[11]  sfll=12

0x2a:pri[12]  sfll=13

0x2c:pri[13]  sfll=14

0x2e:pri[14]  sfll=15

0×30:pri[15]  sfll=16

0×32:pri[16]  sfll=17

0×34:pri[17]  sfll=18

0×36:pri[18]  sfll=19

0×38:pri[19]  sfll=20

0x3a:pri[20]  sfll=21

0x3c:pri[21]  sfll=22

0x3e:pri[22]  sfll=23

0×40:pri[23]  sfll=24

0×42:pri[24]  sfll=25

0×44:pri[25]  sfll=26

0×46:pri[26]  sfll=27

0×48:pri[27]  sfll=28

0x4a:pri[28]  sfll=29

0x4c:pri[29]  sfll=30

0x4e:pri[30]  sfll=31

0×50:pri[31]  sfll=32

0×52:pri[32]  sfll=33

0×54:pri[33]  sfll=34

0×56:pri[34]  sfll=35

0×58:pri[35]  sfll=36

0x5a:pri[36]  sfll=37

0x5c:pri[37]  sfll=38

0x5e:pri[38]  sfll=39

0×60:pri[39]  sfll=40

0×62:pri[40]  sfll=41

0×64:pri[41]  sfll=42

0×66:pri[42]  sfll=43

0×68:pri[43]  sfll=44

0x6a:pri[44]  sfll=45

0x6c:pri[45]  sfll=46

0x6e:pri[46]  sfll=47

0×70:pri[47]  sfll=48

0×72:pri[48]  sfll=49

0×74:pri[49]  sfll=50

0×76:pri[50]  sfll=51

0×78:pri[51]  sfll=52

0x7a:pri[52]  sfll=53

0x7c:pri[53]  sfll=54

0x7e:pri[54]  sfll=55

0×80:pri[55]  sfll=56

0×82:pri[56]  sfll=57

0×84:pri[57]  sfll=58

0×86:pri[58]  sfll=59

0×88:pri[59]  sfll=60

0x8a:pri[60]  sfll=61

0x8c:pri[61]  sfll=62

0x8e:pri[62]  sfll=63

0×90:pri[63]  sfll=64

0×92:pri[64]  sfll=65

0×94:pri[65]  sfll=66

0×96:pri[66]  sfll=67

0×98:pri[67]  sfll=68

0x9a:pri[68]  sfll=-1

Block Rows Dump:

 

So the actual kdbr offset of datafile 4 block 9483 is 94 + 24*(2-1) = 118

 

From the following desc result, we know the number of columns of table T1 is 16, which equals 0×10:

SQL> desc scott.t1

Name           Type          Nullable Default Comments

————– ————- ——– ——- ——–

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                        

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER        Y                        

DATA_OBJECT_ID NUMBER        Y                        

OBJECT_TYPE    VARCHAR2(19)  Y                        

CREATED        DATE          Y                        

LAST_DDL_TIME  DATE          Y                        

TIMESTAMP      VARCHAR2(19)  Y                        

STATUS         VARCHAR2(7)   Y                        

TEMPORARY      VARCHAR2(1)   Y                        

GENERATED      VARCHAR2(1)   Y                        

SECONDARY      VARCHAR2(1)   Y                        

NAMESPACE      NUMBER        Y                        

EDITION_NAME   VARCHAR2(30)  Y                        

ID             NUMBER        Y

 

 

The row header structure of Oracle’s row in a table (not cluster) has three parts.

The first part is row flag, normally (not consider row chain and row migration), the deleted row’s row flag is 0x3c.

The second part is lock byte, the value of a row’s lock byte is between 0×00 to itc of the block where this row belongs to.

The third part is the number of columns, consider NULL value storage optimization of the last column in Oracle, the value of a row’s number of columns is between 0×00 to the number of columns of the table where this row belongs to.

 

According to the above background knowledge, let’s use ODU’s find command to find the actual offset of every deleted rows in datafile 4 block 9483.

The find rules are as follows:

You just need to define a search string, the length of the search string is 3 bytes.

The first byte is 0x3c

The second byte is between 0×00 to 0×02 (the itc of datafile 4 block 9483 is 2)

The third part is between 0×00 to 0×10 (because the number of columns of table T1 is 16, which equals 0×10)

 

Note: the value of third part in that situation is usually 0×10 if the row in datafile 4 block 9483 do not contain null value in last column.

 

So let’s do it:

ODU> blockedit

 

Entering block edit module.

 

BBED> set datafile 4

BBED> set block 9483

BBED> set offset 118

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0076   01 00 02 00 03 00 04 00 05 00 06 00 07 00 08 00  …………….

0086   09 00 0a 00 0b 00 0c 00 0d 00 0e 00 0f 00 10 00  …………….

0096   11 00 12 00 13 00 14 00 15 00 16 00 17 00 18 00  …………….

00a6   19 00 1a 00 1b 00 1c 00 1d 00 1e 00 1f 00 20 00  ………….. .

00b6   21 00 22 00 23 00 24 00 25 00 26 00 27 00 28 00  !.”.#.$.%.&.’.(.

00c6   29 00 2a 00 2b 00 2c 00 2d 00 2e 00 2f 00 30 00  ).*.+.,.-…/.0.

00d6   31 00 32 00 33 00 34 00 35 00 36 00 37 00 38 00  1.2.3.4.5.6.7.8.

00e6   39 00 3a 00 3b 00 3c 00 3d 00 3e 00 3f 00 40 00  9.:.;.<.=.>.?.@.

00f6   41 00 42 00 43 00 44 00 ff ff 00 00 00 00 00 00  A.B.C.D………

0106   00 00 00 00 00 00 00 2c 02 10 06 53 59 53 4d 41  …….,…SYSMA

 

Start search:

BBED> find 3c

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

00ec   3c 00 3d 00 3e 00 3f 00 40 00 41 00 42 00 43 00  <.=.>.?.@.A.B.C.

00fc   44 00 ff ff 00 00 00 00 00 00 00 00 00 00 00 00  D……………

010c   00 2c 02 10 06 53 59 53 4d 41 4e 17 4d 47 4d 54  .,…SYSMAN.MGMT

011c   5f 54 41 52 47 45 54 5f 43 52 45 44 45 4e 54 49  _TARGET_CREDENTI

012c   41 4c 53 ff 04 c3 07 49 61 ff 05 54 41 42 4c 45  ALS….Ia..TABLE

013c   07 78 6e 03 1e 0b 2b 18 07 78 6e 03 1e 0b 2d 21  .xn…+..xn…-!

014c   13 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 2c 02  .2010-03-30:10,.

015c   10 06 53 59 53 4d 41 4e 12 53 59 53 5f 49 4f 54  ..SYSMAN.SYS_IOT

016c   5f 4f 56 45 52 5f 36 37 33 30 32 ff 04 c3 07 4a  _OVER_67302….J

017c   04 04 c3 07 4a 04 05 54 41 42 4c 45 07 78 6e 03  ….J..TABLE.xn.

 

From the above search result, 0x3c003d don’t match our search string, so ignore it.

 

Continue search:

BBED> f

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0268   3c 01 10 06 53 59 53 4d 41 4e 18 50 4b 5f 4d 47  <…SYSMAN.PK_MG

0278   4d 54 5f 45 4e 54 45 52 50 52 49 53 45 5f 43 52  MT_ENTERPRISE_CR

0288   45 44 53 ff 04 c3 07 4a 05 04 c3 07 4a 05 05 49  EDS….J….J..I

0298   4e 44 45 58 07 78 6e 03 1e 0b 2b 19 07 78 6e 03  NDEX.xn…+..xn.

02a8   1e 0b 2b 19 13 32 30 31 30 2d 30 33 2d 33 30 3a  ..+..2010-03-30:

02b8   31 30 3a 34 32 3a 32 34 05 56 41 4c 49 44 01 4e  10:42:24.VALID.N

02c8   01 4e 01 4e 02 c1 05 ff 04 c3 3a 2f 5f 3c 01 10  .N.N……:/_<..

02d8   06 53 59 53 4d 41 4e 1b 4d 47 4d 54 5f 45 4e 54  .SYSMAN.MGMT_ENT

02e8   45 52 50 52 49 53 45 5f 43 52 45 44 45 4e 54 49  ERPRISE_CREDENTI

02f8   41 4c 53 ff 04 c3 07 4a 03 ff 05 54 41 42 4c 45  ALS….J…TABLE

 

‘f’ stands for go on search from the current position, from top to down.

From the above search result, 0x3c0110 match our search string, so the sixty-ninth record’s actual offset is 0×0268.

 

Continue search:

BBED> f

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

02d5   3c 01 10 06 53 59 53 4d 41 4e 1b 4d 47 4d 54 5f  <…SYSMAN.MGMT_

02e5   45 4e 54 45 52 50 52 49 53 45 5f 43 52 45 44 45  ENTERPRISE_CREDE

02f5   4e 54 49 41 4c 53 ff 04 c3 07 4a 03 ff 05 54 41  NTIALS….J…TA

0305   42 4c 45 07 78 6e 03 1e 0b 2b 19 07 78 6e 03 1e  BLE.xn…+..xn..

0315   0b 2d 21 13 32 30 31 30 2d 30 33 2d 33 30 3a 31  .-!.2010-03-30:1

0325   30 3a 34 32 3a 32 34 05 56 41 4c 49 44 01 4e 01  0:42:24.VALID.N.

0335   4e 01 4e 02 c1 02 ff 04 c3 3a 2f 5e 2c 00 0e 06  N.N……:/^,…

0345   53 59 53 4d 41 4e 18 50 4b 5f 4d 47 4d 54 5f 45  SYSMAN.PK_MGMT_E

0355   4e 54 45 52 50 52 49 53 45 5f 43 52 45 44 53 ff  NTERPRISE_CREDS.

0365   04 c3 07 4a 05 04 c3 07 4a 05 05 49 4e 44 45 58  …J….J..INDEX

 

From the above search result, 0x3c0110 match our search string, so the sixty-eighth record’s actual offset is 0x02d5.

 

Continue the above search process, we can get all the 69 records’ actual offset.

Here I only search the last ten records’ actual offset:

69: 0×0268

68: 0x02d5

67: 0x03a8

66: 0x040f

65: 0×0476

64: 0x04dc

63: 0×0541

62: 0x05aa

61: 0×0612

60: 0×0679

 

I just mentioned, Oracle stores the relative offset in row directory.

We must add some base to relative offset to get the actual offset.

The algorithm of base is as follows:

For ASSMbase = 76+itc-1)*24

For MSSMbase = 68+itc-1)*24

 

Now, here we get last ten records’ actual offset, so we have to substract base to get these ten records’ relative offset.

The calculation results of these ten records’ relative offset are as follows:

69 (0×45) : 0×0268 – (76+2-1)*24) = 0×0204

68 (0×44) : 0x02d5 – (76+2-1)*24) = 0×0271

67 (0×43) : 0x03a8 – (76+2-1)*24) = 0×0344

66 (0×42) : 0x040f – (76+2-1)*24) = 0x03ab

65 (0×41) : 0×0476 – (76+2-1)*24) = 0×0412

64 (0×40) : 0x04dc – (76+2-1)*24) = 0×0478

63 (0x3f): 0×0541 – (76+2-1)*24) = 0x04dd

62 (0x3e): 0x05aa – (76+2-1)*24) = 0×0546

61 (0x3d): 0×0612 – (76+2-1)*24) = 0x05ae

60 (0x3c): 0×0679 – (76+2-1)*24) = 0×0615

 

 

Base on the above calculation results, plus we already know the actual kdbr offset of datafile 4 block 9483 is 94 + 24*(2-1) = 118,

so we can modify the datafile 4 block 9483′s last ten records’ relative offset in row directory:

BBED> set offset 118

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0076   01 00 02 00 03 00 04 00 05 00 06 00 07 00 08 00  …………….

0086   09 00 0a 00 0b 00 0c 00 0d 00 0e 00 0f 00 10 00  …………….

0096   11 00 12 00 13 00 14 00 15 00 16 00 17 00 18 00  …………….

00a6   19 00 1a 00 1b 00 1c 00 1d 00 1e 00 1f 00 20 00  ………….. .

00b6   21 00 22 00 23 00 24 00 25 00 26 00 27 00 28 00  !.”.#.$.%.&.’.(.

00c6   29 00 2a 00 2b 00 2c 00 2d 00 2e 00 2f 00 30 00  ).*.+.,.-…/.0.

00d6   31 00 32 00 33 00 34 00 35 00 36 00 37 00 38 00  1.2.3.4.5.6.7.8.

00e6   39 00 3a 00 3b 00 3c 00 3d 00 3e 00 3f 00 40 00  9.:.;.<.=.>.?.@.

00f6   41 00 42 00 43 00 44 00 ff ff 00 00 00 00 00 00  A.B.C.D………

0106   00 00 00 00 00 00 00 2c 02 10 06 53 59 53 4d 41  …….,…SYSMA

 

Let’s locate to the row 60 (0x3c):

BBED> set offset 0xec

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

00ec   3c 00 3d 00 3e 00 3f 00 40 00 41 00 42 00 43 00  <.=.>.?.@.A.B.C.

00fc   44 00 ff ff 00 00 00 00 00 00 00 00 00 00 00 00  D……………

010c   00 2c 02 10 06 53 59 53 4d 41 4e 17 4d 47 4d 54  .,…SYSMAN.MGMT

011c   5f 54 41 52 47 45 54 5f 43 52 45 44 45 4e 54 49  _TARGET_CREDENTI

012c   41 4c 53 ff 04 c3 07 49 61 ff 05 54 41 42 4c 45  ALS….Ia..TABLE

013c   07 78 6e 03 1e 0b 2b 18 07 78 6e 03 1e 0b 2d 21  .xn…+..xn…-!

014c   13 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 2c 02  .2010-03-30:10,.

015c   10 06 53 59 53 4d 41 4e 12 53 59 53 5f 49 4f 54  ..SYSMAN.SYS_IOT

016c   5f 4f 56 45 52 5f 36 37 33 30 32 ff 04 c3 07 4a  _OVER_67302….J

017c   04 04 c3 07 4a 04 05 54 41 42 4c 45 07 78 6e 03  ….J..TABLE.xn.

 

Execute the following modify command:

BBED> modify 1506ae054605dd0478041204ab03440371020402

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

00ec   15 06 ae 05 46 05 dd 04 78 04 12 04 ab 03 44 03  ….F…x…..D.

00fc   71 02 04 02 00 00 00 00 00 00 00 00 00 00 00 00  q……………

010c   00 2c 02 10 06 53 59 53 4d 41 4e 17 4d 47 4d 54  .,…SYSMAN.MGMT

011c   5f 54 41 52 47 45 54 5f 43 52 45 44 45 4e 54 49  _TARGET_CREDENTI

012c   41 4c 53 ff 04 c3 07 49 61 ff 05 54 41 42 4c 45  ALS….Ia..TABLE

013c   07 78 6e 03 1e 0b 2b 18 07 78 6e 03 1e 0b 2d 21  .xn…+..xn…-!

014c   13 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 2c 02  .2010-03-30:10,.

015c   10 06 53 59 53 4d 41 4e 12 53 59 53 5f 49 4f 54  ..SYSMAN.SYS_IOT

016c   5f 4f 56 45 52 5f 36 37 33 30 32 ff 04 c3 07 4a  _OVER_67302….J

017c   04 04 c3 07 4a 04 05 54 41 42 4c 45 07 78 6e 03  ….J..TABLE.xn.

 

Execute the following command to update checksum value:

BBED> sum apply

 

Warning: apply the modified data will overwrite original data.

Would you like to continue? (y/n)

y

 

Old checksum value: 0xa625

New checksum value: 0x5b4e

Writing block has completed

 

We finished all the modification towards these ten records’ relative offset.

Now we use ODU to try to unload these ten records:

ODU> unload table scott.t1 datafile 4 block 9483 blocks 1

unload specific block mode.

 

Unloading table: T1,object ID: 74955 at 2014-04-05 17:22:53

Unloading segment,storage(Obj#=74955 DataObj#=74955 TS#=4 File#=4 Block#=618 Cluster=0)

 

Table T1 10 rows unloaded

At 2014-04-05 17:22:53

 

From the above unload result, you can see that ODU has successfully unloaded these ten records.

 

The following are the unloaded result of these ten records:

MDSYS|SDO_NETWORK_UD_UPD_TRIG||66043||TRIGGER|2010-03-30 10:39:19|2010-03-30 10:39:19|2010-03-30:10:39:19|VALID|N|N|N|3||574685

SYSMAN|SYS_IOT_OVER_67296||67297|67297|TABLE|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|Y|N|1||574686

SYSMAN|MGMT_TARGET_CREDENTIALS||67296||TABLE|2010-03-30 10:42:23|2010-03-30 10:44:32|2010-03-30:10:42:23|VALID|N|N|N|1||574687

SYSMAN|PK_MGMT_TARGET_CREDS||67298|67298|INDEX|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|N|N|4||574688

SYSMAN|SYS_IOT_OVER_67299||67300|67300|TABLE|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|Y|N|1||574689

SYSMAN|MGMT_HOST_CREDENTIALS||67299||TABLE|2010-03-30 10:42:24|2010-03-30 10:44:32|2010-03-30:10:42:24|VALID|N|N|N|1||574690

SYSMAN|PK_MGMT_HOST_CREDS||67301|67301|INDEX|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|N|N|4||574691

SYSMAN|SYS_IOT_OVER_67302||67303|67303|TABLE|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|Y|N|1||574692

SYSMAN|MGMT_ENTERPRISE_CREDENTIALS||67302||TABLE|2010-03-30 10:42:24|2010-03-30 10:44:32|2010-03-30:10:42:24|VALID|N|N|N|1||574693

SYSMAN|PK_MGMT_ENTERPRISE_CREDS||67304|67304|INDEX|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|N|N|4||574694

 

Note the last column of the above unloaded records, the last column is the column ID of table T1, you can see that the value of these ten records’s column ID are continuous and its’ range between 574685 to 574694.

That exactly match the query result we select before:

    MIN_ID      MAX_ID      LOCATION

——————-   ————–  ——————————————————————————–

    574626      574694       4_9483       /* 574694 – 574626 + 1 = 69 */

 

That means ODU 4.3.2 has recovered the above 10 deleted rows successfully and correctly!


如何禁掉ASM实例的AMM

先交代一下背景:RedHat 6.3 x86_64上的11.2.0.3的两节点RAC,已经打了GI PSU 11.2.0.3.9Patch:17063116 for 11.2.0.3.7以及Patch:11072246 for 11.2.0.3.9

这里我想配一下Hugepage,但因为HugepageAMM不兼容以及11.2.0.3 RACASM实例默认会启用AMM,所以我得把ASM实例的AMM给禁掉。

 

本来以为是很简单的操作,但出乎我意料的是,一度出现了我怎么都禁不掉ASM实例的AMM的情形。

 

我们来看一下整个操作过程:

在任意一个RAC节点执行如下操作(注意——如下做法是错误的做法,我后面会跟正确的做法):

[oracle@rddb2 ~]$ su – grid

Password:

[grid@rddb2 ~]$ sqlplus ‘/ as sysasm’;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 6 10:29:23 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Real Application Clusters and Automatic Storage Management options

 

SQL> show parameter target;

 

NAME                                 TYPE                   VALUE

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

memory_max_target                    big integer            272M

memory_target                        big integer            272M

pga_aggregate_target                 big integer            0

sga_target                           big integer            0

 

SQL> alter system reset memory_target scope=spfile sid=’*’;

alter system reset memory_target scope=spfile sid=’*’

*

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

 

SQL> alter system set memory_target=0 scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system set memory_max_target=0 scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system set sga_target=512M scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system set sga_max_size=1G scope=spfile sid=’*’;  

 

System altered.

 

SQL> alter system set shared_pool_size=150M scope=spfile sid=’*’;

 

System altered.

 

停掉数据库:

[grid@rddb2 ~]$ su – oracle

Password:

[oracle@rddb2 ~]$ srvctl stop database -d db112

 

在各个RAC节点依次执行如下操作以重启ASM实例:

RAC节点1GI

[grid@rddb2 ~]$ su – root

Password:

[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stop crs

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rddb2′

CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rddb2′

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rddb2′

CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘rddb2′

CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘rddb2′

……省略显示部分内容

CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘rddb2′

CRS-2677: Stop of ‘ora.gpnpd’ on ‘rddb2′ succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rddb2′ has completed

CRS-4133: Oracle High Availability Services has been stopped.

 

RAC节点2GI

[grid@rddb3 ~]$ su – root

Password:

[root@rddb3 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stop crs

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rddb3′

CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rddb3′

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rddb3′

CRS-2673: Attempting to stop ‘ora.rddb2.vip’ on ‘rddb3′

……省略显示部分内容

CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘rddb3′

CRS-2677: Stop of ‘ora.gpnpd’ on ‘rddb3′ succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rddb3′ has completed

CRS-4133: Oracle High Availability Services has been stopped.

 

启节点1GI

[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl start crs

CRS-4123: Oracle High Availability Services has been started.

 

在节点1等待1分钟执行如下操作检查GI是否已经全部起来了:

[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4529: Cluster Synchronization Services is online

CRS-4534: Cannot communicate with Event Manager

发现这时候CRSD已经起不来了。

 

这里的原因是因为ASM实例起不来,ASM实例启动报错:

SQL> startup

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 1073741824 cannot be set to more than MEMORY_MAX_TARGET 0.

 

SYSASM角色登录后以pfile启动再覆盖spfile可以解决上述问题:

[grid@rddb2 dbs]$ sqlplus ‘/ as sysasm’;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 6 14:47:09 2014

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

Connected to an idle instance.

 

SQL> startup pfile=/opt/u01/app/11.2.0.3/grid/dbs/init+ASM1.ora

ASM instance started

 

Total System Global Area  283930624 bytes

Fixed Size                  2227664 bytes

Variable Size             256537136 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

 

SQL> create spfile from pfile;

create spfile from pfile

*

ERROR at line 1:

ORA-17502: ksfdcre:4 Failed to create file

+SYSTEMDG/rddb-cluster/asmparameterfile/registry.253.840470757

ORA-15177: cannot operate on system aliases

 

看起来是oracle自身的问题,因为当我加上DG名称后即可成功覆盖spfile

SQL> create spfile=’+SYSTEMDG’ from pfile;

 

File created.

 

然后我在节点1重启GI:

[grid@rddb2 dbs]$ su – root

Password:

[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stop crs

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rddb2′

CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rddb2′

……省略显示部分内容

CRS-2677: Stop of ‘ora.gpnpd’ on ‘rddb2′ succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rddb2′ has completed

CRS-4133: Oracle High Availability Services has been stopped.

 

[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl start crs

CRS-4123: Oracle High Availability Services has been started.

 

可以看到现在节点1GI已经启起来了:

[root@rddb2 ~]# su – grid

[grid@rddb2 ~]$ crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

 

如下才是正确禁掉ASM实例AMM的做法:

特别注意的是——如果要禁掉ASM实例的AMM,就一定不要同时reset memory_targetmemory_max_target,而是应该将memory_target设为0并只reset memory_max_target,但很恶心的是基本上所有的MOS文档都在说要同时reset memory_targetmemory_max_target,所以如果你没有注意到这一点,你会发现你怎么也禁不掉ASM实例的AMM

 

在任意一个RAC节点执行如下操作:

alter system set sga_target=512M scope=spfile sid=’*’;

alter system set pga_aggregate_target=256M scope=spfile sid=’*’;

alter system set shared_pool_size=150M scope=spfile sid=’*’;

alter system set memory_target=0 scope=spfile sid=’*’;

alter system set memory_max_target=0 scope=spfile sid=’*’;

alter system reset memory_max_target scope=spfile sid=’*’;

 

[grid@rddb2 dbs]$ su – grid

Password:

[grid@rddb2 ~]$ sqlplus ‘/ as sysasm’;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 6 15:29:07 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Real Application Clusters and Automatic Storage Management options

 

SQL> alter system set sga_target=512M scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system set pga_aggregate_target=256M scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system set shared_pool_size=150M scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system set memory_target=0 scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system set memory_max_target=0 scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system reset memory_max_target scope=spfile sid=’*’;

 

System altered.

 

然后在节点1重启GI:

[grid@rddb2 ~]$ su – root

Password:

[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stop crs

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rddb2′

CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rddb2′

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rddb2′

CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘rddb2′

CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘rddb2′

……省略显示部分内容

CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘rddb2′

CRS-2677: Stop of ‘ora.gpnpd’ on ‘rddb2′ succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rddb2′ has completed

CRS-4133: Oracle High Availability Services has been stopped.

 

[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl start crs

CRS-4123: Oracle High Availability Services has been started.

 

在节点1等待1分钟后执行如下操作,可以看到现在节点1GI已经启起来了:

[root@rddb2 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

 

从如下查询结果可以看到,现在节点1上的ASM实例的AMM终于被我们禁掉了:

[root@rddb2 ~]# ls -l /dev/shm

total 88

-r——– 1 gdm gdm 67108904 Mar  6 11:15 pulse-shm-3757187464

-r——– 1 gdm gdm 67108904 Mar  6 11:15 pulse-shm-4050473372

 

现在我们再来启节点2GI:

[root@rddb3 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl start crs

CRS-4123: Oracle High Availability Services has been started.

 

从如下结果可以看到,两个节点的GI确实已经都起来了:

[root@rddb3 ~]# /opt/u01/app/11.2.0.3/grid/bin/crsctl stat res -t

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

NAME           TARGET  STATE        SERVER                   STATE_DETAILS      

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

Local Resources

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

ora.DATADG1.dg

               ONLINE  ONLINE       rddb2                                       

               ONLINE  ONLINE       rddb3                                       

ora.FRADG.dg

               ONLINE  ONLINE       rddb2                                       

               ONLINE  ONLINE       rddb3                                       

ora.LISTENER.lsnr

               ONLINE  ONLINE       rddb2                                       

               ONLINE  ONLINE       rddb3                                       

ora.SYSTEMDG.dg

               ONLINE  ONLINE       rddb2                                       

               ONLINE  ONLINE       rddb3                                       

ora.asm

               ONLINE  ONLINE       rddb2                    Started            

               ONLINE  ONLINE       rddb3                    Started            

ora.gsd

               OFFLINE OFFLINE      rddb2                                       

               OFFLINE OFFLINE      rddb3                                       

ora.net1.network

               ONLINE  ONLINE       rddb2                                       

               ONLINE  ONLINE       rddb3                                       

ora.ons

               ONLINE  ONLINE       rddb2                                       

               ONLINE  ONLINE       rddb3                                       

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       rddb2                                       

ora.cvu

      1        ONLINE  ONLINE       rddb2                                       

ora.db112.db

      1        OFFLINE OFFLINE                               Instance Shutdown  

      2        OFFLINE OFFLINE                               Instance Shutdown  

ora.oc4j

      1        ONLINE  ONLINE       rddb2                                       

ora.rddb2.vip

      1        ONLINE  ONLINE       rddb2                                       

ora.rddb3.vip

      1        ONLINE  ONLINE       rddb3                                       

ora.scan1.vip

      1        ONLINE  ONLINE       rddb2

     

我们再来同时reset一下memory_targetmemory_max_target,设置完后重启GI

SQL> alter system set memory_target=0 scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system reset memory_target scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system set memory_max_target=0 scope=spfile sid=’*’;

 

System altered.

 

SQL> alter system reset memory_max_target scope=spfile sid=’*’;

 

System altered.

 

重启完GI后发现ASMAMM果然被Oracle启用了,MOS上的不靠谱文章太坑爹了啊:

[root@rddb2 ~]# ls -l /dev/shm

total 516808

-rw-r—– 1 grid oinstall  4194304 Mar  7 09:56 ora_+ASM1_2326534_0

-rw-r—– 1 grid oinstall  4194304 Mar  7 09:55 ora_+ASM1_2326534_1

-rw-r—– 1 grid oinstall        0 Mar  7 09:55 ora_+ASM1_2359303_0

……省略显示部分内容

-rw-r—– 1 grid oinstall  4194304 Mar  7 09:55 ora_+ASM1_2359303_95

-rw-r—– 1 grid oinstall  4194304 Mar  7 09:55 ora_+ASM1_2359303_96

-rw-r—– 1 grid oinstall  4194304 Mar  7 09:55 ora_+ASM1_2359303_97

-rw-r—– 1 grid oinstall  4194304 Mar  7 09:55 ora_+ASM1_2359303_98

-rw-r—– 1 grid oinstall  4194304 Mar  7 09:56 ora_+ASM1_2359303_99

-rw-r—– 1 grid oinstall  4194304 Mar  7 09:55 ora_+ASM1_2392072_0

-r——– 1 gdm  gdm      67108904 Mar  6 11:15 pulse-shm-3757187464

-r——– 1 gdm  gdm      67108904 Mar  6 11:15 pulse-shm-4050473372

 

此时memory_targetmemory_max_target已经自动被Oracle设置成了808M

[root@rddb2 ~]# su – grid

[grid@rddb2 ~]$ sqlplus ‘/ as sysasm’;

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 7 09:58:00 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Real Application Clusters and Automatic Storage Management options

 

SQL> show parameter memory;

 

NAME                                 TYPE                   VALUE

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

memory_max_target                    big integer            808M

memory_target                        big integer            808M