Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Dec 23, 2004

Oracle 10g Availability Enhancements, Part 2: Flashback Database - Page 2

By Jim Czuprynski

Enabling Flashback Database

As its name implies, Flashback Database offers the capability to quickly "flash" a database back to its prior state as of a specified point in time. Oracle does this by retaining a copy of any modified database blocks in flashback logs in the Flash Recovery Area. A new flashback log is written to the Flash Recovery Area on a regular basis (usually hourly, even if nothing has changed in the database), and these logs are typically smaller in size than an archived redo log. Flashback logs have a file extension of .FLB.

When a Flashback Database request is received, Oracle then reconstructs the state of the database just prior to the point in time requested using the contents of the appropriate flashback logs. Then the database's archived redo logs are used to fill in the remaining gaps between the last backup of the datafile and the point in time desired for recovery.

The beauty of this approach is that no datafiles need to be restored from backups; further, only the few changes required to fill in the gaps are automatically applied from archived redo logs. This means that recovery is much quicker than traditional incomplete recovery methods, with much higher database availability.

It is worth noting the few prerequisites that must be met before a database may utilize Flashback Database features:

  • The database must have flashback logging enabled, and therefore a Flash Recovery Area must have been configured. (For a RAC environment, the Flash Recovery Area must also be stored in either ASM or in a clustered file system.)
  • Since archived redo logs are used to "fill in the gaps" during Flashback Database recovery, the database must be running in ARCHIVELOG mode.

Activating Flashback Database. Once the Flash Recovery Area has been configured, the next step is to enable Flashback Database by issuing the ALTER DATABASE FLASHBACK ON; command while the database is in MOUNT EXCLUSIVE mode, similar to activating a database in ARCHIVELOG mode.

Setting the Flashback Retention Target. Once Flashback Database has been enabled, the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines exactly how far a database can be flashed back. The default value is 1440 minutes (one full day), but this can be modified to suit the needs of your database. For purposes of illustration, I have set my demonstration database's setting to 2880 minutes (two full days).

Deactivating Flashback Database. Likewise, issuing the ALTER DATABASE FLASHBACK OFF; command deactivates Flashback Backup and Recovery. Just as in the activation process, note that this command must be issued while the database is in MOUNT EXCLUSIVE mode.

See Listing 2.3 for queries that display the status of the Flash Recovery Area, status of the related initialization parameters, and whether the database has been successfully configured for flashback.

Storing Backups In Flash Recovery Area

Now that I have enabled the Flash Recovery Area and enabled flashback logging, I can next turn my attention to preparing the database to use flashback logs during a Flashback Database recovery operation.

Listing 2.4 lists the RMAN commands I will need to issue to configure the database for Flash Recovery Area and Flashback Database use. Notice that I have not CONFIGUREd a FORMAT directive for the RMAN channels used to create database backups; for these examples, I am going to let RMAN place all backup components directly in the Flash Recovery Area.

Listing 2.5 implements Oracle's recommended daily RMAN backup scheme using datafile image copies and incrementally-updated backups. (See the previous article in this series for a full discussion of this technique.)

Finally, Listing 2.6 shows the abbreviated results of the first cycle's run of this backup scheme. Note that Oracle uses OMF naming standards for each backup component file - in this example, datafiles, the "extra copy" of the archived redo logs, and control file autobackups - stored in the Flash Recovery Area.

Flashback Database: An Example

Now that I have enabled flashback logging and have created sufficient backup components that are being managed in the Flash Recovery Area, it is time to demonstrate a Flashback Database operation.

Let's assume a worst-case scenario: One of my junior developers has been enthusiastically experimenting with logical units of work on what he thought was his personal development database, but instead mistakenly applied a transaction against the production database. He has just accidentally deleted several thousand entries in the SH.SALES and SH.COSTS tables - just in time to endanger our end-of-quarter sales reporting schedule, of course! Here is the DML statements issued, along with the number of records removed:

DELETE FROM sh.sales
 WHERE prod_id BETWEEN 20 AND 80;

10455 rows deleted
Executed in 89.408 seconds

DELETE FROM sh.costs
 WHERE prod_id BETWEEN 20 AND 80;

6728 rows deleted
Executed in 18.086 seconds

COMMIT;
Commit complete
Executed in 0.881 seconds

Flashback Database to the rescue! Since I know the approximate date and time that this transaction was committed to the database, I will issue an appropriate FLASHBACK DATABASE command from within an RMAN session to return the database to that approximate point in time. Here is a more complete listing of the FLASHBACK DATABASE command set:

FLASHBACK [DEVICE TYPE = <device type>] DATABASE 
TO [BEFORE] SCN = <scn>
	TO [BEFORE] SEQUENCE = <sequence> [THREAD = <thread id>]
	TO [BEFORE] TIME = '<date_string>'

Note that I can return the database to any prior point in time based on a specific System Change Number (SCN), a specific redo log sequence number (SEQUENCE), or to a specific date and time (TIME). If I specify the BEFORE directive, I am telling RMAN to flash the database back to the point in time just prior to the specified SCN, redo log, or time, whereas if the BEFORE directive is not specified, the database will be flashed back to the specified SCN, redo log, or time as of that specified point in time, i.e., inclusively.

First, I queried my database's Flashback Logs to determine which ones are available, found the log just prior to the user error and decided to flash back the database based on that log's starting SCN. Listing 2.7 contains the query I ran against V$FLASHBACK_DATABASE_LOGFILE to obtain this information.

Just as I would do during a normal point-in-time incomplete recovery, I then shut down the database by issuing the SHUTDOWN IMMEDIATE command, and then restarted the database and brought it into MOUNT mode via the STARTUP MOUNT command. Instead of having to perform a restoration of datafiles as in a normal incomplete recovery, I instead simply issue the appropriate FLASHBACK DATABASE command to take the database back to the SCN I desired.

Once the flashback is completed, I could have continued to roll forward additional changes from the archived redo logs available; however, I simply chose to open the database at this point in time via the ALTER DATABASE OPEN RESETLOGS; command. Here are the actual results from the RMAN session:

C:>rman nocatalog target sys/@zdcdb

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: ZDCDB (DBID=1863541959)
using target database controlfile instead of recovery catalog

RMAN> FLASHBACK DEVICE TYPE = DISK DATABASE TO SCN = 2127725;

Starting flashback at 08-DEC-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK


starting media recovery
media recovery complete

Finished flashback at 08-DEC-04

RMAN> alter database open resetlogs;

database opened

To see what is really going on during the flashback and recovery process, I have also included a portion of the database's alert log. Note that Oracle automatically cleaned up after itself: Since they are of no use any longer after the RESETLOGS operation, Oracle even deleted the outmoded Flashback Logs from the Flashback Recovery Area.

Conclusion

Oracle 10g's Flash Recovery Area simplifies the storage and handling of backup components and flashback logs, and the new Flashback Database features provide any Oracle DBA with a much improved, faster option for incomplete database recovery. The next article in this series will delve into the details of using Oracle 10g's expanded Logical Flashback features, including some intriguing capabilities for recovering from logical errors at a much more granular level than Flashback Database provides.

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article:

B10734-01 Oracle Database Backup and Recovery Advanced User's Guide

B10735-01 Oracle Database Backup and Recovery Basics

B10750-01 Oracle Database New Features Guide

B10770-01 Oracle Database Recovery Manager Refererence

» See All Articles by Columnist Jim Czuprynski



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM