//
you're reading...
Administration, Flashback

Flashback Database

Before Oracle 10g, if the database suffered some logical data corruptions or user errors, your choice would be Point-in-time recovery. That means restoring datafiles from backup and after that using archived redo logs to applying changes. In short, it was a costly and time-consuming process.

Flashback Database is another flashback technique that can be used as an alternative to perform incomplete recovery. It enables you to return the entire database to a specified previous point in time in the recent past. This operation has the same result as the RMAN Point-in-time recovery, but it is much faster because it does not require restoring all databases’ datafiles from backup and requires applying fewer changes from the archived redo logs. So it is the size of the error that matters [number of changes you want to undo] not the size of database.

Flashback database uses Flashback logs stored in the Flash/Fast Recovery Area [FRA]. These logs are used to perform Flashback database operation. A FRA must be configured for database because database cannot create Flashback logs anywhere else. Flashback logs are generated by database at a regular interval and are not archived, multiplexed or backed up to the disk.

NOTE:
Flashback Database is only helps you to recover from logical corruption or user errors [dropped schemas, a table is truncated or a transaction is performed partially changes] that affected the entire database. In case of physical media corruption or loss of data files, tradition complete recovery is required.

Flashback database is not enabled by default, when enabled image of the modified blocks are copied from db buffer cache to new memory area within SGA called flashback buffer. RVWR – Recovery Writer process is used to copies modified blocks from the flashback buffer to flashback database logs. Unlike redo log which logs all the changes in the database, Flashback Database logs contain a complete “before” image of physical database blocks. But not every change is logged as this would be the overhead in terms of memory usage and the amount of extra disk I/O required, so only as many as blocks are copied to flashback buffer such that the performance of database is not impacted with this.

A user must have a SYSDBA privilege to perform flashback database operation and FRA must have been configured for the database.

Configuring the Flashback Database

First step to ensure that database is in ARCHIVELOG mode. If not then it must be switched to ARCHIVELOG mode. You can use one of the following SQL to check database log mode.

SQL> archive log list
Database log mode              Archive Mode 
Automatic archival             Enabled 
Archive destination            e:\app\Madhurendra\arch\O11R2
Oldest online log sequence     26 
Next log sequence to archive   28 
Current log sequence           28

OR 

SQL> SELECT log_mode FROM v$database;
LOG_MODE
-------------
ARCHIVELOG

If your database is in NOARCHIVE log mode, you can perform following steps to bring db to ARCHIVELOG mode.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Now configure Flash/Fast Recovery Area for database, if not already configured.

Use DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST initialization parameter in init.ora file to configure a flash recovery area.

You can also use ALTER SYSTEM statement to configure FRA if you do not want to specify in init.ora file

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=8G;
SQL> ALTER SYSTEM SET db_recovery_file_dest = <specify location for the FRA>;

In my case

SQL> show parameter DB_RECOVERY_FILE_DEST 
 
NAME                            TYPE          VALUE 
---------------------------     -----------   ------------------------------ 
db_recovery_file_dest           string        E:\app\Madhurendra\arch\O11R2
db_recovery_file_dest_size      big integer   8G

Choose a Flashback Retention Time with specifying value for the DB_FLASHBACK_RETENTION_TARGET parameter.

SQL> ALTER SYSTEM SET db_flashback_retention_target=2880;

Keep in mind the duration we specify is in minute. In this case 2880 minutes [2 days]. This parameter specifies how far back you can rewind a database with Flashback Database.

Now shut down and restart the database in mount or mount exclusive mode, enable the Flashback Database feature and open the database with ALTER DATABASE OPEN command.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Now check if Flashback Database feature is enabled or not by querying V$DATABASE view.

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
---------------
YES

If you do not want to perform these entire steps using SQL command line tool, use Oracle Enterprise Manager [OEM] Database Control to enable Flashback feature.


To place Database in ARCHIVELOG Mode using OEM 11g Database Control:

