flashback_on is "RESTORE POINT ONLY" - How to restore ?

Guaranteed Restore Point creates Flashback Log even Flashback logging is Disabled

Recently, we experienced couple of issues regarding Flash Recovery area space. 
  One is exhausted disk space on production database servers
  And another is Load testing before migration on pre-prod with flash back option

The problem is that the Flashback Recovery Area (FLA), where usually daily RMAN backup are going, So , disk space (90% full) issue some times the big headache . Anyway we have preserved sufficient space for FLA (near two times of actual size of database) and configured backup retention copy to 1,  that problem should be caused by exceptional database activities. Like more on retention policy, generation of huge archivelogs, etc.  After investigation, we shortly found that a folder /flashback, which sits under FLA, consumed over ~25% disk space on FLA.

It’s doubtable that the files within with extension name .flb are Flashback Log Files(OMF). But, the question was raised next: 

Why is flashback log files created even Flashback Logging is disabled?

According to “Oracle Database Backup and Recovery Basics 10gR2 and above”, flashback log files are deleted automatically when:

• If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
• If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make space for other files. 

In such a case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.

since Oracle 10g database, Oracle Flashback Technology is a group of Oracle Database features that that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. To enable flashback, we need to explicitly issue command “alter database flashback on” and the flashback status could be found at view v$database.

Click here to read how to set / configure flashback database.

 Even I’m sure that flashback feature was disabled, I would like to check the view v$database again.

SQL> select name, log_mode, flashback_on from v$database;

NAME  LOG_MODE   FLASHBACK_ON
----- ---------  ------------------
PROD  ARCHIVELOG RESTORE POINT ONLY

         
Usually we made FLASHBACK on, then start your activity. After I issued this query, I surprisedly found that the value of field “FLASHBACK_ON” is “RESTORE POINT ONLY”, instead of “YES” and “NO”. Thus, it’s obvious that we have (guaranteed) restore point inside the database and probably that’s why flashback log files kept creating on FLA. Because restore point is created like below:

SQL> create restore point B4_UPGRADE guarantee flashback database;

 So, “Guaranteed Restore Points” means,  “The best part of this feature is, it uses db_file_recovery_dest to keep the changed blocks, even if flashback logging is not enabled for your database.”

Next, I executed query to check the Restore Point on this database.

SQL> select name, scn, time, guarantee_flashback_database
from v$restore_point;

NAME     SCN     TIME       GUA
------   ------   ---------   -------------
B4_UPGRADE 9329582780 22-JAN-16 06.01.43.00AM YES


The query results clearly  shows that there is Guaranteed Restore Point created almost year ago. And then, I tried to drop this obsolete restore point.

SQL> startup mount;

SQL> flashback database to restore point B4_UPGRADE;

Flashback complete.

SQL> drop restore point B4_UPGRADE ;

Restore point dropped.

SQL> alter database open resetlogs;

Database altered.

SQL>

Note : If you are using Cluster database, then stop all instances. Work from only one node. After activity complete, you can start all nodes again.

Here some more concepts about RESTORE POINT:

Use the CREATE RESTORE POINT statement to create a restore point, which is a name associated with an SCN of the database corresponding to the time of the creation of the restore point. A restore point can be used to flash a table or the database back to the time of creation of the restore point without the need to determine the SCN or timestamp.

There are two types of restore point:

• Guaranteed restore points: A guaranteed restore point enables you to flash the database back deterministically to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting. The guaranteed ability to flash back depends on sufficient space being available in the flash recovery area.
  Guaranteed restore points guarantee only that the database will maintain enough flashback logs to flashback the database to the guaranteed restore point. It does not guarantee that the database will have enough undo to flashback any table to the same restore point.
  Guaranteed restore points must be dropped explicitly by the user using the DROP RESTORE POINT statement. They do not age out. Guaranteed restore points can use considerable space in the flash recovery area. Therefore, Oracle recommends that you create guaranteed restore points only after careful consideration.
• Normal restore points: A normal restore point enables you to flash the database back to a restore point within the time period determined by theDB_FLASHBACK_RETENTION_TARGET initialization parameter. The database automatically manages normal restore points. When the maximum number of restore points is reached, according to the Oracle Document rules described restore_point, the database automatically drops the oldest restore point. However, you can explicitly drop a normal restore point using the DROP RESTORE POINT statement.

You can create either type of restore point on a primary or standby database. The database can be open or mounted but not open. If the database is mounted, then it must have been shut down cleanly before being mounted unless it is a physical standby database.

 This command may take long time and during the processing, You may observe  that the flashback log files within folder flashback will be purged automatically. Meanwhile, I monitored the process and noticed that the major session waits of this process is “control file parallel write”. It’s quite understandable that the “drop” command kept reading/writing the control file to reflect the SCN changes incurred with this command.

Once the execution was finished, the folder flaskback has been purged to empty. Out attempt to release disk space then got success by lowering the disk space utilization from 90% to 60%. Also, the field “FLASHBACK_ON” of view v$database was set back to “NO” instead.

SQL> select name, log_mode, flashback_on from v$database;

NAME  LOG_MODE    FLASHBACK_ON
----- ----------- ------------
PROD   ARCHIVELOG           NO

My approach to solve this problem verified that “Guaranteed Restore Points” does generate flashback log files even flashback logging is disabled.

Furthermore, I would like to share several practical points relating to restore point from other blogs.

Guaranteed restore points guarantee only that the database will maintain enough flashback logs to flashback the database to the guaranteed restore point. It does not guarantee that the database will have enough undo to flashback any table to the same restore point.

Click here to read from Oracle Document.

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';