An Example of Use ODU to Recover Truncated Table in ASM

Posted: June 24th, 2011

ODU has support for ASM after the version 4.0.0, it can unload data directly from ASM disks even all the diskgroups are dismounted.

Because of this feature, now it is a peace of cake to use ODU to recover truncated table in ASM.

Now I use ODU 4.1.2 trial version to demonstrate how to recover a truncated table in Oracle 11gR2 ASM.

Please note that the ODU trial version only works for testing, learning and validation, it can only unload the data in the SYSTEM tablespace, for the data in other tablespaces, it only unloads a small amount of data to verify the data recoverability. The enterprise version can be able to unload all the data which can be recovered after you get license.

Prepare the ODU control file

If the database can be mounted or opened, you can use the following sql to get the necessary information to fill in the ODU control file:

[oracle@bspdev odu]$ sqlplus ‘/ as sysdba’;

 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 12 13:29:01 2011

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> set wrap off

SQL> set linesize 800

SQL> select ts#,file#,rfile#,name from v$datafile;

 

       TS#      FILE#     RFILE# NAME

———- ———- ———- ———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

         0          1          1 +DATA/ora11g/datafile/system.256.747310449

         1          2          2 +DATA/ora11g/datafile/sysaux.257.747310449

         2          3          3 +DATA/ora11g/datafile/undotbs1.258.747310451

         4          4          4 +DATA/ora11g/datafile/users.259.747310451

 

Fill the ODU control file control.txt with the above query output:

[oracle@bspdev odu]$ cat control.txt

0          1          1 +DATA/ora11g/datafile/system.256.747310449

1          2          2 +DATA/ora11g/datafile/sysaux.257.747310449

2          3          3 +DATA/ora11g/datafile/undotbs1.258.747310451

4          4          4 +DATA/ora11g/datafile/users.259.747310451

 

Prepare the ODU ASM disk configuration file

You can use the following sql to get the necessary information from the view v$asm_disk to fill in the ODU ASM disk configuration file:

SQL> select group_number,name,state from v$asm_diskgroup;

 

GROUP_NUMBER NAME                           STATE

———— —————————— ———–

           1 DATA                           CONNECTED

           2 RECO                           MOUNTED

 

SQL> col path for a30

SQL> col name for a30

SQL> select disk_number,name,path,group_number from v$asm_disk order by group_number,disk_number;

 

DISK_NUMBER NAME                           PATH                           GROUP_NUMBER

———– —————————— —————————— ————

          0 DATA_0000                      /dev/raw/raw3                             1

          1 DATA_0001                      /dev/raw/raw5                             1

          2 DATA_0002                      /dev/raw/raw6                             1

          0 RECO_0000                      /dev/raw/raw7                             2

          1 RECO_0001                      /dev/raw/raw8                             2

 

Please note that the above ASM disks are built on raw partitions under Linux, the actual device file name corresponding to the raw disk /dev/raw/raw[i] is /dev/sda[i].

ODU does not directly support raw disks (or raw partitions) under Linux, but ODU supports the corresponding actual device files, such as a database using /dev/raw/raw1, the corresponding actual device file is /dev/sdc1, you need to insert /dev/sdc1 to the corresponding column (ASM disk path) in ODU ASM disk configuration file. If the user does not have the access privilege to execute ODU, then you should grant the read permission to that user, or to switch to an authorized user such as the root to run the ODU.

 

From the following output information we can see that the ODU can not recognize the raw disk directly:

[oracle@bspdev odu]$ cat asmdisk.txt

# disk_no  disk_path       group_name meta_block_size  ausize disk_size header_offset

0 /dev/raw/raw3 DATA 4096 1048576

1 /dev/raw/raw5 DATA 4096 1048576

2 /dev/raw/raw6 DATA 4096 1048576

0 /dev/raw/raw7 RECO 4096 1048576

1 /dev/raw/raw8 RECO 4096 1048576

 

[oracle@bspdev odu]$ ./odu

 

Oracle Data Unloader:Release 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

db_timezone -7

client_timezone 8

asmfile_extract_path   /odu/asmfile

data_path   data

lob_path    /odu/data/lob

charset_name AL32UTF8

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 ……

 

read data error from asm disk ‘/dev/raw/raw3’.error message:Invalid argument

read data error from asm disk ‘/dev/raw/raw5’.error message:Invalid argument

read data error from asm disk ‘/dev/raw/raw6’.error message:Invalid argument

read data error from asm disk ‘/dev/raw/raw7’.error message:Invalid argument

