Restore Point in Flashback Database

Remember the concept of savepoints in SQL? In a transaction, you can create a savepoint, make some modifications, create another savepoint, and so on. If the changes are not what you expected, all you have to do is roll them back to a specific savepoint.

Now pan over to a new functionality introduced in Oracle Database 10g, Flashback Database, which allows you to rewind the database to a previous point in time. Wouldn't it be nice to have functionality similar to savepoint in this situation—that is, to be able to rewind to a specific named point, not just a point in time?

In Oracle Database 10g Release 2, you can do that using a new functionality called restore points. Here's how it works. Suppose you have a long month-end processing involving several batch programs you have to run sequentially. Here is the sequence of events:

1. Create a restore point rp1
2. Run batch job 1
3. Create a restore point rp2
4. Run batch job 2

and so on. The batch job 2 fails in the middle of execution, and you need to take the database to a consistent state. You don't have to take it all the way to the beginning of the run. Because the restore point rp2 was created before the execution of the batch job, you can simply flashback the database to that restore point.

You create a restore point with

create restore point before_monthend_200503;

Restore point BEFORE_MONTHEND_200503 is now created based on the current database time and SCN. If you want to ensure that the database can be flashed back to a particular restore point, you can specify a guarantee by creating guaranteed restore points as shown below:

create restore point before_monthend_200503
guarantee flashback database;

You can confirm the existence of this restore point by SELECTing from a dynamic performance view V$RESTORE_POINT:

SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------
NAME
---------------------------------------------------

1429811 1 YES 8192000
27-MAR-05 05.18.39.000000000 PM
BEFORE_MONTHEND_200503

Later when you want to flashback the database to that restore point, you could simply issue:

flashback database to restore point before_monthend_200503;

If you examine the alert log, it will show a line similar to:

Media Recovery Applied UNTIL CHANGE 1429814

Restore points—especially guaranteed restore points—are quite useful in many database-related tasks. A good example is QA databases, where you may want to establish a restore point, run some tests, and flashback to the restore point to make the database look as if nothing happened. Then you can perform another round of testing and again restore it to the restore point.

Peek into the Flash Recovery Area

You may have configured Flash Recovery Area to back up different types of files. But how do you know what types of backups are available there?

A new view, V$FLASH_RECOVERY_AREA_USAGE, shows what's available in the flashback area.

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG .02 .02 1
BACKUPPIECE 68.98 1.02 10
IMAGECOPY 0 0 0
FLASHBACKLOG .95 0 3

Using this view you can immediately see what kind of files are available in the Flash Recovery Area. It only shows a percentage however, so how do you determine actual values? Simply query the view $RECOVERY_FILE_DEST.

SQL> select * from V$RECOVERY_FILE_DEST;

NAME
----------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/home/oracle
2147483648 1502122496 22201856 14

This query shows that the total size of the recovery area is 16384000. Flashback logs occupy 0.95% of the column SPACE_LIMIT as shown in the previous query, so you can calculate the actual size of the space occupied. It also shows you how much space can be reclaimed from the different types of backups in the Flash Recovery Area. For instance, you can reclaim 1.02% of the space occupied by backup pieces, perhaps due to obsolete backups. Using this view you can make intelligent predictions about Flash Recovery Area usage and sizing.

Source: Oracle 10g R2 Top Features for DBAs

Flashback Drop/Flashback Table

Using Flashback Drop
Flashback drop is the process of saving a copy of the dropped database object and dependent objects in the Recycle Bin so that these objects can be recovered if necessary. The dropped database object is not removed from the database until the Recycle Bin is emptied. This provides a mechanism for the user to recover an accidental drop of a table. In addition, a Flashback Drop does not impact other users in the database to restore a dropped table, whereas incomplete recovery has database-wide impacts because there may be multiple database objects involved in a tablespace or datafile.

SQL> flashback table t1 to before drop;

Flashback Drop is designed to temporarily store the dropped object and dependent objects for a period of time, which can be seen in Recycle Bin.

In order to query the object in the Recycle Bin, you must have the privileges. You also need the FLASHBACK privilege.

SQL> flashback table “BIN$9GjGHKB754HGC==$0” to before drop rename to t2;
SQL> select * from t2;

Using Flashback Table
Flashback Table is a Flashback Technology that allows you to recover a table or set of tables to a specific point-in-time without performing an incomplete recovery. All dependent objects are also recovered when using Flashback Table. Benefits over incomplete recovery:

• Much faster and easier to use
• Flashback Table does not impact the availability of the database
• DBA is not required to perform Flashback Table, so users can quickly recover from logical corruptions

