Statspack can hang your database

It is Thursday evening and am already dreaming of the weekend. I would leave office in another 15 min. I began installing statspack on my production database. Its a straight-forward process. Just run the $ORACLE_HOME/rdbms/admin/spcreate.sql. I had done this over several times on several databases. I was sure this will not take more time and definitely will not throw surprises. I was never this wrong.

The statspack got installed smoothly. What I noticed was that there were eight new invalid objects in the SYS schema. All of them were DBMS_ packages. I quickly started compiling them.

SQL> ALTER PACKAGE '&pack_name' COMPILE BODY;

Wow, i see all of them have compiled without errors. I was happy until I re-checked the invalid objects. There were now eighteen invalid objects in the SYS schema. All were again DBMS_* packages. Most of them were DBMS_AQ* related packages. I missed my heartbeat. This was the production database and almost at the end of the business hours. I didn’t see this issue in the test environment. I didn’t want to end up fixing this at this time.

There is a bug in the metalink that says after installing statspack using spcreate.sql there is a possibility of DBMS_UTILITY package getting invalidated. This bug is for 8.1.7.4 version and on some other platform. But I am on Linux and 9.2.0.4 DB and I still see the similar issue. Thereafter all requests that depend on this package will hang. In my case this package was DBMS_AQ. All Workflow Background Process requests were pending. They were engaging all the Standard Managers and any new concurrent request will join the pending queue. Within 2 hours i saw there were 55 pending requests.I knew the database was hanging.

While all the other invalid packages were validated, the DBMS_AQ validation hangs interminably for almost an hour and then times out. I will have to validate this package and only then will the requests like ‘Workflow Background Process’ will progress. And i can’t validate this package until the requests holding the lock on the object releases it, which is unlikely. Catch-22 situation! I decided I will bounce the application and database.

Convincing the client was going to be a difficult job. Obviously he will ask questions.
Q) Why was the object invalidated?
A) Because we had installed Statspack.
Q) But Statspack is supposed to aid us in diagnosing inherent issues, not to create new issues?
A) Yes…but…you see…there is a bug…
Oh damn all!

The client agreed to bounce the database. But guess what? the trouble is not over yet. The concurrent managers decline to go down smoothly. Because the new ‘Shutdown Concurrent Manager’ request itself joins the Pending queue. What to do? Terminate the requests, and kill the managers’ pids? What if there are any important requests running that will error out due to this? There were a lot of EDI jobs running and the Client was hesitant to let me kill it. Ouph, finally i killed (yes killed!) the managers and terminated all the requests. Brought down all the services, bounced the db, validated the invalid DBMS_AQ package, ran cmclean on the db, brought up the services. The application came back to life with new vigour and zest. The client appeared happy to see his baby playing antics again. I am sure the Client doesn’t remember the issue anymore, but i will.

This brings me to the next question. How can i terminate all the managers when they do not go down smoothly? Especially when there are requests in ‘Running’ status? They will end up with ‘Error’ status upon forceful termination of managers. It should be able to restart the requests from where they ended. Is it possible? Let’s see if it is possible in my future articles.

Moral: If things ought to go wrong, they will.

————————————————

GetContext – In the context of the CONTEXT_FILE

Abstract: Presents a java code that returns the value of the parameter in the CONTEXT_FILE on the lines of UpdateContext class file used internally by AutoConfig.

Application is a fascination. It is the combined effort of many people, many technologies. Java is definitely a major technology the E-Business Suite relies heavily on. We DBAs indulge mainly in SQLs and Shell/Perl scripting. We do not disturb the Java code unless there is a necessity. Even then, we play safe. (Actually I haven’t found a need to open up the Java code in my experience so far.)

I was curious to look into the JAVA code that the Oracle uses. There is a wealth of code in the $JAVA_TOP/oracle directory. It needs to be explored. With a proper Decompiler there is a wealth at our disposal waiting to be amassed. I used JAD (Java Decompiler) to debug certain classes in the $JAVA_TOP/oracle/apps/ad/context directory and found the code utmost interesting.

