Why only apps ?

All the pl/sql packages will be created in APPS Schema
All the views will be created in APPS Schema
For each table in individual schema, there will exist one synonym in APPS schema
Tables are not created in APPS schema.
Every implementation has at least 1 custom schema, where custom tables are created.
For each custom table created by you, you will need to create a Synonym in APPS schema
As a developer, you will either connect to APPS Schema or to the custom schema where you will create new tables.

OPATCH UTILITY (ORACLE RDBMS PATCHING)

Introduction:

This post has been written on request of one of regular visitor of my blog (Altaaf). This post is all about Oracle Patching Utility (OPATCH) and various options that can be used along with Opatch.

We will begin the discussion by knowing what exactly is Opatch?

- OPatch is a java based utility that allow the application and rolling back of interim patches to an Oracle product. The program has sub-commands that may take arguments. The program requires Java(JVM) to be present on the current system.OPatch expects users to have commands such as fuser, jar, ar and make on Unix platforms and jar on Windows platforms to be available in their PATH.

Opatch Details

How to check the Opatch version?

-bash-3.00$ ./opatch version
Invoking OPatch 10.2.0.3.0

OPatch Version: 10.2.0.3.0

OPatch succeeded.

How to know which version is the correct version of Opatch for your RDBMS home?

You can verify whether the Opatch version for your RDBMS release is correct or not using metalink note ID 357221.1.

This note ID gives the copatibilities between OPatch version and RDBMS version.

How to get the latest version of OPatch?

You can download the latest version of OPatch from metalink using following URL.

http://updates.oracle.com/download/6880880.html

In the release dropdown you can select the OPatch release that you want to download based on your RDBMS version. Please read the README.txt before applying the patch.

What is Oracle Database Inventory and where it is located?

Oracle Inventory is the location or the place where all the information about an Oracle Home is stored and maintained. When ever we install an RDBMS Oracle Home, a new inventory gets created. Applying any new patch to Oracle Home will update the invnrtory for that Oracle Home and record the details for the patch applied. This inventory is in the form of XML files.

The location of inventory is defined in a file called oraInst.loc. The path for this file is provided while installing Oracle Home. If we dont supply any path, the is file will be present at central location /etc/oraInst.loc. Also we can have a central inventory if its not used by any other oracle installation. If the central inventory is used by previous Oracle installation we can create local inventory. The content of oraInst.loc file is as shown below.

-bash-3.00$ cd $ORACLE_HOME
-bash-3.00$ cat oraInst.loc
inventory_loc=/slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInventory
inst_group=ems2029
-bash-3.00$

Here “inventory_loc” gives the location of Inventory directory where as inst_group gives the name of the group which is the owner of this inventory.

How to create Local Inventory?

You can create local inventory at any location while installing Oracle Home. You need to use -invPtrLoc variable along with runInstaller command and give the location of oraInst.loc. Otherwise the default path assumed for oraInst.loc is /etc/oraInst.loc.

./runInstaller -invPtrLoc /slot/ems2029/appmgr/apps/tech_st/10.1.2/oraInst.loc

After the installation starts it will ask for location of oraInventory directory and group which should own it. It will make entry of these into oraInst.loc file.

Having know the above information about opatch, now we will move to details about using opatch and various options available.

Opatch help

You can get all the options using opatch -help

-bash-3.00$ ./opatch -help
Invoking OPatch 11.1.0.6.0

Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Usage: opatch [ -help ] [ -r[eport] ] [ command ]

command := apply
lsinventory
prereq
query
rollback
util
version

<global_arguments> := -help       Displays the help message for the command.
-report     Print the actions without executing.

example:
‘opatch -help’
‘opatch apply -help’
‘opatch lsinventory -help’
‘opatch prereq -help’
‘opatch rollback -help’
‘opatch util -help’

OPatch succeeded.

You can get specific help for any command of opatch using opatch <command> -help. Example opatch apply -help.

Applying single patch using opatch

1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory

tar cvf – $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

6. Unzip the patch in $ORACLE_HOME/patches

If you are applying a single intrim patch to oracle home, then change the location to the directory of that patch and then invoke following command.

