Using Flashback Database - Oracle

You can use Oracle Database 10g’s Flashback Database features from three different tools: SQL*Plus, RMAN, and OEM. Let’s look at some basic requirements for using Oracle Flashback Database.

Basic Flashback Database Use Requirements - To use Flashback Database, the database must first be in archivelog mode. Also, you must have any Real Application Clusters databases mounted in exclusive mode. You must also have a flashback recovery area configured, as described earlier in this chapter (done by setting the db_recovery_file_dest and db_recovery_file_dest_size database parameters). The compatible parameter should be set to 10.0 or higher.

Jonathan Says… Oh dear, another Oracle process generating yet more I/O. You had better have a good reason for wanting flashback logging switched on. You should always be very reluctant about introducing expensive run-time features without some very solid justification.

Flashback Database from SQL*Plus To use Flashback Database from the SQL*Plus prompt, you must connect to the database by using the AS SYSDBA login. To enable Flashback Database from SQL*Plus, follow these steps:

1. Shut down the database.

2. Mount the database (if it is a Real Application Clusters database, mount it in exclusive mode).

3. Via the alter system command, set the database parameter db_flashback_ retention_target to a value that defines how far you want to flash back the database. Although this indicates to Oracle Database 10g how far you wish to flash back your database, the availability of undo back to that point in time is critical. Here is an example:
   
   alter system set
   db_flashback_retention_target=2880;
   
4. Enable Flashback Database with the alter database command using the new Oracle Database 10g syntax, flashback on:
   
   alter database flashback on;
   
5. Now, open the database with the alter database open command. You are now prepared to use flashback database! Make some changes to your database at this point. Once you have made some changes, record the current SCN (from the CURRENT_SCN column in the V$DATABASE view if you like!).

6. Shutdown and then mount the database again.

7. Use the Flashback Database to timestamp or the Flashback Database to scn command to enable Database Flashback:
   
   SQL> Flashback Database to timestamp (sysdate-2);
   SQL> Flashback Database to scn 2034455;
   
   
8. Open the database in read-only mode with the alter database open read only command, and make sure that you have flashed back the database to the point in time you are interested in.

9. Once you are sure the database is at the point in time that you wish to restore it to, open the database using the alter database open resetlogs command.

10. You can flash forward or backwards via the flashback database command as much as you like until you have opened the database with the alter database open resetlogs command. Once the alter database open resetlogs command has been issued, the database changes cannot be altered.

NOTE -- It’s always a good idea to record the SCN of the database before you perform a flashback operation. That way you can recover to that SCN if need be.

You can disable database flashback through the use of the alter database flashback off command (the database must be mounted and in exclusive mode). If you wish to disable flashback logging for a specific tablespace, use the alter tablespace flashback off command; you can re-enable flashback logging with the alter tablespace flashback on command. If you disable flashback for a specific tablespace, you must also take its associated datafiles offline.

Flashback Database from RMAN - In Oracle Database 10g RMAN supports the use of Flashback Database to restore the database back to a specific point in time, using the database SCN or log sequence number. Using the RMAN Flashback Database command allows you to restore the database as it looked at a specific point in time, as long as the undo is available to flash back to that point in time. This makes for much quicker restores. Here is an example of using RMAN to restore the database using Flashback Database (note that the database must be mounted, and not open, to execute these RMAN operations)

-- Flashback to a specific date and time RMAN> Flashback Database to_time = to_date('2003-12-01 01:05:00','YYYY-MM-DD HH24:MI:SS');

-- Flashback up to but not including a specific SCN RMAN> Flashback Database to scn = 302223;

-- flashback up to but not including a specific sequence -- number. RMAN> Flashback database until sequence=1022 thread=1;

Once RMAN has completed its Flashback Database operation, you can open the database in read-only mode and make sure it’s recovered to the point you want it recovered to. If it is, simply open the database using the alter database open resetlogs command to open the database for write operations.

This chapter is from Oracle Database 10g New Features, by Robert Freeman (McGraw-Hill/Osborne, 2004, ISBN: 0072229470). Check it out at your favorite bookstore today. Buy this book now.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus