Monday, November 23, 2009

Build a Reporting Database using standby and flashback


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> startup force;

6. In the evening/night, flashback the database to the guaranteed restore point i.e., standby database and resync it with the primary.


SQL> startup mount force;



SQL> flashback database to before_open;



SQL> alter database convert to physical standby;



SQL> startup mount force;



SQL> alter database recover managed standby database disconnect from session;

7. The next day monring, verify that the recovery has indeed completed, by using the following sql:



SQL> set pages 1000



SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;



SQL> select max(sequence#) from v$archived_log; -- execute on both primary and standby.

8. Repeat the Steps 4 & 5 - to open the reporting database to the users.


9. In the evening/night, repeat Step 6. - to resync the reporting database.


References


1. Introduction to Restore points/flashback databases.

2. Introduction to data guard.

3. Business Continuity for Oracle Applications Release 11i, Database Releases 9i and 10g - Note: 216212.1


Monday, October 12, 2009

WGET Syntax for downloading patches from Metalink

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 wget to download patches directly on to the server, instead of downloading them to my local machine and then transferring them to the server (a much more painful process).

So, as a heads up, I would like to share with you the syntax for using wget.

Note: The below syntax worked for Solaris 10 and might not work for other flavours of UNIX since the default installation directory may be different.




/usr/sfw/bin/wget --http-user=<Metalink Username> --http-passwd=<Metalink Password> --output-document=<Name of patch>
"<URL>"

1. Get the URL in the following way:

a. Login to metalink and query up the patch.
b. Right click on the download button and click on "Copy Shortcut" (IE).

2. The name of the patch would be a subset of the URL.


eg:

/usr/sfw/bin/wget --http-user=<Metalink Username> --http-passwd=<Metalink Password> --output-document=p8618053_11i_GENERIC.zip "http://updates.oracle.com/ARULink/Download/process_form/p8618053_11i_GENERIC.zip?file_id=27640717&aru=11477117&userid=ml-693674.993&email=praveen.krishnamurthy@qwest.com&patch_password=&patch_file=p8618053_11i_GENERIC.zip"


Hope this is helpful in the painful transition from the updates.oracle.com ftp site to the Flash Player based new and "improved" Metalink.

Friday, October 9, 2009

Find Request Set Name, Conc Manager given a request ID

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_programs_vl fcpv, fnd_concurrent_processes fcp, fnd_concurrent_queues_vl fcqv
where
fcr.concurrent_program_id = fcpv.concurrent_program_id (+)
and fcr.controlling_manager = fcp.CONCURRENT_PROCESS_ID
and fcp.concurrent_queue_id = fcqv.concurrent_queue_id
and fcr.request_id = &request_id;

Tuesday, September 22, 2009

Print UNIX pipe character ("|") in Blogger

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.

Tuesday, September 8, 2009

Get R12 Login URL from Command Prompt

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 any comments/suggestions.

Wednesday, September 2, 2009

Find the number of CPUs and RAM size in UNIX

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 Process | wc -l


4. lsdev -Ccprocessor | grep -ci available




Total Physical Memory (RAM) Size


Solaris


1. prtdiag | grep "Memory size" | cut -d ':' -f2

2. prtconf | grep "Memory size" | cut -d ':' -f2


Linux

1. echo "$(free -mt | grep "Mem:" | awk '{print $2}' | tr -d [:space:]) MB"

or

free -mt | grep "Mem:" | awk '{print $2}' | tr -d [:space:];echo " MB"

2. grep MemTotal /proc/meminfo | cut -d ':' -f2


HP-UX

1. echo "selclass qualifier memory;info;wait;infolog" | /usr/sbin/cstm | grep "Total Configured Memory" | cut -d ':' -f2

2. echo "$(grep Physical /var/adm/syslog/syslog.log | awk '{print $7}') KB"


AIX

1. echo "$(lsattr -El mem0 | grep "Total" | awk '{print $2}') MB"

2. lsattr -El mem0 | grep "Total" | awk '{print $2}'; echo " MB"



Friday, August 28, 2009

Choose Connection Not Visible in Discoverer 10g after install

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 Oracle Business Intelligence installations and OracleAS Infrastructures".”




Resolution



2.2 How to associate an OracleBI installation with an OracleAS Infrastructure


If you install an OracleBI standalone installation, you might want to associate it with an Oracle Application Server Infrastructure in order to deploy Discoverer with other OracleAS components (e.g. Oracle Portal, Oracle Single Sign-On).



You can associate OracleBI Discoverer with either an OracleAS 9.0.4 Infrastructure or an OracleAS 10.1.2 Infrastructure.



Note: If you want to associate an OracleBI standalone installation with a 9.0.4 Infrastructure, you must also upgrade the Discoverer part of the MR to deploy Discoverer Portlet Provider (for more information, see Section 2.3, "How to upgrade a 9.0.4 metadata repository to work with Discoverer Portlet Provider Version 10.1.2 and Oracle Portal 10.1.2").



To associate an OracleBI standalone installation with an OracleAS Infrastructure:



Start a Web browser and enter the Application Server Control URL containing the fully qualified host name and domain of the OracleBI installation that you want to configure (for more information, see Section 5.1.2, "How to start Application Server Control and display the System Components page").





Display the Infrastructure tab.













In the Identity Management area, do one of the following:



to associate the OracleBI installation, click Configure to display the Configure Identity Management: Internet Directory page


to reassociate the OracleBI installation, click Change to display the Change Identity Management: Internet Directory page







Use the Host field and the Port field to enter the host name (e.g. infra.mycompany.com) and port number (e.g. 389) of the Oracle Internet Directory component on the OracleAS Infrastructure machine.



Hint: To find out the host name and port number values to specify, start Oracle Application Server Control on the OracleAS Infrastructure machine, display the Infrastructure tab, and note down the values of the Internet Directory Host field and the Internet Directory Port field.



Click Next to display the Configure Identity Management: Login page.



Use the User Name field and the Password fields to enter the administration user name and password for the Oracle Internet Directory component on the Infrastructure machine.



Hint: Prefix the user name value with cn= (e.g. cn=orcladmin).



Click Next to display the Configure Identity Management: Validation page.



Click Finish.



Having configured Identity Management, you now configure the Metadata Repository.



In the Metadata Repository area, click Configure to display the Configure Repository: Internet Directory page.



Follow the steps in the Configure/Change Repository: Internet Directory wizard, then click Finish.



The OracleBI standalone installation is now associated with an OracleAS Infrastructure. You can now deploy Discoverer with other OracleAS components (i.e. Oracle Portal, Oracle Single Sign-On) and use Discoverer connections and Discoverer Portlet Provider.




Reference



1. http://download.oracle.com/docs/cd/B14099_19/bi.1012/b13918/assoc.htm#CJAJGEJG

2. 390222.1 - How To Connect to Discoverer Plus/Viewer When "Create Connection" Button Is Not Displayed in Connection Page



Tuesday, August 18, 2009

Oracle Discoverer 10g rehost a.k.a moving Discoverer 10g from one server to another

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 given below:


Reinstallation of Discoverer 10g

1. Install OracleAS Identity Management Infrastructure 10g (10.1.4.0.1) [infra tier].


2. Upgrade OracleAS Identity Management Infrastructure 10g to Oracle Identity Management 10g Release 3 Patch Set 1 (10.1.4.2).


3. Install Oracle Application Server 10g Release 2 (10.1.2.0.2) type Business Intelligence and Forms (mid tier).


4. Copy Database Connection file from $FND_TOP/secure/cpepdb1_cpep.dbc on the admin node to DISCOVERER $ORACLE_HOME/discoverer/secure on the new Discoverer server.


Notes:
a. One can also copy the dbc file from the old Discoverer ORALCE_HOME
on the old server to the new Discoverer ORACLE_HOME on the new
server.


b. Create the directory
$ORACLE_HOME/discoverer/secure if it does not exist.


5. Update tnsnames.ora file - Copy the file DISCOVERER
$ORACLE_HOME/network/admin/tnsnames.ora on the old server to Discoverer
$ORACLE_HOME/network/admin/tnsnames.ora on the new server.


6. Specify Java Virtual Machine for Discoverer Plus - Check what is selected as the default (should be Sun Java 1.4) in Enterprise Manager on the Oracle Business Intelligence 10g Release 2 ORACLE_HOME using ias_admin login.


7. Modify below Applications profile options related to Discoverer in Oracle Applications


ICX_DISCOVERER_LAUNCHER & ICX_DISCOVERER_VIEWER_LAUNCHER




Cloning of Discoverer 10g ORACLE_HOME

1. Install OracleAS Identity Management Infrastructure 10g (10.1.4.0.1) [infra tier] on suomp02l.


2. Upgrade OracleAS Identity Management Infrastructure 10g to Oracle Identity Management 10g Release 3 Patch Set 1 (10.1.4.2) on suomp02l.


3. Prepare the source middle tier (Discoverer 10gAS on suomp971) for cloning.


a. For cloning, Perl 5.6.1 or higher must be installed on your system.


$ export
PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1:$ORACLE_HOME/perl/lib/site_perl/5.6.1:$PERL5LIB



b. Run the script prepare_clone.pl. This script prepares the source to be cloned.


$ cd $ORACLE_HOME/clone/bin
$ perl prepare_clone.pl ORACLE_HOME=$ORACLE_HOME -oid_password <OIDPassword>


4. Archive and compress the source Oracle home, using your preferred tool for archiving (using tar and gzip).


5. Copy the compressed Oracle home from the source machine to the destination machine.


6. Extract the compressed Oracle home into a directory, which will become the new Oracle home at the destination location.


7. Run the clone.pl script. You must have write permission to the directory containing the Oracle inventory file.


$ cd $ORACLE_HOME/clone/bin
$ perl clone.pl ORACLE_HOME=<NEW_ORACLE_HOME_LOCATION> ORACLE_HOME_NAME=OH_1012_BI -instance <new_instance_name> -ias_admin_old_pwd <old_ias_admin_password> -ias_admin_new_pwd <new_ias_admin_password> -oid_password <oid_password>



8. Modify below Applications profile options related to Discoverer in Oracle Applications

ICX_DISCOVERER_LAUNCHER & ICX_DISCOVERER_VIEWER_LAUNCHER


The method that I followed was reinstallation primarily because cloning a 10g AS ORACLE_HOME is renowned to be riddled with errors and pitfalls! The reinstallation method worked like a charm and I was able to move the Discoverer 10g without any hitches and significantly, without any downtime too (!) thus avoiding weekend work!!!


References

2. Metalink Note ID: 313418.1 - Using Discoverer 10.1.2 with Oracle E-Business Suite 11i

Tuesday, July 28, 2009

Extract Controlfile from a backupset using RMAN

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 controlfile name to '<FULL_PATH_TO_SNAP_CTRL_FILE>';

III. the controlfile is included in the RMAN backupset i.e., using the clause "include current controlfile" in the backup database command.


Scenario I - Restoring controlfile using RMAN and controlfile autobackup.

a. Startup the database in nomount state.



b. Run RMAN commands as below:


$ rman target /


Recovery Manager: Release 9.2.0.6.0 - 64bit Production


Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.


connected to target database: BKP (not mounted)


RMAN> restore controlfile from '/opt/apps/vision/backup/c-261506738-20090722-02';
Starting restore at 23-JUL-09
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/apps/vision/bkpdata/bkpdatacontrol01.ctl
output filename=/opt/apps/vision/bkpdata/bkpdatacontrol02.ctl
output filename=/opt/apps/vision/bkpdata/bkpdatacontrol03.ctl

Finished restore at 23-JUL-09

RMAN>


Scenario II - Restoring controlfile using RMAN and controlfile snapshot.



a. Startup the database in nomount state.

b. Run RMAN commands as below:


$ rman target /


Recovery Manager: Release 9.2.0.6.0 - 64bit Production


Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.


connected to target database: BKP (not mounted)


RMAN> restore controlfile from '/opt/apps/vision/backup/snap_BKP_controlfile.ctl';


Starting restore at 23-JUL-09

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: copied controlfile copy
replicating controlfile
input filename=/opt/apps/vision/bkpdata/bkpdatacontrol01.ctl
output filename=/opt/apps/vision/bkpdata/bkpdatacontrol02.ctl
output filename=/opt/apps/vision/bkpdata/bkpdatacontrol03.ctl

Finished restore at 23-JUL-09


RMAN>


Scenario III - Restoring controlfile using RMAN and backupsets (provided backup is taken using the "include current controlfile" clause).


Note: The RMAN backup log would record the backup piece name which includes the controlfile. If you are not sure which backup pice contains the controlfile, then try this on all the backup pieces till the controlfile is restored.


a. Startup the database in nomount state.

b. Run RMAN commands as below:


$ rman target /


Recovery Manager: Release 9.2.0.6.0 - 64bit Production


Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.


connected to target database: BKP (not mounted)


RMAN> restore controlfile from '/opt/apps/vision/backup/BKP_LVL0_20090722_09kkof7i_s9_p1';

Starting restore at 23-JUL-09


using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/apps/vision/bkpdata/bkpdatacontrol01.ctl
output filename=/opt/apps/vision/bkpdata/bkpdatacontrol02.ctl
output filename=/opt/apps/vision/bkpdata/bkpdatacontrol03.ctl

Finished restore at 23-JUL-09


RMAN>

Monday, July 27, 2009

Extract Controlfile, Datafiles & Archivelogs from RMAN Backupsets

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_backup_restore.


Scenario:


A successful production backup (RMAN) is available (cold or hot - if hot, then the archivelogs are also available) while the production and the RMAN catalog databases are irrevertably lost.



Assumptions:


1. RMAN is being used to backup the database.


2. The RMAN backup is a "good" backup and is restore and recovery ready.


3. The list of datafiles (file name + full file path) and their datafile numbers is available (this can be obtained from the RMAN backup log file).



Solution Summary (from Note ID: 388057.1):


1. Start the target instance (nomount).


2. Restore the latest controlfile, ideally from the same backupset as the last incremental to be restored (Note 388052.1 : restore controlfile using PLSQL).


3. Make further copies if necessary as per the target database init.ora (pfile).


4. Mount the target instance (alter database mount).


NOTE: (RMAN may be able to be used to restore / recover the database at this point if the controlfile restored knows about the backup required to be restored and recovered).

5. Restore the datafiles from the level 0 backupset (Note 388053.1 : restore datafiles using PLSQL).


6. Restore (apply) the datafiles from the incremental backupsets (Note 388054.1 : restore incremental backups using PLSQL).


7. Restore the archivelogs from the archivelog backupset (Note 388055.1 : restore archivelogs using PLSQL).


8. Using traditional recovery techniques, recover the database using SQLPLUS. (until cancel using backup controlfile).


9. Open the database (resetlogs).


10. Rebuild the recovery catalog & re-register the target database.


11. Make backups of the target database and recovery catalog database.


References:


1. 60545.1 - How to Extract Controlfiles, Datafiles, and Archived Logs from RMAN Backupsets


2. 388052.1 - How to Extract the Control file from an RMAN backupset using PL/SQL


3. 388053.1 - How to Extract Datafile(s) from an RMAN backupset Level-0(Full) backup using PL/SQL.


4. 388054.1 - How to Extract Incremental Backups of Datafile(s) from an RMAN Incremental backupset Level-1 or higher using PL/SQL.


5. 388055.1 - How to Extract archive logs from an RMAN using PL/SQL.


6. 388057.1 - How to Restore an RMAN Backed up database using PL/SQL.

Wednesday, July 1, 2009

Creating defaultsfile - an easy guide!

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. Else, it creates a new file and the contents of the new file would be the answers that one provides at the subsequent prompts of adadmin.

Note: Not mentioning the correct path would error out as below. So, always ensure that you mention the full path to the default file.

$ adadmin defaultsfile=adadmin_maint_on.txt

Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications AD Administration
Version 11.5.0

NOTE: You may not use this utility for custom development unless you have written permission from Oracle Corporation.

You have not specified the correct path for defaults file.

The expected location of the defaults file is
$APPL_TOP/admin/$TWO_TASK
and you must provide full access path to the file.

2. Assuming that the defaultsfile does not exist, proceed as you normally would for compiling apps schema.

3. Once the compilation is complete, exit out of adadmin and look for the file $APPL_TOP/admin/$TWO_TASK/adadmin_cmpl_apps_schema.txt.

4. Voilà! You are done! You have the defaultsfile (including passwords) for compiling apps schema. Whenever you want to compile apps schema using adadmin NON-INTERACTIVELY, run the below command:

$ adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adadmin_cmpl_apps_schema.txt


Tuesday, June 30, 2009

PCP in Non-RAC Instances - Pitfalls -- Solutions?

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> entry for both the primary server and the failover server. This way, there is no need to reconfigure it each and every time.

Of course, this tnsnames.ora should be saved before running autoconfig else it would be overwritten.

Signing off hoping that the above solutions work!

Wednesday, June 3, 2009

PCP in non-RAC instances - Pitfalls

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 to reflect the change in the concurrent processing server. This entry takes the form FNDFS_<FAILOVER_SERVER_NAME>. Thankfully, this does NOT necessitate downtime of the instance.


Do post your comments/suggestions/ideas on the above 2 problems.

Tuesday, March 17, 2009

Find Tablespace Usage Percent

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_name tablespace_name,
round(decode(ddf.autoextensible,'NO',ddf.bytes/1024/1024,'YES',ddf.maxbytes/1024/1024),2) "TOTAL_SPACE",
ddf.autoextensible,
case
when ddf.maxbytes < ddf.bytes
then round(((ddf.bytes/1024/1024) - nvl((dfs.free_space),0)),2)
else round(ddf.bytes/1024/1024,2)
END "USED_SPACE",
case
when ddf.maxbytes < ddf.bytes
then nvl(dfs.free_space,0)
else round(((ddf.maxbytes/1024/1024) - (ddf.bytes/1024/1024)),2)
END "FREE_SPACE"
/*, case autoextensible
when 'NO' then round(((((ddf.bytes/1024/1024) - nvl(dfs.free_space,0)) / (ddf.bytes/1024/1024)) * 100),2)
when 'YES' then round((((ddf.bytes/1024/1024) / (ddf.maxbytes/1024/1024)) * 100),2)
END '%' "USED_PERCENT" */
from dba_data_files ddf,
(select file_id, round(sum(bytes)/1024/1024,2) free_space from dba_free_space group by file_id) dfs
where
ddf.file_id = dfs.file_id(+)
) tbs
group by tbs.tablespace_name
order by 5 desc;



Wednesday, March 4, 2009

EXCEPTION_ACCESS_VIOLATION in Jinitiator and IE crashes

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 Source)
at sun.java2d.loops.RasterOutputManager.performOpaqueBlit(Unknown Source)
at sun.java2d.loops.RasterOutputManager.compositeSrcDst(Unknown Source)
at sun.java2d.loops.RasterOutputManager.renderImage(Unknown Source)
at sun.java2d.SunGraphics2D.renderingPipeImage(Unknown Source)
at sun.java2d.SunGraphics2D.drawImage(Unknown Source)
at sun.java2d.SunGraphics2D.drawImage(Unknown Source)
at sun.awt.windows.WGraphics.drawImage(Unknown Source)
at sun.awt.windows.WGraphics.drawImage(Unknown Source)
at oracle.ewt.lwAWT.SharedPainter.paint(Unknown Source)
at oracle.ewt.lwAWT.BufferedFrame.paint(Unknown Source)
at sun.awt.RepaintArea.paint(Unknown Source)
at sun.awt.windows.WComponentPeer.handleEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Dynamic libraries:
0x00400000 - 0x0049B000 C:\Program Files\Internet Explorer\iexplore.exe
0x7C900000 - 0x7C9B0000 C:\WINNT\system32\ntdll.dll
0x7C800000 - 0x7C8F5000 C:\WINNT\system32\kernel32.dll
0x77DD0000 - 0x77E6B000 C:\WINNT\system32\ADVAPI32.dll
0x77E70000 - 0x77F02000 C:\WINNT\system32\RPCRT4.dll
0x77FE0000 - 0x77FF1000 C:\WINNT\system32\Secur32.dll
0x77F10000 - 0x77F58000 C:\WINNT\system32\GDI32.dll
0x7E410000 - 0x7E4A0000 C:\WINNT\system32\USER32.dll
0x77C10000 - 0x77C68000 C:\WINNT\system32\msvcrt.dll
0x77F60000 - 0x77FD6000 C:\WINNT\system32\SHLWAPI.dll
0x7C9C0000 - 0x7D1D6000 C:\WINNT\system32\SHELL32.dll
0x774E0000 - 0x7761D000 C:\WINNT\system32\ole32.dll
0x78130000 - 0x78257000 C:\WINNT\system32\urlmon.dll
0x77120000 - 0x771AB000 C:\WINNT\system32\OLEAUT32.dll
0x78000000 - 0x78045000 C:\WINNT\system32\iertutil.dll
0x77C00000 - 0x77C08000 C:\WINNT\system32\VERSION.dll
......
....
....