Example if I need to apply patch 6972343 to RDBMS Oracle Home

-bash-3.00$ cd 6972343
-bash-3.00$ pwd
/slot/ems2029/oracle/db/tech_st/11.1.0/patches/6972343
-bash-3.00$ ls
etc  files  README.txt
$ORACLE_HOME/OPatch/opatch apply

If you have created local inventory then you need to give the correct path for local inventory stored in oraInst.loc. If you have updated /etc/oraInst.loc with correct location of your inventory, then above command will work, If not you can give the correct location by giving local oraInst.loc file.

$ORACLE_HOME/OPatch/opatch apply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

Remember that when we are applying patch, patch number is NOT provided as an input to opatch command. So we have to to be in the directory of patch since opatch will pick the patch files from current directory.

Rolling back a patch

In case if a patch did not fix the issue or if you has applied wrong patch to oracle home, you can always rollback the patch using following comamnd.

opatch rollback -id <Patch Number>

Applying bundle patches

Some times if you have to apply bundle patch having many patches, example lets say you want to a bundle patch 6778860 (    11.1.0.6 BUNDLED PATCH FOR EBS 11I)) containing many other patches to RDBMS home, OPatch provides a facility to apply many patches to RDBMS home using a single comamnd. Following command should be used for applying bundle patches.

Download the patch and extract the same. You will see many directories (one for each patch) inside the main patch directory. Execute following command to apply all patches.

$ORACLE_HOME/OPatch/opatch util NApply -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

NApply -> Will apply all patches to RDBMS home.

You can also use -phBaseDir to point to the directory where bundle patch is installed. So you can run opatch command from any location

$ORACLE_HOME/OPatch/opatch util NApply -phBaseDir /slot/ems2029/oracle/db/tech_st/11.1.0/patches/6778860 -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

You can also apply specific patches using NApply

opatch util napply <patch_location> -id 1,2,3 -skip_subset -skip_duplicate

This will apply patches 1, 2, and 3 which are under < the patch_location> directory. OPatch will skip duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the ORACLE_HOME)

You can see all the options for NApply using following help command.

$ORACLE_HOME/OPatch/opatch util NApply -help
Query the inventory for patches applied

We can query the inventory to check various components installed for database as well as to check various patches applied to database. Use following command to get a minimum information from inventory about patches applied and components installed.

$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

Patch  5763576      : applied on Wed May 28 03:20:53 PDT 2008
Created on 6 Feb 2008, 02:26:04 hrs PST8PDT
Bugs fixed:
5763576

If you are using central inventory then -invPtrLoc variable is not required.

You can also get the detailed view of inventory using following command. In case of detail information it gives which are the files that this patch have touched.

$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

Patch  6318357      : applied on Wed May 28 03:33:27 PDT 2008
Created on 4 Dec 2007, 22:02:16 hrs PST8PDT
Bugs fixed:
6318357
Files Touched:
udjvmrm.sql –> ORACLE_HOME/javavm/install/udjvmrm.sql
Patch Location in Inventory:
/slot/ems2029/oracle/db/tech_st/11.1.0/inventory/oneoffs/6318357
Patch Location in Storage area:
/slot/ems2029/oracle/db/tech_st/11.1.0/.patch_storage/6318357_Dec_4_2007_22_02_16

Which options are installed in Oracle Home?

You can check the options installed in RDBMS home using above command. The options installed will be listed at the start of output.
Example: $ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc

The complete output of this command can be seen at this location.

Opatch Log files

Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch

Advanced Option for OPatch

Here are some of the advanced options of OPatch utility.

UpdateRemoteNodes

