Showing posts with label E-Business Suite. Show all posts
Showing posts with label E-Business Suite. Show all posts

Thursday

Web Cache 11g as a Reverse Proxy for E-Business Suite Virtual Tier


We are currently in the process of implementing a virtual tier setup for our E-Business Suite environment.    The reason is because we are planning on exposing some modules to the internet.   Since we are a small shop a separate physical tier isn't necessary.  

A complicating factor tho is that our DEV environment is in house while our TEST and PROD servers are located at a hosting provider.     TEST and PROD will be able to take advantage of an F5 Loadbalancer, while DEV doesn't have one available.

To setup the virtual tier i'm following the steps in note Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - R12 [ID 726953.1]

The note suggests using Web Cache 10g to simulate a reverse proxy:  Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12 [ID 380486.1]

We don't have any 10g webcache environments, but we use 11g for our Fusion Middleware environment.  I couldn't find any notes on Metalink which described using WC 11g.  Technically tho, there should be no reason why it wouldn't work with 11g, so I decided to give it a try.

NOTE: I am not sure if 11g Web Cache is certified with R12, I don't believe it is.   So you should confirm with Oracle Support before using this in a production environment.   Since this is just a POC and in TEST and PROD I will be using an F5 Load Balancer I decided to give 11g WC a try.

For each under Section 5: Configuration details, I will show the 11g setup.

5.1.1 - Login to the Web Cache Admin page

  • http://server:8091/webcacheadmin


5.1.2 - Listen Port

  • Add a Listen Port.
    • On the left hand side, under Ports click on Listen Ports.   Once the Listen Ports page has loaded click on the Create... button.
    • IP Address Field -> ANY
    • Port Number Field -> 7777 (or whichever port your EBS Virtual Tier is listening on.)
    • Since this is a DEV environment I am not using https so I left Protocol as default.
    • Click on Submit.

5.1.3 - Configure Web Cache Site definitions for E-Business Suite.
  • On the left side under  Origin Servers, Sites, and Load Balancing click on Site Definitions.
    • No changes from the 10g document.   Here is a screenshot of my setup:
    • myserver  is your WebCache server.
    • If your not using HTTPS, don't enter / for HTTPS Only Prefix, leave it blank or it will not work.
5.1.4 - Configure E-Business Suite as an Origin Server
  • On the left hand side click on Origin Servers and Add... on the right hand side once the page loads.  The setup here is almost identical to the 10g document.   The only thing missing is the option to select the protocol.

    Here is a screenshot of my setup.  Remember, the Origin Server is the server where your EBS virtual tier is running.  In my case its configured to listen on port 7777.

  • On the left hand side click on Site-to-Server Mapping 
    • Click on the Add...  button once the page loads.   
    • Below is a screenshot of my configuration.  Host Name (in this case server) is your Web Cache server.   Under Origin Servers select the one you created above for your EBS environment.  (Sorry for blanking out some of the server names, i'm paranoid. ;)   This is  a copy of the default settings for Web Cache 11g for our fusion middleware environment.


    • The above configuration seems to work fine but its quite different that what your instructed to do in 10g.   In 10g your told to create the following mappings: 
      • After seeing this I configured my Site-To-Server Mappings as following:
      • The file site-to-site mapping above was the default one.   The next 3 are the ones I added for this EBS configuration.  The second one is the same as the screen shot above.   The next two are the same as that one but hostname is set to * for both, and for the last the port number is a *.   ESI Content Policy is set to Exclude fragments for both.

As you can see, the 11g setup is pretty much identical.  You can follow the rest of Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12 [ID 380486.1] as is.   If your trying this configuration and running into issues with the configuration on the EBS side for the Virtual Tier let me know.  






Monday

Mac OSX: Disable Java 7


As most of you are probably aware if you are running Java 7 on your desktop there is a bug which will prevent forms from launching:

Steven Chan covers the certifications and the workarounds on his blog.

https://blogs.oracle.com/stevenChan/entry/java_jre_1_7_0


My Mac was updated to Java 7 a short period of time ago and of course I had issues opening forms.    I'm late to the game so i'm sure most people have already hit this issue.  I'm just documenting it here as a personal reference for when I need to back the fix out.

There are two solutions to this problem:

1.  Patch your EBS environment as per Steven's article above.
2.  Disable Java 7 on your Mac.


I choose to do number 2 as I needed to login ASAP and didn't have time to wait for patches to get through QA.

To disable Java 7 and re-enable Java 6 follow the steps on Apple's Support site:

http://support.apple.com/kb/HT5559?viewlocale=en_US


While I was trying* to track down when my system was updated to Java 7, I found the following site from Oracle which provides alot of information for Mac users of Java:

http://www.java.com/en/download/faq/java_mac.xml


* I'm not sure how my Mac was updated to 7.   I'm assuming it was when our tech support upgraded my Mac to Mountain Lion and updated it.

Tuesday

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. ;)

