HP OpenVMS Systems

ask the wizard
Content starts here

Oracle and INSFMEM Errors?

» close window

The Question is:

Is it possible to prevent PAGEDYN becoming to fragmented during Oracle
 processing. LNM-blocks in PAGEDYN growing to more than 70% and would never be
 released again.

The Answer is :

  The OpenVMS Wizard suspects you might be accumulating logical name
  tables -- please contact Oracle for a simple fix for this (known)
  leak in Oracle.  Please also contact Oracle for any issues that
  might be specific to the use and the operation of Oracle software.
  Here is a command procedure that can clean up the stale logical name
  tables that can accumulate:
$! Command procedure to clean up logical name tables left by Oracle
$! processes in order to recover lost PAGEDYN
$! Reported against Oracle V7.1.4
$! When an Oracle process is created, a logical name table is also created
$! to pass information to the new process. Since this is a permanent,
$! shareable table, it is not deleted when the process terminates. Over
$! time, the accumulation of logical names will deplete PAGEDYN to the point
$! that the system hangs or crashes.
$! The logical name tables have names of the form TNS_xxxxxxxx where xxxxxxxx
$! is the PID of the process for which it was created. It is therefore quite
$! straightforward to detect "orphaned" tables by testing to see if the
$! owner process still exists.
$! WARNING - The SHOW LOGICAL/STRUCTURE command is used to generate a list
$!  of all shareable logical name tables on the system. Since the command
$!  takes out the LNM spinlock for the duration of the command, it will
$!  hang most user processes for its duration. If there is a large accumulation
$!  of logical name tables, this can be a significant time. One report was
$!  for a hang of 20mins when displaying 10MB of logical names.
$!  If this procedure is executed frequently, the duration of the hang should
$!  be minimal. Note that the size/response time of the command is non-linear.
$!  Requires SYSPRV and/or BYPASS privilege
$!   Author: John Gillings
$!           Systems Software Consultant
$!           Compaq Customer Support Centre
$!           Sydney, Australia
$!   email:  John.Gillings@Compaq.com
$! First list all shareable logical name tables
$! Filter for Oracle logical name tables
$! Scan Oracle tables
$ loop: READ/END=Finished/ERROR=Finished in Table
$!    Get the table name by removing all whitespace and brackets
$   Table=F$EDIT(Table,"COLLAPSE")-"("-")"
$!    Extract PID from table name
$   pid=F$ELEMENT(1,"_",Table)
$!    Ignore if process still exists. If process does not exist, the
$!    F$GETJPI call will fail with %SYSTEM-W-NONEXPR, nonexistent process
$!    and thus fall through. We will also fall through if there is any
$!    mismatch between the input and output PIDs. This should never
$!    happen, but it's safer code.
$   IF F$GETJPI(pid,"PID").EQS.pid THEN GOTO loop
$!    Sanity check PID format, make sure it's a valid, non-zero, 8 digit
$!    hex string. F$INTEGER will return 0 if there are any non-numeric
$!    characters in the input string
$   IF F$INTEGER("%X''pid'").EQ.0.OR.F$LENGTH(pid).NE.8
$   THEN
$!    Don't know what this table is, output message about it
$     WRITE SYS$OUTPUT "Unrecognised table name ''Table'"
$   ELSE
$!    Passed all tests, this must be an orphaned logical name table.
$!    Delete the logical names in table and the table itself.
$!    Note that the DEASSIGN/ALL should be redundant, but again, it's
$!    safer to be explicit
$     WRITE SYS$OUTPUT "Deleting table ''Table'"
$ Finished:
$ CLOSE in

answer written or last revised on ( 29-MAR-2002 )

» close window