My interest was partly aroused by the article I read in the blog.
The article lists out a few tools to broaden our understanding on the AutoConfig tool. Of the special interest I found was the one below:

[Beware: Do not forget to take a backup of the XML file before running this script. Better still, copy the context_file to a temporary location, and use that as the XML file in the below script]

java -classpath "${CLASSPATH}:${CMDDIR}/java/adconfig.zip" oracle.apps.ad.context.UpdateContext ${HOME}/admin/SID.xml s_appsEnvName "SHYAM"

If you check now in the (whichever) XML file you had given as input, the s_appsEnvName value has changed from whatever value it had earlier been to the new value “SHYAM”.

This is interesting, and is what is used internally by the AutoConfig script. But what set me thinking was the need to get the value of the parameter from the XML file. With the above script we update an existing value. What if I need to know the current value of, say, s_appsEnvName parameter?

I have many ways of achieving this. The easiest would be to grep for that parameter name in the CONTEXT_FILE. The below demonstrates:

$ grep s_appsEnvName DEV_dev.xml
DEV

This is quite sufficient and it serves our purpose. If we are content, we need no more.

Of course, we can embellish the output with frills. We can have an XML parser from Perl language (CPAN) and extract the value only for the given parameter. And, of course, there are better ways. Simpler still, I can pass on the o/p to the shell pipe and awk/cut the relevant o/p.

The way I chose to achieve this might seem strange to all. I chose to write a Java script to get me the value of the parameter that I pass. What makes this Java script unique is that it is modeled around the Java classes the Oracle has in the $JAVA_TOP/oracle/apps/ad/context directory. Yes! And I used the JAD Java Decompiler to decompile the UpdateContext class file and modeled my program along those lines. I, in fact, reused the classes and the code to the maximum. There is hardly any code of mine, all I did was reuse.

Programming is fun, especially so when the program behaves well!

The entire program will be pasted at the end for reference.

Compile the Java code using the below syntax:

$ javac GetContext.java
$ cp –p GetContext.class $JAVA_TOP/oracle/apps/ad/context/
$ java oracle.apps.ad.context.GetContext
[you will see the usage syntax]
$ java oracle.apps.ad.context.GetContext $CONTEXT_FILE s_appsEnvName

Please note that Oracle might not approve of placing this class file in the $JAVA_TOP/oracle/apps/ad/context directory. We may create a custom directory and redirect the CLASSPATH to that directory as well, I think. But I don’t know how to do it, yet.

The utility I wrote might not be useful to any of the readers. Yet it demonstrates that there is the important code hidden in the jungle out there waiting to be explored. As they say, to make an omelette you have to break an egg!

And the code, of course…
package oracle.apps.ad.context;
import java.io.File;
import java.io.PrintStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;
import oracle.apps.ad.util.SystemCheck;
public class GetContext
{
public GetContext() {
}
public GetContext(String param) throws Exception {
if (param == null)
throw new IllegalArgumentException("sVars cannot be null/empty");
OAVars oavars = new OAVars(param);
AppsContext appscontext = new AppsContext(param);
String s4 = oavars.getVal("s_at");
System.out.println("Appl Top = " + s4);
}
public GetContext(String ctxFile, String param) {
if (param == null)
throw new IllegalArgumentException("sVars cannot be null/empty");
if (ctxFile == null)
throw new IllegalArgumentException("sVars cannot be null/empty");
OAVars oavars = new OAVars(ctxFile);
AppsContext appscontext = new AppsContext(ctxFile);
String s4 = oavars.getVal(param);
System.out.println(param + " = " + s4);
}
public static void main(String args[])
{
if(args.length == 2)
{
try
{
new GetContext(args[0], args[1]);
}
catch(Exception exception)
{
exception.printStackTrace();
}
System.exit(0);
return;
}
else if(args.length == 1)
{
try
{
new GetContext(args[0]);
}
catch(Exception exception)
{
exception.printStackTrace();
}
System.exit(0);
return;
}
else
{
exitWithUsage();
return;
}
}
public static void exitWithUsage()
{
System.out.println("nnusage:n");
System.out.println(" java oracle.apps.ad.context.GetContext");
System.out.println(" CONTEXT NAME n");
System.out.println(" where:");
System.out.println(" CONTEXT = the path to the Oracle Applications Context file");
System.out.println(" NAME = the name of the variable whose value is required");
System.exit(1);
}
}
———————————————————————-