read data error from asm disk ‘/dev/raw/raw8’.error message:Invalid argument

 

grp# dsk# bsize ausize disksize diskname        groupname       path

—- —- —– —— ——– ————— ————— ——————————————–

 

load asm disk file ‘asmdisk.txt’ successful

loading default control file ……

 

can not found diskgroup for file +DATA/ora11g/datafile/system.256.747310449.

can not found diskgroup for file +DATA/ora11g/datafile/sysaux.257.747310449.

can not found diskgroup for file +DATA/ora11g/datafile/undotbs1.258.747310451.

can not found diskgroup for file +DATA/ora11g/datafile/users.259.747310451.

 

 ts#   fn  rfn bsize   blocks bf offset filename

—- —- —- —– ——– — —— ——————————————–

load control file ‘oductl.dat’ successful

loading dictionary data……done

 

loading scanned data……done

From the above output information we can confirm that the ODU can not recognize the raw disk directly.

 

At this point, you need to change the raw disk name /dev/raw/raw[i] to the corresponding actual device file name /dev/sda[i] in ODU ASM disk configuration file. If the user does not have the access privilege to execute ODU, then you should grant the read permission to that user, or to switch to an authorized user such as the root to run the ODU:

[oracle@bspdev odu]$ su

Password:

 

[root@bspdev odu]# cat asmdisk.txt

# disk_no  disk_path       group_name meta_block_size  ausize disk_size header_offset

0 /dev/sda3 DATA 4096 1048576

1 /dev/sda5 DATA 4096 1048576

2 /dev/sda6 DATA 4096 1048576

0 /dev/sda7 RECO 4096 1048576

1 /dev/sda8 RECO 4096 1048576

 

[root@bspdev odu]# ./odu

 

Oracle Data Unloader:Release 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

db_timezone -7

client_timezone 8

asmfile_extract_path   /odu/asmfile

data_path   data

lob_path    /odu/data/lob

charset_name AL32UTF8

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     9000 DATA_0000       DATA            /dev/sda3

   1    1  4096  1024K     9000 DATA_0001       DATA            /dev/sda5

   1    2  4096  1024K     9000 DATA_0002       DATA            /dev/sda6

   2    0  4096  1024K     9000 RECO_0000       RECO            /dev/sda7

   2    1  4096  1024K     7288 RECO_0001       RECO            /dev/sda8

 

load asm disk file ‘asmdisk.txt’ successful

loading default control file ……

 

 

 ts#   fn  rfn bsize   blocks bf offset filename

—- —- —- —– ——– — —— ——————————————–

   0    1    1  8192    88320 N       0 +DATA/ora11g/datafile/system.256.747310449

   1    2    2  8192    89600 N       0 +DATA/ora11g/datafile/sysaux.257.747310449

   2    3    3  8192    12160 N       0 +DATA/ora11g/datafile/undotbs1.258.747310451

   4    4    4  8192      640 N       0 +DATA/ora11g/datafile/users.259.747310451

load control file ‘oductl.dat’ successful

loading dictionary data……done

 

loading scanned data……done

From the above output information, you can see that the ODU can recognize all the ASM disks information correctly now.

 

The contents of the ASM disk configuration file asmdisk.txt are as follows:

[oracle@bspdev odu]$ cat asmdisk.txt

# disk_no  disk_path       group_name meta_block_size  ausize disk_size header_offset

0 /dev/sda3 DATA 4096 1048576

1 /dev/sda5 DATA 4096 1048576

2 /dev/sda6 DATA 4096 1048576

0 /dev/sda7 RECO 4096 1048576

1 /dev/sda8 RECO 4096 1048576

Prepare the relative test data and begin to execute truncate operation:

[oracle@bspdev odu]$ sqlplus ‘/ as sysdba’;

 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 13 15:33:37 2011

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

Create a test table t1:

SQL> create table t1 as select * from dba_objects;

 

Table created.

 

SQL> select count(*) from t1;

 

  COUNT(*)

———-

     72206

 

SQL> desc t1;             

 Name                                      Null?    Type

 —————————————– ——– —————————-

 OWNER                                              VARCHAR2(30)

 OBJECT_NAME                                        VARCHAR2(128)

 SUBOBJECT_NAME                                     VARCHAR2(30)

 OBJECT_ID                                          NUMBER

 DATA_OBJECT_ID                                     NUMBER

 OBJECT_TYPE                                        VARCHAR2(19)

 CREATED                                            DATE

 LAST_DDL_TIME                                      DATE

 TIMESTAMP                                          VARCHAR2(19)

 STATUS                                             VARCHAR2(7)

 TEMPORARY                                          VARCHAR2(1)

 GENERATED                                          VARCHAR2(1)

 SECONDARY                                          VARCHAR2(1)

 NAMESPACE                                          NUMBER

 EDITION_NAME                                       VARCHAR2(30)

 