a. Click on the Availability tab on the OEM DB Control Home Page.
b. Click on Recovery Settings under the Setup which is under the Backup/Recovery section.
c. Now check the ARCHIVELOG Mode checkbox within the Media Recovery section to place the database in ARCHIVELOG mode.
d. Click the Apply button to save and Restart the database with specifying your Host and Database Credentials.

To Configure Fast/Flash Recovery Area:
a. Click on the Availability tab on Home page.
b. Click on Recovery Settings under the Setup which is under the Backup/Recovery section.
c. Enter a Flash Recovery Area Location (DB_RECOVERY_FILE_DEST) and Flash Recovery Area Size within the Flash Recovery section.
d. Check the Enable Flashback Database check-box to enable flashback logging and enter a value for Flashback Retention Time.
e. Click the Apply button to save and Restart the database with specifying your Host and Database Credentials.

USING FLASHBACK DATABASE

SQL> CREATE TABLE user_object_info 
     AS 
     SELECT object_id, object_name, object_type 
     FROM user_objects;

Table created.

SQL> SELECT count(*) FROM user_object_info;
COUNT(*)
----------
39

SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
--------------
1362393

SQL> INSERT INTO user_object_info SELECT * FROM user_object_info;
39 rows created.

. . . . .

SQL> SELECT count(*) FROM user_object_info;
COUNT(*)
----------
4992

SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
--------------
1364231

SQL> TRUNCATE TABLE user_object_info;
Table truncated.

SQL> SELECT count(*) FROM user_object_info;
COUNT(*)
----------
0

After performing all these operations we want flashback our database to the SCN 1362393. This means flashing back to the time when user_object_info table held 39 rows.

First shutdown the database and restart in the mount mode. Database must be running in the mount mode in order to use FLASHBACK DATABASE.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1376520 bytes
Variable Size             301993720 bytes
Database Buffers          335544320 bytes
Redo Buffers                5554176 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO SCN 1362393;
Flashback complete.

SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.

/*Open the Database in Read Only mode to ensure that you have flashed back the table to the correct SCN*/

SQL> SELECT count(*) FROM user_object_info;
COUNT(*)
----------
39

Once you are confirm that the corruption have been reversed, shutdown the database, start in Mount mode and open database with RESETLOGS option.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1376520 bytes
Variable Size             301993720 bytes
Database Buffers          335544320 bytes
Redo Buffers                5554176 bytes
Database mounted.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

If you try open database with using RESETLOGS you will likely to encounter the following error message

SQL> ALTER DATABASE OPEN;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

You can disable FLASHBACK DATABASE feature by executing ALTER DATABASE FLASHBACK OFF. Shutdown the db, restart in mount or mount exclusive mode and enter the ALTER DATABASE FLASHBACK OFF command.

NOTE:
• FLASHBACK DATABASE feature requires database to running in ARCHIVE LOG mode.

• It cannot be used for physical media failure or in the case of data file deletion.

• It can be possible that you want to flashback database to a particular time and the nearest flashback logs were written at few minute before. In that case you have to apply changes from online redo files or archived redo logs to cover these time gaps.

• Not possible to flashback the database to an SCN prior to the earliest available SCN available in the flashback logs.

• FLASHBACK DATABASE operation can be performed on SQL* Plus as well as on RMAN with same syntax with minor changes.

• You can check the size of flashback buffer by querying the V$SGASTAT view:

SELECT * FROM v$sgastat WHERE name LIKE 'flashback%';

• Use V$FLASHBACK_DATABASE_LOG and V$FLASHBACK_DATABASE_STAT to monitor current flashback capability and estimate the space needed for and flashback logs the I/O overhead of logging flashback data respectively.

• This retention target specifies how far back you can rewind a database with Flashback Database. But Oracle does not guarantee that you can flash back your database to the time that set in the DB_FLASHBACK_RETENTION_TARGET parameter. It could be possible if FRA is running low on free space. In this case Oracle will remove some older flashback logs in order to make space for archived redo logs or backup related files.

You can find more information about Flashback Database in Oracle Database 11g Backup and Recovery guide.

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: