C
Troubleshooting Oracle Trace

This appendix lists the problems you might encounter while using Oracle Trace. Solutions to these problems are provided, as well as a section on how to isolate problems encountered while using Oracle Trace. Sections in this appendix include:

Typical Problems Encountered While Using Oracle Trace

Table C-1 lists the symptoms Oracle Trace can exhibit, provides solutions, and refers you to the appropriate section for additional information. The table is also divided into sections which reflect how you would use Oracle Trace in the context of a collection.

Table C-1 Typical Problems Encountered While Using Oracle Trace

Symptom	Solution
Prior to Scheduling Collection
"Authentication User" error during product discovery.	Preferences for node are invalid or not set. See "Oracle Enterprise Manager Configuration".
Products not discovered.	Product definition files might be missing. See "Oracle Trace Configuration".
"Failed to connect to agent" during product discovery.	Cannot connect to Oracle Intelligent Agent. See "Network Configuration". See "Oracle Intelligent Agent (UNIX Specific)". See "Oracle Intelligent Agent (NT Specific)".
Database missing from product discovery.	Verify oratab file is on the server. If file exists, verify contents of oratab file. See "Oracle Intelligent Agent (UNIX Specific)".
Oracle Trace discovery fails while processing files for a 7.3 ORACLE_HOME.	Oracle Trace discovery process may have encountered a pre-7.3.3 otrccol image which is not compatible with Oracle Trace Manager. See "Server Environment".
Creating Collection
Cannot connect to Show User List.	Edit the tnsnames.ora file and add a service name that matches the service name known to the Oracle Enterprise Manager console. See "Failure Connecting to Show User List".
Scheduling Collection
Cannot start Oracle Trace from Oracle Enterprise Manager console.	Start Oracle Trace using Start menu or reinstall Oracle Trace.
No nodes listed in Oracle Trace Manager.	In Oracle Enterprise Manager console, discover nodes that you want to manage by choosing Navigator=> Discover Nodes, then perform product discovery in Oracle Trace Manager. See "Oracle Enterprise Manager Configuration".
"Authentication User" error during collection creation.	Preferences for node are invalid or not set. See "Oracle Enterprise Manager Configuration".
"Could not read file <filespec>".	File not in expected location. See "Oracle Trace Configuration".
Error starting/stopping Oracle7 database collection.	There are missing database stored procedures that Oracle Trace uses to start and stop an Oracle7 database collection. See "Stored Procedures".
Oracle Trace Manager shows collection with status 'Created' and Oracle Enterprise Manager Job system shows collection "awaiting agent confirmation".	Cannot connect to Oracle Intelligent Agent. See "Network Configuration". See "Oracle Intelligent Agent (UNIX Specific)". See "Oracle Intelligent Agent (NT Specific)".
Oracle Trace Manager shows collection with status 'Created' and Oracle Enterprise Manager Job system shows collection with changing status.	Oracle Intelligent Agent messages are not returned to Oracle Trace Manager. Exit and restart Oracle Trace Manager. Exit and restart Oracle Enterprise Manager console and Oracle Trace Manager.
Executing Collection
Error finding executable or command.	Preference setting node has invalid path. See "Server Environment".
"Could not read file <filespec>".	File not in expected location. See "Oracle Trace Configuration".
Network timeout.	See "Network Configuration".
Error accessing memory for collections running on NT server nodes.	See "Oracle Trace Could Not Access Memory".
Error starting collection on server node.	See "Database Configuration". See "Oracle Trace Configuration".
Error starting NT database collection, with memory mapping error.	See "Oracle Trace Configuration" for discussion of mismatch between NT database and Oracle Trace Collection Services.
Collection does not end at scheduled time.	Possibly out of disk space or Oracle Intelligent Agent is not available. See "Server Environment". See "Network Configuration". See "Oracle Intelligent Agent (UNIX Specific)". See "Oracle Intelligent Agent (NT Specific)".
Formatting Collection
Format gets "unable to resolve service name" error.	Database not available or invalid tnsnames.ora file on server. See "Network Configuration". See "Database Configuration".
Format gets "invalid username/password" error.	Preferences setting for database is invalid or not set.
Format gets "table does not exist" error (Oracle Server release 7.3.x and earlier).	Formatter tables not created. See "Database Configuration". See "Formatter Tables".
Format gets error extending tablespace.	Insufficient space in target tablespace in the database to which you are formatting. Allocate additional space to tablespace and reformat. See "Collection Is Too Large".
Format gets insufficient privilege error.	See "Could Not View Oracle Trace Formatted Data".
Examining Collection Data
Collection contains no data.	Oracle Trace interface to database is incorrectly configured or there are too many collections running in parallel. See "Database Configuration". See "Collection Is Empty".
SQL statement not captured in collection.	See "Missing SQL Statement from Collection".
Wait times were not collected (for ALL event set).	See "Wait Times Were Not Collected".
Collection has additional data but Data Viewer summaries are unchanged.	See "Additional Formatted Data Is Missing from Data Viewer".
Data Viewer gets errors computing statistics.	See "Need to Restore Formatted Data".
Data Viewer performance is poor.	See "Poor Performance".

