My Oracle administration notes/findings. You are welcome to browse and comment.

Saturday, June 14, 2014

In my 11gR2 (11.2.0.3) database, I've created a guaranteed restore point without explicitly turning on flashback.   That was a bit of a surprise as going back to a restore point means you need to run the "flashback" command.  So I suspected that Oracle turns on flashback behind the scenes after the guaranteed restore point is created.  The test below confirms this.   

After creating the restore point, v$database.flashback_on changed from NO to RESTORE POINT ONLY :

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

CURRENT_SCN LOG_MODE FLASHBACK_ON
----------- ------------ ------------------
     757896 ARCHIVELOG NO

SQL> select * from v$restore_point;

no rows selected

SQL> create restore point R20140614080801 guarantee flashback database

Restore point created.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY


Flashback logs are actually created in the flash recovery area (which was set before creating the restore point).  

[oracle@orahost1 fast_recovery_area]$ find . -type f -mmin -5 -ls
 49417 51264 -rw-r-----   1 oracle   dba      52436992 Jun 14 08:11 ./NVPDBH1/flashback/o1_mf_9spxnngs_.flb
 49418 51264 -rw-r-----   1 oracle   dba      52436992 Jun 14 08:09 ./NVPDBH1/flashback/o1_mf_9spxnp3b_.flb
1796706 9760 -rw-r-----   1 oracle   dba       9977856 Jun 14 08:14 ./NVPDBH1/controlfile/o1_mf_9hy6087n_.ctl
1796749 51260 -rw-r-----   1 oracle   dba      52429312 Jun 14 08:14 ./NVPDBH1/onlinelog/o1_mf_2_9km0b9k6_.log


... and the database records them here:

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE    .19 0 1
REDO LOG   7.03 0 7
ARCHIVED LOG  39.58      .05       80
BACKUP PIECE  12.24 0 2
IMAGE COPY      0 0 0
FLASHBACK LOG   2.01 0
FOREIGN ARCHIVED LOG      0 0 0

7 rows selected.

(The flashback_log row in v$flash_recovery_area_usage was all zero before creating the restore point)

Then after the restore point was dropped, flashback_on became NO again and all the flashback logs were automatically deleted from the recovery area.

In Oracle 10g I remember the database has to be mounted to turn on flashback, but not in 11gR2 (not sure about 11gR1).  So I guess that's why Oracle can turn it on behind the scenes when required by creating the guaranteed restore point.

Finally, I tested flashback database to the restore point and that worked too; the database opened successfully after reset logs.


Followers