How To Configure A Flashback Database

BACKGROUND & OVERVIEW

The flashback database functionality, introduced in Oracle 10g, is of particular use in testing environments where the same starting point is required for multiple tests.

Rather than having to endure the time and effort required to initially back up the database at the starting point, and then after each test restore the database back to the starting point, a slicker approach is to configure the flashback database functionality, and then use it.

DETERMINE IF FLASHBACK DATABASE IS ALREADY ENABLED

     SQL> SELECT flashback_on FROM v$database;

     FLASHBACK_ON
     ------------------
     NO

ENABLING FLASHBACK: A STEP-BY-STEP GUIDE

  • This worked example assumes the database is using ASM to manage its storage.
  1. ALTER SYSTEM SET db_recovery_file_dest='+<FRA Diskgroup>' SCOPE=spfile;
  2. ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=spfile;
  3. SHUTDOWN IMMEDIATE;
  4. STARTUP MOUNT;
  5. ALTER DATABASE FLASHBACK ON;
  6. ALTER DATABASE OPEN;
  7. ALTER SYSTEM SET db_flashback_retention_target=2880;
  • Set the db_recovery_file_dest to an appropriate location for the flashback recovery files.
  • Set the db_recovery_file_dest_size to an appropriate size for the amount and size of the testing required.
  • Set the db_flashback_retention_target to an appropriate time, in mins, to retain flashbackability.

CREATING A RESTORE POINT

  • Create a restore point whenever the database is at a state that it may needed to be flashed back to. Use the optional GUARANTEE FLASHBACK DATABASE clause to ensure that the restore point is not aged out of the flashback recovery area (FRA) as dictated by the db_flashback_retention_target parameter.
  • CREATE RESTORE POINT <restore point name> [GUARANTEE FLASHBACK DATABASE];

ROLLING BACK TO A RESTORE POINT

HOW TO IDENTIFY AVLAILABLE RESTORE POINTS

  1. SQLPLUS / AS SYSDBA
  2. SELECT NAME, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;
  3. EXIT
  • Example:
       SQL> col NAME for a40
       SQL> col TIME for a18
       SQL> SELECT NAME, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;

       NAME                                     TIME               GUA
       ---------------------------------------- ------------------ ---
       <restore point name>                     30-SEP-09 03.16.59 YES
                                               .000000000 PM

SINGLE INSTANCE

  1. SQLPLUS / AS SYSDBA
  2. SHUTDOWN IMMEDIATE;
  3. STARTUP MOUNT;
  4. FLASHBACK DATABASE TO RESTORE POINT <restore point name>;
  5. ALTER DATABASE OPEN RESETLOGS;
  • Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by issuing the TRUNCATE TABLE command against the affected object(s).

RAC INSTANCES

  1. From Unix: SRVCTL STOP DATABASE -d <database name> -o IMMEDIATE

The following should be issued on a single node only.

  1. SQLPLUS / AS SYSDBA
  2. STARTUP MOUNT;
  3. FLASHBACK DATABASE TO RESTORE POINT <restore point name>;
  4. ALTER DATABASE OPEN RESETLOGS;
  5. SHUTDOWN IMMEDIATE
  6. EXIT
  7. From Unix: SRVCTL START DATABASE -d <database name>
  8. Issue the crs_stat -t command from Unix to ensure that all RAC components are running and restart any that aren't using SRVCTL
  • Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by issuing the TRUNCATE TABLE command against the affected object(s).

DROPPING A RESTORE POINT

  • Restore points can be dropped dynamically, i.e. with the database open.
  1. SQLPLUS / AS SYSDBA
  2. DROP RESTORE POINT <restore point name>;
  3. EXIT

MONITORING FLASHBACK LOGGING

  • After enabling flashback logging, Oracle keeps track of the amount of logging generated. This can be queried from V$FLASHBACK_DATABASE_LOG, the estimate gets better with age. Note that this is the size of the flashback logs only and does not include space used by archive logs and RMAN backups.
      SQL> select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;

      EST_FLASHBACK_SIZE(GB)
      ----------------------
                   88.450882

FINDING THE EARLIEST FLASHBACK POINT

  • Querying V$FLASHBACK_DATABASE_LOG will show you the earliest point you can flashback your database to based on the size of the FRA and the currently available flashback logs.
      SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
      SQL> select oldest_flashback_scn,oldest_flashback_time from  v$flashback_database_log;

      OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_
      -------------------- -----------------
                  19138295 05/08/09 16:45:47

DISABLING FLASHBACK DATABASE

  • Flashback can be disabled with the database open. Any unused Flashback logs will be automatically removed at this point and a message detailing the file deletion written to the alert log.
  1. SQLPLUS / AS SYSDBA
  2. ALTER DATABASE FLASHBACK OFF;
  3. EXIT

USING FLASHBACK WITHOUT FLASHBACK

It is possible to use a restore point without the need for flashback logs, however this will mean that only a flashback of the database to the restore point in time is possible and not to any point in time between then and now. The benefit of this, assuming only being able to go back to the resore point is acceptable, is that there are no flashback logs to manage and provide space for.

To achieve this, simply create a restore point, from the mount state, and then do not switch flaskback on before opening the database. An example is shown here:

sqlplus '/ as sysdba'
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
CREATE RESTORE POINT <restore point name>;
ALTER DATABASE OPEN;
EXIT;

TROUBLESHOOTING

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available

  • Cause
    • Oracle needs to have the required archive logs in the archive destination at the time of flashback.
  • Solution
    • Use rman to restore the missing archive logs. The sequence and thread numbers are in the error message. e.g.
      rman target /
      run{
         allocate channel t1 device type 'sbt_tape';=
         FLASHBACK DATABASE TO RESTORE POINT <restore point name>;=
         }
  • Resume the flashback process from the RESETLOGS step.
© copyright 2001-2014 ABCdba.com | all rights reserved