Oracle Transparent Gateway Sybase Administrator's Guide Release 8.1.6 for Windows NT Part Number A80982-01 |
| Library | Contents | Index

# 7
Diagnosing Problems

This chapter provides information about diagnosing problems and understanding error messages. The following sections are included:

- Problem Identification

- Installation and Configuration Issues

- Net8 Configuration Problems

- Common Gateway Problems

- Analyzing Error Messages

- Tracing

- Oracle Support Services

## Problem Identification

When you encounter a problem, you first should determine where the problem occurred. The following is a list of possible problem areas and references to more information about those areas.

- Installation and configuration

  See Also: "Installation and Configuration Issues".

- Net8

  You might be experiencing problems in Net8 if you receive errors that appear as follows:

      ORA-nnnnn:  TNS:  error_text

  See Also: "Net8 Configuration Problems".

- Oracle database server

  You might experience problems with the Oracle database server if the database link is not set up correctly or if the Oracle database server does not have the distributed option. Verify the installation of the Oracle database server, and check the case (upper or lower) of the letters in the user name and password used in the CREATE DATABASE LINK statement.

  See Also: "Tracing".

- Sybase or the gateway

  You might be experiencing problems with Sybase or with the gateway if you receive errors that appear as:

      ORA-nnnnn: error_text
      [Transparent gateway for SYBASE]gateway_error_text

  where nnnnn ranges from 12300 to 12399 or error text involves the database link that calls the gateway.

  See Also: "Common Gateway Problems", "Analyzing Error Messages" and "Tracing".

  To see if the error originated with Sybase, try the SQL statement with Sybase native tools. Verify that the SQL executed correctly and has no syntax errors. Contact the Sybase vendor for support.

## Installation and Configuration Issues

The issues surrounding installation and configuration usually involve verifying if the installation process was successful, and wanting to know what to configure.

#### How do I verify that the installation succeeded?

Review the installation log file, which lists any problems encountered during installation. By default, the file has the following name:

`orainventory_location\logs\installActions.log`

where `orainventory_location` is the directory where the OraInventory is located.

#### Does the Oracle Installer configure everything?

No. The Oracle Installer simply installs the software. You must configure the software after its installation. Follow the tasks in Chapter 4, "Configuring the Gateway", to configure the gateway and its components.

## Net8 Configuration Problems

If you receive errors that refer to TNS when accessing the gateway, the problem is probably with Net8 or the Net8 configuration. Following is a list of the most likely TNS errors and the actions you should take.

ORA-12154 "TNS:could not resolve service name"

  Cause: The service name specified is not defined correctly in the `tnsnames.ora` file.

  Action: Check the following items:

  - Verify the `tnsnames.ora` file exists, is in the proper place, and is accessible.

  - Verify the service name exists in one of the `tnsnames.ora` files. Add a service name if needed.

  - Ensure there are no syntax errors in the `tnsnames.ora` file. Look for unmatched parentheses or stray characters. Any error in the `tnsnames.ora` file makes it unusable. If possible, regenerate the configuration files using the Oracle Network Manager.

    See Also: Net8 Administrator's Guide.

  - Ensure the USING clause of the database link you created for the Sybase database is pointing to the correct `tnsnames.ora` file.

    See Also: "Configuring the Gateway for Multiple Sybase Databases".

  - If the `sqlnet.ora` file exists in the `ORACLE_HOME\network\admin` directory, check to see if the file includes entries such as:

        names.directory_path=(TNSNAMES, HOSTNAME)
        names.default_domain=world
        names.default_zone=world

    If so, the service name might need the suffix .world.

ORA-12203 "TNS:unable to connect to destination"

  Cause: An invalid TNS address was supplied or the destination is not listening. This error can also occur because of underlying network transport problems.

  Action: For the Oracle database server:

  - Ensure that the `tnsnames.ora` file is configured as described in "Configuring the Gateway for Multiple Sybase Databases".

  - Ensure that the USING clause of the database link you created is pointing to the correct `tnsnames.ora` file.

  For the gateway:

  - Ensure that the `listener.ora` on the gateway machine is configured as described in "Configuring Net8 Listener for the Gateway".

  - Ensure that the Net8 listener is started.

  If the Net8 configuration is correct but you still encounter errors, set up a loop-back link, which is a database link in the Oracle database server that points back to itself. If Net8 is working properly you can access a table in the Oracle database server using a loop-back link. For example:

      SELECT * FROM table@loop

  If this link does not enable you to access a table in the Oracle database server, see the Net8 documentation for the platform for information about troubleshooting Net8.

  See Also: For more information about configuring the `listener.ora` and `tnsnames.ora` files, see "Configuring Net8 Listener for the Gateway" and "Configuring the Gateway for Multiple Sybase Databases".

ORA-12500 "TNS:listener failed to start a dedicated server process"

  Cause: The process of starting the Oracle database server failed. The Oracle database server executable could not be found or the environment is set up incorrectly.

  Action: Check these items:

  - Verify that the SID value specified in the `tnsnames.ora` file matches the gateway SID.

## Common Gateway Problems

Many common gateway problems are caused by errors in the initialization parameter file, and are described in this section. For other errors, review the log files for more information. If no log file exists, the gateway might not have started. In that case, Net8 configuration might be the problem.

See Also: "Oracle Support Services".

ORA-02068: following severe error from database_link_name

ORA-01012: not logged error_found_in_init_file

  Cause: One or more gateway initialization parameters might be configured incorrectly or there are syntax errors.

  Action: Review the gateway log file for error messages that describe the problem. Correct the `inittg4sybs.ora` file as needed.

ORA-02068: following severe error from database_link_name

ORA-03113: end-of-file on communication channel

  Cause: The HS_FDS_TRACE_LEVEL parameter is set to an invalid value in the initialization parameter file, or the target database is not set up correctly.

  Action: Ensure the values are set correctly and the target database is set up correctly. Refer to the documentation for the target database.

ORA-02085: database link database_link_name. WORLD connects to database_link_name.domain_name

  Cause: The Oracle database server has GLOBAL_NAMES set to TRUE in its initialization parameter file. This error occurs because the value specified by HS_DB_DOMAIN in the initialization parameter file, displayed as domain_name in the error message, does not match a domain name of WORLD.

  Action: Change the value of HS_DB_DOMAIN in the initialization parameter file to `WORLD`.

ORA-02085: database link database_link_name1.domain_name connects to database_link_name2.domain_name

  Cause: The Oracle database server has GLOBAL_NAMES set to TRUE in its initialization parameter file. The value specified by HS_DB_NAME in the initialization parameter file (database_link_name1) must match the value of the database link used by the gateway (database_link_name2). The error occurs because the values do not match.

  Action: Change the value of HS_DB_NAME in the initialization parameter file to match the database link name being used.

## Analyzing Error Messages

This section describes the following topics:

- Message and Error Code Processing

- Oracle Database Server Error Codes

- Gateway Error Codes and Message Formats

### Message and Error Code Processing

The gateway architecture includes a number of separate components. Any of these components can detect and report an error condition while processing an SQL statement that refers to one or more Sybase database tables. An error condition can be complex, involving error codes and supporting data from multiple components. In all cases, the application receives a single Oracle error code upon which to act.

Error conditions are represented in the following ways:

- An Oracle error code

- A gateway error code

  Gateway error conditions are reported to the application using one of the gateway error codes in the range of ORA-28500 through ORA-28561.

  See Also: "Gateway Error Codes and Message Formats" for an explanation of the message format.

### Oracle Database Server Error Codes

The Oracle database server messages and error codes are documented in Oracle8i Server Messages. Refer to that publication for information about interpreting the Oracle database server messages and for information about specific error codes.

If an error is detected by the Oracle database server, gateway message lines do not occur. For example, if the gateway cannot be accessed because of a Net8 or gateway installation problem, a gateway message line is not present in the generated error.

Another example of error messages without gateway message lines occurs when an INSERT statement attempts to insert data into a table, but does not include values for all of the columns in the table. The following SQL statement causes an error message:

    SQL> insert into "EMP"@SYBS values(9999);

    ERROR at line 1:
    ORA-00947: not enough values

The ORA-00947 message is not accompanied by gateway message lines because the error is detected by the Oracle database server. The Oracle database server obtains a description of the Sybase table before sending the INSERT statement to the gateway for processing. This allows the Oracle database server to detect when the INSERT statement is invalid.

### Gateway Error Codes and Message Formats

Error codes are generally accompanied by additional message text, beyond the text associated with the Oracle message number. The additional text includes details about the error.

Gateway messages have the following format:

    ORA-nnnnn: error_text

    [Transparent gateway for SYBASE]gateway_error_text

    ORA-02063: preceding n lines from dblink

where:

nnnnn | is an Oracle error number where nnnnn ranges from 28500 to 28561.
error_text | is the text of the message associated with the error.
gateway_error_text | are additional messages generated by the gateway or by Sybase. See "Oracle Database Server Error Codes" for more information. The format of the gateway_error_text is: [Transparent gateway for SYBASE]gateway_error_text
n | is the total number of gateway message lines.
dblink | is the name of the database link used to access the gateway.

