Thursday, October 30, 2008

Deploy HTB in 10gAS

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 Oracle 10g AS.





6. Enter the OC4J Instance name (say HTB) and click on CREATE.




7. Click "OK" on the confirmation page.


8. New OC4J Instance HTB seen in the front page.




9. Replace the JDBC library files in <10gas_oracle_home>/jdbc/lib with the JDBC library files from $JAVA_TOP using the following commands:

mv $10gAS_ORACLE_HOME/jdbc/lib/classes12dms.jar $10gAS_ORACLE_HOME/jdbc/lib/classes12dms.jarO


mv $10gAS_ORACLE_HOME/jdbc/lib/nls_charset12.jar $10gAS_ORACLE_HOME/jdbc/lib/nls_charset12.jarO


cp -p $JAVA_TOP/jdbc14.zip $10gAS_ORACLE_HOME/jdbc/lib/classes12dms.jar


cp -p $JAVA_TOP/nls_charset12.zip $10gAS_ORACLE_HOME/jdbc/lib/nls_charset12.jar




10. Copy the dbc file from $FND_TOP/secure/<CONTEXT_NAME>/<sid>.dbc to $10gAS_ORACLE_HOME/j2ee/<APPLICATION_NAME>/.


11. Navigate to the new OC4J Instance and modify the server properties.

HTB (New OC4J Instance) ==> Administration ==> Server Properties

Under the section Command Line Options,
add the below in the field java options and click on apply.

-server -Xmx1024M -Xms1024M -Djava.security.policy=<10gAS_ORACLE_HOME>/j2ee/<NEW_OC4J_INSTANCE>/config/java2.policy -Djava.awt.headless=true -DClientMode=local -DworkflowSupported=N -Djbo.323.compatible=true -DDBCFile=<10gAS_ORACLE_HOME>/j2ee/<NEW_OC4J_INSTANCE>/<SID>.dbc -DJTFDBCFILE=<10gAS_ORACLE_HOME>/j2ee/<NEW_OC4J_INSTANCE>/<SID>.dbc -DLONG_RUNNING_JVM=true -Ddedicated.rmicontext=true -Djbo.connectfailover=false -DAFLOG_ENABLED=true -DAFLOG_LEVEL=EXCEPTION -DAFLOG_MODULE=%ctb%,%hct% -DAFLOG_FILENAME=./aflog_c


12. Modify the application.xml under $10gAS_ORACLE_HOME/j2ee/<NEW_OC4J_INSTANCE>/config file to include the $CTB_TOP and $JAVA_TOP.

<library path="/oracle/ashtb/htbappl/ctb/11.5.0"/>
<library path="/oracle/ashtb/htbcomn/java"/>



13. Start the new OC4J Instance.

Navigation: <HOST_NAME.DOMAIN> ==> OC4J:<NEW_OC4J_INSTANCE> ==> HOME

Click on the Start button.


14. Once the new OC4J instance is started, deploy the htb.ear file.

a. Download the htb.ear under $CTB_TOP/java/ear file to the local desktop to any location (say C:\htb).

b. Navigate to <HOST_NAME.DOMAIN> ==> OC4J:<NEW_OC4J_INSTANCE> ==> Applications

c. Click on "Deploy EAR File".

d. Enter the below information:

J2EE Application = <PATH to the htb.ear file on the local machine>

Application Name = HTB

Parent Application = Defualt

Click on Continue.

e. In the next screen - URL Mapping for Web Modules - click Next.

f. Click on "Next" without changing anything in the Resource Reference Mappings window.

g. In the User Manager window, all the data is entered by default apart from the DBC_FILE_PATH. Enter the path to the DBC file under $10gAS_ORACLE_HOME/j2ee/<NEW_OC4J_INSTANCE> and click on Next.

h. In the Review window, click on "Deploy".

g. Click on OK in the confirmation screen.


15. Modify the file data-sources.xml (10gAS_ORACLE_HOME/j2ee/<NEW_OC4J_INSTANCE>/config):

Add the below line:

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="OracleDS"
location="jdbc/OracleCoreDS"
xa-location="jdbc/xa/OracleXADS"
ejb-location="jdbc/OracleDS"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="apps"
password="apps" url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<DB_HOST_NAME.DOMAIN>)(PORT=<DB_PORT>))(CONNECT_DATA=(SID=<SID>)))"
inactivity-timeout="600"
max-connections="100"
min-connections="100"
stmt-cache-size="200"
/>



We are done as far as deployment of HTB in 10gAS is concerned.

Wednesday, October 29, 2008

Useful Oracle Apps (EBS) Patch Queries

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 patch or a list of patches are applied. Instead it lists the below pl/sql procedure that can be used.


a. Query to find whether a list of patches were applied or not.

