An Example of Use ODU to Recover Truncated Table in ASM
Posted: June 24th, 2011ODU 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 t1’s 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.
