Release 8.1.6 for Windows NT
Part Number A82868-01
 Library |
 Contents |
 Index |
| Oracle Transparent Gateway Microsoft SQL Server Administrator's Guide Release 8.1.6 for Windows NT Part Number A82868-01 |
|
This chapter provides information about diagnosing problems and understanding error messages. The following sections are included:
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.
You might be experiencing problems in Net8 if you receive errors that appear as follows:
ORA-nnnnn: TNS: error_text
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.
You might be experiencing problems with Microsoft SQL Server or with the gateway if you receive errors that appear as:
ORA-nnnnn: error_text
[Transparent gateway for MICROSOFT SQL SERVER]gateway_error_text
where nnnnn ranges from 12300 to 12399 or error text involves the database link that calls the gateway.
To see if the error originated with Microsoft SQL Server, try the SQL statement with Microsoft SQL Server native tools. Verify that the SQL executed correctly and has no syntax errors. Contact the Microsoft SQL Server vendor for support.
The issues surrounding installation and configuration usually involve verifying if the installation process was successful, and wanting to know what to configure.
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.
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.
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:
tnsnames.ora file exists, is in the proper place, and is accessible.
tnsnames.ora files. Add a service name if needed.
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.
tnsnames.ora file.
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:
tnsnames.ora file is configured as described in "Configuring the Gateway for Multiple Microsoft SQL Server Databases".
tnsnames.ora file.
For the gateway:
listener.ora on the gateway machine is configured as described in "Configuring Net8 Listener for the Gateway".
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 |
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:
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.
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 inittg4msql.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.
This section describes the following topics:
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 Microsoft SQL Server 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:
Gateway error conditions are reported to the application using one of the gateway error codes in the range of ORA-28500 through ORA-28561.
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"@MSQL 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 Microsoft SQL Server 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.
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 MICROSOFT SQL SERVER]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 Microsoft SQL Server. See "Oracle Database Server Error Codes" for more information. The format of the gateway_error_text is: [Transparent gateway for MICROSOFT SQL SERVER]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 Microsoft SQL Server or the gateway:
[Transparent gateway for MICROSOFT SQL SERVER]
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 Microsoft SQL Server components of the target database.
Use error tracing to pinpoint problem areas involving the gateway, the Oracle database server, and Microsoft SQL Server.
You can enable error tracing to record by setting the HS_FDS_TRACE_LEVEL gateway initialization parameter.
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.
A log file is created with each SQL session to collect the following information:
Trace files are created in the ORACLE_HOME\tg4msql\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.
If you are having trouble accessing data from the Microsoft SQL Server database, you might receive the following messages:
ORA-00942: table or view does not exist
[Transparent gateway for MICROSOFT SQL SERVER]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 Microsoft SQL Server database, the trace output might show, for example, that the table or column name in the SQL statement sent to the Microsoft SQL Server 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 Microsoft SQL Server 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 Microsoft SQL Server'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 Microsoft SQL Server vendor for support.
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:
|
 Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|