Skip to content

How to Resolve ORA-12560 on Windows Server


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:\Users\Administrator>sqlplus / as sysdba
ORA-12560: TNS:protocol adapter error

Enter user-name:

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


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

C:\Users\Administrator>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:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 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 - 64bit Production

SQL> select instance_name, status from v$instance;

---------------- ------------
erpapp           OPEN

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release - 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:\Users\Administrator>set ORACLE_SID=ERPAPP2
C:\Users\Administrator>echo %ORACLE_SID%

Then connect again.

C:\Users\Administrator>sqlplus / as sysdba

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

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

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"
Windows Regedit - Oracle Software - Check "ORACLE_SID"

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

C:\Users\Administrator>set ORACLE_SID=ERPAPP
C:\Users\Administrator>echo %ORACLE_SID%

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= * 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:
  2. 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.

  3. Force all users to connect the database through shared server mode, except DBA. For example:
  4. 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 to patch 14.

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

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

    Listener code:

    # listener.ora Network Configuration File: C:\app\Oracle\product\18.0.0\dbhomeXE\NETWORK\ADMIN\listener.ora
    # Generated by Oracle configuration tools.
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = CLRExtProc)
          (ORACLE_HOME = C:\app\Oracle\product\18.0.0\dbhomeXE)
          (PROGRAM = extproc)
          (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Oracle\product\18.0.0\dbhomeXE\bin\oraclr18.dll")
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    TNSNAMES code:

    # tnsnames.ora Network Configuration File: C:\app\Oracle\product\18.0.0\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
    # Generated by Oracle configuration tools.
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)

Leave a Reply

Your email address will not be published. Required fields are marked *