Using FLASHBACK to rollback a TRUNCATE

This scenario will show how we can use a combination of FLASHBACK database and also recovery to take a database back in time to undo a TRUNCATE operation and then roll forward the database after the flashback operation to bring it to the current point in time.

INSERT ROWS INTO TABLE MYOBJ – THIS WILL BE TRUNCATED

SQL> insert into scott.myobj select * from all_objects;

50496 rows created.

SQL> /

50496 rows created.

SQL> select count(*) from scott.myobj;

  COUNT(*)
----------
    100992

OBTAIN THE CURRENT SCN – FLASHBACK WILL HAPPEN TO THIS SCN

SQL> select current_scn from v$database;


          CURRENT_SCN
---------------------
          15633908021

TRUNCATE THE TABLE

SQL> truncate table scott.myobj;

Table truncated.

SQL> select count(*) from scott.myobj;

  COUNT(*)
----------
         0

AT THE SAME TIME OTHER CHANGES ARE HAPPENING IN THE DATABASE AND THESE CHANGES WILL BE RECOVERED AFTER THE FLASHBACK IS DONE

SQL> insert into scott.myobj2 select * from scott.myobj2;

356874 rows created.

SQL> /

713748 rows created.

SQL> commit;

Commit complete.

SHUTDOWN THE DATABASE AND PERFORM THE FLASHBACK TO THE SCN BEFORE THE TRUNCATE WAS DONE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  469762048 bytes
Fixed Size                  2084880 bytes
Variable Size             377491440 bytes
Database Buffers           83886080 bytes
Redo Buffers                6299648 bytes
Database mounted.



SQL> FLASHBACK DATABASE TO SCN 15633908021;

Flashback complete.

OPEN THE DATABASE IN READ ONLY MODE AND EXPORT THE TABLE THAT WAS TRUNCATED EARLIER. THIS TABLE WILL BE IMPORTED AFTER THE RECOVERY IS DONE

SQL> alter database open read only;

Database altered.


SQL>  select count(*) from scott.myobj;

  COUNT(*)
----------
         100992

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
crashdb:/u03/oradata/crashdb/arch> exp file=scott.dmp tables=myobj

Export: Release 10.2.0.4.0 - Production on Fri Feb 6 09:53:00 2009

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


Username: scott
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                          MYOBJ          100992 rows exported
Export terminated successfully without warnings.

NOW SHUTDOWN THE DATABASE,STARTUP MOUNT AND PERFORM THE RECOVERY

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  696254464 bytes
Fixed Size                  2086616 bytes
Variable Size             184551720 bytes
Database Buffers          503316480 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

AS EXPECTED TABLE MYOBJ WHICH WAS TRUNCATED NOW AGAIN HAS 0 ROWS AFTER THE RECOVERY – WE CAN IMPORT THE DUMP WE TOOK AFTER THE FLASHBACK

SQL> select count(*) from scott.myobj;

  COUNT(*)
----------
         0

CONFIRM THAT OTHER COMMITTED CHANGES IN THE DATABASE HAVE BEEN RECOVERED

SQL> select count(*) from scott.myobj2;

  COUNT(*)
----------
  713748

The following two tabs change content below.

• Bio
• Latest Posts

Gavin Soorma

Latest posts by Gavin Soorma (see all)