There are a number of uses of Oracle flashback technology, some of which are available under our existing configuration, and some which need additional configuration to be used.

Flashback Table

  •  Based on undo retention, so available to all systems present without any customization (other than setting up auto undo which is standard for us)
  •  Can be up to 5 days (SELECT MIN(TIME_DP) FROM SMON_SCN_TIME) but mainly dependent on (i.e. unless there is no activity will be limited by) undo_retention_period
  •  undo_management must be auto
  • undo_retention lists the minimum time in seconds that you be able to flashback. If there is little undo it is possible you will be able to flashback up to 5 days (uses smon_scn_time which only keeps 5 days worth of data). You can test how long you have available to you by checking an AS OF query against an application table. If you are working on rac remember that UNDO is specific to each instance.
  •  If possible, make sure you have enough undo to go back 5 days using AS OF queries.

 

You can Flashback the table itself, or run an AS OF query to see what the data in the table looked like at a point in time without changing any data. The AS OF query can be joined with other tables.

Flashback Table Example

[email protected]> insert into system2.kpg_test values (‘A’);

1 row created.

 

[email protected]> commit;

Commit complete.

 

[email protected]CALHOST.dbname> select sysdate from dual;

SYSDATE

——————-

2011-01-25 16:19:03

 

[email protected]> select * from system2.kpg_test;

A

-

A

 

[email protected]> delete from system2.kpg_test;

1 row deleted.

 

[email protected]> commit;

Commit complete.

 

[email protected]> select * from system2.kpg_test;

no rows selected

 

[email protected]> select * from system2.kpg_test as of TIMESTAMP TO_TIMESTAMP(’2011-01-25 16:19:03′, ‘YYYY-MM-DD HH24:MI:SS’);

A

-

A

[email protected]> flashback table system2.kpg_test to TIMESTAMP TO_TIMESTAMP(’2011-01-25 16:19:03′, ‘YYYY-MM-DD HH24:MI:SS’);

flashback table system2.kpg_test to TIMESTAMP TO_TIMESTAMP(’2011-01-25 16:19:03′, ‘YYYY-MM-DD HH24:MI:SS’)

*

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

 

[email protected]> alter table system2.kpg_test enable row movement;

Table altered.

 

