In other words we will be performing the following steps to recover a truncated table:
- Simulate table truncate
- Make note of the table truncate time
- Flashback the database before truncate,
- Open the database in read only
- Export the table using traditional export utility
- Shutdown the database
- Open the database with reset logs option
- Import the table data
- Verify the table data
Prerequisites
- Database must in using Fast Recovery Area
- Database must have flashback set to ON
Steps to perform flashback a database to recover the truncated table without loosing data:
Step 1: Connect to the database and make a note of the current time
SQL> alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
Session altered.
SQL> set lines 200
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL
————— —————- —————————————————————- —————– —————– ———————————————— ————
THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PEN
———- —————————- ———————————————————— —————————————- ————
DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE BLOCKED
——————————————————————– ———————————————————————— ———————————— ————
1 orcldb1 racnode1 11.2.0.4.0 30/11/17 07:33:10 OPEN NO
1 STARTED ALLOWED NO
ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL> select sysdate from dual;
SYSDATE
—————–
30/11/17 07:34:30
Step 2: Identify a table for testing
SQL> create table SCOTT.SALES as select * from SCOTT.WAIVER;
Table created.
SQL> select sysdate from dual;
SYSDATE
—————–
30/11/17 07:35:07
SQL> select count(*) from SCOTT.SALES;
COUNT(*)
———-
35268
Step 3: Truncate the table
SQL> select sysdate from dual;
SYSDATE
—————–
30/11/17 07:36:20
SQL> truncate table SCOTT.SALES;
Table truncated.
SQL> select count(*) from SCOTT.SALES;
COUNT(*)
———-
0
Step 4: Shutdown the database and start it in mount state
racnode1-orcldb1 {/home/oracle}: srvctl status database -d orcldb
Instance orcldb1 is running on node racnode1
Instance orcldb2 is running on node racnode2
racnode1-orcldb1 {/home/oracle}: srvctl stop database -d orcldb
racnode1-orcldb1 {/home/oracle}: srvctl status database -d orcldb
Instance orcldb1 is not running on node racnode1
Instance orcldb2 is not running on node racnode2
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3140026368 bytes
Fixed Size 2163800 bytes
Variable Size 1996491688 bytes
Database Buffers 1107296256 bytes
Redo Buffers 34074624 bytes
Database mounted.
Step 5: Flashback database as shown below
SQL> alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
Session altered.
SQL> flashback database to timestamp to_date(’11/30/17 07:36:00′,’mm/dd/yy hh24:mi:ss’);
Flashback complete.
>>>>>>>>….. alert log ……..>>>>>>>>>>>>>>>>>
flashback database to timestamp to_date(’11/30/17 07:36:00′,’mm/dd/yy hh24:mi:ss’)
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Fast Parallel Media Recovery enabled
parallel recovery started with 3 processes
Recovery of Online Redo Log: Thread 1 Group 3 Seq 393 Reading mem 0
Mem# 0: /oradata1/orcldb/redo_t01_g03.log
Incomplete Recovery applied until change 711150562 time 11/30/2012 07:36:01
Flashback Media Recovery Complete
Completed: flashback database to timestamp to_date(’11/30/17 07:36:00′,’mm/dd/yy hh24:mi:ss’)
>>>>>>>>>>>>>>>>…….>>>>>>>>>>>>
Step 6: Open the database in read only mode and verify the table
SQL> alter database open read only;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— —————————————- —————————————————————-
orcldb READ ONLY PRIMARY
SQL> select count(*) from SCOTT.SALES;
COUNT(*)
———-
35268
Step 7: Perform table export using traditional export utility
racnode1-orcldb1 {/oradata1}: exp system file=test.dmp log=test.log tables=SCOTT.SALES compress=y
Export: Release 11.2.0.4.0 – Production on Fri Nov 30 07:56:59 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
Current user changed to SCOTT
. . exporting table SALES 35268 rows exported
Export terminated successfully without warnings.
>>>>>>>>>>>>>>>>…….EXPORT END>>>>>>>>>>>>>>>>>>>>>>>
Step 8: Shutdown the database, recover database and open using resetlogs option
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3140026368 bytes
Fixed Size 2163800 bytes
Variable Size 1979714472 bytes
Database Buffers 1124073472 bytes
Redo Buffers 34074624 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
Step 9: Verify the database
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— —————————————- —————————————————————-
orcldb READ WRITE PRIMARY
Step 10: Import the table data using import utility
SQL> select count(*) from SCOTT.SALES;
COUNT(*)
———-
0
racnode1-orcldb1 {/oradata1}: imp system file=test.dmp log=imp_test.log tables=SALES fromuser=SCOTT touser=SCOTT ignore=y
Import: Release 11.2.0.4.0 – Production on Fri Nov 30 08:02:05 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V11.01.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT’s objects into SCOTT
. . importing table “SALES” 35268 rows imported
Import terminated successfully without warnings.
>>>>>>>>>>>>. IMPORT END ….>>>>>>>>>>>>>>>>>>>.
Step 11: Verify the table data
SQL> select count(*) from SCOTT.SALES;
COUNT(*)
———-
35268
Conclusion
In this article we have learned how to recover a truncated table using flashback database technology without loosing the database. Using flashback database is one of the fastest and easiet method to as it doesn’t require database restore from backup.