Oracle-L: RE: Oracle Applications Development Database Refresh

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Fri, 05 Jul 2002 16:03:20 -0800
Message-ID: <F001.004907D4.20020705160320@fatcity.com>

Jared,

> In addition to the usual SAP system that runs most of our
> company, we have
> Oracle Apps 10.7 HR. For some reason the HR folks didn't
> like the SAP HR
> module, so I have SAP and Oracle Apps.
>
> The problem is a request I have to refresh the Dev database from the
> Production database.

I haven't read any official documentation for 10.7 'cloning' (as the Apps-aware call it). However, it is pretty simple compared to 11.0 and 11i if this is NOT 10.7 NCA. In addition, this also changes if you are using 10.7 Character, 10.7 SC (Smart Client) or 10.7 NCA, so do let us know. In simple terms, you will have to do the following (assuming you have a copy - hot or cold - of the DB):

On the Application/Database server side:

Copy over the APPL_TOP/Custom top (basically all owned by 'applmgr') and Database files to the respective locations. Adjust the environment variables for log/TOP locations including APPLCSF (conc. program output logs) and any Custom tops. I assume you already have ORACLE_HOME copied over and setup (with envs, etc.) - this does not change frequently.
Change the listener ports for the database, FNDFS listener and OEORPC listener if required.
Startup the Database and listeners - recover to a point in time if from a hot backup. Do NOT startup the Concurrent Managers yet. Delete _ANY_ DBlinks that point to other production systems - this may cause some Invalid objects, and you may have to ignore/fix them as the case may be.
Change the global name (alter database rename global_name ..)
Reset all Alerts from 'Enabled' to 'Disabled'. This can be achieved either from the Alerts form or using the following SQL (unless you want to do this off the Alerts screen!):

update applsys. alr_alerts set enabled_flag = 'N' where enabled_flag = 'Y'; This will disable all alerts and avoid erroneous messages to Users/Functional folks.

6. Update all pending jobs to reset copies to print. This is to avoid double-printing when you (or someone else) turn on some of the Concurrent jobs back on - at least you can avoid confusion from double-printing of reports. This can be done using the following SQL:

UPDATE APPS.FND_CONCURRENT_REQUESTS
SET NUMBER_OF_COPIES = 0; 7. Startup your forms interface (not the Concurrent Manager) and go to the Profiles screen (or the Profile->System form). Modify profile options: Query OE: Transaction Manager - change to the OEORPC listener name Query Site Name - change to the right name (this changes the Help->About information in forms)

8. Change the APPS/APPLSYS and other schema passwords. Open a SQL*Plus using 'SYSTEM' or a DBA account in a separate window. Open the 'Security->ORACLE->Register' (Oracle Users) screen. Enter the Password twice for each of APPLSYS, APPS and schemas and Commit, making sure that you change the corresponding Oracle user (APPLSYS/APPS/Schema) as you change them. It is critical to commit this, and make sure that this does not break inbetween - as the APPLSYS/APPS password combination needs to be correct and match in both the internal FND tables as well as the SYS.USER$ tables... You may also have to reset the SYSADMIN passwords.

9. I would also cancel all pending concurrent jobs before starting up the Concurrent Manager, but that is something that you will need to agree with the functional folks. If this is Ok, you can use the following SQL: update apps.fnd_concurrent_requests
set phase_code = 'C',
status_code = 'D'
where phase_code = 'P';

Startup the Concurrent Managers (using 'applmgr' account!) and watch the logs in $APPLCSF/log<DB>/*.mgr for any problems/issues.
You may also choose to resize the Queue sizes on the Dev instance. This reduces the number of processes that start up from the CM. This can be done from the 'Concurrent Managers' screen (Concurrent->Manager->Define) - click on the 'Work Shifts' button and change the value in 'Processes'. The Internal Manager will reduce this number at the next cycle.

Depending on your customizations and environment, you may have to perform some additional steps. I have not included the steps for the Client side : I will have to know what you use and can suggest what needs to be done. This is off some of our internal documentation - I have given you the essentials that should make sense in a general Apps installation.

Hth,
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Want to know about a carpenter who built a bridge with two sticks and three nails? Write me for details!

The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jul 05 2002 - 19:03:20 CDT