Wednesday

CPU Patches and Minimum Baselines


As you may or may not be aware, your environment has to meet a minimum baseline before you can apply CPU patches.  Typically Oracle supplies patches for both the latest patch set of a product and the previous patch set for a certain grace period. For example, below is clip taken from the EBS ECS Policy document  (Note: 1195034.1):
In addition, a given release update pack for the Applications Technology
product family will be treated as the minimum baseline 18 months after its
release. Specifically:

The Applications Technology RUP from 12.1.2 (R12.ATG_PF.B.Delta.2, Note
ID 845809.1) becomes the minimum prerequisite baseline on July 1, 2011.

The Applications Technology RUP from 12.1.3 (R12.ATG_PF.B.Delta.3, Note
ID 1066312.1) becomes the minimum prerequisite baseline on February 1,
2012
So the grace period for the ATG product family is 18 months.   ATG_PF.B.Delta.2 is the current baseline, however, after Feb. 1st the new baseline is ATG_PF.B.Delta.3.    That means if your not already on Delta.3, your going to upgrade before or as part of the July CPU release.

For other products the grace period may be different.  For example, for the database it is a minimum of 3 months, maximum of 1 year.  For more details see:  Database, FMW, EM Grid Control, and OCS Software Error Correction Support Policy [ID 209768.1]


The January 2012 CPU release information can be found here:

http://www.oracle.com/technetwork/topics/security/cpujan2012-366304.html

For Fusion Middleware and Database customers the best way to find this information is to keep an eye on the “Final Patch History” section of the patch availability document.  

For E-Business Suite customers make sure you read the yellow highlighted sections of the patch availability document.

We use E-Business Suite, Weblogic, Grid Control, Database and Fusion Middleware products.  So some key dates for us are:
  • January 2012:
    • Oracle Fusion Middleware 11.1.1.3
    • Oracle WebLogic Server 10.3.3.0
    • E-Business Suite:  ATG_PF.B.Delta.2
  • April 2012:
    • Oracle Fusion Middleware 11.1.1.4
    • Oracle WebLogic Server 10.3.4.0
  • July 2012:
    • Oracle Database 11.2.0.2
The reason I mention this is because it catches people off guard all the time.  Upgrading to new baselines can add a significant amount of time to applying CPU’s, and its nice to make sure management, business owners, etc have as much time as possible to prepare.

Upgrades Complete

Over the past two weekends we've upgraded our Fusion Middleware stack and our E-Buisness Suite.  To say I put in quite a few extra hours would be an understatement.    The Fusion Upgrade (11.1.1.1 -> 11.1.3) went pretty smooth, other than the fact I was up sick the previous night.   I didn't get off the couch until an hour before the upgrade was to start!    I have to say, working while you are sick sucks.    

Last weekend we upgraded our EBS environment.  12.0.6 to 12.1.2, 10g DB to 11gR2 and migrating to a 64bit server.    I started 2pm on Friday and finished 6am Monday morning.  During that time I slept for a grand total of  9 hours.     I only hit one major issue during the upgrade which required opening a P1 SR with Oracle.   It took about 3 hrs to resolve, would have been quicker but my analyst went to lunch!      

The other other issue was performance.     Our DEV server crash and burned a couple of months ago, so the networking guys gave us a loaner.   It didn't even cross my mind to check the specs and compare it to our production hardware before I did a dry run to work out the timings.     I managed to shave 18 hrs off the upgrade, which I assumed was because of a few changes such as applying the patches with nocompiledb,nocompilejsp,noautocfg.     I expected the 12.1.1 patch to finish around 2am on Saturday morning.  2am came and went with the patch still running....  3am, 5am, 7am, finally 8am it finished!   

I couldn't find any significant performance issues so I looked into the server itself.    It turns out, the loaner server is twice as powerful as our prod!   I should have realized it during my dry run, but I was so busy I didn't have time to investigate.   With 2 major  upgrades back to back the last few weeks have been crazy.

Well, all done now, environment has gone live and I haven't heard of any major issues.    I did have issues cloning and will put up a post on that shortly.

Tuesday

Kernel bug and Oracle Enterprise Linux Support

A few months ago I joined a new company embarking on a new R12 implementation project. From a DBA perspective I haven't noticed major day to day differences than 11i.. Patching, routine maintenance, etc are all pretty similar. One big difference that I have noticed is that it seems to be more stable. With 11i it seemed I was constantly applying one off patches but for this R12 implementation, I think I have applied 6 at most.

