HP OpenVMS Systems

Assistance with third-party package? (Oracle)

» close window

The Question is:

 
We have a Alpha 2100 Server to run Oracle.
But I find that the Paged Dynamic Memory has been used over 80%.
 
But when I showed system, the system would not
over 200 process.
 
Besides, after I restart the system in about 1999/06, and the Paged Dynamic
Memory only used about 40%. But when the time passed, the Paged Dynamic
Memory was used more and more. But the process didn;t increase.
 
And I use analyze/system to observe the Paged Dynamic Storage Pool, I find
the LNM use more than 45%.
 
please tell me how to release the resource.
 
Thank you!
 
OS: Open VMS 6.2-1H3
 

The Answer is :

 
  Please contact Oracle Support for assistance with Oracle products.
 
  If this problem is one known to Compaq and to Oracle support, the
  command SHOW LOGICAL/STRUCTURE will probably reveal a large number
  of logical name tables with names of the form "TNS_pid" where pid is
  a process id (PID). There was a problem in some versions of Oracle
  SQLnet which resulted in these tables being left behind after the
  creating process had terminated. They would therefore accumulate,
  consuming PAGEDYN until the system was rebooted (or PAGEDYN was
  depleted enough to hang or crash the system).
 
  Oracle support can provide you with a permanent fix for this problem.
 
  The attached command procedure may also be used to detect orphaned
  logical name tables and delete them. Please read all the comments
  carefully before using it!
 
 
$!
$! 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
$!
$! PROCEDURE
$!
$! First list all shareable logical name tables
$!
$ SHOW LOGICAL/STRUCTURE/OUT=LNMLIST.TMP
$!
$! Filter for Oracle logical name tables
$!
$ SEARCH LNMLIST.TMP "(TNS_" /OUT=ORACLE_TABLES.TMP
$ DELETEXX LNMLIST.TMP;
$ OPEN/READ in ORACLE_TABLES.TMP
$!
$! 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.
$!
$   ON WARNING THEN CONTINUE
$   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'"
$     DEASSIGN/ALL/TABLE='Table'
$     DEASSIGN/USER/TABLE=LNM$SYSTEM_DIRECTORY 'Table'
$   ENDIF
$ GOTO LOOP
$ Finished:
$ CLOSE in
$ DELETE ORACLE_TABLES.TMP;
$ EXIT
 

  

     

     
answer written or last revised on ( 30-SEP-1999 )
     
» close window