Oracle ERP DBA

In this post, I shall talk about deploying HTB in 10gAS. Pre-requisites : 1. Oracle Applications E-Business Suite (EBS) 11.5.10.2 2. Oracle 10g AS 10.1.2.0.2 3. Standalone OC4J 10.1.2.0.2 Steps : 1. Apply ATG RUP 4 patches (pre and post requisites). 2. Apply the HTB v5.3 patches (4178883 -- Patch 11i.HC_PF.E and 5178799 -- HC_PF: 11I.HC_PF.E ROLLUP 3 and their pre and post requisites). Note : Ensure that standalone OC4J is installed (unzipped) and the OC4J environment variable is set before applying patches 4178883 (Patch 11i.HC_PF.E) and 5178799 (HC_PF: 11I.HC_PF.E ROLLUP 3). See previous post for explanation. 3. Install Oracle 10gAS 10.1.2.0.2 on a server other than that hosting Oracle Apps (EBS). If it is installed on the same server as EBS (web tier - if one has a multi-node install), it does make life simple! 4. Copy over the APPL_TOP and the JAVA_TOP from the Oracle Applications EBS web tier to the Oracle 10g AS server. 5. Create a new OC4J Instance in the newly installed Oracl

1. Query to find the time taken for a patch to be applied. col name for a15 col orig_patch_name for a15 col runtime for a30 SELECT NAME, ORIG_PATCH_NAME, to_char(START_DATE,'DD-MON-YYYY::HH24:MI:SS') START_DATE, to_char(END_DATE,'DD-MON-YYYY::HH24:MI:SS') END_DATE, TO_CHAR(FLOOR((end_date - start_date) * 24)) || ' hrs ' || TO_CHAR(FLOOR((end_date - start_date) * (24 * 60)) - (FLOOR((end_date - start_date) * 24) * 60)) || ' min ' || TO_CHAR(FLOOR((end_date - start_date) * (24 * 60 * 60)) - (FLOOR((end_date - start_date) * 24 * 60) * 60)) || ' sec ' RUNTIME, SUCCESS_FLAG FROM applsys.ad_patch_runs JOIN applsys.ad_patch_drivers USING (patch_driver_id) JOIN applsys.ad_appl_tops USING (appl_top_id) where orig_patch_name like '%&patch_number%' ORDER BY ad_patch_runs.creation_date DESC; 2. Note 364439.1 argues that checking ad_bugs for applied patches might not always be the good idea to check whether a given

Many a time, one is faced with a situation where one needs to monitor a process to its completion. For example, if one is performing a clone, one has to copy the source files to the target or if one is performing a backup of any instance, the cp or tar commands that one has fired has to be monitored to its completion. I generally use a customised script (as below) during those situations to monitor the processes. while true do ps -fu <owner_of_the_process> grep <cp or rman or process_id> wc -l printf "\n\nPress Ctrl+C to exit this loop.......\n\n" sleep <the_number_of_seconds_this_process_should_sleep> done

In this post, I shall talk about deploying HTB with standalone OC4J. Note : This needs to be done on the web tier in case you have a multi-node installation. 1. Download standalone OC4J from here . As far as I know, only OC4J versions upto 10.1.2.0.2 are certified with HTB. 2. Unzip the downloaded file to any directory. 3. Install the standalone OC4J using the below command: $java -jar oc4j.jar –install Note : During the installation, you would be asked the enter the admin username (? - I do not really remember if it asks for admin username. If not, then the default username is admin) and the password. Do remember both as they are needed to stop OC4J. 4. Create a file called custom<$TWO_TASK>.env under APPL_TOP. #!/bin/sh OC4J_HOME=<Path_To_OC4J_Directory> export OC4J_HOME 5. Apply the patches 4178883 (Patch 11i.HC_PF.E) and 5178799 (HC_PF: 11I.HC_PF.E ROLLUP 3) and their pre and post requisites. Note : It is interesting to note that the OC4J_HOME environment variable needs

