Oracle Observations

August 8, 2007

Running Forms under Citrix 1

Filed under: Citrix,Oracle forms — bigdaveroberts @ 1:59 pm

I’m sure everyone has their favorite scripts, either home grown or purloined to assist with database monitoring but there are always more things to check and new things to monitor.

The following scripts were developed for the analysis and monitoring of disconnection problems experienced after the system was converted into a three tear application by running the forms client under Citrix.

While developing the script I read the metalink article Note:274697.1 LOGOFF and LOGOFF BY CLEANUP Do Not Have Any LOGON Records in DBA_AUDIT_TRAIL and Vice-Versa.

The article defines its purpose to be:

This bulletin explains what are the differences between the LOGOFF and LOGOFF BY CLEANUP shown in SYS.AUD$ table and why they do not have any associated LOGON records in DBA_AUDIT_TRAIL and vice-versa.

While it includes examples of how normal logons and logoffs are recorded in theaudit table, it doesn’t explicitly indicate how disconnected processes are logged.

Thus given Oracles ‘vice versa’ comment above, I assumed that they were different and the timestamp on the ‘LOGOFF BY CLEANUP’ records would indicate the time that the process was terminated or the time the pmon process detected the dead process.

Having analysed the data collected, I came to the conclusion that my assumptions were wrong and it appears that rather than being different, the way Oracle handles disconnections is exactly the same way that it handles normal logoffs, with the logon record being cannibalised in the process.

The scripts:

The following is run whenever we suspect that there is a problem, which breaks down the disconnected sessions by client machine (Citrix server) and hour of the day:

SELECT terminal,
  TRUNC(logoff_time),
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 0, 1, 0)) zero,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 1, 1, 0)) one,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 2, 1, 0)) two,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 3, 1, 0)) three,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 4, 1, 0)) four,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 5, 1, 0)) five,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 6, 1, 0)) six,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 7, 1, 0)) seven,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 8, 1, 0)) eight,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 9, 1, 0)) nine,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 10, 1, 0)) ten,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 11, 1, 0)) eleven,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 12, 1, 0)) twelve,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 13, 1, 0)) thirteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 14, 1, 0)) fourteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 15, 1, 0)) fifteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 16, 1, 0)) sixteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 17, 1, 0)) seventeen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 18, 1, 0)) eighteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 19, 1, 0)) nineteen,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 20, 1, 0)) twenty,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 21, 1, 0)) twentyone,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 22, 1, 0)) twentytwo,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 23, 1, 0)) twentythree
  FROM dba_audit_trail
 WHERE action_name = ‘LOGOFF BY CLEANUP’
  AND logoff_time > TRUNC(SysDate)
 GROUP BY terminal, TRUNC(logoff_time)
/

The following is run every morning and added to a cumulative log which gives a breakdown of disconnected sessions by Hour by Day which is used to identify trends:

SELECT TRUNC(logoff_time),
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 0, 1, 0)) t0,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 1, 1, 0)) t1,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 2, 1, 0)) t2,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 3, 1, 0)) t3,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 4, 1, 0)) t4,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 5, 1, 0)) t5,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 6, 1, 0)) t6,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 7, 1, 0)) t7,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 8, 1, 0)) t8,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 9, 1, 0)) t9,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 10, 1, 0)) t10,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 11, 1, 0)) t11,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 12, 1, 0)) t12,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 13, 1, 0)) t13,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 14, 1, 0)) t14,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 15, 1, 0)) t15,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 16, 1, 0)) t16,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 17, 1, 0)) t17,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 18, 1, 0)) t18,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 19, 1, 0)) t19,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 20, 1, 0)) t20,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 21, 1, 0)) t21,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 22, 1, 0)) t22,
  SUM(DECODE(RTRIM(TO_CHAR( logoff_time, ‘HH24’ )), 23, 1, 0)) t23
  FROM dba_audit_trail
 WHERE TRUNC(logoff_time) = TRUNC(sysdate – 1 )
  AND action_name = ‘LOGOFF BY CLEANUP’
 GROUP BY TRUNC(logoff_time)
/

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: