When it comes to catastrophic scenarios where the database has been corrupted and there is no available backup, flashback feature was not turned on, database cannot be open due to errors or inconsistent status, there is still a chance to recover and save the data as long as the datafiles are still exist on the filesystem, using third party tools that able to directly read the datafiles without the need to open the database.
In this post I'll cover one of these tools, Oracle Database Unloader (ODU).
Download ODU:
===============
http://www.oracleodu.com/en/download
Full version: Restores all data only if you got the license file from the support [Not Free].
Trial version: No license required, but it comes with limitations, it doesn't work on ASM, it can only recovers data under system datafiles
but for other datafiles it will recover it partially [less than 1% of all rows].
Note: In this demonstration I've used the TRIAL version to restore the data, this is why I was restoring tables located under system tablespace only to be able to recover all data.
Install ODU:
===========
Upload the downloaded file to the server and extract it:
# gunzip odu_433_linux_x86.tar.gz
# tar xvf odu_433_linux_x86.tar
# cd odu
# chown u+x odu
Prepare ODU:
=============
Before start using ODU for recovering data, first you have to modify the configuration files to point to the right paths of your datafiles.
In case the datafiles stored on ASM modify asmdisk.txt by adding ASM disks path to asmdisk.txt file:
# vi asmdisk.txt
0 /oradata/asm/disk1.dbf
0 /oradata/asm/disk2.dbf
0 /oradata/asm/disk3.dbf
Note: The first column should be filled with 0
In case the datafiles are not stored on ASM, add all datafiles path to control.txt file:
This SQL can help you generate the lines you will add to control.txt :
SQL> SELECT 0,0,0,NAME FROM V$DATAFILE ORDER BY FILE#;
# vi control.txt
0 0 0 /database/orcl/datafiles/system01.dbf
0 0 0 /database/orcl/datafiles/users01.dbf
0 0 0 /database/orcl/datafiles/sysaux01.dbf
0 0 0 /database/orcl/datafiles/undotbs01.dbf
0 0 0 /database/orcl/datafiles/example01.dbf
Note: The first three columns should be filled with 0 and the first line must point to the system datafile.
The rest of ODU configuration files like config.txt, contains configurations like block_size which must be the same for the database you are extracting data from.
In config.txt the parameter output_format represent the fashion where the data will be saved after extracting (unload) it from tables. the default value is text where data will be saved in SQL LOADER format, the other value is dump where data will be saved in export dump format.
In case you are using the full version, arrange for the license by save the configuration in a file and send it to ODU support to send you back the license file:
Save the controls to oductl.txt:
# ./odu
ODU> save control
ODU> exit
Send the file oductl.txt to the ODU support mailbox in http://www.oracleodu.com/en/support, they will send you the license file named oductl.dat, copy it under ODU installation directory.
* Note that Trial version doesn't need a license but it's data restoration capability is limited as mentioned before.
Using ODU:
============
Explore the database:
>>>>>>>>>>>>>>>>>
# ./odu
ODU> load config [filename] #Not mandatory, if file name ommited it will load config.txt file
ODU> open [filename] #Not mandatory, if file name ommited it will load control.txt file
ODU> unload dict #Load dictionary data from SYSTEM tablespace.
ODU> list user #List database users & roles.
ODU> desc scott.emp #descripe a table plus provide extra data like object id, tablespace,..etc.
Restore data of a truncated table: [TEST SCENARIO]
>>>>>>>>>>>>>>>>>>>>>>>>>>
*control.txt file must be loaded with datafiles location as shown before.
SQL> CREATE TABLE SCOTT.TEST TABLESPACE USERS AS SELECT * FROM DBA_USERS;
SQL> SELECT COUNT(*) FROM SCOTT.TEST;
SQL> TRUNCATE TABLE SCOTT.TEST;
SQL> alter system checkpoint;
SQL> alter tablespace users offline; #important/not mandatory to offline the tablespace of truncated table to avoid space overwrite.
SQL> alter system checkpoint; #important to write changes to the disk or ODU may not find the table.
# ./odu
ODU> load config [filename] #Not mandatory, if file name ommited it will load config.txt file
ODU> open [filename] #Not mandatory, if file name ommited it will load control.txt file
ODU> unload dict #Load dictionary data from SYSTEM tablespace.
ODU> desc scott.test #Get information of truncated table location (check it's tablespace number: TS#=<> )
Storage(Obj#=55125 DataObj#=55126 TS#=4 File#=4 Block#=403 Cluster=0)
ODU> scan extent tablespace 4 #Scan the extents of the tablespace # of the truncated table.
ODU> unload table scott.test object truncate #Restore all rows of truncated table in a SQL LOADER file.
SQL> alter tablespace users online; #Online back the tablespace of truncated to load back truncated data.
Use SQL LOADER to load the data back to the truncated table:
# cd/data
# sqlldr scott/tiger control=SCOTT_TEST.ctl
Note: In case you want to restore the full data of a table not located on system tablespace, you must use the full licensed version of ODU.
Restore a Dropped Table:
>>>>>>>>>>>>>>>>>>>>>>
Let's create and drop a table for the following testing scenario:
SQL> create table scott.bb tablespace system as select * from dba_synonyms;
SQL> drop table scott.bb;
SQL> alter system checkpoint;
Before using ODU the first step to do is to get the OBJECT ID for the dropped table using Log Miner feature in Oracle:
--Add redo/archive that contain the drop transaction:
SQL> EXEC sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/xty/redo03.log');
--Start mining the registered archives:
SQL> EXEC sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
--Get the execution time of the drop statement:
SQL> SELECT scn,timestamp,sql_redo from v$logmnr_contents where operation='DDL' and sql_redo like '%drop%' order by 2 ;
--Use the previous execution time to get the internal delete statement of the dropped table from the dictionary, where the object id can be found:
SQL> SELECT scn,timestamp,sql_redo from v$logmnr_contents
where timestamp=to_date('2014-05-21 10:33:33','yyyy-mm-dd hh24:mi:ss') and sql_redo like '%OBJ%' order by 1;
--End the Log Miner task:
SQL> EXEC sys.dbms_logmnr.end_logmnr;
Now we suppose to have the object id of the dropped table.
In case you're just testing and don't want to go through this leghnthy process before dropping the table just get it's object id from ODU:
# ./odu
ODU> unload dict
ODU> desc scott.bb #You'll find the object id in the output.
Start the table restoration job using ODU:
# ./odu
ODU> unload dict #Load dictionary data from SYSTEM tablespace.
ODU> scan extent tablespace 0 #scan extents of tablespace 0 (system)
ODU> unload object all tablespace 0 sample #Provides you with the command you shall use to restore for EVERY object inside tablespace 0
ODU> unload user scott #Unload ALL tables owned by user SCOTT (each table will have 3 files located under data directory).
ODU> unload object 55150 sample #Provides you with the command you shall use to restore, for SPECIFIC object inside tablespace 0.
ODU> unload object 55150 tablespace 0 column VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 #Just copy & execute the command from the previous output.
Note: unload command will generate three files for each unloaded table ".txt",".sql",".ctl"
From OS side modify file/data/ODU_0000055150.sql> by changing the name of object from ODU_0000055150 to SCOTT.BB and execute it:
SQL> @/data/ODU_0000055150.sql>
Table created.
From OS modify file/data/ODU_0000055150.ctl> change the name of object from ODU_0000055150 to SCOTT.BB and start load the table with data using SQLLOADER:
# sqlldr scott/tiger control=ODU_0000055150.ctl
Export Data from an existing Table: [Database is down / Skipping Auditing/Permissions]
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# ./odu
ODU> unload dict #Load dictionary data from SYSTEM tablespace.
ODU> desc scott.bb #Scan the extents of the tablespace number of the truncated table.
ODU> scan extent tablespace 0 #Scan the extents of the tablespace #.
ODU> unload table scott.bb object scanned #Export data of table SYS.AA into SQL LOADER file. [object scanned] is not mandatory.
Open the .sql file and change the table owner&name if you wish, do the same for .ctl file then use SQLLOADER to import the data.
# sqlldr scott/tiger control=SCOTT_AA.ctl
Conclusion:
ODU tool can help restore data in catastrophic scenarios where there is no available backup or the database has damaged or cannot be opened for any reason.
On the other hand this tool proves that database security can be easily compromised by anyone have access to database files and have a basic knowledge in using such tools, in order to avoid such breach the DBA should consider encrypting data on filesystem level using encryption features such as TDE, also security hardening and limiting the access on the filesystem level should be highly considered to prevent un-authorized users from accessing database files.
REFERENCES:
Full documentation: http://www.oracleodu.com/soft/ODUUserGuide_en.pdf
Real success story: http://www.basecare.dk/6storage/983/7/recovering_lob-index_block_corruption.pdf
In this post I'll cover one of these tools, Oracle Database Unloader (ODU).
Download ODU:
===============
http://www.oracleodu.com/en/download
Full version: Restores all data only if you got the license file from the support [Not Free].
Trial version: No license required, but it comes with limitations, it doesn't work on ASM, it can only recovers data under system datafiles
but for other datafiles it will recover it partially [less than 1% of all rows].
Note: In this demonstration I've used the TRIAL version to restore the data, this is why I was restoring tables located under system tablespace only to be able to recover all data.
Install ODU:
===========
Upload the downloaded file to the server and extract it:
# gunzip odu_433_linux_x86.tar.gz
# tar xvf odu_433_linux_x86.tar
# cd odu
# chown u+x odu
Prepare ODU:
=============
Before start using ODU for recovering data, first you have to modify the configuration files to point to the right paths of your datafiles.
In case the datafiles stored on ASM modify asmdisk.txt by adding ASM disks path to asmdisk.txt file:
# vi asmdisk.txt
0 /oradata/asm/disk1.dbf
0 /oradata/asm/disk2.dbf
0 /oradata/asm/disk3.dbf
Note: The first column should be filled with 0
In case the datafiles are not stored on ASM, add all datafiles path to control.txt file:
This SQL can help you generate the lines you will add to control.txt :
SQL> SELECT 0,0,0,NAME FROM V$DATAFILE ORDER BY FILE#;
# vi control.txt
0 0 0 /database/orcl/datafiles/system01.dbf
0 0 0 /database/orcl/datafiles/users01.dbf
0 0 0 /database/orcl/datafiles/sysaux01.dbf
0 0 0 /database/orcl/datafiles/undotbs01.dbf
0 0 0 /database/orcl/datafiles/example01.dbf
Note: The first three columns should be filled with 0 and the first line must point to the system datafile.
In config.txt the parameter output_format represent the fashion where the data will be saved after extracting (unload) it from tables. the default value is text where data will be saved in SQL LOADER format, the other value is dump where data will be saved in export dump format.
In case you are using the full version, arrange for the license by save the configuration in a file and send it to ODU support to send you back the license file:
Save the controls to oductl.txt:
# ./odu
ODU> save control
ODU> exit
Send the file oductl.txt to the ODU support mailbox in http://www.oracleodu.com/en/support, they will send you the license file named oductl.dat, copy it under ODU installation directory.
* Note that Trial version doesn't need a license but it's data restoration capability is limited as mentioned before.
Using ODU:
============
Explore the database:
>>>>>>>>>>>>>>>>>
# ./odu
ODU> load config [filename] #Not mandatory, if file name ommited it will load config.txt file
ODU> open [filename] #Not mandatory, if file name ommited it will load control.txt file
ODU> unload dict #Load dictionary data from SYSTEM tablespace.
ODU> list user #List database users & roles.
ODU> desc scott.emp #descripe a table plus provide extra data like object id, tablespace,..etc.
Restore data of a truncated table: [TEST SCENARIO]
>>>>>>>>>>>>>>>>>>>>>>>>>>
*control.txt file must be loaded with datafiles location as shown before.
SQL> CREATE TABLE SCOTT.TEST TABLESPACE USERS AS SELECT * FROM DBA_USERS;
SQL> SELECT COUNT(*) FROM SCOTT.TEST;
SQL> TRUNCATE TABLE SCOTT.TEST;
SQL> alter system checkpoint;
SQL> alter tablespace users offline; #important/not mandatory to offline the tablespace of truncated table to avoid space overwrite.
SQL> alter system checkpoint; #important to write changes to the disk or ODU may not find the table.
# ./odu
ODU> load config [filename] #Not mandatory, if file name ommited it will load config.txt file
ODU> open [filename] #Not mandatory, if file name ommited it will load control.txt file
ODU> unload dict #Load dictionary data from SYSTEM tablespace.
ODU> desc scott.test #Get information of truncated table location (check it's tablespace number: TS#=<> )
Storage(Obj#=55125 DataObj#=55126 TS#=4 File#=4 Block#=403 Cluster=0)
ODU> scan extent tablespace 4 #Scan the extents of the tablespace # of the truncated table.
ODU> unload table scott.test object truncate #Restore all rows of truncated table in a SQL LOADER file.
SQL> alter tablespace users online; #Online back the tablespace of truncated to load back truncated data.
Use SQL LOADER to load the data back to the truncated table:
# cd
# sqlldr scott/tiger control=SCOTT_TEST.ctl
Note: In case you want to restore the full data of a table not located on system tablespace, you must use the full licensed version of ODU.
Restore a Dropped Table:
>>>>>>>>>>>>>>>>>>>>>>
Let's create and drop a table for the following testing scenario:
SQL> create table scott.bb tablespace system as select * from dba_synonyms;
SQL> drop table scott.bb;
SQL> alter system checkpoint;
Before using ODU the first step to do is to get the OBJECT ID for the dropped table using Log Miner feature in Oracle:
--Add redo/archive that contain the drop transaction:
SQL> EXEC sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/xty/redo03.log');
--Start mining the registered archives:
SQL> EXEC sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
--Get the execution time of the drop statement:
SQL> SELECT scn,timestamp,sql_redo from v$logmnr_contents where operation='DDL' and sql_redo like '%drop%' order by 2 ;
--Use the previous execution time to get the internal delete statement of the dropped table from the dictionary, where the object id can be found:
SQL> SELECT scn,timestamp,sql_redo from v$logmnr_contents
where timestamp=to_date('2014-05-21 10:33:33','yyyy-mm-dd hh24:mi:ss') and sql_redo like '%OBJ%' order by 1;
--End the Log Miner task:
SQL> EXEC sys.dbms_logmnr.end_logmnr;
Now we suppose to have the object id of the dropped table.
In case you're just testing and don't want to go through this leghnthy process before dropping the table just get it's object id from ODU:
# ./odu
ODU> unload dict
ODU> desc scott.bb #You'll find the object id in the output.
Start the table restoration job using ODU:
# ./odu
ODU> unload dict #Load dictionary data from SYSTEM tablespace.
ODU> scan extent tablespace 0 #scan extents of tablespace 0 (system)
ODU> unload object all tablespace 0 sample #Provides you with the command you shall use to restore for EVERY object inside tablespace 0
ODU> unload user scott #Unload ALL tables owned by user SCOTT (each table will have 3 files located under data directory).
ODU> unload object 55150 sample #Provides you with the command you shall use to restore, for SPECIFIC object inside tablespace 0.
ODU> unload object 55150 tablespace 0 column VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 #Just copy & execute the command from the previous output.
Note: unload command will generate three files for each unloaded table ".txt",".sql",".ctl"
From OS side modify file
SQL> @
Table created.
From OS modify file
# sqlldr scott/tiger control=ODU_0000055150.ctl
Export Data from an existing Table: [Database is down / Skipping Auditing/Permissions]
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# ./odu
ODU> unload dict #Load dictionary data from SYSTEM tablespace.
ODU> desc scott.bb #Scan the extents of the tablespace number of the truncated table.
ODU> scan extent tablespace 0 #Scan the extents of the tablespace #.
ODU> unload table scott.bb object scanned #Export data of table SYS.AA into SQL LOADER file. [object scanned] is not mandatory.
Open the .sql file and change the table owner&name if you wish, do the same for .ctl file then use SQLLOADER to import the data.
# sqlldr scott/tiger control=SCOTT_AA.ctl
Conclusion:
ODU tool can help restore data in catastrophic scenarios where there is no available backup or the database has damaged or cannot be opened for any reason.
On the other hand this tool proves that database security can be easily compromised by anyone have access to database files and have a basic knowledge in using such tools, in order to avoid such breach the DBA should consider encrypting data on filesystem level using encryption features such as TDE, also security hardening and limiting the access on the filesystem level should be highly considered to prevent un-authorized users from accessing database files.
REFERENCES:
Full documentation: http://www.oracleodu.com/soft/ODUUserGuide_en.pdf
Real success story: http://www.basecare.dk/6storage/983/7/recovering_lob-index_block_corruption.pdf
