Oracle ora 12560 tns ошибка адаптера протокола

16 ответов

## Base XZ03001

SID_LIST_LISTENER_XZ03001=(SID_LIST=(SID_DESC=(ORACLE_HOME =
E:oracleproduct11.2.0dbhome_1)(SID_NAME= XZ03001)))

LISTENER_XZ03001=(DESCRIPTION_LIST=(ADDRESS=(PROTOCOL =
TCP)(HOST=MyComputerName)(PORT= 1521)))

DIAG_ADR_ENABLED_LISTENER_XZ03001=ON

ADR_BASE_LISTENER_XZ03001=E:oracle

    SCHEMADEV =
            (DESCRIPTION =
                     (ADDRESS_LIST =
                     (ADDRESS = (PROTOCOL = TCP)(HOST = dabase_ip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SCHEMADEV)
    )
  )

sqlplus sys/mypassword as sysdba  

sqlplus sys/[email protected] as sysdba

tnsping XE

TNS_ADMIN=[oracle express installation dir]apporacleproduct11.2.0servernetworkADMIN

ORA-12560: TNS:protocol adapter error при подключении к БД. Как исправить?

Есть БД Oracle на виртуалке. Доступ с самой машины через sqlplus работает. Подключиться по сети через sqlplus не получается. tnsping по сети работает. :

На обоих машинах в sqlnet.ora написано:

При подключении писал

и получаю ошибку.

ORA-12560: TNS:protocol adapter error

Попробовал подключиться так:

ORA-12154: TNS: could not resolve the connect identifier specified.

tnsping tns.domain_name выполняется без ошибок. К другим БД на других машинах в сети подключаюсь. Куда посмотреть, как исправить?

1 ответ 1

Подведу итоги комментариям.

Для подключения к удалённой базе устанавливать ORACLE_SID не имеет смысла. Установив эту переменную можно подключиться по Bequeath протоколу без указания строки соединения и без участия прослушивателя, но только на той же машине, на которой установлена база, т.е. локально.

Если на клиентской машине установлено несколько клиентов настоятельно рекомендуеся файлы сетевой конфигурации, tnsnames.ora, sqlnet.ora и др., поместить в отдельную папку (где угодно) и создать переменную окружения TNS_ADMIN указываюшую на эту папку.

Порядок поиска файлов на Windows:

• окружение TNS_ADMIN
• реестр TNS_ADMIN
• %ORACLE_HOME%networkadmin

ORACLE_HOME из окружения всегда имеет преимущество над записью из рееестра.

Использование реестра плохо документировано, т.е. какой дом оракла будет найден в различных клиентах, судя по диспутам на различных форумах, не совсем понятно.
Насколько мне известно — если убираешь базу с машины с экзотической операционкой, надо обязательно ручками почистить реестер, чтобы его устаревшие записи потом где-то опять не всплыли. Повидимому тоже самое относится и к клиентам.

В данном конкретном случае, по видимому tnsping и sqlplus нашли два различных tnsnames.оra и только установкой окружения ORACLE_HOME удалось добится желаемого результата.

Источник

How to Resolve ORA-12560 on Windows Server

In this post, I’ll talk about 3 error patterns of ORA-12560 on Windows platform.

A. Database Service is Stop

When we tried to connect to the database via SYSDBA, we got ORA-12560.

C:UsersAdministrator>sqlplus / as sysdba
.
ERROR:
ORA-12560: TNS:protocol adapter error

This is because the database service is stopped by someone or something, you need to start or restart to get the database service working. This is the most common problem of ORA-12560.

Datapatch

If you saw the error in datapatch (i.e. SQL patching) after issuing datapatch -verbose like this:

C:UsersAdministrator>datapatch -verbose
.
Connecting to database.
Error: prereq checks failed!
Database connect failed with: ORA-12560: TNS:protocol adapter error (DBD ERROR: OCIServerAttach)
.

Then you know what to do it to solve it.

B. Incorrect ORACLE_SID

First of all, allow me reproduce the error ORA-12560 for you.

Check the instance name and current status.

C:UsersAdministrator>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 24 20:31:22 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 — 64bit Production

SQL> select instance_name, status from v$instance;

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 — 64
bit Production

As you can see, I connect the database with OS authentication without problem.

Now I set a non-existing ORACLE_SID .

C:UsersAdministrator>set ORACLE_SID=ERPAPP2
C:UsersAdministrator>echo %ORACLE_SID%
ERPAPP2

Then connect again.

C:UsersAdministrator>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 24 20:32:36 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name: ^C

Oh, I got ORA-12560. The response to an unrecognized ORACLE_SID is very different in Windows from Linux. In Linux, it still connect for you, but show «Connected to an idle instance».

In such situation, you’d better to check ORACLE_SID in software registry editor.

Windows Regedit — Oracle Software — Check «ORACLE_SID»

In this case, we should set the correct ORACLE_SID, then try again.

C. Reach Limitation of PROCESSES

Found repeated Oracle TNS error messages in listener log during peak-hours on Windows Server 2008.

.
19-Oct-2010 05:32:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL11)(CID=(PROGRAM=C:ap001client.exe)(HOST=WIN3451)(USER=EDCHEN))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.2.3)(PORT=49874)) * establish * ORCL11 * 12518
TNS-12518: TNS:listener could not hand off client connection
ORA-12560: TNS:protocol adapter error
.

The error pattern combined with TNS-12518 and ORA-12560, and it repeated itself until some point off the peak.

I think restarting the database should work, but it should also cost your time or credits to explain the inconvenience to your users.

I suspect that PMON was not responsive enough to reclaim the timeout or dead processes and let the whole database reach the maximum number of processes, and eventually, no more new connections will be allowed.

If you are not convenient to patch the database. Here are two alternatives that you may choose:

Raise the maximum number of processes online: SQL> alter system set processes=3000 scope=both;

The whole concept of this tactic is to make the database survive through the daily peak-hours and let PMON can take its time to reclaim the unused processes.

Force all users to connect the database through shared server mode, except DBA. For example: SQL> alter system set shared_servers = 200 scope=both;

SQL> alter system set dispatchers='(address=(protocol=tcp)(PORT=1521)) (dispatchers=20)(sessions=1000)’ scope=both;

In the above statement, we force all connections coming from port 1521 to use dispatchers (shared server mode). DBA and specific users can use other port like 1522 to connect as dedicated mode to finish their jobs. At the beginning, users might feel that the speed of responses of database is slightly affected. I think it’s a trade-off that you must think over before making the decision.

Since the shared server processes will be soon allocated in the server when the database startup, the number of processes can be easily controlled by DBA. For more shared server configuration, you can refer to Oracle documentation: Configuring Oracle Database for Shared Server.

2 thoughts on “How to Resolve ORA-12560 on Windows Server”

I am looking for your advice how to properly install Oracle 18c and connect to the DB via SQL developer.
I have downloaded all software from Oracle website and followed the installation process without any errors. When prompted I have allowed Java to connect via private and public networks.
1. After that I tried to connect to DB via SQL developer which gave an error.
2. I tried to connect to DB via SQLPlus. Not possible. Error shown in the printscreen – 12560.
3. I have tried to reinstall all software couple of times, making sure to clean all files and Win10 register. In the print screen from the tnsnames.ora you can see it just after the installation and checking all services are running. Some part is missing there, not sure what and why.
4. I run all from Windows account with admin privileges.
5. I have Win 10 Pro version. Connected to the Internet via a WiFi network, via router and LTE modem.
6. I tried to edit tnsnames.ora to change localhost name to my IP address obtained from ipconfig.
7. I tried to edit listener.ora to change localhost name to my IP address obtained from ipconfig.
8. I ensured all Oracle services are running. I’ve restarted them multiple times to see no effect.
9. I also tried to check connection via the Command line. Error again – 12170 and 12560
10. I also added manual to Environmental Variables an entry with Oracle_SID – name XE. No effect.
11. I checked there is only 1 DB instance installed. All paths are correctly finding tnsnames.ora and listener.ora files.
12. During my troubleshooting some other errors popped, like 12514.

