Oracle ERP DBA

It's been a long time since I posted something. So, here's something worthwhile. Thanks to my colleague Kevin who devised this method! Requirement A reporting database to be refreshed daily from the UAT instance. The reporting database should be available during the daytime and the refresh can happen during the night. Solution 1. Initially, build the reporting database as a standby database. 2. Once the Managed Recovery process (MRP) is initiated and the standby is in sync with the primary database, cancel the MRP. SQL> alter database recover managed standby database cancel; 3. Enable flashback in the standby database. Note that the DB should be in mount state for this. SQL> startup mount force; SQL> alter database flashback on; 4. create guaranteed restore point. SQL> drop restore point before_open; SQL> create restore point before_open guarantee flashback database; 5. Activate the standby database. SQL> alter database activate standby database; SQL> start

I am sure everyone knows this but just to emphasise the point (and ease the pain somewhat), Classic MetaLink Retires November 6, 2009 . Extract of the text of email from Oracle Support: During the weekend of November 6 – 8, 2009, My Oracle Support, Oracle's next-generation customer support platform, will be upgraded and Classic MetaLink will be retired. Moreover (and this is the most painful part especially for Apps DBAs), the ftp to the patch site will also retire on 06-Nov-2009. :( Below is the extract of the message displayed on updates.oracle.com ftp site: 230- This service will be retired on November 06, 2009 and "My Oracle Support" 230- will be the only interface for downloading the patches and updates. To 230- search and download patches from "My Oracle Support" users are required 230- to login to https://metalink.oracle.com and then go to "Patches & Updates" 230- tab. Though I do NOT like it, I suppose I have to force myself to use wg

Given a request id, how does one find out the program name and the manager running the request? Most of the Metalink notes and blogs provide the answer to this question but for one aspect. What is that? Read on to find out. What the SQL queries available on the web and Metalink lack is that they provide the concurrent program name for a single request given the request id. What about for a request set? Do they provide the answer? No, they don't. I have tried here to cover this aspect too. So, here goes. Note : This query works for all request ids, not only for request ids for request sets. select request_id,fcpv.CONCURRENT_PROGRAM_NAME "PROGRAM_NAME", nvl (fcr.description,fcpv.user_concurrent_program_name) "USER_PROGRAM_NAME", decode(fcpv.REQUEST_SET_FLAG,'N','SINGLE_REQUEST','Y','REQUEST_SET') "PROGRAM_TYPE", fcqv.CONCURRENT_QUEUE_NAME, fcqv.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_requests fcr, fnd_concurrent_p

In order to get the UNIX pipe character ("|") printed in blogger, do the following: Always use the "Edit Html" tab for posts containing the pipe character and NOT the Compose tab. The ASCII value for the pipe character is "& # 124 ;" (I am delibrately using whitespace in betwen the ASCII value. Please remove them before using). Thanks for Greg Houston's blog and this thread for pointing me in the right direction.

A quick question before getting started with today's subject. How does one retrieve the url of an instance? Suppose you were asked to work (or login to the front end) on an instance of which you know only the server names, how do you find the login URL? There are 2 methods (of course, there might be more but I've listed the ones that I use the most) to retrieve the login URL information: 1. Login to sqlplus as apps and query the table icx_parameters for the home_url. 2. Better still and much faster than the above method would be to issue the below command. The output would be nothing but the value for the env variable FORMS60_MAPPING. $ env | grep http I prefer the 2nd method over the 1st. Anyway, to my surprise and dismay, I found out that R12 does NOT set the above env variable. So, the quick recourse to getting the login URL is ruled out or is it? I have written a shell script to get/set the env variable. You can access it here . Let me know if you notice any errors or have

I have tried to collate commands to find the number of CPUs and total physical memory (RAM) size in all common flavours of UNIX in one blog entry. This certainly is not an exhaustive list and will be modified as and when I find new commands. You are also more than welcome to add to this list. Number of CPUs Sun SPARC Solaris 1. prtconf | grep cpu | wc -l 2. psrinfo | wc -l 3. psrinfo -v | grep "Status of" | wc -l 4. psrinfo -vp 5. prtdiag | grep "on-line" | wc -l 6. mpstat | grep -vi cpu | wc -l Linux 1. grep "processor"/proc/cpuinfo | wc -l HP-UX 1. ioscan -kfnC processor | grep "processor" | wc -l 2. sar -M 1 | awk 'END {print NR-5}' 3. sar -Mu 1 1 | awk 'END {print NR-5}' 4. ioscan -kf | grep -c processor 5. cat /var/adm/syslog/syslog.log | grep processor | grep "vmunix" | wc -l AIX 1. lscfg -vp | grep proc | wc -l 2. lparstat -i | grep "Online Virtual CPUs" | cut -d ':' -f2 3. lsdev -C | grep Proces

