Restoring a restore point in Oracle EBS Database

Restoring a restore point in Oracle EBS Database

Restoring a restore point in  Oracle EBS Database configured with PCP running on two nodes Apps Tier and two nodes DB Tier.


At a high level the steps to flashback an Oracle EBS Database are as follows:

1)Shutdown applications
2)Shutdown database (all nodes)
3)Startup mount one instance
4)Execute flashback command
5)alter database open resetlogs;
6)Startup all database nodes (typically I use srvctl start database –d and then check CRS to make sure everything is up).
7)Restart Applications

Additionally ,when starting the CM’s we can run cmclean first,
then start the CM’s, and then execute the CM purge job with a retention of 1 day or
something to attempt to clear out log files.

This is a “nice to have” as we’ve seen sometimes after a flashback the log files don’t match the process that was executed in CM.

Finally,do the Health check up by submitting the concurrent active user request.


TESTDB:
=======

srvctl disable disables target, meaning CRS will not consider it for automatic startup, failover,
or restart. This option is useful to ensure an object that is down for maintenance is not
accidentally automatically restarted.


Syntax:

srvctl disable database -d db_unique_name

srvctl disable database -d TESTDB

$srvctl status database –d TESTDB


>srvctl status database -d TESTDB
Instance TESTDB1 is running on node node1
Instance TESTDB2 is running on node node2


Step 1:Shut both the instances in the database Database normally
 
Node 1:
============

$srvctl stop database –d TESTDB


>srvctl status database -d TESTDB
Instance TESTDB1 is not running on node node1
Instance TESTDB2 is not running on node node2

(OR)

SQL>shut immediate


SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
TESTDB

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


Node 2:
==========

SQL>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2:Startup mount and Roll Database back to restore point

SQL>startup mount


SQL> startup mount
ORACLE instance started.

Total System Global Area 1.3434E+10 bytes
Fixed Size                  2160552 bytes
Variable Size            4697622616 bytes
Database Buffers         8589934592 bytes
Redo Buffers              143785984 bytes
Database mounted.


SQLPLUS>FLASHBACK DATABASE TO RESTORE POINT 'someLOGICALNAME';

SQLPLUS>FLASHBACK DATABASE TO RESTORE POINT BASELINE_RESTORE_11DEC12;                                                                                                         ';

FLASHBACK DATABASE TO RESTORE POINT BASELINE_RESTORE;

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                          PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------
1.2621E+13                     6 YES   3.6739E+10
19-JUL-12 02.27.07.000000000 PM
                                                                            YES
BASELINE_RESTORE


SQL> SHUT IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1.3434E+10 bytes
Fixed Size                  2160552 bytes
Variable Size            4697622616 bytes
Database Buffers         8589934592 bytes
Redo Buffers              143785984 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT BASELINE_RESTORE;
FLASHBACK DATABASE TO RESTORE POINT BASELINE_RESTORE


Step 5:Monitor progress of a flashback operation:

SQL> select opname,to_char(start_time,'MM-DD-YYYY HH24:MI:SS'),time_remaining,elapsed_seconds from v$session_longops;

OPNAME
----------------------------------------------------------------
TO_CHAR(START_TIME, TIME_REMAINING ELAPSED_SECONDS
------------------- -------------- ---------------
Flashback Database
12-14-2012 01:22:51           1395             744


Step 6 a:Open the Database with resetlogs option

SQL>alter database open resetlogs;


Step 6 b:Start and check both the Database instances are up & running.



$srvctl start database -d TESTDB

$srvctl enable database -d TESTDB


$srvctl status database –d TESTDB


>srvctl status database -d TESTDB
Instance TESTDB1 is running on node node1
Instance TESTDB2 is running on node node2


Step 6 c :Start all the Applications and Do Health Check of the Application by submitting active user request

Start the Application from the Primary node in PCP setup,first start listeners on both the nodes ,than start the concurrent managers and than finally start the Applications on both the node.







Step 7:Informed the team flashback is completed to restore point.



Hope it helps.

Happy Apps DBA learning.

 Best regards,

Rafi.