Use RESTORE POINT with Flashback Database


When automating your way through releases and want to have a way to rollback if needed. It might be wise to use flashback database as a method to create a “snapshot” in the database to revert back to.

Here is a mental note for myself and others who find it usefull.

Configure Flashback Database

[NOMT] SYS@NOMT1> ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both;
System altered.


[NOMT] SYS@NOMT1> ALTER SYSTEM SET db_recovery_file_dest='+FRA';
System altered.


[NOMT] SYS@NOMT1> alter database flashback on;
Database altered.

Create Restore Point

[NOMT] SYS@NOMT1> CREATE RESTORE POINT BEFORE_CHANGE GUARANTEE FLASHBACK DATABASE;
Restore point created.

Use the “GUARANTEE” option to keep it no matter what.

Monitor Space usage for Flashback Database Log.

[NOMT] SYS@NOMT1> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE	    |PERCENT_SPACE_USED|PERCENT_SPACE_RECLAIMABLE|NUMBER_OF_FILES
--------------------|------------------|-------------------------|---------------
CONTROL FILE	    |		      0|			0|		0
REDO LOG	    |		      0|			0|		0
ARCHIVED LOG	    |		      0|			0|		0
BACKUP PIECE	    |		      0|			0|		0
IMAGE COPY	    |		      0|			0|		0
FLASHBACK LOG	    |		   2.95|			0|		2
FOREIGN ARCHIVED LOG|		      0|			0|		0
7 rows selected.


[NOMT] SYS@NOMT1> column time format a35
[NOMT] SYS@NOMT1> column name format a30
[NOMT] SYS@NOMT1> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
NAME			      | 	SCN|TIME			       |DATABASE_INCARNATION#|GUA|STORAGE_SIZE
------------------------------|------------|-----------------------------------|---------------------|---|------------
BEFORE_CHANGE	      |   455986430|21-APR-16 08.46.37.000000000 AM    |		    2|YES|   157286400


Drop restore point afterwards

[NOMT] SYS@NOMT1> DROP RESTORE POINT BEFORE_CHANGE;
Restore point dropped.
[NOMT] SYS@NOMT1>

Restore to Restore point.
This requires your instance to be shutdown and recover to the restore point.

[NOMT] SYS@NOMT1> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[NOMT] SYS@NOMT1> startup mount;
ORACLE instance started.
Total System Global Area|   730714112|bytes
Fixed Size		|     2231952|bytes
Variable Size		|   444596592|bytes
Database Buffers	|   272629760|bytes
Redo Buffers		|    11255808|bytes
Database mounted.

[NOMT] SYS@NOMT1> flashback database to restore point before_change;
Flashback complete.

[NOMT] SYS@NOMT1> alter database open resetlogs;
Database altered.