Server Environment

If you suspect a server environment problem, verify:

That the account used by the Oracle Enterprise Manager Job system on the server node has:
- The ORACLE_HOME and the ORACLE_SID (Oracle System Identifier) environment variables defined.
- The $ORACLE_HOME/bin directory is included in the path for the account.
- Write access to the $ORACLE_HOME/otrace/admin/cdf directory
That the server node has sufficient disk space for the collection output files. If there is not sufficient disk space, the collection stops. Use the Oracle Trace Collection wizard to restrict the maximum file size. Because collection files can get large, collect small amounts of data.

To solve the immediate problem, stop the collection, and free up space so Oracle Trace can end the collection.

Initially limiting the collection to specific users and/or Wait events will also help to limit the amount of data collected. Limiting users and Wait Events is available for Oracle Server releases 8.0.4 and higher.
That your session does not participate in more than five collections. Sessions log data for the five most recent collections. Thus, if you have more than five collections, data will be missing for the oldest collection.
That the otrccol image is compatible with Oracle Trace Manager. If a 7.3 $ORACLE_HOME/bin directory contains an otrccol image, determine the version of the image by executing the following command:
```
$ORACLE_HOME/bin/otrccol version
```
This command should return a small integer, for example 1 (for Oracle Server release 7.3.3) or 3 (for Oracle Server release 7.3.4); any error returned by the ottrcol version command indicates an otrccol image that is not compatible with Oracle Trace Manager. You can leave the otrccol image in the $ORACLE_HOME/bin directory, but you must rename the image (for example, to otrccol732). If you rename the image, Oracle Trace will not attempt to use the image during the discovery process.

Oracle Enterprise Manager Configuration

Problems covered in this section are also known as client environment problems. Some of the symptoms of a configuration problem are: nodes and databases have not been discovered or refreshed, user preferences appear to be invalid, and jobs are not running in the Oracle Enterprise Manager Job system.

Try the following:

Discover nodes and databases by choosing Navigator=>Discover Nodes... from the Oracle Enterprise Manager console.
Verify that user preferred credentials are correct. Set preferred credentials by choosing System=>Preferences... in the console. Then select the Preferred Credentials tab to set node and database access credentials.

Note:
Preferred credentials need to be set separately for each domain user. Check the preferences set for the domain user that you currently used to log into Oracle Trace.

Verify that the user account is valid by either invoking SQL worksheet to test the database user account or rlogin to the node to test the node account.

By invoking SQL worksheet using the username/password@service for the account, you are verifying the existence and accessibility of a database user.

By using rlogin to log into the node, you are verifying the node criteria. If the node is not available, check with your system support personnel regarding the availability of the account.
Check the Oracle Enterprise Manager Job system especially if you are having problems running jobs. To create a test job, choose Job=>Create Job... from the console. Schedule a job to run an OS command ('ls' on UNIX and 'dir' on NT).

In the Oracle Enterprise Manager Job System on NT, you can:
1. Click the General tab. The Destination Type equals Node. Select the node on which the Oracle Intelligent Agent is located.
2. Click the Tasks tab. From Available Tasks, choose Run OS Command from the Available Destinations list.
3. Click the Parameters tab. On the Task Parameters section, type 'dir' in the Command Edit control.
4. Click the Submit button.
If the job does not work, there is a problem with the Oracle Enterprise Manager Job system. Consult the Oracle Enterprise Manager console documentation for additional information.
Verify that the Oracle Trace repository tables have been successfully created. You can verify the existence of the Oracle Trace repository tables using Oracle Server Manager or Oracle SQL*Plus Worksheet.
```
connect as repository <userid>
select table_name
from user_tables
where table_name like 'EPC_CLI%';
```
If you received the "Authentication User" message, you need to set the Oracle Enterprise Manager preferred credentials for the node to a valid user name and password with the appropriate privileges.

