Oracle 10g: New Flashback Features

by Srinivas Bolisetty

Introduction

Although the Flashback feature was introduced in Oracle9i, it was limited to Flashback Query only. Great improvements have been made in the Flashback functions in Oracle Database 10g. When user errors and logical corruptions occur in the 10g database, Flashback functionalities provide fast and flexible data recovery.

Here is a list of new Flashback features in Oracle Database 10g:
• Flashback Database
• Flashback Drop
• Flashback Table
• Flashback Version Query
• Flashback Transaction Query
In the first part of this series, we are going to introduce two Flashback features, Flashback Database and Flashback Drop.

Flashback Database

Flashback Database is faster than traditional point-in-time recovery. The traditional recovery method uses backups and redo log files; Flashback Database is implemented using a new type of log file called the Flashback Database log. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.

Flashback Database reduces the time required to recover the database to a point in time. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

RVWR Background Process
Enabling Flashback Database starts a new RVWR background process. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.

Figure 1: RVWR Background process and Flashback Database Logs.
The following list below shows all the background processes for “grid” instance:

$ ps -ef | grep grid
oracle 25124 1 0 16:32:05 ? 0:00 ora_s000_grid
oracle 25116 1 0 16:32:04 ? 0:00 ora_reco_grid
oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid
oracle 25112 1 0 16:32:04 ? 0:00 ora_ckpt_grid
oracle 25110 1 0 16:32:04 ? 0:00 ora_lgwr_grid
oracle 25108 1 0 16:32:04 ? 0:00 ora_dbw0_grid
oracle 25114 1 0 16:32:04 ? 0:00 ora_smon_grid
oracle 25118 1 0 16:32:04 ? 0:00 ora_cjq0_grid
oracle 25120 1 0 16:32:04 ? 0:00 ora_rbal_grid
oracle 25122 1 0 16:32:04 ? 0:00 ora_d000_grid
oracle 25106 1 0 16:32:04 ? 0:00 ora_pmon_grid

Enabling Flashback Database

You can enable Flashback Database using the following steps:

1. Make sure the database is in archive mode.
2. Configure the recovery area by setting the two parameters:
• DB_RECOVERY_FILE_DEST
• DB_RECOVERY_FILE_DEST_SIZE
3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;
4. Set the Flashback Database retention target:
• DB_FLASHBACK_RETENTION_TARGET
Determine if Flashback Database is enabled
Issue the following command:
SQL> select flashback_on from v$database;


FLASHBACK_ON
------------
YES
Disabling Flashback Database
Issue the following command to disable Flashback Database:
SQL> ALTER DATABASE FLASHBACK OFF;
You can also perform the same task in Enterprise Manger:

Monitoring Flashback Database
• Monitor logging in the Flashback Database logs:

SQL> select begin_time, flashback_data,
2 db_data, redo_data, ESTIMATED_FLASHBACK_SIZE
3 from v$flashback_database_stat;


BEGIN_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
-------------------- -------------- ---------- ---------- ------------------------
Feb 22 2004 01:05:14 147456 2719744 92160 0
Feb 22 2004 00:05:09 3891200 5857280 2537984 252788736
Feb 21 2004 23:05:04 7979008 13615104 3385344 254877696
Feb 21 2004 22:05:00 14893056 19857408 17463296 255737856
Feb 21 2004 21:04:55 4210688 6422528 2598912 254361600
Feb 21 2004 20:04:51 4333568 8962048 2775552 256475136
Feb 21 2004 19:04:46 4431872 7028736 2804736 258588672
Feb 21 2004 18:04:41 4202496 8511488 2635264 260726784
Feb 21 2004 17:04:37 4030464 6938624 2546688 263012352
Feb 21 2004 16:04:32 4005888 7479296 2512384 265420800
Feb 21 2004 15:04:27 3874816 6864896 2471936 267927552
Feb 21 2004 14:04:23 4153344 7028736 2578944 270532608
Feb 21 2004 13:04:18 3825664 7675904 2497536 273113088
Feb 21 2004 12:04:13 4489216 6815744 2810880 275914752
Feb 21 2004 11:04:09 3956736 7217152 2475520 278544384
Feb 21 2004 10:04:04 4268032 7086080 2652160 281444352
Feb 21 2004 09:03:59 3915776 7176192 2513920 284344320
Feb 21 2004 08:03:54 3866624 6881280 2456064 287465472
Feb 21 2004 07:03:50 4268032 6889472 2699264 290709504
Feb 21 2004 06:03:45 4063232 9117696 2645504 293904384
Feb 21 2004 05:03:40 14925824 20996096 14270976 297295872
Feb 21 2004 04:03:35 3997696 7708672 2669056 296239104
Feb 21 2004 03:03:28 4169728 7331840 2676224 299802624
Feb 21 2004 02:03:23 4096000 7069696 2680832 303439872
Feb 21 2004 01:03:19 4210688 7069696 2680832 307249152
25 rows selected.

