Merge Patches using admrgpch

Admrgph utility is used to merge two or more patches in oracle apps. The advantage of merging patches is that it reduces downtime as the repetitive task of compiling invalid database objects,  generating forms and reports,jar files etc.

How to use Admrgpch to merge patches

Download the  patches in /patch directory. Now create 2 subdirectory in /patch say mergesource -which contains  the unzipped patches to be merged and mergedest -which contains the merged patch. Please note that both mergesource and mergedest should be created as immediate child of same parent directory say /patch. Now you can execute the following command to merge the patches.

admrgpch -s <source> -d <dest> -merge_name <mergename>

For example->

admrgpch -s </patch/mergesource> -d </patch/mergedest> -merge_name <amebrup2>

Please make sure the the merge path log file "admrgpch.log" does not contain any error. If the  above command to merge patches completes successfully then it displays the following->

Executing the merge of the patch drivers
-- Processing patch: /patch/mergesource/5708576
-- Done processing patch: /patch/mergesource/5708576

-- Processing patch: /patch/mergesource/4428060
-- Done processing patch: /patch/mergesource/4428060

Copying files...

5% complete. Copied 269 files of 5373...
10% complete. Copied 538 files of 5373...
15% complete. Copied 806 files of 5373...
20% complete. Copied 1075 files of 5373...
25% complete. Copied 1344 files of 5373...
30% complete. Copied 1612 files of 5373...
35% complete. Copied 1881 files of 5373...
40% complete. Copied 2150 files of 5373...
45% complete. Copied 2418 files of 5373...
50% complete. Copied 2687 files of 5373...
55% complete. Copied 2956 files of 5373...
60% complete. Copied 3224 files of 5373...
65% complete. Copied 3493 files of 5373...
70% complete. Copied 3762 files of 5373...
75% complete. Copied 4030 files of 5373...
80% complete. Copied 4299 files of 5373...
85% complete. Copied 4568 files of 5373...
90% complete. Copied 4836 files of 5373...
95% complete. Copied 5105 files of 5373...
100% complete. Copied 5373 files of 5373...

Character-set converting files...

  2 unified drivers merged.

Patch merge completed successfully

Please check the log file at ./admrgpch.log

Now go to the destination merge patch directory say "mergedest". You can see that the admrgpch already created a driver with name "u_<mergename>.drv" say u_amebrup2.drv. Now apply the merged patch as a single patch using  adpatch. So you have to give this driver name u_<mergename>.drv" when prompted.

Restrictions of admrgpch ->

It will not merge patches of different releases,platform,different parallel modes. Also do not use admrgpch to merge AD and Non-AD patches ad AD patches will change the patch utility itself.

You are welcome to leave a comment.

Purge old files on Linux/Unix using “find” command

I've noticed that one of our interface directories has a lot of old files, some of them were more than a year old. I checked it with our implementers and it turns out that we can delete all files that are older than 60 days.

I decided to write a (tiny) shell script to purge all files older than 60 days and schedule it with crontab, this way I won't deal with it manually. I wrote a find command to identify and delete those files. I started with the following command:

find /interfaces/inbound -mtime +60 -type f -maxdepth 1 -exec rm {} \;

It finds and deletes all files in directory /interface/inbound that are older than 60 days.
"-maxdepth 1" -> find files in current directory only. Don't look for files in sub directories.

After packing it in a shell script I got a request to delete "csv" files only. No problem... I added the "-name" to the find command:

find /interfaces/inbound -name "*.csv" -mtime +60 -type f -maxdepth 1 -exec rm {} \;

All csv files in /interface/inbound that are older than 60 days will be deleted.

But then, the request had changed, and I was asked to delete "*.xls" files further to "*.csv" files. At this point things went complicated for me since I'm not a shell script expert...

I tried several things, like add another "-name" to the find command:

find /interfaces/inbound -name "*.csv" -name "*.xls" -mtime +60 -type f -maxdepth 1 -exec rm {} \;

But no file was deleted. Couple of moments later I understood that I'm trying to find csv files which is also xls files... (logically incorrect of course).

After struggling a liitle with the find command, I managed to make it works:

find /interfaces/inbound \( -name "*.csv" -o -name "*.xls" \) -mtime +60 -type f -maxdepth 1 -exec rm {} \;