Note:
Preferred credentials must be set individually for each domain user.

If the account is on an NT node, the account must have the Advanced User Right 'Log on as a batch job' enabled.

See the instructions on setting preferences in the Oracle Enterprise Manager Configuration Guide.

Network Configuration

Some of the symptoms of a network configuration problem are: the network times out, error messages stating the system could not be found or the service name could not be resolved.

Try the following:

If available on your system, run the Ping utility to verify that the underlying network communication exists. Ping the server from the client and ping the client from the server.
Verify that the entries in the tnsnames.ora file on the client match the entries in the tnsnames. ora file on the server.

Oracle Intelligent Agent (UNIX Specific)

Symptoms of an agent configuration problem are error messages stating the agent is not working or that the agent is not found. This section offers a few suggestions. Refer to the Oracle Enterprise Manager Configuration Guide for a more in-depth explanation of the Oracle Intelligent Agent.

Verify:

Write access to the $ORACLE_HOME/network/agent directory or $ORACLE_HOME/net80/agent directory.
That the $ORACLE_HOME/bin/dbsnmp agent executable is owned by the root account (applicable to Oracle Server release 8.0.4 and higher).
That the $ORACLE_HOME/bin/dbsnmp agent executable has the 'sticky bit' set as represented by the letter s in the following code example (applicable to Oracle Server release 8.0.4 and higher). Though the executable is owned by the root account, the 'sticky bit' executes dbsnmp with the privileges of the invoking user, rather than with root privileges.
```
ls -l dbsnmp
-rwsr-s--x
```
If the sticky bit is not set, run the following script as the root account:
```
$ORACLE_HOME/orainst/root.sh
```

The processes:

check Oracle Intelligent Agent status

lsnrctl dbsnmp_status

Check for two dbsnmp processes
```
% /usr/ucb/ps auxw | grep dbsnmp
```

The contents of the oratab file (located in the /var/opt/oracle directory) and the listener.ora file. The oratab file is a UNIX-specific file created during installation and lists the installed or known databases on that node. Make sure that the oratab file does not contain entries for databases that no longer exist. The listener.ora file contains the names and locations of the databases you want to monitor. The Oracle Enterprise Manager Configuration Guide describes the proper settings for these files.
The information in the services.ora file compares with the information in the listener.ora file. For example, if you add an item to the list in the listerner.ora file and did not restart the agent, the new item will not be found in the services.ora file. The services.ora file was created when the agent was started for the first time.
If the agent will not start, refer to the Oracle Enterprise Manager Configuration Guide for possible solutions.

Oracle Intelligent Agent (NT Specific)

Symptoms of an agent configuration problem are error messages stating the agent is not working or that the agent is not found.

Verify that the agent is up and running. Select Start=>Settings=>Control Panel from the Task bar. Double-click on Services. Start the agent if it is not started.

Also, examine the NT Event log for individual applications. This log may provide additional information to help solve the problem.

Database Configuration

The symptom is that you scheduled a collection but no data is in the collection or there was an error formatting the data. Do the following.

Verify that you can attach to the database, for example, from Oracle Server Manager or Oracle SQL*Plus Worksheet using the connect command:
```
connect <username>/<password>@<service name>
```
For earlier database versions on NT, the command for invoking the Oracle Server Manager is either svrmgr30 or svrmgr23. On UNIX the command is svrmgrl.

Verify that the database and listener are running on the server.

test-sun% /usr/ucb/ps auxw | grep oracle 
oracle   10684  0.7  5.92736814784 ?        S 12:37:46  1:00 ora_ckpt_v805 
oracle   10678  0.0  6.42843215952 ?        S 12:37:46  0:00 ora_pmon_v805 
oracle   10680  0.0  6.02744014960 ?        S 12:37:46  0:00 ora_dbw0_v805 
oracle   10682  0.0  5.92739214848 ?        S 12:37:46  0:00 ora_lgwr_v805 
oracle   10686  0.0  6.42630416048 ?        S 12:37:46  0:00 ora_smon_v805 
oracle   10688  0.0  6.32628815920 ?        S 12:37:46  0:00 ora_reco_v805 
oracle   10809  0.0  3.211064 8112 ?        S 14:36:10  0:00 
/oracle/app/oracle/product/805/bin/tnslsnr LISTENER -inherit

