Running Forms under Citrix 1

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)
/

How do you change the alert icons in Forms 6i? (and regedit sysinternals)

Easy, in only 2 steps:

Firstly, you need to edit the appropriate UI_ICON registry entry to add a new path for forms icons ahead of the default path.

Then, in this new directory you can then create 3 icons: caution.ico, stop.ico and note.ico.

When your forms application runs and displays an alert box, instead of the 3 default icons for each of the alert types, the icons you have specified will be displayed!

Further, should you wish to, many of the buttons in Forms builder (and presumably other tools) can be re-defined.

All you need to do is get hold of the regmon utility from sysinternals (Now distributed by Microsoft at www.microsoft.com/technet/sysinternals/utilities/regmon.mspx ) and set it to tell you about all the icon files that ifbld60.exe is attempting to open, and by correctly identifying which icon corresponds to which button, it is possible to replace the buttons with your own creations!