Have you ever checked your listener status?

Источник

Local SQL*Plus Connection failing with ORA-12560: TNS:protocol adapter error (Doc ID 2531667.1)

Last updated on AUGUST 23, 2021

Applies to:

Symptoms

sqlplus / as sysdba (Bequeath) or connecting using other non-sysdba usersВ isВ failing withВ ORA-12560: TNS:protocol adapter error

Changes

В There are several changes which can lead to this error. Below are few of the themВ

• Incomplete Upgrade or patching
• Using Incorrect PATH / Environment Variables to startup the instance
• Security products

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

Database on UNIX platforms:

3. Environment variables:

Database on WINDOWS platforms: В

1. Environment Variables:

2.В Failed upgrade or any changes.

3. Service is not started.

4.В Recreate the OracleService .

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

Ora 12560 tns protocol adapter error ошибка

Oracle Call Interface Programmer’s Guide for additional information about the location of the client ADR Home

Oracle Database Net Services Reference for descriptions of the diagnostic parameters

16.1.1 ADRCI: ADR Command Interpreter

ADRCI is a command-line tool that is part of the fault diagnosability infrastructure. ADRCI enables you to do the following:

View diagnostic data within ADR

Package incident and problem information into a zip file for transmission to Oracle Support Services

Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and so on.

ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can run scripts of ADRCI commands in the same way that SQL*Plus runs scripts with SQL and PL/SQL commands.

To view trace files using ADRCI, enter ADRCI at a command line. The following are common ADRCI commands used to check a client:

In the preceding commands, the SHOW ALERT command shows the log.xml file in a text editor, such as VI. The SHOW BASE -product client command displays the value of the ADR_BASE directory for the client. Use that value for client in the SET BASE command.

The following are common ADRCI commands used to check a server:

Other ADRCI command options are available for a more targeted Oracle Net trace file analysis. Type HELP at the ADRCI prompt for help documentation.

Oracle Database Utilities for additional information about ADRCI

16.2 Diagnosing Oracle Net Services

Any underlying fault, noticeable or not, is reported by Oracle Net Services with an error number or message. The error number and message provide useful information for diagnosing the problem, but may not always identify the actual problem. The tasks in this section help determine which parts of Oracle Net Services do function properly rather than the parts which do not work. They also help to determine in which of the following categories the fault belongs:

Operating system layer

Other network layers

Testing the various network layers progressively should, in most cases, uncover any problem.

This section contains the following topics:

16.2.1 Diagnosing Server Problems

To start diagnosing server problems, you should answer the following questions:

Is any other system such as a workstation or server able to connect to the server using Oracle Net?

Has the server, database, or listener configuration remained the same for some time?

If you answered yes to either of the preceding questions, then go to «Diagnosing Client Problems» .

If you are unsure, or answered no to any of the preceding questions, then use the tasks in this section to diagnose the problem. Diagnosing Oracle Net Services on the server involves the following tasks:

Task 1 Verify the Database is Running

To check that the database is up, log in to the database and connect with a valid username and password. For example:

A message appears, confirming that you are connected with the database. If you receive the following errors, then ask the database administrator to assist you:

ORA-1017: invalid U/P

ORA-1034: Oracle not available

Task 2 Perform a Loopback Test

A loopback test uses Oracle Net to go from the database server back to itself, bypassing the Interprocess Communication (IPC) protocol. Many network protocols provide a means of testing network connections. The PING utility can be used with a TCP/IP network. Performing a successful loopback verifies that Oracle Net is functioning on the database server.

The following procedure describes how to perform a loopback test from the server to the database:

Ensure that the listener.ora , tnsnames.ora , and sqlnet.ora files exist in the correct locations, as described in «Using Localized Management» .

Start Oracle Net Manager.

In the navigator, expand the Directory or Local option.

Expand Service Naming to view the available network service and database names.

Select the network service name or database service.

Choose Command , and then select Test Net Service .

Testing assumes the listener and database are running. If they are not, then see «Starting Oracle Net Listener and the Oracle Database Server» to start components.

During testing, a Connection Test dialog box appears, providing status and test results. A successful test results in the following message:

If the test was successful, then proceed to step 7.

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 «Diagnosing Client Problems» .

If the loopback test continues to fail, then contact Oracle Support Services.

Click Close to close the Connect Test dialog box.

16.2.2 Diagnosing Client Problems

Verify at least one of the following statements. This will help you decide if it is a client problem.

The database server passed a loopback test, showing the connection worked.

Other computers connect using Oracle Net Services to the same database.

Connections from this workstation worked before making changes on this computer, such as the installation of a new product or modification to the network configuration.

The following procedure describes how to perform diagnostics on the client:

Check that you have installed the same protocol support that was installed on the database server.

On Linux and UNIX platforms you can use the ADAPTERS utility to verify protocol support. On the database server, run the following command from the ORACLE_HOME/bin directory to display the protocol support, naming methods, and security options linked with the oracle executable:

The adapters utility displays output similar to the following:

On the client, run the adapters command from the ORACLE_HOME/bin directory to display the configured Oracle protocol support, naming methods, and security options. The ADAPTERS utility displays output similar to the following:

The DES , DES40 , 3DES 112 , 3DES 168 , RC4 40 , RC4 56 , RC4 128 , RC4 256 , and MD5 algorithms are deprecated in this release.

To transition your Oracle Database environment to use stronger algorithms, download and install the patch described in My Oracle Support note 2118136.2.

RAW is an internal protocol used by Oracle Net.

Oracle Database Administrator’s Reference for additional information about the adapters utility

Check base connectivity for underlying network transport. Oracle Net technology depends on the underlying network for a successful connection.

Table 16-7 Verify Base Connectivity for Network Transport

Use terminal emulation or file transfer utilities, (PING, FTP, TELNET) from the client to the database server.

See other computers or servers on the Microsoft network.

Ensure that you are able to share drives within the network.

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 any other working client computers are 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:

Do not use the TNSPING utility. The TNSPING utility works like the TCP/IP ping utility and does not create and open a socket, nor does it connect with the listener. It only shows that the listener is present on the database server.

If the connection still fails, then do the following:

Check the Oracle Support Services website for a specific diagnostics bulletin on the error received.

Contact Oracle Support Services.

16.3 Resolving the Most Common Error Messages for Oracle Net Services

Due to the complexity of network communications, network errors may originate from a variety of sources, for a variety of reasons. If an error occurs, then applications such as SQL*Plus, that depend on network services from Oracle Net Services, normally generate an error message.

A list of the most common network error messages follows:

For information about the specific error messages, use the Oracle error tool oerr , by entering the following command at any command line:

In the preceding command, code is the type of message, such as ORA and TNS, and error_number is the number associated with the error message.

Oracle Database Error Messages for a complete listing of error messages

16.3.1 ORA-03113: End-of-file on Communication Channel

This message indicates that an error has occurred on the database server.

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.

16.3.2 ORA-12154: Could Not Resolve the Connect Identifier Specified

This message indicates that 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 network service name then the network service name could not be found in a naming method repository, or the repository could not be located or reached.

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:

The network 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 network service if necessary.

In addition to the preceding steps, perform the steps depending on your naming methods:

16.3.2.1 ORA-12154 Error When Using the Local Naming Method

Perform the following steps when using the local naming method:

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 network 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 network service name is a fully-qualified name. If the network service name in the connect string is simple, then check the NAMES_DEFAULT_DOMAIN parameter in the sqlnet.ora file. Its value is appended to the network 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 the connect network service name.

Activate client tracing, and repeat the operation.

16.3.2.2 ORA-12154 Error When Using the Directory Naming Method

Perform the following steps when using the directory naming method:

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 network service entry.

If the ldap.ora file does not exist, then these parameters are 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 network service object exists in the Oracle Context at the location given by the value of the DEFAULT_ADMIN_CONTEXT parameter.

16.3.2.3 ORA-12154 Error When Using the Easy Connect Naming Method

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.

16.3.2.4 ORA-12154 Error When Using the External Naming Method

Perform the following steps when using the external naming method:

Verify that the NIS file for tnsnames map is properly set up.

Check that the network service name matches the tnsnames entry as described in «ORA-12154 Error When Using the Local Naming Method» .

«Example of Troubleshooting a TNS-12154 Error» for additional information about troubleshooting the error

«Using Localized Management» for configuration file location information

16.3.3 ORA-12170: Connect Timeout Occurred

This message indicates that 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.

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 the sqlnet.ora file to larger values.

If you suspect a malicious client, then perform the following steps:

Restrict access to the client. 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. Keep in mind that an IP address can be forged.

For example, the following sqlnet.log excerpt shows a client IP address of 192.0.2.35 .

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.

«Limiting Resource Consumption by Unauthorized Users» for additional information about setting the SQLNET.INBOUND_CONNECT_TIMEOUT parameter

16.3.4 TNS-12500 or ORA-12500: Listener Failed to Start a Dedicated Server Process

These messages indicate that 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 or ORA-12549

TNS-12540 or ORA-12540

TNS-12560 or ORA-12560

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.

16.3.5 ORA-12514: Listener Does Not Currently Know of Service Requested in Connect Descriptor

This message indicates that 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.

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.

16.3.6 ORA-12520: Listener Could Not Find Available Handler for Requested Type of Server

This message indicates that the type of service handler requested by the client is incorrect or not registered for the requested SERVICE_NAME/INSTANCE_NAME parameters, or the database instance is not registered with the listener.

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 the USE_DEDICATED_SERVER parameter 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.

«Monitoring Services of a Listener» for additional information about service handlers

16.3.7 ORA-12521: Listener Does Not Currently Know of Instance Requested in Connect Descriptor

This message indicates that the instance name in the connect descriptor is incorrect, or the database instance is not registered with the listener.

Perform the following steps:

Ensure the service name specified in the connect descriptor is correct.

Ensure 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.

«Monitoring Services of a Listener» for additional information about SERVICES command

16.3.8 ORA-12525: Listener Has Not Received Client’s Request in Time Allowed

This message indicates that 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.

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. Keep in mind that an IP address can be forged.

For example, the following listener.log file excerpt shows a client IP address of 192.0.2.35 .

Restrict access to the client. You can configure parameters for access rights in the sqlnet.ora file.

«Limiting Resource Consumption by Unauthorized Users» for additional information about setting the INBOUND_CONNECT_TIMEOUT_ listener_name parameter

16.3.9 ORA-12533: Illegal Address Parameters

This message indicates that the protocol specific parameters in the ADDRESS section of the designated connect descriptor are incorrect.

Correct the protocol address.

This error is often caused by hand-editing of the tnsnames.ora file. Only edit the tnsnames.ora file using Oracle Enterprise Manager Cloud Control or Oracle Net Manager.

16.3.10 TNS-12540 or ORA-12540: TNS:Internal Limit Restriction Exceeded and TNS-00510: Internal Limit Restriction Exceeded

These messages indicate that an internal limit has been exceeded. Possible limits include the following:

Number of open connections that Oracle Net can process simultaneously

Number of memory buffers that 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 or ORA-12500 error is also returned. In some cases, these errors can be caused by the same conditions which cause TNS-12549 or ORA-12549, and TNS-00519 errors.

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.

16.3.11 TNS-12541 or ORA-12541: TNS:No Listener

These messages indicate that the connection request could not be completed because the listener is not running.

Perform the following actions:

Ensure the supplied destination address matches one of the addresses used by the listener.

Verify 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.

16.3.12 TNS-12549 or ORA-12549: TNS:Operating System Resource Quota Exceeded and TNS-00519: Operating System Resource Quota Exceeded

These messages indicate that 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

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.

16.3.13 TNS-12560 or ORA-12560: TNS:Protocol Adapter Error Occurred

These messages indicate that 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 or ORA-12549 errors.

These errors occur 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 OracleService sid if it is disabled.

Restart the computer.

Check that Oracle Services started.

16.3.14 Directory Naming Errors

Directory naming issues associated with connectivity errors for database service or network service name entries in a directory server require analysis of the data. You can analyze the data contained within a directory server with the ldifwrite command line tool. The ldifwrite tool is an Oracle Internet Directory tool.

The ldifwrite tool can be used to convert all or part of the information residing in a directory server to LDIF . The ldifwrite tool performs a subtree search, including all entries following the specified distinguished name (DN) , including the DN itself.

The ldifwrite tool syntax is as follows:

Table 16-8 lists ldifwrite tool arguments and descriptions for each.

Table 16-8 ldifwrite Arguments

The network service name or database service name that connects to the directory server.

The base of the subtree to be written out in LDIF format.

The output file name.

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

Check the ldap.ora file to determine the base_DN value. It is the same as the DEFAULT_ADMIN_CONTEXT entry in the ldap.ora file.

16.4 Troubleshooting Suggestions for Oracle Net Services

The following suggestions may be useful when diagnosing network problems:

Use the node or network address during configuration instead of the name of the server computer. This eliminates any internal lookup problems and make the connection slightly faster.

If you are using TCP/IP addresses, then use the IP address rather than the host name. For example, change the HOST= server_name line in the tnsnames.ora file to the IP address, such as HOST=192.0.2.5 .

Perform a loopback test on the server as described in Task 2, «Perform a Loopback Test». If the test passes, then use FTP to send the tnsnames.ora and sqlnet.ora files to the client.

Check the systems between the client and the server. If it is a wide area network (WAN), then identify any intermediate systems that may not work correctly. If all computers are fine, then the problem may be a timing issue.

Verify whether there is a timing issue. Timing issues are associated with an ORA-12535 error in the client log files.

To resolve a timing issue, try speeding up the connection by using exact addresses instead of names and increase the INBOUND_CONNECT_TIMEOUT_ listener_name parameter in the listener.ora file. The default value for this parameter is 10 seconds.

Determine which Oracle applications are failing. SQL*Plus may work, but CASE tools may not. If you determine the problem is a data volume issue, then try to transfer a large (5 MB) file with the base connectivity.

16.4.1 Questions to Consider When Troubleshooting Oracle Net Services

The following questions can help diagnose a problem:

Do all computers have a problem, or is it just one?