Verify that all required patches have been applied to the database and that the database has been upgraded.
Verify init.ora parameters for Oracle Trace.
- ORACLE_TRACE_ENABLE=TRUE for Oracle Server release 8.0 and higher.
- If you schedule the collection through the command-line interface, supply the collection path (ORACLE_TRACE_FACILITY_PATH) and the collection name (ORACLE_TRACE_FACILITY_NAME). Make sure these parameter values are valid names and that the account running the collection has access to the specified directory and file.
- If you change any init.ora parameters, the database must be restarted for the parameters to take effect.
Check for stored procedures (for Oracle Server releases 7.3.x)

To check for stored procedures using the Oracle Enterprise Manager console, use the Navigator and the following path:
```
Networks=>Databases=><your database>=>Schema Objects=>Packages=>SYS
```
Look for stored procedures starting with DBMS_ORACLE_TRACE_xxx.

To check for stored procedures using Oracle Server Manager or Oracle SQL*Plus Worksheet:
```
select object_name from dba_objects where object_name like '%TRACE%' 
and object_type = 'PACKAGE'; 
OBJECT_NAME
DBMS_ORACLE_TRACE_AGENT
DBMS_ORACLE_TRACE_USER
2 rows selected.
```
Check privileges of the database account. Using the Navigator in the Oracle Enterprise Manager console, choose:
```
Networks=>Databases=><your database>=>Users=><dba account>=>System 
privileges
```
See "Could Not View Oracle Trace Formatted Data" for the list of required privileges.
Check for formatter tables (for Oracle Server releases 7.3.x)

To check for formatter tables using the Oracle Enterprise Manager console, use the Navigator and the following path:
```
Networks=>Databases=><your database>=>Users=><user id>=>Schema 
Objects=>tables
```
Look for EPC_COLLECTION.

To check for formatter tables using SQL Worksheet:
```
connect <username>/<password>@<service name>
describe epc_collection
```
To create formatter tables, execute the otrcfmtc.sql script from the ORACLE_HOME of the Oracle Server that you connected to. You only need to run this script on Oracle Server release 7.x collections.
```
@ORACLE_HOME/otrace/admin/otrcfmtc.sql
```
Check tablespace usage and extents available using Oracle Storage Manager. There may not be enough space to hold the collection data.
Verify that users do not have SYSTEM as default tablespace. Using the Navigator in Oracle Enterprise Manager:
```
Networks=>Databases=><your database>=>Users
```
Right mouse click on the user ID you want to verify and select Quick Edit. The Tablespaces section of the General tab lists the default tablespace. The default tablespace should be a user tablespace.

Oracle Trace Configuration

If you suspect an Oracle Trace configuration problem:

Examine the EPC_ERROR.LOG file for details of any logged Oracle Trace errors.
Look for the administration files on $ORACLE_HOME/otrace/admin (*.dat files) and run otrccref to recreate the Oracle Trace *.dat files if the files do not exist.
Verify that the .fdf files are in the $ORACLE_HOME/otrace/admin/fdf directory.
Verify that the TCL scripts are in the correct directory. The server files are located under $ORACLE_HOME/network/agent/jobs/oracle/otrace/general
```
% cd $ORACLE_HOME/network/agent
% find . -name "otrc*.tcl" -print
```
The client file (otrcjob.tcl) is located in $ORACLE_HOME\Sysman\Scripts\Tcl directory.

Verify the correct version of Oracle Trace Collection Services match the appropriate Oracle Server version

% $ORACLE_HOME/bin/otrccol version

Table C-2 Matching Releases of Oracle Trace Collection Services with Releases of Oracle Server

If the Returned Value Is:	Then the Command-Line Interface Release Is:
1	733
2	803
3	734
4	804
5	805
6	813
7	814
8	815

To verify that a collection is currently running, use the command-line interface to check the status:
```
% $ORACLE_HOME/bin/otrccol check <collection_name>
```
To verify the Oracle Trace configuration, use the command-line interface to start, stop, and format collections.
```
otrccol start <parameters>
otrccol stop <parameters>
otrccol format <parameters>
```
Refer to the Oracle Enterprise Manager Oracle Trace Developer's Guide for all the possible parameters.

Data Viewer Problems