Issue “Choose Connection” is NOT seen in Discoverer Plus and Discoverer Viewer screen after installation. Symptom Installed Discoverer 10g as an OracleBI standalone installation (i.e., without associating with an OracleAS infrastructure). Associated the standalone Discoverer to Oracle Apps Database. Users are unable to see Choose Connection in the Discoverer Plus and Viewer login screen. Cause Discoverer has not been associated with an infrastructure. This happens when Discoverer 10g is installed as a Standalone without associating it with an OracleAS infrastructure. Quoting from the documentation: “An OracleBI standalone installation is not associated with an OracleAS Infrastructure, and therefore has a limited number of components available. .. .. If Discoverer is not associated with an OracleAS Infrastructure, Discoverer connections are not available to end users. For more information about associating Discoverer with an Oracle Infrastructure install, see Chapter 2, "About Orac

Long time no blog! The reason being that I was busy performing this Discoverer 10g rehost. Without wasting much of your time, let me get into the details. Background Recently, we rehosted (i.e., moved) our production instance to a new set of production servers. Discoverer 10g is also a part of our prodcution implementation. Discoverer resided on the old production servers. Requirement Move Discoverer 10g from the old production servers to the new ones (a.k.a rehost!). Methodology After scrutinizing all the available Metalink docs, I found out that no Metalink doc existed for my requirement! I had to chart my own course here (not that no one had done this before but lack of documentation - both on the web and Metalink, was a bit of an impediment). After burrowing through all the available documentation, I surmised that I had two methods to achieve my goal: 1. Reinstallation of Discoverer 10g (including OID) 2. Cloning of Discoverer 10g ORACLE_HOME. Steps involved in both the methods is

In my previous post, I had talked about extracting controlfiles, datafiles and archivelogs from RMAN backupsets using the package dbms_backup_restore. In this post, I would like to throw light upon using RMAN as an alternate method that could be employed to extract controlfiles from RMAN backupsets. This method uses the RMAN command "restore controlfile from '<FULL_PATH_TO_CONTROLFILE_BACKUP>'". The RMAN method can be used in 3 scenarios. So, make sure that at least any one of the backups is indeed available. Else, you are DOOMED! I. the controlfile autobackup is turned on in RMAN. In this case the file format would be "c<DBID>-<DATE>-<COUNT>". It is turned on using the command: configure controlfile autobackup on; configure controlfile autobackup format for device type disk to '/opt/apps/vision/backup/%F'; II. the controlfile snapshot backup is configured in RMAN. It is turned on using the command: configure snapshot control

The other day, one of my colleagues posed me this interesting and thought-provoking question/scenario: "Suppose, you were to lose both your production and your RMAN catalog databases irrevertably. You do have a full/incremental RMAN backup, however. How are you going to recover?" How indeed? I scratched my head and shone a torch upon the dark recesses of my memory to come up with an answer. The light (of the torch) did indeed reveal something - that is that a note existed in Metalink which list the method that could be used to delve into a RMAN backup set to extract the controlfile, the datafiles and also the archivelogs and Voilà, I hit the nail right on the head. I did some more digging and found a bunch of "Notes" in Metalink that pointed me to the eventual answer to the above question. The Notes that I am referring to are listed below. A summary of the procedure to be followed in such scenarios is also provided. All the notes mentioned use the package dbms_backu

This post is for the automation fanatics and enthusiasts. Of course, the others aren't excluded! The defaultsfile option that can be used with adutilities is a handy option to save time during patching or other maintenance activities. Though the defaultsfile gets a mention by almost everyone in the context of reducing downtime, the method of creating it is not, unfortunately. In this post, I'd like to share this using an example - compiling apps schema using adadmin. Note: The full explanation of creating a defaultsfile is mentioned in Chapter 2 (Maintaining Your System) of the Oracle Applications Maintenance Procedures . 1. Run adadmin command as below: adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/<DEFAULTS_FILE_NAME> e.g.: adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adadmin_cmpl_apps_schema.txt The above command searches for a file with name adadmin_cmpl_apps_schema.txt in $APPL_TOP/admin/$TWO_TASK directory. If it exists, it uses the values provided in that file.

