About "Latch Free" Wait Event - My Perspective

Hi,

All latch wait events are collectively referred to as "latch free" event in 9i. The specific latch involved must be determined either from a 10046 trace or using the SQLs. There are many types of latches and all the latches are catalogued in v$latchname. Since all latch wait events show up as just "latch free" in v$session_wait, there is no single corrective action that fixes the "latch free" wait issue. The corrective action depends on the type of the latch for which the session is waiting. Here is how we can identify the type of latch and then act accordingly.

The P1 column in v$Session_wait corresponds to Latch Adress. P2 refers to the latch number. P3 is tries and i dont think is too important in finding out the name of the latch.

To find the name of the latch, you can take the value from P1 column of v$session_wait and substitute the value in following sql

select x.latch#,x.name from v$latchname x, v$latch y
where y.addr = '&address_from_P1'
and y.latch#=x.latch#;

You can also do the same by substituting the P2 column value in the following SQL:

SELECT name FROM v$latchname
WHERE latch# = &P2;

If many sessions are waiting on latch free event, then we would want to first find out what latch are they waiting for. If all of them are waiting for a specific latch, then I think the troubleshooting becomes focused on that latch and hence, is easier.

One of the latch that I have had trouble in the past was Cache Buffers Chain Latch.

This latch is acquired when a data block is accessed in buffer cache. If too many sessions want to access the same data block, you will see all these sessions waiting for "latch free" event. This situation is referred to as "hot block". This can happen in situations where sql statements are run in parallel mode and each one wanting to access the data block.

I came across a good metalink document to identify the hot blocks: 163424.1

While I am not aware of a sure fire way of fixing this issue outright, situation specific solutions can be implemented. For eg. If month end /quarter end processing is happening in Oracle Apps and lot of batch jobs are running, then one of the possible solution might be rescheduling some of the jobs contending for the hot blocks at a later time. If a sql statement is running in parallel, it might be worth while to see if it makes sense to run the query in a non parallel fashion.


- Aravind Kamath

Autoconfig failing with ORA-00603: ORACLE server session terminated by fatal error

Hi,

We were in the process of applying the RUP4 in our R12 development environment. As part of this activity, we had to run autoconfig and autoconfig failed with the following error:

Running Profile Process 1 of 2 for BIS_TOP
Executing script in InstantiateFile:
/apps/INSTANCE_NAME/apps/tech_st/10.1.3/perl/bin/perl -I /apps/INSTANCE_NAME/apps/tech_st/10.1.3/perl/lib/5.8.3 -I /apps/INSTANCE_NAME/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3 -I /apps/INSTANCE_NAME/apps/apps_st/appl/au/12.0.0/perl -I /apps/INSTANCE_NAME/apps/tech_st/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/i686-linux-thread-multi /apps/local/INSTANCE_NAME/inst/apps/CONTEXT_NAME/admin/scripts/adexecsql.pl sqlfile=/apps/local/INSTANCE_NAME/inst/apps/CONTEXT_NAME/admin/install/bisdblrp.sql

DECLARE
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

-------
alert log - ORA-00600: internal error code, arguments: [16500], [kqddlu]


The following changes were made in the environment recently:

- Refresh from production
- Database upgrade to 10.2.0.4 from 10.2.0.2


Troubleshooting tips:

The bisdblrp.sql creates two db links EDW_APPS_TO_WH and APPS_TO_APPS. This sql was failing outside of autoconfig with the same error. The script actually drops (if the links already exists) and recreates the db links.The script also spools the output. The spool showed that we were facing this issue while trying to drop the above db links. In the meanwhile, I queried dba_db_links and found two entries for each of the above links. This was the reason for the error we were seeing. Fix was to delete the rows for these db links from sys.link$ table and then run autoconfig. Autoconfig ran fine without issues.

Upon further analysis, I found that the issue was happening due to import of sys.link$ after refresh as one of the post refresh tasks. We are adopting this import process as we have large number of private db links in our database. So the sequence of events were like this:

- export the sys.link$ table from the target database before destroying the database.
- clone the database from production
- refresh the apps from production and run autoconfig as part of this activity
- import the sys.link$ table to get back all the db links.

- Aravind Kamath Posral

