Friday, November 21, 2008

R12 install and post install Issues in 64 Bit Solaris

Recently, I installed R12 on Sun SPARC Solaris 64 Bit and I faced a few issues that I have listed below.



Issue 1

Installation errors with the below error in .log.

Statusstring Configuring Database
..
Log file located at ../ApplyDatabase_.log
..
RC-50004: Fatal: Error occurred in ApplyDatabase:
Control file creation failed
Cannot execute configure of database using RapidClone
RW-50010: Error: - script has returned an error: 1
RW-50004: Error code received when running external process.

Error in ApplyDatabase_.log


WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Mon Nov 17 01:05:46 2008
Errors in file /opt/apps/Release12/db/tech_st/10.2.0/admin/R12VIS_suomt06k/udump/r12vis_ora_3206.trc:
ORA-01565: error in identifying file '/opt/apps/Release12/db/apps_st/data/sys5.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 25: Inappropriate ioctl for device

Cause
ST Patch 5752399 was not included in 12.0.4 media as it was not detected before the media was cut. Likelyhood of occurance is limited to specific hardware setup.

Solution
Apply Patch 5752399 while installation is underway.
1. Wipe out the failed installation and restart a new one.
2. Closely monitor the progress to catch the time it starts copying the datafiles.
3. While datafiles are being copied go ahead and apply patch 5752399 with following steps before rapidinstall calls the adcrdb.sh.
1. cd /rdbms/lib
2. mv config.s config.s.before5752399
3. cp config.s.ouibak.3 config.s
4. Apply the patch 5752399
5. Try SQL*Plus and make sure it doesn't fail with ORA-3113
4. If above steps are completed before adcrdb.sh, install will continue successfully, otherwise repeat the steps but faster.

References: NOTE:565966.1


Issue 2

Unzip of appltop failed during install with the below error.

Unzip Failed:
Cannot execute install of appltop

Cause

APPL_TOP zip files were not unzipped correctly.

Solution

Unzip all the APPL_TOP zip files once more. That did not work unfortunately. Then, I downloaded the zip file which has an issue once more from edelivery and proceeded with the installation.

References: NA


Issue 3

HTTP services weren't started after install. Output of opmn log file (/opt/apps/Release12/inst/apps/R12VIS_suomt06k/logs/appl/admin/log/adopmnctl.txt) as below.

11/17/08-07:18:57 :: adopmnctl.sh: Starting Oracle Process Manager (OPMN)
libopmnoraclenls.so: unable to load ÿ¿òPÿ¿ò\206ÿ¿ò\211ÿ¿ò\214
main: NLS initialization failed!!
libopmnoraclenls.so: unable to load ÿ¿òHÿ¿ò~
main: NLS initialization failed!!
libopmnoraclenls.so: unable to load ÿ¿ñÈÿ¿ñþÿ¿ò^Aÿ¿ò^D
main: NLS initialization failed!!
opmnctl: opmn start failed.
11/17/08-07:18:58 :: adopmnctl.sh: exiting with status 2

Cause

This issue is due to the libclntsh.so.10.1 library not being present under the lib32 folder.

Solution

1) Simply copy the file libclntsh.so.10.1 file from a working
environment to non-working environment into the lib32 directory.
But, this is applicable only to the same version of OS.
OR
2) You can generate libclntsh.so.10.1 by executing the following command
$10.1.3_OH/bin/genclntsh -32
3) Setting the correct oracle home to 10.1.3 then running that ./bin/genclntsh helps:

If you run with the -32 option it updates the ./lib32 version of libclntsh.so.10.1
If you run without the -32 option it updates the ./lib version of libclntsh.so.10.1
( and similarly if it was set to 10.1.2, libclntsh.so.10.1 will be created in 10.1.2/lib )
So it is very important to set OH to 10.1.3.

References: NOTE:413109.1


Issue 4

When attempting to open any forms, the following error occurs:

ERROR
FRM-40833: Could not completely load the dynamic user exit libraries.
User exit FND did not execute.

Cause

Relink generated a corrupted $FND_TOP/bin/fndfmxit.so due to missing $GMA_TOP/lib/libgma.a.

Solution

1. Copy the library libgma.a from a backup taken after installation of RUP3 patch to $GMA_TOP/lib
2. Relink fndfmxit.so by running:
adrelink.sh force=y "fnd fndfmxit.so"

References: NOTE:554927.1


Issue 5

Concurrent Managers weren't coming up. The error in the ICM log file ($APPLCSF/$APPLLOG/_.mgr)

ld.so.1: FNDLIBR: fatal: /opt/apps/Release12/apps/tech_st/10.1.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64
Killed
The R12VIS_1117@R12VIS internal concurrent manager has terminated with status 137 - giving up.

Cause

Using a 64 bit executable and trying to link to 32 bit libraries.

Solution

1) You can generate libclntsh.so.10.1 under $10.1.2_ORACLE_HOME/lib32 by executing the following command
$10.1.2_OH/bin/genclntsh -32

References: NOTE:604084.1, NOTE:413109.1

Thursday, November 6, 2008

Check JDBC Connection String using java