You are welcome to leave a comment.

Troubleshooting APP-FND-01630:

Few days back when I was working with FNDLOAD utility,got the below error.

FNDLOAD apps/DEV2APPLSYS O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup_XXTWC_YIELD_DIGITALPHONE.ldt
Log filename : L6977176.log


Report filename : O6977176.out
APP-FND-01630: Cannot open file L6977176.log for appending

Cause: FDPFOP encountered an error when attempting to open file L6977176.log for appending.

Action: Verify that the filename is correct and that the environment variables controlling that filename are correct.

Action: If the file is opened in read mode, check that the file exists. Check that you have privileges to read the file in the file directory. Contact your system administrator to obtain read privileges.

Action: If the file is opened in write or append mode, check that you have privileges to create and write files in the file directory. Contact your system administrator to obtain create and write privileges.


Solutions:
Check the permission in the Directory where you are trying to do FNDLOAD upload.

touch file1

       If you are able to create the file and have the required permission and still the issue exists,perform the below steps.

Note 1:

In R12. Context File is no more in $APPL_TOP/admin directory.It has been moved to $INST_TOP/appl/admin directory

Note 2:
Check env variables APPLTMP and APPLPTMP
$env|grep TMP
APPLTMP=/product/app/TESTAPPS/inst/apps/TESTAPPS_NodeApps/appltmp
APPLPTMP=/ora_backup/u0001
REPORTS_TMP=/product/app/TESTAPPS/inst/apps/TESTAPPS_NodeApps/temp
NodeApps(TESTAPPS)  /product/app/TESTAPPS/inst/apps/TESTAPPS_NodeApps/admin/install

Change the value of the APPLTMP and APPLPTMP environment variable in context file to point to new Directory and run autoconfig to make changes saved.Also make sure
utl_file_dir has the same value.


sqlplus / as sysdba (On DB Tier)
show parameter utl_file_dir;

Step 1:Run cmclean.sql
>sqlplus apps

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Nov 7 23:42:44 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @cmclean.sql

Note:CMCLEAN.SQL is the Non Destructive Script to Clean Concurrent Manager Tables

Step 2:Bounce the middle Tier applications services


Hope now the issue is resolved...

Enjoy Apps DBA Troubleshootings.

You are welcome to leave a comment.
 

Rebuild Index in oracle

In the following scenarios, one can think about rebuilding indexes->
1 If the ratio of deleted to actual leaf entries is more than 20% or
2 If the "blevel">3 (in table dba_indexes)
Here's how you can find it.You are supposed to do
analyze index validate structure ;
and then query the view INDEX_STATS
SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 FROM INDEX_STATS;

Here's the command to rebuild indexes ->
alter index rebuild online;
You will require twice the space because during index rebuilding, the old index will still be there and will be dropped only when the new one has been created successfully.
References-> Oracle metalink document Id-182699.1 when and how to rebuild indexes. It provides the "bde_rebuild" script to automate rebuilding indexes .



You are welcome to leave a comment.

This summary is not available. Please click here to view the post.

This summary is not available. Please click here to view the post.

How to find if server is single node or multi node

Oracle apps can be implemented on single node or multi node.

In single node-database,CM,Reports,forms,Apache are all on one single server only.

In multi-node we can have these components on different servers.

A typical 2-node server has the following->

1st node-database,CM,reports

2nd node-forms,Apache

A typical 3 node server has the following->

1st node-database

2nde node-CM,reports

3rd node-forms,Apache

Here's how to find out if the server is single node or multi node->

1-select * from fnd_nodes(It also contain column like "support_forms","support_web","support_web","support_db","support_admin" which identifies which tier it supports)

2-OAM-oracle applications manager

3- context file(Has variables s_isDB, s_isAdmin, s_isWeb, s_isForms,s _isConc which identifies which tier it supports)

You are welcome to leave a comment.

Merge Patches using admrgpch

Admrgph utility is used to merge two or more patches in oracle apps. The advantage of merging patches is that it reduces downtime as the repetitive task of compiling invalid database objects,  generating forms and reports,jar files etc.

How to use Admrgpch to merge patches

Download the  patches in /patch directory. Now create 2 subdirectory in /patch say mergesource -which contains  the unzipped patches to be merged and mergedest -which contains the merged patch. Please note that both mergesource and mergedest should be created as immediate child of same parent directory say /patch. Now you can execute the following command to merge the patches.

