Friday, February 18, 2011

Flashback Database

 
Oracle Flashback database is one of the jazziest features of Oracle10g database. As a starter, it has a cool name - Flashback. In addition, Flashback is a powerful word. It sounds like a Hollywood or Bollywood flick theme. It implies turning back the time or looking in the rearview mirror. Thanks to Oracle for lightening up tedious work that goes into database administration. And thanks to Oracle for providing a feature as powerful as the word itself.

Oracle flashback database feature lets you unwind the database easily and quickly to a previous point-in-time. There is no need to restore the backups and perform time-consuming incomplete media recovery. You just need few flashback commands, and commands are simple.

But note that flashback database command, as name suggests, operates at the database level. You will flashback the whole database, not just few schemas or just few tables.

As shown below, You can flashback database with or without FLASHBACK mode which is activated through ALTER DATABASE FLASHBACK ON command when the database is in MOUNT mode.

Flashback database with FLASHBACK mode
  • You must turn on FLASHBACK when database is in MOUNT mode.
  • Flashback Recovery Window is specified by db_flashback_retention_target init.ora parameter. Please note that this parameter specifies a potential target. Your actual mileage may vary depending upon disk space available in the flash recovery area.
  • Flashback to any point-in-time or SCN within the range specified by db_flashback_retention_target.
  • No need to create normal restore points, but should be created as aliases to represent SCN or time.
Flashback database without FLASHBACK mode
  • Flashback database to a known baseline, also known as pre-defined guaranteed restore point.
  • You must explicitly define guaranteed restore points.
  • Oracle does generate flashback logs, but you can only flashback to a pre-defined guaranteed restore point. You need to make sure that you have enough disk space to store flashback logs that Oracle would generate.
My databases are not in FLASHBACK mode, so I can't perform media recovery using the flashback feature, but I usually define guaranteed restore points when applications or data upgrade operations have potential of failures, and may require rollback.

Let's look at the examples to understand Oracle Flashback features

Example 1 - Flashback database without FLASHBACK mode

Step 1: Define initialization Parameters

SQL> show parameter db_recovery_file
NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      e:\oraflash                   
db_recovery_file_dest_size           big integer 19531250K                     
SQL> show parameter db_flashback_retention_target
NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440                          

Step 2: Create Guaranteed Restore Points

One important point to note is that the first guarantee restore point must be created when the database is in MOUNT mode. Subsequent mount points can be created while DB is open.

SQL> create restore point test guarantee flashback database ;
create restore point test guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'TEST'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> create restore point test guarantee flashback database ;
Restore point created.

SQL> alter database open ;
Database altered.

SQL> create restore point test1 guarantee flashback database ;
Restore point created.

SQL> select * from v$restore_point ;
       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE                              
---------- --------------------- --- ------------                              
TIME                                                                           
---------------------------------------------------------------------------    
NAME                                                                           
--------------------------------------------------------------------------------
 114645657                     2 YES            0                              
13-FEB-11 12.16.58.000000000 PM                                                
TEST                                                                           
                                                                               
 114645903                     2 YES      8192000                              
13-FEB-11 12.17.39.000000000 PM                                                
TEST1                                                                          
                                                                            
Step 3: Flashback Database to a Guaranteed Restore Point

Two noteworthy points are that database must be in MOUNT mode in order to flashback and database must be opened withh RESETLOGS.

SQL> flashback database to restore point test1 ;
flashback database to restore point test1
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> flashback database to restore point test1 ;
Flashback complete.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs ;
Database altered.

Example 2 - Flashback database with FLASHBACK mode

Step 1: Define initialization Parameters

SQL> show parameter db_recovery_file
NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      e:\oraflash                   
db_recovery_file_dest_size           big integer 19531250K                     
SQL> show parameter db_flashback_retention_target
NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440                          

Step 2: Create Normal Restore Points

SQL> connect / as sysdba

SQL> create restore point example_normal_restore_point ;
Restore point created.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> flashback database to restore point example_normal_restore_point ;
flashback database to restore point example_normal_restore_point
*
ERROR at line 1:
ORA-38782: cannot flashback database to non-guaranteed restore point
'EXAMPLE_NORMAL_RESTORE_POINT'

SQL> drop restore point example_normal_restore_point ;

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> alter database flashback on ;

Database altered.

SQL> alter database open ;
Database altered.

SQL> create restore point example_normal_restore_point ;
Restore point created.

Step 3: Flashback Database

SQL> flashback database to restore point example_normal_restore_point ;
flashback database to restore point example_normal_restore_point
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> flashback database to restore point example_normal_restore_point ;
Flashback complete.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs ;
Database altered.

No comments:

Post a Comment