Setting up Directory Name Resolution in Oracle Environment

Hi,

Wishing you all a very happy deepavali!!!!!

There are many ways in which Oracle Service Name Resolution can be implemented in an enterprise. Some of the naming methods supported by Oracle Net Services are Local Naming, Directory Naming, Host Naming, Oracle Names (I think this will be deprecated) etc. In almost every environment I have worked in the past, Local Naming Method was in use. This would invariably involve name resolution happening using the local "tnsnames.ora" file and multiple local copies of tnsnames.ora file is often inevitable.

In my current working environment, we have a large number of oracle databases. Interestingly but not surprisingly, Directory Naming method has been implemented in our environment. This post is about the steps that are necessary to setup directory naming method in an Oracle environment.

Prerequisite:

You need to have a working LDAP server configured for use by Oracle. Now, although Oracle supports Microsoft LDAP, I think this has some limitations. One of the biggest hinderances of implementing Directory naming method on Microsoft LDAP server is that the Oracle database needs to be running on Windows. While this may not be feasible at many enterprises, Oracle integrates tightly with Oracle Internet Directory (OID), which is also a LDAP compliant server from Oracle. OID can be installed as part of the "Infrastructure" install of Oracle 10gAS.

You need what is known as "Oracle Context" to be able to register Oracle database service and Net Service Names or Aliases. OID creates a default Oracle Context for usage called as "OracleContext" in the OID. If you need alternate Oracle context, you can create one with Oracle Net Configuration Assistant. Net configuration Assistant is also the one which creates your ldap.ora file which contains the information about the OID to which the oracle home must connect. The ldap.ora file contains information like the directory server name, port and the directory server type.

Apart from the above, the directory server must allow anonymous authentication.

I think that planning, set up and maintenance of an LDAP server is a specialist job function in itself and dont think would come under the purview of an Oracle DBA.

Steps to Configure Directory Naming Method:

There are three different types of configuration that can happen:

1. Oracle Service Registration with OID. (Mandatory)

This is nothing but database service name entry that contains the actual name of the database. This entry in the OID will contain the "attributes" which constitute the "Connect Descriptor" that is used to locate the database. Connect Descriptor is nothing but the entries that are found in normal tnsnames.ora file which help Oracle Net locate the database service. Once the service registration is done, remote users who are configured to use directory naming can start connecting to the database with no additional configuration. You need to have your ldap.ora before you attempt this. The user credentials that you use to register the database service in OID, needs to be part of either OracleDBCreators or OracleContextADmins (Super user privs) OID groups to be able to register the service.


2. Setting up directory server usage on database server* and client. (Mandatory)

This step enables the clients to use directory server for name resolution.


3. Oracle Net Service Names / Alias registration with OID. (Optional)

This is about registering Net Service Names (additional names to refer a database) for a given database. Use Oracle Net Manager for this.


In either of the above cases 1 and 2, two more options fork out:

1a. Oracle Service Registration with OID during installation of the database.
>> When you use custom database installation method, you can use the Oracle Net Configuration assistant to configure the ldap server for usage. The Oracle Net Configuration asssistant is launched as part of the installation itself. If this is the first time you are setting up directory naming method, you can choose the option to create the context and repository in OID. Else you can just select the OID and the context and continue. Eventually, during the process of installation, When the DBCA is invoked, you have the option to register the database. DBCA then goes on to create the service entry and updates the RDBMS_SERVER_DN initialization parameter.

1b. Oracle Service Registration with OID after installation of the database. (This can happen for eg. if you were originally using local naming method and then decided to move on to directory naming method.)

>> You need to have the ldap.ora in place. To get one, you need to run Net Configuration Assistant. In this case you may invoke the dbca and then register the database service with the OID. This time, choose "configure database" option. In the subsequent screens you should see an option "Yes, Register the database". You will be prompted for the OID credentials. Enter the credentials of the user who has authority to register the service.



2a. Setting up directory server usage on database server and clients during installation

>> If you chose to register your database with OID in step 1a or 1b, then you dont have to do anything as far as database server oracle home is concerned. For your client installations, use Net Configuration Assistant to set up OID usage by choosing the Directory type, directory's hostname, port and OracleContext. That is all you need to do.



