Using Hints in SQLs versus Using Outlines

Many times as DBAs or sometimes developer we need to fix a performance issue and mostly the fix is adding hint/s to the sql.

There are two options to put a hint:
1. Put the hint in the sql itself and redeploy the code (works well if it is custom code).
2. Create an outline with hint for the sql (works well for standard code and most of the time when we cannot change the code).

Attached is the document I prepared, that compares both the techniques. It helps in taking a decision as to what approach to use when.
hints-in-sqls-vs-outlines.doc

I will discuss more on creating outlines (esp. in Oracle Apps they are very helpful) in my later posts. Till then keep checking my blog…………….

Cloning Basics

If you are trying to learn oracle apps dba skills, cloning would be first practical thing would try to learn and try.

So what exactly is cloning ?? Cloning is a process by which you can literally copy all of your Oracle Apps instance to another location and bring it up. The “copied” instance will inherit virtually everything (patches, profiles, data, code etc…) from source instance, hence it is called “cloned” instance. The reason why you would clone an instance are varied. As per official Oracle Metalink note on cloning (Note:230672.1), you would clone:

1. Creating a copy of the production system for testing updates.
2. Migrating an existing system to new hardware.
3. Creating a stage area to reduce patching downtime.

Let us briefly discuss how cloning works.

For example you have a production instance called PROD and want to create a testing instance for developers and others to play around, this instance is called TEST. In this case source instance is PROD and target instance is TEST. As you know oracle apps has three distinct tiers (see my post Apps Architecture), the desktop tier, apps tier, db tier. You would have to first clone your db tier (As Oracle DBAs you must be familier with cloning db). Once target database and listeners are up and running, copy your application tier files and change configuration files to reflect target instance (e.g. hostname, ports etc). This step of configuring your apps tier is the most difficult step until release 11i when adclone,autoconfig and rapidclone came into the picture.

I would suggest you to go through following metalink notes and get your self familiar with cloning. I will discuss in detail about cloning steps in my future posts on cloning.

References:
Note: 216664.1 – FAQ : Cloning Oracle Applications Release 11i
Note 282930 – Cloning Oracle Applications Release 11i
Note ID 230672.1 – Cloning Oracle Applications Release 11i with Rapid Clone

Configuring Load Balancer and Shared Application Tier for multiple Forms/Web Tier nodes

In this post I will describe the procedure to implement Http Layer load balancer for forms/web tier nodes. This also covers the method to setup your second forms/web tier node from the already running one forms/web node. The following picture describes what we will achieve:

Assumptions:

1. There is already a Forms/web node running. This node will be called primary node.
2. The second node has already been setup and it is exactly similar in configuration to primary node i.e. OS version e.g. Red hat 4.0, OS kernel Parameters, OS patches, OS packages. This node will be called secondary node.
3. The secondary node is in the same network and has all the network related settings already done i.e. Assigned name, had an entry in DNS servers already. There should be ability to NFS mount application tier filesystems from primary node to secondary node.
4. You have setup your hardware level HTTP load balancer (e.g. Cisco, Big IP etc) and it is setup in your network (DNS entries, Ip adress setting have been done already etc..)

Steps:

1. Sharing your existing application file system with secondary node.

a. Verify Software versions: Before doing anything make sure you have the following softwares/patches
i. AD minipack H or higher (patch 2673262)
ii. ADX should be 11i.ADX.E.1 Feb 2005 Consolidated Update. (patch 4175764)
iii. iAS should be Oracle HTTP Server Rollup 4 (patch 3072811)
iv. Developer 6i patchset 16 or higher
v. Oracle Universal Installer should be 2.2.0.19: Apply patch 4017155 to each iAS ORACLE_HOME to have OUI version 2.2
vi. Apply patch 4104924 to each APPL_TOP in the application tier to have TXK AutoConfig Templates to RUP K.
vii. Apply patch 3219567 to each APPL_TOP in the application tier. This patch is included in 11.5.10.
viii. Zip version 2.3

b. Implement AutoConfig if you have not done yet : See metalink note:165195.1

c. Rename the Applications context file: If the Applications system was created with Rapid Install version 11.5.8 or earlier, you must regenerate the Applications context file:
i. cd /bin
ii. perl adclonectx.pl sharedappltop contextfile= Context file name
iii. Running adclonectx.pl creates a new Applications context file named SID_Hostname.xml

