Create/Drop a Guaranteed Restore Point Using Flashback

Hello again, Oracle Flashback Captains!!

Here’s a Friday lunchtime special. Since my lunchtime is being interrupted by creating a restore point for a customer, I thought I would share my fun with you! There’s nothing earth shaking or innovative here. Just some good, old fashioned syntax to store away for later use.

• First make sure archivelog is set:

select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON

———— ——————

ARCHIVELOG   NO

1 row selected.

• Next turn on flashback:

Alter database flashback ON;

LOG_MODE     FLASHBACK_ON

———— ——————

ARCHIVELOG   YES

 

• Next see if any restore points are still out there.  Drop them if they exist:

 

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

no rows selected

• Now create restore point with the date time and db name

CREATE RESTORE POINT restorePointName_todaysDate_timeOfRestorePoint GUARANTEE FLASHBACK DATABASE;

• Make sure the sql stmt worked.

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

• Should you need to use this restore point…
  • flashback database to restore point restorePointName_todaysDate_timeOfRestorePoint
  • On a RAC Database…

>>> srvctl stop database -d dbname
set env
. oraenv
sqlplus / as sysdba
startup mount;
flashback database to restore point restorePointName_todaysDate_timeOfRestorePoint;
alter database open resetlogs;
drop restore point before_upgrade;
shutdown immediate;
exit;
>>> srvctl start database -d dbname
Alter database flashback off;

• After Deployment set Flashback off

DROP RESTORE POINT restorePointName_todaysDate_timeOfRestorePoint;

Alter database flashback off;

• Verify flashback is off

select log_mode,flashback_on from v$database;

Thanks for reading!

Jason