Thinking Out Loud

February 18, 2010

flashback database

Filed under: oracle — mdinh @ 5:39 pm

Once again, flashback saved the day.

A table with million rows was accidentally dropped, not by me, with cascade constraint purge option.

Ideas starting flying around as how to recover the data.

Keep in mind that RMAN is not being used and only full database hot backup using snapshot technology is being utilized.

Options suggested were:

datapump: I can do that but last backup was 18 hours ago and lots of missing data.

TSPITR: without RMAN would be ugly and time consuming for the database size

standby: desperate measures to retrieve data from standby. I know this would not work because I implemented physical standby using real-time apply.

So what was done?

Because we are using EMC BCV and Oracle flashback technology, it was possible to create a backup of production and flashback the database.

Export the table from backup and import table to production.

I like to use SCN for flashback and here are the steps.

Find SCN for timestamp:

select timestamp_to_scn(to_timestamp('17/02/2010 14:24:54','DD/MM/YYYY HH24:MI:SS')) as scn from dual;

Mount the database:

Flashback database:

Open database resetlogs:

Recreate table and data using database link.

Relatively quick, easy, painless process.

Here are the details for a time in the past that I did this.

SYS@restorezone:PRIMARY> select scn_to_timestamp(32318874893) as timestamp from dual;

16-OCT-08 PM

SYS@restorezone:PRIMARY> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@restorezone:PRIMARY> startup mount;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2133936 bytes
Variable Size 264138832 bytes
Database Buffers 805306368 bytes
Redo Buffers 2162688 bytes
Database mounted.

SYS@restorezone:PRIMARY> flashback database to scn 32318874893;
Flashback complete.

SYS@restorezone:PRIMARY> alter database open resetlogs;
Database altered.


I apologizes for the funky format as I am not  very adept with wordpress.

Blog at