Cheers!

Improper ‘JAVA’ version can fail adcfgclone.pl

In one of my recent clones (11.5.10.2), I saw an issue whose fix was interesting.

When I run the command ‘perl adcfgclone.pl appsTier’, it would run well until 50% and then it errors out with the following message:

RC-50004: Fatal: Error occurred in ApplyAppsTier:
StackTrace -
java.lang.reflect.InvocationTargetException
at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:68)
at java.lang.Thread.run(Thread.java)
ERROR while running Apply…
ERROR: Failed to execute /u10/app/appsymerpp/symerppcomn/clone/bin/adclone.pl

There was also an error statement similar to the below:

ERROR in CLASSPATH:
$COMMON_TOP/clone/bin/../jre/lib/classes111.zip

Most of the fixes in the Metalink suggested that I copy the classes111.zip from $FND_TOP/java/3rdparty/ (note, the locations are not correct. I am relying on my bad memory. To locate correctly the file one can try this command ‘find $FND_TOP –name “classes111.zip”’ from the unix prompt)

Needless to say (else why would I blog!), it didn’t work.

My troubleshooting would almost always stop with Metalink. I never had to think beyond it. Well, it wouldn’t be the case this time. Almost at my wit’s end, I did a grep for classes in the adcfgclone.pl script. Voila, things began to take shape! See the below code:

if ( versionCompare($JAVAVERSION,$JAVA13 )>=0) {
if (-e “$PROG_PATH../jlib/classes12.jar”) {
$JDBCCLASSES = “$PROG_PATH../jlib/classes12.jar”;
} elsif (-e “$PROG_PATH../jlib/classes12.zip”) {
$JDBCCLASSES = “$PROG_PATH../jlib/classes12.zip”;
} else {
display_error(“$PROG_PATH../jlib/classes12.jar”);
}
} else {
if ( -e “$PROG_PATH../jlib/classes111.zip”) {
$JDBCCLASSES = “$PROG_PATH../jlib/classes111.zip”;
} else {
display_error( “$PROG_PATH../jlib/classes111.zip”);
}
}

If you will notice in the above code snippet, classes111.zip will come into effect only if versionCompare function returns value less than zero.
I also thought classes111.zip is an obsolete set of classes and currently classes12.zip is in vogue. In actuality, the site says:
“classes111.zip contains the classes for use with JDK 1.1.x–all the JDBC driver classes except the classes necessary for globalization support. classes111.zip also contains Oracle extensions that allow you to use JDBC 2.0 functionality for objects, arrays, and LOBs under JDK 1.1.x.
classes12.zip contains the classes for use with 1.2.x, 1.3.x, and 1.4–all the JDBC driver classes except the classes necessary for globalization support.”

So I should be using classes12.zip and not classes111.zip. Then my java version need to be higher than 1.1. Is my java version not even 1.3? I am surprised. How does adcfgclone.pl find out my java version? This can be verified from the below code (taken from adcfgclone.pl):

#
# Calculate the jre location
#
my $JREDIR=”jre”;
if ( -d “$PROG_PATH../jre/1.3.1″ ) { $JREDIR=”jre/1.3.1″; }
# For AIX (bug 2886910)
if ( -d “$PROG_PATH../jre/jre” ) { $JREDIR=”jre/jre”; }
if ( -d “$PROG_PATH../jre/1.3.1/jre” ) { $JREDIR=”jre/1.3.1/jre”; }