set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
select appl_top_id, name
from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.enable;
dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943');
--
LOOP
FETCH alist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN alist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,p_patchlist(i));
println('..Patch ' || p_patchlist(i) || ' was ' || p_patch_status);
end loop;
END if;
println('.');
END LOOP;
--
close alist;
END;
/



b. Query to find whether a single patch was applied or not -- My customisation!

set serveroutput on;
DECLARE
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
cursor atoplist is
select appl_top_id, name from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.enable;
dbms_output.put_line(msg);
end;
--
BEGIN
open atoplist;
--
LOOP
FETCH atoplist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN atoplist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,&bug);
println('..Patch ' || &bug || ' was ' || p_patch_status);
END if;
println('.');
END LOOP;
--
close atoplist;
END;
/

Shell script to monitor processes

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


Friday, October 24, 2008

Deploy HTB on standalone OC4J

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 to be set for patches 4178883 and 5178799 to complete successfully since they run Autoconfig and during the Autoconfig run, if the environment variable OC4J_HOME is not set, then it will error with:
Executing script in InstantiateFile:
/oracle/applhtb/htbcomn/admin/install/HTB_dseiq-lab-vm2/ctbjdbc.sh

script returned:
****************************************************

ERRORCODE = 1 ERRORCODE_END
.end std out.
mv: cannot stat `/jdbc/lib/classes12dms.jar': No such file or directory
sed: can't read /tmp/tmp.txt: No such file or directory
mv: cannot stat `/jdbc/lib/nls_charset12.jar': No such file or directory
sed: can't read /tmp/tmp.txt: No such file or directory
cp: cannot create regular file `/jdbc/lib/classes12dms.jar': No such file or directory
sed: can't read /tmp/tmp.txt: No such file or directory
cp: cannot create regular file `/jdbc/lib/nls_charset12.jar': No such file or directory
sed: can't read /tmp/tmp.txt: No such file or directory



6. Perform any post patching steps such as compile apps schema, generate message files and compile flex fields.


7. Copy the htb.ear file to $OC4J_HOME

cp –p $CTB_TOP/java/ear/htb.ear $OC4J_HOME/j2ee/home


8. Modify the file $OC4J_HOME/j2ee/home/config/application.xml to update the library paths. Add the following lines:

<library path="<Absolute Path to the $CTB_TOP>"/>
<library path="<Absolute Path to the $JAVA_TOP>"/>
<library path="../../../jdbc/lib/classes12dms.jar"/>



9. Verify the ports in the XML config files - Make sure the default ports used in the following files are available in the host, else choose any free port available and edit the files

$OC4J_HOME/j2ee/home/config/rmi.xml
$OC4J_HOME/j2ee/home/config/jms.xml
$OC4J_HOME/j2ee/home/config/http-web-site.xml


10. Replace the JDBC library files in /jdbc/lib with the JDBC library files from $JAVA_TOP using the following commands:

mv $OC4J_HOME/jdbc/lib/classes12dms.jar $OC4J_HOME/jdbc/lib/classes12dms.jarO

mv $OC4J_HOME/jdbc/lib/nls_charset12.jar $OC4J_HOME/jdbc/lib/nls_charset12.jarO

cp -p $JAVA_TOP/jdbc14.zip $OC4J_HOME/jdbc/lib/classes12dms.jar

cp -p $JAVA_TOP/nls_charset12.zip $OC4J_HOME/jdbc/lib/nls_charset12.jar



11. Edit the file '$OC4J_HOME/j2ee/home/config/data-sources.xml'

Replace the existing <data-source> with the following <data-source>


<data-source class="com.evermind.sql.DriverManagerDataSource"
name="OracleAppsDS"
location="jdbc/OracleAppsCoreDS"
ejb-location="jdbc/OracleAppsDS"
xa-location="jdbc/OracleAppsXADS"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="apps"
password=""
url="jdbc:oracle:thin:@host:port:SID"
inactivity-timeout="30000"
/>


Replace "<appspass>" with the actual apps password and "@HOST:PORT:SID" with "@<real host name>:<DB Port#>:<ORACLESID>.



12. Post-install steps for Outbound Message - create a directory htbclient under $OC4J_HOME. $OC4J_HOME/htbclient will now be addressed as $HTBCLIENT.

$ cd $OC4J_HOME
$ mkdir htbclient



13. Copy the following jar files into $HTBCLIENT

$ cp -p $OC4J_HOME/j2ee/home/oc4j.jar .
$ cp -p $OC4J_HOME/j2ee/home/oc4jclient.jar .
$ cp -p $OC4J_HOME/j2ee/home/lib/servlet.jar .
$ cp -p $OC4J_HOME/j2ee/home/lib/ejb.jar .
$ cp -p $OC4J_HOME/j2ee/home/lib/jndi.jar .
$ cp -p $OC4J_HOME/j2ee/home/lib/jta.jar .
$ cp -p $OC4J_HOME/j2ee/home/lib/jmxri.jar .
$ cp -p $OC4J_HOME/j2ee/home/lib/jaas.jar .
$ cp -p $OC4J_HOME/diagnostics/lib/ojdl.jar .