In the last post, I had talked about the pitfalls in configuring PCP in non-RAC instances. I have figured out (is it really the solution? - only time will tell!) workarounds for the 2 pitfalls that I had listed. 1. How would the VNC server failover when using PCP with non-RAC? Ans: The best bet would be to start the vnc server using the virtual hostname instead of the physical hostname (thanks to my colleague Mansoor who suggested it). Once the VNC is started using the virtual hostname, set the display variable to this value and run autoconfig so that the changes are reflected in the concurrent manager and the report server startup scripts. So, when the VIP (and hence, the virtual host) fails over, the VNC would still be running. This solution needs to be tested thoroughly though. Below is the command that I can use: $ vncserver -name <VIRTUAL_HOSTNAME>:<PORT_NUMBER> 2. The web tier 806_ORACLE_HOME tnsnames.ora can be modified to include the FNDFS_<PHYSICAL_HOSTNAME>

It was a wonderful surprise when I stumbled upon Metalink Note ID: 743716.1 , which details steps for configuring PCP in non-RAC instances. This is very useful while using hardware or software clustering and not Oracle clustering (RAC). We are using Veritas clustering (software clustering) for server failover. We were recently testing server failover and there are two things that I am unable to figure out as of now and which necessitates manual intervention rendering the whole failover mechanism manual: 1. How would the VNC server failover when using PCP with non-RAC? Since, the hostname changes when the failover happens, the display variable would still be pointing to the failed server and not the failover server. The work around to the above problem is obviously to reset the display variable and run autoconfig, which would mean that the failover mechanism is no longer automatic. 2. As soon as the failover happens, we need to make a manual entry in the Web Tier $TNS_ADMIN/tnsnames.ora

