How to Resolve ORA-12560 on Windows Server

  • by

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

Leave a Reply

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