14. Update the AF_CLASSPATH in $APPL_TOP/admin/adovars.env to point to

AF_CLASSPATH=$HTBCLIENT:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/jre/lib/i18n.jar:$JAVA_TOP/appsborg2.zip:$IAS_HOME/rdbms/jlib/aqapi.jar:$IAS_HOME/rdbms/jlib/jmscommon.jar:$HTBCLIENT/oc4j.jar:$HTBCLIENT/oc4jclient.jar$HTBCLIENT/servlet.jar:$HTBCLIENT/jms.jar:$HTBCLIENT/ejb.jar:$HTBCLIENT/jndi.jar:$HTBCLIENT/jta.jar:$ORACLE_HOME/forms60/java:$HTBCLIENT/jmxri.jar:$HTBCLIENT/jaas.jar:$HTBCLIENT/ojdl.jar:$JAVA_TOP

Where:
$OC4J_HOME is the home directory where OC4J is installed.
$HTBCLIENT is the directory you created under $OC4J_HOME. For example:
$OC4J_HOME/htbclient.
$IAS_HOME is the location where Oracle E-Business Suite installs Oracle iAS.
The E-Business Suite ships with its own application server install, and
$IAS_HOME refers to that installation.
$JAVA_HOME is where the JDK is installed.

Please remove the following jar files if present, from the AF_CLASSPATH:
bc4jdomorcl.jar and bc4jct.jar.


Note: Also modify the AF_CLASSPATH variable in the XML file so that the next time autoconfig is run, the value of the AF_CLASSPATH is not overwritten.


15. Create a file called jndi.properties under $HTBCLIENT with the following lines

java.naming.factory.initial=com.evermind.server.rmi.RMIInitialContextFactory
java.naming.provider.url=ormi://:/htb



16. Start the OC4J Container

Navigate to $OC4J_HOME/j2ee/home

Use the following case sensitive parameters (the dbc file should be located in $FND_TOP/secure):

-Djbo.323.compatible=true

-DDBCFile=<absolute path to the dbc file including the file name>

-DJTFDBCFILE=<absolute path to the dbc file including the file name>


For example (increase memory if required):

java -Xmx512M -Xms512M -DworkflowSupported=<N - if the worflow need to be disabled (OR) Y - if the workflow need to be enabled> -Djbo.323.compatible=true -DDBCFile=<absolute path to the dbc file including the file name> -DJTFDBCFILE=<absolute path to the dbc file including the file name> -jar oc4j.jar

A message is displayed indicating that the OC4J container has been initialized.


17. Deploy the htb.ear file

$ cd $OC4J_HOME/j2ee/home

java -Xmx512M -Xms512M -jar admin.jar ormi://<host_name>:<rmi_port> admin admin -deploy -file htb.ear -deploymentName htb


Note: You can get the RMI port number from the file $OC4J_HOME/j2ee/home/config/rmi.xml


18. Update the orion-application.xml files to include the absolute path to the dbc file name

$OC4J_HOME/j2ee/home/application-deployments/htb/orion-application.xml

$OC4J_HOME/j2ee/home/applications/htb/META-INF/orion-application.xml

<property name="DBC_FILE_PATH" value="<enter absolute path here>" />



19. Shutdown the oc4j container:

cd $OC4J_HOME/j2ee/home

java -jar admin.jar ormi://<host_name>:<rmi port> <admin_username> <admin_password> -shutdown force
Note: The admin username and passwords are the same ones created during the installation of OC4J (Step 3).

20. Restart oc4j server - use the same steps as noted above.

21. Update the jserv.properties file to include the following lines between <#Begin customizations> and <#End customizations>:

wrapper.bin.parameters=-DAdaptorCatalog=$JAVA_TOP/oracle/apps/ctb/tools/bridge/catalog.xml
Note: Use physical path for $JAVA_TOP
wrapper.bin.parameters=-DClientMode=local


22. Run the below sqls for DQM Setup and Implementation.

cd $AR_TOP/patch/115/sql

sqlplus apps_username/apps_password @arhdqcgr.sql

sqlplus apps_username/apps_password @ARHDCTXS.pls ctxsys_username ctxsys_password apps_username

sqlplus apps_username/apps_password @arhdqcpr.sql ctxsys_username ctxsys_password apps_username



23. Restart the application services after disabling Maintenance Mode.


24. Modify the following profile options:

Profile Option Name = "FND: Framework Compatibility Mode"
Profile Option Application Name = Clinical Transaction Base (Short_name = CTB)
Profile Option Value = 11.5.10

