From the bestselling Oracle10g book, Oracle Database 10g New Features by Mike Ault, Madhu Tumma, and Daniel T. Liu, published by Rampant TechPress.

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:
• USER_RECYCLEBIN — list all dropped user objects.
• 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.

--

Daniel Liu is a senior Oracle Database Administrator at First American Real Estate Solutions in Anaheim, CA and co-author of Oracle Database 10g New Features  (http://www.rampant-books.com/book_2003_2_oracle10g.htm) by Rampant TechPress. His expertise includes Oracle database administration, performance tuning, Oracle networking, and Oracle Application Server. As an Oracle Certified Professional, he taught Oracle certified DBA classes and IOUG University Seminar. Daniel has published articles with DBAzine.com, Oracle Internals, and SELECT Journal. Daniel has received SELECT Editorial Award for Best Article in 2001. He has also given presentations at IOUG-A Live, LAOUG, OCOUG, NoCOUG, Oracle Open World and Oracle World. Daniel has served as panelist on Oracles of Oracle at Oracle World and IOUG-Live. Daniel holds a Master of Science degree in computer science from Northern Illinois University.

References

Oracle Database 10g New Features, Ault, Liu and Tumma; Rampant Techpress;

Recovery Manager Reference, 10g Release; 10g Release

Backup and Recovery Advanced Users Guide, 10g Release

All companies and product names are trademarks or registered trademarks of the respective owners. Please report errors in this article to the author. Neither FARES nor the author warrants that this document is error-free.