Initial Troubleshooting
Uninstalling and reinstalling jinitiator did not work. So, probed in Metalink to find out whether it is a known issue.

Found only one relevant hit in Metalink - Doc ID: 422761.1 - for this issue.

The Cause
As per the Note 422761.1, "This is a work station hardware issue related to a device driver associated with usually video cards. It also could cause by other device drivers."

The Solution
Again, as per the Note 422761.1, there are 2 solutions.

Solution 1
1. Add the following parameter to your Jinitiator Java Runtime Parameters:2. Control Panel > Open Jinitiaitor 1.3.1.24 (or whatever version) > [Tab] Basic > Enter the following in your Java Runtime Parameters field:
-Dsun.java2d.noddraw=true
3. Click Apply
4. Open your browser, then clear browser cache, and test.

Solution 2
To add to the environment:
1. Start->Control Panel->System
2. Click the Advanced Tab
3. Click the Environment Variables button
4. Click the New button
5. For Variable name:_JAVA_OPTIONS
6. For Variable value -Dsun.java2d.noddraw=true [or -Dsun.java2d.d3d=false ]
7. Click OK

Followed solution 1 mentioned above and Voilà, the issue is resolved.

Monday, February 16, 2009

java.lang.ExceptionInInitializerError while trying to Access login page

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.ExceptionInInitializerError