[email protected]> flashback table system2.kpg_test to TIMESTAMP TO_TIMESTAMP(’2011-01-25 16:19:03′, ‘YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

 

[email protected]> select * from system2.kpg_test;

A

-

A

  •  enabling row movement allows Oracle to modify rowids – has to be enabled to run flashback table. No downside that I am aware of (unless maintaining the rowid is important somehow to your application).

 

Flashback drop <table>

  •   relies on same undo technology as Flashback Query
  •   uses the recyclebin (ON by default)
  •   allows you to re-create a dropped table

 

Flashback Drop Example

[email protected]> drop table system2.kpg_test;

Table dropped.

 

[email protected]> select * from dba_recyclebin where ORIGINAL_NAME = ‘KPG_TEST’;

OWNER                          OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE

—————————— —————————— ——————————– ——— ————————-

TS_NAME                        CREATETIME          DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN

—————————— ——————- ——————- ———- ——————————– — —

RELATED BASE_OBJECT PURGE_OBJECT      SPACE

———- ———– ———— ———-

SYSTEM2                        BIN$mrQ0IO2AkPjgQwocEPiQ+A==$0 KPG_TEST                         DROP      TABLE

USERS                          2011-01-25:16:16:17 2011-01-25:16:26:11 3.3398E+12                                  YES YES

155242      155242       155242          8

 

[email protected]>  flashback table system2.kpg_test to before drop;

Flashback complete.

 

[email protected]> select count(*) from system2.kpg_test;

COUNT(*)

———-

1

Flashback Database

  •    must be configured i.e. not available ‘out of the box’
  •    needs a flash_recovery_area (FRA) for the flashback logs
  •    flashback must be enabled while the database is in mount mode

 

Setup

The three key parameters are :

  •   db_recovery_file_dest_size
  •  db_recovery_file_dest
  •  db_flashback_retention_target

 

db_recovery_file_dest_size is the size limit of the flash recovery area.  This is the size that Oracle will use to determine when it will start sending messages to the alert log warning that space used is above 80%, regardless of the space you may actually physically have in the file system or ASM disk group.

  •   Unless you have also configured archive logs (or anything else) to go to this location the parameter will be the upper limit on the size of the flashback logs written to this location.
  •   To configure archive logs to go to the FRA in addition to flashback logs, either remove all local log_archive_dest_n targets (since log_archive_dest_10 goes to the FRA by default if nothing is specified, or explicitly set  log_archive_dest_10 =’LOCATION=USE_DB_RECOVERY_FILE_DEST’;
  •  If you send archive logs to the FRA, they will use the oracle managed files naming format, not the log_archive_format= %t_%s_%r.ARC that is our standard system configuration.
  •  If the db_recovery_file_dest_size limit is reached then flashback logs or archive logs will be deleted to free up space. Flashback logs which are part of a guaranteed restore point, and archive logs which have not been backed up outside of the FRA and are needed to meet the rman retention policy are not eligible for deletion.  If the FRA fills up, the archiver process will suspend and the database will stop processing dml.
  •  If the ASM or file system physical space limit is reached, before 100% of the db_recovery_file_dest_size is reached, the user will get ORA-38700: Limit of %s flashback database logs has been exceeded, and the archiver pocess will hang.
  •  If you have guaranteed restore points (only) the system will stop processing (just as if you run out of archive log destination space) if you reach the limit of db_recovery_file_dest_size or the disk group/file system runs out of space.
  • This parameter is very useful if you have a shared flashback recovery area and should be sized appropriately.
  •  Needs to have the same value on all rac instances

 

db_recovery_file_dest is the flashback recovery area.

  •  It needs to have the same value on all rac instances.
  •  It can be ASM or a file system.
  •  Oracle will create a sub-directory (in file systems or under ASM of the database name) so there is no need to make that part of the db_recovery_file_dest destination.
  •   A subdirectory called flashback, and one called archivelog if archivelogs are sent to the FRA, will be created under the dbname in the FRA.
  •    Subdirectories of the date in YYYY_MM_DD format will be created under the archivelog subdirectory if archivelogs are sent to the FRA.

 

db_flashback_retention_target specifies the upper limit (in minutes) on how far back in time the database can be flashed back

  •   If this limit is reached older flashback logs will be eligible to be deleted, but may be kept if there is space in the FRA.
  •  Unless a guaranteed restore point is in effect, flashback logs will always be deleted from the FRA if it runs out of space, regardless of the flashback_retention_target.

 

Flashback Database Setup Example

 [email protected]> startup mount

ORACLE instance started.

Total System Global Area  293601280 bytes

Fixed Size                  2083536 bytes

Variable Size             260048176 bytes

Database Buffers           25165824 bytes

Redo Buffers                6303744 bytes

Database mounted.

 

[email protected]> show parameter db_recovery

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_recovery_file_dest              string      /logdump

db_recovery_file_dest_size      big integer 2G

 

[email protected]> select FLASHBACK_ON from v$database;

 

FLASHBACK_ON

——————

NO

 

[email protected]> alter database flashback on;

Database altered.

 

[email protected]> alter database open;

Database altered.

 

[email protected]> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES


[email protected]: cd /logdump/DBNAME1

 

[email protected]:/logdump/DBNAME1 $ ls

archivelog  flashback

 

[email protected]:/logdump/DBNAME1 $ ls flashback/

o1_mf_6o8jfy9s_.flb  o1_mf_6o8jj065_.flb  o1_mf_6o8jl68o_.flb  o1_mf_6o8jnb0k_.flb  o1_mf_6o8jpd12_.flb

 

[email protected]:/logdump/DBNAME1 $ ls archivelog/

2011_02_10

 

[email protected]:/logdump/DBNAME1 $ ls archivelog/2011_02_10/

o1_mf_1_1000_6o8jl30f_.arc  o1_mf_1_1059_6o8jqp7j_.arc  o1_mf_1_1118_6o8jx9xj_.arc

 

You can flashback to

  •    An SCN
  •    A timestamp
  •  A time (appears to only be available from RMAN, not SQLPLUS)
  •  A restore point
  •   A guaranteed restore point

 

You can create restore points or flashback to a time or scn. Use v$flashback_database_log to determine the oldest SCN/timestamp you can use.

 

You can create named restore points. These can be either standard restore points, or guaranteed restore points. The latter is useful for testing in DR site, or pre-release or pre-upgrade save points, but be very careful with guaranteed restore points since the system will halt processing if the flashback recovery area fills up when you have a guaranteed restore point.

 

[email protected]> create restore point restore_A;

 

Restore point created.

 

[email protected] >  create restore point restore_B GUARANTEE FLASHBACK DATABASE;

 

Restore point created.

 

[email protected] > select * from v$restore_point;

 

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME

———- ——————— — ———— —————————————————————————

NAME

——————————————————————————————————————————–

3.3398E+12                     2 YES      3981312 03-FEB-11 04.32.46.000000000 PM

RESTORE_B

 

3.3398E+12                     2 NO             0 03-FEB-11 04.31.40.000000000 PM

RESTORE_A

 

Note:

  •  The database has to be in mount mode, i.e. not open, to execute the FLASHBACK DATABASE command.
  •  The FLASHBACK DATABASE command can be issued via SQLPLUS or RMAN. For the most part the functionality is the same, although I have found some commands (such as FLASHBACK DATABASE TO TIME) which do not work from sqlplus. In addition,  if any archive logs are needed (and flashback database does use archive logs as well as flashback logs) that are not online i.e. have been backed up via RMAN, then issuing the command via RMAN will cause those archive logs to be automatically retrieved from backup, whereas  use of SQLPLUS has the limitation that any archive logs that are needed must already be available online.
  •  Need to issue ALTER DATABASE OPEN RESETLOGS; when the flashback is complete.

RMAN>  FLASHBACK DATABASE TO TIME “TO_DATE(’2011-01-26 14:35:30′, ‘YYYY-MM-DD HH24:MI:SS’)”;

Starting flashback at 26-JAN-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=235 devtype=DISK

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=46816

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=46817

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=46818

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=46819

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=46820

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=46821

channel ORA_DISK_1: reading from backup piece /dbdump/DBNAME1/bkarc_DBNAME1_20110126_51991_1_741452321

channel ORA_DISK_1: restored backup piece 1

piece handle=/dbdump/DBNAME1/bkarc_DBNAME1_20110126_51991_1_741452321 tag=TAG20110126T145840

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

media recovery complete, elapsed time: 00:00:09

Finished flashback at 26-JAN-11

RMAN> alter database open resetlogs;

database opened

Miscellaneous Info

  • The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

  •  Other Components to be stored in the FRA. Oracle recommends that multiple things be stored in the FRA. E.g. in addition to archive logs and flashback logs

Control files

Online redo logs

Datafile copies

Controlfile copies

Control file autobackups

Backup pieces

If space and configuration permits, it makes sense to keep database backups, controlfile copies, and control file autobackups in the FRA too. I’m not convinced that it makes sense to keep the other files there too.

To have RMAN backups be part of the FRA and managed by Oracle, you need to clear out the FORMAT destination for disk backups and it will automatically go the FRA (subdirectory backuppiece/<date>).

The V$FLASH_RECOVERY_AREA_USAGE view is a very handy view into what is taking up space in the FRA.