FLASHBACK: Oracle 10G enhancements

Submitted by:Jhon Brain

Date added:12 July, 2015

Category:SQL

Oracle 10G has enhanced the flashback feature further and has turned it into a much more powerful feature by introducing a number of additional function. Some of the more common ones are discussed here.

- Flashback Table
- Flashback Drop
- Flashback Database

Tags: flashback table , flashback drop , flashback database

Code Snippet:

/*
Flashback Table
Just like the flashback query helps retrieve rows of a table,
FLASHBACK TABLE helps restore the state of a table to a certain
point in time even if a table structure changed has occurred since
then. The following command will take us to the table state at the
specified timestamp.
*/

FLASHBACK TABLE EMPLOYEE TO
TIMESTAMP ('15-SEP-08 8:50:58','DD-MON-YY HH24: MI: SS');

/*
This command will not only restore the tables, but also the
associated objects like indexes, constraints etc.
*/


/*
Flashback Drop
Oracle 10g introduces the function "Flashback drop". If a DROP
TABLE command has been issued for the table EMPLOYEE, we can
still restore the entire table by issuing the following command:
*/

FLASHBACK TABLE EMPLOYEE TO BEFORE DROP;

-- Recovering a dropped table doesn't any easier than this!


/*
Flashback database
Flashback Database requires the creation and configuration of
an Oracle Flash Recovery Area before this feature can be used.

"Flash Recovery Area", created by the DBA, is the allocation of
space on the disk to hold all the recovery related files (Flashback
Logs, Redo Archive logs, RMAN backups, and copies of control files).

Use the initialization parameters db_recovery_file_dest and
b_recovery_file_dest_size to set the destination and the size
of the recovery area.

Set Flashback to enabled to make Oracle database enter the
flashback mode. The database must be mounted as Exclusive and
not open. The database also has to be in the ARCHIVELOG MODE
before we can use this feature:
*/

ALTER DATABASE ARCHIVELOG;

-- start the database in EXCLUSIVE mode:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT EXCLUSIVE


-- enter flashback mode:

ALTER DATABASE FLASHBACK ON;


-- issue the Flashback command and rewind the database to
-- the state it was in one hour ago.

Flashback database TO TIMESTAMP sysdate-(1/24);

-- after the system comes back with FLASHBACK COMPLETE,
-- open the database:

ALTER DATABASE OPEN RESETLOGS;

-- the database is now restored.

-- note that we have the option
-- of using SCN instead of timestamp.
 
 

Comments