Skip navigation.

ContractOracle

Syndicate content
Contract Oracle Limited is not affiliated to Oracle Corporation.
Updated: 1 hour 53 min ago

Oracle Redo log contents for Insert to new table.

Mon, 2013-03-04 22:28
The following is an examination of the redo generated for an insert to a new table in a new datafile.  It includes a comparison of the redo log dump, and actual byte contents of the log.  To generate this output I ran :-

create table BBBBB (attribute1 char(5));

alter system switch logfile;
insert into BBBBB values ('AAAAA');
commit;
select vlf.member from v$log vl, v$logfile vlf where vl.group#=vlf.group# and vl.status = 'CURRENT';
alter system switch logfile;

ALTER SYSTEM DUMP LOGFILE 'D:\ORACLE\WIN64\ONLINELOG\O1_MF_3_8L5T3995_.LOG';

In the comparison the dump file output will be red, and the log byte value will be blue.

I ran the example on Windows, so the redo block size is 512 bytes.

I skipped the first block of the redo log, which contains the file header, and the second block, which contains the redo header.  Output starts with the 3rd block, which contains the first redo record.

You can see in the output in red that the RBA (Redo Byte Address) where the first redo record starts is log sequence 9, block 2 (not including file header block) offset 16.  The total length of the redo record is 668 bytes (not including the 16 byte block headers).  The record also includes the VLD which defines redo record header size, SCN, SUBSCN, and Timestamp.

REDO RECORD - Thread:1 RBA: 0x000009.00000002.0010 LEN: 0x029c VLD: 0x0d

SCN: 0x0000.00110ad8 SUBSCN:  1 02/28/2013 16:19:07

# Each block starts with a 16 byte block header containing signature, block number sequence, offset and checksum.
01 22 Signature (the same in every block)
00 00
02 00 00 00 Block Number (2)
09 00 00 00 Sequence Number
10 Offset to redo record (16)
80
66 B2 CheckSum

# At offset 16 the Redo Record Header starts.  For VLD 13 the header is 68 bytes containing VLD, SCN, and Timestamp.
9C 02  Redo Record Size (668) 
00 00
0D 00 VLD (13)
00 00
D8 0A 11 00 SCN
01 00 00 00 Sub SCN
00 00 00 00 
00 00 00 00 
00 00 01 00 
02 00 00 00 
02 00 00 00 
08 00 00 00 
D7 0A 11 00 SCN 
00 00 00 00 
00 00 00 00 
00 00 00 00 
D9 0A 11 00 SCN
00 00 00 00 
7B 1A 32 30 Timestamp

# The first change after the redo record header relates to a change to object 73524 in DBA (Database Block Address) 0x00415129.  The block Class (CLS) is 1 (data block) and the Absolute File Number (AFN) is 1.  The change is Operation (OP) 13.5 which is a "block format" prior to the insert.

CHANGE #1 TYP:1 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad6 SEQ:1 OP:13.5 ENC:0 RBL:0
KTSFRBFMT (block format) redo: Segobjd: 0x00011f34 type: 1 itls: 2 cscn: 0x0000.00110ad6

# change record for OP 13.5 is 48 bytes
0D 05 OP (13.5)
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D6 0A 11 00 00 00 SCN
DC 58 
01 SEQ
01 TYP
34 1F 
06 00
08 00 08 00 
00 00 
34 1F 01 00 SegObjd
01 00 type
02 00 itls
D6 0A 11 00 00 00 cscn
00 00 

# The second change in the redo record is to the same object and block, consisting of operation 13.6 which is a "block link modify" "lock set" following the format.

CHANGE #2 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:1 OP:13.6 ENC:0 RBL:0
KTSFRBLNK (block link modify) redo:  Opcode: LSET (lock set)
Next dba: 0x0041512a itli: 0

# change record for OP 13.6 is 40 bytes
0D 06 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
0D 00 
01 SEQ
00 TYP
34 1F 
04 00
0C 00 02 00 
00 00 
2A 51 41 00 Next dba
00 00 00 00 itli

# The third change in the redo records is operation 13.6 which is a "block link modify" "lock write"

CHANGE #3 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:2 OP:13.6 ENC:0 RBL:0
KTSFRBLNK (block link modify) redo:  Opcode: LWRT (lock write)
Next dba: 0x00000000 itli: 0

# change record for OP 13.6 is 40 bytes
0D 06 OP
01 00 CLS
01 00 AFN
01 00
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
DC 58 
02 SEQ
00 TYP
34 1F 
04 00
0C 00 03 00 
00 00 00 00 Next dba
00 00 00 00 itli
00 00

# The fourth change in the redo record is operation 13.7 to maintain the block linked list (freelist) and high water mark (hwm).

CHANGE #4 TYP:0 CLS:4 AFN:1 DBA:0x00415128 OBJ:73524 SCN:0x0000.00110ac8 SEQ:1 OP:13.7 ENC:0 RBL:0
KTSFRGRP (fgb/shdr modify freelist) redo:
 Opcode: HWMMV (move hwm)
NBK: 1
 Opcode: LUPD_LLIST (link a list)
Slot no: 0, Count: 1
Flag: = 1 xid or slot0 ccnt:  0x0000.000.00000001 Head:  0x00415129 Tail:  0x00415129 

# change record for OP 13.7 is 72 bytes
0D 07 OP
04 00 CLS
01 00 AFN
01 00 
28 51 41 00 DBA
C8 0A 11 00 00 00 SCN
4C 4C 
01   SEQ
00 TYP
34 1F 
06 00
08 00 
20 00 38 34 
01 00 00 00 
01 00 00 00 
05 00 00 00 
01 00 00 00 
00 00 00 00 
01 00 00 00 
00 00 00 00 
01 00 00 00
29 51 41 00 head
29 51 41 00 tail

# The fifth change in the redo record is OP 11.2 which is the actual redo for the insert to the newly formatted block. It contains details on the System Change Number (SCN), Transaction ID (xid), Undo Block Address (uba), table column and data. 
# The xid is 8 bytes composed of Undo segment number(0x006) , Undo segment header transaction table slot (0x009), and sequence number wrap (0x0000032d).
# The uba is 8 bytes composed of DBA of undo block (0x00c00182), Sequence number (0x00ca), and Record number in block (0x20).

CHANGE #5 TYP:0 CLS:1 AFN:1 DBA:0x00415129 OBJ:73524 SCN:0x0000.00110ad8 SEQ:3 OP:11.2 ENC:0 RBL:0
KTB Redo 
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 0
op: F  xid:  0x0006.009.0000032d    uba: 0x00c00182.00ca.20
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00415129  hdba: 0x00415128
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0) size/delt: 9
fb: --H-FL-- lb: 0x1  cc: 1
null: -
col  0: [ 5]  41 41 41 41 41

# change record for OP 11.2 is 112 bytes (will vary depending on table and data)
0B 02 OP
01 00 CLS
01 00 AFN
01 00 
29 51 41 00 DBA
D8 0A 11 00 00 00 SCN
00 00 
03   SEQ
00 TYP
34 1F 
08 00
14 00 31 00 
05 00 01 05 
DC 58 
06 00 09 00 2D 03 00 00 xid
82 01 C0 00 CA 00 20 00 uba
29 51 41 00 bdba
28 51 41 00 hdba
FF 12 maxfr
02 01 
01 00 itli
00 00
2C 01 
01 00 
00 00 
00 00 slot
00 00 
00 00 
00 00 
00 00 
00 00 
00 00 
00 00 
00 00 
09 00 size 
00 00 lb
00 00 
00 00 
00 00 
00 00 
41 41 41 41 41 00 00 00 DATA (AAAAA)


# The sixth change is OP 5.2 containing SCN, uba.

CHANGE #6 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.00110a95 SEQ:2 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0009 sqn: 0x0000032d flg: 0x0012 siz: 108 fbi: 0
            uba: 0x00c00182.00ca.20    pxid:  0x0000.000.00000000

# change record for OP 5.2 is 60 bytes
05 02 OP
1B 00 CLS
03 00 AFN
FF FF 
D0 00 C0 00 DBA
95 0A 11 00 00 00 SCN
DC 58 
02   SEQ
00 TYP
FF FF 
04 00 20 00 
09 00 slt
3A 00 
2D 03 00 00 sqn
82 01 C0 00 CA 00 20 uba
00
12 00 flg 
6C siz (108)
00 
00 00 00 00 
00 00 00 00 
00 00 00 00

# The seventh change is OP 5.4 is the final commit, containing SCN, uba.

CHANGE #7 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.00110ad8 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0009 sqn: 0x0000032d srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00182.00ca.20 ext: 3 spc: 4250 fbi: 0 

# change record for OP 5.4 is 72 bytes
05 04 OP
1B 00 CLS
03 00 AFN
FF FF 
D0 00 C0 00 DBA
D8 0A 11 00 00 00 SCN
DC 58 
01   SEQ
00 TYP
FF FF 
08 00 14 00 
10 00 04 00 
09 00 00 00 
2D 03 00 00 sqn
00 00 
00 00 srt
09 00 sta
00 00 
02 00 flg
00 00 
82 01 C0 00 uba part 1

# The redo record did not fit into one 512 byte block, so now continues in block 3, which starts with a 16 byte header including offset.

# block header = 16 bytes
01 22 Signature
00 00
03 00 00 00 Block Number
09 00 00 00 Sequence Number
BC Offset 
80
EE 58 CheckSum

# continuation of change 7 in new block
CA 00 20 00 uba part 2
03 00  ext
9A 10 spc
00 07 00 00
FB 12 2F 51 

# The eighth and final change is OP 5.1 which is the undo record.

CHANGE #8 TYP:0 CLS:28 AFN:3 DBA:0x00c00182 OBJ:4294967295 SCN:0x0000.00110a95 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 108 spc: 4360 flg: 0x0012 seq: 0x00ca rec: 0x20
            xid:  0x0006.009.0000032d  
ktubl redo: slt: 9 rci: 0 opc: 11.1 [objn: 73524 objd: 73524 tsn: 0]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00c00182.00ca.1d 
prev ctl max cmt scn:  0x0000.00110540  prev tx cmt scn:  0x0000.00110544 
txn start scn:  0x0000.00110ad1  logon user: 0  prev brb: 12583297  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00415129  hdba: 0x00415128
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0)
END OF REDO DUMP

# change record for OP 5.1 is 156 bytes
05 01 OP
1C 00 CLS
03 00 AFN
FF FF 
82 01 C0 00 DBA
95 0A 11 00 00 00 SCN
00 00 
03   SEQ
00 TYP
FF FF 
0A 00 14 00 
4C 00 02 00 
14 00 
00 00 
6C siz (108)
00 
08 11 
12 00 flg
00 00 
06 00 09 00 2D 03 00 00    xid
CA 00 seq
20 00 rec
34 1F 01 00 objn
34 1F 01 00 objd
00 00 00 00 
00 00 00 00 
0B 01 09 00 
08 0C 01 00 
00 00 00 00 
82 01 C0 00 DBA
CA 00 
1D 00 
40 05 11 00 00 00 prev ctl max cmt scn
C0 00 
44 05 11 00 00 00   prev tx cmt scn
00 00 
00 00  
00 00 
D1 0A 11 00 00 00   txn start scn
00 00 
81 01 
C0 00 
00 00 00 00 
00 00 00 00 
03 05 
FF FF 
29 51 41 00 bdba
28 51 41 00 hdba
FF 12 maxfr
03 01 
01 00
00 00
00 00
00 58

Categories: DBA Blogs

A demonstration of Oracle row movement within a block.

Tue, 2013-02-26 02:37
The following is an investigation of what happens in the Oracle block when a record does not have room to grow.


SQL> create table TEST_TABLE(id integer, attribute1 varchar2(200)) tablespace test;

Table created.

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';

   FILE_ID
----------
FILE_NAME
----------------------------------------------------------------
         5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF

SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     73516          73516

SQL> insert into test_table values (1,'AAAAA');

1 row created.

SQL> insert into test_table values (2,'BBBBB');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segme
nt_name = 'TEST_TABLE';

   FILE_ID   BLOCK_ID     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
         5        160          8            5

We extract the block contents using the following program :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html


Block 163 Contains Object ID 73516
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971683
 SCN Base                    :- 1075559
 SCN Wrap                    :- 0
 Sequence                    :- 01 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- 32492
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73516
 Cleanout SCN Base           :- 1075556
 Cleanout SCN Wrap           :- 0
 Spare 4                     :- 00 
 Spare 5                     :- 00 
 ITL Slots                   :- 2
 UNKNOWN Byte                :- 00 
 Flag 00-FREE                :- 32 
 ITL TX Feeelist Slot        :- 00 
 Next Block On Free List     :- 20971680
ITLSlot                     :- 1
 Undo Segment               :- 1
 Undo Segment Slot          :- 5
 Transaction Sequence       :- 633
 Undo Block Address         :- 12583566
 Undo Sequence              :- 165
 Undo Record Number         :- 23
 Spare 1                    :- 0
 Flag                       :- 8194
 _ktbitun                   :- 0
 Base                       :- 1075559
ITLSlot                     :- 2
 Undo Segment               :- 0
 Undo Segment Slot          :- 0
 Transaction Sequence       :- 0
 Undo Block Address         :- 0
 Undo Sequence              :- 0
 Undo Record Number         :- 0
 Spare 1                    :- 0
 Flag                       :- 0
 _ktbitun                   :- 0
 Base                       :- 0
Data Header start        :- 100
 Flags                       :- 00 
 Number of Tables            :- 1
 Number of Rows              :- 2
 Offset to Freespace Start   :- -1
 Offset to Freespace End     :- 22
 Available Space             :- 8064
 Available Space after Commit:- 8042
