Ora 12560 tns protocol adapter error sqlplus windows

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

1. Database service is stop
2. Incorrect ORACLE_SID
3. Reach limitation of PROCESSES

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

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, 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:

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.

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.

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