Request URI:/OA_HTML/AppsLocalLogin.jsp
Exception:

java.lang.NoClassDefFoundError

As soon as the services were bounced, we were receiving the first error. After some time, we received the second error (yes, truth is indeed stranger than fiction!!).



Analysis:

Since, we were receiving "NoClassDefFoundError", performed the following:

1. Checked the CLASSPATH and AF_CLASSPATH variable and ensured that every path is valid.



2. Checked all the class paths defined by wrapper.classpath in jserv.properties using the command below:

for i in `grep classpath $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties grep -v "#" cut -d '=' -f2`
do
ls -ld $i >> /tmp/check_class_path.txt
if [[ $? -eq 0 ]]; then
continue
else
printf "\n\n '${i}' is not proper. \n\n"
fi
done


I compared the output generated from the above script with that from the development instance that was working and found no discrepancies.



3. Ran aoljtest.jsp and found that it was running fine till the step "Verify Guest User", where it was throwing "Page cannot be found" error.



4. The above error set me thinking and I checked the GUEST username and password in the

  • dbc file
  • database (profile option)
  • CONTEXT_FILE
  • output of the below query "select fnd_web_sec.validate_login ('GUEST','<PASSWORD>') from dual;"

and found it to be consistent.



5. Checked a lot of Metalink Notes and documents and blogs and what not, but to no avail.



