How to Resolve ORA-12560 on Windows Server

I have two error patterns on ORA-12560, one is related to ORACLE_SID problem, the other is related to the limitation of PROCESSES.

ORA-12560 due to 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 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;

INSTANCE_NAME    STATUS
---------------- ------------
erpapp           OPEN

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:\Users\Administrator>set ORACLE_SID=ERPAPP2
C:\Users\Administrator>echo %ORACLE_SID%
ERPAPP2

Then connect again.

C:\Users\Administrator>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.

C:\Users\Administrator>regedit
Windows Regedit - Oracle Software - Check "ORACLE_SID"

Windows Regedit - Oracle Software - Check "ORACLE_SID"

ORA-12560 due to Reach 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 searched MOS and found a document (Doc ID 1240013.1) matches the error pattern I met. The document applies to version 11.1.0.6.0 to 11.2.0.1 on Generic Windows platform, and says the error was due to the Bug 9764135, the real solution is to patch the database software up to 11.1.0.7.0 patch 34.

The document also mentions a workaround:
The workaround is to restart oracle service and reboot database to correct the problem.
I think restarting the database should work as the document suggests, 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

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.
    
    DEFAULT_SERVICE_LISTENER = XE
    
    SID_LIST_LISTENER =
      (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")
        )
      )
    
    DEFAULT_SERVICE_LISTENER = (XE)
    
    LISTENER =
      (DESCRIPTION_LIST =
        (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.
    
    LISTENER_XE =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    
    
    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (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 *