admrgpch -s <source> -d <dest> -merge_name <mergename>

For example->

admrgpch -s </patch/mergesource> -d </patch/mergedest> -merge_name <amebrup2>

Please make sure the the merge path log file "admrgpch.log" does not contain any error. If the  above command to merge patches completes successfully then it displays the following->

Executing the merge of the patch drivers
-- Processing patch: /patch/mergesource/5708576
-- Done processing patch: /patch/mergesource/5708576

-- Processing patch: /patch/mergesource/4428060
-- Done processing patch: /patch/mergesource/4428060

Copying files...

5% complete. Copied 269 files of 5373...
10% complete. Copied 538 files of 5373...
15% complete. Copied 806 files of 5373...
20% complete. Copied 1075 files of 5373...
25% complete. Copied 1344 files of 5373...
30% complete. Copied 1612 files of 5373...
35% complete. Copied 1881 files of 5373...
40% complete. Copied 2150 files of 5373...
45% complete. Copied 2418 files of 5373...
50% complete. Copied 2687 files of 5373...
55% complete. Copied 2956 files of 5373...
60% complete. Copied 3224 files of 5373...
65% complete. Copied 3493 files of 5373...
70% complete. Copied 3762 files of 5373...
75% complete. Copied 4030 files of 5373...
80% complete. Copied 4299 files of 5373...
85% complete. Copied 4568 files of 5373...
90% complete. Copied 4836 files of 5373...
95% complete. Copied 5105 files of 5373...
100% complete. Copied 5373 files of 5373...

Character-set converting files...

  2 unified drivers merged.

Patch merge completed successfully

Please check the log file at ./admrgpch.log

Now go to the destination merge patch directory say "mergedest". You can see that the admrgpch already created a driver with name "u_<mergename>.drv" say u_amebrup2.drv. Now apply the merged patch as a single patch using  adpatch. So you have to give this driver name u_<mergename>.drv" when prompted.

Restrictions of admrgpch ->

It will not merge patches of different releases,platform,different parallel modes. Also do not use admrgpch to merge AD and Non-AD patches ad AD patches will change the patch utility itself.

You are welcome to leave a comment.

Differences between Oracle Apps 11i and R12

The Database Version was 9i Release 2 in Oracle Apps 11i. It has been replaced by 10g Release 2 (10.2.0.2) in Apps R12.

HTTP server consist of iAS (1.0.2.2.2) in apps 11i. It has been replaced by 10.1.3 (10g AS) in apps R12.

Forms 6i in apps 11i has been replaced by Forms 10.1.2 (10g AS) in apps R12.

Socket mode was the default for forms connectivity in apps 11i. In R12, servlet mode is the default.

Jserv in apps 11i has been replaced by OC4J in R12.

JDK 1.3.1 in apps 11i has been replaced by JRE 1.5 in apps R12.

JDBC version 9 in apps 11i has been replaced by JDBC 10.2.0 in Apps R12.

Modplsql/ mod_pls has been removed from Apps R12.

NOTE:- Although Release 12 uses OracleAS 10g -10.1.2 and 10.1.3, these components do not require an OracleAS 10g Infrastructure (Metadata Repository). The Release 12database does not include a OracleAS 10g Infrastructure.

You are welcome to leave a comment.

Unable to lock Central Inventory. OPatch will attempt to re-lock

Sometimes while applying a patch on the database (opatch) i come across the following error->
Unable to lock Central Inventory. OPatch will attempt to re-lock.
Do you want to proceed? [y|n]
n
User Responded with: N
Unable to lock Central Inventory. Stop trying per user-request?
OPatchSession cannot load inventory for the given Oracle Home /u01/XXXXDB/product/10.2.0. Possible causes are:
No read or write permission to ORACLE_HOME/.patch_storage
Central Inventory is locked by another OUI instance
No read permission to Central Inventory
The lock file exists in ORACLE_HOME/.patch_storage
The Oracle Home does not exist in Central Inventory

ApplySession failed: ApplySession failed to prepare the system. Unable to lock Central Inventory. Stop trying per user-request?
System intact, OPatch will not attempt to restore the system
OPatch failed with error code 73

