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