Oracle ERP DBA

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: Inappr

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 = "[

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

Below are some interesting interview questions that I have faced over the years. What is the difference between a concurrent manager and a cron job? Can one patch a CRS Oracle home with the CRS services up? 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? 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? What are the sizing requirements for say, a 100 concurrent users Oracle Apps Installation? What is the difference between a shared appl_top and a staged appl_top?

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: Scout service run by Nero 7 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 s

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/

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 pr

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"

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_

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