AnySQL.net English

    I have seems somebody truncated their tables by mistaken several times, because of they connect to wrong database and run the script without careful check. To save data, the first thing they need to do is stop the database, and take a backup of the tablespace where the tables locate. For truncation, Oracle just reformat the segment header, and other blocks remain untouched, so we are able to recover the data, however if the blocks are touched by oracle again, it will be formatted to hold new rows. Following is an example of how to recover the rows back, let's build a demo table first:

SQL> CREATE TABLE T_TRUNCATE AS SELECT * FROM TAB;

Table created.

SQL> SELECT COUNT(*) FROM T_TRUNCATE;

  COUNT(*)
----------
        14

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> TRUNCATE TABLE T_TRUNCATE;

Table truncated.

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

    The truncated table will be assigned a new data object id, we could get it from DESC command output:L

AUL> desc anysql.t_truncate

Storage(OBJ#=9976 OBJD=9977 TS=4 FILE=4 BLOCK=5235 CLUSTER=0)
No. SEQ INT Column Name                   Type
--- --- --- ----------------------------- ----------------
  1   1   1 TNAME                         VARCHAR2(30) NOT NULL
  2   2   2 TABTYPE                       VARCHAR2(7)
  3   3   3 CLUSTERID                     NUMBER

    To recover the rows, we need run scan extent command to generate the extent map to a text file (AULEXT.TXT), because segment header is formatted by Oracle.

    All the blocks of a chunk must be continual with a single extent, because you cannot specify a chunk size larger than the next extent size. Based on this rule, I add different chunk size support to AUL 4. Following are two demo tables, they both contains one column and only one row, populated with the same data.

SQL> SELECT TABLE_NAME,COLUMN_NAME,CHUNK FROM USER_LOBS;

TABLE_NAME      COLUMN_NAME       CHUNK
--------------- ------------ ----------
T_CHUNK2        COL1              32768
T_CHUNK1        COL1              16384

    Then we will use the chunk option of unload command to specify the LOB chunk size (how many blocks?) of this table, default is one. There is one limit that all the LOB columns should have the same chunk size in a single table. Then we start to unload the two LOB value to OS files :

AUL> set clob_edian big
  Current CLOB_EDIAN is : BIG
AUL> set lob_storage 1
  Current LOB_STORAGE is : 1-FILE
AUL> set lob_convert 1
  Current LOB_CONVERT is : 1-GBK
AUL> unload table anysql.t_chunk1 chunk 2;
2006-12-19 12:58:18
Unload OBJD=9986 FILE=4 BLOCK=5243 CLUSTER=0 ...
LOB_0100147C_1FE8.dat
2006-12-19 12:58:18
AUL> unload table anysql.t_chunk2 chunk 4;
2006-12-19 12:58:25
Unload OBJD=9989 FILE=4 BLOCK=5523 CLUSTER=0 ...
LOB_01001594_1FE8.dat
2006-12-19 12:58:25

    The above command created two data files on disk, LOB_0100147C_1FE8.dat and LOB_01001594_1FE8.dat, let's check the size and do a compare with the original data file:

    When you prepare to do recovery of partitioned table with LOB columns with AUL4, you must modify the LOB index's partition name first in AULOBJ.TXT. I have thought that the LOB index have the same partition name with table, just as normal local index, and in AUL 4 I use the table's partition name to locate the LOB index's partition to get the relative data object id. But the names are not same, let's start with two demo table :

SQL> CREATE TABLE T_HASHLOB (COL1 NUMBER, COL2 CLOB)
  2  LOB(COL2) STORE AS (DISABLE STORAGE IN ROW)
  3  PARTITION BY HASH(COL1) PARTITIONS 2;

Table created.

    Insert few rows and the perform a checkpoint, and start AUL 4 to unload the dictionary table, and describe the table structure:

AUL> desc anysql.t_hashlob

Storage(OBJ#=0 OBJD=0 TS=0 FILE=0 BLOCK=0 CLUSTER=0)
No. SEQ INT Column Name         Type
--- --- --- ------------------- ----------------
  1   1   1 COL1                NUMBER
  2   2   2 COL2                CLOB  (SYS_IL0000010046C00002$$)

    We could use grep to get the partition name information:

C:\MYDUL>grep -i t_hashlob AULOBJ.TXT
10048,25,T_HASHLOB,SYS_P28,19
10047,25,T_HASHLOB,SYS_P27,19
10046,25,T_HASHLOB,,2

C:\MYDUL>grep -i "SYS_IL0000010046C00002\$\$" AULOBJ.TXT
10054,25,SYS_IL0000010046C00002$$,SYS_IL_P32,20
10053,25,SYS_IL0000010046C00002$$,SYS_IL_P31,20
10052,25,SYS_IL0000010046C00002$$,,1

    Then we modify the partition name of LOB index according to the order of object id:

    AUL do support IOT table, but need some extra steps to modify dictionary information unloaded by AUL. Let's start with two demo tables:

CREATE TABLE T_IOT
(
   COL1 NUMBER NOT NULL PRIMARY KEY,
   COL2 VARCHAR2(20)
)
ORGANIZATION INDEX;

CREATE TABLE T_IOT2
(
   COL1 NUMBER NOT NULL CONSTRAINT PK_T_IOT2 PRIMARY KEY,
   COL2 VARCHAR2(20)
)
ORGANIZATION INDEX;

    If we don't specify the constraint name of primary key constraint of IOT table, the data segment name looks like "SYS_IOT_TOP_objid". If we specify a constraint name, the constraint name would be the segment name of IOT table. And the object id of the IOT index should be equal to object id of table plus 1. I will test the partitioned IOT tables later. Then I query the object id and the IOT segment name from the database with SQL*Plus, as following:

OBJECT_ID OBJECT_NAME
---------- ------------------------------
     10077 SYS_IOT_TOP_10076
     10076 T_IOT
     10078 T_IOT2
     10079 PK_T_IOT2

    The problem existing is that AUL cannot match the table name to the IOT segment name, and then cannot get the data object id of IOT tables. So we need to modify the rows in AULTAB.TXT file for the IOT segment, we need to change the object id (first column) of IOT segment, it should be equal to IOT table's object id.

C:\MYDUL>grep SYS_IOT_TOP_10076 AULOBJ.TXT
10077,25,SYS_IOT_TOP_10076,,1

C:\MYDUL>grep T_IOT AULOBJ.TXT
10076,25,T_IOT,,2

C:\MYDUL>grep 10077 AULTAB.TXT
10077,10077,4,4,2451

    I will copy the a row, and then change the object id of IOT segment to 10076 (the IOT table's object id).

    Several commands (OSDUMP, UNLOAD, ORADUMP, BCHECK, CORRUPT, ROWID) of AUL accept an option named RDBA (Relative Data Block Address), a 32 bit unsigned integer consists of relative file number (RFILE#, the highest 10 bits) and data block id (BLOCK#, the lower 22 bits), this value is always printed as a HEX string in Oracle's block dump trace, while in previously version, you are required to convert the HEX string to a integer. But now it was enhanced, you could assign this option a value by providing both an integer or HEX string (by prefix by 0x.

    The following sample show you how to assign a HEX value to RDBA option:

AUL> unload object 10102 rdba 0x0180000f column number char char;
2007-01-29 23:38:38
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON
2007-01-29 23:38:38

    Following sample will introduce you a very useful command in AUL, create or analyze an oracle ROWID:

AUL> rowid create object 10102 rdba 0x0180000f slot 0

OBJD  = 10102
RDBA  = 0x0180000f = 25165839
RFN#  = 6
BID#  = 15
SLOT  = 0
ROWID = AAACd2AAGAAAAAPAAA

AUL> ROWID PARSE AAACd2AAGAAAAAPAAA

OBJD  = 10102
RDBA  = 0x0180000f = 25165839
RFN#  = 6
BID#  = 15
SLOT  = 0
ROWID = AAACd2AAGAAAAAPAAA

    Make step slowly, but it's making step forward.