If one computer works and another does not, and the same software (Oracle and third-party products) is installed on each computer, then, if possible, swap out the network cables to see if the problem occurs on the second client. If it does occur, then it indicates that the problem has something to do with the client/server connection and is not local to the client.

What kind of connections exist between the client and the server, for example, X.25, ISDN, or leased line?

Sniffers and LAN analyzers are useful for locating intermittent connection failures, and detecting time outs and resent packets. You can also see which side is waiting for a response.

16.5 Example of Troubleshooting a TNS-12154 Error

This section offers some solutions for the TNS-12154 error. The TNS-12154 error is encountered when SQL*Net cannot find the connect identifier specified for a connection in the tnsnames.ora file or other naming adapter.

Before attempting to resolve the problem, it may be helpful to print out or view the tnsnames.ora file and the sqlnet.ora file. Looking at these files at the same time is helpful because references are made to both.

In this example, the tnsnames.ora and sqlnet.ora files are located in the default network administration directory on the client system.

Be sure that the tnsnames.ora file and the sqlnet.ora file resemble the following examples.

Example 16-1 shows an example of a tnsnames.ora file.

Example 16-1 tnsnames.ora Sample

Example 16-2 shows an example of a sqlnet.ora file.

Example 16-2 sqlnet.ora Sample

The alias in Example 16-1 is DEV1.WORLD . However, the NAMES.DEFAULT_DOMAIN = WORLD parameter does not exist in Example 16-2. 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.

16.6 Logging Error Information for Oracle Net Services

All errors encountered by Oracle Net Services are appended to a log file for evaluation by a network or database administrator. The log file provides additional information for an administrator about on-screen error messages. The error stack in the log file shows the state of the software at various layers.

To ensure that all errors are recorded, logging cannot be disabled on clients or name servers. Furthermore, only an administrator may replace or erase log files. The log file for the listener includes audit trail information about every client connection request, and most listener control commands.

This section contains the following topics:

16.6.1 Oracle Net Error Stacks

Log files provide information contained in an error stack. An error stack refers to the information that is produced by each layer in an Oracle communications stack as the result of a network error.

The error stack components are described in Table 16-9.

Table 16-9 Error Stack Components

Network Interface. This layer provides a generic interface for Oracle clients, servers, or external processes to access Oracle Net functions. The NI layer handles the «break» and «reset» requests for a connection.

Network Session (main and secondary layers). These layers receive requests from NI, and settle all generic computer-level connectivity issues, such as:

The location of the server or destination (open, close functions).

Whether one or more protocols are involved in the connection (open, close functions).

How to handle interrupts between client and server based on the capabilities of each (send, receive functions).

Network Authentication. This layer negotiates authentication and encryption requirements.

Network Transport (main, secondary, and operating system layers). These layers map Oracle Net foundation layer functionality to industry-standard protocols.

16.6.1.1 Understanding Error Stack Messages

Suppose that a user of a client application tries to establish a connection with a database server using Oracle Net and TCP/IP, by entering the following commands:

When the commands are entered, the following error displays:

This message indicates that the connection to the server failed because the database could not be contacted. Although the application displays only a one-line error message, an error stack that is much more informative is recorded in the log file by the network layer.

On the client side, the sqlnet.log file as shown in Example 16-3 contains an error stack corresponding to the ORA-12543 error.

Example 16-3 sqlnet.log File

16.6.2 Oracle Net Services Log File Names

Each Oracle Net Services component produces its own log file. When using ADR, the default, the log file names are log.xml in the appropriate alert directory. Table 16-10 lists the default log file names and lists the components that generate the log files that appear in the ADR/diag/ instance_name /trace directory.

Table 16-10 Log File Names When Using ADR

Client or database server

Oracle Connection Manager listener

Oracle Connection Manager alert log

16.6.3 About the Logging Parameters

Parameters that control logging, including the type and amount of information logged, and the location where the files are stored, are set in the configuration file of each network component as described in Table 16-11.

Table 16-11 Location of Log Parameters

Oracle Connection Manager processes

If the ADR_ENABLED parameter is set to ON , then all logging parameters are set by ADR. Using Oracle Net Manager to change the parameters will not work.

This section contains the following topics:

Oracle Database Net Services Reference for additional information about the parameters

16.6.3.1 sqlnet.ora Log Parameters

Table 16-12 describes the log parameters settings that can be set in the sqlnet.ora file.

Table 16-12 sqlnet.ora Log Parameters

You must set this parameter manually.

The ADR_BASE parameter specifies the base directory for storing tracing and logging incidents.

Use this parameter when DIAG_ADR_ENABLED is set to ON .

You must set this parameter manually.

The DIAG_ADR_ENABLED parameter indicates whether ADR tracing is enabled.

When the DIAG_ADR_ENABLED parameter is set to OFF , non-ADR file tracing is used.

Client Information: Log Directory

The destination directory for the client log file. By default, the client directory is the current working directory. This parameter is disabled when ADR_ENABLED is set to ON .

Server Information: Log Directory

The destination directory for the database server log files. By default the server directory is ORACLE_HOME/network/log . This parameter is disabled when ADR_ENABLED is set to ON .

Client Information: Log File

The name of the log file for the client. By default the log name is sqlnet.log .

You must set this parameter manually.

The name of the log file for the database server. By default the log name is sqlnet.log .

16.6.3.2 listener.ora Log Parameters

Table 16-13 describes the log parameters settings that can be set in the listener.ora file.

Table 16-13 listener.ora Log Parameters

You must set this parameter manually.

The ADR_BASE_ listener_name parameter specifies the base directory for storing which tracing and logging incidents.

Use when DIAG_ADR_ENABLED_ listener_name is set to ON .

You must set this parameter manually.

The DIAG_ADR_ENABLED_ listener_name parameter indicates whether ADR tracing is enabled.

When DIAG_ADR_ENABLED_ listener_name is set to OFF , non-ADR file tracing is used.

The destination directory and file for the log file that is automatically generated for listener events. By default the directory is ORACLE_HOME/network/log , and the file name is listener.log . These parameters are disabled when ADR_ENABLED is set to ON .

16.6.3.3 cman.ora Log Parameters

Table 16-14 describes the log parameters settings that can be set in the cman.ora file.

Table 16-14 cman.ora Log Parameters

The ADR_BASE parameter specifies the base directory for storing tracing and logging incidents.

Use this parameter when DIAG_ADR_ENABLED is set to ON .

The DIAG_ADR_ENABLED parameter indicates whether ADR tracing is enabled.

When the DIAG_ADR_ENABLED parameter is set to OFF , non-ADR file tracing is used.

The event groups that are logged. Multiple events may be designated using a comma-delimited list. This parameter accepts the following values:

INIT_AND_TERM : initialization and termination

MEMORY_OPS : memory operations

CONN_HDLG : connection handling

PROC_MGMT : process management

REG_AND_LOAD : registration and load update

WAKE_UP : events related to CMADMIN wakeup queue

TIMER : gateway timeouts

CMD_PROC : command processing

RELAY : events associated with connection control blocks

The destination directory for log files.

By default, the directory is ORACLE_HOME/network/log .

This parameter is disabled when ADR_ENABLED is set to ON .

The level of logging. Four levels are supported:

off (default): no logging

user : user log information

admin : administrative log information

support : Oracle Support Services information

16.6.4 Setting Logging Parameters in Configuration Files

You configure logging parameters for the sqlnet.ora file with Oracle Net Manager and for the listener.ora file with either Oracle Enterprise Manager Cloud Control or Oracle Net Manager. You must manually configure cman.ora file logging parameters.