The following section deals with problems specific to Data Viewer.

Could Not View Oracle Trace Formatted Data

To view Oracle Trace formatted data using the Data Viewer, a user account needs the following system privileges:

CREATE SESSION
CREATE SEQUENCE
CREATE TABLE
CREATE PROCEDURE
CREATE ANY INDEX
EXECUTE ANY PROCEDURE
DELETE ANY TABLE
DROP ANY INDEX
DROP ANY SEQUENCE
UNLIMITED TABLESPACE

Need to Restore Formatted Data

Data Viewer adds statistical information to your formatted data. To restore your formatted data to the exact state it was in prior to running Data Viewer, run the ORACLE_HOME\Sysman\Admin\TdvDrop.sql script. Reasons for restoring data include:

You encountered an insufficient privilege error prior to or during computing statistics.

You received a database error creating a tablespace extent.

ORA-01658: unable to create INITIAL extent for segment in tablespace
MY_TABLESPACE

You encountered an unexpected error that appears to be non-recoverable.

Wait Times Were Not Collected

Wait times are collected only if the INIT<sid>.ORA parameter, TIMED_STATISTICS, is set to TRUE. When you change this database parameter, you must stop and restart the database for the change to take effect.

Additional Formatted Data Is Missing from Data Viewer

The first time Data Viewer views a collection, you can compute SQL statistics for all queries currently in the collection. Data Viewer does not detect new data added to a collection by a partial format while Data Viewer is viewing that collection. If you choose to partially format your collected data, you can force Data Viewer to recompute statistics using both existing and newly collected data by choosing Options=>Recompute Statistics.

Missing SQL Statement from Collection

If an expected SQL statement does not appear to be in your collection, it may be because the Oracle Trace auto-format function does not flush the collection buffer until the next time the database is accessed after the collection was stopped. This may result in a small amount of event data not reaching the collection file.

Collection Is Too Large

There may be times when a collection is too large. Starting with Oracle Trace release 1.5.5 and Oracle Server release 8.0.4, you can collect data for specific users and specific Wait event types to minimize the size of the collection. Because the server is almost always waiting for a latch, lock, or resource, Wait event data for a brief collection can be quite extensive.

The Oracle Trace Collection Wizard walks you through limiting the collected data by individual users.

Poor Performance

If multiple collections exist in one set of user tables, computing statistics and subsequent selects may take longer than necessary. Placing a large collection in a new database user account will improve performance.

Table or View Does Not Exist (or No Data in Collection)

When a record is not written to the EPC_FACILITY_REGISTRATION table, the user may see an error like the following:

XP-21016: A database error has occurred:
SELECT DISTINCT FACILITY_NUMBER, FACILITY_VERSION, VENDOR 
FROM EPC_FACILITY_REGISTRATION WHERE COLLECTION_ID - :1
ORA-00942: table or view does not exist

Removing the 'Filtering by User' option on collections targeting Oracle Server release 8.0.4 databases will correct this problem for future collections.

Manually adding an EPC_FACILITY_REGISTRATION record for the collection allows Trace Data Viewer to see that collection's formatted data. For example, insert EPC_FACILITY_REGISTRATION record with the following values:

Collection_ID:    123       [Look in formatted data's EPC_COLLECTION table
                             for the collection_id column value that matches
                             your collection_name]
Vendor:           192216243 [Hard code this value]
Facility_number:  5         [Hard code this value]
Facility_version: '8.0'     [or '7.3' for a collection against a 7.3 database
                             or '8.1' for a collection against an 8.1 database]

Oracle Trace Manager Problems

The following section deals with problems specific to Oracle Trace Manager.

Failure Connecting to Show User List

If the user name and password you use to connect to the target collection database are correct, the problem may be that the service name used for connection is not the same name as your service name for the target collection database.

The service name is set to the service name as it is known to the Oracle Enterprise Manager console. The console gets the service name of discovered databases from the agent.

If the service name does not match the service name in your tnsnames.ora file, you will not be able to connect to the database when you click the Show User List button.

Edit your tnsnames.ora file and add a service name that matches the service name known to the Oracle Enterprise Manager console.

Format of Collection Failed

There are a number of reasons why this can occur.

