Ошибка ora 12560 tns protocol adapter error

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

1. Database service is stop (SYSDBA)
2. Listener service is stop (Normal Users)
3. Incorrect ORACLE_SID
4. Reach limitation of PROCESSES
5. Database 10g, 11gR1 Creation

Database Service is Stop (SYSDBA)

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

Listener Service is Stop (Normal Users)

For those who are not SYSDBA, they need to connect to the database through the listener. If the listener service is stop, they have no way to connect to the database, even though the database is running.

So the solution is clear, just startup the listener service.

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

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

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.

C:UsersAdministrator>regedit

In this case, we should set the correct ORACLE_SID in registry or set it temporarily for your working environment like this:

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

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:

1. Raise the maximum number of processes online:

SQL> alter system set processes=3000 scope=both;

System altered.

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.

2. Force all users to connect the database through shared server mode, except DBA. For example:

SQL> alter system set shared_servers = 200 scope=both;

System altered.

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

System altered.

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.

Database 10g, 11gR1 Creation

For Windows Server 2008 with AD controller installed, you might see that DBCA failed with ORA-12560 at around 2% completion of database creation.

Let’s see the database creation log of DBCA.

[Thread-15] [18:26:52:256] [BasicStep.execute:202]  Executing Step : CLONE_DB_CREATION_RMAN_RESTORE
[Thread-15] [18:26:52:256] [StepErrorHandler.setFatalErrors:322]  setting Fatal Error: ORA-01092
[Thread-15] [18:26:52:256] [StepErrorHandler.setFatalErrors:322]  setting Fatal Error: ORA-01034
[Thread-15] [18:26:52:256] [StepErrorHandler.setFatalErrors:322]  setting Fatal Error: ORA-03114
[Thread-15] [18:26:52:256] [StepErrorHandler.setFatalErrors:322]  setting Fatal Error: ORA-12560

To fix the problem, you may consider to uninstall AD, then install Oracle database. After the database has been created, get AD back.

Alternatively, you may patch 11.1.0.7 to patch 14.

How do we solve ORA-12560 Error? How to solve TNS Protocol Adapter Error? Tips and Tricks to resolve ORA-12560 TNS Error in Oracle Database

This is a most common and disturbing error which is faced my most of the Oracle users while connecting to the database. Even I also had faced it many times. So what I am going to share here is the method which I use to fix this problem.

ORA-12560 comes when you are making a connection to the database and somehow it is not able to connect. At that time, it simply throws TNS Protocol Adapter Error. The reason can be one of the following:

1. Listener is not active.

2. Third Party Software.

3. Problem with the tnsnames.ora file.

1. Listener is not active

This may be a case when you might haven’t noticed that your listener process is not working and you are trying to make a connection with the database. At that time, ORA-12560 error will be thrown.

Solution:

• Simply press Windows + R
• Type services.msc and press enter

Look for listener process in the services list and check whether it is running or not. If it’s not running, press right click button and start the process.

After you have started the listener service, you are allowed to make a connection with the database.

2. Third Party Software

This problem is also very common with the different Oracle users who use third party software to make a connection with the database. For example, prior to Oracle 11g database, we have to install PL/SQL Developer manually which was externally added to the system. Now sometimes ORA-12560 error problem also comes with this tool.

Solution:

Third Party Tools sometimes aren’t able to get the connection string by their own. So we have to check the preference setting in these tools and fix the problem in case if it is required.

Just go to Preference -> Connection and set the path there. You’ll solve the connection problem.

3. Problem with the tnsnames.ora file

To connect to a database, you have to configure tnsname.ora file. At the time of default database creation, configuration is automatically added to the tnsnames.ora file. But in case if you want to connect to some other database on the network, you have to manually insert the settings in the tnsnames.ora file which includes the insertion of new IP and Port.

Tnsnames.ora file looks something like this:

Solution:

There may be a case where your database name and the other database name (which is on some other machine) are same. In that case, you have to manually give them different names so that you can differentiate between them. Like in the above image «DEMON» is the database name given for the local connectivity. I can also change it to DEMON1 or DEMON2 or can be any other name but make sure that you are not changing the SERVICE_NAME. By default my Service_Name and the name which I am using for the connectivity are both same. But you can replace «DEMON» with anything you want. Just don’t change the SERVICE_NAME.

There can be different databases with the same service name on a network. All you need to do is, provide a different name to this connection like mine is DEMON.

4. Making TNS_ADMIN Entry in Environment Variables.

In case you have multiple Oracle Environments (Oracle Softwares) installed on your system, there are chances that the Environment is unaware of the TNS_ADMIN directory it needs to use. To resolve the same, add the entry of TNS_ADMIN in the System Environment Variables on your system. Below mentioned are the demos of TNS_ADMIN paths, 

%ORACLE_HOME%networkadmin

$ORACLE_HOME/network/admin

I hope this helps !!

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

 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