Flashback Database (Alternative to Point-In-Time Recovery)
Oracle Flashback Database, lets you quickly recover the entire database from logical data corruptions or user errors.
To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database.
Once you set these parameters, From that time on, at regular intervals, the database copies images of each altered block in every datafile into flashback logs stored in the flash recovery area. These Flashback logs are use to flashback database to a point in time.
Enabling Flash Back Database
Step 1. Shutdown the database if it is already running and set the following parameters
DB_RECOVERY_FILE_DEST=/d01/ica/flasharea
DB_RECOVERY_FILE_DEST_SIZE=10G
DB_FLASHBACK_RETENTION_TARGET=4320
(Note: the db_flashback_retention_target is specified in minutes here we have specified 3 days i.e. 3x24x60=4320)
Step 2. Start the instance and mount the Database.
SQL>startup mount;
Step 3. Now enable the flashback database by giving the following command
SQL>alter database flashback on;
Now Oracle start writing Flashback logs to recovery area.
To how much size we should set the flash recovery area.
After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
This will show how much size the recovery area should be set to.
How far you can flashback database.
To determine the earliest SCN and earliest Time you can Flashback your database, give the following query:
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
Example: Flashing Back Database to a point in time
Suppose, a user erroneously drops a schema at 10:00AM. You as a DBA came to know of this at 5PM. Now since you have configured the flashback area and set up the flashback retention time to 3 Days, you can flashback the database to 9:50AM by following the given procedure
1. Start RMAN
$ rman target /
2. Run the FLASHBACK DATABASE command to return the database to 9:59AM by typing the following command
RMAN>FLASHBACK DATABASE TO TIME
timestamp('2007-06-21 09:59:00');
or, you can also type this command.
RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-8/24);
3. When the Flashback Database operation completes, you can evaluate the results by opening the database read-only and run some queries to check whether your Flashback Database has returned the database to the desired state.
RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';
At this time, you have several options
Option 1:-
If you are content with your result you can open the database by performing ALTER
DATABASE OPEN RESETLOGS
SQL>ALTER DATABASE OPEN RESETLOGS;
Option 2:-
If you discover that you have chosen the wrong target time for your Flashback Database operation, you can use RECOVER DATABASE UNTIL to bring the database forward, or perform FLASHBACK DATABASE again with an SCN further in the past. You can completely undo the effects of your flashback operation by performing complete recovery of the database:
RMAN> RECOVER DATABASE;
Option 3:-
If you only want to retrieve some lost data from the past time, you can open the database read-only, then perform a logical export of the data using an Oracle export utility, then run RECOVER DATABASE to return the database to the present time and re-import the data using the Oracle import utility
4. Since in our example only a schema is dropped and the rest of database is good, third option is relevant for us.
Now, come out of RMAN and run export utility to export the whole schema
$ exp userid=system/manager file=scott.dmp owner=SCOTT
5. Now Start RMAN and recover database to the present time
$rman target /
RMAN> RECOVER DATABASE;
6. After database is recovered shutdown and restart the database in normal mode and import the schema by running IMPORT utility
$imp userid=system/manager file=scott.dmp
Posts
0 comments:
Post a Comment