It is used to propagate/remove files/directories to/from remote nodes using files under ORACLE_HOME/.patch_storage/<ID>/rac/*.

The directories listed in copy_dirs.txt will be copied to remote nodes.
The files listed in copy_files.txt wil be copied to remote nodes.
The directories listed in remove_dirs.txt will be deleted from remote nodes.
The files listed in remove_files.txt will be deleted from remote nodes.
This is used where RAC setup is present.

Cleanup

It is used to clean up ‘restore.sh, make.txt’ files and ‘rac, scratch, backup’ directories in the ORACLE_HOME/.patch_storage directory. If -ps option is used, then it cleans the above specified areas only for that patch, else for all patches under ORACLE_HOME/.patch_storage. You will be still able to rollback patches after this cleanup.
Example: opatch util cleanup -ps 6121183_Ju _21_2007_04_19_42

CopyListedFiles

It is used to copy all files listed in ORACLE_HOME/.patch_storage/<ID>/rac/copy_files.txt to remote nodes. If -fp option is used, then one can specify the path of the file containing the list of files to be copied. The files mentioned in this file will be copied to the remote nodes.
Example: opatch util copylistedfiles -fp a -remote_nodes ceintcb-a5
This is used where RAC setup is present.

CopyListedFilesTest

It is used to copy a single file to remote nodes. The usage remains the same as CopyListedFiles.
Example: opatch util copylistedfilestest -fp /home/oracle/a -remote_nodes ceintcb-a5
This is used where RAC setup is present.

CopyListedDirs

It is used to recursively copy all directories listed in ORACLE_HOME/.patch_storage/<ID>/rac/copy_dirs.txt to remote nodes. If -dp option is used, then one can specify the path of the file containing the list of directories to be copied. The directories mentioned in this file will be copied to the remote nodes.
This is used where RAC setup is present.

CopyListedDirsTest

It is used to copy a single file to remote nodes. The usage remains the same as CopyListedDirs.
This is used where RAC setup is present.

RemoveListedFiles

It is used to remove files listed in ORACLE_HOME/.patch_storage/<ID>/rac/remove_files.txt on remote nodes. If -fr option is used, then one can specify the path of the file containing the list of files to be removed. The files mentioned in this file will be removed from the remote nodes.
This is used where RAC setup is present.

RemoveListedFilesTest

It is used to remove a single file from remote nodes. The usage remains the same as RemoveListedFiles.
This is used where RAC setup is present.

RemoveListedDirs

It is used to recursively remove directories listed in ORACLE_HOME/.patch_storage/<ID>/rac/remove_dirs.txt from remote nodes. If -dr option is used, then one can specify the path of the file containing the list of directories to be removed. The directories mentioned in this file will be removed from the remote nodes.
This is used where RAC setup is present.

RemoveListedDirsTest

It is used to remove a single directory from remote nodes. The usage remains the same as RemoveListedDirs.
This is used where RAC setup is present.

RunLocalMake

It is used to invoke re-link on the local node. The make commands are stored in ORACLE_HOME/.patch_storage/<ID>/make.txt. You need to use the -ps option to specify the Patch ID with timestamp. A directory by this name will be present under ORACLE_HOME/.patch_storage. The make.txt file present under ORACLE_HOME/.patch_storage/<Patch ID with timestamp>/ will be used to perform the local make operation. This command cannot be run if you have already run Cleanup as it would have removed these make.txt files.

Example: opatch util runlocalmake -ps 6121250_ un_21_2007_04_16_11

RunRemoteMake

It is used to invoke re-link on remote nodes. The make commands are stored in
ORACLE_HOME/.patch_storage/<ID>/rac/makes_cmd.txt. The usage remains the same as RunLocalMake.
This is used where RAC setup is present.

RunAnyCommand

It is used to run any command on remote nodes. The command should be specified using the -cmd option.
Example: opatch util runanycommand -remote_nodes ceintcb-a5 -cmd ls
This is used where RAC setup is present.

LoadXML

It is used to check the validity of an XML file. The -xmlInput option can be used to specify the path of the xml file.

@ Support can use this utility to verify the integrity of XML files contained in the local and central inventory.
Example: opatch util loadxml -xmlInput $ORACLE_HOME/inventory/ContentsXML/comps.xml

Verify

It is used to run the patch verification process to ensure that the patch was applied to the ORACLE_HOME. It uses the defined ORACLE_HOME and the given patch location via -ph, to run the check.

@ Support can use this utility to re-run the OPatch verification stage, if required for patch diagnosis. The patch staging area has to be present or created.

Example: opatch util verify -ph ~/6646853/6121183

Troubleshooting

Some times the inventory get corrupted because of some issues. In that case you need to repair the inventory. Following are the two methods which I know can be used to repair the inventory.

1) Development has released a utility (checkinv) which can be use to repair the  inventory if it becomes corrupted for some reason.

Please note that this version of checkinv works only for 9.2 and 10.1 releases of oracle database. This doesn’t work for 10.2 releases of oracle database.

You can download the checkinv utility from Patch 4121946.

To cleanup the components, you should determine the unwanted or dangling components, then specify those using the option “-remcomp”, to cleanup the component list.

The utility detects and asks user’s permission to repair a bad inventory.  In case you want to repair the inventory, the utility will first back up the files before any modification.

However, it is better that you backup the inventory before running the tool.

For more details, check metalink note ID 298906.1

2) Creating a new inventory using Oracle Universal Installer (OUI).

Hope this helps !!

References:

Metalink note ID : 298906.1
Metalink note ID : 554417.1
Metalink note ID : 374092.1
Metalink note ID : 357221.1

The Internal Workflow of an E-Business Suite Concurrent Manager Process

Concurrent processing is one of the key elements of any E-Business Suite system. It provides scheduling and queuing functionality for background jobs, and it’s used by most of the application modules. As many things depend on concurrent processing, it’s important to make sure that the configuration is tuned for your requirements and hardware specification.

This is the first article in a series about the performance of concurrent processing. We’ll take a closer look at the internals of concurrent managers, the settings that affect their performance, and the ways of diagnosing performance and configuration issues. Today we’ll start with an overview of the internal workflow of a concurrent manager process. Enjoy the reading!

There are few things that need to be clear before we start:

1. This is only about how the concurrent processing framework (the concurrent managers) works and not about the concurrent requests executed in the framework.
2. There are multiple types of concurrent managers in EBS – internal manager, conflict resolution manager, workflow agent listener service, standard manager, etc. Their roles are different, and in this post, I’ll discuss only managers that pick up scheduled concurrent requests from the “queue” and execute them – or specifically all managers that have a type of “Concurrent Manager” set in Concurrent managers’ definition form. Typical examples of these managers are “Standard Manager”, “Inventory Manager”, and probably your own custom concurrent managers created for processing specific types of concurrent requests.

“Concurrent Managers” Form

It is important to understand the internal workflow of a concurrent manager because otherwise, it’s hard to realize how a configuration change actually affects the system. Several years ago, I had to implement an online change of a specialization rule, and it triggered a bounce of all Standard Manager processes – that’s when I realized I had to understand how it worked and since then I have spent lots of hours looking into internals of concurrent managers. I’m not saying that everything is 100% clear for me now – there are too many little things that matter in certain situations. This series of posts will be more about concepts. I hope you’ll find it useful.

So how does a concurrent manager process work? Here is a diagram I created to explain it:

Internal workflow of a concurrent manager process

I’ve numbered each step of the diagram to provide more details about them:

0. This is where the story begins. There is no EXIT state in the diagram as the managers normally process requests in an infinite loop. Obviously, there is a way for a concurrent manager process to receive the command to quit when the managers need to be shut down, but that’s not included here for simplicity.
1. Internal Concurrent Manager (ICM) requests the Service Manager (FNDSM) to start up the Concurrent Manager process. For the Standard Manager processes, the binary executable FNDLIBR is started. For the Inventory Manager, it’s  INVLIBR. There are others too.
2. The manager process connects to the database and reads the settings (e.g profile options, sleep seconds, cache size).
3. The process saves information about itself in FND_CONCURRENT_PROCESSES table (os process id, database name, instance name, DB session identifiers, logfile path and name, and others). It also updates FND_CONCURRENT_QUEUES by increasing the value of RUNNING_PROCESSES.
4. The concurrent manager process collects information from the database to build the SQL for querying the FND_CONCURRENT_REQUESTS table. The query will be used every time the manager process looks for scheduled concurrent requests.  This is the only time the manager process reads the Specialization Rules (which programs it is allowed to execute) from the database. Keep in mind that if the specialization rules are changed while the managers are running, they are bounced without warning as that is the only way to update the specialization rules cached by the manager process.
5. The SQL (from step 4) is executed to collect information about pending concurrent requests from FND_CONCURRENT_REQUESTS table.
6. The results are checked to verify if any requests are pending for execution.
7. If no requests are pending for execution, the manager process sleeps and then goes to step 5. The “Sleep Seconds” parameter of the  “Work Shifts” settings of the concurrent manager determines how long the process sleeps before FND_CONCURRENT_REQUESTS table is queried again. This is the only time the “sleep seconds” setting is used.
8. If there is at least one concurrent request pending for execution, the concurrent manager process caches rowids for the FND_CONCURRENT_REQUESTS rows of pending concurrent requests. The “Cache Size” setting of the concurrent manager specifies how many rowids to cache.
9. The cached list of rowids is checked to verify if there are any unprocessed concurrent requests (rows in FND_CONCURRENT_REQUESTS table) left. If none are left – the processing returns to step 5 and the FND_CONCURRENT_REQUESTS table is queried again.
10. The next unprocessed rowid is picked from the process cache, and the processing starts.
11. Concurrent manager process executes a SELECT-for-UPDATE statement to lock the STATUS_CODE in FND_CONCURRENT_PROCESSES for the request it’s about to process. This is the mechanism to ensure that each concurrent request is executed only once and only by one manager process even if many processes are running simultaneously. The SELECT-for-UPDATE statement can complete with “ORA-00054: resource busy and acquire with NOWAIT specified” or “0 rows updated” if another manager process has started processing the request already.
12. If the STATUS_CODE of the request was locked successfully, the concurrent manager executes the concurrent request. The processing moves to step 9 where the cached list of concurrent requests (rowids) is being checked again.

The workflow is not very complex, but it’s important to remember that there are normally multiple concurrent manager processes running at the same time, and they are competing for the requests to run. This competition introduces another dimension of tuning for settings, like number of concurrent manager processes, sleep seconds, or cache size. Stay tuned for the next post in the series to find out more!

 Differenece between Dbtier,appstier,dbTechstack,Dbconfig,database  

  (Different EBS Cloning techniques)

Options with adcfgclone.pl

Application Node: appsTier | appltop  | atTechStack

Database Node: dbTier   | database | dbconfig | dbTechStack | addracnode

 Configure Target System

On Database Tier

adcfgclone.pl dbTier

·         Create Context File

·         Register Oracle Home

·         Relink Oracle Home

·         Configure Oracle Home

·         Recreate Control File

·         Configure Database

·         Start SQL*Net Listener

What it does?

adcfgclone.pl dbTier à adclone.pl à ApplyDBTier.java à  1- ApplyDatabase.java

                                                                                   2- ApplyDBTechStack.java

     Application Tier

adcfgclone.pl appsTier

·         Create Context File

·         Register Oracle Homes

·         Relink Oracle Homes

·         Configure Oracle Homes

·         Create INST_TOP

·         Configure APPL_TOP

·         Start Apps Processes

What it does?

adcfgclone.pl dbTier à adclone.pl à ApplyAppsTier.java à  1- ApplyApplTop.java

                                                                                      2- ApplyAppsTechStack.java

4-   Other Configuration Commands

On Database Tier

adcfgclone.pl dbTechStack

·         Create Context File

·         Register Oracle Home

·         Relink Oracle Home

·         Configure Oracle Home

·         Start SQL*Net Listener

adcfgclone.pl dbconfig

·         Configure Database

adcfgclone.pl database

·         Recreate Control File

·         Configure Database

·         Start SQL*Net Listener

On Applications Tier

adcfgclone.pl atTechStack

·         Create Context File

·         Register Oracle Homes

·         Relink Oracle Homes

·         Configure Oracle Homes

·         Create INST_TOP

adcfgclone.pl appltop

·         Configure APPL_TOP

·         Create INST_TOP

·         Start SQL*Net Listener

adclonectx.pl

·         Clone Context File

·         Allows Automation

What it does?

adclonectx.pl à CloneContext.java