Ran out of space in the database; tablespace needs extending.
Invalid user name/password.
The otrcfmtc.sql script has not been run to create the formatter tables. The script is located in $ORACLE_HOME\otracenn\admin. (This step was required for releases prior to Oracle Trace Collection Services releases 7.3.4 and 8.0.4.) For Oracle Trace releases 1.3.6 and higher, run the vobsh command to create the formatter tables. Refer to the Oracle Enterprise Manager Configuration Guide for additional information about the vobsh command.
If the formatter tables are not local to the node running the Oracle Enterprise Manager console, check that the node where the collection was performed does have a TNS names entry for the format database.
If the collection has already been formatted, it will not be formatted again. To format the collection, disable the Partial Format option.
Preferred credentials must use a service name from the list of Oracle Enterprise Manager service names. That service name must be in the tnsnames.ora file for the server the data was collected on.

Collection Is Empty

In Oracle8 databases, the ORACLE_TRACE_ENABLE parameter in the INITsid.ORA file on the server must be set to TRUE before the database is started. (In previous versions of Oracle Server, the ORACLE_TRACE_ENABLE parameter was set to FALSE.) You can also see this problem if there are too many collections running concurrently.

Refer to "Database Configuration" for additional information.

Oracle Trace Could Not Access Memory

On Windows NT systems, if you are running Oracle Trace collections and an error occurs indicating Oracle Trace could not access memory, the collect.dat file has become full. You must create a new .dat file by running the otrccref.exe image located in the $ORACLE_HOME/bin directory. However, database services must be shutdown to release the collect.dat file for the otrccref script to be able to create the new collect.dat file. You can also increase the number of collect.dat records above the default of 36 records (for example, otrccref -c50).

Isolating an Oracle Trace Problem

A good approach in isolating a problem is to perform tests on each component of Oracle Trace. The components are:

Oracle Trace Manager (graphical user interface)
- Oracle Trace
- Oracle Intelligent Agent and Oracle Enterprise Manager Job system
Oracle Trace Collection Services
- init.ora parameters
- Stored procedures (for Oracle Server release 7.3.x only)
- Oracle Trace Command-Line Interface (CLI)

There are two Oracle Trace mechanisms that are valuable when troubleshooting: Oracle Trace logging window (see "Oracle Trace Manager Logging") and EPC_ERROR.LOG file (see "EPC_ERROR.LOG File").

Isolating a Problem in Oracle Trace Manager

You can test Oracle Trace Manager to determine if the problem is within the graphical user interface. Turn on Oracle Trace logging to see the communication within Oracle Trace Manager.

Oracle Trace Manager performs the following:

Takes input from the user.
Packages the information from the user into a temporary input parameters file. Oracle Trace Manager uses the Oracle Enterprise Manager Job system to schedule specified tasks. Within the Oracle Intelligent Agent, the otrcjob.tcl script then runs the job to be performed on the database server.

The otrcjob.tcl script is a general purpose tcl script. It manages the other tcl scripts by taking input parameters plus those supplied by the temporary parameter files and running one of the other task specific tcl scripts.

Each of the other tcl scripts is used to send the information required by the CLI images to perform the specific task against the database: start, stop, delete, or format a collection.
The jobs submitted run the CLI images to start and stop collections, format the collection data, and delete the collection.
The jobs manage the return of the information from the CLI to Oracle Trace Manager.

To check and see if the communications is set up correctly, you can run the Oracle Enterprise Manager Job system independently. You may also want to ping the server from the client and ping the client from the server.

Oracle Enterprise Manager Job System

You can use the Oracle Enterprise Manager Job system to test if the communication between Oracle Management Server and the Oracle Intelligent Agent is working. For step by step instructions, see "Oracle Enterprise Manager Configuration".

Formatter Tables

If you format a collection, Oracle Trace will format the data into your repository database and schema by default. The format operation requires repository tables to store collection information.

You can check that the formatter tables have been created.

You can query the database and look for the tables "EPC_COLLECTION". The tables that start with "EPC_CLI" are the repository tables. All the other tables that start with "EPC_" are the formatter tables.

An easy way to find the formatter tables is to use Oracle Enterprise Manager Schema Manager to graphically see if the formatter tables are present.

Oracle Server releases 7.3.4 and 8.0.4 and later automatically create the formatter tables. Prior to Oracle Server releases 7.3.4 and 8.0.4, you must run the otrcfmtc.sql script from Oracle Server Manager or Oracle SQL*Plus Worksheet as the user who will be formatting the data. To ensure version compatibility, use the otrcfmtc.sql script located in the ORACLE_HOME of the destination server.