d. Run AutoConfig to enable shared file system
i. Run these commands on the primary node where you are enabling shared file system support. After performing this step, re-load your environment settings.
ii. cd /admin/scripts/CONTEXT_NAME
iii. adstpall.sh apps/apps pass
iv. cd FND_TOP/patch/115/bin
v. perl -I AU_TOP/perl txkSOHM.pl

The script prompts for the following information:

Important thing to note here is Configuration_top. There is an application environment variable called IAS_CONFIG_HOME which will be set to value of Configuration_top that you will provide. For non shared nodes this variable is set to IAS_ORACLE_HOME value. The perl script txkSOHM.pl that you ran above will create the following directories in IAS_CONFIG_HOME:
a. network (all tnsnames.ora etc)
b. Apache (apache config files)
c. oem_webstage (for oem conf files)
d. soap (Jserv soap conf files)
Please note that now your Apache configuration and log directories are under IAS_CONFIG_HOME and not IAS_ORACLE_HOME. This way oracle has placed IAS related configurations to separate directories for the nodes that will be sharing one IAS_ORACLE_HOME, this avoiding any conflict that may arise out of running same executable from two separate machines.

Adding the secondary node to a shared file system:

a. Execute Rapid clone on existing node.
i. cd COMMON_TOP/admin/scripts/CONTEXT_NAME
ii. perl adpreclone.pl appsTier

b. NFS mount the application tier files from primary node to secondary node.
Please note there is a specific way to NFS mount. Please see my blog post in wierdos sections:
NFS mounts with Shared Application Tier
c. Configure the node you want to add: On the secondary node login as applmgr and do the following.
i. cd AD_TOP/bin
ii. perl adclonectx.pl sharedappltop contextfile= Application Context file for existing node
iv. cd FND_TOP/patch/115/bin
v. perl -I AU_TOP/perl txkSOHM.pl

The script will prompt you same question as mentioned above. The only difference is that this time you have to mention “Type of Instance” as “secondary”

At this point your second node is added and ready to run. You can test it by bringing up services on it. Please note that since load balancer is not yet configured, you can only use one node at a time.

Setup HTTP layer Load Balancer the for two nodes

1. Create a web entry point : When you setup your hardware level load balancer there is an option to specify for which URL this load balancer is configured. For example: http://navdeep.practicalappsdba.com. This URL is your “web entry point”. Also make sure that suitable option should be chosen to ensure that the load-balancer sends all the requests from one client session to the same Web Server Node, this is also called as session persistency or “stickyness”. At present oracle supports “Cookie based” and “IP address based” stickyness. Please consult your sysadmins/harware fols for Loadbalancer to know more about which stickyness is available for you.
2. On each Web server node, run the AutoConfig Context Editor : In the Context Detail screen, set the following configuration values:
a. “Web entry point Host” to the HTTP load-balancer machine name.
b. “Web entry point Domain” to the HTTP load-balancer domain name
c. “Web entry protocol” to the HTTP load-balancer protocol e.g. “http” or “https”
d. “Active Web Port” to the value of the HTTP load-balancer’s external port
e. “Login Page” to include “Web entry protocol”://“Web Host entry point”.“Web domain entry point”:“Active Web Port”

a. Here is an example values you will want to give:

i. Web entry point Host = navdeep
ii. Web entry point domain = practicalappsdba.com
iii. Web entry protocol = http
iv. Active Web Port = 8001
v. Login Page = http://navdeep.practicalappsdba.com:8001
3. Run Autoconfig on both the nodes.
4. Bring up the services on both the nodes and change your login URL to the one mentioned above e.g. http://navdeep.practicalappsdba.com:8001, try to login and see if everything is working.

At this point you have implemented shared application tier between your two nodes and placed Http based Hardware load balancer in front of them. If your implementation includes configurator then you will have to enable load balancing at the Apache JServ layer even if your hardware load balancer is configured to maintain session persistency. I will talk about Apache Jserv load balancer in my future post, till then keep reading my posts and good luck ……….

Migrating Oracle Apps Middle Tiers to Linux