Like other Flashback Technologies, Flashback Table is based on undo data. The privilege required to use Flashback Table is the system privilege FLASHBACK ANY TABLE or FLASHBACK TABLE. You must also grant SELECT, INSERT, DELETE, and ALTER object privileges to user performing Flashback Table.

There are two main clauses that are used with Flashback Table:

• TO SCN clause
• TO TIMESTAMP clause

NOTE: Flashback Table must have ROW MOVEMENT enabled with the following command: ALTER TABLE table_name ENABLE ROW MOVEMENT.

Ex:
SQL> alter table t1 enable row movement;
SQL> select current_scn from v$database;
SQL> update t1 set salary=500000 where employee=’jones’;
SQL> commit

SQL> flashback table t1 to scn 1071333;

NOTE: Triggers are disabled by default during Flashback Table process. Triggers can be enabled with ENABLE TRIGGERS option on the FLASHBACK TABLE command.
SQL> flashback table table_name to scn 168879 enable triggers;

Configuring Flashback Database

In order to use Flashback Database, the database must have multiple features configured prior to configuring Flashback Database. Database must have ARCHIVE LOG enabled, and flash recovery area must be configured to store Flashback Database logs.
First you can configure Flashback Database, so the database must be shutdown. Next, the database must be started in MOUNT mode. Then, the parameter DB_FLASHBACK_RETENTION_TARGET can be set to desired value, in minutes. This value determines how far back in time you can flash back the database. Next, the Flashback Database can be enabled with ALTER DATABASE FLASHBACK ON command. Finally, the database can be opened for normal use.
SQL> connect / as sysdba
SQL> startup mount
SQL> alter system set db_flashback_retention_target=4320; # for 3days
SQL> alter database flashback on;
SQL> alter database open;

Using Flashback Database with RMAN
Flashback database can be used with RMAN to perform recoveries. You need to get either OLDEST_FLASHBACK_SCN or OLDEST_FLASHBACK_TIME from V$FLASHBACK_DATABASE_LOG view. This will allow you to utilize the TO SCN or TO TIME clause in the FLASHBACK DATABASE clause. There is also TO SEQUENCE clause, which uses redo log sequence and thread to perform recovery.

Ex: Performing Flashback Database recovery to a SCN

SQL> connect / as sysdba
SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

Next, shutdown and start the instance in MOUNT mode
SQL> shutdown
SQL> startup mount

> rman
RMAN> connect target
RMAN> flashback database to scn=689316;

Finally, open database with RESETLOGS option
SQL> alter database open resetlogs;

The V$FLASHBACK_DATABASE_LOG dynamic view is useful for both TO SCN and TO TIME recoveries.

Monitoring Flashback Database
Flashback database can be monitored using few dynamic views: V$DATABASE, V$FLASHBACK_DATABASE_LOG, and V$FLASHBACK_DATABASE_STAT.
V$DATABASE displays if the Flashback Database is on or off.
SQL> select flashback_on from v$database;

The V$FLASHBACK_DATABASE_LOG view is new to 10g and was created to support Flashback Database.
SQL> select oldest_flashback_scn, oldest_flashback_time, retention_target, estimated_flashback_size from v$flashback_database_log;


The V$FLASHBACK_DATABASE_STAT is used to monitor the overhead of maintaining the data in the Flashback Database logs.
SQL> select * from v$flashback_database_stat;

Using Flashback Database with Enterprise Manager

Configuring Flashback Database with EM
To configure Flashback database with EM, you have to log in with SYSDBA account. Go to Maintenance screen -> choose Configure Recovery Settings under Backup/Recovery section.

Using Flashback Database with EM
Maintenance -> Perform Recovery

On the Perform Recovery: Type page, specify the type of recovery.

The database will need to be shutdown and mounted to proceed with the recovery. After 2 or 3 min, click Refresh button. An information screen appears that tells the database is unavailable.

Click Perform Recovery and choose type of recovery you need to perform.

Monitoring Flashback Database with EM
Maintenance -> Configure Recovery Settings

Flashback Versions Query Walkthrough

SQL> create table digits (id number(2), description varchar(20));

Table created.

SQL> insert into digits values (1, 'ONE');

1 row created.

SQL> update digits set description = 'TWO' where id=1;

1 row updated.

SQL> insert into digits values (2, 'TWO');

1 row created.

SQL> commit;

Commit complete.

SQL> delete from digits;

2 rows deleted.

SQL> select description from digits versions between timestamp minvalue and maxvalue;

DESCRIPTION
--------------------
TWO
TWO