The otrcfmtc.sql script is located in the $ORACLE_HOME/otracenn/admin directory (where nn represents the version of Oracle Trace).

Formatting error might be due to one of the following causes:

The user did not run the script to create the formatter tables (valid for releases of Oracle Server prior to 7.3.4 and 8.0.4).
The formatter tables were not created by the same user ID that was used when the collection was created (valid for releases of Oracle Server prior to 7.3.4 and 8.0.4).
There is a version mismatch. If the user formats to a given schema with an Oracle Server release 8.0.4 formatter, then later tries to format to the same schema using an older formatter, for example Oracle Server release 7.3.x, an error occurs. For additional information see "New otrcfmt Image for Oracle Server Releases 7.3.4 and 8.0.4".

Isolating a Problem in Oracle Trace Collection Services

Here are a few suggestions to help you isolate problems in the Oracle Trace Collection Services.

Oracle Trace Command-Line Interface

Oracle Intelligent Agent uses the command-line interface to access the Oracle Trace Collection Services. You can run the CLI to determine if the Oracle Trace Collection Services are functioning properly.

The CLI is located on the same server as the database that you are running the collection against. If you have multiple ORACLE_HOMEs, you could have multiple CLIs, that is, one CLI per ORACLE_HOME. Verify that your path is pointing to the correct CLI.

To test the CLI:

Run the CLI directly on the server node to test if the collection services are working properly.
1. CLI needs to run from a privileged account, for example, the Oracle operating system user account.
2. The Oracle Home and SID environmental variables must be set properly.
  
  To check settings on UNIX:
```
printenv ORACLE_HOME
printenv ORACLE_SID
```
  To set settings on UNIX:
```
setenv ORACLE_HOME <path>
setenv ORACLE_SID <sid>
```
  There should be one CLI per ORACLE_HOME. For example, if you have two Oracle Server release 7.3.3 instances sharing the same ORACLE_HOME, there should be only one CLI.
Verify that the CLI release matches the database server release.

The Oracle Trace Collection Services files may be overwritten on NT by other product installations. If this occurs, your Oracle Trace Collection Services files will no longer match the version required by your Oracle Server database and you may receive a memory mapping error.

On the server node, type the following command on the operating system command line:
```
$ORACLE_HOME/bin/otrccol version
```
See Table C-2 for the possible results. If the versions do not match, call Oracle Support for additional assistance.
Verify if the collection name has not already been used before you start the collection. Look in the .cdf and .dat directories $ORACLE_HOME\otracenn\admin on NT ($ORACLE_HOME/otrace/admin on UNIX).

Prepare your input file as follows:

<collection_name>.inp

col_name= col_name (no spaces before equal sign and 1 space after equal 
sign)
dat_file= col_name.dat
cdf_file= col_name.cdf
fdf_file= oraclee.fdf (whatever .fdf file you wish to use)
regid=1 192216243 0 0 5 <your_service_name> [for Oracle8 use <sid>]
resubmit= 0 (1 only if resubmission)

If you want to generate database activity for this collection, connect to the database.
- For Oracle Server release 7.3.x, connect to the service before you create your collection.
- For Oracle Server release 8.0, you can connect to the database anytime and the processes will be registered.
To test the entire CLI process, start a collection, verify the collection is running, and stop the collection. For example:
1. To start a collection:
```
otrccol start <job_number> <collection_name.inp> (see step 4 
where you can make up your own input parameter file)
```
  You should see the response "collection started".
  
  A .dat file and a .cdf file will be created in one of the following directories: $ORACLE_HOME/otrace/admin/cdf or the directory specified by the ORACLE_TRACE_COLLECTION_PATH parameter in your init<sid>.ora file.
2. Verify that the collection was created.
```
otrccol check <collection_name>
```
  The collection should show as active.
3. To stop a collection:
```
otrccol stop <job_number> <collection_name.inp>
```
  After you run the stop command your collection will not show as active.
If there are any errors, the EPC_ERROR.LOG file will be written to the same directory from which you run the otrccol command.

Test that the format works properly. Choose a schema in a database with enough room to hold the data.

Verify that the formatter tables have been created by querying the schema to see if there are tables prefixed with `EPC_'. The Oracle Trace repository tables are prefixed with `EPC_CLI' and the formatter tables are prefixed with 'EPC_'. You can also use Oracle Schema Manager to look at the tables.

