Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: FLASHBACK: Oracle 10G enhancements Jump to:  
Category: >> Oracle PL/SQL >> FLASHBACK: Oracle 10G enhancements Bookmark and Share

<< lastnext >>

Snippet Name: FLASHBACK: Oracle 10G enhancements

Description: 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

Also see:
» FLASHBACK: AS OF clause
» FLASHBACK: SYS commands and queries
» FLASHBACK: Initialization Parameters
» FLASHBACK: Syntax Elements

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 13th, 2009

/*
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.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 247 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?