Create a table t1_backup which is the backup table of t1, the purpose is to facilitate comparison and verify the data after the recovery:

SQL> create table t1_backup as select * from t1;

 

Table created.

 

SQL> select count(*) from t1_backup;

 

  COUNT(*)

———-

     72206

 

SQL> select * from t1 minus select * from t1_backup;

 

no rows selected

 

Truncate table t1:

SQL> truncate table t1;

 

Table truncated.

 

Then make a full checkpoint, so that ODU can read the latest data dictionary information:

SQL> alter system checkpoint;

 

System altered.

 

You can see that the t1’s 72206 rows are gone:

SQL> select count(*) from t1;

 

  COUNT(*)

———-

         0

 

To illustrate the feature that ODU can unload data directly from ASM disks even all the diskgroups are dismounted, we now shutdown database and ASM instance:

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

[oracle@bspdev odu]$ su – grid

Password:

[grid@bspdev ~]$ sqlplus ‘/ as sysasm’;

 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 13 15:37:47 2011

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Automatic Storage Management option

 

SQL> shutdown immediate

ASM diskgroups dismounted

ASM instance shutdown

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Automatic Storage Management option

 

Form the above output, we can see that all the diskgroups are dismounted now:

[grid@bspdev ~]$ crsctl stat res

NAME=ora.DATA.dg

TYPE=ora.diskgroup.type

TARGET=OFFLINE

STATE=OFFLINE

 

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on bspdev

 

NAME=ora.RECO.dg

TYPE=ora.diskgroup.type

TARGET=OFFLINE

STATE=OFFLINE

 

NAME=ora.asm

TYPE=ora.asm.type

TARGET=OFFLINE

STATE=OFFLINE

 

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on bspdev

 

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on bspdev

 

NAME=ora.ora11g.db

TYPE=ora.database.type

TARGET=OFFLINE

STATE=OFFLINE

 

[grid@bspdev ~]$ crs_stat -t

Name           Type           Target    State     Host       

————————————————————

ora.DATA.dg    ora….up.type OFFLINE   OFFLINE              

ora….ER.lsnr ora….er.type ONLINE    ONLINE    bspdev     

ora.RECO.dg    ora….up.type OFFLINE   OFFLINE              

ora.asm        ora.asm.type   OFFLINE   OFFLINE              

ora.cssd       ora.cssd.type  ONLINE    ONLINE    bspdev     

ora.diskmon    ora….on.type ONLINE    ONLINE    bspdev     

ora.ora11g.db  ora….se.type OFFLINE   OFFLINE              

 

Use ODU trial version to recover the above truncated table

Now we use the ODU trial version to recover the above truncated table t1.

Because we use the raw partition under Linux, we switch to the user root to execute ODU:

[grid@bspdev ~]$ su

Password:

[root@bspdev grid]# cd /u01/app/oracle/odu

[root@bspdev odu]# ./odu

 

Oracle Data Unloader trial version 4.1.2

 

Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.

 

Web: http://www.oracleodu.com

Email: magic007cn@gmail.com

 

loading default config…….

 

byte_order little

block_size  8192

block_buffers 1024

error at line 3.

db_timezone -7

client_timezone 8

asmfile_extract_path   /odu/asmfile

data_path   data

lob_path    /odu/data/lob

charset_name AL32UTF8

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     9000 DATA_0000       DATA            /dev/sda3

   1    1  4096  1024K     9000 DATA_0001       DATA            /dev/sda5

   1    2  4096  1024K     9000 DATA_0002       DATA            /dev/sda6

   2    0  4096  1024K     9000 RECO_0000       RECO            /dev/sda7

   2    1  4096  1024K     7288 RECO_0001       RECO            /dev/sda8

 

load asm disk file ‘asmdisk.txt’ successful

loading default control file ……

 

 

 ts#   fn  rfn bsize   blocks bf offset filename

—- —- —- —– ——– — —— ——————————————–

   0    1    1  8192    92160 N       0 +DATA/ora11g/datafile/system.256.747310449

   1    2    2  8192    92160 N       0 +DATA/ora11g/datafile/sysaux.257.747310449

   2    3    3  8192    12160 N       0 +DATA/ora11g/datafile/undotbs1.258.747310451

   4    4    4  8192     1280 N       0 +DATA/ora11g/datafile/users.259.747310451