This is the patch list that I followed for applying HTB v5.3 on a 11.5.10.2 EBS installation with database running 10g R2 (10.2.0.2). Once all the patches are applied, you can safely say that HTB is indeed installed on the EBS installation! 4119792 -- RDBMS Server: Patch INS_OEMAGENT.MK IS NOT USING THE GLIBC STUBS 4186352 -- LIBOLAPAPI9.SO FILE MISSING FROM RDBMS 9.2.0.6.0 PATCHSET 4712852 -- Minipack 11i.AD.I.4 5478710 -- 11i.ADX.F 5658489 -- CPUJAN2007:TECHNOLOGY STACK COMPONENTS FOR CRITICAL PATCH UPDATE JAN 2007 5891161 -- JDBC Patch 5225940 -- POST ADX-F FIXES 3349676 -- SERVICEBEAN OAF5.7 ARU V4 3634650 -- ONE-OFF PATCH FOR BUG#3634321 ON SERVICEBEAN 11.5.57 ARU V4 3821746 -- BI BEANS CATALOG PATCH V5 - BASED ON BI BEANS V3.1.0.54 4318480 -- HEALTHCARE SERVICEBEAN 11.5.10 ARU ROLLUP V1 5449280 -- FILE IN PATCH IS NOT A KNOWN ORACLE APPLICATIONS FILE: JTF MEDIA RUN_ENABLED.GIF 3904641 -- CP

I am not sure how many people have worked with Oracle HTB (Healthcare Transaction Base), but I have been part of two projects, which involved implementation and maintenance of Oracle HTB. It was a good learning process for me and I enjoyed working on it thoroughly. What exactly is HTB? Below is an extract from the HTB Implementation Guide (which is for the base version 5 - 11i.HC_PF.E. The latest version I believe is 6) on what exactly is Oracle HTB: Oracle Healthcare Transaction Base (HTB) is a comprehensive data repository and serviceinfrastructure that provides independent software vendors, system integrators, and provider organizations with a state of the art software platform that lets them build robust and scalable healthcare applications. HTB software components let HTB based applications centralize and consolidate patient, provider, and healthcare data, including business rules that span the enterprise. HTB provides a unified data model based on the HL7 version3 Reference In

If one would like to find out their Develper patchset version, they can easily refer metalink note 232313.1 titled Information on Previous Versions of Developer 6i Patchsets. However, this note only lists the developer patchsets till patchset 15. In case anyone has an older installation, they would not know where to look. Incidentally, one of our installations is on developer patchset 12 (forms/reports version is 6.0.8.21.3). I had a tough time to find out that we were indeed on developer patchset 12! Hence, this blog entry. In order to find the current Developer 6i patchset version from the forms/reports 6i version, two metalink notes 266541.1 and 74145.1 are available. However, Note 266541.1 provides stale information since it does not include information on the latest two developer patchsets 18 and 19. So, Metalink note 74145.1 seems to be the best bet in finding out the correct developer patchset version. Note : All the metalink links that I have used are from the new metalin

One of my colleagues had started the MWA server (telnet server) and it was running on port number 9198. However, when I queried the processes (ps -ef | grep mwa), I could not find the process which had kick started the MWA server. This had me thinking and I resolved to find the process ID. However, the utility lsof was not installed on the OS (Sun Sparc Solaris 5.10) . When I probed further (googled! actually), I found a script on onlineappsdba , which I found helpful. I had to modify that script suitably to exclude processes not owned by the current user. I also referred another blog , to complete my script. ______________________________________________________ # # Make sure the logfile directory exists # if [ ! -d ${HOME}/bkp/log ]; then mkdir -p ${HOME}/bkp/log fi; if [ $# -ne 1 ]; then echo "To run the program, please provide the port number as the arguement" printf "\n\t ${usage} \n\n" exit 1; fi port_num="$1" echo printf "\n Finding the

I always say that the risk of running a script in an instance wherein it is not meant to be run, is always high, if the SQL prompt in SQL*PLUS is unchanged i.e., if it is retained as "SQL>". This risk can be mitigated, to a great extent, if not fully, by customising the SQL prompt to something like ' USER@SID> '. This can be achieved in Oracle 10g quite easily (refer my earlier post ). Releases prior to Oracle 10g did not have the special pre-defined variable "_USER", which can be set in glogin.sql. However, there exist a lot of workarounds for customising the SQL Prompt and retaining it! I have detailed below one such workaround. 1. Modify the .profile of each user and add the environment variable SQLPATH to it so that our scripts can be accessed from anywhere. e.g: export SQLPATH=/u019/app/oracle/govtt/govttora/8.0.6/sqlplus/admin;/u018/app/oracle/govtt/govttdb/9.2.0/sqlplus/admin 2. Create two files login.sql and connect.sql under $ORACLE_HOME/sql