Profile Option Name = "FND: Migrated to JRAD"
Profile Option Application Name = Clinical Transaction Base (Short_name = CTB)
Profile Option Value = Y



This completes the deployment of HTB on standalone OC4J.

Tuesday, October 21, 2008

Patch List for HTB v5.3

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 -- CPU Oct 05 : 2004-S291E
  • 5021981 -- CPUApr06: 5018818 for EBS 11.5.1-11.5.10RUP3
  • 5565583 -- BI
  • 5046719 -- CPUApr2006: 5046719 :TECHNOLOGY STACK COMPONENTS FOR CRITICAL PATCH UPDATE APR 2006
  • 5452096 -- RE-APPLYING ATG RUP4 4676589 FAILS ON FNDDECLDIAG.LDT, OAMDIAG.LDT OR EBIZSEC.LDT
  • 4676589 -- 11i.ATG_PF.H RUP4
  • 3865683 -- RELEASE 11.5.10 PRODUCTS NAME PATCH
  • 5487856 -- ONE-OFF REQUEST FOR BUG 5262377: HEALTHCARE BC4J PERFORMANCE FIXES
  • 4696889 -- CONCURRENCY ISSUE
  • 4919821 -- 1OFF:4279469:PF.F:11510:ISSUE WITH DATE TRANSFORMATION
  • 4407281 -- CONSOLIDATED HTML ADMIN UI BUGS ON TOP OF 11.5.10
  • 4585760 -- CTB AUTOCONFIG SCRIPTS HAVE INVALID SYNTAX FOR SOLARIS
  • 2267681 -- ADSPLICE FILES FOR ORACLE APPLICATIONS PRODUCT (CTB)
  • 2592520 -- ADSPLICE FILES FOR ORACLE APPLICATIONS PRODUCT (HCT)
  • 2955037 -- ADSPLICE FILES FOR ORACLE APPLICATIONS PRODUCT (HCP)
  • 4178883 -- Patch 11i.HC_PF.E
  • 5178799 -- HC_PF: 11i.HC_PF.E Rollup 3

In the next two posts, I shall talk about deploying HTB in a standalone OC4J installation and in a 10gAS installation.

Friday, October 17, 2008

Oracle HTB - A brief Introduction

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 Information Model (RIM).

As far as I know, HTB requires the following components (of Oracle):

  1. Oracle E-Business Suite (11i or R12)
  2. Oracle 10g AS or standalone OC4J (for deploying HTB)
  3. Oracle 10g AS SOA Suite 10.1.0.3.0 and above (for integrating HTB with third party applications using web services).

In my next post, I shall take up the procedure to install HTB in EBS 11i.

Developer 6i Patchset Version

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 metalink, which requires Adobe Flash Player 9.

Find the UNIX Process ID given a port number

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 PID given the port number '${port_num}'.............. \n"
echo

for i in `ls -l /proc | grep ${cur_user} | awk '{print $9}'`
do
pfiles $i | grep AF_INET | grep ${port_num}
if [ $? -eq 0 ]
then
echo "Port ${port_num} owned by pid $i" >> ${LOG_DIR}/pid_from_port.txt
else
continue;
fi
done

clear
if [ -s ${LOG_DIR}/pid_from_port.txt ]; then
echo
cat ${LOG_DIR}/pid_from_port.txt
echo
else
echo
echo "Can not find the process that owns the port ${port_num}"
echo
fi


# Remove the temporary file.
rm -f ${LOG_DIR}/pid_from_port.txt
______________________________________________________

Wednesday, October 15, 2008

Customising SQL Prompt in pre-10g Oracle Homes

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/sqlplus/admin or under any directory that is defined in the SQLPATH, with the following contents:

___________________________________________________________

login.sql

col prom new_value prom
set termout off
define prom='NOT CONNECTED'
select upper(sys_context('USERENV','SESSION_USER'))|| '@'|| upper(sys_context('USERENV','DB_NAME')) prom

from dual;
set sqlprompt "&prom> "
set termout on


connect.sql

connect &1
@login.sql

___________________________________________________________


3. Whenever one wants to switch between users, use the command @connect instead of connect. If we do not use @connect, then the new user would not be displayed in the sqlprompt. This is as illustrated below:

___________________________________________________________

$ sqlplus apps

SQL*Plus: Release 8.0.6.0.0 - Production on Tue Oct 14 07:35:38 2008

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


Enter password:


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production


APPS@ORCL> @connect system

Enter password:
Connected.

SYSTEM@ORCL> sho user

USER is "SYSTEM"

SYSTEM@ORCL> conn apps

Enter password:
Connected.

SYSTEM@ORCL> sho user

USER is "APPS"

___________________________________________________________