How to Resolve ORA-12560 on Windows Server

ORA-12560

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

Datapatch

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

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%
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=SCOTTI))) * (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.