2b. Setting up directory server usage on database Server and Clients after installation
>> If you chose to register your database with OID in step 1a or step 1b, then you dont need any further configuration on database server. On the client side, just invoke Net Configuration Assistant to setup directory usage. Choose the Directory type, directory's hostname, port and OracleContext under Configure Directory Usage option. That is all you need to do. Verify that your sqlnet.ora file has been correctly updated to lookup ldap before any other methods listed.

For eg.

NAMES.DIRECTORY_PATH = (LDAP,TNSNAMES)


3. Oracle Net Service Names / Alias registration with OID (Optional)
>> You need an OID user credential that is part of OracleNetAdmins group. This is the privilege that is required to make net service names/alias entries. You also need the ldap.ora. In the left side pane, you need to choose "Directory" and then underneath directory, you can define service names. You can also create service name alias here.

I think this pretty much sums up the Directory usage configuration exercise.

*Setting up directory server usage on database server Oracle home may be required if you use db links in the database and need to look up for other databases and make connections.

- Aravind Kamath

Return to Unix World

Hi,

I have been through a transition in the last few months both in my personal and professional life. On the professional front, there has been a significant change in the environment in which I will continue to work with Oracle Apps. Yes, I am back to working on Unix platforms. I will continue to update this space with technical posts as and when I come up with something.

-Aravind Kamath Posral

Java Workflow Mailer does not startup

Issue:


The Java Workflow Mailer does not startup. The following error message is recorded in the log file - "Exception in static block of jtf.cache.appsimpl.AppsCacheLogger. Stack trace is: oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException: Exception creating new Poolable object."

Symptoms, Troubleshooting and Fix:

We started seeing this issue when we were validating various components of Oracle Apps in our newly upgraded environment. We had just finished upgrading from 11.5.8 to 11.5.10.2. The workflow mailer would not come up and I started troubleshooting the issue. I observed that all Java based managers like SFM event manager, Workflow Agent Listener etc were not working and the problem was not limited to workflow mailer alone.

As part of 11.5.10.2 upgrade, we had upgraded the tech stack components as well. Two of the tech stack components that were upgraded among others were JDK and Oracle Database. We upgraded from JDK 1.3 to JDK 1.6 and from 9.2.0.6 (64-Bit) to 10.2.0.3.0 (64-Bit) on the database front.

Further investigation of the issue revealed the following issue:

- References to JDK 1.3 in the windows registry on the concurrent manager server.


The issue was corrected after changing the references from JDK 1.3 to JDK 1.6. Specifically the AFJVAPRG registry variable was the culprit.

- Aravind Kamath

How to determine the Oracle Application Server Metadata Repository (Portal repository) Version

This post is a 10gAS post and not an ERP one.

Here is the situation: You are analyzing the latest release of CPU patches / updates and the patch readme says "apply this patch only if you have xxxx version of portal metadata repository". Now you want to find out what version of the repository do you have in your environment. These steps will help identify the repository version:



1. login to Enterprise Manager - Application Server Control using the application server control URL.
2. Open the file portlist.ini under $PORTAL_ORACLE_HOME/install and this file will reveal the port on which Application Server Control component will listen.
3. Ensure that the Application Server Control service is running in the services panel.
4. Generally you would use ias_admin usercode and password to login.
5. Once you login, under the 'standalone Instances', choose your portal instance.
6. In the resulting screen, choose portal:portal under 'System Components'.
7. The resulting screen shows something like this:

OracleAS Metadata Repository Used By Portal



Status Up
Name udsard
Start Time Apr 13, 2008 10:04:36 AM
Database Version 10.2.0.3.0
Repository Version 10.1.2.0.2


Now you know your repository version !

- Aravind Kamath

RW-50010: Error: - script has returned an error

Last couple months I was held up with some personal stuff that kept me away from my tech blogs. Today I will share a couple of issues that we faced while setting up a 11.5.10.2 instance on a windows 2003 server and their resolution.

==

Cannot execute Install for database ORACLE_HOME

There was an error while running the command - E:\oraerp\uviserpdb\9.2.0\temp\UVISERP_usearvp405\adrun9i.cmd APPS APPS
The process tried to write to a nonexistent pipe.