This section contains the following topics:

16.6.4.1 Setting Parameters for the sqlnet.ora File Using Oracle Net Manager

The following procedure describes how to set the logging parameters in the sqlnet.ora file.

Start Oracle Net Manager.

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.

Choose Save Network Configuration from the File menu.

The name of the log file is sqlnet.log .

16.6.4.2 Setting Parameters for the listener.ora File Using Oracle Enterprise Manager Cloud Control

The following procedure describes how to set the logging parameters in the listener.ora file using Oracle Enterprise Manager Cloud Control:

Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.

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 .

16.6.4.3 Setting Parameters for the listener.ora File Using Oracle Net Manager

The following procedure describes how to set the logging parameters in the listener.ora file using Oracle Net Manager:

Start Oracle Net Manager.

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 .

16.6.5 Setting Logging During Control Utilities Runtime

You can set logging during control utility runtime. Setting logging with a control utility does not set parameters in the *.ora files, and the setting is only valid for the control utility session.

The following settings can be set for a control utility:

For a listener, use the SET LOG_FILE and SET LOG_DIRECTORY commands from the Listener Control utility.

For an Oracle Connection Manager, use the SET LOG_DIRECTORY , SET LOG_LEVEL , and SET EVENT commands from the Oracle Connection Manager control utility.

If the ADR_ENABLED parameter is set to ON , then all logging parameters are set by ADR. Using Oracle Connection Manager to change the parameters will not work.

16.6.6 Using Log Files

The following procedure describes how to use a log file to diagnose a network error:

Review the log file for the most recent error number received from the application. This is usually the last entry in the log file.

Starting from the bottom of the file, locate the first nonzero entry in the error report. This is usually the actual cause.

If that error does not provide the information, then review the next error in the log until you locate the correct error information.

If the cause of the error is still not clear, then turn on tracing and repeat the command that produced the error message.

16.6.7 Analyzing Listener Log Files

This section describes what is recorded in the listener log file. This section contains the following topics:

16.6.7.1 Listener Log Audit Trail Information

The listener log file contains audit trail information that enables you to collect and analyze network usage statistics, as well as information indicating the following:

A client connection request

A RELOAD , START , STOP , STATUS , or SERVICES command issued by the Listener Control utility

You can use the audit trail information to view trends and user activity by first storing it in a table and then collating it in a report format. To import the data into a table, use an import utility such as SQL*Loader.

16.6.7.1.1 Format of the Listener Log Audit Trail

The audit trail formats text into the following fields:

Properties of the audit trail are as follows:

Each field is delimited by an asterisk ( * ).

Protocol address information and service name or SID information appear only when a connection is attempted.

A successful connection or command returns a code of zero.

A failure produces a code that maps to an error message.

Example 16-4 shows a log file excerpt with RELOAD command request.

Example 16-4 Listener Log Event for Successful RELOAD Request

Example 16-5 shows a log file excerpt with a successful connection request.

Example 16-5 Listener Log Events for a Successful Connection Request

Example 16-6 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.0.2.35 . This connection attempt resulted in an ORA-12525: Listener Has Not Received Client’s Request in Time Allowed 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.

Example 16-6 Listener Log Events for an Unsuccessful Connection Request

Oracle Database Error Messages for a complete listing of error messages

16.6.7.2 Listener Service Registration Event Information

The listener records service registration events. During service registration, the Listener Registration (LREG) process provides the listener with information about the following:

Service names for each running instance of the database

Instance names of the database

Service handlers (dispatchers or dedicated servers) available

Dispatcher, instance, and node load information

Dynamic listening endpoints

The recorded service registration-related events listed in Table 16-15 are listed in the listener.log file:

Table 16-15 Service Registration Event Log Information

The listener received registration information for an instance.

The listener received updated registration information for a particular instance, such as dispatcher or instance load information.

The listener lost its connection to Listener Registration (LREG). All registration information for the instance is discarded. Clients are unable to connect to the instance until LREG registers it again.

16.6.7.2.1 Format of the Listener Service Registration Information

The service registration events are formatted into the following fields:

Properties of service registration fields are as follows:

Each field is delimited by an asterisk ( * ).

It is normal for the events to appear multiple times in a row for one instance.

A successful registration returns a code of zero, meaning the client can connect to the instance.

A failure produces a code that maps to an error message.

Example 16-7 shows a log file with service registration events. The listener is able to receive a client request after a successful service_register event, but is unable to receive client requests after a service_died event.

Example 16-7 Listener Log with Service Registration Events

Oracle Database Error Messages for a complete listing of error messages

16.6.7.3 Listener Direct Hand-Off Information

The listener records direct hand-off events to dispatcher s. These events are formatted into the following fields:

Properties of direct hand-off fields are as follows:

Each field is delimited by an asterisk ( * ).

A successful connection or command returns a code of zero.

A failure produces a code that maps to an error message.

Example 16-8 shows a direct hand-off event in the log file.

Example 16-8 Listener Log Event for Direct Hand-Off

16.6.7.4 Listener Subscription for ONS Node Down Event Information

The listener subscribes to the Oracle Notification Service (ONS) node down event on startup if the ONS configuration file is available. This subscription enables the listener to remove the affected service when it receives node down event notification from ONS. The listener uses asynchronous subscription for the event notification.

The following warning message is recorded to the listener log file on each STATUS command if the subscription has not completed, such as the ONS daemon is not running on the host.

The listener cannot receive the ONS event while subscription is pending. Other than that, no other listener functionality is affected.

16.6.7.5 Listener Oracle Clusterware Notification Information

If the required Oracle Clusterware (shown as CRS in the following log messages) libraries are installed and Oracle Clusterware is started on the host, then Oracle Listener will notify Oracle Clusterware about its status during start and stop processes. After successful notification, listeners record the event in the log. No message is recorded if the notification fails.

16.6.8 Analyzing Oracle Connection Manager Logs

Oracle Connection Manager generates four types of log files, one each for its listener, gateway, CMADMIN processes, and alerts. The alert log file is a chronological record of all critical errors. In addition to logging critical errors, the alert log captures information about instance startup and shutdown. It also records the value of all configuration parameters at the beginning and end of a session.

Example 16-9 and Example 16-10 are representative of the CMADMIN and gateway log files. Table 16-16 explains log entries. Each entry consists of a timestamp and an event. You can configure the cman.ora file to log events for the following categories:

Initialization and termination

Registration and load update

Events related to CMADMIN wakeup queue

Events associated with connection control blocks

Use the SET EVENT command to specify which events to log.

Example 16-9 shows a typical CMADMIN log.

Example 16-9 CMADMIN Log File

Example 16-10 shows a typical gateway log file.

Example 16-10 Gateway Log File

Table 16-16 shows the log file entries and their descriptions.

Table 16-16 CMADMIN and Gateway Log Entries

Failed to get procedure ID

The CMCTL session connected to CMADMIN has disconnected.

GMON Attributes validated

Informational message. The parameters needed for CMADMIN to come up are specified correctly.

Invalid connect data

An unknown client is trying to connect to CMADMIN. This is most likely a denial of service attack.

No connect data

An unknown client is trying to connect to CMADMIN. This is most likely a denial of service attack.

Connected to Monitor

The gateway has connected to CMADMIN.

Informational message. Internal housekeeping for the gateway process is in order. The gateway process is properly connected to the CMADMIN process.

The connection was disconnected because it was idle longer than the time specified in the cman.ora file.

