Here are the steps to use Oracle guarantee restore point with in Oracle Flashback to roll back changes in 12.1.0.2 database when backout from Apps changes becomes necessary.
Pre-steps:
1. Run full level 0 backup through OEM (or by script) on EBS_DB
2. Setup recovery directory for EBS_DB if you have not yet. Here is example to do that. You need to find location & space for flashback.
SQL> alter system set db_recovery_file_dest_size='100G' scope=both;
SQL> alter system set db_recovery_file_dest='/path/to/EBS_DB/flashback' scope=both;
3. Send email to all parties to confirm it is complete
Steps
1. Checking App Admin before run archivelog backup
2. Setup GRP:
SQL> create restore point JDK_BACKOUT_GRP guarantee flashback database;
3. Send out email for confirmation
4. After all changes worked and are confirmed, drop GRP. Please make sure to drop GRP, otherwise database might get frozen if flashback area is full.
SQL> drop restore point JDK_BACKOUT_GRP;
Backout steps
If things went bad and decision to roll all changes back is made, you need to restore database EBS_DB by following steps
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to restore point JDK_BACKOUT_GRP;
SQL> alter database open resetlogs;
SQL> drop restore point JDK_BACKOUT_GRP;
After that, database will be restored back to the time point when JDK_BACKOUT_GRP was created. You may do a database backup before run Backout steps, in case you need it to troubleshoot problem later.
