Flashback database setup and use

Working with Flashback database :

1) What are pre-requisites?
2) How to configure?
3) Discussion Details:
    Enable archivelog, Enable and disable flashback, create RESTORE POINT, Flashback to restore point, Point in time recovery (PITR), Flashback Drop, Flashback query
4) FAQ

Oracle 10gR2 inwards, this is brilliant alternative to database point in time recovery is the the Flashback Database feature. With this feature in place you can do almost everything that you can with point in time recovery, without actually having to go through all the disruptions and hassle that a PITR necessarily entails.

1) Pre-requisites:

a) It's only available in the Enterprise Edition of the database.
b) Archivelog must be enabled.
c) Sufficitent storage space should available as per retaintion.
d) Based on CPU load, processor must be available to bear extra performance cost.

2) How to configure?

a) Enable archivelog mode if not enabled.

SQL> startup mount;
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 120G ;
System altered.
SQL> alter system set db_recovery_file_dest='/u01/FRA';
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/u01/FRA/archive_logs';
System altered.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

Note: If you want to keep archivelogs in db_recovery_dest location, then don't specify archivelog location, just enable archivelog mode.

b) Enable FLASHBACK 

SQL> startup mount;
-- 3 days
SQL> alter system set db_flashback_retention_target=4320 scope=both;
SQL> alter database flashback on;
SQL> alter database open;

SQL>

-- So, that enables flashback for our database. We can query v$flashback_database_log to see what's going on

SQL> 
col OLDEST_FLASHBACK_SCN format 9999999999999;
col FLASHBACK_SIZE format 9999999999999;
set lines 120;
select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- -------------------- ---------------- -------------- ------------------------
         91098353333 25-NOV-2015 17:08:09             4320    54173696000               4272537600
SQL>

We see that oldest SCN that we can flashback to is SCN 539853. In other words this is our baseline SCN. The entire technology of flashback database is being implemented from this SCN. We can easily convert the SCN to a timestamp, 

if we are interested in seeing the time from which flashback database stands enabled.

SQL> col current_scn format 99999999999;
SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

 CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
------------ ---------------------------------------------------------------------------
 91105623393 06-JAN-16 02.26.02.000000000 PM

-- Create the flash_back restore point:

If you want to create restore point to go back restore point, then:
SQL> 
alter system set db_flashback_retention_target=4320 scope=both;
alter database flashback on;
create restore point BEFORE_TEST guarantee flashback database;
alter database open;

-- Rollback to restore point
SQL>

startup mount force;
flashback database to restore point BEFORE_TEST;
drop restore point BEFORE_TEST;
alter database open resetlogs;
SQL>

-- To disable Flashback

SQL> startup mount;
SQL> alter database flashback off;
SQL> alter database open;

-- SCN based recovery using flashback database

SQL> shut immediate;
SQL> startup mount exclusive;

-- be sure to substitute your SCN
SQL> FLASHBACK DATABASE TO SCN 91098353333;

Note: See your alert log what is happening.

-- Some other variations of the flashback database command include.

FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;

-- Flashback 5 minutes.

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

-- Flashback Drop (Recycle Bin)

DROP TABLE command is to move the table to the recycle bin (or rename it), rather than actually dropping it. The DROP TABLE ... PURGE option can be used to permanently drop a table.

The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege. This feature does not use flashback logs or undo, so it is independent of the other flashback technologies. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN command and purged using the PURGE TABLE command. As a result, a previously dropped table can be recovered from the recycle bin.

SQL> CREATE TABLE fb_drop_test (id  NUMBER(10));
SQL> INSERT INTO fb_drop_test (id) VALUES (1);
SQL> COMMIT;
SQL> DROP TABLE fb_drop_test;
SQL> SHOW RECYCLEBIN

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FB_DROP_TEST    BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE        2016-14-29:11:09:07IST

FLASHBACK TABLE fb_drop_test TO BEFORE DROP;

SELECT * FROM fb_drop_test;

        ID
----------
         1

If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space. Where multiple versions are present it's best to reference the tables via the RECYCLEBIN_NAME. For any references to the ORIGINAL_NAME it is assumed the most recent object is drop version in the referenced question. 

During the flashback operation the table can be renamed.

SQL> FLASHBACK TABLE fb_drop_test TO BEFORE DROP RENAME TO fb_drop_test_old;

-- Flashback Table

The FLASHBACK TABLE command allows point in time recovery of individual tables subject to the following requirements.

• You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.
• You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
• There must be enough information in the undo tablespace to complete the operation.
• Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).

The following example creates a table, inserts some data and flashbacks to a point prior to the data insertion. Finally it flashbacks to the time after the data insertion.

SQL> CREATE TABLE flashback_table_test (id  NUMBER(10));

SQL> ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     91105623393

SQL> INSERT INTO flashback_table_test (id) VALUES (1);
SQL> COMMIT;

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
     91105623393

SQL> FLASHBACK TABLE flashback_table_test TO SCN 91105623393;

SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         0

SQL> FLASHBACK TABLE flashback_table_test TO SCN 91105623410;

SQL> SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         1

Flashback of tables can also be performed using timestamps.

FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2016-01-06 10:00:00', 'YYYY-MM-DD HH:MI:SS');



4) FAQ

a) If database is  open with RESETLOGS, thereby creating a new incarnation, how can you perform a complete recovery? Isn't the previous backup, logs are useless?

Ans:
Very good point that you picked out. Actually, I should have clarified that in Oracle 10g Release 2, you can actually use Flashback Database to go back past an OPEN RESETLOGS operation. Technically speaking, we can return our current database to an ancestor or sibling incarnation.

b) I am geeting "out of disk space" with ORA-16014:

The error ora-16014 is the real clue for this problem. Once the archive destination becomes full the location also becomes invalid. Normally Oracle does not do a recheck to see if space has been made available.

-- then
shutdown abort;
-- clean up disk space: then
startup
alter system archive log all to '/u01/FRA/archive_logs';