RW-50010: Error: - script has returned an error: 1
RW-50004: Error code received when running external process.

==

We encountered the above error on the database server while rapid install was in the process of installing the vision database. The error was quite deceptive. After checking basic things like read write permissions etc, I came across a suggested solution to reduce the %NUMBER_OF_PROCESSORS% environment variable to less than 12. Our server has 16 CPUs and hence rapid installer (rather the java program which is invoked by rapid installer) was expecting the number of cpus to be less than 12. The suggested solution was to change the NUmBER_OF_PROCESSORS environment variable to 12 or less and reboot the server. Once, that is done clean up the failed install and start over again.

I did not want to clean up a failed install. I came up with the following approach instead:

- Defined a new environment variable with the same name (NUMBER_OF_PROCESSORS) at user level (the owner of the db file system / user-code with which rapid install was being run) and assigned a value of 12.
- Instead of a clean up, I restarted the installation with restart option : RapidWiz.cmd -restart

Once this was done, we got past the error and the installation continued. The joy of overcoming this issue was short lived when I saw the following error 10 minutes after I sorted out the first one:

==

"Cannot execute update of the OUI Inventory for database ORACLE_HOME\n";

There was an error while running the command - E:\oraerp\uviserpdb\9.2.0\temp\UVISERP_usearvp405\adrun9i.cmd APPS APPS
The process tried to write to a nonexistent pipe.

RW-50010: Error: - script has returned an error: 1
RW-50004: Error code received when running external process.

Registering local Oracle Home located at E:\oraerp\uviserpdb\9.2.0 to central oracle inventory
RC-00126: Update inventory failed.
null
Raised by oracle.apps.ad.clone.util.OracleHomeCloner

==

The last three lines of error was obtained from the Oracle Inventory logs.

This was due to the fact that we already had a 10g database running on the same server for some other application. The oracle inventory was created by 10g installation. However, all oracle installations will try to update the same Oracle Inventory. 11.5.10.2 brings in 9.2.0.6 database as part of the tech stack and this would not update the oracle inventory created by oracle 10g. I employed the following workaround to get past this issue:

- Renamed the whole existing oracle folder containing the inventory
- Ran rapid install and the new inventory was created. Installation was successful.

Also, ours is a two node install with no apps components on db server. So i can rename the inventory created by 9.2.0.6 instance any time and bring back the 10g inventory if 10g oracle home needs to be patched.

- Aravind Kamath

Routine CALL_STORED_PROCEDURE cannot initialize concurrent request information for

Problem:

Concurrent requests are failing. Only a fraction of Standard managers are running as against expected 50. No error messages in the ICM Log. Concurrent requests are failing with following errors:

Routine CALL_STORED_PROCEDURE cannot initialize concurrent request information for

Routine AFPEOT cannot initialize concurrent request information for .

Solution:

The mount point containing the $APPLLOG and $APPLOUT was full. Clean up some space.

- Aravind Kamath

Cisco Load Director to Cisco Content Switching Module Migration in ERP Environment

In the recent past, we had to move from our old desupported load balancer to a newer certified version of load balancer in our ERP environment. The old desupported load balancer was Cisco Load Director. The new supported version is Cisco Content Switching Module.

Actually, we (DBAs) do not handle the configuration and setup of the load balancer. However, we are responsible to ensure that the load balanced ERP environment meets our requirements and the ERP environment is available to the users.

One of the "simple" requirements for CSM was that the Servers which would be defined as "Real Servers" in CSM needed to be in a specific VLAN segment. This would mean that the IP addresses for the real servers (in our case online servers - apache / forms) had to be changed. We started with this background and had to take care of the following steps to complete the migration successfully:

1. Change the IP address of the ERP Mid-tier servers.
2. Update the host DNS records to reflect the new IP.
3. Configure the CSM with our load balancing requirements.
4. Update the DNS records for the virtual URL for ERP.
5. Update Oracle Applications (fnd_nodes) with the new ip address information.
6. Update the firewall rules to allow access to the new IP addresses.

