08 February, 2015

Database Flashback -- 2

Continuing my series on Oracle Database Flashback.
(My first post on this topic was a week ago).


I create a Restore Point :

[oracle@localhost Hemant]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 8 22:55:28 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>create restore point Feb08_15;

Restore point created.

SYS>
SYS>show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SYS>

Oracle has captured a Restore Point. Note that this is *not* a Guaranteed Restore Point.  Although the default Flashback Retention Target is 24 hours, However, Oracle maintains Flashback Logs to *target* the ability to flashback to 24hours ago.  If the FRA is insufficient to hold all the Flashback Logs created over 24hours, some Flashback Logs may be deleted.

At any time, I can query my Flashback-ability status.

SYS>  
SYS>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
            14569609 01-FEB-15             1440       24576000                        0

SYS>

I had enabled Flashback on 01-Feb and there has been no activity in this database since then.  So, I currently do have Flashback Logs upto the time they were first created.

Therefore : It is not a hard-and-fast rule that you can Flashback to the Flashback Retention Target.  You might be able to flashback further back in time in an inactive database.  On the other hand, in a very active database, with inadequate FRA, you might NOT be able to Flashback to the Flashback Retention Target.
.
.
.

No comments: