Grid Control–Application Management Pack–Getting Started

A little while ago we purchased the Oracle Application Management Pack.   As you may or may not know, you cannot use Grid Control to manage and monitor individual components of an E-Business Suite environment, such as the concurrent manager, forms, etc.

The following document on Metalink describes the packs features and how to install it:

Getting Started with Oracle E-Business Suite Plug-in, Release 4.0 [ID 1224313.1]


I figured I would document my progress with setting up the AMP pack here.  The first step is to make sure my EBS environment is at a certified level.   Unfortunately  this isn’t the case for us.   We are on ATG_PF.B.Delta.2 and to use AMP 4.0 the requirement is ATG_PF.B.Delta.3.   Also depending on the features of AMP you would like to use there are additional patches.   NOTE:  If you have applied the April 2012 CPU release tho, it had the same ATG_PF requirement.

This link is to a Google Docs spreadsheet which details the patches and steps I had to follow in order to bring our EBS environment up to the proper level.  I also used the opportunity to review our environment and apply any recommended techstack patches, such as database patches from note: 761570.1.   These metalink notes change overtime and its always a good idea to keep an eye on them.

For Reference they are:

• Database Preparation Guidelines for an E-Business Suite Release 12.1.1 Upgrade [ID 761570.1] 
• Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12 [ID 437878.1] 
• Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12 [ID 454811.1]

Note:  Each EBS environment is different and you may not need all the patches or you may need more.   So I wouldn’t recommend following this step by step.   Its provided as a reference to give you an idea of the tasks involved.   As well, if you feel I missed anything please let me know.  Part of the advantages of sharing information is that hopefully its reciprocated. ;)

How to deregister the appsTier or dbTier.

 
During my upgrade from 32bit to 64bit, as described in my last post,  I hit an unexpected issue.  After migrating, the environment wouldn’t start up successfully.  By successfully, I mean all services being available.
  
As part of my upgrade to 32bit I migrated the application to new servers, thus new server names, etc.  I quickly noticed in FND_NODES, FND_DATABASES, tnsnames files, etc that there were still references to the old servers as well as the new.  Since I already migrated the database I couldn’t use the standard way to deregister a tier.

Example, to deregister an application tier it is:

perl $AD_TOP/bin/adgentns.pl appspass=<APPSpwd> \
contextfile=<CONTEXT> –removeserver


So the question is, how do I deregister a tier if I’ve already moved it?   Manually using the FND_NET_SERVICES package.

First execute the following query:

select NAME, SERVER_TYPE from FND_APP_SERVERS, FND_NODES 
where FND_APP_SERVERS.NODE_ID = FND_NODES.NODE_ID and 
SERVER_TYPE in ('DB',’APPS"’)
 and FND_NODES.NODE_NAME=upper('hostname');

Substitute hostname with the name of your old servers. Sample output:

NAME                   SERVER_TYPE
---------------------- ------------------------------
myserver_VIS_APPS      APPS
myserver_VIS_DB        DB


The next step is to call  FND_NET_SERVICES.remove_server(SYSTEM_NAME, SERVER_NAME); to remove them.  Example:

begin
  FND_NET_SERVICES.remove_server( 'VIS', 'myserver_VIS_DB');
end;

begin
FND_NET_SERVICES.remove_server('VIS', ‘myserver_VIS_APPS');
end;

The last step is to run autoconfig on both the dbTier and appsTier.

Once I hit that issue, in my first development pass, I could have modified my steps to deregister the tiers.    However, I didn’t want to modify the original environment in any way.    Worst case scenario, if some issues were encountered during the migration or testing phase,  I could rollback to the original environment.

R12 Migration: 32bit–> 64bit Overview

I’ve had a few enquiries as to the steps I used to migrate our R12 environment from 32bit to 64bit.   Fortunately we were moving to new hardware at the same time, which made the process easier.  Instead of having to backup the server, wipe it and install 64bit Linux, I could just migrate the data.

1. We use Red Hat Linux, so the first step was to install the 64bit version on the new server and make sure all the setup steps were performed.   
   
   Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 [ID 761566.1]
   
   The above note details all of the operating system requirements and is very easy to follow.
   
2. Shutdown the application tier.
3. Export OLAP Analytic Workspaces.
   
   OLAP, as far as I know, is only used for the EPB (Enterprise, Planning & Budgeting) and ODP (Oracle Demand Planning) modules.  I asked Oracle Support if I needed to migrate OLAP if we aren’t or didn’t plan on using these modules.    They stated no, so I didn’t export them.   If you are using OLAP be aware of this note:
   
   EPB AW's Cannot Be Attached Or Do Not Exist [ID 795247.1]
   
4. Execute preclone on the appsTier.        ($ADMIN_SCRIPTS_HOME/adpreclone.pl appsTier)
5. Backup the controlfile to trace.
   alter database backup control file to trace;
6. Move necessary files to the new servers:
   * Application software.
   * Database datafiles
   * Files under $ORACLE_HOME/dbs such as the password file, parameter file, etc.
   * Control File trace.
7. Install the 11.2.0 64bit software on the new server, including the examples CD.
8. Create the 9inls directory:
              perl $ORACLE_HOME/nls/data/old/cr9idata.pl
9. Apply the necessary database patches:
   
   8328200,8993052,9081430,9218789,9318214,9500046,9535951,
   9644960,9657344,9719541,9756939
   
   NOTE: For Patch 9218789:  opatch napply -skip_subset -skip_duplicate
   
   You may have to set the following environment variable:
   export OPATCH_PLATFORM_ID=226
   
   Failed To Apply Patch Opatch Detects Platform 46 While Patch Is For 226 [ID 337812.1]
10. On the new server create the control files using the backup you created in step 5. 
11. Start the database in UPGRADE mode:
    STARTUP UPGRADE;
12. Invalidate and recompile database objects:
    ?/rdbms/admin/utlirp
    ?/rdbms/admin/utlrp
    
    Note: If you didn’t export and drop the OLAP schema then you may hit error:
    
    ORA-03113 And ORA-03114 While Running Utlrp.sql [ID 578940.1]
    
    The solution is to run the following and re-run utlrp.sql:
    @?/olap/admin/olap.sql SYSAUX TEMP;
    
    I didn’t drop OLAP as per the instructions since I was migrating the database to a new server and the rollback plan in the event of a problem was simply to use the old servers.
13. Import OLAP analytical workspaces
    
    If you need to migrate OLAP then this is where you would want to import them.
14. On the appsTier set the following environment variable:
    LDEMULATION to elf_i386
15. Update CTXORIG.xml
    
    This file can be found under <COMMON_TOP>/clone/context/apps
    Modify the following line:
    
    <platform oa_var="s_platform" osd="LINUX_X86-64">LINUX_X86-64</platform>
    
16. Use Netca to create a new Listener.  Otherwise you’ll hit issues while trying to implement autoconfig.  Create it with the same name and port as the source environment.  Add an entry for the SID to the listener.ora.  Restart the listener.
17. Implement Autconfig on DB Tier.
    
    This step struck me as weird because we haven’t built the appsTier yet.  So I basically just copied the old appsutil.zip from $INST_TOP on the source server.
    Unzip -o appsutil.zip in the DB Oracle Home
    
    Set TNS_ADMIN=$ORACLE_HOME/network/admin
    
    Create Context File
          perl adbldxml.pl
    
    
    Run autoconfig
       adconfig.sh contextfile=/u01/VIS/db/tech_st/11.2.0/appsutil/VIS_myserver.xml
18. Execute Post Clone on the Apps Tier
    $COMMON_TOP/clone/bin
    perl adcfgclone.pl appsTier
19. Deregister the old Application and Database servers.
20. Modify $AD_TOP/bin/adrelinknew.sh
    
    CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh -Wl,--noinhibit-exec'
21. Relink AD executables (Use AD Relink for this step)
    
    Relink Applications programs
    
    Generate message files
    
    Generate form files
    
    Generate report files
    
    Generate product JAR files
    

If I haven’t missed anything while modifying my document for this post, then that should be it.  Feel free to send me any questions.
As for step 19, it’s a bit detailed, so I will post details in a follow up shortly.

R12.1.2 Relinking issues after 64bit Migration

We have a large R12 upgrade coming up on the horizon.  Upgrading to R12.1.2, 11gR2 and migrating to 64bit.   As part of the post 64bit migration you have to relink the application programs.  A number of modules wouldn’t relink tho:

Example:

g++: /u01/TEST/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libschedule.a: No such file or directory
g++: /u01/TEST/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libsolveriim.a: No such file or directory
g++: /u01/TEST/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libconcertext.a: No such file or directory
g++: /u01/TEST/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libsolver.a: No such file or directory
g++: /u01/TEST/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libconcert.a: No such file or directory
make: *** [/u01/TEST/apps/apps_st/appl/eng/12.0.0/bin/ENCACN] Error 1
Done with link of eng executable 'ENCACN' on Tue Oct 19 15:32:10 EDT 2010
Relink of module "ENCACN" failed.


I opened up an SR with oracle but continued to research the problem.   I didn't see anything on Metalink for R12, Google same result.   I searched the entire filesystem to see if the libraries existed elsewhere but no luck
.
I noticed a zip file /u01/TEST/apps/apps_st/appl/sht/12.0.0/lib/ilog62lib.zip and listed the contents to see what was in it.  Here is a snip:

[oravis@myserver lib]$ unzip -l ilog62lib.zip
Archive:  ilog62lib.zip
$$Header: ilog62lib.zip 120.5 2006/10/02 17:00  juliang ship                       $
Length     Date   Time    Name
--------    ----   ----    ----
0  05-02-06 02:12   ilog/
0  09-27-06 18:09   ilog/6.2/LINUX/
2  09-27-06 18:08   ilog/6.2/LINUX/libconcert.a
2  09-27-06 18:08   ilog/6.2/LINUX/libconcertext.a
8  09-27-06 18:08   ilog/6.2/LINUX/libcplex.a
2  09-27-06 18:08   ilog/6.2/LINUX/libilocplex.a
8  09-27-06 18:08   ilog/6.2/LINUX/libschedule.a
0  09-27-06 18:08   ilog/6.2/LINUX/libsolver.a
0  09-27-06 18:08   ilog/6.2/LINUX/libsolverfloat.a
0  09-27-06 18:08   ilog/6.2/LINUX/libsolveriim.a
.
.
.
--------                   -------
665793708                   72 files

The libraries adrelink was looking for.  I extracted the files, changed to the LINUX directory, moved the files up to the parent directory,  relinked (via adadmin) and it completed successfully.

----

Looking back on the problem, its strange I didn't encounter it before.   This environment is a copy of an existing R12.1.2 32bit environment.   The only thing i'm changing is migrating it to 64bit.   So I should have hit these relinking issues before.   I checked the source environment and the libraries are indeed there!  

The zip file contains the libraries for a number of platforms, AIX, HPUX, etc.   So the only thing I can think of is that since i'm doing a platform migration, these libraries get removed to make sure the proper ones are used.  However, it doesn't continue and extract the correct libaries from the zip file.

EBS: DB Upgrade to 11gR2 – Autoconfig Fails

Today I hit an issue upgrading our database from 10.2.0.4 to 11.2.0.1.  

The main metalink note which details the steps needed is: Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]


Step 22 involves implementing autoconfig in the new database home.  However, when I ran $ORACLE_HOME/appsutil/bin/adconfig.sh it would fail


Checking the logfile I found that afdbprf.sh fails with ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Metalink has an article which discusses the issue:


ORA-12504 When Using (HOSTNAME) Method For 11G Client/Database [ID 556996.1]


The note goes into a fair bit of detail about why this error is happening and how to resolve it.  In a nutshell, 11g expects the service name to be specified in the connect string.  If one isn’t specified then it uses the default service name specified at the listener level.   If the listener is not configured with a default then an error is thrown: 


ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Previous to 11g, if you dídn’t specify the service name, then the connect string alias was used instead.  In the case of the following connection string, VIS is the alias:


sqlplus apps/pass@VIS


The solution is to configure listener with a default service name using the DEFAULT_SERVICE_listener_name parameter.   I added the following to my listener.ora ifile, reloaded the listener and re-ran adconfig.sh successfully. Note: if you add it directly to the listener.ora file and not the ifile, then the change will be lost when your run adconfig.sh.


DEFAULT_SERVICE_VIS=(VIS)


Since this is an EBS environment I always search to make sure there are no issues but I was surprised to find nothing.   I’m wondering if I missed something in the upgrade steps.  Have you upgraded to 11g?  Did you hit this issue?

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.   

11.5.10.2 Upgrade

The past 6 months or so we have been working on a 11.5.10.2 upgrade. Last weekend we finally upgraded production over a period of 48 hrs. (That includes rebuilding test environments for testing.) The upgrade went pretty smooth and there haven't been any major issues on the techstack side. We have had the usual jinitiator issues and I believe there have been some minor application issues, but otherwise i'd say a very successful upgrade.

Going into the upgrade we expected it to about the same level of effort as our 11.5.8 to 11.5.9. However, that wasn't the case to say the least.

So hopefully i'll have more time to post in the new year...!

10g Upgrade issues

This weekend we are upgrading our E-Business Suite database from 9i to 10g (10.2.0.2 to be specific). Today while performing some final checks I noticed that an initialization parameter was not set properly in our test environment.

I had used the script bde_chk_cbo.sql from Metalink note: 174605.1 which builds a report consisting of the current, required and default values of your initialization parameters. The parameter in question was optimizer_secure_view_merging and according to the report it was undefined. Which means its using the default value of TRUE. According to Note 216205.1, Database Initialization Parameters for Oracle Applications 11i this parameter should be set to FALSE.

I checked my logs of the test upgrade and I did indeed set this parameter to FALSE. So I performed a search on metalink and found Note 404646.1, How To Set Parameter optimizer_secure_view_merging = FALSE Using Autoconfig. In summary, autoconfig isn't updating your pararameter file properly unless your on version 115.7 of the template file $ORACLE_HOME/appsutil/template/afinit_db102.ora. We have version 115.3 installed. This parameter is needed to avoid some performance issues with dictionary queries. If this applies to your environment, you should review the metalink note for the workaround.

Over the past month or so we have upgraded many environments to 10g, reverted some back to 9i, etc. It started to get confusing as to which environment was running 10g and which were still on 9i. (We have about 14 dev/test/project environments right now, with more planned.) One of my coworkers started up a 9i database with the 10g Oracle Home and we noticed something interesting:

ALERT: Compatibility of the database is changed from pre-10i to 10.2.0.0.0.
Increased the record size of controlfile section 2 to 256 bytes
The number of logical blocks in section 2 remains the same
Increased the record size of controlfile section 4 to 428 bytes
Control file expanded from 1614 blocks to 1644 blocks
Increased the record size of controlfile section 9 to 56 bytes
Control file expanded from 1644 blocks to 1672 blocks
Increased the record size of controlfile section 10 to 200 bytes
Control file expanded from 1672 blocks to 1694 blocks
kcv_lh_or_upgrade: 10.2 upgrading 1 incarnations
Control file expanded from 1694 to 1990 blocks for upgrade.
Setting recovery target incarnation to 1
Wed Jun 20 00:11:34 2007
Successful mount of redo thread 1, with mount id 764203184
Wed Jun 20 00:11:34 2007
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Jun 20 00:11:34 2007
ALTER DATABASE OPEN


As you can probably guess by the messages above, the control file was modified. When the database was shutdown and started with the 9i software the following happened:


ORA-00201: controlfile version 9.2.0.0.0 incompatible with ORACLE version
9.2.0.0.0


Yikes! On Monday I'm going to see if I can recreate the control files and startup the database but right now i'm off to upgrade our production EBS database to 10g.

EBS 10g upgrade problem

Recently we tried to upgrade our database from 9.2.0.6 to 10.2.0.2 following Metalink Note: 362203.1. [FYI: 10.2.0.3 is now certified with EBS and the metalink note has been updated to reflect that.]

The upgrade was applied to a dev environment with no issues and passed the developers sanity checks. Next we upgraded our stage environment but an issue was flagged during user acceptance testing. The issue was isolated to one custom form.

What made this issue confusing was that the problem only existed in stage, not our dev environment. To cut a long story short the problem was related to a known 10g upgrade issue.

Note: 345048.1
Subject: 'Group By' does not sort if you don't use order by in 10g.

The gist behind this note is that in 10g the group by clause uses a hash algorithm to group data while in 9i a sort algorithm was used. As all DBA's know, there is no guarantee that rows will be returned in the same order after each execution unless you use an order by clause. I guess at the time the form was created, the results were returned in an order favorable for the developer.

This doesn't answer why this problem wasn't consistent between the two upgraded environments. When comparing init parameters between both databases, it was noticed that the optimizer_features_enable parameter was not the same. In the environment with the form issue the value was 10.2.0 and the other environment was set to 9.2.0. This is actually one of the workarounds stated in the note above.

As part of the 10g upgrade for EBS you have to modify the init parameters detailed in Note 216205.1, Database Initialization Parameters (init.ora settings) in Oracle Applications Release 11i. While upgrading the problem environment, the DBA had issues reseting a few parameters, optmimizer_features_enable was one of them, so it remained at its 9.2.0 value.

The command used to reset an init parameter if your using an spfile is:

SQL> alter system reset optimizer_features_enable scope=spfile sid='*';

In one environment it worked, but the problem environment returned an error:

ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

In the end the problem was solved, but if both upgrades had been identical some of the confusion and alot of work would have been avoided. The process to find this solution was pretty lengthy.

As a result, we have asked our developers to go through custom code and verify order by clauses are being used when the query also has a group by. It also hightlights the importance of making sure identical steps are followed when promoting changes and to investigate anything different.