Configure, Monitor and Use Oracle Database 11g Flashback

Configuring Flashback Database

Step # 1

Confirm that Flashback is enabled or not.

SQL> Select flashback_on from v$database;

FLASHBACH_ON
------------------------
NO

Step # 2

Ensure that the database is in archivelog mode. Archivelog mode is a prerequisite for enabling Flashback Database. Confirm this by querying the V$DATABASE view

SQL> Select Log_mode from v$database;

LOG_MODE
----------------------
NOARCHIVELOG

If the database is not in Archivelog mode then above result is displayed.
Step # 3

Now switch Database to Archivelog mode

SQL> Shutdown Immediate;
SQL> Startup mount;
SQL> Alter Database Archivelog;


SQL> Select Log_mode from v$database;

LOG_MODE
----------------------
ARCHIVELOG
Step # 4

Set up a flash recovery area.

The flash recovery area is the location for the flashback logs. You have no control over them other than setting the flash recovery area directory and limiting its size. It is controlled with two instance parameters: DB_RECOVERY_FILE_DEST specifies the destination directory; DB_RECOVERY_FILE_DEST_SIZE restricts the maximum amount of space in bytes that it can take up. Remember that the flash recovery area is used for purposes other than flashback logs, and it will need to be sized appropriately. For example,

SQL> Alter System Set db_recovery_file_dest='/opt/oracle/flash_recovery_area';
SQL> Alter System Set db_recovery_file_dest_size=8G;
Step # 5

Set the lifespan for the flashback retention target.

This setting is controlled by the DB_FLASHBACK_RETENTION_TARGET instance parameter, which specifies a time in minutes (the default is one day). The flashback log space is reused in a circular fashion, older data being overwritten by newer data. This parameter instructs Oracle to keep flashback data for a certain number of minutes before overwriting it:

SQL> Alter System Set db_flashback_retention_target=240;

It is only a target (four hours in this example), and if the flash recovery area is undersized, Oracle may not be able to keep to it. But in principle, you should be able to flash back to any time within this target.

Step # 6

Cleanly shut down and mount the database.

SQL> Shutdown Immediate;
SQL> Startup Mount;
Step # 7

Enable flashback logging. While in mount mode,

SQL> Alter Database flashback on;

This will start the RVWR process and allocate a flashback buffer in the SGA. The process startup will be automatic from now on.
Step # 8

Open the database.

SQL> Alter Database open;

Logging of data block images from the database buffer cache to the flashback buffer will be enabled from now on.

Monitoring Flashback Database

1) The most basic level of flashback monitoring is to confirm that it is actually enabled:

SQL> Select flashback_on from v$database;

FLASHBACH_ON
------------------------
YES

2) To monitor the current flashback capability and estimate the space needed for flashback logs to meet your target issue the following query.

SQL> Select Retention_target, Estimated_flashback_size, Flashback_size from v$Flashback_database_log;

RETENTION_TARGET       ESTIMATED_FLASHBACK_SIZE       FLASHBACK_SIZE
------------------------------         --------------------------------------------            --------------------------
14510 -----------------------          42558554544          ---------------------------- 60056544

3) This query shows exactly which SCN and time the flashback logs could take the database back to. If the flash recovery area is sized appropriately and the retention target is realistic, then there will be a sensible relationship between the time shown in this query and the current time less the retention target.

SQL> Select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN -----   OLDEST_FLASHBACK_TIME
--------------------------------------- -          ----------------------------------------
570695 ---------------------------------          16-JUN-2009


4)

SQL> Select end_time, flashback_data, db_data, redo_data from v$flashback_database_stat;

END_TIME         FLASHBACK_DATA    DB_DATA                REDO_DATA
--------------      ---------------------------             --------------                ------------------
16-JUN-09 --------966656 --------------1114112 39-----                   812695

5) The size of the flashback buffer is outside the DBA’s control, but you can see the current size.

SQL> Select * from v$sgastat where name like 'flashback%';

POOL ------                    NAME                       -----------------------------------------BYTES
----------                  -----------------------------------------------------                           -------------
Shared pool ----         flashback generation buff                                ------------3981264
Shared pool -----        flashback_marker_cache_si                            ---------9196

Using Flashback Database

The method of use of Flashback is:-
1. Shut down the database.
2. Mount the database.
3. Flash back to a time, an SCN, or a log switch sequence number.
4. Open the database with RESETLOGS.

A flashback operation will proceed completely automatically.

EXAMPLE

It is 16 JUNE 2009. At about 10:00 a junior DBA dropped an important schema on the production database, when s/he thought s/he was logged on to the test database. The error is noticed within ten minutes, but it is a big, busy database in a call center, used for taking orders, and every second of processing counts. The first step is to shut down the database:

SQL> shutdown abort;

There is no point in using any other type of shutdown; all work in progress is going to be lost anyway, and you need to minimize the downtime. Then take the database back to 10:00 as follows:

SQL> startup mount;

SQL> flashback database to timestamp to_timestamp('16-06-09 10:00:00','dd-mm-yy hh24:mi:ss');

SQL> alter database open read only;

Note that unlike RECOVER DATABASE UNTIL TIME, this command is sensitive to NLS settings for the timestamp format. While the database is in READ ONLY mode, you can run a query against the dropped schema. If you discover that the schema is still there, perhaps you can recover a bit more user data:

SQL> shutdown abort;
SQL> startup mount;
SQL> recover database until time '2009-06-16:10:02:00';
SQL> alter database open read only;

You run your test query again, and you discover that after recovering two more minutes of data the schema is gone: it must have been dropped between 10:00 and 10:02. So you split the difference:

SQL> shutdown abort;
SQL> startup mount;
SQL> flashback database to timestamp to_timestamp('16-06-09 10:01:00','dd-mm-yy hh24:mi:ss');

Or

SQL> flashback database to scn=2728665;

Or

SQL> flashback database to sequence=2123 thread=1;

SQL> alter database open read only;
SQL> shutdown abort;
SQL> alter database open resetlogs;