How to quickly restore to a clean database using Oracle’s restore point

Problem:
----------------------------------------------------------------------------------------------------------Often while conducting benchmarking tests, it is required to load a clean database before the start of a new run. One way to ensure a clean database is to recreate the entire database before each test run, but depending on the size of it, this approach may be very time consuming or inefficient.

Solution:
----------------------------------------------------------------------------------------------------------This article describes how to use Oracle’s flashback feature to quickly restore a database to a state that existed just before running the workload. More specifically, this article describes steps on how to use the ‘guaranteed restore points’.

Restore point: Restore point is nothing but a name associated with a timestamp or an SCN of the database. One can create either a normal restore point or a guaranteed restore point. The difference between the two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available (assuming you have enough space in the flash recovery area).

NOTE: In this article Flashback logging was not turned ON.

Guaranteed Restore point:

Prerequisites: Creating a guaranteed restore point requires the following prerequisites:

• The user must have the SYSDBA system privileges
• Must have created a flash recovery area
• The database must be in ARCHIVELOG mode

Create a guaranteed restore point:After you have created or migrated a fresh database, first thing to do is to create a guaranteed restore point so you can flashback to it each time before you start a new workload. The steps are as under:

1. $> su – oracle
2. $> sqlplus / as sysdba;
3. Find out if ARCHIVELOG is enabled
   SQL> select log_mode from v$database;
   If step 3 shows that ARCHIVELOG is not enabled then continue else skip to step 8 below.
4. SQL> shutdown immediate;
5. SQL> startup mount;
6. SQL> alter database archivelog;
7. SQL> alter database open;
8. SQL> create restore point CLEAN_DB guarantee flashback database;
   where CLEAN_DB is the name given to the guaranteed restore point.

Viewing the guaranteed restore point
SQL> select * from v$restore_point;

Verify the information about the newly created restore point. Also, note down the SCN# for reference and we will refer to it as “reference SCN#”

Flashback to the guaranteed restore pointNow, in order to restore your database to the guaranteed restore point, follow the steps below:

1. $> su – oracle
2. $> sqlplus / as sysdba;
3. SQL> select current_scn from v$database;
4. SQL> shutdown immediate;
5. SQL> startup mount;
6. SQL> select * from v$restore_point;
7. SQL> flashback database to restore point CLEAN_DB;
8. SQL> alter database open resetlogs;
9. SQL> select current_scn from v$database;

Compare the SCN# from step 9 above to the reference SCN#.

NOTE: The SCN# from step 9 above may not necessarily be the exact SCN# as the reference SCN# but it will be close enough. 

How to find ICM log on Oracle Apps 11i and R12

I keep forgetting this ALL the time, so I thought I note it down on the intrawebs.

All Concurrent Mangers log files are located in the $APPLCSF/$APPLLOG location.
The following bellow options are available to get the latest log files.

Option1:

Login to Apps User and source environment file
cd $APPLCSF/$APPLLOG
ls -lrt *$TWO_TASK*
Internal Concurrent Manager Log:_MMDD.mgr

Here you can see other list of log files which are related to concurrent mangers.The name conversation for the concurrent manger log files are
Standard manager log: w.mgr
Transaction manager log: t.mgr
Conflict Resolution manager log: c.mgr
*Where: is the concurrent process id of the manager

Option2:

Log-in to the Apps User
Run Source environment file
Connect to sqlplus and run this query

SELECT 'ICM_LOG_NAME=' || fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'
AND fcp.process_status_code = 'A';

Sample Output:
'ICM_LOG_NAME='||FCP.LOGFILE_NAME
-------------------------------------------------------------
ICM_LOG_NAME=/applprod/comndev/admin/log/DEV_moon1/DEV_0426.mgr

Option3:

Log in to Sysadmin Responsibility
Go to ->Concurrent -> Manager -> Administer -Processes -> Click button Internal Manager Log.