Friday, August 21, 2009

Steps to clone Oracle Applications database from PROD to TEST

Steps to clone Oracle Applications database from PROD to TEST
Steps to clone a database from PROD to TEST

The following is a brief Guideline on steps to follow to clone you Oracle Applications from PROD to TEST

1) log In to PROD Server as applprod user ; go to /applprod/prodappl ; run APPSORA.env
2) sqlplus system/manager
SQL> alter database backup controlfile to trace;
This will generate a trace file in $ORACLE_HOME//admin/_/udump/*.trc
Go to above directory
Run ls - alt
Find out latest trace file and edit the trace file
SID to desired SID (PROD to TEST)

3) Change dbf path accordingly.
Shutdown database (Shutdown normal both production & test server by using prod_stop.sh , your PROD stop script, and test_stop.sh your TEST stop script, respectively )

4) Now ftp to your server as applprod user and download the file from PROD server to Test server in /appltest directory .

5) Rename /appltest/testdata directory to /appltest/testdatabk
Create a new testdata directory in /appltest
Copy all datafiles, control file etc.of to new /appltest/testdata from PROD
(E.g rcp applprod @ ebsnode2:/applprod/proddata/*.dbf) and rename cntrl*.dbf to cntrl*.bak on test server

6) Run TEST instance .env file from /appltest/testdb/9.2.0 .
Now run the edited trace file (this will recreate the TEST database with PROD configuration) (sqlplus /nolog @edited script.sql)
This will start up TEST oracle database

7) Change profile_option_values from FND_profile_option_values table

==================================================
Select * from fnd_concurrent_queues
Where control_code=’N’

Update fnd_concurrent_queues
set target_node=NULL

Table ICX_Parameters set session_cookie= null

8) Clear concurrent Manager table from toad by using the following commands:

==================================================================================
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;

UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

UPDATE fnd_concurrent_queues
SET target_node = null;

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R'

===================================================================================


9) Copy the Server_ID from $FND_TOP/secure/ebsnode1_test.dbc and update the server_id column of FND_NODES table with that. Also change the NODE_NAME = EBSNODE1
Run the . APPSORA.env from /appltest/testappl directory


10) Now run following Java commands from command prompt:


=================================================================================
java oracle.apps.fnd.security.AdminAppServer apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/TEST_ebsnode1/test.dbc

java oracle.apps.fnd.security.AdminAppServer apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/ebsnode1.<>_test.dbc

java oracle.apps.fnd.security.AdminAppServer apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/ebsnode1_test.dbc

====================================================================================

11)Shutdown the Oracle database. ; Start application by using test_start.sh from /appltest dir
If anything goes wrong check at http://:8000/OA_HTML/jsp/fnd/aoljtest.jsp

12) Login to Oracle Applications
Change Profile options from Application –
System Administrator(Responsibility) Profile-> System-> concurrent: GSM enabled = ‘N’
Thats's all folks

2 comments:

sambaman said...

Why aren't you using the Auto Config to clone the PROD to TEST the easy way?! Without going into too much detail:
(i) Run adpreclone.pl for dbTier
(ii) Run adpreclone.pl for AppsTier
(iii) backup controlfile to trace as you have described
(iv) Shutdown APPS and DB environments
(v) Copy everything to your TEST server unless TEST environment is on the same server!!
(vi) Rename DB, datafiles and re-create control files using trace file generated in (iii)
(v) Run adcfgclone.pl for dbTier
(vi) Run adcfgclone.pl for appsTier

I find this method much cleaner and easier to clone E-Business environment. Just my point of view.

sambaman said...

Just to clarify where I've mentioned AutoConfig is actually "Rapid Clone".