Currently Oracle supports following platforms for its middle tier:

1. HP Tru64
2. HP-UX PA-RISC
3. IBM AIX Based Systems
4. Linux x86
5. Solaris Operating System SPARC
6. Windows 2000/Windows Server 2003 (x86)

For more information go to : https://www.metalink.oracle.com

As an experianced dba you would know that bulk of load will be on database tier where you would prefer bigger proprietry OS/hardware machines. For middle tier i.e. forms, web and conc. manager you would prefer cheaper machines with optional load balancing which also can give you higher availability and scalability (option to add more machines as load increases), than spending millions on one or two large proprietry hardware.

Many organizations nowadays are demanding that they move thier middle tier to cheaper Linux hardware at the same time keep administrative costs low by implemeting multiple middle tier nodes (for HA) and shared application tier (for lower maintenance costs). Oracle provides the ability nowadays to move your middle tier from any one of above mentioned platform to Linux Platform. The migration utility retains your exact Applications patch level, so that no APPL_TOP/Database synchronization is necessary. It also allows you to retain many customizations, thereby lowering your overall cost/time of migrating your application tiers to linux.

Although oracle supports various flavors of Linux (SLES-9,SLES-8, RHEL 3, 4,2.1, Enterprise Linux,AsiaLinux 1.0) , for sake of simplicity I would talk about Red Hat version 4.0 only, in this blog post. Also to have multiple nodes for conc. manager you would have to implement Parallel Concurrent Processing (PCP) which would make things more complex for this article, hence I would talk about only moving your forms/web nodes to Linux platform. The steps for conc. manager will be same if you want to keep one node, however for multi node, after migrating to Linux you would have to implement PCP about which I talk in my later articles.
In short I am going to do the following:

This article talks about middle tier migration only. If you want to migrate your database from one platform to Linux you would have to export/import your apps database. Please follow instructions in the following metalink note:
For 10gR2 : Note:362205.1
For 10gR1 : Note:331221.1
For 9iR2 : Note:230627.1
For 8i : Note:204015.1

1. Prerequisites
a. Database Version should be 8.1.7.4 or higher (Beware that if you are planning to move your database also from windows to linux using export/import, there is a bug in 8.1.7.4. You would have to migrate to 9i first to export/import from windows to linux.)
b. AD mini-pack G or later (minimum recommended by me will be H)
c. Implement Autoconfig if you havent yet, refer to Migrating to AutoConfig on the Application Tier
d. Maintain Snapshot Information: This will sync up your application tier with database.
e. Perl version 5.005
f. JDK version 1.3.1
g. Install Red Hat linux : Will talk about it more in a separate post.