Out of connection control block (CCB)

CMADMIN cannot process a connection request. There could be two reasons:

Faulty load update between CMADMIN and listener.

Someone is trying to connect to CMADMIN directly (possibly a denial of service attack).

The connection was disconnected because it exceeded the session timeout specified in the cman.ora file.

State change from Empty to Init

State change message from the gateway. After it reaches init state, the gateway begins some internal data initialization.

State change from Init to Ready

State change message from the gateway. After it reaches a ready state, the gateway begins accepting connections from the client.

16.7 Tracing Error Information for Oracle Net Services

Tracing produces a detailed sequence of statements that describe network events as they are run. Tracing an operation enables you to obtain more information about the internal operations of the components of Oracle Net Services than is provided in a log file. This information is output to files that can be evaluated to identify the events that led to an error.

Tracing uses a large amount of disk space and may have a significant impact upon system performance. Therefore, you should enable tracing only when necessary.

This section contains the following topics:

16.7.1 Understanding Oracle Net Services Trace File Names

Each Oracle Net Services component produces its own trace file. Table 16-17 provides the default trace file names and lists the components that generate the trace files.

Table 16-17 Trace Files Names

Oracle Connection Manager listener

Oracle Connection Manager CMGW process

Oracle Connection Manager CMADMIN process

16.7.2 Setting Tracing Parameters

Parameters that control tracing, including the type and amount of information trace, and the location where the files are stored, are set in the configuration file of each network component as described in Table 16-18.

Table 16-18 Location of Trace Parameters

Oracle Connection Manager processes

This section contains the following topics:

Oracle Database Net Services Reference for additional information about these parameters

16.7.2.1 cman.ora Trace Parameters

Table 16-19 describes the trace parameters settings for Oracle Connection Manager that can be set in the cman.ora file.

Table 16-19 cman.ora Trace Parameters

The destination directory for trace files.

By default, the directory is ORACLE_HOME/network/trace .

The size of the trace file in KB. When the size is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO parameter.

The number of trace files for tracing. When this parameter is set along with the TRACE_FILELEN parameter, trace files are used in a cyclical fashion. The first file is filled, then the second file, and so on. When the last file has been filled, the first file is reused, and so on.

The trace file names are distinguished from one another by their sequence number. For example, if this parameter is set to 3, then the Oracle Connection Manager trace files for the gateway processes would be named instance-name_ cmgw1_ pid .trc , instance-name_ cmgw2_ pid .trc and instance-name _cmgw3_ pid .trc .

In addition, trace events in the trace files are preceded by the sequence number of the file.

The level of detail the trace facility records for the listener. The trace level value can either be a value within the range of 0 (zero) to 16 where 0 is no tracing and 16 represents the maximum amount of tracing or one of the following values:

off (equivalent to 0) provides no tracing.

user (equivalent to 4) traces to identify user-induced error conditions.

admin (equivalent to 6) traces to identify installation-specific problems.

support (equivalent to 16) provides trace information for troubleshooting by Oracle Support Services.

The Oracle Connection Manager listener, gateway, and CMADMIN processes create trace files on both Linux and Microsoft Windows.

If the TRACING parameter is enabled, then a time stamp in the form of dd-mon-yyyy hh:mi:ss:mil is created for every trace event in the listener trace file.

16.7.2.2 listener.ora Trace Parameters

Table 16-20 describes the trace parameters settings for the listener that can be set in the listener.ora file.

Table 16-20 listener.ora Trace Parameters

Select a trace level/Trace Level

The level of detail the trace facility records for the listener. The trace level value can either be a value within the range of 0 (zero) to 16 where 0 is no tracing and 16 represents the maximum amount of tracing or one of the following values:

off (equivalent to 0) provides no tracing.

user (equivalent to 4) traces to identify user-induced error conditions.

admin (equivalent to 6) traces to identify installation-specific problems.

support (equivalent to 16) provides trace information for troubleshooting by Oracle Support Services.

The destination directory and file for the trace file. By default, the directory is ORACLE_HOME/network/trace , and the file name is listener.trc .

You must set this parameter manually.

The maximum age of listener trace files in minutes. When the age limit is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_ listener_name parameter.

You must set this parameter manually.

Specifies the maximum age of server trace files in minutes. When the age limit is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_SERVER parameter.

You must set this parameter manually.

The size of the listener trace files in KB. When the size is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_ listener_name parameter

You must set this parameter manually.

The number of trace files for listener tracing. When this parameter is set along with the TRACE_FILELEN_ listener_name parameter, trace files are used in a cyclical fashion. The first file is filled, then the second file, and so on. When the last file has been filled, the first file is re-used, and so on.

The trace file names are distinguished from one another by their sequence number. For example, if the default trace file of listener.trc is used, and this parameter is set to 3, then the trace files would be named listener1.trc , listener2.trc and listener3.trc .

In addition, trace events in the trace files are preceded by the sequence number of the file.

When this parameter is set with the TRACE_FILEAGE_ listener_name parameter, trace files are cycled based on the age of the trace file. The first file is used until the age limit is reached, then the second file is use, and so on. When the last file’s age limit is reached, the first file is re-used, and so on.

When this parameter is set with both the TRACE_FILELEN_ listener_name and TRACE_FILEAGE_ listener_name parameters, trace files are cycled when either the size limit or the age limit is reached.

You must set this parameter manually.

A time stamp in the form of dd-mon-yyyy hh:mi:ss:mil for every trace event in the listener trace file.

16.7.2.3 sqlnet.ora Trace Parameters

Table 16-21 describes the trace parameters settings that can be set in the sqlnet.ora file.

Table 16-21 sqlnet.ora Trace Parameters

Client Information: Trace Directory

The destination directory for the client trace output. By default, the client directory is ORACLE_HOME/network/trace .

Server Information: Trace Directory

The destination directory for the database server trace output. By default, the server directory is ORACLE_HOME/network/trace .

Client Information: Trace File

The name of the trace file for the client. By default, the trace file name is sqlnet.trc .

Server Information: Trace File

The name of the trace file for the database server. By default the trace file name is svr_ pid .trc .

You must set this parameter manually.

Specifies the maximum age of client trace files in minutes. When the age limit is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_CLIENT parameter.

You must set this parameter manually.

Specifies the maximum age of server trace files in minutes. When the age limit is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_SERVER parameter.

You must set this parameter manually.

The size of the client trace files in KB. When the size is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_CLIENT parameter.

You must set this parameter manually.

The size of the database server trace files in KB. When the size is reached, the trace information is written to the next file. The number of files is specified with the TRACE_FILENO_SERVER parameter.

You must set this parameter manually.

The number of trace files for client tracing. When this parameter is set along with the TRACE_FILELEN_CLIENT parameter, trace files are used in a cyclical fashion. The first file is filled, then the second file, and so on. When the last file has been filled, the first file is re-used, and so on.

The trace file names are distinguished from one another by their sequence number. For example, if the default trace file of sqlnet.trc is used, and this parameter is set to 3, then the trace files would be named sqlnet1_ pid .trc , sqlnet2_ pid .trc and sqlnet3_ pid .trc .

In addition, trace events in the trace files are preceded by the sequence number of the file.

When this parameter is set with the TRACE_FILEAGE_CLIENT parameter, trace files are cycled based on the age of the trace file. The first file is used until the age limit is reached, then the second file is use, and so on. When the last file’s age limit is reached, the first file is re-used, and so on.