By the way, our load balancing requirements were very simple. We have three online mid-tier servers and we wanted the requests to be routed in a round robin fashion on Apache, Forms and Mobile Supply Chain Application ports. We also wanted buddy enabled between Apache and Forms. This will ensure that the apache and forms sessions from a same IP will be directed to the same mid-tier. Also, we wanted "sticky" to be at 60 minutes. This ensures that one IP address will stay on one midtier with an idle time of upto 60 minutes.

- Aravind Kamath

Musings of the Mind: Of Choices and Right Decisions

You might wonder as to what this post is all about, because you were expecting a Apps-DBA-Tech-Stuff post and this one is far from being a tech post.

Nevertheless, I am sure life presents each one of us with different choices almost at every step in our lives. However, not often we get into the mode of sweating it out to make the right choices out of the convoluted ones and there by making the right decisions. This is so, I believe, because the choices presented in front of us make that task easy. In other words, it is easy to pick the 'obvious' one. What really puts one under tremendous stress and pressure is when all the available choices have their own significant merits and significant demerits, that it becomes difficult to choose one. It is like having to choose between my engineering exam questions 1a and 1b OR 2a and 2b, each pieces carrying 10 marks, where albeit my wish that i would love to answer 1a AND 2b, that would not be allowed. This is especially true when the choices pertain to ones life and will chart the course of ones future - with no option of returning back - kind of one way street.

I think this is when one has to start analysing the choices against the backdrop of the knowledge of the consequences of making a choice. Let go of things over which we have no control. Focus on things which are under our control. Seek answers from the innerself. Apply past experiences. Seek expert assistance. Confide in your friend, partner. Then go ahead, make a decision, because there are no right or wrong decisions - only consequences. Embrace the consequences with open arms, for that was the best you could do, under "those" circumstances. After all, we are human and no Oracle!

- Aravind Kamath

Killing a Jserv in a Load Balanced Environment

Some time back, we came across a very interesting situation where the requirement was to kill a jserv. Here is the background : In our environment (11.5.8 + JDK 1.3.1_19), we have multiple Jservs configured to run under apache. These Jservs are running in "Automatic" mode - in other words not in manual mode. One of the jservs was throwing noclassdefinitionfound error and we wanted to kill that jserv so that we could get rid of this issue. The problem in this approach is that all the jservs write to one single jserv.log file and there is no way to identify which jserv is hitting this error based on the log file.

I came up with the following approach to overcome this issue:

1. Time spent by each jservs in user mode and kernel mode
2. IO performed by each jservs
3. Virtual Memory consumed by each of the jserv processes.

Since the load balancing happens in a round robin fashion, in other words apache distributes the load in a round robin fashion between the jservs started automatically, it is fair to assume that each of the jservs should see equal amount of load in terms of user requests. Based on this assumption and the facts collated as above, I was able to zero down on a single jserv which had spent less time, less io and least vm amongst the four jservs to be the culprit.

I killed that jserv and the issue was fixed.

- Aravind Kamath

Oracle Apps DBA: Workflow Notification Mailer - Mails Sitting in Inbox

This is the other situation that i talked about in my earlier post with Notification Mailer.

Situation:

Mails are sitting in the inbox and Production Workflow Notification Mailer is not processing them.

Symptoms:

1. Most of the mails are getting processed without any issues.
2. Some of the mails are not getting processed. They are not moved to Discard folder either.
3. Out going emails are working fine.


Troubleshooting:

The starting point in this case too, was to pick up a NID from the one of the mails sitting in the inbox. This was specially puzzling because there were no errors logged in the workflow notification mailer logs. The log level was set to error. There were no obvious discrepancies in the wf_notification or wf_notification_out or wf_notification_in queues.

After some desperate troubleshooting techniques like bouncing the mailer failed, I changed the log level to 'statement'. This is when the first clue emerged. I saw some entries in the mailer log something like 'mailer will not process these NIDs'. I cant recollect the exact error message, but it sounded something like that. Then i decided to take a closer look as to why the mailer refused to process those notification.

The answer lied in the previous week end patching that was performed in production environment. I realized that autoconfig was run as part of patching and this was the key to the issue. In our environment, we have modified the node names to match wf. The default value that oracle brings in WFMAIL. We have two production instances and hence we have renamed this to read WFOIAP and WFOGAP. This was reset to WFMAIL after autoconfig was run.