Last week we made the decision to go live... within 10 minutes production crashed! We could ping the server but could not login either remotely or at the console.. So a hard reset was in order. When the server came back up we found the following in /var/log/messages:

Jun 3 11:55:18 myserver kernel: kernel BUG at kernel/exit.c:904!
Jun 3 11:55:18 myserver kernel: invalid operand: 0000 [#1]
Jun 3 11:55:18 myserver kernel: SMP

A quick search on metalink turned up the following note:

Linux Crashes when Enterprise Manager Agent Starts on RHEL 4 Update 6 and 7
Doc ID: 729543.1

This note caught my eye immediately because we just started to setup grid control for this server in preparation for going live. We had also just purchased OEL support from Oracle, so we opened an SR with them to confirm, which they did.

Over the past few weeks we were debating which vendor to purchase linux support from. There were two camps, one which preferred Redhat and the other (dba's) Oracle. My argument was that Oracle would be more aware of linux based issues affecting their software. The other side argued that they weren't sure Oracle could deliver the same level of service as Redhat.

Fast forward to the kernel bug. It turns out that OEL customers are not affected because the OEL 4.7 kernel already contains the fix. I realize this is just one case but at least it adds some weight to my argument.

I won't be able to apply the kernel patch until the next maintenance window, so until then i'll have to monitor our production environment the old way, via scripts.

Thursday

Huge Events*.log files in $APPLCSF/$APPLLOG?

For the past 5 years i’ve supported the same Oracle Applications environment but recently, because of changes at my company, I have started to support new environments. Its interesting to see the subtle differences in configuration and to determine why it was setup that way.

The other day I received an automated email alert that a partition was running low on available space. Its the partition which contains $APPLCSF/$APPLLOG (ie. $COMMON_TOP/admin/log/<Context Name>). This directory stores concurrent manager logs, concurrent request logs, etc.

I noticed one file, Events01.log was 7GB in size. I should add that this environment is pretty static, so there aren’t log of changes and it doesn’t get restarted often.

The issue is described in Note:601375.1, which says the culprit is the Fulfillment Server having a high level of debugging enabled. The fix is to change the parameter s_jto_debug_string = OFF in your context file. (Don’t edit this manually, use OAM.) However, to enable this change you’ll need to execute autoconfig.

If your not able to run autoconfig at this time (I prefer to bundle these types of changes with patches so that users will do a quick sanity check of the environment), you can manually edit the file $COMMON_TOP/admin/scripts/<Context Name>/jtffmctl.sh and remove the references to:

-Dengine.LogLevel=9

-Ddebug=full

Once that change is made you need to stop apache (adapcctl.sh), the fulfillment server (jtffmctl.sh) and restart them. You can now remove that huge Events log file.

Note: If you remove an active file while a process is still pointing to it, the space will not be released. I’ve been asked by people many times why they removed a file but did not see the available space increase.

Tuesday

Walk through – Installing R12

The following article describes at a medium-high level how to install an R12 Vision Demo environment. Each OS has different pre and post requirements so be sure to read the metalink notes specific for your environment.

The article has two sections, the first lists relevant documentation and Metalink notes. The second section provides installation steps with screenshots of each screen that you will see during the installation process. However, I didn’t detail every pre or post requirement performed because there are differences for various OSes and it could get confusing. I’ll mention the following a few times throughout this article: Make sure you follow the Metalink note for your OS.

Documentation Notes and Links:

Rapid Install Guide


Metalink Notes:

Linux x86 402310.1

Linux x86-64 (64-bit) 416305.1

HP-UX Itanium 402307.1

HP-UX PA-RISC 402308.1

IBM AIX 402306.1

Microsoft Windows 402311.1

Sun Solaris SPARC 402312.1

405565.1 – R12 Installation Guidelines. Contains upto date information which may not be included in the Rapid Install Guide.


549389.1 - Oracle Applications Release Notes, Release 12.0.4 – Contains information on latest Rapid Install patch.

Installation Steps:

  1. Create a user account. I will be using the account oravis which will be a member of the dba group. Some installations use two accounts, applmgr or appl<sid> for the E-Business Suite files and ora<sid> for the database account.

  2. Review your operating system specific metalink note and verify all OS requirements are met. This document can seem overwhelming if you are new to Oracle/E-Business Suite so take your time and read through it. In most cases, you’ll just need to summarize this note for your system administrator. Some of the key areas are:
    1. Confirm your operation system is supported.
    2. Confirm required patches/packages are applied.
    3. Confirm kernel settings.
    4. Modifying various configuration files depending on platform. (/etc/hosts, limits.conf, etc..)

      Each type of OS has different requirements, so make sure you review the appropriate metalink note (above) If you don’t have a system administrator, feel free to ask me questions. I’ll gladly help as best I can.

  3. Download R12 from Oracles eDelivery site, http://edelivery.oracle.com. Unzip the files to create your stage directory. You will need about 75GB of space.
    Note: You don’t have to download every archive listed. You only need to download upto the documentation files, which for version 12.0.4 is around 26 archives (Solaris version).

  4. Download the latest Rapid Install patch as described in Note 549389.1. At the time this article was written the latest patch is 6919017. Uncompress this file within your stage directory. Ie.
    1. $ cd Stage
      $ unzip –o p6919017_R12_GENERIC.zip

  5. Welcome Screen

    cd <Stage DIR>/startCD/Disk1/rapidwiz and launch the program rapidwiz:

    clip_image002
    Click on Next.

    NOTE: The Rapid Install Wizard for the unix or linux platform needs access to an X server. If your not executing rapidwiz from a local xterm on the server then you must export your display to a server in which you have X access. I use Hummingbird Exceed so I can view the GUI on my laptop but there are many other options available.

    For Exceed there are two ways to do this. The easiest way would be to use Exceeds Client Wizard to create a connection. This will automatically set your DISPLAY properties.
    The other option would be to launch Exceed manually, login to your server and set the DISPLAY variable to that of your PC’s IP address.

  6. Wizard Operation Screen

    clip_image004

    Select “Use Express Install” and click on Next. When Express Install is selected you will not be prompted for additional information which will allow you to customize the installation. Since this will be used for self training, it’s a perfect start.

  7. Oracle Configuration Manager

    clip_image006

    Select Decline followed by the Next button.

  8. Express Configuration Information

    Some of the fields below will be pre-populated. For those that aren’t fill them in.

    image

    Once you click on Next , Rapid Install Wizard will perform a system check.

  9. System Check Status

    clip_image010

  10. Pre-Install Checks

    Once the system check is complete you will be presented with a summary window. If any of the checks failed you can click on the red X and it will show you why. If all the checks are successful you will see the following screen:

    clip_image012

    When you click on Next the application will begin to install:

    clip_image014

  11. Post-Install Checks

    Once the install is complete the following window will appear. If you see any red X’s, click on them to get more information on the error. If everything was successful you will see the following window:

    clip_image016

    If everything was successful click Next

  12. Finish

    clip_image018

    The install is complete. Click on the Finish button to exit the Rapid Install Wizard.

  13. Verify

    You can either click on “Connect to Oracle Applications Release 12” button in step 12 or launch a web browser manually. For the URL enter:
    http://<servername>.<domain_name>:<Port number>/OA_HTML/AppsLogin
    <Port Number> is derived from the Port Pool we choose in Step 8 prefixed by the value 80. Since we choose 50 for the port pool, the port number above would be 8050. The following screenshot is of the login page:

    clip_image020

  14. Post-Install Tasks

    Each OS may have different post-installation tasks. For instance, if you installed the Vision Demo environment such as we have above, then you need to submit a concurrent request to build DQM indexes. In the case of Linux (OEL5 REL5), you will have to upgrade the 10.1.3 application server to 10.1.3.3

If you have gotten this far, then you have successfully installed an R12 Vision demo environment. If you have any questions, feel free to ask.

Wednesday

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!

Tuesday

Everyone Has One

Everyone has an environment that they are tasked with supporting but dread the day a call comes in. For me is an old 10.7 environment which we have for historical purposes. I'm not exactly sure how often users need to run reports in there but i'd say a few times a month. I dread supporting this environment because I have never used 10.7... When I joined this group we had been using 11i for a few years. Since then, the only people who knew anything about 10.7 have left the company.

Recently we received a call that users could not connect. To make a long story short we had to restore the application server from a backup. I wish I could tell the story because its hilarious but suffice to say i've seen a few bloggers lose their jobs for posting too many environment specific details. Better safe than sorry.

Luckily I was going on vacation the day after we requested the restore and I was hoping it would all be resolved by the time I returned. No such luck. Once the server was ready for us I logged in to restart the services expecting no issues. No suck luck. Executing owsctl produced the following error:


> Error initializing CORE
> Please check if the ORACLE_HOME is set correctly.



Great. Luckily there is a 7 year old, archived document on Metalink which pointed me in the right location: Note:1015720.101 The note refers to changing
the location of your ORACLE_HOME but symbolic links under $ORACLE_HOME/ows/mesg were not updated correctly to the new location.

Well, in this case everything should be identical but with no other hits on Metalink its was my only option. Indeed the symbolic links were pointing to an invalid location. When the unix admin restored the files he had to put them in a temporary location first and copy them over. The restore process must have recreated the symbolic links because they were still pointing to that temp location.

Sweet, I found the solution so I fired up the startup scripts again.. No suck luck. Now I faced the following error:


OMN-2001, could not contact address server
Information: Application init function oracle_adp_init returned ADI_FATALINIT
Error: The server could not initialize
Information: The server is exiting
OWS-08811: Unable to startup Oracle Web Listener `xyz01'.



This time Note:1016600.102 came to my rescue. OMN-2001 means the admin process was started before the WRB (web request broker). Solution, start WRB before admin. Then I could process to starting the listener.

While the first problem was as a result of the restore, the second could have been avoided with good documentation. Now that I know the proper order to start the services (wrb, admin, listener) I found the correct startup scripts.

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.

ORA 24323 - Report Builder

Yesterday we encountered a weird issue with Report Builder. One of the developers, while working on a report, encountered the following error:

ORA-24323: value not allowed
==> select * from mytable

Report Builder: Release 6.0.8.27.0 - Production on Thu Jun 12 10:10:05 2008

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter Username:
Program exited with status 1
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 4792266.

Review your concurrent request log and/or report output file for more detailed information.



The developer proceeded to make several changes but received the same error. Then they pulled up other reports and the error occured there as well. They contacted us thinking that there may be something wrong with the environment.

I have never used Report Builder but a quick search turned up 2 possible solutions:

1. A mandatory value received incorrect or null data.
2. Your not connected to the database in report builder.

I asked the developer to verify that the SQL they were working on was valid by pasting it into sqlplus and executing it and to verify that they are connected to the database.

A short while later they let me know that once you receive the error above, it seems Report Builder loses connection to the database. At that point they have to reconnect and if the query is fine they no longer receive the error.

Thursday

How to Determine your Forms Patchset Level

There are supposedly two ways to determine your forms patchset level. The first method is to launch a form and click on Help -> About Oracle Applications. According to Note:74647.1 you should see it in the window that pops up. Maybe its just me, but I don't see it there.

Another method is to view the output by f60gen. Note:283985.1 provides a script which will determine the patchset level for you. You don't really need a script for it tho.

Execute f60gen and look at the first line:

oravisf@myserver=> f60gen
Forms 6.0 (Form Compiler) Version 6.0.8.27.0 (Production)



Subtract 9 from 27 and you'll have your patchset level. So I am on patchset level 18.

Jinitiator and the Google Toolbar


A little while ago I started getting the following error when trying to launch a responsibility.



There have been no changes to my laptop that i'm aware of. However, my company does use a tool to push patches out and if a reboot wasn't required I may not have noticed. Via Add and Remove programs I checked for updates and there has been no activity lately.

I followed Note:430359.1 which details how to troubleshoot IE crashes when launching forms. Following the steps I determined that the root cause of my problem is the Google Toolbar. I recently migrated to a new laptop and indeed I have the same problem there, which goes away after disabling it.

To disable the google toolbar and within Internet Explorer click on Tools -> Internet Options -> Programs (Tab) -> Manage Add-ons (Button). Click on Google and at the bottom of the window there will be an option to disable it. Perform the same action for google Toolbar Helper.

Below is an image of my add-on's showing Google as disabled.


I performed a google search and apparently IE with certain 3rd party add-ons has been a problem for awhile, with at least one site containing a list of which ones should be disabled. I reviewed the jinitiator install notes, etc but nothing specifically mentions 3rd party add-ons. In my case, I have had the google toolbar installed for ages without issue so I was surprised to find it as the culprit.

Wednesday

11i and Listener Passwords

A standard practice for any Oracle database should be to enable passwords on your listeners. EBS is no exception and recently Oracle published a metalink note: 386374.1 on how to do this for 11.5.x environments.

There are some pre-reqs, specifically:

3453499 (11i.ADX.F)
5225940 (Post ADX.F Fixes)
5107107 - AUTOCONFIG ENGINE & CONFIG TOOLS ROLLUP PATCH N or higher

The note has detailed steps on how to set the password at the application and database tiers.

In my opinion, just as important as setting a password, is to monitor for invalid attempts after. Otherwise whats the point? At best you will only slow down a determined intruder. Once you have set a password, any attempt to execute a command via lsnrctl (without setting your password first) will result in the following error showing up in your listener log:

TNS-01169: The listener has not recognized the password

Each DBA has his preferred language for scripts. Some prefer shell, others perl. Myself, I prefer perl. Below is a perl script I have used to monitor the listener log for the above error.


#!/usr/bin/perl -w

if ($#ARGV !=1) {
die "Usage: check_alert.pl ex. hostname /var/opt/oracle/listener.log.\n";
}
sleep 2;

$interval=60; # How many seconds before we check to see if data has been written to the logfile;
$email_threshold=5; # How many errors within the interval before an email gets sent;
$hostname=$ARGV[0];
$file=$ARGV[1];
open(filePtr, $file) or die "Can't find $file\n";


for (;;) {
@errors=("Subject: Listener Password Errors for $hostname\n");
$currTime = localtime(time);
push(@errors,"Here are some errors found at $currTime for $hostname.\n");

while () {
chop $_;
if (/TNS-01169/) {
push(@errors, "$_\n");
}
}

if ($#errors > $email_threshold) {
$rndExt = time;
$rndFile = "alert_errors_$rndExt";
open (TMPFILE, ">/tmp/$rndFile");

foreach $error (@errors) {
print TMPFILE $error;
}
close(TMPFILE);
system ("mail username\@mycompany.com < /tmp/$rndFile");
system ("rm /tmp/$rndFile");
}

sleep $interval;
seek filePtr, 0, 1;
}




The script takes two parameters: <hostname> <path_to_listener_log>

A couple of key things you may want to customize in the above script are the $interval and $email_threshold variables. As well, there is an email address towards the bottom where alerts will be sent.

Now, I am by no means a perl expert but the above works for me. I'm sure there are some more efficient ways to monitor the log file. Feel free to post a comment with your preferred method. If you cycle your logfiles keep in mind you'll need to restart this script. I've monitored resource consumption and its pretty minimal but I know some DBA's will prefer not having a process run constantly.

Note: I've only tested this script on a 9i listener log. Its on my todo list to test it on 8.0.6.

UPDATE: Thanks to Howard Rogers for informing me of the 10g changes. Security has been enhanced such that the TNS listener administrator has to be a member of the local dba group. Unless you plan on administering your listener remotely then you don't need a password. Please see the comments and Metalink note: 340009.1

Most of our databases are still 9i but i'll be sure to remove the listener passwords as part of the upgrades.

Thursday

Jinitiator Vulnerability

Great analysis over at Integrigy about on the recent Jinitiator vulnerabilities. If you are running Jinitiator versions 1.1.8.3 to 1.1.8.25 then you should definitely take a look.

Integrigy Jinitiator Vulnerability Analysis

Wednesday

Port Pools

Every now and then I get a request asking which port a specific service is using. The server team may ask for a list of ports so they can be reserved, etc. The Metalink document below lists all the ports required and which services run on them. It also has a nice utility which will calculate the ports required depending on the port pool you choose when installing or cloning.

Subject: FAQ: Cloning Oracle Applications Release 11i Doc ID: Note:216664.1

See question number 21: What is the port pool? What if I want to give a specific value to a Server Port?

Note: the port calculations given by the utility are defaults. They may have been changed in your environment for various reasons. For example, we may have to change the port listed below because it interfered with another application.

<jcache_port oa_var="s_java_object_cache_port" oa_type="PORT">12346 </jcache_port>

Friday

X Server and EBS

According to Note: 231137.1 Troubleshooting the Self Service Framework with Oracle Applications, you need access to an X server for dynamic image generation. These images are used for buttons and menu bars that you see on self service pages. Before I get to far, I should probably add that if your on windows you don't need an X server.

Metalink note: 181244.1 lists the different options you have available and describes how to configure each. In summary the 3 options you have are:

1. Use the X display of your server.
2. If your server doesn't have a display you can use 3rd party software Xvfb or VNC.
3. A Desktop X display server - ie, use 3rd party software such as Exceed on a PC

If your environment is autoconfig enabled you can look at you context file to see where you display is currently pointed.

ora@myserver=> grep display *.xml ORA_myserver.xml: myserver:0.0

You can also see it in your $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties file

wrapper.env=DISPLAY=myserver:0.0


To test whether or not the application is able to access the X Server you can use the AOL test UI:

http://hostname:port/OA_HTML/jsp/fnd/aoljtest.jsp

The first thing you will be prompted for is your connection details:



After you click on the TEST button scroll to the bottom of the screen and click on "Enter AOL/J Setup Test". On the left hand side of the following screen you see a summary of the environment you connected to and a list of tests. Scroll down on the left hand side until you see "X Server Accessibility". Once you click on that link the right hand frame will show you whether or not the X Server is accessible.




Note 231137.1 also describes some of the common errors you may see if the X server is not accessible. However, sometimes you will get rather cryptic errors if the application can't talk to an X server.

For the record, we use the X display of our server (option 1 above). In order to use this option the unix administrators have to type xhost + on the console to grant access to the X server. A caveat is that the unix administrators have to lock their terminal. If they logout, then access is revoked. This past weekend we had some maintenance in which the servers needed to be rebooted. After which the unix admins logged out of the terminal. Sunday morning users reported the following error when in iProcurement:



As you can see, not alot of information to go on. Since this is self-service web application the first thing to check is the apache and jserv log files. In the jserv.log file was the following error:

[24/06/2007 14:38:08:378 EDT] OAButtonBean, localName='button': oracle.cabo.style: Could not initialize the graphical environment
. Please make sure that the DISPLAY environment variable is set correctly. Proceeding with image generation disabled...


Using the X server test via AOL Test UI we could verify that the application couldn't talk to the X server. After a call to the unix team they granted us access, "locked" the screen and once apache was bounced the error was resolved. So in a nutshell, keep that link above handy. We have hit numerous weird errors in the past and sometimes it involves a lot of investigation to find out in the end it was just the X Server.

I believe in R12 the need for an X server is gone but we are a long ways from R12..... For now we may investigate using option 2 or 3 above so that we can manage this piece ourselves.








Thursday

Shared Application Filesystem

Recently we upgraded our production environment to a shared application file system. The intention of posts like these isn't to list out step by step which tasks we followed while performing the upgrade but to provide you with an overview of our approach and issues you may encounter.

To start, it would probably be best to provide you with an overview of our architecture. Our production environment is by use by almost everyone in the company. If you have an expense, we use EBS, if you have to submit a timesheet, chances are you use EBS. Of course, its mostly used as a financials environment but by the end of this year more and more modules will be brought online.




As you can see from the diagram, we have a load balanced application tier, a single database node with a cold failover and most of the environment resides on a SAN.

Details on how to implement a Shared Application filesystem can be found in Note: 233428.1










Here are the basic steps we followed:
  • Merge existing APPL_TOPS. The document above doesn't say which node should be the primary in a shared application filesystem. I did encounter one note: 372758.1 which mentions WEB node must be primary. In the end, we did choose the admin node as primary since it would cut down on the implementation time.

    When running perl adcfgclone.pl appsTier on the primary node after you copy the files from the secondary nodes, you have to say yes to all services.
  • Share the filesystem to the other Nodes. Make sure the filesystem is mounted in nolock mode if this is a unix based environment. Or you may encounter the problem described in Note: 390425.1 - Cannot Start Apache After Adding A Web Node On Shared APPL_TOP. For our environment, we shared a SAN mount point on the dbTier via NFS to the application tiers.
  • Add a node to the shared application filesystem. Note: you will need to create a location for each application tier to store configuration files. It needs to be created before you run txkSOHM.pl. In our environment, we used local disk to avoid the issue above.


Issues Encountered
Even tho we ran the adadmin option to check for missing files there were still some that were missing after the merge. Files that were customized on the secondary nodes but not the primary node (pre-merge) did not get updated. One example is the appslogo.gif file. I'm not sure what logic Oracle follows when it determines which files need to be brought over from the secondary servers, so i'm assuming that since appslogo.gif already existed on the primary server, Oracle didn't replace it.

We also noticed that there were quite a few files missing under the $OA_HTML subdirectories.

We have SSL setup in our environment, so we had to re-enable it via the txkrun.pl script. I believe the reason we did this was so we didn't have to manually edit all the files it touches. You will need to copy your SSL certificates to the location you created on each server for configuration files.

Since we have a loadbalanced environment we had to change the related context file values to the appropriate settings. It appears the clone conveniently resets those values. (ie, Web enty%, Active Web Port, Login page... etc)

If you receive a the infamous yellow security bar within forms after you regenerate the jar files and you didn't choose the web tier as your primary server, then you may need to copy your adcert.txt, adsign.txt and adappltop.cer to the primary node.

Maintenance mode via adadmin only works on the primary server. I hit this issue once and haven't double checked it to confirm. Since all patching is performed on the primary server this really isn't an issue.

Each time we run autoconfig a number of files have to be modified, such as adovars.env, some context variables.

As I have mentioned above, on our application tier we have forms/web and on the database tier concurrent/admin. The problem with our approach above is that all the services are enabled on the dbTier. So, when you execute adstrtal.sh all of the services, even apache start up. Obviously, this is not something you want. You can maintain your own start/stop scripts, remove the scripts for the services you don't need (and see some errors when running adstrtal.sh) or you can rerun adcfgclone.pl on the primary tier and disable the services you don't want.

When you execute adpatch it checks $APPL_TOP/admin/adconfig.txt file for which services are enabled on that tier. This file will still show that all services are available, so patching is not an issue. We tested this configuration by applying some patches in our sandbox and confirming that forms and web files have been updated properly.

Monday

Recreating your Global inventory

As every Apps DBA knows, we apply alot of patches. For some reason, every now and then, the global inventory gets corrupted (for lack of a better term) in our cloned environments. For example:

oraora@myserver=> opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.55
Copyright (c) 2006 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com

Oracle Home = /oradata/app/oradb/920_64bit
Location of Oracle Universal Installer components = /oradata/app/oradb/920_64bit/oui
Location of OraInstaller.jar = "/oradata/app/oradb/920_64bit/oui/jlib"
Oracle Universal Installer shared library = /oradata/app/oradb/920_64bit/oui/lib/solaris/liboraInstaller.so
Location of Oracle Inventory Pointer = /var/opt/oracle/oraInst.loc
Location of Oracle Inventory = /oradata/app/oradb/920_64bit/inventory
Path to Java = /oradata/app/oradb/920_64bit/jre/1.4.2/bin/java
Log file = /oradata/app/oradb/920_64bit/.patch_storage//*.log

Creating log file "/oradata/app/oradb/920_64bit/.patch_storage/LsInventory__03-05-2007_16-43-00.log"

Result:

LsInventory: OPatch Exception while accessing O2O


OPATCH_JAVA_ERROR : An exception of type "OPatchException" has occurred:
OPatch Exception:
OUI found no such ORACLE_HOME set in the environment
Can not get details for given Oracle Home
An exception occurs
null


There is no Interim Patch



OPATCH_JAVA_ERROR : An exception of type "OPatchException" has occurred:
Can not get a list of inventory on this home.

ERROR: OPatch failed because of Inventory problem.



If you do a search on metalink you'll see a few potential resolutions which range from verifying your oraInst.loc file to manually editing the inventory.xml file within your global inventory.

For 11i environemnts, Oracle provides us with a method to recreate the global inventory. For complete details you can view Note:295185.1 but the key steps are:

1. Verify ora inventory_loc within oraInst.loc
2. Take a backup of your inventory location.
3. Make sure you are on OUI 2.2 or later.

If your iAS ORACLE_HOME is missing from the inventory.xml file:

4. Sync your apps environment file.
5. Change to $IAS_ORACLE_HOME/appsoui/setup and execute perl OUIsetup.pl

If your RDBMS ORACLE_HOME is missing from the inventory.xml file:

4. Sync your database environment file.
5. Change to $ORACLE_HOME/appsoui/setup and execute perl OUIsetup.pl

ex. Excuting on the database tier:


oraora@myserver=> . ./DBORA.env
oraora@myserver=> cd $ORACLE_HOME/appsoui/setup
oraora@myserver=> perl OUIsetup.pl
ORACLE_HOME : /oradata/app/oradb/920_64bit (APPSDB_ORA)
OUI Location : /oradata/app/oradb/920_64bit/appsoui/oui
CLASSPATH : /oradata/app/oradb/920_64bit/appsoui/setup/OUISetup.jar:/oradata/app/oradb/920_64bit/appsoui/oui/lib/OraInstaller.jar:/oradata/app/oradb/920_64bit/appsoui/oui/lib/xmlparserv2.jar
JRE_INST : /oradata/app/oradb/920_64bit/appsoui/jre/1.1.8

Executing OUI setup now:
/oradata/app/oradb/920_64bit/appsoui/jre/1.3.1/bin/java -classpath /oradata/app/oradb/920_64bit/appsoui/setup/OUISetup.jar:/oradata/app/oradb/920_64bit/appsoui/oui/lib/OraInstaller.jar:/oradata/app/oradb/920_64bit/appsoui/oui/lib/xmlparserv2.jar -DDEBUG_MODE=false oracle.apps.ad.util.OUISetup /oradata/app/oradb/920_64bit /oradata/app/oradb/920_64bit/appsoui/oui /oradata/app/oradb/920_64bit/appsoui/jre/1.1.8 APPSDB_ORA

Log file : /oradata/app/oradb/920_64bit/appsoui/setup/OUIsetup.log
Checking inventory ...
Updating registration of ORACLE_HOME in global inventory...
Oracle home registration completed.
Removing OUI entries from the global inventory: /oradata/home/oraagf/oraInventory/ContentsXML/comps.xml
Removing OUI entries from the global inventory completed successfully
Registering the OUI component with local inventory...
OUI 2.2 registration completed.


OPatch lsinventory should now work fine.