For example, if you get a message where nnnnn is between 28500 and 28561, it is a gateway message and that is where the error is occurring. A gateway message line such as the following means that the error originates in Sybase or the gateway:

    [Transparent gateway for SYBASE]

The ORA-28500 error code is returned for all errors for which a more specific error code does not exist. When the ORA-28500 error code is returned, the error might have been caused in the gateway by the Sybase components of the target database.

## Tracing

Use error tracing to pinpoint problem areas involving the gateway, the Oracle database server, and Sybase.

You can enable error tracing to record by setting the HS_FDS_TRACE_LEVEL gateway initialization parameter.

See Also: For information, see Appendix C, "Heterogeneous Services Initialization Parameters".

Use tracing only while testing and debugging the application. Do not enable tracing when the application is running in a production environment because tracing reduces gateway performance.

### Trace Log Files

A log file is created with each SQL session to collect the following information:

- The SQL statement as it was sent to the gateway and then on to the target database

- User information such as user name, user program and terminal ID

Trace files are created in the `ORACLE_HOME\tg4sybs\trace` directory, where ORACLE_HOME is the ORACLE_HOME directory under which the gateway is installed. The trace file names have the format agent_sid_agt_process_id.trc where agent_sid is the sid for the gateway and process_id is the operating system process ID of the gateway session.

### Reviewing the Trace Output

If you are having trouble accessing data from the Sybase database, you might receive the following messages:

    ORA-00942: table or view does not exist

    [Transparent gateway for SYBASE]Object not found....

    ORA-02063: preceding n lines from database_link_name

or the following message:

    ORA-00904: invalid column name

To find the source of the problem, review the trace output from the gateway.

If you are certain that the identifier exists in the Sybase database, the trace output might show, for example, that the table or column name in the SQL statement sent to the Sybase database has the wrong case, or you forgot to enclose it in double quotation marks. The Oracle database server identifiers must be in uppercase unless you enclose them in double quotation marks. The case for identifiers for Sybase depends on whether it is case-sensitive or not.

The outgoing parsed SQL statement listed in the trace output is the SQL statement passed to the gateway and on to the target database. If you think the SQL statement might have been generated incorrectly, test the SQL statement by executing it with Sybase's native tools or using an ODBC application such as ODBC-Test. If you need help to execute the statement using these tools, contact the Sybase vendor for support.

## Oracle Support Services

If you determined that the problem does involve an Oracle component, but you have not been able to solve it, gather the information and fill out the worksheet before contacting Oracle Customer Support Services:

Note: Support for non-Oracle components is the responsibility of the vendor providing that component.

##### Table 7-1 Oracle Support Services Worksheet

Required Information | Site-specific Values
--- | ---
Name and full version numbers for: |
- the gateway | Oracle Transparent Gateway for Sybase Version: ____________________________________________
- the platform/OS on which the gateway resides | Windows NT____________________________ Version:__________________________________________________________ Machine Name:______________________________________
Versions of Net8 Server used by the gateway | Net8 Version:________________________________________
Net8 Adapter used by the gateway | Circle one: Named Pipes - SPX/IPX - TCP/IP Version:____________________________________________
Full pathname to directory in which gateway resides | Path:______________________________________________
Oracle database server and the platform/OS on which it resides | Oracle___ Server, Version:____________________________ OS:_______________________Version:__________________ Machine Name:_____________________________________
Oracle SID of the Oracle database server | SID:______________________________________________
Full pathname to directory where the Oracle database server is installed | ORACLE_HOME:___________________________________
Version of Net8 Client used by the Oracle database server | Version:___________________________________________
Net8 adapter used by the Oracle database server | Circle one: Named Pipes - SPX/IPX - TCP/IP Version:___________________________________________
The database and any patch information for it | Server Name:______________________________________ Database Name:____________________________________ Version:__________________________________________
Listing of spool log if using SQL*Plus, or the SQL statement and error message received | Check here:
Relevant table and view definition (a full description of the tables involved in the SQL query) | Check here:
Description of the problem (input, output, test cases, and so on) | Check here:
Listings of key files: | Check here: `tg4sybs_agt_``pid``.trc` log file gathered with HS_FDS_TRACE_LEVEL Net8 configuration files, `tnsnames.ora` and `listener.ora` initialization parameter file

| Copyright © 2001 Oracle Corporation. All Rights Reserved. |
| Library | Contents | Index