Table Directory start    :-114
 Table                       :- 1
  Offset                      :- 0
  Number of Rows              :- 2
 Total Number of Rows        :- 2
Row Directory start      :-118
 Row Offset 1 :- 8076(+100)
 Row Offset 2 :- 8064(+100)
1 Row Header start  :-8176
 Flags              :-  2C  Table Data
 Lock Status        :- 01 
 Number of Columns  :- 2
Column 1 Bytes 2 Data :- C1 02 
Column 2 Bytes 5 Data :- AAAAA
2 Row Header start  :-8164
 Flags              :-  2C  Table Data
 Lock Status        :- 01 
 Number of Columns  :- 2
Column 1 Bytes 2 Data :- C1 03 
Column 2 Bytes 5 Data :- BBBBB

From the above we can see that there are two rows in block 163, starting at bytes 8164 and 8176.  The first record inserted (AAAAA) is at the end of the block, with no room to grow.

If we update the record with more data :-


SQL> update test_table set attribute1 = '012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567890' where attribute1 = 'AAAAA'
;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.


We can now see that :-

1. the block SCNs and sequence have been updated
2. the block CheckSum has been updated
3. the Available Space in the block has decreased
4. the record that previously started at byte 8176 has now moved to byte 8066 to allow it to grow.


Block 163 Contains Object ID 73516
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971683
 SCN Base                    :- 1075759
 SCN Wrap                    :- 0
 Sequence                    :- 02 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- -25092
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73516
 Cleanout SCN Base           :- 1075757
 Cleanout SCN Wrap           :- 0
 Spare 4                     :- 00 
 Spare 5                     :- 00 
 ITL Slots                   :- 2
 UNKNOWN Byte                :- 00 
 Flag 00-FREE                :- 32 
 ITL TX Feeelist Slot        :- 00 
 Next Block On Free List     :- 20971680
ITLSlot                     :- 1
 Undo Segment               :- 1
 Undo Segment Slot          :- 5
 Transaction Sequence       :- 633
 Undo Block Address         :- 12583566
 Undo Sequence              :- 165
 Undo Record Number         :- 23
 Spare 1                    :- 0
 Flag                       :- -32768
 _ktbitun                   :- 0
 Base                       :- 1075559
ITLSlot                     :- 2
 Undo Segment               :- 7
 Undo Segment Slot          :- 22
 Transaction Sequence       :- 605
 Undo Block Address         :- 12583559
 Undo Sequence              :- 222
 Undo Record Number         :- 49
 Spare 1                    :- 0
 Flag                       :- 8193
 _ktbitun                   :- 0
 Base                       :- 1075759
Data Header start        :- 100
 Flags                       :- 00 
 Number of Tables            :- 1
 Number of Rows              :- 2
 Offset to Freespace Start   :- -1
 Offset to Freespace End     :- 22
 Available Space             :- 7966
 Available Space after Commit:- 7956
Table Directory start    :-114
 Table                       :- 1
  Offset                      :- 0
  Number of Rows              :- 2
 Total Number of Rows        :- 2
Row Directory start      :-118
 Row Offset 1 :- 7966(+100)
 Row Offset 2 :- 8064(+100)
1 Row Header start  :-8066
 Flags              :-  2C  Table Data
 Lock Status        :- 02 
 Number of Columns  :- 2
Column 1 Bytes 2 Data :- C1 02 
Column 2 Bytes 91 Data :- 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
2 Row Header start  :-8164
 Flags              :-  2C  Table Data
 Lock Status        :- 00 
 Number of Columns  :- 2
Column 1 Bytes 2 Data :- C1 03 
Column 2 Bytes 5 Data :- BBBBB



Categories: DBA Blogs

What happens to Oracle data blocks during Truncate.

Tue, 2013-02-26 02:05
The following is an investigation of what happens during an Oracle table truncate.  First we create a table and insert two records.


SQL> create table TEST_TABLE(id integer, attribute1 char(5), attribute2 varchar2 (5)) tablespace test;

Table created.

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';

   FILE_ID
----------
FILE_NAME
-----------------------------------------------------------------
         5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF


SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     73514          73514

SQL> insert into test_table values (1,'AAAAA','BBB');

1 row created.

SQL> insert into test_table values (2,'CCCCC','DDDDD');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segment_name = 'TEST_TABLE';

   FILE_ID   BLOCK_ID     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
         5        152          8            5


We can find the data for the table in file 5 in an extent starting at block 152.  When we scan for the data, we find that the records we inserted are in block 155, with empty blocks 156,7,8,9.  The following output is from :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html



Block 155 Contains Object ID 73514Block Header start       :- 0 Block Type 06-DATA          :- 06  Block Format                :- A2  Spare1                      :- 00  Spare2                      :- 00  Relative Block Address      :- 20971675 SCN Base                    :- 1074483 SCN Wrap                    :- 0 Sequence                    :- 01  Flag 01-NEW                 :- 06  CheckSum                    :- 14737 Spare3                      :- 0Transaction Header start  :- 20 Type 01-DATA 02-INDEX       :- 01  Spare 1                     :- 00  Spare 2                     :- 00  Spare 3                     :- 00  Object ID                   :- 73514 Cleanout SCN Base           :- 1074478 Cleanout SCN Wrap           :- 0 Spare 4                     :- 00  Spare 5                     :- 00  ITL Slots                   :- 2 UNKNOWN Byte                :- 00  Flag 00-FREE                :- 32  ITL TX Feeelist Slot        :- 00  Next Block On Free List     :- 20971672ITLSlot                     :- 1 Undo Segment               :- 1 Undo Segment Slot          :- 14 Transaction Sequence       :- 632 Undo Block Address         :- 12583042 Undo Sequence              :- 162 Undo Record Number         :- 51 Spare 1                    :- 0 Flag                       :- 8194 _ktbitun                   :- 0 Base                       :- 1074483ITLSlot                     :- 2 Undo Segment               :- 0 Undo Segment Slot          :- 0 Transaction Sequence       :- 0 Undo Block Address         :- 0 Undo Sequence              :- 0 Undo Record Number         :- 0 Spare 1                    :- 0 Flag                       :- 0 _ktbitun                   :- 0 Base                       :- 0Data Header start        :- 100 Flags                       :- 00  Number of Tables            :- 1 Number of Rows              :- 2 Offset to Freespace Start   :- -1 Offset to Freespace End     :- 22 Available Space             :- 8054 Available Space after Commit:- 8032Table Directory start    :-114 Table                       :- 1  Offset                      :- 0  Number of Rows              :- 2 Total Number of Rows        :- 2Row Directory start      :-118 Row Offset 1 :- 8072(+100) Row Offset 2 :- 8054(+100)1 Row Header start  :-8172 Flags              :-  2C  Table Data Lock Status        :- 01  Number of Columns  :- 3Column 1 Bytes 2 Data :- C1 02 Column 2 Bytes 5 Data :- AAAAAColumn 3 Bytes 3 Data :- BBB2 Row Header start  :-8154 Flags              :-  2C  Table Data Lock Status        :- 01  Number of Columns  :- 3Column 1 Bytes 2 Data :- C1 03 Column 2 Bytes 5 Data :- CCCCCColumn 3 Bytes 5 Data :- DDDDD
Block 156 Contains Object ID 73514Block 157 Contains Object ID 73514Block 158 Contains Object ID 73514Block 159 Contains Object ID 73514
Now we truncate the table.  Selecting from the data dictionary table we can see that the data blocks listed in DBA_EXTENTS did not change, but in DBA_OBJECTS the DATA_OBJECT_ID was updated.



SQL> truncate table test_table;
Table truncated.
SQL> alter system checkpoint;
System altered.
SQL> select * from test_table;

no rows selected


SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';
 OBJECT_ID DATA_OBJECT_ID---------- --------------     73514          73515
SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segment_name = 'TEST_TABLE';
   FILE_ID   BLOCK_ID     BLOCKS RELATIVE_FNO---------- ---------- ---------- ------------         5        152          8            5
If we again scan for blocks associated with the old DATA_OBJECT_ID 73514 we can see that they are unchanged and still have the original data in them.  This means that even after a table is truncated, the data remains in the blocks on disk, and could be extracted (until the blocks are overwritten).


Block 155 Contains Object ID 73514
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971675
 SCN Base                    :- 1074483
 SCN Wrap                    :- 0
 Sequence                    :- 01 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- 14737
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73514
 Cleanout SCN Base           :- 1074478
 Cleanout SCN Wrap           :- 0
 Spare 4                     :- 00 
 Spare 5                     :- 00 
 ITL Slots                   :- 2
 UNKNOWN Byte                :- 00 
 Flag 00-FREE                :- 32 
 ITL TX Feeelist Slot        :- 00 
 Next Block On Free List     :- 20971672
ITLSlot                     :- 1
 Undo Segment               :- 1
 Undo Segment Slot          :- 14
 Transaction Sequence       :- 632
 Undo Block Address         :- 12583042
 Undo Sequence              :- 162
 Undo Record Number         :- 51
 Spare 1                    :- 0
 Flag                       :- 8194
 _ktbitun                   :- 0
 Base                       :- 1074483
ITLSlot                     :- 2
 Undo Segment               :- 0
 Undo Segment Slot          :- 0
 Transaction Sequence       :- 0
 Undo Block Address         :- 0
 Undo Sequence              :- 0
 Undo Record Number         :- 0
 Spare 1                    :- 0
 Flag                       :- 0
 _ktbitun                   :- 0
 Base                       :- 0
Data Header start        :- 100
 Flags                       :- 00 
 Number of Tables            :- 1
 Number of Rows              :- 2
 Offset to Freespace Start   :- -1
 Offset to Freespace End     :- 22
 Available Space             :- 8054
 Available Space after Commit:- 8032
Table Directory start    :-114
 Table                       :- 1
  Offset                      :- 0
  Number of Rows              :- 2
 Total Number of Rows        :- 2
Row Directory start      :-118
 Row Offset 1 :- 8072(+100)
 Row Offset 2 :- 8054(+100)
1 Row Header start  :-8172
 Flags              :-  2C  Table Data
 Lock Status        :- 01 
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 02 
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start  :-8154
 Flags              :-  2C  Table Data
 Lock Status        :- 01 
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 03 
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD

Block 156 Contains Object ID 73514
Block 157 Contains Object ID 73514
Block 158 Contains Object ID 73514
Block 159 Contains Object ID 73514



Categories: DBA Blogs

What happens in an Oracle block during a delete.

Tue, 2013-02-26 01:23
The following is an investigation of what happens in the Oracle database block when a record is deleted.  First we create a table with 2 rows.


SQL> create table TEST_TABLE(id integer, attribute1 char(5), attribute2 varchar2 (5)) tablespace test;

Table created.

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';

   FILE_ID
----------
FILE_NAME
----------------------------------------------------------------
         5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF


SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST_TABLE';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     73513          73513

SQL> insert into test_table values (1,'AAAAA','BBB');

1 row created.

SQL> insert into test_table values (2,'CCCCC','DDDDD');

1 row created.

SQL> commit;

Commit complete.

SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segment_name = 'TEST_TABLE';

   FILE_ID   BLOCK_ID     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
         5        144          8            5

SQL> alter system checkpoint;

System altered.


When we scan the file for data object ID 73513 we find the data in block 150.  The following block data was extracted using :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html


Block 150 Contains Object ID 73513
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971670
 SCN Base                    :- 1073062
 SCN Wrap                    :- 0
 Sequence                    :- 01 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- 14473
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73513
 Cleanout SCN Base           :- 1073058
 Cleanout SCN Wrap           :- 0
 Spare 4                     :- 00 
 Spare 5                     :- 00 
 ITL Slots                   :- 2
 UNKNOWN Byte                :- 00 
 Flag 00-FREE                :- 32 
 ITL TX Feeelist Slot        :- 00 
 Next Block On Free List     :- 20971664
ITLSlot                     :- 1
 Undo Segment               :- 5
 Undo Segment Slot          :- 33
 Transaction Sequence       :- 814
 Undo Block Address         :- 12583110
 Undo Sequence              :- 169
 Undo Record Number         :- 6
 Spare 1                    :- 0
 Flag                       :- 8194
 _ktbitun                   :- 0
 Base                       :- 1073062
ITLSlot                     :- 2
 Undo Segment               :- 0
 Undo Segment Slot          :- 0
 Transaction Sequence       :- 0
 Undo Block Address         :- 0
 Undo Sequence              :- 0
 Undo Record Number         :- 0
 Spare 1                    :- 0
 Flag                       :- 0
 _ktbitun                   :- 0
 Base                       :- 0
Data Header start        :- 100
 Flags                       :- 00 
 Number of Tables            :- 1
 Number of Rows              :- 2
 Offset to Freespace Start   :- -1
 Offset to Freespace End     :- 22
 Available Space             :- 8054
 Available Space after Commit:- 8032
Table Directory start    :-114
 Table                       :- 1
  Offset                      :- 0
  Number of Rows              :- 2
 Total Number of Rows        :- 2
Row Directory start      :-118
 Row Offset 1 :- 8072(+100)
 Row Offset 2 :- 8054(+100)
1 Row Header start  :-8172
 Flags              :-  2C  Table Data
 Lock Status        :- 01 
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 02 
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start  :-8154
 Flags              :-  2C  Table Data
 Lock Status        :- 01 
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 03 
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD


Now we delete one row :-


SQL> delete from test_table where id = 1;

1 row deleted.

SQL> commit;

Commit complete.


SQL> select * from test_table;

        ID ATTRI ATTRI
---------- ----- -----
         2 CCCCC DDDDD


SQL> alter system checkpoint;

System altered.