How it works ?
1. Platform Migration Utility Patch: Oracle gives you the ability to change your middle tiers by using its Platform Migration Utility, which comes as a patch (patch#3453499). As of today patch#3453499 is included in TXK (FND & ADX) Autoconfig Rollup Patch N (August 2006) patch#5107107.

2. Create your application tier: You would have to copy your APPL_TOP,OA_HTML,OA_JAVA, COMMON_TOP/util,COMMON_TOP/_pages from your old middle tier node to linux node. Now obviously these files wont work as is on Linux, because all the exeutables will be of old middle tier e.g. if you were on HP-UX and copy files to Linux, all the executable files will be of HP-UX and wont work on linux. This gap is bridged by Platform Migration Utility. After copying the COMMON_TOP/util make sure you remove jre directory in it. This directory will be created when you install tech-stack as mentioned below.

3. Generate customer manifest: After applying platform migration utility patch mentioned above, you will have to run a script in AD_TOP. perl $AD_TOP/bin/adgenpsf.pl
This script will take about minute an generate a text file ($APPL_TOP/admin/$TWO_TASK/out/adgenpsf.txt ), which is called manifest of customer-specific files. You will have to upload this file to: http://updates.oracle.com/PlatformMigration . Oracle will send a e-mail with the link to download a patch and also username and password for this patch. This patch is specific to you only and gives all the files necessary to make your “copied” application files to work on linux (Dont apply this patch yet)

4. Clone your autoconfig xml file: As you have copied your APPL_TOP from old node to new Linux node, you would have autoconfig xml (to learn more about autoconfig please see: Migrating to AutoConfig on the Application Tier ) which has all the entries related to old node. You would have to “Clone” your xml file to new node. To do this:
run the following perl script “perl $AD_TOP/bin/adclonectx.pl migrate java= contextfile=”. This script will ask you about new mount points, ports etc… on new Linux node and will create new xml file in /admin/SID_Hostname.xml.

5. Create technology stack: If you have noticed till now, all this while we didnt have 8.0.6 and iAS homes on new Linux node. We didnt copy them ??? so where will they come from ? This is acheived by using 11.5.10 rapid-install new feature which allows you install just tech-stack from 11.5.10 CDs (DVDs in this case). Refer to chapter 5 of apps installation guide : Oracle Apps Installation Guide
Make sure you have ordered 11.5.10.2 (latest as of now) media from Oracle.

6. Apply Red hat 4.0 Oracle Interoperability patches: You would have to apply the following patches as your target linux node is Red Hat 4.0

3830807 Oracle 8.0.6.3 Interop patch
3170128 Oracle 8.0.6 Interop patch for Discoverer 4i
3846086 8.1.7.4 Interop patch for iAS ORACLE_HOME

7. Generate environment files: At this point you have all the necessary files for apps middle tier, together with autoconfig xml file. Next step will be to apply customer specific patch that you got above. But before that you would need to create environment files that has to be sourced. Run the following shell script to generate env files. /bin/adconfig.sh run=INSTE8_SETUP contextfile=target system ctxfile

8. Apply customer specific patch : Download the customer specific patch that you got above and unzip it. Read the readme carefully and apply the patch, make sure you have sourced the environment files before running adpatch. Important thing to notice here is that adpatch is to be run from “/bin” directory where you have unzipped your patch. The executable in AD_TOP/bin is not yet ready to be executable on linux. This patch will replace all your old executables with Linux and relink them. While relinking if there are any errors related executables to Ilog, Roguewave, Quantum, In that case, answer “yes” when adpatch asks whether to “Continue as if it were successful”.

9. Upgrade to latest developer 6i patchset : This is a good oppurtunity to upgrade your developer patchset level to latest level. As of today patchset 18 is available. For more information refer to : note:125767.1

10. Apply 11.5.10 tech-stack inter-op patch : Since you will be using 11.5.10 tech stack, you will have to apply interop patch so as to make your applications work with this new tech-stack. Read the readme and Apply patch:4139957

11. Regenerate the executables: Run adadmin to generate messages, forms, reports, graphics and jar files.

12. Run AutoConfig to complete the target system configuration : At this point your application tier is migrated to linux, you have to run autoconfig sync-up your new application tier with the database. Run autoconfig as:
$AD_TOP/bin/adconfig.sh contextfile=Target system context file

Finishing Tasks : Depending upon your implementation you may have to look at the following:
1. Custom forms may need recompile.
2. Custom settings in environment files.
3. If using discoverer, SSO, portal etc. need to check them separately if they are working. refer to thier documentation.
4. Update printer settings if you have migrated your conc. node.
5. Update workflow settings for new host.

After making sure everything is alright, try to bring up your services and login into the application. HURRAAHHH.. now you are on Linux. Do a thorough testing on Linux and in the end you can de-comission your old, costly middle tier hardware.

At this point you can bring-in another linux server and implement shared application tier and load balancing. For more details on this, wait for my next post .. till then good luck with Linux and keep reading my blog…

Apps Architecture

As I mentioned in my other post (How to Become Oracle Apps DBA),learning Oracle Apps Architecture is the first step towards learning anything in oracle apps. This blog entry tries to address this topic.

When Oracle first came out with ERP package it was all character based and basically was a centralized computing architecture. In apps release 10.7, the character release of Applications primarily had the database and it’s end-user forms defined in character based forms. The database was Oracle 7.3.2, and forms version was “SQL forms 3″ (character forms). The users had to telnet to the server to be able to access the applications. After connecting to the server they had to issue the command ‘found’ to be able to invoke the form that used to connect them to the server. The client machines used to connect to the server were primarily dumb terminals. e.g. VT100 etc.

In Release 10.7 there were three options:

1. The original char based
2. Smart Client (which was basically client-server architecture)
3. NCA (Network Computing Architecture)

In a Smart Client Install of 10.7, the database was Oracle 7.3.2 and the front end constituted of Developer 1.3.1 or 1.3.2. The clients used to connect to the server with the help of the named service or TCP/IP link made in the file tnsnames.ora. The SQL*Net protocol or the Net80 protocol was used for connectivity.

In NCA oracle apps was finally available in “The Three Tier Architecture”. There were three distinct “Tiers” now and client side could be much lighter (generally a java enabled browser). This architecture has been taken into release 11 and 11i, and from what I hear and read about release 12, there will not be any major change in this architecture.

The three disctinct “Tiers” in Oracle apps 11i (11.5.10.2 is the latest as of now) are:

1. Desktop Tier
2. Application Tier
3. Database Tier

The following image taken from Oracle Applications Concepts illustrates the three tiers:

Lets talk breifly about these tiers individaully:

1. Deskptop Tier: Its is nothing but a simple web browser (Internet explorer or Netscape etc) from which end-user logs into the application. Important thing to remember is that browser has to be java enabled. The componants required on the desktop tier are Forms Client Applet and Oracle Jinitiator.

a. Forms Client Applet:

Most of the Oracle Applications is based on Oracle Forms and Reports (Oracle Developer) and to display oracle forms on a client’s browser an applet is used. This forms client applet displays Oracle Applications screens and support field level validation, multiple windows and list of values (LOVs). The forms client applet is paclaged as Java Archive (JAR) files that contain all java classes for forms applet. These jar files are downoaded from Web server at the beginning of the client’s session and remains in the cache known as Jcache.

b. Oracle Jinitiator:

The Forms Client Applet must run within a Java Virtual Machine (JVM) on the client machine. For Oracle Apps the JVM is provided by Oracle Jinitiator which acts as a plug-in (Active X componant in IE) in the web browser. Please note that for Oracle apps we do not use other JVMs e.g. Microsoft JVM, Sun JVM etc..
When end-user enters the desired Oracle Applications signon URL withing the web browser, the browser will execute the Oracle Jinitiator. If Jinitiator has not been previously installed, end user will be prompted to download the necessary installation executable to the desktop. Once Jinitiator is installed, the forms client applet is started and Oracle Forms sessions is opened.

2. Application Tier : The application Tier, as the name suggest is nothing, but location of application servers. This Tier is sometimes referred to as middle tier and provided business logic and code processing. There are five servers in Applications tier:

a. HTTP server (powered by Oracle Apache)
b. Forms server (This host all you Oracle forms)
c. Concurrent Processing Server
d. Reports Server (host all the Oracle reports)
e. Admin Server

3. Database Tier : The database tier contains the Data Server and holds all the data stored and maintained by Oracle Applications system. There are basically two types of database objests: Data Objects like Oracle apps Tables, Indexes, sequences etc.. and Code Objects like Stored Procedures, Packages, functions,triggers etc..
Important thing to remember is that database tier does not directly communicates with desktop tier, but rather with servers on the middle tier which in turn provides the end-users with the information as required in interface which is more user friendly. Hence almost all the processing is handled at Applications and Database Tier (which are much bigger machines) leaving client machines free of any processing.

At this point you must be having a basic understanding of Oracle Applications Architecture. I suggest that for deeper understaning of the various componants of Oracle Applications, one should carefully read chapter 1 (Applications Architecture) of Oracle Applications Concept Manual (Oracle Applications Concept)

NFS mounts with Shared Application Tier

Here is an interesting thing that I noticed. Oracle does not say it anywhere in the metalink notes, but if you plan to use NFS mounts to share application tier between different nodes, you should mount NFS with NOLOCK option. E.g. in Red Hat ES Linux 4.0
mount -t nfs :/mnt/inst /mnt/dir -o nolock
or update fstab such as:
:/mnt/inst /mnt/dir nfs auto,nolock 0 0

Read below for complete story ……….

We were in the process of upgrading our 11.0.3 application to 11.5.10.2. All the tiers were running in Hp-UX. We had successfully completed 4 iteration of the upgrade and also moved our application tier-forms/web server from Hp-UX to Linux (Red hat 4.0) (using note Note:238276.1 , as post upgrade step)

Forms/web server on Linux (RED HAT 4.0) had been successfully running for past 2 months now. At this point we wanted to have Cisco load balancer in front of two Linux forms/web server and use shared application tier for these two Linux servers.

As part of this step, I was trying to enable “shared application tier” on first Linux node-1, NFS mount application tier from node1 to node2 and add node2 as secondary node to shared application tier (Note:233428.1). After successful completion of adding node to shared application, enable http layer hardware load balancing (Note:217368.1).

However I was having problem bringing up apache on node2 after enabling “shared application tier” and adding the secondary node. The error_log of apache shows the following:

[Tue Sep 12 23:46:39 2006] [debug] opm_tbl.c(230): OPM:Into opm_tbl_create_tbl
[Tue Sep 12 23:46:39 2006] [debug] opm_sys.c(128): OPM:Into opm_sys_init
[Tue Sep 12 23:46:39 2006] [debug] opm_sys.c(267): OPM:Into opm_sys_init_mtx
[Tue Sep 12 23:46:39 2006] [error] OPM:Fail to allocate memory for mutex in shmpool
[Tue Sep 12 23:46:39 2006] [error] OPM:Fail to call opm_sys_init
[Tue Sep 12 23:46:39 2006] [error] OPM: process manager could not create internal tables
[Tue Sep 12 23:46:39 2006] [crit] (37)No locks available: Apache JServ
encountered a fatal error; check your ApJServLogFile for details if none are
present in this file. Exiting.

Please note that I specified config_top as local mount point on node2 when adding secondary node to “shared application tier”. However I saw that apache was still trying to write opm_sys_shm and similar lock files on $IAS_ORACLE_HOME/Apache/Apache/logs which is a NFS mount instead of $IAS_CONFIG_TOP/Apache/Apache/logs, which was a local mount. The strace of the adapcctl.sh shows the:
fcntl64(10, F_SETLKW, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}) = -1 ENOLCK (No locks available)

