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.

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?

Security Patch Woes

A few weeks ago we applied a number of security patches. Due to various reasons we were a bit behind schedule and had to push a couple of releases out to production. Since then we have encountered 3 bugs, one of which crashed production just before month end. 2 of the bugs were the result of upgrading to 10.2.0.3 (a requirement for the security patches). The other was a bug on top of ATG_PF.H.5

Problem 1:

After cloning, Concurrent Managers fail to start. As per Note:434613.1, this problem exists on top of ATG_PF.H delta 5 and delta 6, as well as R12. During cloning neither the service manager or the internal monitor are created thus the concurrent managers will not start. A patch has been published to resolve this issue and there is a very easy workaround. We have added the patch to the next release cycle and modified our cloning scripts to incorporate the workaround.

The interesting part about this problem is that we have been on ATG_PF.5 since Dec when we upgraded to 11.5.10.2. Since then we have cloned a test environment over 200 times (its rebuilt nightly) and did not encounter this bug. We did hit it once back in March for a one off clone, but since then we have recloned that environment plus many others multiple times and it didn't resurface. I find it interesting that now after upgraded to 10.2.0.3 (from 10.2.0.2) and applying security patches that we can reproduce the problem consistently.

In retrospect we should have added this patch to the release cycle but we tend not to recommend that unless a problem can be consistently reproduced.


Problem 2:

Users stopped receiving email notifications from workflow. The following error could been seen in the logs:

ORA-06502:

PL/SQL: numeric or value error: associative array shape is not consistent with session parameters

A quick search turned up bug 5890966 which mentions this problem could occur during periods of high activity. Once we encountered this bug emails ceased to be sent. Oracle confirmed that this is a mandatory patch for 10.2.0.3 but has not been published as such yet.

Thanks to the next problem tho, we had to restart our environment and the problem hasn't reoccurred yet. We have added the patch to the next release cycle and hopefully it won't reoccur before then.

Problem 3:

On 10.2.0.3 bug 5907779 can cause sessions to self hang if dbms_stats is executing... I recall reading a few blog posts about this particular error but since it wasn't recorded as a mandatory patch we didn't apply it. At least the blog posts helped me identify the problem quickly.

Our statistics gathering jobs are scheduled on weekends and in this particular case only 1 type of session was hanging as a result of this bug. This session was spawned by an integration which is scheduled to execute once every few minutes. Unfortunately it wasn't smart enough to detect previous instances were still running and of course isn't monitored on weekends.

So as the weekend wore on, more and more sessions consuming more and more resources accumulated in the database. We didn't realize there was a problem until Sunday night when APO users came online. Unfortunately by then it had progressed to the point where the system ran out of resources, sessions couldn't be killed and we had to reboot the server. Luckily I was able to capture enough information (hang analyze and system state dumps) to confirm that bug 5907779 was the culprit. Everything came back up properly and as a bonus temporarily fixed our workflow email issues.


Unfortunately these types of problems (at least number 2 and 3) are not likely to surface in a test/dev environment. We have some patch review meetings coming up over the next few days to re-examine our processes but i'm not sure how we can prevent these types of problems in the future. Note:401435.1 lists a number of issues specific to 10.2.0.3. I guess I could have analyzed each of those patches to determine if they were applicable to our environment but whats to say they wouldn't have introduced additional bugs? Even then, I would have only prevented one issue since the workflow patch isn't listed in that note. Normally I just review Note:285267.1 which is the EBS 11i and Database FAQ to make sure there are no known issues.

Feel free to leave a comment describing how you analyze patchsets and full releases...

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.