6. Enabled debug in :

  • jserv.conf
  • jserv.properties
  • httpd.conf

but no log file was informative enough to point me in the right direction.



7. Meanwhile, the error log had been throwing the below error, which I analysed but could not make much headway and found it to be actually misleading.

[Sun Feb 15 23:13:20 2009] [error] [client <IP_ADDRESS>] (78)File name too long: access to /redirectURL$url=http_3A$$ <hostname.domain_name>_3A<port_number>$oa*_servlets$ oracle.apps.fnd.sso.AppsLogin_3FrequestUrl$=$oa*_servlets$weboam$ oam$oamLogin_26cancelUrl$=http_3A$$ <hostname.domain_name>_3A<port_number>$oa*_servlets$ oracle.apps.fnd.sso.AppsLogin$forward=oam$oamLogin failed


8. After exhausting all the options, I decided to take a break and promptly headed for lunch!



9. Post lunch, I tried to re-examine the issue from a different perspective. As point 3 above states, I was receiving an error while trying to check the Guest username using aoljtest.jsp. Again checked the guest username and password in the palces mentioned in point 4 above and found them to be consistent.



10. Finally (actually, I had found this note earlier, but ignored it because I found the guest username and password to be correct), I found Note: 458064.1, which I found relevant on three counts:

  • It mentions about the "java.lang.ExceptionInInitializerError" error.
  • we were also on ATG RUP 5 (5473858), as the note mentions.
  • It also mentions that "Verify Guest user" ends with an error (though different from the one that I was receiving).

