Oracle Undelete Record/Rows/Table Database/RDBMS deleted by mistake

Data Record/rows is deleted in Oracle , it just means the row piece has been flagged as deleted, but we can still extract the record/rows from underlying disk .

This can be done by bbed or PRM-DUL (http://www.parnassusdata.com/en).

the flag info can be :

struct kdrh

 

 /* row header structure for passing as an argument to a function */
 struct kdrh
 {
 ub1 kdrhflag; /* the flag byte for the piece being inserted */
 #define KDRHFK 0x80 /* cluster Key */
 #define KDRHFC 0x40 /* Clustered table member */
 #define KDRHFH 0x20 /* Head piece of row */
 #define KDRHFD 0x10 /* Deleted row */
 #define KDRHFF 0x08 /* First data piece */
 #define KDRHFL 0x04 /* Last data piece */
 #define KDRHFP 0x02 /* first column continues from Previous piece */
 #define KDRHFN 0x01 /* last column continues in Next piece */
 #define KDRHFCK (KDRHFK|KDRHFH|KDRHFF|KDRHFL) /* Cluster Key flag settings */
 #define KDRHFSB(c) (KDRHFH|((c)&KDRHFC)) /* StuB flags from regular */
 #define KDRHFSP (KDRHFH|KDRHFF|KDRHFL) /* Single Piece flags */
 #define KDRHFCS (KDRHFSP|KDRHFC) /* Clustered Single piece */
 #define KDRHCK (KDRHFCK|KDRHFN|KDRHFP) /* hash cluster key */
 ub1 kdrhlock; /* locking itl index */
 #define KDRLKCLR 0 /* LocK CLeaR */
 ub1 kdrhccnt; /* the column count for the row piece */
 #define KDRMAXCO UB1MAXVAL /* MAXimum number of COlumns */
 ub1 kdrhckix; /* Cluster Key IndeX */
 #define KDRMAXCK (UB1MAXVAL+1) /* MAXimum number of cluster keys */
 kd4ssrid kdrhhrid; /* Head RowID, if there is one */
 kd4ssrid kdrhnrid; /* Next RowID, if there is one */
 sb2 kdrhkref; /* Key REFerence count */

A hexadecimal dump of a data block showing an entire row has a row flag value of

“2c.” This sets the bits KDRHFH, KDRHFF, KDRHFL, which would display as –HFL–

in a logical tracefile dump. That is, the row piece contains the header, the first column,

and the last column.

If the row is being updated, then the lock byte references the ITL entry# of the

transaction involved. One byte is used to store the number of columns in this row piece.

If the row is part of a cluster, then you see a one byte cluster key index located after

the number of columns field. This is covered later in the course.

Note: Row-level SCNs will require an additional six bytes of additional storage per

row. Row-level SCNs are used principally in replication environments and are used by

users, and may be used in 3-tier apps as a form of optimistic locking.

Row Format: Column data:

The column length is stored in one byte if the length is up to 250 bytes. A NULL value

stored between two populated columns will be represented as 0xFF (literally) in the

length byte. If the column length is over 250 bytes long, three bytes are used to represent

the length, the first byte being the value 0xFE and the next two bytes being the actual

column length.

Trailing NULLs are not stored, thus occupy no space in the block.

A logical block dump of a table with four columns follows

block_row_dump:

tab 0, row 0, @0x7aa

tl: 14 fb: –H-FL– lb: 0x1 cc: 4

col 0: [ 2] c1 02

col 1: [ 4] 52 4f 57 31

col 2: *NULL* This would appear as 0xFF in the length byte.

col 3: [ 1] 31

tab 0, row 1, @0x79f

tl: 11 fb: –H-FL– lb: 0x1 cc: 2

col 0: [ 2] c1 03

col 1: [ 4] 52 4f 57 32 Trailing NULLs for columns 2 and 3 are not stored.

end_of_block_dump

In the index block, patch the flag byte of the index row to undelete the row. i.e. change the flag byte from 0x01 to 0x00.