Adpatch seems to hang?

While applying a patch to a cloned environment adpatch seemed to hang on the following line:

Attempting to instantiate the current-view snapshot...

If you perform a search of metalink you will get a couple of hits which may solve your problem. One of the problems is that the AD module level is lower than the required version for the patch. The other note mentions that it could be a temp tablespace issue. In my case, both of those checked out ok.

Before I continue, what is a snapshot?

There are two types of snapshosts, APPL_TOP and global. APPL_TOP snapshots contain version of files and patches applied within that APPL_TOP. A global snapshot contains the same information but for the entire environment, ie. all APPL_TOPS.

The global view snapshot is used when you use Patch Wizard to determine whether or not a patch has been applied. APPL_TOP snapshots are using by autopatch (adpatch) to determine if all prerequisite patches have been applied. Each time you apply a patch AutoPatch updates the current view snapshot. I believe it may even create a new current view snapshot and just replace the existing one.

Additionally there are two types of snapshots, current view and named. A named snapshot is just a copy of the current view snapshot at a given point in time. Patch wizard and AutoPatch use current view snapshots.

To access snapshot information launch adadmin, select option 2 - Maintain Applications Files menu, select 5 - Maintain snapshot information and you will see the menu below:

         Maintain Snapshot Information
   -------------------------------------------

  1.    List snapshots

  2.    Update current view snapshot

  3.    Create named snapshot

  4.    Export snapshot to file

  5.    Import snapshot from file

  6.    Delete named snapshot(s)

  7.    Return to Maintain Applications Files menu

Back to the problem of adpatch seeming to hang while instantiating a current-view snapshot. Since this is a cloned environment, a snapshot doesn't exist yet for the APPL_TOP. So before AutoPatch can check if prerequistite patches have been applied, it must create a snapshot. This process can take 1-2 hours depending on how fast your servers are. You can avoid this by running "Update current view snapshot" via adadmin after you clone.

I should add, that in my experience I've only encountered this problem a few times. Most of the patches I apply to a cloned environment are quick one-offs with no pre-reqs. Large patching efforts such as family packs or patches with pre-reqs may experience this problem. I haven't tried, but if your crunched for time you may be able to bypass adpatch updating the current view snapshot by specifying "adpatch options=noprereq" to skip the prerequisite check.

Monitoring Forms sessions in OAM

One of the monitoring features of OAM, Oracle Applications Manager is the ability to view forms sessions and details about them. Login to OAM and proceeed to the Applications Dashboard -> Performance (tab). You will see a summary of activity similiar to the following:




Note: If you see a 0 next to "Forms Sessions" your environment may not be setup correctly. Skip down to the bottom of this post. As well, I've blanked out alot of information that may be sensitive. I'm in the process of setting up an R12 demo environment and once that is done take screenshots from there so I don't have to worry about making the company I work for mad.

From there you can click on the link to the right of "Forms Sessions" and you will be brought to a page similar to the following:



On this page you will see a list of the forms sessions, the usernames of those who launched the form, responsibility used. As well as an indication of how much resources the form is consuming such as memory, cpu, and IO. From here you can drill down into each forms session by clicking on a link in the AUDSID column or by selecting the row and click on the "Session Details" button. That will bring you to a screen similar to the following:



This page will provide you with more session information (sid, serial#, etc) and session wait statistics. If you'd like to collect more information about this session you can also enable tracing and view the output.

Very useful.

Forms Sessions showing a 0?

Check the profile option "Sign-on: Audit level" and make sure it is set to "Form". This is required in order to monitor forms sessions.

In case your curious, there are 4 valid values for this profile option: None, User, Reponsibility and Form. As you progress from None to Form additional information is collected and stored.

• None - Obvious, nothing is audited.
  
• User - FND_LOGINS table gets updated with one record per user session.
  
• Reponsibility - Same as User, as well, FND_LOGIN_RESPONSIBILITIES will be updated with 1 record for each responsibility used during the session.
  
• Form - Same as user and responsibility, plus FND_LOGIN_RESP_FORMS will be updated with one record for each form selected during the session.
  

As with any audting, you should determine how long you require the data and purge/archive it. In order to purge data in the above tables you need to schedule the concurrent program, "Purge Signon Audit Data". This program requires one parameter, Audit Date. All data older than this date will be deleted. Note: this program deletes data in the above tables as well as FND_UNSUCCESSFUL_LOGINS.

Beware: Enabling auditing does have a slight impact on performance.

Note: Purge Signon Audit Data executes $FND_TOP/sql/FNDSCPRG.sql

ORA-01555 and Automatic Undo

In the past couple of years of support Oracle E-Business Suite I have only hit ORA-01555: snapshot too old twice... About a year ago and again today.

Before you start to troubleshoot you need to gather some information:

• If your unaware of the current settings for undo you should check. One way is to use the show parameter command but you can also check the database initialization parameter file or spfile.
  
  

  
  SQL> show parameter undo
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- -------------
  undo_management                      string      AUTO
  undo_retention                       integer     1200
  undo_suppress_errors                 boolean     FALSE
  undo_tablespace                      string      APPS_UNDOTS1
  

  
  
  
• Determine which time the error occurred from the alert log, which will help you narrow down the time for a query we will use later. You alert log should have an entry similiar to:
  
  

  Sat Aug 16 23:05:41 2008
  ORA-01555 caused by SQL statement below (Query Duration=1506 sec, SCN: 0x0000.a8d6d835):
  Sat Aug 16 23:05:41 2008
  SELECT X FROM Y WHERE A=B
  

Now that you the automatic undo settings and the time of the error execute the following query (substitute the timestamp of the error in your alert log in place of the one below, with some buffer at either end):

SQL> select to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNDOBLKS, UNXPSTEALCNT, EXPSTEALCNT ,
SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN
from v$undostat
where begin_time between to_date('08/16/2008 22:30:00','MM/DD/YYYY HH24:MI:SS')
                 and to_date('08/16/2008 23:30:00','MM/DD/YYYY HH24:MI:SS')
order by begin_time;


BEGIN_TIME       UNDOBLKS UNXPSTEALCNT EXPSTEALCNT SSOLDERRCNT NOSPACEERRCNT MAXQUERYLEN
---------------- -------- ------------ ----------- ----------- ------------- -----------
08/16/2008 22:32   10394             0           0           0             0         513
08/16/2008 22:42    5693             0           0           0             0         212
08/16/2008 22:52   10853             0           0           0             0         451
08/16/2008 23:02    8950             0           0           0             0        1653
08/16/2008 23:05   11284             0           0           1             0        1506

5 rows selected.

Based on this query we can determine a couple of things such as the number of transactions which required UNDO during the time period. How many undo blocks they required and whether there were any (successful) atempts to steal undo space from other transactions.

The query also shows which transactions received ora-1555 errors (SSOLDERRCNT) and whether or not there was enough space in the UNDO tablespace (NOSPACEERRCNT). If you look at the row above with a timestamp of 23:05, this is the transaction which produced my ora-1555 error. We know this because the SSOLDERRCNT column for this row has the value 1 and the timestamp is the same as the one in the log file. Since the MAXQUERYLEN for this transaction (1506) is greated than the undo_retention (1200) one solution would be to increase undo_retention.*

*There are other ways to solve this problem to try and avoid ora-1555 errors such as transaction tuning, etc. More detail can be found in Note:10630.1

For this particular case I will set it to 2000 and monitor the undo tablespace. By increasing undo_retention undo is kept for a longer period of time, so you should monitor the undo tablespace to make sure you have enough room. Oracle 9i+ has advisors which can help you determine the impact of raising undo_retention. The following screen shot is taken from Oracle 9i Enterprise Manager but you can also find better graphs in 10G Database or Grid Control:




*Note: this graph is not from same database as above. Its just for informational purposes.

We can determine from this graph if we were to double undo retention from 30 to 60 minutes we would need at most 70MB of space. This estimate is based on undo activity since the database was initially started.

If the UNDO tablespace was too small, you would see a value in the NOSPACEERRCNT column. Take a look at the following row with a timestamp of 23:22:

BEGIN_TIME       UNDOBLKS UNXPSTEALCNT EXPSTEALCNT SSOLDERRCNT NOSPACEERRCNT MAXQUERYLEN
---------------- -------- ------------ ----------- ----------- ------------- -----------
08/16/2008 23:22    1034             10          0           1             1        1428

This row has a values for UNXPSTEALCNT and NOSPACEERRCNT. This means the transaction was not able to obtain space from the UNDO tablespace and attempted to steal 10 blocks (UNXPSTEALCNT) from other transactions. In this case we need to add space to the UNDO tablespace because it was not large enough to meet the undo_retention requirement.

Statistics, Concurrent requests and Terminations

There is a script on Metalink which can help you automate gathering statistics for an E-Business Suite environment. As you are probably aware in an EBS environment you have to use FND_STATS or some predefined concurrent programs such as "Gather Schema Statistics".


Subject: coe_stats.sql - Automates CBO Stats Gathering using FND_STATS and Table sizes
Doc ID: Note:156968.1

This note contains a script which verifies statistics and generates a script and report which lists all the tables that need to be analyzed. Beware that coe_stats.sql automatically executes the dynamic script coe_fix_stats.sql script at the end of its execution. So make sure you comment out the line "START coe_fix_stats.sql" if you don't want this to happen.

The report generated also contains some information about the table. If its partitioned, number of rows (at the time it was last analyzed), sample percent, etc.. As well as "Days since last analyze". Immediately I noticed that the time since the tables were last analyzed was over a week. Our main Gather Schema Statistics job is scheduled to run every weekend.

What does this have to do with terminations? In a previous post I mentioned that our environment crashed as a result of bug 5907779. This happened as our weekly concurrent request to gather statistics was running. After the environment was bounced this job restarted but it was now during business hours. Statistics should always be gathered at periods of low activity because it causes performance issues. So I canceled the request via OAM, which changed the status to Phase: Completed, Status: Terminated.

The side affect to terminating a job, which had slipped my mind* is that it will not be rescheduled. I'm not sure why that happens, it doesn't make much sense to me but it appears to be by design. The solution is to reschedule the job.

* Apparently 8hrs of sleep in the past 72hrs can have that affect. Partly due to a last night out while on vacation, traveling, falling ill and being paged at 3am.

In summary, the goal of this post was to point out a useful metalink note and refresh your memory to reschedule canceled requests if you need them to run again.

A Smart Performer

Alex asked a question on his blog, "As a Manager who would you prefer - a smart performer or a hard worker?" I am not a manager but my personal preference would be to work with a smart performer. Over the past 10 years i've probably worked with over 100 dbas, so its probably safe to say i've seen a pretty broad range of expertise.

I personally find hard workers who don't produce results, frustrating to work with. At the end of the day, if I consistently have to solve issues then they add no value. I might as well do it all myself. I should add, I expect questions as a senior DBA since thats part of the job. But I expect junior level questions from junior members of the team, not senior. They say there is no such thing as a stupid question and I agree. However, if your a senior resource then you should be able to find the answer to junior questions very quickly yourself.

This debate has cropped up on mailing lists, forums many times over the past year. The general consensus seems to be that managers prefer a hard worker who has social skills vs a smart performer who may be a little socially inept. I've worked with my share of socially inept DBA's and even tho at times they can make you feel like dirt stuck in the crack of their shoe, at least there is an opportunity to learn from them. Usually these DBA's have that attitude for a reason and most times its because of managements inability to 'fix' hard workers who produce no results or consistently repeat past mistakes.

So to answer Alex's question, "Or a fireman who has a smart solution, extinguishes the fire in two hours and saves most of your house. Which one do you choose?" I choose the the fireman who saves my house, even if he curses on me for being a moron while doing so.