load control file ‘control.txt’ successful

loading dictionary data……done

 

loading scanned data……done

 

It is very simple to use ODU to recover truncated table, a total of only three steps you need to do:

The first step is to unload the data 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

 

Get the information of table t1, from the following output, we can see that the tablespace number of the tablespace where t1 belongs to is 0 and the object id of table t1 is 74332:.

ODU> desc sys.t1

 

 

Object ID:74332

Storage(Obj#=74332 DataObj#=74334 TS#=0 File#=1 Block#=83800 Cluster=0)

 

NO. SEG INT Column Name                    Null?     Type                         

— — — —————————— ——— ——————————

  1   1   1 OWNER                                    VARCHAR2(30)                 

  2   2   2 OBJECT_NAME                              VARCHAR2(128)                

  3   3   3 SUBOBJECT_NAME                           VARCHAR2(30)                 

  4   4   4 OBJECT_ID                                NUMBER                       

  5   5   5 DATA_OBJECT_ID                           NUMBER                       

  6   6   6 OBJECT_TYPE                              VARCHAR2(19)                 

  7   7   7 CREATED                                  DATE                         

  8   8   8 LAST_DDL_TIME                            DATE                         

  9   9   9 TIMESTAMP                                VARCHAR2(19)                 

 10  10  10 STATUS                                   VARCHAR2(7)                  

 11  11  11 TEMPORARY                                VARCHAR2(1)                  

 12  12  12 GENERATED                                VARCHAR2(1)                  

 13  13  13 SECONDARY                                VARCHAR2(1)                  

 14  14  14 NAMESPACE                                NUMBER                       

 15  15  15 EDITION_NAME                             VARCHAR2(30)                 

 

The second step is to scan the table t1s extent with ODU, you can specify the tablespace number and object id to narrow the scan range when you use the command "scan extent":

 

ODU> scan extent tablespace 0 object 74332

 

scan extent start: 2011-04-13 15:40:33

scanning extent…

scanning extent finished.

scan extent completed: 2011-04-13 15:40:59

 

The third step is to use ODU to unload the data, from the following output, we can see that the number of unloaded rows is also 72206:

ODU> unload table sys.t1 object truncate

Auto mode truncated table.

 

Unloading table: T1,object ID: 74332

Unloading segment,storage(Obj#=74332 DataObj#=74332 TS#=0 File#=1 Block#=83800 Cluster=0)

72206 rows unloaded

 

ODU> exit

Data import and validation process

Now we use SQL*Loader (sqlldr) to import the recovered 72206 rows:

By default, the recovery data are stored in the subdirectory data in the ODU installation directory:

[root@bspdev data]# pwd

/u01/app/oracle/odu/data

 

ODU will generate the necessary sql statements for creating table and control file used for SQL*Loader automatically

[root@bspdev data]# ls -lrt

total 8592

-rw-r–r–. 1 root root     468 Apr 13 15:41 SYS_T1.sql

-rw-r–r–. 1 root root     644 Apr 13 15:41 SYS_T1.ctl

-rw-r–r–. 1 root root 8770686 Apr 13 15:41 SYS_T1.txt

 

SYS_T1.sql is the SQL statements to create table SYS.T1:

[root@bspdev data]# cat SYS_T1.sql

CREATE TABLE "SYS"."T1"

(

    "OWNER" VARCHAR2(30) ,

    "OBJECT_NAME" VARCHAR2(128) ,

    "SUBOBJECT_NAME" VARCHAR2(30) ,

    "OBJECT_ID" NUMBER ,

    "DATA_OBJECT_ID" NUMBER ,

    "OBJECT_TYPE" VARCHAR2(19) ,

    "CREATED" DATE ,

    "LAST_DDL_TIME" DATE ,

    "TIMESTAMP" VARCHAR2(19) ,

    "STATUS" VARCHAR2(7) ,

    "TEMPORARY" VARCHAR2(1) ,

    "GENERATED" VARCHAR2(1) ,

    "SECONDARY" VARCHAR2(1) ,

    "NAMESPACE" NUMBER ,

    "EDITION_NAME" VARCHAR2(30)

);

 

SYS_T1.ctl is the control file used by SQL*Loader for loading the restored table data into the database:

[root@bspdev data]# cat SYS_T1.ctl

–Generated by ODU,for table "SYS"."T1"

OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)

LOAD DATA

INFILE ‘SYS_T1.txt’ "STR X’0a’"

APPEND INTO TABLE "SYS"."T1"

FIELDS TERMINATED BY X’7c’ TRAILING NULLCOLS

(

    "OWNER" CHAR(30),

    "OBJECT_NAME" CHAR(128),

    "SUBOBJECT_NAME" CHAR(30),

    "OBJECT_ID" ,

    "DATA_OBJECT_ID" ,

    "OBJECT_TYPE" CHAR(19),

    "CREATED" DATE "yyyy-mm-dd hh24:mi:ss",

    "LAST_DDL_TIME" DATE "yyyy-mm-dd hh24:mi:ss",

    "TIMESTAMP" CHAR(19),

    "STATUS" CHAR(7),

    "TEMPORARY" CHAR(1),

    "GENERATED" CHAR(1),

    "SECONDARY" CHAR(1),

    "NAMESPACE" ,

    "EDITION_NAME" CHAR(30)

)

 

SYS_T1.txt is the text file which stored actual data of table SYS.T1:

[root@bspdev data]# tail -n 20 SYS_T1.txt

SYS|WRH$_ACTIVE_SESSION_HISTORY|WRH$_ACTIVE_4143510747_257|74296|74296|TABLE PARTITION|2011-04-12 19:10:38|2011-04-12 19:10:38|2011-04-12:19:10:38|VALID|N|N|N|1

SYS|WRH$_ACTIVE_SESSION_HISTORY_PK|WRH$_ACTIVE_4143510747_257|74298|74298|INDEX PARTITION|2011-04-12 19:10:38|2011-04-12 19:10:38|2011-04-12:19:10:38|VALID|N|N|N|4

SYS|WRH$_ACTIVE_SESSION_HISTORY_PK|WRH$_ACTIVE_4143510747_234|74297|74191|INDEX PARTITION|2011-04-12 19:10:38|2011-04-12 19:10:38|2011-04-12:19:10:38|VALID|N|N|N|4

SYS|WRH$_TABLESPACE_STAT|WRH$_TABLES_4143510747_257|74300|74300|TABLE PARTITION|2011-04-12 19:10:38|2011-04-12 19:10:38|2011-04-12:19:10:38|VALID|N|N|N|1

SYS|WRH$_TABLESPACE_STAT_PK|WRH$_TABLES_4143510747_257|74302|74302|INDEX PARTITION|2011-04-12 19:10:38|2011-04-12 19:10:38|2011-04-12:19:10:38|VALID|N|N|N|4

SYS|WRH$_TABLESPACE_STAT_PK|WRH$_TABLES_4143510747_234|74301|74195|INDEX PARTITION|2011-04-12 19:10:38|2011-04-12 19:10:38|2011-04-12:19:10:38|VALID|N|N|N|4

SYS|WRH$_OSSTAT|WRH$_OSSTAT_4143510747_257|74304|74304|TABLE PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|1

SYS|WRH$_OSSTAT_PK|WRH$_OSSTAT_4143510747_257|74306|74306|INDEX PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|4

SYS|WRH$_OSSTAT_PK|WRH$_OSSTAT_4143510747_234|74305|74199|INDEX PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|4

SYS|WRH$_SYS_TIME_MODEL|WRH$_SYS_TI_4143510747_257|74308|74308|TABLE PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|1

SYS|WRH$_SYS_TIME_MODEL_PK|WRH$_SYS_TI_4143510747_257|74310|74310|INDEX PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|4

SYS|WRH$_SYS_TIME_MODEL_PK|WRH$_SYS_TI_4143510747_234|74309|74203|INDEX PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|4

SYS|WRH$_SERVICE_WAIT_CLASS|WRH$_SERVIC_4143510747_257|74312|74312|TABLE PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|1

SYS|WRH$_SERVICE_WAIT_CLASS_PK|WRH$_SERVIC_4143510747_257|74314|74314|INDEX PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|4

SYS|WRH$_SERVICE_WAIT_CLASS_PK|WRH$_SERVIC_4143510747_234|74313|74207|INDEX PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|4

SYS|WRH$_EVENT_HISTOGRAM|WRH$_EVENT__4143510747_257|74316|74316|TABLE PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|1

SYS|WRH$_EVENT_HISTOGRAM_PK|WRH$_EVENT__4143510747_257|74318|74318|INDEX PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|4

SYS|WRH$_EVENT_HISTOGRAM_PK|WRH$_EVENT__4143510747_234|74317|74211|INDEX PARTITION|2011-04-12 19:10:39|2011-04-12 19:10:39|2011-04-12:19:10:39|VALID|N|N|N|4

SYS|T1||74332|74332|TABLE|2011-04-13 15:34:08|2011-04-13 15:34:08|2011-04-13:15:34:08|VALID|N|N|N|1

SYS|T5||74330|74331|TABLE|2011-04-13 10:15:32|2011-04-13 10:48:23|2011-04-13:10:15:32|VALID|N|N|N|1

 

You can see that the number of recovery data is 72206 indeed:

[root@bspdev data]# cat SYS_T1.txt|wc -l

72206

 

In order to import these 72206 rows, we startup the ASM and database instance:

First we startup the ASM instance:

[root@bspdev data]# su – grid

[grid@bspdev ~]$ sqlplus ‘/ as sysasm’;

 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 13 15:44:13 2011

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ASM instance started

 

Total System Global Area  284565504 bytes

Fixed Size                  1336036 bytes

Variable Size             258063644 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Automatic Storage Management option

 

Then we startup the database instance:

[grid@bspdev ~]$ su – oracle

Password:

[oracle@bspdev ~]$ sqlplus ‘/ as sysdba’;

 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 13 15:44:39 2011

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  845348864 bytes

Fixed Size                  1339796 bytes

Variable Size             637537900 bytes

Database Buffers          201326592 bytes

Redo Buffers                5144576 bytes

Database mounted.

Database opened.

 

We can see that the table t1 is still empty:

SQL> select count(*) from t1;

 

  COUNT(*)

———-

         0

 

SQL> select count(*) from t1_backup;

 

  COUNT(*)

———-

     72206

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

[oracle@bspdev ~]$ cd $ORACLE_HOME/odu/data

[oracle@bspdev data]$ ls -lrt

total 8592

-rw-r–r–. 1 root root     468 Apr 13 15:41 SYS_T1.sql

-rw-r–r–. 1 root root     644 Apr 13 15:41 SYS_T1.ctl

-rw-r–r–. 1 root root 8770686 Apr 13 15:41 SYS_T1.txt

 

Change the permission, so the SQL*Loader can read these files:

 

[oracle@bspdev data]$ su

Password:

[root@bspdev data]# chmod 777 SYS_T1*

[root@bspdev data]# ls -lrt

total 8592

-rwxrwxrwx. 1 root root     468 Apr 13 15:41 SYS_T1.sql

-rwxrwxrwx. 1 root root     644 Apr 13 15:41 SYS_T1.ctl

-rwxrwxrwx. 1 root root 8770686 Apr 13 15:41 SYS_T1.txt

 

Start to use SQL*Loader (sqlldr) to import these 72206 rows:

[root@bspdev data]# su – oracle

[oracle@bspdev ~]$ cd $ORACLE_HOME/odu/data

[oracle@bspdev data]$ ls

SYS_T1.ctl  SYS_T1.sql  SYS_T1.txt

[oracle@bspdev data]$ sqlldr "’sys/oracle as sysdba’ control=SYS_T1.ctl"

 

SQL*Loader: Release 11.2.0.1.0 – Production on Wed Apr 13 15:48:58 2011

 

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

 

Commit point reached – logical record count 5302

Commit point reached – logical record count 10604

Commit point reached – logical record count 15906

Commit point reached – logical record count 21208

Commit point reached – logical record count 26510

Commit point reached – logical record count 31812

Commit point reached – logical record count 37114

Commit point reached – logical record count 42416

Commit point reached – logical record count 47718

Commit point reached – logical record count 53020

Commit point reached – logical record count 58322

Commit point reached – logical record count 63624

Commit point reached – logical record count 68926

Commit point reached – logical record count 69266

Commit point reached – logical record count 72206

 

[oracle@bspdev data]$ sqlplus ‘/ as sysdba’;

 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 13 15:49:07 2011

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

You can see that the truncated 72206 rows are come back:

 

SQL> select count(*) from t1;

 

  COUNT(*)

———-

     72206

 

SQL> select count(*) from t1_backup;

 

  COUNT(*)

———-

     72206

 

At this point, all the steps to recover the truncated table have been completed. Compare the recovered data and the backup data before truncate to see whether they were exactly the same. You can see that the data has been completely restored:

SQL> select * from t1 minus select * from t1_backup;

 

no rows selected

 

Since then, recover a truncated table in ASM will no longer be an extremely difficult task.