And we can extract the block data again and confirm that the result of the delete is that  :-
1. the block SCNs and Sequence have been updated2. the block CheckSum has been updated3. ITL Slot 2 was used for the transaction4. ITL Slot 1 flag was set to -32768 (unused)5. The row header flag was updated from 2C to 3C6. The row header lock status was updated to 02
Note that the delete did not actually remove the record from the block, it just updated the row flag.  This means that even if a record has been deleted from an Oracle database table is possible to read the data for the deleted record directly from the block (until it is overwritten).


Block 150 Contains Object ID 73513
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971670
 SCN Base                    :- 1073294
 SCN Wrap                    :- 0
 Sequence                    :- 02 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- -6419
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73513
 Cleanout SCN Base           :- 1073292
 Cleanout SCN Wrap           :- 0
 Spare 4                     :- 00 
 Spare 5                     :- 00 
 ITL Slots                   :- 2
 UNKNOWN Byte                :- 00 
 Flag 00-FREE                :- 32 
 ITL TX Feeelist Slot        :- 00 
 Next Block On Free List     :- 20971664
ITLSlot                     :- 1
 Undo Segment               :- 5
 Undo Segment Slot          :- 33
 Transaction Sequence       :- 814
 Undo Block Address         :- 12583110
 Undo Sequence              :- 169
 Undo Record Number         :- 6
 Spare 1                    :- 0
 Flag                       :- -32768
 _ktbitun                   :- 0
 Base                       :- 1073062
ITLSlot                     :- 2
 Undo Segment               :- 6
 Undo Segment Slot          :- 27
 Transaction Sequence       :- 797
 Undo Block Address         :- 12583068
 Undo Sequence              :- 174
 Undo Record Number         :- 43
 Spare 1                    :- 0
 Flag                       :- 8193
 _ktbitun                   :- 14
 Base                       :- 1073294
Data Header start        :- 100
 Flags                       :- 00 
 Number of Tables            :- 1
 Number of Rows              :- 2
 Offset to Freespace Start   :- -1
 Offset to Freespace End     :- 22
 Available Space             :- 8054
 Available Space after Commit:- 8032
Table Directory start    :-114
 Table                       :- 1
  Offset                      :- 0
  Number of Rows              :- 2
 Total Number of Rows        :- 2
Row Directory start      :-118
 Row Offset 1 :- 8072(+100)
 Row Offset 2 :- 8054(+100)
1 Row Header start  :-8172
 Flags              :-  3C  Deleted
 Lock Status        :- 02 
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 02 
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start  :-8154
 Flags              :-  2C  Table Data
 Lock Status        :- 00 
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 03 
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD


Categories: DBA Blogs

What happens in an Oracle Block during an update.

Tue, 2013-02-26 00:50
The following is an investigation of what happens in an Oracle datafile block during an update.  First we create a simple table with two rows.


SQL> create table TEST_TABLE(id integer, attribute1 char(5), attribute2 varchar2(5)) tablespace test;

Table created.

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'TEST';

   FILE_ID
----------
FILE_NAME
-----------------------------------------------------------------
         5
D:\ORACLE\WIN64\DATAFILE\O1_MF_TEST_8LRBSQ59_.DBF


SQL> select object_id, data_object_id from dba_objects where object_name = 'TEST
_TABLE';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     73512          73512

SQL> insert into test_table values (1,'AAAAA','BBB');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test_table values (2,'CCCCC','DDDDD');

1 row created.

SQL> commit;

Commit complete.

SQL> select file_id, block_id, blocks, relative_fno from dba_extents where segme
nt_name = 'TEST_TABLE';

   FILE_ID   BLOCK_ID     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ------------
         5        136          8            5

SQL> alter system checkpoint;

System altered.


The table we are working on for this test is stored in file 5 with extent starting at block 136 in blocks with DATA_OBJECT_ID 73512 .  When we scan the file for blocks with data object ID 73512 we can see that the rows we are interested in are in block 139.  Two ITL Slots have been created in the block.

The following block data was extracted using :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html


Block 139 Contains Object ID 73512
Block Header start       :- 0
 Block Type 06-DATA          :- 06 
 Block Format                :- A2 
 Spare1                      :- 00 
 Spare2                      :- 00 
 Relative Block Address      :- 20971659
 SCN Base                    :- 1072047
 SCN Wrap                    :- 0
 Sequence                    :- 02 
 Flag 01-NEW                 :- 06 
 CheckSum                    :- 17170
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01 
 Spare 1                     :- 00 
 Spare 2                     :- 00 
 Spare 3                     :- 00 
 Object ID                   :- 73512
 Cleanout SCN Base           :- 1072042
 Cleanout SCN Wrap           :- 0
 Spare 4                     :- 00 
 Spare 5                     :- 00 
 ITL Slots                   :- 2
 UNKNOWN Byte                :- 00 
 Flag 00-FREE                :- 32 
 ITL TX Feeelist Slot        :- 00 
 Next Block On Free List     :- 20971656
ITLSlot                     :- 1
 Undo Segment               :- 10
 Undo Segment Slot          :- 29
 Transaction Sequence       :- 633
 Undo Block Address         :- 12583185
 Undo Sequence              :- 140
 Undo Record Number         :- 3
 Spare 1                    :- 0
 Flag                       :- 8193
 _ktbitun                   :- 0
 Base                       :- 1072043
ITLSlot                     :- 2
 Undo Segment               :- 5
 Undo Segment Slot          :- 19
 Transaction Sequence       :- 814
 Undo Block Address         :- 12583108
 Undo Sequence              :- 169
 Undo Record Number         :- 33
 Spare 1                    :- 0
 Flag                       :- 8193
 _ktbitun                   :- 0
 Base                       :- 1072047
Data Header start        :- 100
 Flags                       :- 00 
 Number of Tables            :- 1
 Number of Rows              :- 2
 Offset to Freespace Start   :- -1
 Offset to Freespace End     :- 22
 Available Space             :- 8054
 Available Space after Commit:- 8032
Table Directory start    :-114
 Table                       :- 1
  Offset                      :- 0
  Number of Rows              :- 2
 Total Number of Rows        :- 2
Row Directory start      :-118
 Row Offset 1 :- 8072(+100)
 Row Offset 2 :- 8054(+100)
1 Row Header start  :-8172
 Flags              :-  2C  Table Data
 Lock Status        :- 01 
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 02 
Column 2 Bytes 5 Data :- AAAAA
Column 3 Bytes 3 Data :- BBB
2 Row Header start  :-8154
 Flags              :-  2C  Table Data
 Lock Status        :- 02 
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 03 
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD



Now we update one record in the table.


SQL> update test_table set attribute1 = 'EEEEE' where attribute1 = 'AAAAA';
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.

After dumping the block we can see that :-


1. block SCNs were updated
2. block CheckSum was updated
3. ITL Slot 1 was used for the transaction
4. ITL Slot 2 flag was updated to -32768 (I guess indicating unused)
5. The Row lock status for the untouched row was cleared.
6. The data was updated.



Block 139 Contains Object ID 73512
Block Header start       :- 0
 Block Type 06-DATA          :- 06
 Block Format                :- A2
 Spare1                      :- 00
 Spare2                      :- 00
 Relative Block Address      :- 20971659
 SCN Base                    :- 1072247
 SCN Wrap                    :- 0
 Sequence                    :- 02
 Flag 01-NEW                 :- 06
 CheckSum                    :- -6509
 Spare3                      :- 0
Transaction Header start  :- 20
 Type 01-DATA 02-INDEX       :- 01
 Spare 1                     :- 00
 Spare 2                     :- 00
 Spare 3                     :- 00
 Object ID                   :- 73512
 Cleanout SCN Base           :- 1072246
 Cleanout SCN Wrap           :- 0
 Spare 4                     :- 00
 Spare 5                     :- 00
 ITL Slots                   :- 2
 UNKNOWN Byte                :- 00
 Flag 00-FREE                :- 32
 ITL TX Feeelist Slot        :- 00
 Next Block On Free List     :- 20971656
ITLSlot                     :- 1
 Undo Segment               :- 4
 Undo Segment Slot          :- 16
 Transaction Sequence       :- 596
 Undo Block Address         :- 12583572
 Undo Sequence              :- 147
 Undo Record Number         :- 55
 Spare 1                    :- 0
 Flag                       :- 8193
 _ktbitun                   :- 0
 Base                       :- 1072247
ITLSlot                     :- 2
 Undo Segment               :- 5
 Undo Segment Slot          :- 19
 Transaction Sequence       :- 814
 Undo Block Address         :- 12583108
 Undo Sequence              :- 169
 Undo Record Number         :- 33
 Spare 1                    :- 0
 Flag                       :- -32768
 _ktbitun                   :- 0
 Base                       :- 1072047
Data Header start        :- 100
 Flags                       :- 00
 Number of Tables            :- 1
 Number of Rows              :- 2
 Offset to Freespace Start   :- -1
 Offset to Freespace End     :- 22
 Available Space             :- 8054
 Available Space after Commit:- 8032
Table Directory start    :-114
 Table                       :- 1
  Offset                      :- 0
  Number of Rows              :- 2
 Total Number of Rows        :- 2
Row Directory start      :-118
 Row Offset 1 :- 8072(+100)
 Row Offset 2 :- 8054(+100)
1 Row Header start  :-8172
 Flags              :-  2C  Table Data
 Lock Status        :- 01
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 02
Column 2 Bytes 5 Data :- EEEEE
Column 3 Bytes 3 Data :- BBB
2 Row Header start  :-8154
 Flags              :-  2C  Table Data
 Lock Status        :- 00
 Number of Columns  :- 3
Column 1 Bytes 2 Data :- C1 03
Column 2 Bytes 5 Data :- CCCCC
Column 3 Bytes 5 Data :- DDDDD


Categories: DBA Blogs

Java program to extract records from Oracle Control Files.

Mon, 2013-02-25 21:03
Following is the simple Java program I used to extract database name, file names, and tablespace names from Oracle Control Files.  It could easily be modified to extract backup information etc.

It is a large Java program laid out in order of execution for easy reading and learning purposes.  Yes, I know that  a "real Java programmer" would do a better job, and there is lots of room for improvement, but it is good enough for learning about Oracle data structures.

Anyone is welcome to download it, modify it, and use it for any purpose, but I don't guarantee that it extracts all records available, or will work on all OS or DB versions.  Use it at your own risk.  Enjoy !

An example of the output can be found here :- http://blog.contractoracle.com/2013/02/oracle-control-file-physical-structure.html


# ControlScanCMD.java


import java.io.IOException;
import java.io.RandomAccessFile;

public class ControlScanCMD {

public static void main(String[] args) {
try {

    RandomAccessFile raf = new RandomAccessFile("D:\\oracle\\WIN64\\CONTROLFILE\\O1_MF_8L5T35GK_.CTL", "r");
int BlockSize = 8192*2;
int BlockStart = 0;
int BlockNum;
int FileSize =(int)raf.length();
int NumBlocks = FileSize / BlockSize;
int NextRecordStart;
int FileRecordSize = 524;
int TablespaceRecordSize = 68;
int HeaderSize =18;
int TailSize = 4;
int RecordCount = 0;
int FileIdOffset = 4;
int TablespaceFirstChar = 9;
int NameOffset = 14;
int Pointer = 0;
byte MyByte = 0;
char MyChar = 0;
int FileID = 0;

System.out.println("Number of Blocks                 :- " + NumBlocks);

// get database name

BlockStart = BlockSize * 1;
System.out.println("Block                            :- 2");
System.out.print("Tablespace Name :- ");

raf.seek(BlockStart + HeaderSize + NameOffset);

for(int ct1 = 1; ct1 <= 8; ct1++)
{
MyByte = raf.readByte();
if (MyByte == 0) 

System.out.print("");
//System.out.println(String.format("%02X ", raf.readByte()));
}
else if (32 < MyByte && MyByte <= 126) 
{
//MyChar = ((char)Integer.parseInt(String.format("%02X", MyByte), 16));
MyChar = (char)MyByte;
System.out.print("" + MyChar);

//jTextAreaFile.append(" " + ct + "-" + String.format("%02X", MyByte) + " ");
}
else

System.out.print(" " + ct1 + "-" + String.format("%02X", MyByte) + " ");
}
}

System.out.println("");
System.out.println("");

// get file details

for(BlockNum = 31; BlockNum <= 32; BlockNum++)
{
BlockStart = BlockSize * BlockNum;
NextRecordStart = HeaderSize;
RecordCount = 0;
System.out.println("Block                            :- " + BlockNum);

raf.seek(BlockStart + NextRecordStart + FileIdOffset);
FileID = raf.readByte();

while (FileID != 0 && (NextRecordStart + FileRecordSize) < BlockSize)
{
RecordCount = RecordCount + 1;
raf.seek(BlockStart + NextRecordStart);

System.out.println("Record                           :- " + RecordCount);
System.out.println("Byte 1                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 2                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("File Type 3-LOG, 4-DATA, 7-TEMP  :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 4                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("File ID                          :- " + raf.readByte());
System.out.println("Byte 6                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 7                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 8                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 9                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 10                          :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 11                          :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 12                          :- " + String.format("%02X ", raf.readByte()));
System.out.print("File Name :- ");

for(int ct1 = 1; ct1 <= 512; ct1++)
{
MyByte = raf.readByte();
if (MyByte == 0) 

System.out.print("");
//System.out.println(String.format("%02X ", raf.readByte()));
}
else if (32 < MyByte && MyByte <= 126) 
{
//MyChar = ((char)Integer.parseInt(String.format("%02X", MyByte), 16));
MyChar = (char)MyByte;
System.out.print("" + MyChar);

//jTextAreaFile.append(" " + ct + "-" + String.format("%02X", MyByte) + " ");
}
else

System.out.print(" " + ct1 + "-" + String.format("%02X", MyByte) + " ");
}
}
System.out.println("");
System.out.println("");
NextRecordStart = NextRecordStart + FileRecordSize;
raf.seek(BlockStart + NextRecordStart + FileIdOffset);
FileID = raf.readByte();
}


// get tablespace names

for(BlockNum = 179; BlockNum <= 179; BlockNum++)
{
BlockStart = BlockSize * BlockNum;
NextRecordStart = HeaderSize;
RecordCount = 0;
System.out.println("Block                            :- " + BlockNum);

raf.seek(BlockStart + NextRecordStart + TablespaceFirstChar);
FileID = raf.readByte();

while (FileID != 0 && (NextRecordStart + TablespaceRecordSize) < BlockSize)
{
RecordCount = RecordCount + 1;
raf.seek(BlockStart + NextRecordStart);

System.out.println("Record                           :- " + RecordCount);
System.out.println("Byte 1                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 2                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Tablespace ID                    :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 4                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 5                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 6                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 7                           :- " + String.format("%02X ", raf.readByte()));
System.out.println("Byte 8                           :- " + String.format("%02X ", raf.readByte()));
System.out.print("Tablespace Name :- ");


for(int ct1 = 1; ct1 <= 30; ct1++)
{
MyByte = raf.readByte();
if (MyByte == 0) 

System.out.print("");
//System.out.println(String.format("%02X ", raf.readByte()));
}
else if (32 < MyByte && MyByte <= 126) 
{
//MyChar = ((char)Integer.parseInt(String.format("%02X", MyByte), 16));
MyChar = (char)MyByte;
System.out.print("" + MyChar);

//jTextAreaFile.append(" " + ct + "-" + String.format("%02X", MyByte) + " ");
}
else

System.out.print(" " + ct1 + "-" + String.format("%02X", MyByte) + " ");
}
}
System.out.println("");
System.out.println("");
NextRecordStart = NextRecordStart + TablespaceRecordSize;
raf.seek(BlockStart + NextRecordStart + TablespaceFirstChar);
FileID = raf.readByte();
}
}



}
raf.close();

      } catch (IOException ex) {
         ex.printStackTrace();
      }

}
}
Categories: DBA Blogs