# Toggle between jre or java executable
my $JAVA;
my $JAVA_TOP;
if ( -e “$PROG_PATH../$JREDIR/bin/jre${ext}” ) {
$JAVA = “$PROG_PATH../$JREDIR/bin/jre${ext} $NoJit”;
$JAVA_TOP = “$PROG_PATH../$JREDIR”;
} elsif ( -e “$PROG_PATH../$JREDIR/bin/java${ext}” ) {
$JAVA = “$PROG_PATH../$JREDIR/bin/java${ext}”;
$JAVA_TOP = “$PROG_PATH../$JREDIR”;
} else {
printf “ERROR: cannot find jre or java under
$PROG_PATH../jre/binn”;
printf ” Please verify that you ran adpreclone.pl successfullyn”;
die “n”;
}

The below if statement in the above code suited me:

if ( -e “$PROG_PATH../$JREDIR/bin/jre${ext}” )

The $PROG_PATH here is the path of the program (adcfgclone.pl) and so is $COMMON_TOP/clone/bin. Hence the JAVATOP will be $COMMON_TOP/clone/jre and the JAVA executable is $COMMON_TOP/clone/jre/bin/jre.

Interesting thing to note here is both java and jre executables are referred here with a single variable JAVA. You will have any one of either of them.

So, what was the version of my jre (or JAVA variable in the program)
The snapshot of the same is below:

[applmgr@sandbox bin]$ ./jre -version
Illegal option: -version
Java(tm) Runtime Loader Version 1.1.8

And, 1.1.8 is smaller than 1.3. This can be easily understood if we know how strcmp function works in C language.

What can be done now! Upgrade JRE? Let proper JAVA version be introduced into the location? Will it fix the issue?

A quick hack on my part laid my doubts to rest. What I did was this.

In the $COMMON_TOP/clone/jre/bin directory, there was no java executable. Only jre executable was present and its version (as shown earlier) was 1.1.8.
I had a java 1.4.2_13 version installed in the location $COMMON_TOP/util/j2sdk1.4.2_13.
I backed up the jre executable in the $COMMON_TOP/clone/jre/bin location and created a symbolic link jre to $COMMON_TOP/util/j2sdk1.4.2_13/bin/java using the below commands.

$ cd $COMMON_TOP/clone/jre/bin
$ mv jre jre.orig
$ ln –s $COMMON_TOP/util/j2sdk1.4.2_13/bin/java jre

This created a soft link jre that actually pointed to the java executable version on 1.4.2_13. This should be sufficient for adcfgclone.pl to think that the proper version of java is installed.

The script adcfgclone.pl ran successfully after that.

Summary:
The adcfgclone.pl script fails due to incorrect classes111.zip file. Upon close look in the adcfgclone.pl script, this class was being called because the java/jre version was less than 1.3 in the $COMMON_TOP/clone/jre/bin location. When I created a soft link (by name jre) in this directory that is pointing to the java executable with version 1.4.2_13, the script ran successfully.

Lesson:
There are more ways than one to achieve something in the Oracle applications.

Acknowledgement:
Thanks are due to Swarna K Duttagupta for pointing me to the document.

What am I doing?

Friends, I am currently trying to blog my attempts to resolve an issue that i faced during a clone. The issue was with the Java and its classes. The interesting part of the issue was its troubleshooting and the fix, of course.

Sent the article to some of my friends for review. Will update this blog as soon as i get a positive feedback.

P.S: I just finished my 10g OCA (1z0-042)!
Feeling great. Planning to take the OCP very soon.

Cheers!

Hello World

Hi to all,

Here is Yet-Another-Blog-On-Oracle (YABOO!).
You may find something important here, though i will not promise.
There might be something un-important here, but i will not threaten.
There might be nothing (updated) here for days too. Bliss!

Yet, whatever i will blog will be true, sincere and honest.
Of course, there is a BattleAgainstAnyGuesses (BAAG) too!

Good Luck!