The notification ids contain the following section at the bottom of the notification:

NID[5369273/218241628447419580901891042449889261398@WFOIAP]

In the above example, WFOIAP is the mailer that would process this notification. Since the mailer name got changed to WFMAIL in production, it would not process.

Hence, to sort out the issue, we changed the node name back to WFOIAP and the mails were cleared in no time.

But this also led to another issue - the mails that were generated with node name as WFMAIL in the problem window started piling up in inbox as WFOIAP would not process them.

This situation was handled manually to get past the issue.

- Aravind Kamath

Oracle Apps DBA: Workflow Notification Mailer - Mails incorrectly being moved to Discard Folder

In the recent past, we were faced with two separate issues pertaining to Workflow Notification Mailer in our production environment. We have OWF.H in our environment and of course, it is a java mailer. I will write about one situation here and the other in a separate post.

Here is the situation:

Inbound mail processing is erratic. Some mails are processed and some are not.

Symptoms:

1. Outbound notifications are working fine.
2. Inbound notifications are not being processed correctly. Some mails are being processed and some legitimate mails are directly moved from "Inbox" to "Discard" folder.

Troubleshooting Tips:

I first started with a legitimate email which was incorrectly moved to discard folder directly from inbox. I noted the NID of the notification which is available in the email response. The next step was to see if something was obviously wrong with the notification.. like for example the requisition was approved from within application and hence the notification was discarded. This was achieved by querying up the records for this NID from wf_notifications table. Everything seemed OK there.

The next step was to scan through the log files for the workflow notification mailer in the production environment by searching for this specific NID. I was able to locate log entries which relating to sending out the mail with this NID. However, there was no record of any inbound mail with this specific NID. This enabled me to conclude that the production workflow notification mailer never was aware of this email which came to the inbox.

This can happen in following situation amongst others:

1. Some rules are defined at the mailbox level that move the specific mails automatically
2. Some other mailer is also working on the same mailbox
3. Human intervention

In our case, I verified that rules were not defined at the mailbox level and there was no human intervention.

The remaining candidate was some other notification mailer accessing the same mailbox.

That pointed out to the latest refresh that happened from this production environment. I verified in the target instance and i was able to locate these NIDs in the mailer logs. That suggests that the portion of the refresh process which addresses the Mailer related settings was overlooked.

Now, as far as the mystery of why some legitimate notifications were being sent to discard folder is concerned, here is what i discovered:

There are two scenarios possible:

1. Set of notifications (or the originating processes) in production, that were created before the target database was refreshed.
2. Set of notifications (or the originating processes) in production, that were created after the target database copy was made.

In the first case, since the target database already had the relevant data about the notifications and hence it actioned (i.e., either approved or rejected) in the target (dev) database.

In the second case, the dev database did not have the entries for those NIDs and hence was moved to discard as "bad" responses.

Finally, there was a third category which were processed by the legitimate production mailer which was all ok.

Hence, the mailes in "process" folder does not essentially mean that notification was updated appropriately in the production database. This needs to be addressed on a case to case basis and that is where my developer friends can contribute.

- Aravind Kamath

ORACLE error 1422 in get_sm_info

Problem:

Concurrent managers not coming up after a refresh. The error in the concurrent manager logs states "ORACLE error 1422 in get_sm_info". The ICM comes up but other managers dont.


Solution:

This issue happens when there are more than one Service Managers are configured in the source instance and target instance does not have as many nodes. For eg, if you have a 3 node source instance with PCP enabled, and you are refreshing a target instance with 1 node, then the database will have the entries for 3 Service Managers which will cause this issue.

One of the fix is to do a clean up of concurrent queues and then run autoconfig. However, a simpler fix to overcome this issue would be to set the target_node and nodename columns in fnd_concurrent_queues table to NULL for those Service Managers which will not be used in the target environment. Leave the Service Manager for the target instance alone - do not change anything there.

Other "dirty" solution is to set the "GSM Enabled" to "N" which will not use the Service Manager. This would not be the right thing to do as this changes the architecture.

- Aravind Kamath

Workflow Status Monitor Diagram Issue

Problem: Users reported issues with the view workflow status diagram functionality. When we use the Order Entry module and open up an order and try to see the workflow status diagram, an error page would be displayed stating something like "An unexpected error occured. Please get in touch with your system administrator".