Java program to extract data from Oracle datafile blocks.

Mon, 2013-02-25 20:54
I wrote the following Java program to explore the Oracle data block.  It scans the specified Oracle datafile for blocks belonging to a particular object and then extracts the row data directly from datafile blocks.

Because it extracts directly from the block it can be used to extract data from corrupt files or blocks, truncated tables, and even extract deleted rows.  
The program is written purely for learning, so I don't guarantee it will extract all records in a block, or that it can handle all data types.  I am happy for anyone to copy the program or improve it, but I do not provide any guarantee for it.  Use it at your own risk.
It is a large Java program laid out in order of execution for easy reading and learning purposes.  Yes, I know that  a "real Java programmer" would do a better job, and there is lots of room for improvement, but it is good enough for learning about Oracle data structures.

An example of the program output can be found here :- http://blog.contractoracle.com/2013/02/extracting-data-directly-from-oracle.html

# OraTabExport.java

import java.io.IOException;
import java.io.RandomAccessFile;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class OraTabExport {

public static void main(String[] args) {
try {

        RandomAccessFile raf = new RandomAccessFile("D:\\oracle\\WIN64\\DATAFILE\\O1_MF_TEST_8LRBSQ59_.DBF", "r");
int BlockSize = 8192;
int ObjectID = 73520;

int StartByte = 0;
int ObjectIDLoc = 24;
int ITLSlotsLoc = 36;
int ITLSlots = 2;
int ITLSlot = 0;
int NumTablesLoc = 0;
int BlockObjectID = 0;
int FileSize =(int)raf.length();
int NumBlocks = FileSize / BlockSize;
int BlockNum = 0;
int DataHeaderStart = 0;
int TableDirectoryStart = 0;
int RowDirectoryStart =0;
int RowStart = 0;
int BlockHeaderSize = 20;
int TransactionHeaderSize = 24;
int DataHeaderSize = 14;
int TableDirectorySize = 4;
int NumTables = 0;
int TableNum = 0;
int NumTableRows = 0;
int TotalTableRows = 0;
int RowNumber = 0;
int NumColumns = 0;
int ColNum = 0;
int ColumnSize = 0;
int RowFlag = 0;
short RowOffset = 0;
int MinOffset = 0;
int MaxOffset = 0;
int OffsetCount = 0;
int ByteNum = 0;
Byte MyByte = 0;
char MyChar = 0;


for(BlockNum = 0; BlockNum <= NumBlocks -1; BlockNum++)
{
StartByte = BlockSize * BlockNum;
raf.seek(StartByte + ObjectIDLoc);
BlockObjectID = Integer.reverseBytes(raf.readInt());


raf.seek(StartByte + ITLSlotsLoc);
ITLSlots = raf.read();  

if ( BlockObjectID == ObjectID)
{

raf.seek(StartByte);

System.out.println("\nBlock " + BlockNum + " Contains Object ID " + BlockObjectID);

// The first 20 bytes is the Block Header

System.out.println("Block Header start       :- " + 0);
System.out.println(" Block Type 06-DATA          :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Block Format                :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare1                      :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare2                      :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Relative Block Address      :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" SCN Base                    :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" SCN Wrap                    :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Sequence                    :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Flag 01-NEW                 :- " + String.format("%02X ", raf.readByte()));
System.out.println(" CheckSum                    :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Spare3                      :- " + Short.reverseBytes(raf.readShort()));

// The next 24 bytes are Fixed Transaction Header

System.out.println("Transaction Header start  :- " + BlockHeaderSize);
System.out.println(" Type 01-DATA 02-INDEX       :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare 1                     :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare 2                     :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare 3                     :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Object ID                   :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" Cleanout SCN Base           :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" Cleanout SCN Wrap           :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Spare 4                     :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Spare 5                     :- " + String.format("%02X ", raf.readByte()));
System.out.println(" ITL Slots                   :- " + raf.readByte());
System.out.println(" UNKNOWN Byte                :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Flag 00-FREE                :- " + String.format("%02X ", raf.readByte()));
System.out.println(" ITL TX Feeelist Slot        :- " + String.format("%02X ", raf.readByte()));
System.out.println(" Next Block On Free List     :- " + Integer.reverseBytes(raf.readInt()));

// Each ITL Slot has 24 bytes allocated.  

for(ITLSlot = 1; ITLSlot <= ITLSlots; ITLSlot++) 
{
System.out.println("ITLSlot                     :- " + ITLSlot);
System.out.println(" Undo Segment               :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Undo Segment Slot          :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Transaction Sequence       :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" Undo Block Address         :- " + Integer.reverseBytes(raf.readInt()));
System.out.println(" Undo Sequence              :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Undo Record Number         :- " + raf.readByte());
System.out.println(" Spare 1                    :- " + raf.readByte());
System.out.println(" Flag                       :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" _ktbitun                   :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Base                       :- " + Integer.reverseBytes(raf.readInt()));
}

DataHeaderStart = BlockHeaderSize + TransactionHeaderSize + ITLSlots*TransactionHeaderSize + 8; 
raf.seek(StartByte + DataHeaderStart);

// The Data header contains details of the number of tables, rows, and free space in the block.

System.out.println("Data Header start        :- " + DataHeaderStart);
System.out.println(" Flags                       :- " + String.format("%02X ", raf.readByte()));
NumTables = raf.readByte(); 
System.out.println(" Number of Tables            :- " + NumTables);
System.out.println(" Number of Rows              :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Offset to Freespace Start   :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Offset to Freespace End     :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Available Space             :- " + Short.reverseBytes(raf.readShort()));
System.out.println(" Available Space after Commit:- " + Short.reverseBytes(raf.readShort()));

TableDirectoryStart = DataHeaderStart + DataHeaderSize;
raf.seek(StartByte + TableDirectoryStart);

// The Table Directory has a list of tables in the block and row counts

System.out.println("Table Directory start    :-" + TableDirectoryStart);
TotalTableRows = 0;

for(TableNum = 1; TableNum <= NumTables; TableNum++) 
{
System.out.println(" Table                       :- " + TableNum);
System.out.println("  Offset                      :- " + Short.reverseBytes(raf.readShort()));
NumTableRows = Short.reverseBytes(raf.readShort());
TotalTableRows = TotalTableRows + NumTableRows;
System.out.println("  Number of Rows              :- " + NumTableRows);
}

System.out.println(" Total Number of Rows        :- " + TotalTableRows);

RowDirectoryStart = TableDirectoryStart + NumTables*TableDirectorySize;
raf.seek(StartByte + RowDirectoryStart);

// The Row Directory contains a list of rows in the block, with offsets to the first byte

System.out.println("Row Directory start      :-" + RowDirectoryStart);

MinOffset = TotalTableRows*2;
MaxOffset = BlockSize - DataHeaderStart - 4;
RowOffset = 1;
OffsetCount = 0;

List RowOffsetList = new ArrayList();
while (RowOffset != 0 && OffsetCount < TotalTableRows) 
{
RowOffset = Short.reverseBytes(raf.readShort());
if (RowOffset > MinOffset && RowOffset < MaxOffset) 
{
OffsetCount = OffsetCount + 1;
System.out.println(" Row Offset " + OffsetCount + " :- " + RowOffset + "(+" + DataHeaderStart + ")");
RowOffsetList.add(RowOffset);
}
}

// Now go to each offset and get the row header which contains number of columns, row status etc

Iterator OffsetIterator = RowOffsetList.iterator();
RowNumber = 0;
while(OffsetIterator.hasNext())
{
RowOffset = (Short)OffsetIterator.next();
RowNumber = RowNumber + 1;
RowStart = DataHeaderStart + RowOffset;
raf.seek(StartByte + RowStart);
System.out.println(RowNumber + " Row Header start  :-" + RowStart);

RowFlag = raf.readByte();
String RowFlagString = String.format("%02X ", RowFlag );
String RowFlagDecode = "";

// Translate the Row Flag to identify deleted rows, table data etc

switch (RowFlag) {
case 3:  RowFlagDecode = "Junk ?";
                    break;
 case 12:  RowFlagDecode = "Chained ?";
                    break;
case 32:  RowFlagDecode = "Chained ?";
                    break;
case 44:  RowFlagDecode = "Table Data";
                    break;
 case 60:  RowFlagDecode = "Deleted";
                     break;
case 84:  RowFlagDecode = "Cluster Key ?";
                    break;
case -84:  RowFlagDecode = "Cluster Key ?";
                    break;
case 108:  RowFlagDecode = "Cluster Data";
                     break;
            case 124:  RowFlagDecode = "Deleted";
                     break;
default: RowFlagDecode = "Invalid Flag";
                     break;
}

System.out.println(" Flags              :-  " + RowFlagString + " " + RowFlagDecode);
System.out.println(" Lock Status        :- " + String.format("%02X ", raf.readByte()));

NumColumns = raf.readByte();
System.out.println(" Number of Columns  :- " + NumColumns);
if (NumColumns == 0)
{
System.out.println("Integer         :- " + Integer.reverseBytes(raf.readInt()));
System.out.println("Short           :- " + Short.reverseBytes(raf.readShort()));
}

if (NumTables > 1) 
{
System.out.println(" Cluster Byte       :- " + String.format("%02X ", raf.readByte()));
}
if (RowFlag == 76) 
{
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
}
if (RowFlag == 12) 
{
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
//System.out.println("Read Byte :- " + String.format("%02X ", raf.readByte()));
System.out.println("Integer         :- " + Integer.reverseBytes(raf.readInt()));
System.out.println("Short           :- " + Short.reverseBytes(raf.readShort()));
}

if (RowFlag != 3) {

for(ColNum = 1; ColNum <= NumColumns; ColNum++){
ColumnSize = raf.read();


if (ColumnSize == 255) 
{
System.out.print("Column " + ColNum + " Bytes " + ColumnSize + " Data :- ");
System.out.println("NULL");
}
else if (ColumnSize == 254)
{
ColumnSize = Short.reverseBytes(raf.readShort());
System.out.print("Column " + ColNum + " Bytes " + ColumnSize + " Data :- ");
for(ByteNum = 0; ByteNum < ColumnSize; ByteNum++) 
{

//System.out.print("" + String.format("%02X ", MyByte));
//MyByte = raf.readByte();
System.out.print("" + (char)raf.readByte());
}
System.out.println("");
}
else 
{

System.out.print("Column " + ColNum + " Bytes " + ColumnSize + " Data :- ");
for(ByteNum = 0; ByteNum < ColumnSize; ByteNum++) 
{

MyByte = raf.readByte();

//System.out.print("" + raf.readByte());


if (MyByte < 32 || MyByte > 126)
{
System.out.print("" + String.format("%02X ", MyByte));
}
else
{
 MyChar = ((char)Integer.parseInt(String.format("%02X", MyByte), 16));
   System.out.print("" + MyChar);

}
}
System.out.println("");

}
}
}


}
}

raf.close();

      } catch (IOException ex) {
         ex.printStackTrace();
      }
}
}
Categories: DBA Blogs

Extracting data directly from Oracle datafiles using Java.

Mon, 2013-02-25 02:26
I wrote a Java program today to scan Oracle datafiles for specific objects and then extract the data.  It first extracts the Block Header, Transaction Header, Data Header, Table Directory, Row Directory, Row Header, and finally the Row Data.

This allows me to extract any data I need direct from datafiles while bypassing audit and security at database level.  Following is example output running the program to scan for object 10 which is a clustered table containing USER$ and extracting data including usernames and password hashes.
Being able to directly access data in blocks allows more detailed analysis and auditing, so I should be able to  extract data for tables that have been truncated, extract deleted rows, or identify which blocks have changed since a specific SCN.

The source code can be found here :- http://blog.contractoracle.com/2013/02/java-program-to-extract-data-from.html
The output still contains a bit of Hex data, so more work is needed to convert to proper char and date formats.
Block 209 Contains Object ID 10Block Header start       :- 0 Block Type 06-DATA          :- 06  Block Format                :- A2  Spare1                      :- 00  Spare2                      :- 00  Relative Block Address      :- 4194513 SCN Base                    :- 954865 SCN Wrap                    :- 0 Sequence                    :- 01  Flag 01-NEW                 :- 06  CheckSum                    :- 10511 Spare3                      :- 0
Transaction Header start  :- 20 Type 01-DATA 02-INDEX       :- 01  Spare 1                     :- 00  Spare 2                     :- 00  Spare 3                     :- 00  Object ID                   :- 10 Cleanout SCN Base           :- 954861 Cleanout SCN Wrap           :- 0 Spare 4                     :- 00  Spare 5                     :- 00  ITL Slots                   :- 2 UNKNOWN Byte                :- 00  Flag 00-FREE                :- 03  ITL TX Feeelist Slot        :- 00  Next Block On Free List     :- 0
Data Header start        :- 92 Flags                       :- 00  Number of Tables            :- 2 Number of Rows              :- 43 Offset to Freespace Start   :- -1 Offset to Freespace End     :- 108 Available Space             :- 4711 Available Space after Commit:- 5519
Table Directory start    :-106 Table                       :- 1  Offset                      :- 0  Number of Rows              :- 21 Table                       :- 2  Offset                      :- 21  Number of Rows              :- 22 Total Number of Rows        :- 43
Row Directory start      :-114 Row Offset 1 :- 8074(92) Row Offset 2 :- 8005(92) Row Offset 3 :- 7829(92) Row Offset 4 :- 7740(92) Row Offset 5 :- 7650(92) Row Offset 6 :- 7559(92) Row Offset 7 :- 7473(92) Row Offset 8 :- 7294(92) Row Offset 9 :- 7192(92) Row Offset 10 :- 7089(92) Row Offset 11 :- 6987(92) Row Offset 12 :- 6809(92) Row Offset 13 :- 6709(92) Row Offset 14 :- 6609(92) Row Offset 15 :- 6504(92) Row Offset 16 :- 6412(92) Row Offset 17 :- 6220(92) Row Offset 18 :- 6116(92) Row Offset 19 :- 6021(92) Row Offset 20 :- 5842(92) Row Offset 21 :- 5733(92) Row Offset 22 :- 5951(92) Row Offset 23 :- 4882(92) Row Offset 24 :- 7762(92) Row Offset 25 :- 7672(92) Row Offset 26 :- 7581(92) Row Offset 27 :- 7495(92) Row Offset 28 :- 4711(92) Row Offset 29 :- 7214(92) Row Offset 30 :- 7111(92) Row Offset 31 :- 7009(92) Row Offset 32 :- 5391(92) Row Offset 33 :- 6731(92) Row Offset 34 :- 6631(92) Row Offset 35 :- 6526(92) Row Offset 36 :- 6434(92) Row Offset 37 :- 6242(92) Row Offset 38 :- 6138(92) Row Offset 39 :- 6043(92) Row Offset 40 :- 5864(92) Row Offset 41 :- 5755(92) Row Offset 42 :- 5647(92) Row Offset 43 :- 5562(92)
23 Row Header start  :-4974 Flags :- 108 6C  Cluster Data Lock Status :- 00  Number of Columns :- 22 Cluster Byte :- 01 Column 1 Bytes 3 Data :- SYSColumn 2 Bytes 2 Data :- C1 02 Column 3 Bytes 16 Data :- DCB748A5BC5390F2Column 4 Bytes 1 Data :- 80 Column 5 Bytes 2 Data :- C1 04 Column 6 Bytes 7 Data :- xn03 1E 0B 08 7Column 7 Bytes 7 Data :- xq02 13 0B 3'Column 8 Bytes 7 Data :- xn03 1E 0C 07 9Column 9 Bytes 7 Data :- xn03 1E 0C 07 9Column 10 Bytes 1 Data :- 80 Column 11 Bytes 255 Data :- NULLColumn 12 Bytes 2 Data :- C1 02 Column 13 Bytes 255 Data :- NULLColumn 14 Bytes 255 Data :- NULLColumn 15 Bytes 1 Data :- 80 Column 16 Bytes 1 Data :- 80 Column 17 Bytes 22 Data :- DEFAULT_CONSUMER_GROUPColumn 18 Bytes 255 Data :- NULLColumn 19 Bytes 1 Data :- 80 Column 20 Bytes 255 Data :- NULLColumn 21 Bytes 255 Data :- NULLColumn 22 Bytes 62 Data :-S:CDD630F4165A338BF851D4552897EB41EB6A9FCF7587B366260E0352A7C7

28 Row Header start  :-4803 Flags :- 108 6C  Cluster Data Lock Status :- 02  Number of Columns :- 22 Cluster Byte :- 05 Column 1 Bytes 6 Data :- SYSTEMColumn 2 Bytes 2 Data :- C1 02 Column 3 Bytes 16 Data :- EED9B65CCECDB2E9Column 4 Bytes 1 Data :- 80 Column 5 Bytes 2 Data :- C1 04 Column 6 Bytes 7 Data :- xn03 1E 0B 08 8Column 7 Bytes 7 Data :- xq02 13 0B 3'Column 8 Bytes 7 Data :- xn03 1E 0C 07 9Column 9 Bytes 7 Data :- xn03 1E 0C 07 9Column 10 Bytes 1 Data :- 80 Column 11 Bytes 255 Data :- NULLColumn 12 Bytes 2 Data :- C1 02 Column 13 Bytes 255 Data :- NULLColumn 14 Bytes 255 Data :- NULLColumn 15 Bytes 1 Data :- 80 Column 16 Bytes 1 Data :- 80 Column 17 Bytes 22 Data :- DEFAULT_CONSUMER_GROUPColumn 18 Bytes 255 Data :- NULLColumn 19 Bytes 1 Data :- 80 Column 20 Bytes 255 Data :- NULLColumn 21 Bytes 255 Data :- NULLColumn 22 Bytes 62 Data :- S:83FDD7C19ABAB64415514C28AD798DDAA3FE619B10B1AEA79E8463DC2566
If anyone wants more details on the Oracle block structure you can check :-http://orafaq.com/papers/dissassembling_the_data_block.pdfand http://www.v3rity.com/OracleForensicsDataBlock.pdf
Categories: DBA Blogs

Oracle Control File Physical Structure

Mon, 2013-02-25 01:26
I did a bit of investigation of the Oracle Control File structure today using a Hex editor and Java program to extract data.  Following are my observations, based on a few 11g databases on 64 bit Windows and Linux.  Format may be different on other OS and database versions.

The Java program I wrote to extract the data below can be found here :- http://blog.contractoracle.com/2013/02/java-program-to-extract-records-from.html

The basics :-
  • The controlfile is composed of blocks that are 2X database block size.  
  • Each block has a header of 18 bytes.
  • Each block has a tail of 4 bytes.
  • Each block is duplexed within the datafile for redundancy.  E.g Block 31 is duplexed to Block 32.
The database name is stored in block 2 starting at byte 32.
Number of Blocks                 :- 595Block                            :- 2Tablespace Name                  :- WIN64
File information starts in block 31 and is contained in records of 524 bytes.The first 12 bytes is record header information, and the last 512 bytes is the file name.  Byte 3 indicates the file type (3=LOG, 4=DATA, 7=TEMPFILE) and byte 5 appears to be file ID (unique for each file type)
Following is an example of extracting file details from a Control File :-
Number of Blocks                 :- 595Block                            :- 31Record                           :- 1Byte 1                           :- 00 Byte 2                           :- 00 File Type 3-LOG, 4-DATA, 7-TEMP  :- 03 Byte 4                           :- 00 File ID                          :- 3Byte 6                           :- 00 Byte 7                           :- 00 Byte 8                           :- 00 Byte 9                           :- 00 Byte 10                          :- 00 Byte 11                          :- 00 Byte 12                          :- 00 File Name :- D:\ORACLE\WIN64\ONLINELOG\O1_MF_3_8L5T3995_.LOG
Record                           :- 2Byte 1                           :- 00 Byte 2                           :- 00 File Type 3-LOG, 4-DATA, 7-TEMP  :- 03 Byte 4                           :- 00 File ID                          :- 2Byte 6                           :- 00 Byte 7                           :- 00 Byte 8                           :- 00 Byte 9                           :- 00 Byte 10                          :- 00 Byte 11                          :- 00 Byte 12                          :- 00 File Name :- D:\ORACLE\WIN64\ONLINELOG\O1_MF_2_8L5T38G3_.LOG
Record                           :- 3Byte 1                           :- 00 Byte 2                           :- 00 File Type 3-LOG, 4-DATA, 7-TEMP  :- 03 Byte 4                           :- 00 File ID                          :- 1Byte 6                           :- 00 Byte 7                           :- 00 Byte 8                           :- 00 Byte 9                           :- 00 Byte 10                          :- 00 Byte 11                          :- 00 Byte 12                          :- 00 File Name :- D:\ORACLE\WIN64\ONLINELOG\O1_MF_1_8L5T37VY_.LOG
Record                           :- 4Byte 1                           :- 00 Byte 2                           :- 00 File Type 3-LOG, 4-DATA, 7-TEMP  :- 04 Byte 4                           :- 00 File ID                          :- 4Byte 6                           :- 00 Byte 7                           :- 00 Byte 8                           :- 00 Byte 9                           :- 00 Byte 10                          :- 00 Byte 11                          :- 00 Byte 12                          :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_USERS_8L5T0ZC4_.DBF
Record                           :- 5Byte 1                           :- 00 Byte 2                           :- 00 File Type 3-LOG, 4-DATA, 7-TEMP  :- 04 Byte 4                           :- 00 File ID                          :- 3Byte 6                           :- 00 Byte 7                           :- 00 Byte 8                           :- 00 Byte 9                           :- 00 Byte 10                          :- 00 Byte 11                          :- 00 Byte 12                          :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_UNDOTBS1_8L5T0ZBF_.DBF
Record                           :- 6Byte 1                           :- 00 Byte 2                           :- 00 File Type 3-LOG, 4-DATA, 7-TEMP  :- 04 Byte 4                           :- 00 File ID                          :- 2Byte 6                           :- 00 Byte 7                           :- 00 Byte 8                           :- 00 Byte 9                           :- 00 Byte 10                          :- 00 Byte 11                          :- 00 Byte 12                          :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_SYSAUX_8L5T0ZB4_.DBF
Record                           :- 7Byte 1                           :- 00 Byte 2                           :- 00 File Type 3-LOG, 4-DATA, 7-TEMP  :- 04 Byte 4                           :- 00 File ID                          :- 1Byte 6                           :- 00 Byte 7                           :- 00 Byte 8                           :- 00 Byte 9                           :- 00 Byte 10                          :- 00 Byte 11                          :- 00 Byte 12                          :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_SYSTEM_8L5T0Z83_.DBF
Record                           :- 8Byte 1                           :- 00 Byte 2                           :- 00 File Type 3-LOG, 4-DATA, 7-TEMP  :- 07 Byte 4                           :- 00 File ID                          :- 1Byte 6                           :- 00 Byte 7                           :- 00 Byte 8                           :- 00 Byte 9                           :- 00 Byte 10                          :- 00 Byte 11                          :- 00 Byte 12                          :- 00 File Name :- D:\ORACLE\WIN64\DATAFILE\O1_MF_TEMP_8L5T3MYY_.TMP

Tablespace information starts at block 179 and is contained in 68 byte records.  The first 8 bytes of the record are header information, with byte 3 being TABLESPACE#. 
Following is an example of tablespace information extract from a Control File.
Block                            :- 179Record                           :- 1Byte 1                           :- 00 Byte 2                           :- 00 Tablespace ID                    :- 00 Byte 4                           :- 00 Byte 5                           :- 00 Byte 6                           :- 00 Byte 7                           :- 06 Byte 8                           :- 00 Tablespace Name :- SYSTEM
Record                           :- 2Byte 1                           :- 00 Byte 2                           :- 00 Tablespace ID                    :- 01 Byte 4                           :- 00 Byte 5                           :- 00 Byte 6                           :- 00 Byte 7                           :- 06 Byte 8                           :- 00 Tablespace Name :- SYSAUX
Record                           :- 3Byte 1                           :- 00 Byte 2                           :- 00 Tablespace ID                    :- 02 Byte 4                           :- 00 Byte 5                           :- 00 Byte 6                           :- 00 Byte 7                           :- 08 Byte 8                           :- 00 Tablespace Name :- UNDOTBS1
Record                           :- 4Byte 1                           :- 00 Byte 2                           :- 00 Tablespace ID                    :- 04 Byte 4                           :- 00 Byte 5                           :- 00 Byte 6                           :- 00 Byte 7                           :- 05 Byte 8                           :- 00 Tablespace Name :- USERS
Record                           :- 5Byte 1                           :- 00 Byte 2                           :- 00 Tablespace ID                    :- 03 Byte 4                           :- 00 Byte 5                           :- 00 Byte 6                           :- 00 Byte 7                           :- 04 Byte 8                           :- 00 Tablespace Name :- TEMP
Categories: DBA Blogs

Using ODU to extract audit data from truncated AUD$ table.

Mon, 2013-02-18 02:11
The following example shows the use of the ODU (Oracle Database Unloader) to extract records from tables that have been truncated.  In this example I will asume someone has hacked into a database and then truncated the AUD$ table in an attempt to hide their activities.

### Evil DBA logs into the HR database, increases his salary, changes the password for SYS user, then logs into SYS and truncates the AUD$ table.


SQL> connect evil_dba/badpassword
Connected.
SQL> update hr.employees set salary = 100000.00 where first_name = 'EVIL' and last_name = 'DBA';

1 row updated.

SQL> alter user sys identified by badpassword;

User altered.

SQL> connect sys/badpassword as sysdba;
Connected.
SQL> select count(*) from dba_audit_trail;

  COUNT(*)
----------
        40

SQL> truncate table aud$;

Table truncated.

SQL> select count(*) from dba_audit_trail;

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

SQL> exit


It is reported to the auditor that someone has hacked into the HR database, but no audit records can be found in AUD$ to identify the guilty person and nobody is sure of exactly what was changed.  The Auditor could restore the database and recover it until the SCN prior to the truncate, but that may take a long time, so it would usually be faster to read data from the truncated table blocks if they are still in the datafile and have not been overwritten.

1.  Configure control.txt so ODU has a list of datafiles
[oracle@rac1 odu]$ more control.txt
0          1        1 +DATA/test/datafile/system.266.807721615
1          2        2 +DATA/test/datafile/sysaux.273.807721615
2          3        3 +DATA/test/datafile/undotbs1.272.807721615
4          4        4 +DATA/test/datafile/users.271.807721615
6          5        5 +DATA/test/datafile/example.262.807721709

2. Configure asmdisk.txt so ODU has a list of ASM devices
[oracle@rac1 odu]$ more asmdisk.txt
0 /dev/sdb1 DATA1  4096 1048576
0 /dev/sdc1 FRA1   4096 1048576

3. Start ODU
[oracle@rac1 odu]$ ./odu

Oracle Data Unloader trial version 4.1.3

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
block_buffers 1024
error at line 3.
db_timezone -7
Invalid db timezone:-7
client_timezone 8
Invalid client timezone:8
asmfile_extract_path   /asmfile
data_path   data
lob_path    /odu/data/lob
charset_name US7ASII
charset name 'US7ASII' not found,will use default charset ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order big
trace_level 1
delimiter |
unload_deleted no
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 20000
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 ......


grp# dsk# bsize ausize disksize diskname        groupname       path
---- ---- ----- ------ -------- --------------- --------------- -----------------
   1    0  4096  1024K    10236 DATA1           DATA            /dev/sdb1
   2    0  4096  1024K     5114 FRA1            FRA             /dev/sdc1

load asm disk file 'asmdisk.txt' successful
loading default control file ......


 ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ -----------------------------------------
   0    1    1  8192    85760 N     0 +DATA/test/datafile/system.266.807721615
   1    2    2  8192    60160 N     0 +DATA/test/datafile/sysaux.273.807721615
   2    3    3  8192     5120 N     0 +DATA/test/datafile/undotbs1.272.807721615
   4    4    4  8192      640 N     0 +DATA/test/datafile/users.271.807721615
   6    5    5  8192    12800 N     0 +DATA/test/datafile/example.262.807721709
load control file 'control.txt' successful
loading dictionary data......done

loading scanned data......done


4. Unload the dictionary
ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 208
TABLE OBJ$ 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


5. Check that the SYS.AUD$ table exists and record the TS# and OBJ#
ODU> desc sys.aud$


Object ID:383
Storage(Obj#=383 DataObj#=74574 TS#=0 File#=1 Block#=2296 Cluster=0)

NO. SEG INT Column Name                    Null?     Type                       
--- --- --- ------------------------------ --------- ------------
  1   1   1 SESSIONID                      NOT NULL NUMBER                     
  2   2   2 ENTRYID                        NOT NULL NUMBER                     
  3   3   3 STATEMENT                      NOT NULL NUMBER                     
  4   4   4 TIMESTAMP#                              DATE                       
  5   5   5 USERID                                  VARCHAR2(30)               
  6   6   6 USERHOST                                VARCHAR2(128)              
  7   7   7 TERMINAL                                VARCHAR2(255)              
  8   8   8 ACTION#                        NOT NULL NUMBER                     
  9   9   9 RETURNCODE                     NOT NULL NUMBER                     
 10  10  10 OBJ$CREATOR                             VARCHAR2(30)               
 11  11  11 OBJ$NAME                                VARCHAR2(128)              
 12  12  12 AUTH$PRIVILEGES                         VARCHAR2(16)               
 13  13  13 AUTH$GRANTEE                            VARCHAR2(30)               
 14  14  14 NEW$OWNER                               VARCHAR2(30)               
 15  15  15 NEW$NAME                                VARCHAR2(128)              
 16  16  16 SES$ACTIONS                             VARCHAR2(19)               
 17  17  17 SES$TID                                 NUMBER                     
 18  18  18 LOGOFF$LREAD                            NUMBER                     
 19  19  19 LOGOFF$PREAD                            NUMBER                     
 20  20  20 LOGOFF$LWRITE                           NUMBER                     
 21  21  21 LOGOFF$DEAD                             NUMBER                     
 22  22  22 LOGOFF$TIME                             DATE                       
 23  23  23 COMMENT$TEXT                           VARCHAR2(4000)             
 24  24  24 CLIENTID                                VARCHAR2(64)               
 25  25  25 SPARE1                                  VARCHAR2(255)              
 26  26  26 SPARE2                                  NUMBER                     
 27  27  27 OBJ$LABEL                               RAW(255)                   
 28  28  28 SES$LABEL                               RAW(255)                   
 29  29  29 PRIV$USED                               NUMBER                     
 30  30  30 SESSIONCPU                              NUMBER                     
 31  31  31 NTIMESTAMP#                             TIMESTAMP(6)               
 32  32  32 PROXY$SID                               NUMBER                     
 33  33  33 USER$GUID                               VARCHAR2(32)               
 34  34  34 INSTANCE#                               NUMBER                     
 35  35  35 PROCESS#                                VARCHAR2(16)               
 36  36  36 XID                                     RAW(8)                     
 37  37  37 AUDITID                                 VARCHAR2(64)               
 38  38  38 SCN                                     NUMBER                     
 39  39  39 DBID                                    NUMBER                     
 40  40  40 SQLBIND                                 CLOB                       
 41  41  41 SQLTEXT                                 CLOB                       
 42  42  42 OBJ$EDITION                             VARCHAR2(30)            


6.  Scan tablespace 0 for all extents associated with object 383
ODU> scan extent tablespace 0 object 383

scan extent start: 2013-02-18 15:32:18
scanning extent...
scanning extent finished.
scan extent completed: 2013-02-18 15:32:23


7.  Unload records from the truncated table extents
ODU> unload table sys.aud$ object truncate
Auto mode truncated table.

Unloading table: AUD$,object ID: 383
Unloading segment,storage(Obj#=383 DataObj#=383 TS#=0 File#=1 Block#=2296 Cluster=0)
40 rows unloaded

The 40 records from truncated table AUD$ have now been extracted to file SYS_AUD$.txt and if we look at them we can see the last activity before the truncate.  These records could be loaded into temporary tables using sqlldr.

20027|1|1||EVIL_DBA|rac1.test.com|pts/1|100|0||||||||||||||Authenticated by: DATABASE||oracle||||5||2013-02-18 07:22:50.091234|||0|24432|0000000000000000|||2105868124
20027|2|10||EVIL_DBA|rac1.test.com|pts/1|103|0|HR|EMPLOYEES|||||----------S-----|73953||||||||oracle||||49||2013-02-18 07:22:59.535459|||0|24432|0300150027030000||990832|2105868124||®<90><93>©¬<8f>
20027|3|11||EVIL_DBA|rac1.test.com|pts/1|43|0||SYS||||||||||||||oracle||||22||2013-02-18 07:23:25.381809|||0|24432|040014005B020000||990849|2105868124||<91>[¬ <84>®<92>ÃÃ
20027|4|1||EVIL_DBA|rac1.test.com|pts/1|101|0|||||||||1987|121|43|0|2013-02-18 07:23:37|||oracle|||||22|2013-02-18 07:23:37.142216|||0|24432||||2105868124

The records above relate to the actions of EVIL_DBA and the ACTION# can give a clue to their activities.  
    ACTION ACTION_NAME---------- ----------------------------       100 LOGON       103 SESSION REC        43 ALTER USER       101 LOGOFF
So just from the text file we can see the EVIL_DBA connected to the database, did something to the HR.EMPLOYEES table, altered user SYS, then logged out.  The extracted data contains additional details which can be mapped to other database tables (USER$, OBJ$ etc) to give a more comprehensive picture.  There is also LOB data which can be loaded which contains the actual commands executed.

In this example ODU extracted audit data directly from truncated table blocks on ASM devices without logging in to the database, so this could also work if the database was shutdown, or corrupted.
Categories: DBA Blogs

Oracle database on ZFS - reduce costs through deduplication

Thu, 2013-02-14 03:09

I recently had a customer who wanted to reduce storage costs.  They had a 13TB PROD database, and needed 40 copies of the database to support DEV and TEST activities, totalling over 500TB.

We did some diagnostics on the databases, and worked out that only about 10% of data blocks in a clone were ever updated, so deduplicating at filesystem or storage block level could result in 90% saving.  The client was a large conservative bank, so the preference was to use existing products from large vendors.  We evaluated products from Delphix, NetApp, and Oracle, and ended up choosing the NetApp product due to a combination of functionality, cost, and in-house skills.

Since then I have been wondering if we could have done it cheaper, and have done a quick evaluation of the ZFS filesystem which does allow deduplication at filesystem level.  

ZFS is a "copy on write" filesystem which is included in the Solaris operating system, and has been ported to other operating systems.

You can find more information here :-  http://docs.oracle.com/cd/E19253-01/819-5461/index.html

The following test was done using Oracle VirtualBox running Oracle Solaris 11 x86.

Create the "dbpool" disk pool using a storage device by running  "zpool create"

root@sol11:~# zpool create dbpool /dev/dsk/c7t2d0p0

Configure dbpool with recordsize 8k which is optimal for databases with 8k block size, and set mount point to /u01/data  

Refer to - http://www.oracle.com/technetwork/server-storage/solaris10/config-solaris-zfs-wp-167894.pdf

root@sol11:~# zfs create -o recordsize=8k -o mountpoint=/u01/data dbpool/data
root@sol11:~# zfs set logbias=throughput dbpool/data 

Now confirm the settings using "zfs get"

root@sol11:~# zfs get primarycache,recordsize,logbias dbpool/data
NAME         PROPERTY      VALUE       SOURCE
dbpool/data  primarycache  all         default
dbpool/data  recordsize    8K          local
dbpool/data  logbias       throughput  local

Then create 11g database "test" in /u01/data

oracle@sol11:/u01/data/test$ pwd/u01/data/testoracle@sol11:/u01/data/test$ ls -l
total 2720998
-rw-r----- 1 oracle oinstall 9748480 Feb 14 23:55 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Feb 14 23:55 control02.ctl
-rw-r----- 1 oracle oinstall 52429312 Feb 14 23:25 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Feb 14 23:52 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Feb 14 23:25 redo03.log
-rw-r----- 1 oracle oinstall 461381632 Feb 14 23:46 sysaux01.dbf
-rw-r----- 1 oracle oinstall 702554112 Feb 14 23:51 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Feb 14 23:25 temp01.dbf
-rw-r----- 1 oracle oinstall 31465472 Feb 14 23:51 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Feb 14 23:25 users01.dbf

oracle@sol11:/u01/data/test$ du -k .1360500 .

oracle@sol11:/u01/data/test$ df -k .
Filesystem   1024-blocks   Used   Available Capacity  Mounted on
dbpool/data   10257408     1360529     8896686    14% /u01/data

We can see that database test is using about 1.3GB of disk in /u01/data

The "zfs list" command confirms space used is 1.3GB.

oracle@sol11:~$ zfs list
NAME                    USED  AVAIL  REFER  MOUNTPOINT
dbpool                  1.30G  8.48G    31K  /dbpool
dbpool/data             1.30G  8.48G  1.30G  /u01/data

Shutdown database test and run the "zfs snapshot" command to create a snapshot of the dbpool filesystem data called "backup"

root@sol11:/u01/data# zfs snapshot dbpool/data@backup

The contents of the snapshot can be viewed in the ".zfs" directory.

root@sol11:/u01/data/.zfs# find .
.
./snapshot
./snapshot/backup
./snapshot/backup/test
./snapshot/backup/test/redo02.log
./snapshot/backup/test/control02.ctl
./snapshot/backup/test/redo03.log
./snapshot/backup/test/sysaux01.dbf
./snapshot/backup/test/temp01.dbf
./snapshot/backup/test/system01.dbf
./snapshot/backup/test/control01.ctl
./snapshot/backup/test/users01.dbf
./snapshot/backup/test/redo01.log
./snapshot/backup/test/undotbs01.dbf
./shares


Clone the backup by running "zfs clone" to create "clone1"

root@sol11:/u01/data# zfs clone dbpool/data@backup dbpool/clone1

Mount clone1 to /u01/clone1 using "zfs set mountpoint"

root@sol11:/u01/data# zfs set mountpoint=/u01/clone1 dbpool/clone1
root@sol11:/u01/data/.zfs# cd /u01/clone1/test
root@sol11:/u01/clone1/test# ls
control01.ctl  redo01.log     redo03.log     system01.dbf   undotbs01.dbf  control02.ctl  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf


Using "zfs list" we can see that the clone only uses 21K (for 1.3GB of files)

root@sol11:/u01/clone1/test# zfs list
NAME          USED   AVAIL REFER  MOUNTPOINT
dbpool        1.30G  8.48G   31K  /dbpool
dbpool/data   1.30G  8.48G 1.30G  /u01/data
dbpool/clone1   21K  8.48G 1.30G  /u01/clone1


Now we can prepare initclone1.ora, create audit file directory, and recreate the controlfile for the clone database.

oracle@sol11:~$ export ORACLE_SID=clone1
oracle@sol11:~$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 15 00:35:13 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> @recrctl_clone1.sql

ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2217624 bytes
Variable Size             603982184 bytes
Database Buffers          432013312 bytes
Redo Buffers                5672960 bytes
Control file created.

Database altered.
SQL> select name from v$database;

NAME
---------
CLONE1
SQL> select name from v$datafile;

NAME
-----------------------------------------------------------------
/u01/clone1/test/system01.dbf
/u01/clone1/test/sysaux01.dbf
/u01/clone1/test/undotbs01.dbf
/u01/clone1/test/users01.dbf

We now have databases test and clone1 running, with block level deduplication.

oracle@sol11:~$ ps -ef | grep pmon  
oracle  2800     1   0 00:33:28 ?           0:00 ora_pmon_test  oracle  2920     1   0 00:35:18 ?           0:00 ora_pmon_clone1

After the "open resetlogs" the clone is now using 120MB (for an additional 1300MB database)

oracle@sol11:~$ zfs list
NAME             USED  AVAIL  REFER  MOUNTPOINT
dbpool          1.42G  8.36G    31K  /dbpool
dbpool/data     1.30G  8.36G  1.30G  /u01/data
dbpool/clone1    120M  8.36G  1.30G  /u01/clone1

oracle@sol11:~$ df -k /u01/clone1
Filesystem  1024-blocks    Used Available Capacity  Mounted on
dbpool/clone1 10257408     1361036  8770425    14%    /u01/clone1
oracle@sol11:~$ df -k /u01/data
Filesystem   1024-blocks   Used   Available Capacity  Mounted on
dbpool/data  10257408      1360530  8770425    14%    /u01/data



Categories: DBA Blogs

Oracle database on BTRFS - reduce costs through deduplication

Wed, 2013-02-13 22:05
I recently had a customer who wanted to reduce storage costs.  They had a 13TB PROD database, and needed 40 copies of the database to support DEV and TEST activities, totalling over 500TB.

We did some diagnostics on the databases, and worked out that only about 10% of data blocks in a clone were ever updated, so deduplicating at filesystem or storage block level could result in 90% saving.  The client was a large conservative bank, so the preference was to use existing products from large vendors.  We evaluated products from Delphix, NetApp SnapManager for Oracle, and Oracle ZFS appliance, and ended up choosing the NetApp product due to a combination of functionality, cost, and in-house skills.
Since then I have been wondering if we could have done it cheaper, and have done a quick evaluation of the BTRFS filesystem which does allow deduplication at filesystem level.  
BTRFS is a "copy on write" filesystem and is listed as EXPERIMENTAL, so I don't think it is ready to use in a PROD environment, but is worth testing to support DEV and TEST environments depending on the cost of downtime.
You can find more information here :-  http://btrfs.wiki.kernel.org
The following test was done using Oracle VirtualBox running Oracle Linux 6 2.6.39-300.28.1.el6uek.x86_64.  BTRFS is included in the Linux kernel, but I needed to install package btrfs-progs :-
yum install btrfs-progs
Format the storage device using mkfs.btrfs :-
[root@btrfs ~]# mkfs.btrfs /dev/sdb

WARNING! - Btrfs Btrfs v0.19 IS EXPERIMENTAL
WARNING! - see http://btrfs.wiki.kernel.org before using

fs created label (null) on /dev/sdb
nodesize 4096 leafsize 4096 sectorsize 4096 size 12.00GB
Btrfs Btrfs v0.19

You can see that we formatted a 12GB device with devault 4K block size.  Block size can impact performance, so if the BTRFS filesystem will only contain database files with 8K blocks, I suspect you would see better performance if the filesystem was also formatted to use 8K blocks.
Now mount the filesystem :-
[root@btrfs ~]# mount /dev/sdb /btrfs1
And add an entry to /etc/fstab :-
/dev/sdb          /btrfs1     btrfs   defaults        0 0
Using the "btrfs filesystem show" and "btrfs filesystem df" commands we can see the usage.  Note that the filesystem has reserved 2GB out of 12GB.
[root@btrfs /]# btrfs filesystem show /dev/sdbLabel: none  uuid: f6f7d6c7-14ca-4c01-8c33-3066fc7f4ad3        Total devices 1 FS bytes used 92.00KB        devid    1 size 12.00GB used 2.04GB path /dev/sdb
Btrfs Btrfs v0.19
[root@btrfs /]# btrfs filesystem df /btrfs1Data: total=8.00MB, used=64.00KBSystem, DUP: total=8.00MB, used=4.00KBSystem: total=4.00MB, used=0.00Metadata, DUP: total=1.00GB, used=24.00KBMetadata: total=8.00MB, used=0.00
We can also see that usage at OS level.
[root@btrfs /]# df -k /btrfs1Filesystem         1K-blocks      Used Available Use% Mounted on/dev/sdb           12582912       120  10457024   1% /btrfs1
Create some directories for the Oracle binaries and datafiles :-
[root@btrfs ~]# cd /btrfs1[root@btrfs u01]# mkdir app[root@btrfs u01]# mkdir data[root@btrfs u01]# chown oracle:dba app data
And now we can start using some of the BTRFS functionality. Create subvolumes for the source database binaries and datafiles. BTRFS allows us to take snapshots of the subvolumes at a later time, so it is best to plan subvolume layout before installing files.

Create subvolumes using "btrfs subvolume create" :-

[root@btrfs btrfs1]# su - oracle
[oracle@btrfs app]$ cd /btrfs1/app
[oracle@btrfs app]$ btrfs subvolume create source
Create subvolume './source'
[oracle@btrfs app]$ cd /btrfs1/data
[oracle@btrfs data]$ btrfs subvolume create source
Create subvolume './source'

Install the Oracle binaries and database in the subvolumes.

Oracle binaries in /btrfs1/app/source and create database "source" in /btrfs1/data/source.This install used about 5.6GB of disk.
[oracle@btrfs source]$ df /btrfs1
Filesystem        1K-blocks      Used Available Use% Mounted on
/dev/sdb          12582912   5661424   4938480  54% /btrfs1

Database source currently has 1.1GB of datafiles, using total 1.3GB in the filesystem.
SQL> select sum(bytes)/1024/1024/1024 from dba_data_files;
SUM(BYTES)/1024/1024/1024-------------------------               1.11816406
[oracle@btrfs source]$ pwd
/btrfs1/data/source
[oracle@btrfs source]$ du -k .
1346188 .
[oracle@btrfs source]$ ls
control01.ctl redo01.log  redo03.log  system01.dbf  undotbs01.dbf
control02.ctl  redo02.log  sysaux01.dbf temp01.dbf users01.dbf

If we want to take a cold backup of the source database before we run some tests, all we need to do is shutdown and create a snapshot.  First check how much space is available in the filesystem and how much is used by the source database.

[oracle@btrfs data]$ pwd
/btrfs1/data
[oracle@btrfs data]$ df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sdb 12582912 5661820 4938124 54% /btrfs1
[oracle@btrfs data]$ du -sk *
1346188 source

Create the snapshot using "btrfs subvolume snapshot".

[oracle@btrfs data]$ btrfs subvolume snapshot source backup1
Create a snapshot of 'source' in './backup1'

And we can see that the snapshot did not use any additional disk, but we now have a second set of datafiles in directory backup1. 

[oracle@btrfs data]$ df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sdb 12582912 5661820 4938124 54% /btrfs1
[oracle@btrfs data]$ du -sk *
1346188 source
1346188 backup1

We can now start the database and run our tests, knowing we have a backup available.  Because BTRFS is a "copy on write" filesystem, any block that is changed in the source database will be copied to a new location, and the original left in place as part of the backup1 snapshot.  Since the binaries are also installed in a BTRFS subvolume, we could also take a snapshot of them in case we wanted to test patching or relinking.
If we needed additional test environments we can use the files from a snapshot, recreate the controlfile, and open resetlogs.
To create a new clone database called "target", take a snapshot :-
[oracle@btrfs data]$ btrfs subvolume snapshot source targetCreate a snapshot of 'source' in './target'
Then create the parameter file inittarget.ora, audit directories, and script to recreate the controlfile.
[oracle@btrfs data]$ export ORACLE_SID=target[oracle@btrfs data]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 14 11:44:17 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to an idle instance.
SQL> @/home/oracle/recrctl_target.sqlORACLE instance started.
Total System Global Area 1570009088 bytesFixed Size                  2213696 bytesVariable Size             922749120 bytesDatabase Buffers          637534208 bytesRedo Buffers                7512064 bytes
Control file created.Database altered.Tablespace altered.SQL> select name from v$database;
NAME---------TARGET
SQL> select name from v$datafile;
NAME-----------------------------------------------------------------/btrfs1/data/target/system01.dbf/btrfs1/data/target/sysaux01.dbf/btrfs1/data/target/undotbs01.dbf/btrfs1/data/target/users01.dbf
At the OS level we can see both databases running, against their own files, with very little additional storage consumed.
[oracle@btrfs data]$ ps -ef | grep pmonoracle    5581     1  0 11:44 ?        00:00:00 ora_pmon_targetoracle    5708     1  0 11:42 ?        00:00:00 ora_pmon_source
[oracle@btrfs data]$ df /btrfs1Filesystem        1K-blocks      Used Available Use% Mounted on/dev/sdb           12582912   5792420   4808972  55% /btrfs1
[oracle@btrfs data]$ du -sk *1346188 source1346188 backup11346828 target
The result :-
Using BTRFS snapshots, we now have two running databases, and one cold backup, with deduplication at filesystem block level.  If we had created the backup and clone in a normal filesystem (ext3 etc) we would have used almost 2.7GB of disk, but with deduplication we have only used about 0.1GB.  We now have 4.8GB free on the filesystem, so could create many more databases to keep the developers happy without making the accountants unhappy.   Disk usage will increase as blocks are updated, but unless 100% of the blocks are updated we will still see an advantage from deduplication.

Obviously this was a small example to demonstrate the use of BTRFS snapshots and deduplication, but the same will apply with large databases, with considerable savings.   In the example above we shutdown the source database to take a snap, but if the database is in archivelog mode we could just take a hot backup, or we could run a Dataguard Standby, and defer recovery to take a snapshot.  My tests were only run on a small VM which is not suitable for performance testing, so additional testing on physical hardware would be useful if database performance is important.
The benefits of block level deduplication are obvious, with significant cost savings on storage, but we should also consider the downside.  There is an overhead to deduplication, so database users are likely to see a degrade in performance.  It should also be noted that BTRFS is currently listed as experimental, so a bit more work needs to be done before it is ready for important systems, but it is worth testing to see what is possible.  In addition to the deduplication features offered by BTRFS, the filesystem also supports compression which would further reduce disk usage.











Categories: DBA Blogs

Select from Alert and Listener logs using V$DIAG_ALERT_EXT

Tue, 2013-01-29 03:03
With Oracle 11.2 it is now possible to select directly from the alert and listener logs.

This is quite useful if a DBA is logged into the database, but not the server, and wants to monitor logs.

# Selecting from the Alert log :-


SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXTfrom V$DIAG_ALERT_EXTWHERE ORIGINATING_TIMESTAMP > sysdate -1/24and trim(COMPONENT_ID)='rdbms'; 
ORIGINATING_TIMESTAMP-----------------------------------------------------------------MESSAGE_TEXT-----------------------------------------------------------------29-JAN-13 12.19.40.633000000 PM +09:00create tablespace HR_AUDIT
29-JAN-13 12.19.42.037000000 PM +09:00Completed: create tablespace HR_AUDIT

# Selecting from the Listener log :-


SQL> select ORIGINATING_TIMESTAMP,MESSAGE_TEXT
from V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > sysdate -1/240
and trim(COMPONENT_ID)='tnslsnr';  

ORIGINATING_TIMESTAMP
-----------------------------------------------------------------
MESSAGE_TEXT
-----------------------------------------------------------------
29-JAN-13 12.20.22.866000000 PM +09:00
29-JAN-2013 12:20:22 * (CONNECT_DATA=(SID=test)(CID=(PROGRAM=perl)(HOST=rac1.tes
t.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.204)(PORT=15961))
 * establish * test * 0

29-JAN-13 12.20.23.540000000 PM +09:00
29-JAN-2013 12:20:23 * (CONNECT_DATA=(SID=test)(CID=(PROGRAM=perl)(HOST=rac1.tes
t.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.204)(PORT=15962))
 * establish * test * 0

Categories: DBA Blogs

Linking BBED on Oracle 11g / Linux

Mon, 2013-01-21 02:19
The BBED tool is not documented or supported by Oracle for external clients, and is not distributed by Oracle in binary form, so must be compiled when needed.  The libraries needed to compile it have not been distributed in 11g, but it still compiles with files copied from a 10g install.

Of course linking this tool with manually copied libraries, with no documentation or support from Oracle could easily lead to unrecoverable data corruption.  It should be safe to use it to read data from blocks but I don't recommend using BBED to modify data on a database you care about.   Test it against a DEV database, and don't expect support.

1. copy library files from 10g $ORACLE_HOME/rdbms/lib to 11g $ORACLE_HOME/rdbms/lib :-

cp ssbbded.o $ORACLE_HOME/rdbms/lib/
cp sbbdpt.o $ORACLE_HOME/rdbms/lib/

2. copy message files from 10g $ORACLE_HOME/rdbms/mesg to 11g $ORACLE_HOME/rdbms/mesg :-

cp bbedus.msb $ORACLE_HOME/rdbms/mesg/
cp bbedus.msg $ORACLE_HOME/rdbms/mesg/

3. set environment for the 11g $ORACLE_HOME/rdbms/lib and make bbed

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

4. execute bbed

cd $ORACLE_HOME/bin
$ ./bbed
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Mon Jan 21 07:08:13 2013

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

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

BBED>


The password is hard coded in the binary, so you can get a list of likely candidates using  "strings -a bbed" and use trial and error to confirm that the correct one is "BLOCKEDIT"
Categories: DBA Blogs

Oracle BBED command syntax from "help all"

Mon, 2013-01-21 02:15
BBED> help all
SET DBA [ dba | file#, block# ]
SET FILENAME 'filename'
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] 'filename'
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE  [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]

:N - a number which specifies a repeat count.
u - a letter which specifies a unit size:
  b - b1, ub1 (byte)
  h - b2, ub2 (half-word)
  w - b4, ub4(word)
  r - Oracle table/index row
f - a letter which specifies a display format:
  x - hexadecimal
  d - decimal
  u - unsigned decimal
  o - octal
  c - character (native)
  n - Oracle number
  t - Oracle date
  i - Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
      [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] =
: [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
: [ value | ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]
Categories: DBA Blogs

Using BBED to update Oracle Password Hashes

Mon, 2013-01-21 01:56

In a previous blog post I showed it was possible to extract unencrypted data directly from Oracle database files, bypassing all database level security and auditing.

In this post I will show it is also possible to avoid database level security and audit to update data.    It is relatively easy to write a program to do this if you understand the Oracle block structure, but for this example I will use the BBED tool (Oracle Block Browser and EDitor)

The BBED tool is not documented or supported by Oracle for external clients, and is not distributed by Oracle in binary form, so must be compiled when needed.  The libraries needed to compile it have not been distributed in 11g, but it still compiles with files copied from a 10g install.

Of course linking this tool with manually copied libraries, with no documentation or support from Oracle could easily lead to unrecoverable data corruption.  It should be safe to use it to read data from blocks but I don't recommend using BBED to modify data on a database you care about.   Test it against a DEV database, and don't expect support.

Using BBED to update password hashes in USER$ (cluster C_USER#)
BBED can directly update data in tables.  In this case I am updating the password hash in table USER$ (cluster C_USER#) to allow database login with a known password.

The database is Oracle 11.2.0.1 on 32 bit Linux.  I have also enabled case sensitive login for extra security, so password hashes are stored in attribute SPARE4.

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
sec_case_sensitive_logon             boolean     TRUE

First get the hash for a known password.  This can be done in any database, then used on the target.

SQL> alter user hr identified by knownpassword;

User altered.

SQL> select spare4 from user$ where name in ('HR');

SPARE4
--------------------------------------------------------------------------------
S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E

So the hash we WANT is :-
S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E

Now find details of the string, file, block, offset to replace.  This could be done by scanning blocks on disk, but in this demonstration I will use data dictionary tables to speed it up.

SQL> select spare4 from user$ where name in ('HR');

SPARE4
-----------------------------------------------------------------
S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C

And the hash we want to REPLACE is :-
S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C

Now we find the file and block where we should look for the hash string.

SQL> select file_id, block_id, blocks from dba_extents where segment_name = 'C_USER#';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         1        208          8

SQL> select rowid from sys.user$ where name = 'HR';

ROWID
------------------
AAAAAKAABAAAADVAAG

SQL> SELECT dbms_rowid.rowid_block_number('AAAAAKAABAAAADVAAG') FROM dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAAAKAABAAAADVAAG')
---------------------------------------------------
                                                213

SQL> select file#||' '||name||' '||bytes from v$datafile where file# = 1;

FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf 702545920

So we have identified that C_USER# is stored in file 1, in one extent of 8 blocks starting at block number 208.  The actual row we want to update is in block 213.

With the file details we can create the parameter file and execute BBED :-

vi /home/oracle/bbed.par

blocksize=8192
listfile=/home/oracle/fileunix.log
mode=edit
1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf 702545920


$ bbed
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Mon Jan 21 08:34:55 2013

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

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


# Load the configuration file :-

BBED> SET LIST '/home/oracle/bbed.par'
        LISTFILE        /home/oracle/bbed.par

# Specify the filename to work on :-

BBED> SET FILENAME '/ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf'
        FILENAME        /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf

# Tell BBED to work on file 1, block 213 :-

BBED> set dba 1,213
        DBA             0x004000d5 (4194517 1,213)

# Now find the offset where the hash string starts :-

BBED> find /c S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C
 File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
 Block: 213              Offsets: 5393 to 5904           Dba:0x004000d5
------------------------------------------------------------------------
 533a3637 43364532 45334230 36393044 36363539 43463131 42374542 39363841
 36343837 39313734 46343237 36363534 43443042 39383033 31344339 38436c00
 16040248 5202c102 10433232 42333046 42423839 42363541 4402c108 02c10407


# Dump the data at that offset to confirm the length and content

BBED> dump /v dba 1,213 offset 5393 count 62
 File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
 Block: 213     Offsets: 5393 to 5454  Dba:0x004000d5
-------------------------------------------------------
 533a3637 43364532 45334230 36393044 l S:67C6E2E3B0690D
 36363539 43463131 42374542 39363841 l 6659CF11B7EB968A
 36343837 39313734 46343237 36363534 l 64879174F4276654
 43443042 39383033 31344339 3843     l CD0B980314C98C


# Set BBED to EDIT mode

BBED> SET MODE EDIT
        MODE            Edit

# Modify update the block with the new hash string from the specified offset

BBED> modify /c S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E dba 1,213 offset 5393
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
 File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
 Block: 213              Offsets: 5393 to 5454           Dba:0x004000d5
------------------------------------------------------------------------
 533a4541 30353837 37424330 33433033 34323243 35354533 39413433 42443031
 33303133 44374545 46333334 43463546 45414130 32324439 35324237 3845

# Dump the data again to confirm the update.

BBED> dump /v dba 1,213 offset 5393 count 62
 File: /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf (1)
 Block: 213     Offsets: 5393 to 5454  Dba:0x004000d5
-------------------------------------------------------
 533a4541 30353837 37424330 33433033 l S:EA05877BC03C03
 34323243 35354533 39413433 42443031 l 422C55E39A43BD01
 33303133 44374545 46333334 43463546 l 3013D7EEF334CF5F
 45414130 32324439 35324237 3845     l EAA022D952B78E

We confirmed that the hash has been updated.  Now, we need to update the block checksum to match the new data.

Check if the checksum needs to be updated :-

BBED> sum dba 1,213
Check value for File 1, Block 213:
current = 0x8d06, required = 0x8208

It does need to be updated, so now we apply the update.

BBED> sum dba 1,213 apply
Check value for File 1, Block 213:
current = 0x8208, required = 0x8208

# Now exit BBED and check from the database if we can see the changed data :-

SQL> select spare4 from user$ where name in ('HR');

SPARE4
-----------------------------------------------------------------
S:67C6E2E3B0690D6659CF11B7EB968A64879174F4276654CD0B980314C98C

# Select from the database is still showing the old value because it uses the cached block version in memory.  The database needs to re-read the block from disk to get the updated block.  This could be done by a database restart, by filling the buffer cache to flush LRU blocks, or by manually flushing the cache.  If someone was actually using BBED to update data the best time would be immediately before or after a database restart to avoid a mismatch between data on disk and in cache, and avoid having the changed disk block being overwritten.

In this case I will speed up the process by flushing the cache.

SQL> alter system flush buffer_cache;

System altered.

SQL> select spare4 from user$ where name in ('HR');

SPARE4
-----------------------------------------------------------------
S:EA05877BC03C03422C55E39A43BD013013D7EEF334CF5FEAA022D952B78E

You can see the select now returns the updated hash for the known password.  And we can test is using :-

SQL> connect hr/knownpasswordConnected.
Using BBED (or similar program) it is possible for someone with write access to Oracle datafiles to directly update data, passwords, or code and bypass database level security and audit.   To avoid this vulnerability, secure the server, limit access to datafiles, and encrypt sensitive data.

If you want to find out more about BBED you can run "help all" you can get a basic listing of commands.

More information about BBED can be found here in the following document by Graham Thornton :- http://orafaq.com/papers/dissassembling_the_data_block.pdf
Categories: DBA Blogs

Using Oracle Data Unloader (ODU) to extract data from Oracle Databases while avoiding security and auditing.

Fri, 2013-01-18 00:54

There are some very expensive products being sold to stop people from accessing sensitive data in Oracle databases, and some companies even insist that DBAs should manage databases without having access to the data. This post is to remind managers that even with the most expensive security product, if the data is not encrypted, anyone with access to the files on disk can read it. Don't believe what the Salesmen tell you !!!

Anyone with access to Oracle datafiles with unencrypted table data can extract it. The Oracle block structure is reasonably well known, and there are a number of commercial products that allow users to read directly from files without being logged into the database. If you are reading data directly from files, it can be done from standby databases, shutdown databases, or even file fragments or backup pieces.

These data unloader tools are usually meant for extracting data from corrupt files, truncated tables, or for fast data migration or replication, but they can also be used to bypass database security and audit layers for extraction of sensitive data or even password hashes which can be used to guess passwords for privilege escalation.

Following is an example using the Oracle Data Unloader (ODU) tool.  There are other data unloader tools available, and I have not compared them, so can't say which is best, and have not compared prices.

1. Download Oracle Data Unloader (ODU) trial version from http://www.oracleodu.com/en/ and extract the binary and config files.2. Get the SYSTEM tablespace file names by selecting from v$datafile, or by looking in the OS.
SQL> select ts#,file#,rfile#,name from v$datafile where name like '%system%';
       TS#      FILE#     RFILE#  NAME
---------- ---------- ----------  --------------------------------------------------------------------------------
         0          1          1  /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf

3. Add the datafile to control.txt
$ more control.txt
#ts fno   rfno     filename                                          block_size  is_big_file header_offset blocks
 0 1 1 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf

4. Start ODU
$ ./odu

Oracle Data Unloader trial version 4.1.3

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
block_buffers 1024
error at line 3.
db_timezone -7
Invalid db timezone:-7
client_timezone 8
Invalid client timezone:8
asmfile_extract_path   /asmfile
data_path   data
lob_path    /odu/data/lob
charset_name US7ASII
charset name 'US7ASII' not found,will use default charset ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order big
trace_level 1
delimiter |
unload_deleted no
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 20000
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 ......


grp# dsk# bsize ausize disksize diskname        groupname       path
---- ---- ----- ------ -------- --------------- --------------- --------------------------------------------

load asm disk file 'asmdisk.txt' successful
loading default control file ......


 ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
   0    1    1  8192    85760 N       0 /ora01/oradata/TEST1/datafile/o1_mf_system_8h9crmow_.dbf
load control file 'control.txt' successful
loading dictionary data......done

loading scanned data......done
5. Unload the dictionary
ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 208
TABLE OBJ$ 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
6. Unload the table data you want.  In this case we are unloading the USER$ table which contains password hashes.ODU> unload table SYS.USER$

Unloading table: USER$,object ID: 22
Unloading segment,storage(Obj#=22 DataObj#=10 TS#=0 File#=1 Block#=208 Cluster=1)
89 rows unloaded
7. Review the extracted data, load it into another database etc.
$ ls -lrt
total 9136
-rwxr-xr-x  1 oracle oinstall     558 Mar 22  2011 config.txt
-rwxr-xr-x  1 oracle oinstall 2588361 Feb  2  2012 odu
-rw-r--r--  1 oracle oinstall       0 Jan 18 09:40 odu_trace.txt
-rwxr-xr-x  1 oracle oinstall      87 Jan 18 09:40 asmdisk.txt
-rwxr-xr-x  1 oracle oinstall     181 Jan 18 09:50 control.txt
-rw-r--r--  1 oracle oinstall    1440 Jan 18 09:52 user.odu
-rw-r--r--  1 oracle oinstall 2732224 Jan 18 09:52 obj.odu
-rw-r--r--  1 oracle oinstall 3725801 Jan 18 09:52 col.odu
-rw-r--r--  1 oracle oinstall  148683 Jan 18 09:52 tab.odu
-rw-r--r--  1 oracle oinstall   33307 Jan 18 09:52 lob.odu
-rw-r--r--  1 oracle oinstall      40 Jan 18 09:52 lobfrag.odu
-rw-r--r--  1 oracle oinstall   61636 Jan 18 09:52 ind.odu
drwxr-xr-x  2 oracle oinstall    4096 Jan 18 09:53 data
$ cd data
$ ls
SYS_USER$.ctl  SYS_USER$.sql  SYS_USER$.txt

$ more SYS_USER$.txt

0|SYS|1|DCB748A5BC5390F2|0|3|2009-08-13 23:00:59|2013-01-15 10:22:57|2009-08-13 23:56:35|2009-08-13 23:56:35|0||1|||0|0|DEFAU
LT_CONSUMER_GROUP||0|||S:53620F1B30414FA6489438A818421FB22C752C53A9B0519C7A3FEB67A7C5


I was able to extract the password hashes for the SYS user, and all other users in the database (not shown), without logging into the database, or leaving an audit record.  I could then use brute force, or rainbow tables to find the password.  Alternatively I could also extract any unencrypted credit card details, application passwords etc.

To properly secure sensitive data in Oracle Databases, access to the server, datafiles, and backups should be restricted, and data should be encrypted.
Categories: DBA Blogs