Note:
For Oracle Trace Collection Services releases prior to 7.3.4 and 8.0.4, you need to manually run the otrcfmtc.sql script located on $ORACLE_HOME/otrace/admin to create the formatter tables.

Attempt to format a collection using the command-line interface.
```
otrccol format <format_input_file>
```
The format_input_file contains the following input parameters:
```
username= <db username>
password= <db pw>
service= <db service name>
cdf_file= <collection_name>.cdf
full_format= <0/1>
```
Full format value of 1 formats all the data in the data file, 0 formats only the data that has not previously been formatted.

Stored Procedures

If the attempt to collect Oracle Trace data for an Oracle7 database results in the message "Error starting/stopping Oracle7 database collection," this may be due to missing database stored procedures that Oracle Trace uses to start and stop Oracle7 collections.

To verify that the stored procedures are present:

Select the database in the Navigator tree in the Oracle Enterprise Manager console.
Select Schema Objects=>Packages=>SYS.

Verify that DBMS_ORACLE_TRACE_USER and DBMS_ORACLE_TRACE_AGENT are listed. This is an issue only if you are using Oracle Server release 7.3.x.

Prior to Oracle Server release 8.0.3, Oracle Trace required that stored procedures be installed on the database. These SQL scripts may be automatically run during database installation depending on the platform-specific installation procedures. If they are not executed during database installation, you must run these scripts manually. You can add these stored procedures to the database by running the otrcsvr.sql script from $ORACLE_HOME\otracenn\admin on NT ($ORACLE_HOME/otrace/admin on UNIX) from a privileged database account (SYS or INTERNAL). To run the script, set the default to the path were the script is located. This script runs other scripts that do not have the path specified. These other scripts fail if you are not in the directory where these scripts will run.

Oracle Trace Manager Logging

There are two methods by which to capture activity taking place in the Oracle Trace Manager: logging window (/l option) and log file (/o option). Run Oracle Trace Manager with the /l option, the /o option, or both options activated. To activate the options, do the following:

Run the Oracle Enterprise Manager console.
From the Start menu, choose Run.

Assuming your ORACLE_HOME is C:\ORANT, in the Open field, type:

C:\ORANT\BIN\EPC.EXE /l /o=<filename> [Do not leave any spaces 
between the equal sign and the filename.]

Click OK.

This displays the Oracle Trace Log window, which displays in-depth information about Oracle Trace Manager client/server processing. This information includes:

Node and discovery information
Information about jobs being submitted
Detailed error messages

With the /o option activated, you automatically have a file copy of the information displayed in the logging window.

EPC_ERROR.LOG File

The EPC_ERROR.LOG file provides information about the collection processing, specifically the Oracle Trace Collection Services errors.

The EPC_ERROR.LOG file is created in the current default directory of the Oracle Intelligent Agent when it runs the Oracle Trace Collection Services otrccol image.

Depending if you are running Oracle Trace from the Oracle Trace Manager or the command-line interface, the EPC_ERROR.LOG file can be located in one of the following server locations:

$ORACLE_HOME or $ORACLE_HOME/network/agent (on UNIX)
%ORACLE_HOME%\network\agent or %ORACLE_HOME%\net80\agent (on NT)
$ORACLE_HOME\rdbmsnn on NT ($ORACLE_HOME\rdbms on UNIX)
In current working directory, if you are using the command-line interface

To find the file on UNIX, do the following:

% cd $ORACLE_HOME
% find . -name EPC_ERROR.LOG -print

Note:
On UNIX the EPC_ERROR.LOG file name is case sensitive and is in uppercase.

For general information about causes and actions for most Oracle Trace messages, see the Oracle Enterprise Manager Messages Manual.

Information Needed When Reporting a Problem

If you have tried all the previously described suggestions and Oracle Trace is still not working, please call your local Oracle World Wide Support Center. When reporting a problem, have the following information available:

Version of Oracle Trace
Version of Oracle Intelligent Agent
Operating system and version you are using for the server and the version of the Oracle Server
Operating system you are using for the client and the version of the client
Verify whether you have multiple ORACLE_HOME installations. If so, what Oracle components and versions are in each ORACLE_HOME installation?
Steps to follow to reproduce the problem
Log files (information from Oracle Trace Manager logging window and EPC_ERROR.LOG)
Steps you have followed to attempt to solve the problem

C Troubleshooting Oracle Trace