I have written a sql query which would output the usage percent of all the tablespaces in a given database. Although lots of tablespace usage queries are available, most of them, if not all, utilise dba_free_space view as the tool of measurement. It is well known that this view is inadequate when datafiles have autoextend feature turned on. I have tried to address this inadequacy in my query. Caveat : This query is not optimised for performance. Hence, you might observe slow performance. set linesize 100 set pagesize 10000 set feedback off verify off col tablespace_name format a18 col num_files format 999 col total_space format 9999999.99 col free_space format 9999999.99 col used_percent format 999.99 select /*+ parallel(tbs) */ tbs.tablespace_name, count(tbs.file_id) num_files, round(sum(TOTAL_SPACE),2) TOTAL_SPACE_MB, sum(FREE_SPACE) FREE_SPACE_MB, round(((sum(tbs.USED_SPACE)/sum(tbs.TOTAL_SPACE)) * 100),2) USED_PERCENT from (select ddf.file_id file_id, ddf.file_name, ddf.tablespace

The Environment Apps Version : 11.5.10.2 Jinitiator Version : 1.3.1.26 Internet Explorer Version : 6 The Issue One of our end users was receiving a very strange error while using jinitiator. The Internet explorer window would crash (close by itself) after creating a file HS_ERR_PID<####>.log on the desktop, where #### stands for the Process ID of the f60webmx session. This error is intermittent and not easily reproducible. Moreover, the user would have to redo his/her work and hence this issue was turning out to be very vexing indeed. The error log is as below: An unexpected exception has been detected in native code outside the VM. Unexpected Signal : EXCEPTION_ACCESS_VIOLATION occurred at PC=0x6D043D1AFunction name=Java_sun_java2d_loops_DefaultComponent_IntIsomorphicCopy Library=C:\Program Files\Oracle\JInitiator 1.3.1.26\bin\awt.dll Current Java thread: at sun.java2d.loops.DefaultComponent.IntIsomorphicCopy(Native Method) at sun.java2d.loops.IntRgbToIntRgb.OpaqueBlit(Unknown

Today morning, we (the DBAs) were faced with a strange issue. On Saturday (the 14th of February - (St.) Valentine's day!!), the UNIX boxes hosting our critical test instance went down for scheduled maintenance without any prior information. So, we could not bring down our databases and applications. As soon as the unix box came up, we encountered this issue. Let me briefly describe the environment, the issue, the analysis performed and of course, the solution to this most vexing problem. Environment : Apps Version : 11.5.10.2 with ATG RUP 5 DB Version : 10.2.0.3 Architecture : Two Node instance with database + admin on one tier and web server on another. PS : Another instance (development) exists on the same server with the same configuration, which was working fine. Issue : When trying to access the login page of Oracle Apps, received the below error. Even OAM login page was throwing the same error. Request URI: /OA_HTML/AppsLocalLogin.jsp Exception: java.lang.ExceptionInIniti

When passing a variable in unix, the general convention is to use $variable . Although this does suffice in most cases but in some rare cases, it gives connotations totally different than what was originally intended, as shown in the below example. The requirement Copy all files ending with ver to ver_old. For example, suppose I have a file called formver, I would like to copy it to formver_old. The code below is the code that I wrote to achieve the above requirement. for i in `ls *ver` do cp -p $i $i_old done The pitfall A cursory glance at the code indicates that it should run smoothly like a river. But it doent' and herein lies the catch. Let's have a look at the error and figure out what exactly is wrong with the above code. __________________________________________________________________ $ for i in `ls *ver` > do > cp -p $i $i_old > done cp: Insufficient arguments (1) Usage: cp [-f] [-i] [-p] [-@] f1 f2 cp [-f] [-i] [-p] [-@] f1 ... fn d1 cp -r-R [-H-L-P] [-f] [

Apps Configuration Oracle Apps Version ==> 11.5.9 Node Type ==> Multi-node RAC ==> No OS & version ==> Sun SPARC Solaris (64-Bit) 10 (5.10) Issue Recently (last week, to be precise), I was researching an "Internal Server error" while opening Installed Base. The error log had error "client denied by server configuration" (not very helpful, I would say). The mod_jserv.log was much more informative and in fact, quite specific. Refer the note marked in red below. __________________________________________________________________ [28/01/2009 06:39:44:223] (ERROR) ajp12: Servlet Error: OracleJSP: oracle.jsp.provider.JspCompileException: <H3>Errors compiling$COMMON_TOP/_pages/_oa__html//_csiSwitchRespMain.java< /H3><TABLE BORDER=1 WIDTH=100%><TR><TH ALIGN=CENTER>Line #</TH><TH ALIGN=CENTER>Error</TH></TR><TR><TD WIDTH=7% VALIGN=TOP><P ALIGN=CENTER>990</TD><TD> as of release

Listed below is the ready reckoner for finding out the kernel bit information for Linux, Solaris, HP-UX and IBM AIX, which are the most commonly used OS for Oracle Apps. Linux getconf LONG_BIT or uname -m (works for x86-64 bit servers. Not tested it in 32 bit servers) eg: $ getconf LONG_BIT 64 $ uname -m x86_64 $ Sun SPARC Solaris isainfo -kv eg: $ isainfo -kv 64-bit sparcv9 kernel modules $ HP-UX getconf KERNEL_BITS eg: $ getconf KERNEL_BITS 64 $ IBM AIX bootinfo -K Not tested as I currently do not have any AIX servers to test on! Note 1 : To find whether a given executable is 32-bit or 64-bit, use the command file <executable_name> eg: $ file oracle oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped $ file tnslsnr tnslsnr: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped $ file sqlplus sqlplus: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped Note 2 : You can install a 32-bit executab

Case Facts : Database Version ==> 10.2.0.3.0 Oracle Apps Version ==> 11.5.10.2 Node Type ==> Multi-node RAC ==> No OS & version ==> Sun SPARC Solaris (64-Bit) 10 (5.10) Symptom ==> Database crashes with ORA-07445 Case Description : While trying to bring up an Oracle Apps instance, I found that the database was crashing with ORA-07445 errors (as below) as soon as I brought up the concurrent managers. _____________________________________________________________ Mon Feb 2 00:26:24 2009 Errors in file $ORACLE_HOME/admin/[CONTEXT_NAME]/udump/cpek_ora_6085.trc: ORA-07445: exception encountered: core dump [lfilic()+328] [SIGSEGV] [Address not mapped to object] [0x000000040] [] [] ORA-29282: invalid file ID Mon Feb 2 00:26:27 2009 Errors in file $ORACLE_HOME/admin/[CONTEXT_NAME]/udump/cpek_ora_5512.trc: ORA-07445: exception encountered: core dump [lfilic()+328] [SIGSEGV] [Address not mapped to object] [0x000000040] [] [] Mon Feb 2 00:26:34 2009 Errors in file $ORACLE_HOM

I was trying to find something in the CONTEXT_FILE when I happened to notice the AF_CLASSPATH env variable (not that I haven't noticed it earlier). But this time, it set me thinking as to what might its purpose be since we already had a CLASSPATH variable and its value seemed to be the same as the AF_CLASSPATH variable. I found 3 variables starting with AF: AF_JRE_TOP AF_CLASSPATH AFJVAPRG. I wanted to find out what they were and why they were used. This is the explanation provided in Metalink Note 412709.1 (Oracle Workflow Documentation Updates for 11i.ATG_PF.H.delta.5 (RUP 5)) AFJVAPRG - The location of the JDK or JRE executable for the concurrent processing tier. AF_CLASSPATH - The classpath for the concurrent processing tier. Also, Note 373386.1 has this to say about AF_CLASSPATH. The AF_CLASSPATH variable is used by JAVA concurrent programs and must use literal (full) path values. By combining the above two notes, we can perhaps safely say that the AF variables are set for l