guaranteed restore point

How to create guaranteed restore point:

Create guaranteed restore point in oracle:

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment.

Before you start DB upgrade, enable flashback and create a restore point.  In case the upgrade fails, you can restore DB using guarantee restore point.

NOTE:

No need to enable Flashback Database from 11.2.0.1 onward.

Database MUST be in Archive Log mode.

Compatible parameter MUST NOT change to the higher version.

You can check whether the parameter is on or off.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------------

NO

Find out if the ARCHIVELOG mode enabled or not.

select log_mode from v$database;

If not:

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

alter system set db_recovery_file_dest='/u3/flashback/KARDBPRD/PRE_UPGRADE' scope=both;

alter system set db_recovery_file_dest_size=10G scope=both;

alter database flashback on; ç no need if DB version 11.2.0.1 onward  

show parameter recovery;

Now create a restore point:

select * from V$restore_point;

create restore point pre_upgrade guarantee flashback database;

col name for a20

col GUARANTEE_FLASHBACK_DATABASE for a10

col TIME for a60

set lines 190

select name, guarantee_flashback_database, time from v$restore_point;

IF UPGRADE FAILS:

In general, there are very less chance to fail the upgrade, If you complete precheck properly. Even so it the upgrade fails you can revert to previous version.

1. Shutdown immediate;

2. Set ORACLE_HOME to 11g or whatever you have the previous version.

3. Start up mount (with the 11g spfile)

4. select * from v$restore_point;

5. flashback database to restore point pre_upgrade; ( this restore point was created before upgrade)

6. alter database open resetlogs;

If Upgrade completed successfully. You can drop restore point: 

col name for a20

col GUARANTEE_FLASHBACK_DATABASE for a10

col TIME for a60

set lines 190

select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

drop restore point PRE_UPGRADE;

select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

Completed!