solution:

First of all, we wanted to isolate the issue. So we enabled the FND: Diagnostics profile option to Yes. This will create "Click here" link which will provide you the details about the error. This in our case generated an error stack, with nullpointerexception.

Part of the error stack was as follows:

java.lang.NullPointerException at java.util.Hashtable.get(Hashtable.java:315) at oracle.apps.fnd.util.OracleTimezone.getTimezoneNameFromCode(OracleTimezone.java:171) at oracle.apps.fnd.framework.OANLSServices.getTimezoneName(OANLSServices.java:1635) at oracle.apps.fnd.wf.monitor.webui.GraphMonitorCO.processRequest(GraphMonitorCO.java:107) at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:581)

One of the notes suggested to set Server Timezone profile option and test the problem. We on the contrary unset the client timezone profile option which some users had set at the user level. This helped our case.

In this particular case users unknowingly had set these profile options. What I mean by that is - this profile option was set at the background when the users updated their preferences in the Home Page (in Framework Only mode). One of the preferences available is to set the timezone.

In any case, what we figured out in our encounter with this issue was that, once any of the users who have this client timezone set accessed the application, the application would misbehave and throw errors to all other users who do not have this parameter set as well. The other symptom of this issue was that users would click on OA Framework based responsibilities but the menus/navigation page would not load.

So, eventually, the right thing to do is to set the Server Timezone so that even if users set the client timezone, we should be OK. Besides, we have little control over the client timezone profile option, unless we take care of this in one of the following two ways:

1. Make the client timezone preference unavailable / unupdatable from the OA Framework perspective

2. Make the client timezone profile option unupdatable from the conventional profile option setting screen.

- Aravind Kamath

How to trace back to a session on a remote database

Here is another snippet which will help a DBA managing an oracle database on windows in performing day-to-day maintenance activities.

Problem: Need to identify the sessions running on the remote database which are connected to the target database over a db link.

Let me explain with an example. Lets define two databases A and B. There is a session in database B which originates from database A. The objective is to find out the details of the session in the database A.


Solution:

Analysis of the session in consideration in database A will reveal something like this:

SID SERIAL# OSUSER PROCESS PROGRAM MACHINE
191 7483 NA\oracle-usa 4800:3336 ORACLE.EXE UEAP102



In the above output, the program oracle.exe is the database B. the osuser field "NA\oracle-usa" is the service account that is used to run the database B on machine UEAP102.

Follow the steps below to locate the details of the originating session on database B on UEAP102:

Login to database B and query the v$session and v$process:

select s.* from v$session s, v$process p where s.paddr=p.addr and p.spid='3336';

This gives you the details of the originating session in database B. If you notice, the spid to be used in B is the second portion of PROCESS column in database A, which in our case is 3336 (4800:3336)


You can use a tool like pslist or process explorer to see the thread id (this will not reveal much information, but just for your understanding.)

- Aravind Kamath

How to drop a private database link

Problem: You are refreshing the database from production and need to drop the private database link owned by a user. You do not know the password for the schema (As a DBA the chances that you are not aware of the password is slim, but it can happen). You wish to leave the password unchanged. You cannot login as system or any other user with dba privilege and drop the link because "drop database link" does not allow you to qualify the db link name with the schema name. In other words you cannot do 'drop database link scott.testdblink;' .
Solution: The easiest way is to login as system or any other privileged user and change the password for the schema owning the db link. Then login as the schema owning the db link and drop the db link. For whatever reason if you need to keep the password untouched, you can follow these steps:
1. Login as system or any user that has DBA role.
2. Note down the encrypted password for the schema owning the db link using the DBA_USERS view. The password will be in the form of hexadecimal literals.
3. Change the password of the schema which owns the db link. for eg alter user scott identified by test;
4. login to the database as scott/test. Drop the private db link.
5. Log back in to the database as system user and issue the following command:Alter user scott identified by values ''; . Use the hexadecimal value you noted down in step 2 here. The password will be set back to what it was when you started this exercise.


The same approach can be used whenever you need to see things from a specific database user perspective for which you do not have the password.


- Aravind Kamath