So, I decided to follow the solution mentioned in that note.



Solution:

1. a. Login directly to Forms via http://<hostname>:<port>/dev60cgi/f60cgi

b. Set the value of system profile "Password Case Option" to "Insensitive"

c. Save and close Oracle Applications


2. On the operating system as the apps user (e.g. applmgr) execute these commands:

a. cd $FND_TOP/secure/<SID>_<hostname>

b. java oracle.apps.fnd.security.AdminAppServer <apps username>/<apps password> UPDATE GUEST_USER_PWD=GUEST/<PASSWORD ALL UPPERCASE> DB_HOST=<host> DB_NAME=<SID> DB_PORT=<port>


The values for GUEST_USER_PWD, DB_HOST, DB_NAME, and DB_PORT can be found in the $FND_TOP/secure/<SID>_hostname/<SID>.dbc file if required.


3. Verify that the following things are true:

a. The GUEST password is updated and all upper-case in the $FND_TOP/secure/<SID>_<hostname>/<SID>.dbc file.

b. That the profile GUEST_USER_PWD is set correctly after the password change. The SQL to run is:

set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set linesize 120
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60 wrap
select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10007, 'SERVRESP',
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) = 'GUEST_USER_PWD'
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set;


c. That the following SQL returns 'Y':

SELECT fnd_web_sec.validate_login('GUEST','') FROM dual;


