Re: OTRACE Archeology

From: Shreepad Vaidya <Shreepad.Vaidya_at_alltel.com>
Date: Tue, 11 Jul 2000 4:58 -0500
Message-Id: <10555.111640@fatcity.com>

--=_sb2000/07/11-09-56-57-783=_sb
Content-Type: text/plain; charset=us-ascii

       Hi,
       Even we had faced similiar problems.

       I am attaching the entire resolution to the problem available
       on metalink.



       (See attached file: otrace.txt)


            Hope this helps.


                           shreepad


       I've been reviewing and rewriting some old cleanup scripts
       created by DBAs
       long since gone.

       For each 7.3 version database, they do the following:

            rm -f $ORACLE_HOME/otrace/admin/*.dat

            $ORACLE_HOME/bin/otrccref

       The first clears out three files, collect.dat, process.dat, and
       regid.dat.

       The second seems to reinitialize them.

       I've looked through the 7.3 docs, but can't find anything
       specific on this.

       Does anybody have any idea what these files are and is the
       above an
       appropriate thing to do on a nightly basis.  The files do seem
       to grow over
       time.

       Thanks.

       --
       Author: Smith, Andy
         INET: andy.smith_at_gwl.com

       Fat City Network Services    -- (858) 538-5051  FAX: (858)
       538-5051
       San Diego, California        -- Public Internet access /
       Mailing Lists
       ---------------------------------------------------------------
       -----
       To REMOVE yourself from this mailing list, send an E-Mail
       message
       to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
       and in
       the message BODY, include a line containing: UNSUB ORACLE-L


       (or the name of mailing list you want to be removed from).  You

       may
       also send the HELP command for other information (like
       subscribing).

--=_sb2000/07/11-09-56-57-783=_sb

Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment; filename="otrace.txt"

=20
=20
Bookmark Fixed font Go to End=20

Doc ID : Note:1020763.6=20
Subject: CONNECTION/QUERY TIME IS SLOW OR ORA-07337 WHEN STARTING DATABASE= =20
Type: PROBLEM=20
Status: PUBLISHED=20
Content Type: TEXT/PLAIN=20
Creation Date: 23-AUG-1996=20
Last Revision Date: 28-JUN-1999=20
Language: USAENG=20
=20

Problem Description:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

The connection time is extremely slow, and the "process.dat" file located in the "$ORACLE_HOME/otrace/admin" directory is unusually large.

Performance problems occur with SQL*Plus after Oracle7 Server 7.3.2.X is installed.

Symptoms:

Slow connection times through SQL*Plus by way of the client or directly on the server.
Performance degradations when querying through SQL*Plus.
On Dec Unix, ORA-07307 or ORA-07337 may occur on startup. (See BUG:374964)
A SQL*Plus session may return one or more of the following errors:

ORA-01034, ORA-07320, OS error 12, OS error 28

Problem Explanation:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

"Otrace", a new, common tracing mechanism, is automatically enabled in versions 7.3.X, and can result in large tracing files in the "$ORACLE_HOME/otrace/admin" directory. SQL*Net v2.3 has been instrumented with Oracle Trace, as have the RDBMS version 7.3.X and Forms version 5.0.

What is Oracle Trace?

Oracle Trace allows you to turn on collections of performance related data through a common interface for any trace instrumented product. Additionally, it automatically correlates data collected in one instrumented product such as SQL*Net, with data collected in another such as the RDBMS, so that you can view the performance of various parts of a logical thread. For example, you can get a view of a given transaction that shows the performance data for it on the client, on the network, and on the server.

Search Words:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

otrace, otrccref, regid.dat, EPC_DISABLED, sqlplus, hang, hangs, hanging, svrmgrl, lsnrctl, core dump, bus error, ORA-1034, ORA-7320, Digital, sqlnet, ORA-7307, ORA-7429, ORA-7337, 7.dat, process.dat

Solution Description:=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20 =20
This could be BUG:387185 which only occurs on RDBMS version 7.3.X.=20 =20
To Disable Oracle Trace (otrace):=20

Bring the listener and the database instance down prior to performing the steps listed below. =20

If the listener is running, shut it down. To verify if the listener is running, to stop it, or to start it, use the commands listed below:

    % lsnrctl status  (informs user if listener is running)
    % lsnrctl stop    (stops the listener process)
    % lsnrctl start   (starts the listener process)

If the database is running, shut it down with the following commands:

   % svrmgrl
   % connect internal
   % shutdown immediate
   % exit

Change your location to the "admin" directory.=20 =20 % cd $ORACLE_HOME/otrace/admin=20 =20
Ensure the process.dat, regid.dat, collect.dat, and facility.dat files are not greater than 5MB in size.

% ls -al *.dat

3. Delete the process.dat, regid.dat, collect.dat, and facility.dat

files with the following commands:

       % cd $ORACLE_HOME/otrace/admin
       % ls -al *.dat
       % rm process.dat
       % rm facility.dat
       % rm collect.dat
       % rm facility.dat
       % ls -al *.dat  (to verify the files have been removed)

4. Recreate process.dat, regid.dat, collect.dat, and facility.dat.

=20

       % cd $ORACLE_HOME/bin
       % otrccref  (this command recreates the *.dat files
                    to the default size)

5. Set the user's environment variable EPC_DISABLED in .profile,

.login, or .cshrc to disable otrace:

For Korn or Bourne Shell:

$ EPC_DISABLED=3DTRUE; export EPC_DISABLED

For C Shell:

% setenv EPC_DISABLED TRUE

*Note: These files will not be updated when users login.

6. For specific SQL*Net connections, modify the 'SID_LIST_listener'

clause in the 'listener.ora' file. Set EPC_DISABLED=3DTRUE by adding it to the SID_DESC in each 7.3 database after the=20 (ORACLE_HOME=3D...) clause.

Example:

BEFORE:

      SID_LIST_LISTENER=3D


       (SID_LIST=3D

        (SID_DESC=3D
          (SID_NAME=3DDB1)
          (ORACLE_HOME=3D/oracle/OFA_base/app/oracle/product/7.3.2)

        )
        (SID_DESC=3D
          (SID_NAME=3DDB2)
          (ORACLE_HOME=3D/oracle/OFA_base/app/oracle/product/7.3.2)

        )
      )

AFTER:

      SID_LIST_LISTENER=3D


       (SID_LIST=3D

        (SID_DESC=3D
          (SID_NAME=3DDB1)
          (ORACLE_HOME=3D/oracle/OFA_base/app/oracle/product/7.3.2)
          (ENVS=3D'EPC_DISABLED=3DTRUE')
        )
        (SID_DESC=3D
          (SID_NAME=3DDB2)
          (ORACLE_HOME=3D/oracle/OFA_base/app/oracle/product/7.3.2)
          (ENVS=3D'EPC_DISABLED=3DTRUE')
        )
      )

6. Restart the database and SQL*Net listeners.

To Enable Oracle Trace (otrace):=20

--------------------------------=20

=20
1. Unset EPC_DISABLED=20
=20

For Korn or Bourne Shell:=20
=20

$ unset EPC_DISABLED=20
=20

For C Shell:=20
=20

% unsetenv EPC_DISABLED=20
=20
2. Change your location to the "bin" directory=20 =20

% cd $ORACLE_HOME/bin=20
=20
3. Run the "otrccref" command=20
=20

% ./otrccref=20
=20

This recreates the regid.dat and process.dat files to the default sizes of 14K for regid.dat and 330K for process.dat.

References:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Received on Tue Jul 11 2000 - 04:58:37 CDT