Recover Truncated Table Using Flashback Database Technique

In this article we will demonstrate how to flashback a database and recover the truncated table without loosing data. 

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.