4. Bounced the services and retried the login, which worked like a charm.

Thursday, February 5, 2009

Flower brackets {} and variables in UNIX - A perfect match

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] [-i] [-p] [-@] d1 ... dn-1 dn
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] [-i] [-p] [-@] d1 ... dn-1 dn
cp: Insufficient arguments (1)
Usage: cp [-f] [-i] [-p] [-@] f1
cp [-f] [-i] [-p] [-@] f1 ... fn
cp -r-R [-H-L-P] [-f] [-i] [-p] [-@] d1 ... dn-1 dn
cp: Insufficient arguments (1)
Usage: cp [-f] [-i] [-p] [-@] f1
cp [-f] [-i] [-p] [-@] f1 ... fn
cp -r-R [-H-L-P] [-f] [-i] [-p] [-@] d1 ... dn-1 dn
cp: Insufficient arguments (1)
Usage: cp [-f] [-i] [-p] [-@] f1
cp [-f] [-i] [-p] [-@] f1 ... fn
cp -r-R [-H-L-P] [-f] [-i] [-p] [-@] d1 ... dn-1 dn
cp: Insufficient arguments (1)
Usage: cp [-f] [-i] [-p] [-@] f1
cp [-f] [-i] [-p] [-@] f1 ... fn
cp -r-R [-H-L-P] [-f] [-i] [-p] [-@] d1 ... dn-1 dn
__________________________________________________________________

Can you guess or pinpoint as to what exactly is wrong with the above code, which makes it throw this error?

It took me sometime but I finally figured it out. $i_old was being considered as a variable i.e., the shell was reading the variable as $i_old and not as the variable $i appended with an underscore(_) and 'old'.


The solution
The solution to this obviously is to use the flower brackets (braces/parantheses) for the variable $i so as to make it ubiquitous. Now the code looks like:
for i in `ls *ver`
do
cp -p ${i} ${i}_old
done


The conclusion
To conclude, I would like to say to my fellow DBAs who are also into shell scripting, watch out for small things like these, which might make or break your day!

Wednesday, February 4, 2009

11.5.9 & JDK 5 - Mutually exclusive

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 1.5, 'enum' is a keyword, and may not be used as an identifier (try -source 1.4 or lower to use 'enum' as an identifier) Enumeration enum = request.getParameterNames(); </TD></TR><TR><TD WIDTH=7% VALIGN=TOP><P ALIGN=CENTER>991</TD><TD> as of release 1.5, 'enum' is a keyword, and may not be used as an identifier (try -source 1.4 or lower to use 'enum' as an identifier) while (enum.hasMoreElements()) </TD></TR><TR><TD WIDTH=7% VALIGN=TOP><P ALIGN=CENTER>993</TD><TD> as of release 1.5, 'enum' is a keyword, and may not be used as an identifier (try -source 1.4 or lower to use 'enum' as an identifier) String param = (String)enum.nextElement();
__________________________________________________________________

