Flashback table to before / Restore table

How can I restore a table to a before state in a Oracle pluggable database (PDB)?

  • If Flashback is enabled, you will use this amazing feature and flaskback the table.
  • From Oracle 12c you can also restore a table with RMAN

 

Flashback table

If the timeframe allows it and flashback enabled.

Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.

PRE-REQUISITE: You must enable row movement for the table to carry out flashback operation on it.

ALTER TABLE my-table-to-flaskback ENABLE ROW MOVEMENT;

Now, let’s Flaskback the table:

sqlplus / as sysdba
alter session set container=my-pdb;
create table my-schema.my-table-08312018 as select * from my-schema.my-table as of timestamp to_timestamp('31-AUG-2018 10:45:52','DD-Mon-YYYY hh24:MI:SS');

FLASHBACK TABLE my-table
[TO BEFORE DROP] |
[TO TIMESTAMP time_stamp] |
[TO SCN scn_number] |
[ENABLE TRIGGERS | DISABLE TRIGGERS];

With flashback table you can retrieve removed tables from the database, dropped using DROP and TRUNCATE commands.

 

Restore table from RMAN backup (until Oracle 12c)

RMAN enables you to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. You can use previously-created RMAN backups to recover tables and table partitions to a specified point in time.

-- create directory /u01/vincent. You will need space for the tablespace of the table and also for system, sysaux
recover table my-schema.my-table OF PLUGGABLE DATABASE my-pdb until time "to_date('08/31/2018 10:45:00','mm/dd/yyyy hh24:mi:ss')"
auxiliary destination '/u01/vincent' REMAP TABLE my-schema.my-table:my-table_08312018;

With this command we restore the SYSTEM, SYSAUX, and UNDO tablespaces and the tablespace that contain the data for this table. Having restored the database to the appropriate point in time, you can use Oracle Data Pump expdp to export the table.  And you import them into the original database, again using Oracle Data Pump impdp.

Have a nice day!

Author: Vincent Fenoll – Oracle DBA Montreal

Summary
Article Name
Flashback table to before / Restore table - Oracle script
Description
If Flashback is enabled, you will use this amazing feature and flaskback the table.From Oracle 12c you can also restore a table with RMAN.
Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.