Sometimes, I have faced a situation wherein the tnsping to the database is working but the JDBC connection fails. So, in order to check the JDBC connection string, one has to use a java program, which unfortunately isn't my forte.

Thankfully, my friend, Padmaraj, who is a SME on java gave me a small piece of code which works wonderfully well.

Note: Save the below java program as ReadCharacter.java, else the program won't work.


// Program to check the JDBC Connection string.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ReadCharacter {
public static void main(String[] args) {
Connection dbConnection;
Statement stmt;
String sqlStmt;

// For Oracle 9.2
String thin = "jdbc:oracle:thin:@[db_host]:[db_port]:[oracle_sid]";
String name = "[username]";
String pass = "[password]";
String table = "V$INSTANCE";
try{
pt("Getting Connection....");
Class.forName("oracle.jdbc.driver.OracleDriver");
dbConnection = DriverManager.getConnection(thin, name, pass);
pl(" Got");
pt("Creating statment....");
sqlStmt = "SELECT instance_name, host_name from " + table;
stmt = dbConnection.createStatement();
pl(" Got");
pt("Excuting statment (" + sqlStmt + ")....");
ResultSet rs = stmt.executeQuery(sqlStmt);
pl(" Excuted");
while(rs.next()) {
String n = rs.getString("instance_name");
String a = rs.getString(2);
pl("Instance_Name = " + n + ", Host_Name = " + a );
}
pt("Closing Result set...");
rs.close();
pl(" Closed");
pt("Closed statment....");
stmt.close();
pl(" Closed");
pt("Closing Connection....");
dbConnection.close();
pl(" Closed");
} catch (Exception e) {
e.printStackTrace();
}
}
static void pt(String s) {
System.out.print(s);
}
static void pl(String s) {
System.out.println(s);

}
}


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"

___________________________________________________________

Tuesday, September 23, 2008

Interesting Oracle Applications (EBS) Interview Questions

Below are some interesting interview questions that I have faced over the years.

  1. What is the difference between a concurrent manager and a cron job?


  2. Can one patch a CRS Oracle home with the CRS services up?


  3. Suppose, there are 2 nodes node1 and node2 with CRS cluster and one wants to patch node1. Can it be done with node2 being up?


  4. Suppose one fires a select statement and the data does not in the library cache, then the data, obviously, has to be fetched from the data files. Which background process is used for this purpose?


  5. What are the sizing requirements for say, a 100 concurrent users Oracle Apps Installation?


  6. What is the difference between a shared appl_top and a staged appl_top?



Monday, July 28, 2008

Solution to "End Program - WMS Idle"

Though this topic is unrelated any Apps DBA topics, I found it necessary to share the solution so that everyone benifits out of it!

Issue:


I used to receive the below error message whenever I tried to shutdown windows XP. A rather vexing problem, no doubt.




Cause:

This message appears because when windows is trying to shutdown and end all the running processes, the WMS idle process keeps working. Thus an end-program message appears. The two most common reasons for this are:

  1. Scout service run by Nero 7
  2. Microsoft Office Communicator 2005

Solution:

The fix is simple, disable the Nero 7 scout service if it is enabled.

For this, go to Start > All Programs > Nero > Tools > Nero Scout.

On this screen ,Uncheck Enable the Nero scout.



This will fix the issue for most of the users, for the rest of you who are not using Nero are most probably getting this error because of Microsoft Office Communicator 2005. For that you need to either manually exit the communicator 2005 from the system tray before you shutdown windows, or also you can use this hot-fix provided by Microsoft for WMI Idle error message.


References: http://www.troublefixers.com/end-program-wms-idle-message-on-windows-shutdown/

Tuesday, July 22, 2008

Version of mod_security with EBS 11.5.10.2

Though it was a bit tough to find out the version of Mod_security that is shipped with E-Business Suite 11.5.10.2, I finally managed to do it, thanks to Metalink Fora!

The version that comes with eBS 11.5.10.2 is 1.8.4. Below is how you find out the version of mod_security shipped with your version of eBS.

$ strings $IAS_ORACLE_HOME/Apache/Apache/libexec/mod_security.so | grep mod_security/

Introduction to Mod_Security

What Is ModSecurity?

ModSecurity is a web application firewall that can work either embedded or as a reverse proxy. It provides protection from a range of attacks against web applications and allows for HTTP traffic monitoring, logging and real-time analysis. [7]

mod_security is an Apache module (for Apache 1 and 2) that provides intrusion detection and prevention for web applications. It aims at shielding web applications from known and unknown attacks, such as SQL injection attacks, cross-site scripting, path traversal attacks, etc. [6]

mod_security is an Apache module designed as a sort of web application firewall. It’s most useful for preventing SQL Injection and Cross Site Scripting (or XSS). [2]

It is also an open source project that aims to make the web application firewall technology available to everyone. [7]


SQL Injection

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.


Cross-site scripting (XSS)

