Create/Drop a Guaranteed Restore Point Using Flashback

Posted: June 3, 2016 in 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s