When this parameter is set with both the TRACE_FILELEN_CLIENT and TRACE_FILEAGE_CLIENT parameters, trace files are cycled when either the size limit or age limit is reached.

You must set this parameter manually.

The number of trace files for database server tracing. When this parameter is set along with the TRACE_FILELEN_SERVER parameter, trace files are used in a cyclical fashion. The first file is filled, then the second file, and so on. When the last file has been filled, the first file is re-used, and so on.

The trace file names are distinguished from one another by their sequence number. For example, if the default trace file of svr_ pid .trc is used, and this parameter is set to 3, then the trace files would be named svr1_ pid .trc , svr2_ pid .trc and svr3_ pid .trc .

In addition, trace events in the trace files are preceded by the sequence number of the file.

When this parameter is set with the TRACE_FILEAGE_SERVER parameter, trace files are cycled based on the age of the trace file. The first file is used until the age limit is reached, then the second file is use, and so on. When the last file’s age limit is reached, the first file is re-used, and so on.

When this parameter is set with both the TRACE_FILELEN_SERVER and TRACE_FILEAGE_SERVER parameters, trace files are cycled when either the size limit or age limit is reached.

Client Information: Trace Level

The level of detail the trace facility records for the client.

The trace level value can either be a value within the range of 0 (zero) to 16 where 0 is no tracing and 16 represents the maximum amount of tracing or one of the following values:

off (equivalent to 0) provides no tracing.

user (equivalent to 4) traces to identify user-induced error conditions.

admin (equivalent to 6) traces to identify installation-specific problems.

support (equivalent to 16) provides trace information for troubleshooting by Oracle Support Services.

Server Information: Trace Level

The level of detail the trace facility records for the database server. The trace level value can either be a value within the range of 0 (zero) to 16 where 0 is no tracing and 16 represents the maximum amount of tracing or one of the following values:

off (equivalent to 0) provides no tracing.

user (equivalent to 4) traces to identify user-induced error conditions.

admin (equivalent to 6) traces to identify installation-specific problems.

support (equivalent to 16) provides trace information for troubleshooting by Oracle Support Services.

You must set this parameter manually.

A time stamp in the form of dd-mon-yyyy hh:mi:ss:mil for every trace event in the client trace file, sqlnet.trc .

You must set this parameter manually.

A time stamp in the form of dd-mon-yyyy hh:mi:ss:mil for every trace event in the client trace file, sqlnet.trc .

Client Information: Unique Trace File Name

When the value is set to on , Oracle Net creates a unique file name for each trace session by appending a process identifier to the name of each trace file generated, and enabling several files to coexist. For example, trace files named sqlnet pid .trc are created if default trace file name sqlnet.trc is used. When the value is set to off , data from a new client trace session overwrites the existing file.

You can manually add the TNSPING utility tracing parameters described in Table 16-22 to the sqlnet.ora file. The TNSPING utility determines whether a service, such as a database or other TNS services on an Oracle Net network can be successfully reached.

Table 16-22 TNSPING Trace Parameters

The destination directory for TNSPING trace file, tnsping.trc . By default, the directory is ORACLE_HOME/network/trace .

The level of detail the trace facility records for the TNSPING utility. The trace level value can either be a value within the range of 0 (zero) to 16 where 0 is no tracing and 16 represents the maximum amount of tracing or one of the following values:

off (equivalent to 0) provides no tracing.

user (equivalent to 4) traces to identify user-induced error conditions.

admin (equivalent to 6) traces to identify installation-specific problems.

support (equivalent to 16) provides trace information for troubleshooting by Oracle Support Services.

16.7.2.4 Setting Tracing Parameters in Configuration Files

Configure tracing parameters for the sqlnet.ora file with Oracle Net Manager and listener.ora file with either Oracle Enterprise Manager Cloud Control or Oracle Net Manager. You must manually configure cman.ora file tracing parameters.

This section contains the following topics:

16.7.2.4.1 Setting Tracing Parameters for sqlnet.ora File Using Oracle Net Manager

The following procedure describes how to set the tracing parameters for the sqlnet.ora file using Oracle Net Manager:

Start Oracle Net Manager.

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 .

16.7.2.4.2 Setting Tracing Parameters for the Listener Using Oracle Enterprise Manager Cloud Control

The following procedure describes how to set the tracing parameters for the listener using Oracle Enterprise Manager Cloud Control:

Access the Net Services Administration page in Oracle Enterprise Manager Cloud Control.

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 .

16.7.2.4.3 Setting Tracing Parameters for the Listener Using Oracle Net Manager

The following procedure describes how to set the tracing parameters for the listener using Oracle Net Manager:

Start Oracle Net Manager.

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.

16.7.3 Setting Tracing During Control Utilities Runtime

You can set tracing during control utility runtime. Setting tracing with a control utility does not set parameters in the *.ora files. The setting is only valid for the session of the control utility:

For the listener, use the SET TRC_DIRECTORY , SET TRC_FILE , and SET TRC_LEVEL commands from the Listener Control utility.

For Oracle Connection Manager, use the SET TRACE_DIRECTORY and SET TRACE_LEVEL , and SET TRACE_TIMESTAMP commands from the Oracle Connection Manager control utility.

16.7.4 Evaluating Oracle Net Services Trace Files

Trace files can help Oracle Support Services diagnose and troubleshoot network problems. This section explains how to perform basic analysis of trace files. It contains the following topics:

16.7.4.1 Flow of Data Packets Between Network Nodes

Oracle Net performs its functions by sending and receiving data packets. You can view the actual contents of the Oracle Net packet in your trace file by specifying a trace level of support . The order of the packet types sent and received help to determine how the connection was established.

16.7.4.2 Oracle Net Data Packet Formats

Each line in the trace file begins with a procedure followed by a message. Following each procedure is a line of hexadecimal data representing actual data. The actual data that flows inside the packet is sometimes viewable to the right of the hexadecimal data.

Each packet has a keyword that denotes the packet type. All packet types begin with the prefix » nsp «. This is helpful when reviewing trace files for specific packet information. The following keywords are used in a trace file:

NSPTCN: Used with connect packet types.

NSPTAC: Used with accept packet types.

NSPTRF: Used with refuse packet types.

NSPTRS: Used with resend packet types.

NSPTDA: Used with data packet types.

NSPCNL: Used with control packet types.

NSPTMK: Used with marker packet types.

Example 16-11 shows typical packet information. In the example, the nscon procedure sends an NSPTCN packet over the network.

Example 16-11 Packet Information

16.7.4.3 Pertinent Oracle Net Trace Error Output

When there is a problem, the error code is logged in the trace file. Example 16-12 illustrates typical trace file output for a failed SQL*Plus connection to a database server. The error message and error stack are shown in bold.

Example 16-12 Trace Example

In the error stack in the preceding example, an operating system error code is shown. Each operating system has its own error codes, refer to your system documentation for information about operating system error codes.

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:

As an example, consider the following nserror entry logged in the trace file shown in Example 16-12:

In the preceding example, 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 12537, 12560, and 507. User input is shown in bold in the following examples.

16.7.5 Using the Trace Assistant to Examine Trace Files

Oracle Net Services provides a tool called the Trace Assistant to help understand the information provided in trace files by converting existing lines of trace file text into a more readable paragraph. The Trace Assistant works only with level 16 (support) Oracle Net Services trace files.

The Trace Assistant can only be used when the DIAG_ADR_ENABLED parameter is set to off . See «Understanding Automatic Diagnostic Repository» .

This section contains the following topics:

Источник

Adblock
detector

 Cause:

Oracle classify this as a ‘generic protocol adapter error’.  it indicates that Oracle client does not know what instance to connect to or what TNS alias to use.

• Oracle client is not installed on Qlik product server
• Oracle Listener or database is not up
• TNSNAMES.ora has a problem
• Connection between Oracle client and server has problem (TCP/IP or network side)
• ORCL 2019.3: the SQLNET.ora has AUTHENTICATION set to TNS. Changing it to (NONE) may help with starting the Listener. 

Resolution:

Check the listener status:

Go to a command prompt on the Oracle database server and run

C:>  lsnrctl status

Verify that the tnsnames entry is correct:

Go to a command prompt on the Qlik Product server and run

C:> tnsping INSTANCENAME

Check the connection between Oracle client and database server:

Go to a command prompt on the Qlik Product server and run

C:Document and settingsORANGE> cd
C:> set oracle_sid=<DB name>

e.g   C:> set oracle_sid=<rajesh> {press enter}
      C:> sqlplus /nolog {press enter}
sql:>connect sys/sys as sysdba {press enter} it cant ask for password directly connected
    or
sql:>connect sys as sysdba {press enter}
password : sys {press enter}
sql:> connected
       or
sql:> connect scott {press enter}
Enter password :tiger {press enter}
sql:> connected
     or
sql:> connect system {press enter}
Enter password :manager {press enter}
sql:> connected

Как победить ошибку ORA-12560: TNS:protocol adapter error в Windows

При подключении через sqlplus командой «sqlplus / as sysdba» появляется сообщение об ошибке:

Для отключения UAC в Windows необходимо выполнить следующие действия:

1. Перейти в «Пуск»-«Панель управления»-«Учетные записи пользователей»-«Изменение параметров контроля учетных записей»

2. Ползунок опустить до самого нижнего уровня.

3. Перезагрузить машину.

После этих действий у меня все заработало.

Если же это не помогло, то попробуйте:

1. Прописать «SQLNET.AUTHENTICATION_SERVICES = (NTS)» в файле sqlnet.ora на сервере БД Oracle

2. Проверить, присутствует ли запись о Вашей БД в файле listener.ora

As we all know that Oracle is free and open business management software that is available for both personal and commercial use. Oracle database uses object-relational database management system that allows to process credit card payments, support and manages customer orders, handles customers, etc.

This networking software does the task of exchange of data between machines using underlying services to establish communication between the components. Establishment of communication happens due to some set of services, these services are known as Transparent Network Substrate (TNS). However, sometimes due to some uncertain reasons, when this service is taken into action, throws an error stated as ‘ORA-12560 TNS Protocol Adapter Error’

So, here, in this blog today, I will let you know how to fix ora-12560 tns protocol adapter error in easy ways. But before that, let us have a brief look at the Oracle TNS which throws an error.

About Oracle Transparent Network Substrate (TNS)

Transparent Network Substrate is a service that handles communication with oracle components with its database codes and pre-programmed schema. The acronym TNS occurs in various instances, when you get an error. TNS uses various protocols, which could be TCP/IP, DECNET, SPX/IPX, AppleTalk etc.

All these combination of TNS and the hidden protocols used to help in networking makes the connection establish between components. The Oracle Transparent Network Substrate (TNS) facilitates simple inter-database communication and has an built-in management layer over the standard network topology. But sometimes there may occur some communication error while connecting to the database. And one of the errors is ORA-12560 TNS Protocol Adapter Error which happens whenever there is a problem in accessing a specific server of the database.

ORA-12560 TNS Protocol Adapter Error takes place due to several reasons, but the most common is the server problem.  This problem arises when a user does not specify which database or server was being accessed or there may be incorrect password provided to the database. Instead of giving exact reasons for the connection error, Oracle simply prompts ORA-12560 TNS Protocol Adapter Error. In short, if you want to troubleshoot this error, then you have to ensure the following:

• Listener is up and pointing to the desired database service
• Database service is up
• Oracle variables are configured in a correct manner which includes ORACLE_BASE, ORACLE_HOME, ORACLE_SID, PATH, TNS_ADMIN
• Cross check the firewall and the network configurations
• Full access is applicable to ORACLE_HOME including its sub-directories

Ways To Fix ORA-12560 TNS Protocol Adapter Error          

Here are the best ways you can try to fix ORA-12560 TNS Protocol Adapter Error. Try them one by one and see which fix works in your case. Let’s get started with the first solutions…..

Fix #1: Set Correct ORACLE_SID         

If you are running on a server that is running the database (i.e. database is local to machine) then it is important to first make sure that you have set a correct ORACLE_SID. If it is set correctly then it is good and if not then you have to try the below steps:

First of all, go to the command prompt and then run the below command:

• Set Oracle Database SID as

                 Set oracle_sid=ORCL

• Next, run Net Start command

                 Net start oracleserviceORCL

Fix #2: Check The Service Name ‘Oracleservice<SID>’ Ties Up With The Actual Database SID

Another method you can try to fix ora-12560 tns protocol adapter error is to check the service name ties up with the actual database SID. If, in any case, database SID changes without recreating service then this can lead this error. In this case, you have to recreate this service by following this step:

oradimxx -delete -sid <old-sid>

oradimxx -new -sid <SID> –intpwd <internal-password> AUTO –pfile <full-init-ora-filename>

Fix #3: Restart The Oracle Service

If the above method doesn’t work, maybe the OracleService for the database is not running. Check for the service, if it is not running then starts the service. If it is running then restart the service. To do so, you have to follow the below steps:

• First of all, you have to go to Start option.
• Next, you have to type Run and open it.
• Now, on the dialogue box, you have to type services.msc and then hit Enter.
• After this, you have to navigate to find OracleServicesORCL and then select the service and simply click on Start to initiate oracle database server if it’s not already running.
• After it has been done and running, from the command prompt run the following:

Tnsping<tnsalias> (tnsalias entry you can find it in tnsnames.ora file)

Fix #4: Try Oracle File Repair Tool To Fix ORA 12560: TNS: protocol adapter Error

If the above guides are out of your mind and you are perplexed what to do and wonder now how to fix ORA 12560 TNS protocol adapter error then don’t worry. For your rescue, there is a tool named Oracle File Repair Tool which will help you out. This tool doesn’t require any technical knowledge to use the steps.

Steps To Fix ORA 12560 TNS Protocol Adapter Using Tool

Step 1:  Run Oracle File Repair Tool and you would see options to select or search corrupt  Oracle databases in your computer.

Step:2  Click on Scan File to start the scan process after selecting the oracle database. On the left-side pane, recoverable objects get listed.

Step 3: Click an object to see its preview.

Step 4: : Click Start Repair in the icon bar to start the repair process. A prompt message occurs which will guide you further. Click next and continue.

Step 5: Provide user name, password and path of the blank database where you want to save the repaired database objects.

Step 6: Repairing and restoring various database objects after establishing a connection with blank oracle database.

This way you could fix ORA 12560: TNS protocol adapter error. Hope it helps.

Final Verdict

While using Oracle database if you ever come across and error stated as ‘ora-12560 tns protocol adapter error’ then you have come to the right place. I am saying so because I have already mentioned here the best fixes you can try to fix ora-12560 tns protocol adapter error.

All these fixes are easy to apply and the best part is that you do not need any technical knowledge to perform these fixes. All you have to do is to just follow the step by step guide to resolve ora-12560 tns protocol adapter error.