OAM and EBS Breaks after Cloning

We are in the process of integrating E-Business Suite with Oracle Access Manager (OAM). Its setup in DEV and TEST but not in PROD yet.    There was a requirement to reclone DEV and as a result the integration with OAM broke.

We ran all the configuration steps on the EBS side, such as:

• $FND_TOP/bin/txkrun.pl -script=SetSSOReg -registerinstance=yes
• Verifying ASADMIN was active and the correct password.
• Profile options to configure authentation:
• Applications SSO Type
• Applications SSO Login types
• etc...
• Business Events to make sure account creation and modifications synchronizes correctly.
• We skipped all of the tasks to be performed on the OAM side since they were already done.

However, when restarting Access Gate we would see the following error in the logs:

weblogic.application.ModuleException:

        at weblogic.jdbc.module.JDBCModule.prepare(JDBCModule.java:302)

        at weblogic.application.internal.flow.ModuleListenerInvoker.prepare(ModuleListenerInvoker.java:199)

        at weblogic.application.internal.flow.DeploymentCallbackFlow$1.next(DeploymentCallbackFlow.java:517)

        at weblogic.application.utils.StateMachineDriver.nextState(StateMachineDriver.java:52)

        at weblogic.application.internal.flow.DeploymentCallbackFlow.prepare(DeploymentCallbackFlow.java:159)

        Truncated. see log file for complete stacktrace

Caused By: weblogic.common.resourcepool.ResourceSystemException:

Could not connect to 'oracle.apps.fnd.ext.jdbc.datasource.AppsDataSource'.

The returned message is: ORA-01017: invalid username/password; logon denied

We triple checked the datasource credentials defined in the Access Gate server but still nothing worked.  It turns one of the steps to deploy Access Gate was to copy the dbc file from $FND_SECURE over to the $MW_HOME/appsutil/accessgate/dev directory.   Since DEV was recloned the APPL_SERVER_ID value had changed.    We re-copied this file, bounced the Access Gate server and everything started working again.

Since we don't have EBS integrated with OAM in PROD yet i'm not sure if we will need to do any additional steps after a clone.   I'm thinking all the steps above will apply except for maybe business events as they will already be configured properly in PROD.   I'll update this post if I have to do anything else.

R12 Cloning: adcrdb.sh missing from file system.

This weekend I had to build an R12 environment.   We use Oracle VM in our development environment so this made the who process much easier.    Before I would have to setup a new server, install the OS, patch it, put in the Oracle pre-req's then clone.   Now I just need to run pre-clone, shutdown the environment, copy it, rename/reconfigure the vm and run post clone.

When I ran adcfgclone.pl dbTier tho I hit the error described in the following note:

On Release 12 adcfgclone is failing with AC-00423, RC-50014 adcrdb.sh missing from file system [ID 549872.1]

The solution is to copy adcrdb.sh from the source system and place it under $ORACLE_HOME/appsutil/template

However, when I executed adcfgclone.pl again I hit the same error.   adcrdb.sh would get deleted from the template directory.

The solution was to run autoconfig, preclone and recopy the db files and software over.

Well, at least I didn't have to build a new server.. Saved me a little time anyways.

If your having cloning issues you may want to take a look at the other issues I encountered as well.  Just click on the EBS Cloning label on the right side.

R12 Cloning: Can't login as apps

This past week I was cloning a DEV environment from PROD.   I noticed that the adcfgclone.pl script appeared to be hanging.  Looking at the logs it was stuck on:


Attempting to connect to database using :

jdbc:oracle:thin:@myserver:1553:DEV,APPS
        DbSid     :DEV
        DbHost    :myserver
        DomainName:mydomain.com
        DbPort    :1553

So the first thing I tried was logging into the database using tns, ex:  sqlplus apps/apps@DEV.   This also hung.   However, other accounts such as dbsnmp, etc were fine.

Using Grid Control I noticed there was a lot of contention in the database:

   

Drilling down into concurrency it was very easy to see that the issue was related to library cache locks.

Looking at the sessions involved I found that they were related to our SOA server and other integrated applications.    Since I am in the process of cloning this database from PROD, the databases are identical, down to the account passwords.   So all of these connection attempts were using the incorrect password.   I reset the passwords to their appropriate DEV values a little later in the cloning process.

I did a quick search on Metalink and this note jumped out at me:

LIBRARY CACHE LOCKS DUE TO INVALID LOGIN ATTEMPTS [ID 1309738.1]

The recommendation is to set the following event:

EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"

I set this parameter, bounced the database, reran adcfgclone.pl and it ran with no issues.   

So, just an FYI.  If your cloning an EBS environment and have alot of integrated applications/processes that you may run into this issue until you either reset the account passwords or set this event.

R12: Sometimes cloning doesn’t remove all source references

I’m in the process of applying the latest round of security patches to our environment.   While patching TEST (I’ve already applied it to a sandbox and DEV environment), I hit the following error:

OPatch found the word "error" in stderr, please look into it.
You may have to run the make again.
Stderr Output (from /var/TEST/apps/tech_st/10.1.2/.patch_storage/12828135/make_local.stderr):
/usr/bin/ld: cannot find –ljava
collect2: ld returned 1 exit status
make[1]: *** [/u01/TEST/apps/tech_st/10.1.2/sysman/lib/libnmemso.so] Error 1
make: *** [libnmemso] Error 2




I've hit a similar issue in the past, documented in post http://newappsdba.blogspot.com/2009/01/cloning-leftovers.html

The issue was with the same file:

$ORACLE_HOME/sysman/lib/env_sysman.mk