Cross-site scripting (XSS) is a type of computer security vulnerability typically found in web applications which allow code injection by malicious web users into the web pages viewed by other users. Examples of such code include HTML code and client-side scripts. An exploited cross-site scripting vulnerability can be used by attackers to bypass access controls such as the same origin policy. Vulnerabilities of this kind have been exploited to craft powerful phishing attacks and browser exploits. As of 2007, cross-site scripting carried out on websites were roughly 80% of all documented security vulnerabilities. Often during an attack "everything looks fine" to the end-user who may be exposed to unauthorized access, theft of sensitive data and financial loss.


References:

Monday, July 21, 2008

Find the size of an Oracle database

A very common question in any interview is "How do you find the size of a database?" This question can be answered in numerous ways. For e.g., some might say that the size of the database is the sum of the size of each datafile. Some might add the size of the tempfiles to the afore mentioned answer.

However, according to me, the size of the database can be calculated as below:

Database size = size of datafiles + size of tempfiles + size of redologs + size of controlfiles

The above formula can be put to use using the below mentioned SQL.

set serveroutput on
declare
DB_SIZE number;
dfsize number;
tfsize number;
rlsize number;
cfsize number;
begin
select sum(bytes)/1024/1024 "DATA_FILE_SIZE" into dfsize from dba_data_files;
select sum(bytes)/1024/1024 "TEMP_FILE_SIZE" into tfsize from dba_temp_files;
select sum(bytes)/1024/1024 "REDO_LOG_SIZE" into rlsize from v$log;
select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE" into cfsize from sys.v_$controlfile where rownum = 1;
DB_SIZE:=round((dfsize+tfsize+rlsize+cfsize)/1024,3);
dbms_output.put_line('-------------------------------------');
dbms_output.put_line('The Database Size is ' || DB_SIZE || ' Gigabytes');
dbms_output.put_line('-------------------------------------');
end;
/



Eg:

SQL > set serveroutput on
SQL > declare

2 DB_SIZE number;
3 dfsize number;
4 tfsize number;
5 rlsize number;
6 cfsize number;
7 begin
8 select sum(bytes)/1024/1024 "DATA_FILE_SIZE" into dfsize from dba_data_files;
9 select sum(bytes)/1024/1024 "TEMP_FILE_SIZE" into tfsize from dba_temp_files;
10 select sum(bytes)/1024/1024 "REDO_LOG_SIZE" into rlsize from v$log;
11 select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE" into cfsize from sys.v_$controlfile where rownum = 1;
12 DB_SIZE:=round((dfsize+tfsize+rlsize+cfsize)/1024,3);
13 dbms_output.put_line('-------------------------------------');
14 dbms_output.put_line('The Database Size is ' || DB_SIZE || ' Gigabytes');
15 dbms_output.put_line('-------------------------------------');
16 end;
17 /
-------------------------------------
The Database Size is 88.259 Gigabytes
-------------------------------------

PL/SQL procedure successfully completed.

Find the size of the control files

Below is the SQL statement to find the size of the control files.


select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile where rownum = 1;

OR

select distinct (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile;


Eg:

SQL > select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile where rownum = 1;

CONTROL_FILE_SIZE (MB)
----------------------
18.515625


SQL > select distinct (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile;


CONTROL_FILE_SIZE (MB)
----------------------
18.515625

SQL > select distinct (BLOCK_SIZE * (1 + FILE_SIZE_BLKS)) "CONTROL_FILE_SIZE (BYTES)" from sys.v_$controlfile;

CONTROL_FILE_SIZE (BYTES)
-------------------------
19415040


$ ls -slrt [path_to_Controlfile]/cntrl0*
18984 -rw-rw---- 1 oracle dba 19415040 Jul 21 02:56
[path_to_Controlfile]/cntrl03.dbf
18984 -rw-rw---- 1 oracle dba 19415040 Jul 21 02:56
[path_to_Controlfile]/cntrl02.dbf
18984 -rw-rw---- 1 oracle dba 19415040 Jul 21 02:56
[path_to_Controlfile]/cntrl01.dbf


Note: 1 block needs to be added to the file_size_blks to allow for the file header.

Thursday, July 3, 2008

Customising SQL Prompt in 10g Database/Oracle Home

Many a time, DBAs and Developers tend to run scripts, accidentally, in instances that they do not actually intend to. In extreme cases, this might result in disastrous consequences, sometimes even leading to recovering/restoring the database from a backup.


This mistake can easily be avoided if the sql prompt displays the username and/or the SID in lieu of just "SQL>".


Whenever sqlplus is invoked, 2 files are executed: glogin.sql and login.sql (if it exists) in the order mentioned. These 2 files are located under $ORACLE_HOME/sqlplus/admin.



Modify the file glogin.sql and add the below line:


set sqlprompt "_user'@'_connect_identifier > "


The net result of this is that the sqlprompt will appear as below whenever you login to sqlplus:


system@ORCL>


where system is the username and ORCL is the SID.


Note: One drawback of this method, if it may be called so, is that whenever one logs in to sqlplus using /nolog option, only the '@' character is displayed as the connection to the database is yet to be established. As soon as the connection is established, the username and SID are again populated in the sqlprompt. Incidentally, this is a new feature in 10g, wherein the glogin.sql and login.sql files are executed after the connect command.