Research

Now, the above error gave me an idea. I decided to check the Metalink Note 300482.1 - Overview of Using Java with Oracle E-Business Suite Release 11i.

This note clearly states that for 11.5.9, the JDK (Java SE) version supported is 1.4.2.

Does that mean that JDK 5 and JDK 6 are not supported for 11.5.9? Not quite, as I found out.

The Note 304099.1 (Using J2SE Version 5.0 with Oracle E-Business Suite 11i) does NOT mention 11.5.9 anywhere, implying that JDK 5 is indeed not supported with 11.5.9.

However, the Note 401561.1 (Using J2SE Version 6 with Oracle E-Business Suite 11i) does mention that JDK 6 can be used with 11.5.9 provided (yes, as the ads say - conditions apply!) that you "ensure that your E-Business Suite Release 11i system has been upgraded to Maintenance Pack 11.5.9 CU2 or higher with ATG_PF.H RUP5 or higher on the 11.5.9 stream."

Conclusion

This means that 11.5.9 is indeed supported with JDK 6 but NOT JDK 5 (of course, provided that the above conditions are satisfied).

Because, 11.5.9 is NOT supported with JDK 5, I believe we are hitting the above issue (internal server error in installed base). As of now, there is no solution to this issue apart from
1. downgrading JDK from 5 to 1.4.2
2. modifying the xml to point to 1.4.2
3. running autoconfig to ensure the changes are reflected.

Signing off now hoping that the above solution works!

Find OS Kernel Bit in UNIX - Ready reckoner

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 executable on a 64-bit OS but not vice-versa.

Monday, February 2, 2009

The curious case of database crash and ORA_NLS10

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_HOME/admin/[CONTEXT_NAME]/udump/cpek_ora_5468.trc:
ORA-07445: exception encountered: core dump [lfilic()+328] [SIGSEGV] [Address not mapped to object] [0x000000040] [] []
Mon Feb 2 00:26:51 2009
Errors in file $ORACLE_HOME/admin/[CONTEXT_NAME]/udump/cpek_ora_5573.trc:
ORA-07445: exception encountered: core dump [lfilic()+328] [SIGSEGV] [Address not mapped to object] [0x000000040] [] []
Mon Feb 2 00:28:02 2009
Errors in file $ORACLE_HOME/admin/[CONTEXT_NAME]/udump/cpek_ora_5486.trc:
ORA-07445: exception encountered: core dump [lfilic()+328] [SIGSEGV] [Address not mapped to object] [0x000000040] [] []
Mon Feb 2 00:28:37 2009
.....
..
..
..
Mon Feb 2 00:38:10 2009
Errors in file $ORACLE_HOME/admin/[CONTEXT_NAME]/udump/cpek_ora_5560.trc:
ORA-07445: exception encountered: core dump [lfilic()+328] [SIGSEGV] [Address not mapped to object] [0x000000040] [] []
Mon Feb 2 00:40:15 2009
Incremental checkpoint up to RBA [0x27c.48028.0], current log tail at RBA [0x27d.1fc8.0]
Mon Feb 2 00:42:30 2009
Completed checkpoint up to RBA [0x27d.2.10], SCN: 10245291290327
Mon Feb 2 00:48:19 2009
MMNL: terminating instance due to error 472
Instance terminated by MMNL, pid = 4493
_____________________________________________________________

Suspects:

I found that this error is caused because the ORA_NLS10 variable is not set when the database was started or is set to an incorrect value.

Case Resolution:

1. set the env variable ORA_NLS10 as below:
export ORA_NLS10=$ORACLE_HOME/nls/data/9idata (for an Apps instance).

2. Startup the database.

3. Startup all the Apps services.

Witnesses:

1. Metalink Note: 420069.1

2. Metalink Note: 567472.1


AF Variables in CONTEXT_FILE - What they are and their use.

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:
  1. AF_JRE_TOP
  2. AF_CLASSPATH
  3. 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 letting programs know the location of java (AFJVAPRG), JRE (AF_JRE_TOP) and the classpath (AF_CLASSPATH) on the concurrent node.

Also, note that these variables are also set on the web node (and must be set to valid values too).