Of course you will have issue writing lock file on NFS mount. The workaround I found was that you have to mount NFS with NOLOCK option.

How to Debug Apache Log Files

I got this file sometime back from oracle support. It is really helpful and provides all the information for turning “on” apache log/debug in different configuration files.

How to Debug Apache Log Files

How to Become Oracle Apps DBA

“How do I become an Oracle apps DBA” I have been asked this question lots of time. Well my answer is always, “To start with, you should be an Oracle DBA”. Oracle DBA skills are must to learn anything about apps dba. You can also be a good apps developer and still become an Oracle Apps DBA (There are developer there who asked me this question !!), but route is same, learn Oracle DBA.
There are many ways to learn Oracle DBA, however for newbie’s I would suggest to start with OCP DBA(Oracle Certified Professional) learning. To learn more about OCP see:

OCP Link (Under Job Role look for Database Administrator certifications.)

Once you are good at Oracle DBA you can start learning apps stuff. Good place to start is to read Oracle Applications Concepts manual. You can find Oracle apps documentation at:

11i (11.5.10.2+ virtual) Documentation Library

You should devote time to understand oracle applications architecture. Understanding oracle apps architecture is as important as learning to walk before you run. You can read more about Oracle Applications Architecture in my Blog entry under Categories > Beginner’s Guide > 4 Apps Architecture, or simply click on:

Apps Architecture

Rest of the stuff in concepts manual (apart from chapter 1, apps archi) will be OHT (Over-head Transmission) for the beginners . Dont worry and just keep on reading (If you find it interesting). Believe me, I could comprehend just 20% of the manual when I first read it.
As you move on everything will start falling into its place and you will understand the concepts.

Next thing will be to learn installation. Its pretty simple and most of it is java GUI based. Installation manual is the first one in the Apps Document Doc link that I mentioned above. I will also discuss installation in my blog entry here :

Apps Installation

At this point you would have learned two important topics in apps dba. Other things that you will have to learn as apps DBA e.g. AD Utilities like Patching and Cloning etc, Upgrade and more. (For complete list of topics please see my blog post:Main Topics)
These are covered under different Blog categories that I have created. I will keep on posting in them in future… so keep on checking my blogs……….and happy learning.

RAC with 11i

Place Holder

Troubleshooting

Place Holder