The following line was still pointing to the source system:


JRE_LIB_DIR=/u01/PROD/apps/tech_st/10.1.2/jdk/jre/lib/i386

I changed it to TEST, reran the patch and it completed successfully.   Just a reminder that cloning doesn’t update all the necessary files.

R12.1.2 Cloning Issues

I have encountered two issues with cloning after our upgrade to R12.1.2.

The first:

[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:
<filename> <phase> <return code where appropriate>
[APPLY PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /u01/VIS/db/tech_st/11.2.0/perl/bin/perl -I /u01/VIS/db/tech_st/11.2.0/perl/lib/5.8.3 -I /u01/VIS/db/tech_st/11.2.0/perl/lib/site_perl/5.8.3 -I /u01/VIS/db/tech_st/11.2.0/appsutil/perl /u01/VIS/db/tech_st/11.2.0/appsutil/clone
ouicli.pl INSTE8_APPLY 255
AutoConfig is exiting with status 1
RC-50013: Fatal: Instantiate driver did not complete successfully.
/u01/VIS/db/tech_st/11.2.0/appsutil/driver/regclone.drv


This error was caused by Rapid Clone using an incorrect version of perl.  By default my system has v5.8.8 installed in /usr/bin.   The solution was to update my PATH variable to point to $ORACLE_HOME/perl/bin, which is v5.10.0. 

The second issue I encountered while building a new environment. I normally bring over the database home directory and reconfigure it for the new environment with adcfgclone.pl dbTechStack, then I use RMAN to duplicate the database.  However, then I tried to startup the database in nomount mode I received an ORA-1031.

I traced the problem using the following Metalink Notes:

Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues [ID 233223.1]
UNIX: Checklist for Resolving Connect AS SYSDBA Issues [ID 69642.1]



Which brought me to note:

SYSDBA and SYSOPER Privileges in Oracle [ID 50507.1]


It appears that my config.o file was corrupted.   You can fix it by relinking:

mv config.o config.o.orig
make -f ins_rdbms.mk ioracle

I say that my config.o was corrupted because config.c looks ok and I didn’t make any changes to it. For a detailed description about config.o take a look at 50507.1.

I encountered this back in Dec and noticed the other day that Oracle has come out with a patch:

Error: ORA-01031: insufficient privileges : SQLPLUS Fails After Rapid Clone adcfgclone.pl dbTier/dbTechStack With 11gR2 Database [ID 1276420.1]

I’m not sure if its my problem since they mention it occurs with dbTechStack and I don’t recall encountering it there.   But if you are cloning an environment and don't have time to test this patch, try reconfiguring your database Oracle Home with dbTechStack, rebuild config.o then run adcfgclone with the database parameter.  (Remember dbTier = database + dbTechStack.)

Web Home 10.1.3.5 Upgrade – OPMN is failing to start

As part of our upgrade from 12.0.6 to 12.1.2 the Web Oracle Home (10.1.3) needed to upgraded to 10.1.3.5.   I encountered the following issue:



I actually encountered this same error while upgrading two cloned environments.  Each time the cause was different.  The easiest one to check is to verify that your ORACLE_CONFIG_HOME is pointing to $INS_TOP/ora/10.1.3 directory. If thats ok and this is a cloned environment, rebuild your inventory.    For a complete guide of how to rebuild an R12 inventory check Metalink note:  How to create, update or rebuild the Central Inventory for Applications R12 [ID 742477.1].


If that doesn’t work, try creating a clean inventory: How to Create a Clean oraInventory in Release 12 [ID 834894.1]


Both of those notes talk about running the $IAS_ORACLE_HOME/appsutil/clone/ouicli.pl script.  I also hit an error running this:

NON-COMPLIANT: /u03/VIS/apps/tech_st/10.1.3/oraInst.loc does not point to an inventory inside the currenOME
Rapid Clone only supports oraInst.loc at that location if its content points to an inventory inside the s_HOME
Please make the necessary changes to the following file:
/u03/VIS/apps/tech_st/10.1.3/oraInst.loc


The weird thing here, is that I compared the oraInst.loc files from my first cloned environment upgrade and they are identical.  In both environments the oraInst.loc file is pointing to:

inventory_loc=/u01/VIS/inst/apps/VIS_servername/admin/oraInventory


For the ouicli.pl script I found OUICLI.PL Fails when Running adcfgclone with R12 if Global Inventory does not exist [ID 458653.1]  which instructs you to create an oraInventory directory inside 10.1.3 ORACLE_HOME, then modify the 10.1.3 ORACLE_HOME/oraInst.loc file and point it to that directory.  Afterwords re-run the cloning script, adcfgclone.pl.

I didn’t run adcfgclone.pl, instead I re-ran the ouicli.pl script and tried to apply the 10.1.3.5 patch again.   This time the OPMN failing to start error did not occur.

Note 458652.1 says the cause was that the global inventory wasn’t found under default locations or there were permissions issues.   I verified that both of those issues were ok.

I believe the problem stems from the fact that I didn’t totally clean this environment before cloning.   There was an existing oraInventory from a previous clone and some other Oracle products that are no longer used.   

Refreshing VS Cloning an e-Business Suite Environment

Just a quick note on refreshing vs cloning, what each of them means and when you should perform them.

What is Refreshing?

A refresh is where the data in the target environment has been synchronized with a copy of production. This is done by taking a copy of the production database and restoring it to the target environment.

What is Cloning?

Cloning means that an identical copy of production has been taken and restore to the target environment. This is done by taking both a copy of the production database as well as all of the application files.

When should you Clone or Refresh?

There are a couple of scenarios when cloning should be performed:

1. Building a new environment.
 
2. Patches or other configuration changes have been made to the target environment so that they are now out of sync.

3. Beginning of development cycles. Before major development efforts take place, its wise to re-clone dev, test environments so that your 100% positive that the environments are in sync.

There is only one scenario in which you should refresh an environment:

1. Your 100% confident that the environments are in sync and need an updated copy of the production data in order to reproduce issues.

Technically, if proper change control processes are being followed, test and production environments should be identical. So in the case of test, you should be able to get away with performing refreshes. However, to ease concerns and for comfort levels, test environments are usually re-cloned at the beginning of new development cycles as well.

If I have missed any scenarios, feel free to comment.

Related Articles:
R12 Cloning with RMAN

R12 - Cloning from an RMAN backup using duplicate database

Since most DBA's are using rman for their backup strategy I thought I would put together the steps to clone from an rman backup. The steps you follow are pretty much the same as described in Appendix A: Recreating the database control files manually in Rapid Clone in Note 406982.1 - Cloning Oracle Applications Release 12 with Rapid Clone.

Here are the steps:

1. Execute preclone on all tiers of the source system. This includes both the database and application tiers. (For this example, TEST is my source system.)
   
   For the database execute: $ORACLE_HOME/appsutil/scripts/<context>/adpreclone.pl dbTier
   Where context name is of the format <sid>_<hostname>
   
   For the application tier: $ADMIN_SCRIPTS_HOME/adpreclone.pl appsTier
   
   
2. Prepare the files needed for the clone and copy them to the target server.
   
   • Take a FULL rman backup and copy the files to the target server and place them in the identical path. ie. if your rman backups go to /u01/backup on the source server, place them in /u01/backup on the destination server. To be safe, you may want to copy some of the archive files generated while the database was being backed up. Place them in an identical path on the target server as well.
     
   • Application Tier: tar up the application files and copy them to the destination server. The cloning document referenced above ask you to take a copy of the $APPL_TOP, $COMMON_TOP, $IAS_ORACLE_HOME and $ORACLE_HOME. Normally I just tar up the System Base Directory, which is the root directory for your application files.
   • Database Tier: tar up the database $ORACLE_HOME.
     
     ex. from a single tier system. The first tar file contains the application files and the second is the database $ORACLE_HOME
     
     [oratest@myserver TEST]$ pwd
     /u01/TEST
     [oratest@myserver TEST]$ ls
     apps db inst
     [oratest@myserver TEST]$ tar cvfzp TEST_apps_inst_myserver.tar.gz apps inst
     .
     .
     [oratest@myserver TEST]$ tar cvfzp TEST_dbhome_myserver.tar.gz db/tech_st
     
     Notice for the database $ORACLE_HOME I only added the db/tech_st directory to the archive. The reason is that the database files are under db/apps_st and we don't need those.
   • Copy the tar files to the destination server, create a directory for your new environment, for example /u01/DEV. (For the purpose of this article I will be using /u01/DEV as the system base for the target envrionment we are building and myserver is the server name.)
     
   • Extract each of the tar files with the command tar xvfzp
     
     Ex. tar xvfzp TEST_apps_inst_myserver.tar.gz
     
     
3. Configure the target system.
   
   • On the database tier execute adcfgclone.pl with the dbTechStack parameter.
     
     For example. /u01/DEV/db/tech_st/10.2.0/appsutil/clone/bin/adcfgclone.pl dbTechStack
     
     By passing the dbTechStack parameter we are tell the script to configure only the necessary $ORACLE_HOME files such as the init file for the new environment, listener.ora, database environment settings file, etc. It will also start the listener.
     
     You will be prompted the standard post cloning questions such as the SID of the new environment, number of DATA_TOPS, Oracle Home location, port settings, etc.
     
     Once this is complete goto /u01/DEV/db/tech_st/10.2.0 and execute the environment settings file to make sure your environment is set correctly.
     
     [oradev@myserver 10.2.0] . ./DEV_myserver.env
     
     
4. Duplicate the source database to the target.
   
   • In order to duplicate the source database you'll need to know the scn value to recover to. There are two wasy to do this. The first is to login to your rman catalog, find the Chk SCN of the files in the last backupset of your rman backup and add 1 to it.
     
     Ex. Output from a rman> List backups
     .
     .
     List of Datafiles in backup set 55729
     File LV Type Ckp SCN Ckp Time Name
     ---- -- ---- ---------- --------- ----
     7 1 Incr 5965309363843 15-JUN-09 /u02/TEST/db/apps_st/data/owad01.dbf
     .
     .
     So in this case the SCN we would be recovery to is 5965309363843 + 1 = 5965309363844.
     
     The other method is to login to the rman catalog via sqlplus and execute the following query:
     
     select max(absolute_fuzzy_change#)+1,
     max(checkpoint_change#)+1
     from rc_backup_datafile;
     
     Use which ever value is greater.
     
   • Modify the db_file_name_convert and log_file_name convert parameters in the target init file. Example:
     
     db_file_name_convert=('/u02/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/',
     '/u01/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/')
     
     log_file_name_convert=(/u02/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/',
     '/u01/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/')
     
   • Verify you can connect to source system from the target as sysdba. You will need to add a tns entry to the $TNS_ADMIN/tnsnames.ora file for the source system.
     
   • Duplicate the database. Before we use rman to duplicate the source database we need to start the target database in nomount mode.
     
     Start rman:
     
     rman target sys/<syspass>@TEST catalog rman/rman@RMAN auxiliary /
     
     If there are no connection errors duplicate the database with the following script:
     
     run {
     set until scn 5965309363844;
     allocate auxiliary channel ch1 type disk;
     allocate auxiliary channel ch2 type disk;
     duplicate target database to DEV }
     
     The most common errors at this point are connection errors to the source database and rman catalog. As well, if the log_file_name_convert and db_file_name_convert parameters are not set properly you will see errors. Fix the problems, login with rman again and re-execute the script.
     
     When the rman duplicate has finished the database will be open and ready to proceed with the next steps.
     
     
   • Execute the library update script:
     
     cd $ORACLE_HOME/appsutil/install/DEV_myserver where DEV_myserver is the <context_name> of the new environment.
     
     sqlplus "/ as sysdba"@adupdlib.sql
     
     If your on linux replace with so, HPUX with sl and for windows servers leave blank.
     
     
   • Configure the target database
     
     cd $ORACLE_HOME/appsutil/clone/bin/adcfgclone.pl dbconfig
     
     Where is $ORACLE_HOME/appsutil/DEV_myserver.xml
     
     
5. Configure the application tier.
   
   cd /u01/DEV/apps/apps_st/comn/clone/bin
   perl adcfgclone.pl appsTier
   
   You will be prompted the standard cloning questions consisting of the system base directories, which services you want enabled, port pool, etc. Make sure you choose the same port pool as you did when configuring the database tier in step 3.
   
   Once that is finished, initialize your environment by executing
   
   . /u01/DEV/apps/apps_st/appl/APPSDEV_myserver.env
   
   
   
6. Shutdown the application tier.
   
   cd $ADMIN_SCRIPTS_HOME
   ./adstpall.sh apps/<source apps pass>
   
   
7. Login as apps to the database and execute:
   
   exec fnd_conc_clone.setup_clean;
   
   I don't believe this step is necessary but if you don't do this you will see references to your source environment in the FND_% tables. Every time you execute this procedure you need to run autoconfig on each of the tiers (db and application). We will get to that in a second.
   
   
8. Change the apps password. Chances are you don't want to have the same apps password as the source database, so its best to change it now while the environment is down.
   
   With the apps tier environment initialized:
   
   FNDCPASS apps/<source apps pass> 0 Y system/<source system pass>> SYSTEM APPLSYS <new apps pass>
   
   
9. Run autoconfig on both the db tier and application tier.
   
   db tier:
   cd $ORACLE_HOME/appsutil/scripts/DEV_myserver
   ./adautocfg.sh
   
   Application Tier
   cd $ADMIN_SCRIPTS_HOME
   ./adautocfg.sh
   
   
10. If there are no errors with autoconfig start the application. Your already in the $ADMIN_SCRIPTS_HOME so just execute:
    
    ./adstrtal.sh apps/<new apps pass>
    
    
11. Login to the application and perform any post cloning activities. You may want to override the work flow email address so that notifications goto a test/dev mailbox instead of users. We always change the colors and site_name profile options, etc. More details can be found in Section 3: Finishing tasks of the R12 cloning document referenced earlier.
    
    
    

Thats it, hopefully now you have successfully cloning an EBS environment using rman duplicate.

Cloning leftovers

Today while trying to run netca in a cloned environment, I was greeted with the following error:

UnsatisfiedLinkError exception loading native library: njni10
Exception in thread "main" java.lang.UnsatisfiedLinkError: get
at oracle.net.common.NetGetEnv.get(Native Method)
at oracle.net.config.Config.getNetDir(Unknown Source)

I noticed that under LD_LIBRARY_PATH ($ORACLE_HOME/lib32) a bunch of symbolic links were still pointing to the location of the SRC environment I cloned from. Included in that list was the file "ldflags”.

The solution was to fix all of these broken symbolic links and relink. Relinking tho I hit errors:

ld: fatal: library -ljava: not found
ld: fatal: library -ljvm: not found
ld: fatal: File processing errors.

Luckily I stumbled upon Note: 367535.1, which pointed me to the file $ORACLE_HOME/sysman/lib/env_sysman.mk. Within that makefile JRE_LIB_DIR was pointing to the SRC environment I cloned from as well. I changed it to the correct location for this environment and executed the relink command again. This time it was successful.

So basically, FYI, that if you hit weird errors in a cloned environment, it could be the result of some invalid pointers that weren’t modified during Rapid Clone. If you know of a fix, or have a handy list of things to check, feel free to share. I’ll add this to my post clone checklist.

ORA-00600 during adcfgclone.pl dbTier

While cloning a new environment (to me) today I hit the following error:

ORA-00600: internal error code, arguments: [skkxerrp], [skkxdllload],
[SlfFopen], [/u001/CRP1/oracle/crp1db10g/10.2.0/plsql/nativelib/d66/STANDARD__SYS__S__647.so], [No such file or directory], [], [], []

A search of metalink will result in a single hit, NOTE:382767.1. The note mentions two possible solutions:

• Setup native PL/SQL before cloning
  
• Modify the rapid clone template file to remove two plsql parameters.
  

Well, in this case i'm told the production environment has always had pl/sql native compilation, so number 1 didn't apply. If I performed the second potential solution then my newly cloned environment wouldn't be identical to production until I re-setup native compilation.

When I looked at the template file ($ORACLE_HOME/appsutil/template/afinitdb_102.ora), I noticed that by default the parameter plsql_code_type gets reset to INTERPRETED. I'm not sure why this happens, I would have assumed it would be set to the production value of NATIVE. So instead of performing the solutions described in the metalink note I change plsql_code_type to native and re-executed adcfgclone.pl dbTier, which this time completed without error.

Cleaning up the FND_NODES Table.

After a clone, you may notice that FND_NODES still contains entries for the source system. You may also see the same thing if you relocate services to a new node. You can query FND_NODES but an easy way to see this is via OAM (Oracle Applications Manager) on the opening overview screen:

(Hostnames blanked for obvious reasons..)



This particular environment has a single application tier and database tier, which means there are 7 extra rows. Note:260887.1 details how to clean up the FND_NODES table (11.5.10-12.0.x) and its very easy to do if your on the latest TXK Autoconfig rollup patch.

Here are the steps:

• Verify you have at least the TXK AUTOCONFIG ROLLUP PATCH Q (JUL/AUG 2007), patch number 5985992. My environment is a bit out of date so I applied the latest rollup patch S from April/May 2008, patch number 6372396. Personally, if a patch has been replaced I try to go with the latest unless there are too many pre-requisites. In the past i've been bitten by applying the minimum requirement only to have to apply the latest version a little while later. So if its not much more effort, it makes sense to do it.
  
  • The patch took about 2 hours to apply.
    
  • NOTE: Make sure you review the README file for this patch. If you have manually added product tops you may need to apply another patch. As well there are a few post-steps but the main one is to refresh the RDBMS AutoConfig files:
    

    
    Create the appsutil.zip file by executing:
     $ADPERLPRG $AD_TOP/bin/admkappsutil.pl
     (On Windows: %ADPERLPRG% %AD_TOP%\bin\admkappsutil.pl)
     This will create appsutil.zip in $APPL_TOP/admin/out .
    

    
    Copy/ftp the appsutil.zip file to your RDBMS server and:
    

    
    cd $ORACLE_HOME
    unzip -o appsutil.zip
    execute AutoConfig:
     $ORACLE_HOME/appsutil/scripts/<context_name>/adautocfg.sh
    

  
  
  
• From sqlplus execute the following command:
  
  SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
  COMMIT;
  
  This deletes data from a few FND tables such as FND_NODES but after AutoConfig has been executed they will contain the correct values.
  
• Run AutoConfig ($COMMON_TOP/admin/scripts/<context_name>/adautocfg.sh) on each tier.
  
• Startup the environment.
  

You should now have a nice and clean FND_NODES table:



So why would you want to do this? Personally it just annoyed me seeing incorrect values in OAM. As well, seeing production information in a cloned environment always makes me uneasy. There are other reasons as to why you would want to or may have to do this. If you search Metalink for FND_CONC_CLONE.SETUP_CLEAN you will get a couple of dozen hits. Quite a few notes are related to cloning, clean up or services not starting properly.

There you go, now you have a nice clean FND_NODES table!

iProcurement: Catalog Bulk Load

Just over a year ago we implemented Shared Application Filesystem in our EBS environment. This reduced maintenance time by my guesstimate of 25-50%. Its hard to say exactly how much time we have saved but previously we would have to apply a patch to two application tiers (web, forms) and a database tier (cm, db). If patches took an equal amount of time per tier it would be easy to say how much time we have saved but typically patches on the database tier take longer to apply.

One of the modules we have implement is iProcurement and as part of that, periodically catalog data has to be uploaded. Catalog data consists of items available and their prices. To peform this task a user with the "Internet Procurement Catalog Administration/eContent Manager" responsibility has to select a file on their system and load it via the "Bulk Load Items & Price Lists" screen.

Behind the scenes the catalog data file is stored as a temporary file on the application tier. The POXCDXBL concurrent request starts and if it detects the node names for the cm tier and application tier are different it executes a file transfer, regardless of whether or not you are using a shared filesystem. In order for you to take advantage of the shared file system you need to apply patch 4656509 which upgrades RemoteFile.java to version 115.4 and set the profile option "POR: Catalog bulkload Directory" to a location on your share.

At the time we implemented Shared Application Filesystem we investigated this but it would have required alot of patching. At least 11 patches, with some of them being family packs. Even tho our POR profile option (listed above) was set to a local directory, catalog bulk load was working fine. So we decided not to change the configuration as it would require alot of testing.

Skip ahead almost two years, with alot of changes in between, and this configuration was still working until we applied the Oct 07, Jan 08 and Apr 08 security patches. During testing users reported that catalog bulk load failed:

POXCDXBL module: Catalog Bulk Load - Items & Price Lists failing with:

{true,/apps/appsvis/visappl/fnd/11.5.0/secure/myserver_vis.dbc,1,1,0}]
https://myappserver.cognos.com:8005/OA_CGI/FNDWRR.exe?temp_id=2964546434
null
oracle.apps.fnd.cp.request.FileAccessException
at oracle.apps.fnd.cp.request.RemoteFile.getFile(RemoteFile.java:441)
at oracle.apps.icx.loader.LoadRequest.runProgram(LoadRequest.java:51)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)
[6/12/08 3:11:02 PM]  ********** Loader stopped **********

At first I assumed it was a cloning issue so I checked some typical problems we have experienced in the past. I noticed a few things that were wrong such as adovars.env settings, an error in our apache error_log file (below) and a few others but fixing them didn't resolve the error. I spent a fair bit of time on the apache SSL error because I had seen that in the past and it usually meant the txkrun.pl script to enable SSL failed for some reason. I re-executed it but the error did not go away (although, it did fix another problem). (As an aside, I have to say, debugging someone elses clones is a nightmare, especially if they didn't log everything.)

[Wed Jul 2 13:07:13 2008] [notice] caught SIGTERM, shutting down
[Wed Jul 2 13:07:24 2008] [notice] FastCGI: process manager initialized (pid 17232)
[Wed Jul 2 13:07:25 2008] [notice] Oracle HTTP Server Powered by Apache/1.3.19 configured -- resuming normal operations
[Wed Jul 2 13:10:34 2008] [error] mod_ssl: SSL handshake failed (server
myappserver.cognos.com:8005, client 10.69.92.24) (OpenSSL library error
follows)
[Wed Jul 2 13:10:34 2008] [error] OpenSSL: error:14094416:SSL routines:SSL3_READ_BYTES:sslv3 alert certificate unknown
Terminated

Moving on, Note:281530.1 talks about this feature and how it works behind the scenes in great detail. It also includes typical problems that may be encountered, workarounds and potential fixes. Using this note I narrowed down our problem to be at the point where the CM requests the catalog temp file from the application tier and places it in the CM tiers $APPLTMP directory. However, this file was of zero size.

At this point we decided to change the "POR: Catalog bulkload Directory" profile option to a directory on our shared filesystem and retest. This solved the issue. An SR with Oracle couldn't confirm whether or not the problem was caused by the security patches. So for now we will put this change into our release instructions since it is a better configuration (why transfer the file if can be available locally?). We have to apply the patches to one more environment before we go live and I will test catalog bulk loads before and after we apply the patches to see if they were the cause or it was indeed a cloning issue.

Refreshing an 11i Database using Rman

In order to facilitate troubleshooting we maintain a test environment which is a nightly copy of our 11i production environment. Since this environment is usually used to test data fixes it has to be as up to date as possible. To perform the database refresh we use rman's duplicate feature.

The goal of this article isn't just to provide the entire set of scripts and send you on your way. I think its safe to say that most EBS environments aren't identical, so its not like you could take them and execute with no issues. Instead i'll highlight the steps we follow and some of the key scripts.

NOTE: This doesn't include any pre-setup steps such as, if this is the first time duplicating the database make sure you have the parameters db_file_name_convert and log_file_name_convert specified in your test environments init file.

• Step 1: Shutdown the test environment. If you are using 10g then remove any tempfiles. In 10g, rman now includes tempfile information and if they exist you will encounter errors. Check this previous post. Startup the database in nomount mode.
  
• Step 2: Build a Rman Script. There are a couple of ways to recover to a point in time and we have decided to use SCN numbers. Since this process needs to be automated, we query productions rman catalog and determine the proper SCN to use and build an rman script. Here it is:
  

  
  set feedback off
  set echo off
  set serverout on
  spool $HOME/scripts/prod_to_vis.sql
  declare
  vmax_fuzzy number;
  vmax_ckp number;
  scn number;
  db_name varchar2(3) := 'VIS';
  log_file_dest1 varchar2(30) := '/dbf/visdata/';
  begin
  select max(absolute_fuzzy_change#)+1,
       max(checkpoint_change#)+1
       into vmax_fuzzy, vmax_ckp
  from rc_backup_datafile;
  if vmax_fuzzy > vmax_ckp then
  scn := vmax_fuzzy;
  else
  scn := vmax_ckp;
  end if;
  dbms_output.put_line('run {');
  dbms_output.put_line('set until scn '||to_char(scn)||';');
  dbms_output.put_line('allocate auxiliary channel ch1 type disk;');
  dbms_output.put_line('allocate auxiliary channel ch2 type disk;');
  dbms_output.put_line('duplicate target database to '||db_name);
  dbms_output.put_line('logfile group 1 ('||chr(39)||log_file_dest1||'log01a.dbf'||chr(39)||',');
  dbms_output.put_line(chr(39)||log_file_dest1||'log01b.dbf'||chr(39)||') size 10m,');
  dbms_output.put_line('group 2 ('||chr(39)||log_file_dest1||'log02a.dbf'||chr(39)||',');
  dbms_output.put_line(chr(39)||log_file_dest1||'log02b.dbf'||chr(39)||') size 10m;}');
  dbms_output.put_line('exit;');
  end;
  /
  spool off;
  
  This script produces a spool file called, prod_to_vis.sql:
  
  run {                                                                        
  set until scn 14085390202;                                                   
  allocate auxiliary channel ch1 type disk;                                    
  allocate auxiliary channel ch2 type disk;                                    
  duplicate target database to VIS                                             
  logfile group 1 ('/dbf/visdata/log01a.dbf',                          
  '/dbf/visdata/log01b.dbf') size 10m,                                 
  group 2 ('/oradata/dbf/visdata/log02a.dbf',                                  
  '/dbf/visdata/log02b.dbf') size 10m;}                                
  exit; 

  Note: Our production nightly backups are on disk which are NFS mounted to our test server.
  
  
• Step 3: Execute the rman script. Launch rman, connect to the target, catalog, auxiliary and execute the script above:
  
  ie.
  rman> connect target sys/syspasswd@PROD catalog rmancat/catpasswd@REPO auxiliary /
  
  
  You may want to put some error checking around rman to alert you if it fails. We have a wrapper script which supplies the connection information and calls the rman script above. Our refresh is critical so if it fails we need to be paged.
  

  
  rman @$SCRIPTS/prod_to_vis.sql
  if [ $? != 0 ]
  then
       echo Failed
       echo "RMAN Dupcliate Failed!"|mailx -s "Test refresh failed" pageremail@mycompany.com
       exit 1
  fi
  

  
  
• Step 4: If production is in archivelog mode but test isn't, then mount the database and alter database noarchivelog;
  
• Step 5: If you are using a hotbackup for cloning then you need to execute adupdlib.sql. This updates libraries with correct OS paths. (Appendix B of Note:230672.1)
  
• Step 6: Change passwords. For database accounts such as sys, system and other non-applications accounts change the passwords using alter user. For applications accounts such as apps/applsys, modules, sysadmin, etc use FNDCPASS to change their passwords.
  
  ie. To change the apps password:
  
  FNDCPASS apps/<production appspassword=> 0 Y system/<system_passwd> SYSTEM applsys <new apps passwd>
  
  
• Step 7: Run autoconfig.
  
• Step 8: Drop any database links that aren't required in the test environment, or repoint them to the proper test environments.
  
• Step 9: Follow Section 3: Finishing Tasks of Note:230672.1
  
  • Update any profile options which have still reference the production instance.
    
    Example:
    
    UPDATE FND_PROFILE_OPTION_VALUES SET
    profile_option_value = REPLACE(profile_option_value,'PROD','TEST')
    WHERE profile_option_value like '%PROD%
    
    Specifically check the FND_PROFILE_OPTION_VALUES, ICX_PARAMETERS, WF_NOTIFICATION_ATTRIBUTES and WF_RESOURCES tables and look for production hostnames and ports. We also update the forms title bar with the date the environment was refreshed:
    
    UPDATE apps.FND_PROFILE_OPTION_VALUES SET
    profile_option_value = 'TEST:'||' Refreshed from '||'Production: '||SYSDATE
    WHERE profile_option_id = 125
    ;
    
    
  • Cancel Concurrent requests. We don't need concurrent requests which are scheduled in production to keep running in test. We use the following update to cancel them. Also, we change the number of processes for the standard manager.
    
    update fnd_concurrent_requests
    set phase_code='C',
    status_code='D'
    where phase_code = 'P'
    and concurrent_program_id not in (
    select concurrent_program_id
    from fnd_concurrent_programs_tl
    where user_concurrent_program_name like '%Synchronize%tables%'
    or user_concurrent_program_name like '%Workflow%Back%'
    or user_concurrent_program_name like '%Sync%responsibility%role%'
    or user_concurrent_program_name like '%Workflow%Directory%')
    and (status_code = 'I' OR status_code = 'Q');
    
    update FND_CONCURRENT_QUEUE_SIZE
    set min_processes = 4
    where concurrent_queue_id = 0;
    
  
  
• Step 10: Perform any custom/environment specific steps. We have some custom modules which required some modifications as part of cloning.
  
• Step 11: Startup all of the application processes. ($S_TOP/adstrtal.sh)
  
  NOTE: If you have an application tier you may have to run autoconfig before starting up the services.

Hopefully this article was of some use even tho it was pretty vague at times. If you have any questions feel free to ask. Any corrections or better methods don't hesitate to leave a comment either.

Error cloning a clone

A few posts ago I hit an issue with executing the runInstaller. It still surprised me that after hundreds of installs you can still hit new errors. The other day I hit a new error while cloning an EBS environment. While preparing the environment for cloning (executing adpreclone.pl dbTier), the error below appeared:


RC-50409: Topology information required for cloning not found in the database. Make sure that
AutoConfig was run successfully prior to starting Rapid Clone, and try again
Raised by oracle.apps.ad.tools.AdCreateCtlFile
RC-50208: Exception in method gen_crctrfclone_sql
RC-50208: Exception in Constructor AdCreateCtlFile
Raised by oracle.apps.ad.tools.AdCreateCtlFile


This particular environment is a clone of our production environment. After it was built users had performed some work and wanted it cloned to another environment. There are two solutions for this particular error, which can be found in detail in Metalink Note: 376964.1.


The easiest solution is to run autoconfig at the dbTier level. The curious part is that since this environment is a clone of production, autoconfig would would have been executed as part of the post cloning steps (adcfgclone.pl dbTier). I checked my post cloning logs and indeed it completed successfully. I executed autoconfig ($ORACLE_HOME/appsutil/bin/adconfig.sh) on the database tier again and the preclone script finished without error.

Cloning and your SGA

Finally back from a little vacation... It started off rocky. During a normal disk resize operation (SAN attached) the storage team encountered a bug (or feature depending how you look at it) and it corrupted all of the databases on our dev server. Normally this isn't a big deal but we have some rather large projects ongoing at the moment and their environments are also on this server. So, the first weekend of my vacation was spent recovering.
----
Cloning is the act of creating an identical copy of an EBS environment. That means everything from the number of httpd servers to the size of your SGA will be the same. This can be a problem if your cloning from production, since its memory footprint is probably significantly larger than your dev/test environments can support. Whether thats because the server(s) aren't physically large enough or because multiple environments are eating into the available memory. So during the cloning process, you may want to resize the database's SGA.

Note: There are probably a few ways to change the SGA size during cloning. So if you know if another method please leave a comment because i'd be interested in knowing. For the sake of this article, lets assume the only parameter I want to change is the sga_target.

Before you clone an environment you have to prepare the source system by running adpreclone.sh on both the appsTier and the dbTier. This process gathers information about your environment and creates template files. In the case of the database tier, these templates are stored under $ORACLE_HOME/appsutil/template. Two files in particular are:

$ORACLE_HOME/appsutil/template/afinit_db920.ora -- 9i Database
$ORACLE_HOME/appsutil/template/afinit_db102.ora -- 10G Database

If you look at your log file ($ORACLE_HOME/appsutil/log/ApplyDBTier*.log) created during the database post cloning ($ORACLE_HOME/appsutil/clone/bin/adcfgclone.pl ) you will see the below:

instantiate file:
source : /envision1/app/oradb/1020_64bit/appsutil/template/afinit_db102.ora
dest : /envision1/app/oradb/1020_64bit/dbs/initORA.ora
backup : /envision1/app/oradb/1020_64bit/dbs/initORA.ora to /envision1/app/oradb/1020_64bit/appsutil/out/ORA_myserver/0
6220730/initORA.ora

In this case I have a 10g database, so the afinit_db102.ora file is being referenced to create the new parameter file. If you view this file it looks like a normal parameter file except for the fact not all the values are specified. For example:

sga_target = %s_db_sga_target% # Required 11i setting

So one way to modify SGA parameters would be to edit the template file for your database version and hard code the value you want, ie:

sga_target = 700M

Another option would be to modify the context file that is used, in conjunction with the template file, to build the parameter file. I'm not 100% sure that the file i'm about to reference is the correct one but from my limited testing it has worked for me. During the preclone, along with the template file creation I noticed some other files are created. One such being:

$ORACLE_HOME/appsutil/clone/context/db/CTXORIG.xml

Changing the following line in this file from:

<sga_target oa_var="s_db_sga_target">800M</sga_target>

to
<sga_target oa_var="s_db_sga_target">600M</sga_target>


also produces the same result. Once adcfgclone.pl completes the SGA_TARGET in the newly created parameter file will be set to 600M.

RC-10203 and directory permissions

This past week we hit a weird issue while trying to clone a test environment from production. It took a fair bit of effort to figure out the problem so I thought i'd mention it in case you have a similar setup but haven't added this item to your checklist.

Almost a year ago we moved to a shared application filesystem. So as part of the cloning process we have to run the script:

$ cd /bin
$ perl adclonectx.pl sharedappltop \
contextfile='applications context file for the existing node'

This configures the node to be able to access the shared setup. This script errored out:

Choose a value which will be set as APPLPTMP value on the target node [1]:
AC-10006: Exception - java.io.FileNotFoundException: /admin/template/adxmlctx.tmp thrown while creating OAVars object for file: ../admin/template/adxmlctx.tmp
RC-50004: Error occurred in CloneContext:
RC-10203: The template provided does not match with the context file.
Check logfile /apps/appsora/oraappl/admin/ORA/out/CloneContext_07270115.log for details.
ERROR: context creation not completed successfully.
Please check /tmp/adclonectx.err file for errors

Our environment hasn't changed much and cloning is a pretty regular exercise. So my first thought is what could be different now than previously? To me this type of problem screams setup issues.

On our side everything seemed fine. It wasn't until we noticed the following that any progress was made:

$ pwd
/apps/appsora/oraappl
$ df -k .
df: cannot canonicalize .: Permission denied

Since this is a shared application file system, the file system is mounted via NFS. Apparently the permissions on the mount point (/apps/appsora) were 700 so it wasn't writable by the group (in this case dba). I still wasn't sure why this was causing our problem... I could view, create, modify files since the account I was using was the owner. (UID's and GID's are global)

To fix the permissions problem the file system was unmounted, and the directory permissions changed to 770. Once it was remounted the adclonectx script worked properly. So you may want to add directory permissions to your checklist if your using NFS and EBS.

10.2.0.2 Rman duplicate issue

Each night we use rman's duplicate database feature to refresh a test environment to aid in troubleshooting production issues. This past weekend we upgraded our production database from 9i to 10gR2. We also applied alot of patches and the simplest way to update our test environment was to reclone it.

Last night we used our refresh scripts (rman duplicate) to rebuild the test database. Early this morning users reported the following error:




Since this problem wasn't reproducible in production we knew it had to be something with the refresh. The first thing we did was to look for differences between production and test databases. Some objects were invalid in test but recompiling them didn't resolve the issue. Checking through the log files for the refresh I noticed that autoconfig didn't complete successfully.


Uploading Context file /apps/appsora/oraappl/admin/ora_myserver.xml
Context File upload for /apps/appsora/oraappl/admin/ora_myserver.xml failed.
Check log file created by FNDCPUCF program.
. . .
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:


[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /apps/appsora/oracomn/admin/install/ora_myserver
afcpctx.sh INSTE8_PRF 1


AutoConfig is exiting with status 1


afcpctx.sh uploads you context file to the database by calling FNDCPUCF. I assuming FNDCPUCF stands for FND Concurrent Process Upload Context File but I can't seem to find much information on it. Since it fires off a concurrent request the next step was to check the log files generated for errors. The following error was in one of them:

Uploading context file...
Oracle error -1187: ORA-01187: cannot read from file 702 because it failed verification tests
ORA-01110: data file 702: '/oradata/dbf/oradata/temp02.dbf' has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.

I haven't encountered that error before so but a quick search on Metalink turned up the following note:


Subject: RMAN After 10.2.0.2 Duplicate Database Gets Ora-1187 And Ora-1110 on TempFiles
Doc ID: Note:374934.1

In 9i tempfile information is removed from the database during the duplication process and you need to re-add them manually. In 10g, tempfiles are now included. However, if the files already exist on the OS then the tempfiles are not re-created and you will see errors in the alert log. So, in order to resolve this issue we dropped the tempfiles and re-added them (as per the metalink note).

To drop tempfiles:

alter database tempfile '/u01/oracle/mydb/temp01.dbf' drop;

To add a new one (assuming your temporary tablespace is named temp):

alter tempspace temp add tempfile '/u01/oracle/mydb/temp01.dbf' size 500M;

Once this was done, autoconfig completed without error and we restarted the application.