Here's the Solution
/var/opt/oracle/oraInventory/ContentsXML/comps.xml was owner by applmgr and oracle was not having write permissions on it. Changed the owner to "oracle" & changed the permissions to "777" which resolved the issue.
Sometimes the above permissions are already there and patch failed because of other issues. You might want to do the following in these cases
Remove the directory /var/opt/oracle/oraInventory/locks if it exists
Remove the file under $ORACLE_HOME/.patch_storage if it exists
Change the permissions of /var/opt/oracle/oraInventory to "777" and owner to oracle.
Please note that "/var/opt/oracle/oraInventory" is the central inventory as mentioned in /var/opt/oracle/oraInst.loc .

You are welcome to leave a comment.

Oracle Applications SQL Scripts

oa_info.sql	-	Nice script to show the physical layout of an Oracle Applications database. Includes product installations and versions (e.g. duplicates information from auto install and adutconf.sql).
fndlock.sql	-	This is lockmon with the Oracle Applications user added for apps account logins.
fndreq.sql	-	Show currently running concurrent requests
fndusrmap.sql	-	Maps Oracle Applications user information, similar to the Monitor Users screen.  This query has been improved to handle SC/NCA users more accurately.  NOTE: fndchrmap.sql has been removed and the code added as comments back into this script.
fnd_long.sql	-	Displays concurrent requests that have run times longer than one hour (3600 seconds)
fndcmmap.sql	-	This script will map concurrent manager process information about current concurrent managers.
fndrqmap.sql	-	This script will map concurrent request information about a specific request id.
fnd_cp_time.sql	-	Script to list concurrent programs by time started.
fnd_pgm.sql	-	Script to list concurrent programs by name and run time.
fnd_pgm_RP.sql	-	Script to list concurrent programs in a resource profile type format.
fnd_cp_ovlp_bymgr.sql	-	Script to list concurrent manager queue overlaps.
fnd_cm_hrly_sum.sql	-	Script to list concurrent manager utilization by hour. You are welcome to leave a comment.

How to find if module is installed or not in Oracle Apps

We have 3 ways to find out if a module is installed in oracle apps

1 We can run the following script
cd $AD_TOP/sql/adutconf.sql
In this script, there is a section->"Product Installation Status, Version Info and Patch Level"
In this, status of installed means the product is installed.

2  Through OAM
In oracle apps, navigate to
OAM>Site Map>/License Manager>Reports>Licensed Products
Here filter the products by  using "Licensed". These are the licensed and installed products in oracle apps.

3 Using a sql query->
We can use the following query
SELECT a.application_name,a.product_code,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id
and b.status='I'
order by product_code asc;

How to audit users accessing responsibility/forms in Apps

Manytimes we want to audit, which users access which responsibilities. There is a direct way of doing it in oracle apps.
There is a System Profile in Oracle Apps "Sign-on Audit level". The profile "sign on audit level" can be set to any of the 4 values->
Users-determines login and log-off timings,terminal from which user logs in oracle apps
Resposibilty-does user level auditing + which user logs in responibilty at which time, and when does the user navigates off  from it.
Forms-does respobility level auditing + which user access which forms and how  much time user spends on that form.
None-none

Once the above profile is set to the desired value, it will start auditing from the next time the user logs in. We can now run the concurrent request "Sign on Audit Responsibilities" to capture which users accessed which responsibilities.

Oracle Installation Error

Long time back, while installing Oracle on Windows XP, i faced the following strange problem.

"Error in writing to directory "C:\DOCUME~1\XP\LOCALS~1\Temp\OraInstall2009-04-12_08-26-12PM. Please ensure that this directory is writable and has atleast 60 MB of free space. Installation cannot continue."

I checked that the directory was writable and C: had over 5 GB free space.The solution was however, also very strange.
I copied the software directory directly in C: and then started the installation. It worked without any error.
Earlier i was running the setup from "Desktop". Even running it from "My Documents" did not work.

How to FIND and REPLACE in unix

Many times we want to find and replace all the occurences of  one word in a file to another word in Unix. Using the power of vi editor, it can be done easily.
Here is the syntax for the same in unix

:1,$s/oldword/newword/g

example
To find and replace all occurences of "erp" with "BERP" in a file, we have to do the following->
Open the file in vi editor, and issue the following command
:1,$s/erp/BERP/g