• Monitor the Flashback Database retention target:
SQL> select *

2 from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE
ESTIMATED_FLASHBACK_SIZE

-------------------- --------------------- ---------------- -------------- -----

2.2029E+12 Oct 06 2003 09:44:42 1440 48316416
21774336
Note: The default value for flashback retention time is 1400 minutes.
• Adjust recovery area disk quota:

SQL> select estimated_flashback_size
2 from v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE
------------------------
21823488
Example 1: Flashback a Database Using RMAN
RMAN> FLASHBACK DATABASE
2> TO TIME = TO_DATE
3> (‘06/25/03 12:00:00’,’MM/DD/YY HH:MI:SS’);
Example 2: Flashback a database using SQL command
The database must be in mount state to issue these commands:
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE – 5/24);
SQL> FLASHBACK DATABASE TO SCN 76239;
You must issue the follow command afterwards:
SQL> ALTER DATABASE RESETLOGS;
Flashback Drop
Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.
This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.
Recycle Bin
A recycle bin contains all the dropped database objects until:
• You permanently drop them with the PURGE command.
• You recover the dropped objects with the UNDROP command.
• There is no room in the tablespace for new rows or updates to existing rows.
• The tablespace must be extended.
• You can view the dropped objects in the recycle bin from two dictionary views:
o USER_RECYCLEBIN — list all dropped user objects.
o DBA_RECYCLEBIN — list all dropped system-wide objects.
Example 1: Dropping an object
In this example, when you drop an object and it is moved to the recycle bin, the name of the object is changed. The recycle bin also keeps the original name of the object. This feature allows you to create a new object of the same name and then drop it again.
Example 2: Restoring a dropped object
This example will restore the dropped table test.
SQL> flashback table “BIN$0+ktoVChEmXgNAAADiUEHQ==$0” to before drop;
Flashback complete.
Example 3: Dropping a table permanently
This statement puts the table in the recycle bin:
SQL> drop table test purge;
Table dropped.
This statement removes the table permanently:
SQL> purge table "BIN$0+ktoVChEmXgNAAADiUEHQ==$0";
Table purged.
Example 4: Dropping a tablespace
You can only issue this command when the tablespace users is empty. Objects in the recycle bin of tablespace users will be purged:
SQL> drop tablespace users;
When you issue this command, objects in the tablespace users are dropped. They are not placed in the recycle bin. Any objects in the recycle bin belonging to the tablespace users are purged.
SQL> drop tablespace users including contents;
Example 5: Purging the Recycle Bin
This statement purges the user recycle bin:
SQL> purge recyclebin;
Recyclebin purged.
This statement removes all objects from the recycle bin:
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
This statement purges all objects from tablespace users in the recycle bin:
SQL> purge tablespace users;
Tablespace purged.
Conclusion

We have examined two new features in Oracle Database 10g. Specially, we looked at the Flashback Database and Flashback Drop functions. Those two new features simplify database backup and deliver fast and flexible data recovery. In Part 2, we will examine another three new Flashback features, Flashback Table, Flashback Versions Query, and Flashback Transaction Query.