How to use embedded BBED in ODU to manually fix missing offset in row directory
Posted: April 12, 2014 | Author: Cui Hua | Filed under: Oracle | 2 Comments »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=0x06 (table/index/cluster segment data block)
block format=0xa2 (oracle 10+)
block rdba=0x010002ad (file#=4, block#=685)
scn=0x0000.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: 0x00.1ae559 itc: 3 flg: E typ: 1 (data)
brn: 0 bdba: 0x10002a8 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.001a30aa
0x02 0x0003.004.00000478 0x00c1168c.0180.51 C— 0 scn 0x0000.001a7899
0x03 0x0004.00f.00000398 0x00c11452.00bb.20 C— 0 scn 0x0000.001ae557
Data Block Dump:
================
flag=0x0 ——–
ntab=1
nrow=76
frre=0
fsbo=0xaa
ffeo=0x170
avsp=0x1ed6
tosp=0x1ed6
0xe:pti[0] nrow=76 offs=0
0x12:pri[0] sfll=1
0x14:pri[1] sfll=2
0x16:pri[2] sfll=3
0x18:pri[3] sfll=4
0x1a:pri[4] sfll=5
0x1c:pri[5] sfll=6
0x1e:pri[6] sfll=7
0x20:pri[7] sfll=8
0x22:pri[8] sfll=9
0x24:pri[9] sfll=10
0x26:pri[10] sfll=11
0x28:pri[11] sfll=12
0x2a:pri[12] sfll=13
0x2c:pri[13] sfll=14
0x2e:pri[14] sfll=15
0x30:pri[15] sfll=16
0x32:pri[16] sfll=17
0x34:pri[17] sfll=18
0x36:pri[18] sfll=19
0x38:pri[19] sfll=20
0x3a:pri[20] sfll=21
0x3c:pri[21] sfll=22
0x3e:pri[22] sfll=23
0x40:pri[23] sfll=24
0x42:pri[24] sfll=25
0x44:pri[25] sfll=26
0x46:pri[26] sfll=27
0x48:pri[27] sfll=28
0x4a:pri[28] sfll=29
0x4c:pri[29] sfll=30
0x4e:pri[30] sfll=31
0x50:pri[31] sfll=32
0x52:pri[32] sfll=33
0x54:pri[33] sfll=34
0x56:pri[34] sfll=35
0x58:pri[35] sfll=36
0x5a:pri[36] sfll=37
0x5c:pri[37] sfll=38
0x5e:pri[38] sfll=39
0x60:pri[39] sfll=40
0x62:pri[40] sfll=41
0x64:pri[41] sfll=42
0x66:pri[42] sfll=43
0x68:pri[43] sfll=44
0x6a:pri[44] sfll=45
0x6c:pri[45] sfll=46
0x6e:pri[46] sfll=47
0x70:pri[47] sfll=48
0x72:pri[48] sfll=49
0x74:pri[49] sfll=50
0x76:pri[50] sfll=51
0x78:pri[51] sfll=52
0x7a:pri[52] sfll=53
0x7c:pri[53] sfll=54
0x7e:pri[54] sfll=55
0x80:pri[55] sfll=56
0x82:pri[56] sfll=57
0x84:pri[57] sfll=58
0x86:pri[58] sfll=59
0x88:pri[59] sfll=60
0x8a:pri[60] sfll=61
0x8c:pri[61] sfll=62
0x8e:pri[62] sfll=63
0x90:pri[63] sfll=64
0x92:pri[64] sfll=65
0x94:pri[65] sfll=66
0x96:pri[66] sfll=67
0x98: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=0x06 (table/index/cluster segment data block)
block format=0xa2 (oracle 10+)
block rdba=0x0100250b (file#=4, block#=9483)
scn=0x0000.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: 0x00.1ae559 itc: 2 flg: E typ: 1 (data)
brn: 0 bdba: 0x1002282 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.00f.00000398 0x00c0cf15.0104.16 C— 0 scn 0x0000.001ae557
0x02 0x0003.004.00000478 0x00c08e1b.01b5.3f C-U- 0 scn 0x0000.001a7af8
Data Block Dump:
================
flag=0x0 ——–
ntab=1
nrow=69
frre=0
fsbo=0x9c
ffeo=0x204
avsp=0x1efc
tosp=0x1efc
0xe:pti[0] nrow=69 offs=0
0x12:pri[0] sfll=1
0x14:pri[1] sfll=2
0x16:pri[2] sfll=3
0x18:pri[3] sfll=4
0x1a:pri[4] sfll=5
0x1c:pri[5] sfll=6
0x1e:pri[6] sfll=7
0x20:pri[7] sfll=8
0x22:pri[8] sfll=9
0x24:pri[9] sfll=10
0x26:pri[10] sfll=11
0x28:pri[11] sfll=12
0x2a:pri[12] sfll=13
0x2c:pri[13] sfll=14
0x2e:pri[14] sfll=15
0x30:pri[15] sfll=16
0x32:pri[16] sfll=17
0x34:pri[17] sfll=18
0x36:pri[18] sfll=19
0x38:pri[19] sfll=20
0x3a:pri[20] sfll=21
0x3c:pri[21] sfll=22
0x3e:pri[22] sfll=23
0x40:pri[23] sfll=24
0x42:pri[24] sfll=25
0x44:pri[25] sfll=26
0x46:pri[26] sfll=27
0x48:pri[27] sfll=28
0x4a:pri[28] sfll=29
0x4c:pri[29] sfll=30
0x4e:pri[30] sfll=31
0x50:pri[31] sfll=32
0x52:pri[32] sfll=33
0x54:pri[33] sfll=34
0x56:pri[34] sfll=35
0x58:pri[35] sfll=36
0x5a:pri[36] sfll=37
0x5c:pri[37] sfll=38
0x5e:pri[38] sfll=39
0x60:pri[39] sfll=40
0x62:pri[40] sfll=41
0x64:pri[41] sfll=42
0x66:pri[42] sfll=43
0x68:pri[43] sfll=44
0x6a:pri[44] sfll=45
0x6c:pri[45] sfll=46
0x6e:pri[46] sfll=47
0x70:pri[47] sfll=48
0x72:pri[48] sfll=49
0x74:pri[49] sfll=50
0x76:pri[50] sfll=51
0x78:pri[51] sfll=52
0x7a:pri[52] sfll=53
0x7c:pri[53] sfll=54
0x7e:pri[54] sfll=55
0x80:pri[55] sfll=56
0x82:pri[56] sfll=57
0x84:pri[57] sfll=58
0x86:pri[58] sfll=59
0x88:pri[59] sfll=60
0x8a:pri[60] sfll=61
0x8c:pri[61] sfll=62
0x8e:pri[62] sfll=63
0x90:pri[63] sfll=64
0x92:pri[64] sfll=65
0x94:pri[65] sfll=66
0x96:pri[66] sfll=67
0x98: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=0x06 (table/index/cluster segment data block)
block format=0xa2 (oracle 10+)
block rdba=0x0100026b (file#=4, block#=619)
scn=0x0000.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: 0x00.1a805f itc: 3 flg: E typ: 1 (data)
brn: 0 bdba: 0x1000268 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.001a30aa
0x02 0x0003.004.00000478 0x00c003dd.017f.39 C— 0 scn 0x0000.001a7899
0x03 0x0004.00f.00000398 0x00c016c7.00ba.1e —- 88 fsc 0x1bfe.00000000
Data Block Dump:
================
flag=0x0 ——–
ntab=1
nrow=88
frre=-1
fsbo=0xc2
ffeo=0x13c
avsp=0x210
tosp=0x1ebe
0xe:pti[0] nrow=88 offs=0
0x12:pri[0] offs=0x1f2f
0x14:pri[1] offs=0x1edc
0x16:pri[2] offs=0x1e8c
0x18:pri[3] offs=0x1e3b
0x1a:pri[4] offs=0x1de6
0x1c:pri[5] offs=0x1d94
0x1e:pri[6] offs=0x1d38
0x20:pri[7] offs=0x1ce6
0x22:pri[8] offs=0x1c93
0x24:pri[9] offs=0x1c41
0x26:pri[10] offs=0x1be1
0x28:pri[11] offs=0x1b90
0x2a:pri[12] offs=0x1b40
0x2c:pri[13] offs=0x1ae6
0x2e:pri[14] offs=0x1a93
0x30:pri[15] offs=0x1a41
0x32:pri[16] offs=0x19ef
0x34:pri[17] offs=0x199e
0x36:pri[18] offs=0x194b
0x38:pri[19] offs=0x18fb
0x3a:pri[20] offs=0x18ab
0x3c:pri[21] offs=0x1858
0x3e:pri[22] offs=0x1805
0x40:pri[23] offs=0x17b5
0x42:pri[24] offs=0x1764
0x44:pri[25] offs=0x1711
0x46:pri[26] offs=0x16c1
0x48:pri[27] offs=0x166a
0x4a:pri[28] offs=0x1617
0x4c:pri[29] offs=0x15be
0x4e:pri[30] offs=0x156c
0x50:pri[31] offs=0x151a
0x52:pri[32] offs=0x14c8
0x54:pri[33] offs=0x1476
0x56:pri[34] offs=0x1423
0x58:pri[35] offs=0x13d4
0x5a:pri[36] offs=0x1378
0x5c:pri[37] offs=0x1323
0x5e:pri[38] offs=0x12d0
0x60:pri[39] offs=0x127a
0x62:pri[40] offs=0x122a
0x64:pri[41] offs=0x11d8
0x66:pri[42] offs=0x1187
0x68:pri[43] offs=0x1136
0x6a:pri[44] offs=0x10e4
0x6c:pri[45] offs=0x1091
0x6e:pri[46] offs=0x1041
0x70:pri[47] offs=0xfef
0x72:pri[48] offs=0xf9b
0x74:pri[49] offs=0xf4b
0x76:pri[50] offs=0xefa
0x78:pri[51] offs=0xea7
0x7a:pri[52] offs=0xe54
0x7c:pri[53] offs=0xe01
0x7e:pri[54] offs=0xdae
0x80:pri[55] offs=0xd5c
0x82:pri[56] offs=0xd09
0x84:pri[57] offs=0xca9
0x86:pri[58] offs=0xc54
0x88:pri[59] offs=0xc00
0x8a:pri[60] offs=0xbaa
0x8c:pri[61] offs=0xb54
0x8e:pri[62] offs=0xb01
0x90:pri[63] offs=0xaac
0x92:pri[64] offs=0xa57
0x94:pri[65] offs=0xa07
0x96:pri[66] offs=0x9b7
0x98:pri[67] offs=0x966
0x9a:pri[68] offs=0x90f
0x9c:pri[69] offs=0x8ba
0x9e:pri[70] offs=0x863
0xa0:pri[71] offs=0x80c
0xa2:pri[72] offs=0x7bc
0xa4:pri[73] offs=0x769
0xa6:pri[74] offs=0x710
0xa8:pri[75] offs=0x6be
0xaa:pri[76] offs=0x66c
0xac:pri[77] offs=0x61a
0xae:pri[78] offs=0x5ca
0xb0:pri[79] offs=0x578
0xb2:pri[80] offs=0x526
0xb4:pri[81] offs=0x4d2
0xb6:pri[82] offs=0x2e6
0xb8:pri[83] offs=0x290
0xba:pri[84] offs=0x239
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: 0x3 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: 0x3 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: 0x3 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: 0x3 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 ASSM：base = 76+（itc-1)*24
For MSSM：base = 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: 0x00.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=0x06 (table/index/cluster segment data block)
block format=0xa2 (oracle 10+)
block rdba=0x0100250b (file#=4, block#=9483)
scn=0x0000.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: 0x00.1ae559 itc: 2 flg: E typ: 1 (data)
brn: 0 bdba: 0x1002282 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.00f.00000398 0x00c0cf15.0104.16 C— 0 scn 0x0000.001ae557
0x02 0x0003.004.00000478 0x00c08e1b.01b5.3f C-U- 0 scn 0x0000.001a7af8
Data Block Dump:
================
flag=0x0 ——–
ntab=1
nrow=69
frre=0
fsbo=0x9c
ffeo=0x204
avsp=0x1efc
tosp=0x1efc
0xe:pti[0] nrow=69 offs=0
0x12:pri[0] sfll=1
0x14:pri[1] sfll=2
0x16:pri[2] sfll=3
0x18:pri[3] sfll=4
0x1a:pri[4] sfll=5
0x1c:pri[5] sfll=6
0x1e:pri[6] sfll=7
0x20:pri[7] sfll=8
0x22:pri[8] sfll=9
0x24:pri[9] sfll=10
0x26:pri[10] sfll=11
0x28:pri[11] sfll=12
0x2a:pri[12] sfll=13
0x2c:pri[13] sfll=14
0x2e:pri[14] sfll=15
0x30:pri[15] sfll=16
0x32:pri[16] sfll=17
0x34:pri[17] sfll=18
0x36:pri[18] sfll=19
0x38:pri[19] sfll=20
0x3a:pri[20] sfll=21
0x3c:pri[21] sfll=22
0x3e:pri[22] sfll=23
0x40:pri[23] sfll=24
0x42:pri[24] sfll=25
0x44:pri[25] sfll=26
0x46:pri[26] sfll=27
0x48:pri[27] sfll=28
0x4a:pri[28] sfll=29
0x4c:pri[29] sfll=30
0x4e:pri[30] sfll=31
0x50:pri[31] sfll=32
0x52:pri[32] sfll=33
0x54:pri[33] sfll=34
0x56:pri[34] sfll=35
0x58:pri[35] sfll=36
0x5a:pri[36] sfll=37
0x5c:pri[37] sfll=38
0x5e:pri[38] sfll=39
0x60:pri[39] sfll=40
0x62:pri[40] sfll=41
0x64:pri[41] sfll=42
0x66:pri[42] sfll=43
0x68:pri[43] sfll=44
0x6a:pri[44] sfll=45
0x6c:pri[45] sfll=46
0x6e:pri[46] sfll=47
0x70:pri[47] sfll=48
0x72:pri[48] sfll=49
0x74:pri[49] sfll=50
0x76:pri[50] sfll=51
0x78:pri[51] sfll=52
0x7a:pri[52] sfll=53
0x7c:pri[53] sfll=54
0x7e:pri[54] sfll=55
0x80:pri[55] sfll=56
0x82:pri[56] sfll=57
0x84:pri[57] sfll=58
0x86:pri[58] sfll=59
0x88:pri[59] sfll=60
0x8a:pri[60] sfll=61
0x8c:pri[61] sfll=62
0x8e:pri[62] sfll=63
0x90:pri[63] sfll=64
0x92:pri[64] sfll=65
0x94:pri[65] sfll=66
0x96:pri[66] sfll=67
0x98: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 0x10:
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 0x00 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 0x00 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 0x00 to 0x02 (the itc of datafile 4 block 9483 is 2)
The third part is between 0x00 to 0x10 (because the number of columns of table T1 is 16, which equals 0x10)
Note: the value of third part in that situation is usually 0x10 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 0x0268.
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: 0x0268
68: 0x02d5
67: 0x03a8
66: 0x040f
65: 0x0476
64: 0x04dc
63: 0x0541
62: 0x05aa
61: 0x0612
60: 0x0679
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 ASSM：base = 76+（itc-1)*24
For MSSM：base = 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 (0x45) : 0x0268 – (76+（2-1)*24) = 0x0204
68 (0x44) : 0x02d5 – (76+（2-1)*24) = 0x0271
67 (0x43) : 0x03a8 – (76+（2-1)*24) = 0x0344
66 (0x42) : 0x040f – (76+（2-1)*24) = 0x03ab
65 (0x41) : 0x0476 – (76+（2-1)*24) = 0x0412
64 (0x40) : 0x04dc – (76+（2-1)*24) = 0x0478
63 (0x3f): 0x0541 – (76+（2-1)*24) = 0x04dd
62 (0x3e): 0x05aa – (76+（2-1)*24) = 0x0546
61 (0x3d): 0x0612 – (76+（2-1)*24) = 0x05ae
60 (0x3c): 0x0679 – (76+（2-1)*24) = 0x0615
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
Posted: March 7, 2014 | Author: Cui Hua | Filed under: Oracle | Leave a comment »先交代一下背景：RedHat 6.3 x86_64上的11.2.0.3的两节点RAC，已经打了GI PSU 11.2.0.3.9和Patch:17063116 for 11.2.0.3.7以及Patch:11072246 for 11.2.0.3.9。
这里我想配一下Hugepage，但因为Hugepage和AMM不兼容以及11.2.0.3 RAC的ASM实例默认会启用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节点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.
停RAC节点2的GI：
[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.
启节点1的GI：
[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.
可以看到现在节点1的GI已经启起来了：
[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_target和memory_max_target，而是应该将memory_target设为0并只reset memory_max_target，但很恶心的是基本上所有的MOS文档都在说要同时reset memory_target和memory_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分钟后执行如下操作,可以看到现在节点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-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
现在我们再来启节点2的GI:
[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_target和memory_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后发现ASM的AMM果然被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_target和memory_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
Recent Comments