illustrates the directory hierarchy of the ADR for an Oracle Net Listener instance. Other ADR homes for other Oracle products or components (such as Oracle Automatic Storage Management (Oracle ASM) or Oracle Database) can exist within this hierarchy, under the same ADR base.
lists the values of the path components for an Oracle Connection Manager instance.
illustrates the directory hierarchy of the ADR for an Oracle Connection Manager instance. Other ADR homes for other Oracle products or components (such as Oracle ASM or Oracle Database) can exist within this hierarchy, under the same ADR base.
Within the ADR home directory are subdirectories where each instance, such as the database, listener, Oracle Connection Manager, or client, stores diagnostic data.
lists some of these subdirectories and their contents.
The ADR_BASE is the physical location in which one or more ADR homes are placed. Conceptually, it is the root directory of ADR.
Non-ADR (meaning that the DIAG_ADR_ENABLED parameter is set to OFF) diagnostic and tracing methods are still current and applicable but the parameters are ignored if ADR is enabled. ADR is enabled by default.
Diagnostic parameters are found in the following configuration files:
sqlnet.ora for clients.
listener.ora for listeners.
cman.ora for connection managers.
compares usage of diagnostic parameters found in the sqlnet.ora file used in both ADR and non-ADR-based diagnostics.
compares usage of diagnostic parameters found in the listener.ora file used in both non-ADR and ADR-based diagnostics.
compares usage of diagnostic parameters found in the cman.ora file used in both non-ADR and ADR-based diagnostics.
In the navigator, expand Directory or Local & Service Naming.
Select the net service name or database service.
Choose Command & Test Net Service.
Testing assumes the listener and database are running. If they are not, then see
to start components.
During testing, a Connection Test dialog box appears, providing status and test results. A successful test results in the following message:
The connection test was successful.
If the test was successful, then proceed to Step 6.
If the test was not successful, then do the following:
Ensure the database and listener are running, and then click Test.
Click Change Login to change the username and password for the connection, and then click Test.
If the loopback test passes, then go to .
If the loopback test continues to fail, then contact Oracle Support Services.
Click Close to close the Connect Test dialog box.
Check base connectivity for underlying network transport. Oracle Net technology depends on the underlying network for a successful connection.
Ensure that the Oracle Net foundation layer and the appropriate Oracle protocol support are present by verifying that all Oracle Net Services software has been installed for the client.
Ensure that the client computer has the tnsnames.ora and the sqlnet.ora files in the correct locations.
If you have any other working client computers connecting to the selected Oracle Database, then back up your existing files and copy both the working tnsnames.ora and sqlnet.ora files from the working client computer to the non-working clients. This eliminates the possibility of errors in the files.
Test the Oracle Net foundation layer. You can test using the following command to connect to SQL*Plus:
sqlplus user/password@connect_string
If the connection still fails, then do the following:
Use tracing, as described in section
Check the Oracle Support Web site for a specific diagnostics bulletin on the error received
Contact Oracle Support Services
ORA-03113: TNS:end-of-file on communication channel
Cause: An error has occurred on the database server.
Action: Check the alert_sid.log file on the server. An unexpected end of file was processed on the communication channel. This may be an indication that the communications link may have gone down at least temporarily, or it may indicate that the server has gone down.You may need to modify your retransmission count.
ORA-12154: TNS:could not resolve the connect identifier specified
Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.
Action: Perform the following steps:
Check the type of naming adapters listed in the names.directory_path parameter in the sqlnet.ora file. If none are configured, then use the adapters command to determine which adapters are in use. The following example shows the adapters:
$ adapters
Installed Oracle Net naming methods are:
Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming
NIS Naming
The net service name given in the connect string should be defined for at least one of the naming methods.
Check the resolution path for each adapter for possible problems. For example, ensure that the name given in the connect string is correct and complete, using the full name of the net service if necessary.
When using the local naming method, do the following:
Verify that the tnsnames.ora file exists and is in the correct location. The location is either the ORACLE_HOME/network/admin directory or the directory specified by the TNS_ADMIN environment variable.
Verify there is an entry in the tnsnames.ora file for the name given in the connect string. This net service name should match the name in the tnsnames.ora file exactly if the name is simple and there is not NAMES_DEFAULT_DOMAIN in the sqlnet.ora file, or the net service name is a fully-qualified name. If the net service name in the connect string is simple, then check the NAMES_DEFAULT_DIRECTORY parameter in the sqlnet.ora file. Its value is appended to the net service name given in the connect string. This fully-qualified name should be the entry in the tnsnames.ora file.
If you are connecting from a login dialog box, then verify that you are not placing an at sign (@) before your connect net service name.
Activate client tracing and repeat the operation.
When using the directory naming method, do the following:
Verify the ldap.ora file exists and is in the correct location. The following directories are searched for ldap.ora file in the order given. The ldap.ora file found will be used.
The directory specified by the TNS_ADMIN environment variable.
The ORACLE_HOME/network/admin directory.
The directory specified by the LDAP_ADMIN environment variable.
The ORACLE_HOME/ldap/admin directory.
Verify that the parameters defined in the ldap.ora file are correct, as follows:
The DIRECTORY_SERVERS parameter defines the correct host and port for one or more valid LDAP servers.
The DEFAULT_ADMIN_CONTEXT parameter defines the location of the Oracle Context in this directory which should include the net service entry.
If the ldap.ora file does not exist, then these parameters will be resolved using automatic discovery.
Verify that the LDAP server host and port are defined in DNS.
Verify that the directory has the default Oracle Context defined.
Use the ldapsearch utility or a directory administration tool to verify that the net service object exists in the Oracle Context at the location given by the value of the DEFAULT_ADMIN_CONTEXT parameter.
When using the Easy Connect naming method, do the following:
Verify that the host name give is correct, and is defined in the local host name resolution service, such as local hosts file, DNS, and so on.
When using the external naming method, do the following:
Verify that the NIS file for tnsnames is properly set up.
Check that the net service name matches the tnsnames entry as described in the preceding local naming section.
ORA-12170: TNS:Connect timeout occurred
Cause: The client failed to establish a connection and complete authentication in the time specified by the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in the sqlnet.ora file. This error may be a result of network or system delays, or it may indicate that a malicious client is trying to cause a denial-of-service attack on the database server.
Action: If the error occurred due to system or network delays that are normal for the particular environment, then perform the following steps:
Turn on tracing to determine which clients are timing out.
Reconfigure the SQLNET.INBOUND_CONNECT_TIMEOUT, SQLNET.SEND_TIMEOUT, or SQLNET.RECV_TIMEOUT parameters in sqlnet.ora to a larger value.
If you suspect a malicious client, then perform the following steps:
Restrict access to the client. For example, you can configure parameters for access rights in the sqlnet.ora file.
Locate the IP address of the client in the sqlnet.log file on the database server to identify the source. Remember that an IP address can be forged.
For example, the following sqlnet.log excerpt shows a client IP address of 192.168.2.35.
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.0.0
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.0.0
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.0.0
Time: 03-MAY-:12
Tracing to file: /ora/trace/svr_13279.trc
Tns error struct:
nr err code: 0
ns main err code: 12637
TNS-12637: Packet receive failed
ns secondary err code: 12604
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.35)(PORT=52996))
If the time out occurs before the IP address can be retrieved by the database server, then enable listener tracing to determine the client that made the request.
TNS-12500/ORA-12500: TNS: listener failed to start a dedicated server process
Cause: The listener failed to start the Oracle program. Possible reasons include:
The maximum number of processes allowed for a single user was exceeded
The listener does not have execute permission on the Oracle program
The associated Microsoft Windows service is not started
In some cases, these errors can be caused by the same conditions which cause the following errors:
TNS-12549/ORA-12549
TNS-12540/ORA-12540
TNS-12560/ORA-12560
Action: Perform the appropriate action:
Increase the number of processes by setting the PROCESSES parameter in the database initialization file to a larger value.
Check the listener.log file for detailed error stack information.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that has either not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
Action: Perform the following steps:
Wait a moment, and then try to connect a second time.
Check which services are currently known by the listener by running the Listener Control utility STATUS or SERVICES command.
Check that the SERVICE_NAME parameter in the connect descriptor specifies a service name known by the listener.
Check for an event in the listener.log file.
ORA-12520: TNS:listener could not find available handler for requested type of server
Cause: The type of service handler requested by the client is incorrect or not registered for the requested SERVICE_NAME/INSTANCE_NAME, or the database instance is not registered with the listener.
Action: If you suspect the problem is the wrong type of service handler, then perform the following steps:
If (server=value) is set in the connect descriptor, then ensure that the value is set to the appropriate service handler type for the database, that is, dedicated for dedicated server or shared for dispatchers. You can use the Listener Control utility SERVICES command to see what service handlers are currently registered with the listener.
If USE_DEDICATED_SERVER is set to ON in the sqlnet.ora file, then ensure the database is configured to use dedicated servers. If it is not, then set this parameter to OFF.
Ensure that the database instance is running. If the instance not running, then start it so that it can register with the listener.
ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor
Cause: The instance name in the connect descriptor is incorrect, or the database instance is not registered with the listener.
Action: Perform the following steps:
Ensure that the service name specified in the connect descriptor is correct.
Ensure that the database instance is running. If the instance not running, then start it so that it can register with the listener. You can use the Listener Control utility SERVICES command to see what instances are currently registered with the listener.
ORA-12525: TNS:listener has not received client's request in time allowed
Cause: The client failed to complete its connect request in the time specified by the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. This error may be a result of network or system delays, or it may indicate that a malicious client is trying to cause a denial-of-service attack on the listener.
Action: If the error occurred due to system or network delays that are normal for the particular environment, then reconfigure the INBOUND_CONNECT_TIMEOUT_listener_name parameter in listener.ora to a larger value.
If you suspect a malicious client, then perform the following steps:
Locate the IP address of the client in the listener.log file to identify the source. Remember that an IP address can be forged.
For example, the following listener.log excerpt shows a client IP address of 192.168.2.35.
03-MAY-:35 * &unknown connect data& *
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.35)(PORT=53208)) * establish *
&unknown sid& * 12525
TNS-12525: TNS:listener has not received client's request in time
TNS-12604: TNS: Application timeout occurred
Restrict access to the client. For example, you can configure parameters for access rights in the sqlnet.ora file.
ORA-12533: TNS:illegal ADDRESS parameters
Cause: The protocol specific parameters in the ADDRESS section of the designated connect descriptor are incorrect.
Action: Correct the protocol address.
TNS-12540/ORA-12540: TNS:internal limit restriction exceeded and TNS-00510: Internal limit restriction exceeded
Cause: An internal limit has been exceeded. Possible limits include:
Number of open connections that Oracle Net can process simultaneously
Number of memory buffers which can be used simultaneously
Number of processes a particular database instance is allowed
The first two are examples of hard limits. The third is an example of a limit which can be increased by setting PROCESSES parameter in the database initialization file to a larger value. In this case, a TNS-12500/ORA-12500 error is also returned. In some cases, these errors can be caused by the same conditions which cause TNS-12549/ORA-12549 and TNS-00519 errors.
Action: Wait for the open connections to close and retry. If the error persists, then check the sqlnet.log or listener.log file for detailed error stack information.
TNS-12541/ORA-12541: TNS:no listener
Cause: The connection request could not be completed because the listener is not running.
Action: Perform the following actions:
Ensure that the supplied destination address matches one of the addresses used by the listener.
Verify that the listener is running at the address specified by the request.
Ensure the listener is listening on the host and port specified by the request.
Verify the client is pointing to the listener.
TNS-12549/ORA-12549: TNS:operating system resource quota exceeded and TNS-00519: Operating system resource quota exceeded
Cause: A quota or hard limit imposed by the operating system has been exceeded.
Possible limits include:
The maximum number of processes allowed for a single user
The operating system is running low on paging space
Action: Perform the appropriate action:
Increase the number of processes by setting the PROCESSES parameter in the database initialization file to a larger value.
Check the sqlnet.log or listener.log file for detailed error stack information, such as an operating system error code to help identify which quota has been exceeded.
TNS-12560/ORA-12560: TNS:protocol adapter error occurred
Cause: There was an error when using a particular protocol. This error may be due to incorrect configuration of an ADDRESS parameter or may occur due to errors returned from the underlying protocol or operating system interface.
In some cases, these errors are caused by the same conditions which cause TNS-00510, TNS-00519, TNS-12540/ORA-12540, TNS-12549/ORA-12549 errors.
Action: This error occurs on Microsoft Windows systems only. Perform the following actions:
Select Run from the Microsoft Windows Start menu.
Enter MSCONFIG in the Open field.
Go to the Services tab.
Enable OracleServicesid if it is disabled.
Restart the computer.
Check that Oracle Services has started.
The following example writes all the directory naming entries under dc=us,dc=example,dc=com to the output1.ldi file:
ldifwrite -c ldap -b "dc=us,dc=example,dc=com" -f output.ldif
shows an example of a sqlnet.ora file.
To begin the diagnostic process, determine which section of this document applies to the problem. In the sample files shown in
and , the alias in
is DEV1.WORLD. However, the NAMES.DEFAULT_DOMAIN=WORLD parameter does not exist in . To fix this problem, add the NAMES.DEFAULT_DOMAIN=WORLD parameter anywhere in the sqlnet.ora file. Save the file, and try the connection again.
If the TNS-12154 error still persists, then determine whether the files were transferred from the client to the server and check the configuration files to ensure that CTRL-M (^M) or CTRL-R (^R) characters were not inserted at the ends of any lines. Remove any such characters you may find.
If the characters do not exist, then verify whether the NAMES.DIRECTORY_PATH parameter exists in the sqlnet.ora file and make sure the value in parentheses is TNSNAMES, as follows:
NAMES.DIRECTORY_PATH=(TNSNAMES)
NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT, HOSTNAME)
This parameter is not necessary but if it exists in the sqlnet.ora file and appears as shown in the preceding example, then the configuration files are technically accurate.
At the Linux prompt, echo the TNS_ADMIN environment variable, as follows:
% echo $TNS_ADMIN
If nothing is returned, then set the TNS_ADMIN environment variable to explicitly point to the location of the tnsnames.ora file.
In C shell:
% setenv TNS_ADMIN full_path_to_tnsnames.ora_file
In Korn shell:
% TNS_ADMIN=full_path_to_tnsnames.ora_file; export TNS_ADMIN
Try the connection again.
If the error persists, then add the AUTOMATIC_IPC=OFF parameter to the sqlnet.ora file. If AUTOMATIC_IPC is already set to ON, then change the value to OFF. Try the connection again.
If the error persists, then check the permissions of the tnsnames.ora and sqlnet.ora files and parent directories. Usually the .ora files are either -rwxrwxrwx or -rwxrwx---. Change the permissions of the configuration files to 777 to set the permissions to fully open and try the connection again.
If the error persists, then remove all line feeds and carriage returns so that the net alias is on one line, and try again.
If the error persists, then redo the configuration as follows:
Set the TNS_ADMIN environment variable to /tmp.
Go to the /tmp directory and create a new tnsnames.ora file using a text editor.
Copy the sample tnsnames.ora file from
into the text editor and save the new tnsnames.ora file.
Exit the text editor and at the command line, type:
% sqlplus scott@dev1.world
Enter password: password
This section contains the following topics:
In the navigator pane, expand Profile under the Local heading.
From the list in the right pane, select General.
Click the Logging tab.
Specify the settings.
Select Save Network Configuration from the File menu.
The name of the log file is sqlnet.log.
Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
Click Go to display the Listeners page.
Select a listener, and then click Edit to display the Edit Listeners page.
Click the Logging & Tracing tab.
Specify the settings.
The name of the log file is listener.log.
In the navigator pane, expand Listeners under the Local heading.
Select a listener.
From the list in the right pane, select General.
Click the Logging and Tracing tab.
Specify the settings.
Choose Save Network Configuration from the File menu.
The name of the log file is listener.log.
shows a log file excerpt with a successful connection request.
shows a log file excerpt with a successful execution of the STATUS command by host sales-server. It is followed by an unsuccessful connection attempt by a client with an IP address of 192.168.2.35. This connection attempt resulted in an
error message. This error occurs when a client fails to complete its connection request in the time specified by the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. This client could be attempting a denial-of-service attack on the listener.
shows a typical gateway log file.
shows the log file entries and their descriptions.
This section contains the following topics:
This section contains the following topics:
You can manually add the TNSPING utility tracing parameters described in
to the sqlnet.ora file. The TNSPING utility determines whether a service, such as a database or other TNS services, on a Oracle Net network can be successfully reached.
In the navigator pane, expand Profile under the Local heading.
From the list in the right pane, select General.
Click the Tracing tab.
Specify the settings.
Choose Save Network Configuration from the File menu.
The name of the trace file for the client is sqlnet.trc. The name of the trace file for the server is svr_pid.trc.
Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
Click Go to display the Listeners page.
Select a listener, and then click Edit to display the Edit Listeners page.
Click the Logging & Tracing tab.
Specify the settings.
The name of the trace file is listener.trc.
In the navigator pane, expand Listeners from the Local heading.
Select a listener.
From the list in the right pane, select General.
Click the Logging and Tracing tab.
Specify the settings.
Choose Save Network Configuration from the File menu.
The most efficient way to evaluate error codes is to find the most recent nserror entry logged, as the session layer controls the connection. The most important error messages are the ones at the bottom of the file. They are the most recent errors and the source of the problem with the connection.
For information about the specific return codes, use the Oracle error tool oerr, by entering the following at any command line:
oerr tns error_number
As an example, consider the following nserror entry logged in the trace file shown in :
[22-MAY-:09:625] nserror: nsres: id=0, op=68, ns=12537, ns2=12560;
nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
In the preceding entry, the main TNS error is 12537, and its secondary error is 12560. The protocol adapter error is 507. Using oerr, you can find out more information about return codes 1, and 507. User input is shown in bold in the following examples.
oerr tns 12537
1, "TNS:connection closed"
// *Cause: "End of file" condit partner has disconnected.
// *Action: N this is an information message.
oerr tns 12560
1, "TNS:protocol adapter error"
// *Cause: A generic protocol adapter error occurred.
// *Action: Check addresses used for proper protocol specification. Before
// reporting this error, look at the error stack and check for lower level
// transport errors. For further details, turn on tracing and reexecute the
// operation. Turn off tracing when the operation is complete.
oerr tns 507
0, "Connection closed"
// *Cause: Normal "end of file" condit partner has
disconnected.
// *Action: N this is an information message.
If no options are provided, then the default is -odt -e0 -s, which provides detailed connectivity and TTC events, error level zero (0), and statistics in the trace file.
shows how the Trace Assistant converts the trace file information into a more readable format using the -e1 option.
However, other errors may also exist within the trace file that were not logged from the nserror function.
The packets being sent or received have a prefix of ---& Send nnn bytes or &--- Received nnn bytes showing that this node is sending or receiving a packet of a certain type and with nnn number of bytes. This prefix enables you to determine if the node is the client or the database server. The connection request is always sent by the client, and received by the database server or listener.
shows detailed information from the -od option. The output shows all of the details sent along with the connect data in negotiating a connection.
shows detailed TTC information from the -ot option.
shows detailed SQL information from the -ouq option. On each line of the output, the first item displayed is the actual request made. The second item shows on what cursor that operation has been performed. The third item is either a listing of the SQL command or flag that is being answered. The number of bytes sent and received are displayed at the far right. A flag can be one of the following:
!PL/SQL = Not a PL/SQL request
COM = Commit
IOV = Get I/O Vector
DEFN = Define
EXEC = Execute
FETCH = Fetch
CAN = Cancel
DESCSEL = Describe select
DESCBND = Describe Bind
BND = Bind
PARSE = Parse
EXACT = Exact
shows output for connection ID 00008 from the -li 00008 option.
Scripting on this page enhances content navigation, but does not change the content in any way.}