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.