GDS --TNS-12516: TNS:listener Could Not Find Available Handler With Matching Protocol Stack

(Doc ID 2390008.1)

Last updated on MAY 18, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Connecting to the primary database using the global service MSUSERS_readwrite.db.att.com_reg that was added to the GDS configuration failed 

with the error below:

GDS --TNS-12516: TNS:listener could not find available handler with matching protocol stack

$ sqlplus system@MSUSERS_readwrite.db.att.com_reg

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 23 10:16:09 2018

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

Enter password:

ERROR:

ORA-12516: TNS:listener could not find available handler with matching protocol stack

 

Changes

 This is a newly created GDS configuration. Please see outputs below:

GDSCTL>databases

Database: "d1c2d537" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: westcoast

Service: "readonly" Globally started: Y Started: N

Scan: N Enabled: Y Preferred: N

Service: "readwrite" Globally started: Y Started: Y

Scan: N Enabled: Y Preferred: Y

Registered instances:

msrw%1

Database: "t1c5d683" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: eastcoast

Service: "readonly" Globally started: Y Started: Y

Scan: N Enabled: Y Preferred: Y

Service: "readwrite" Globally started: Y Started: N

Scan: N Enabled: Y Preferred: N

Registered instances:

msrw

 

GDSCTL>services

Service "readonly.msrw.oradbcloud" has 1 instance(s). Affinity: ANYWHERE

Instance "msrw", name: "d1c2d537", db: "t1c5d683", region: "eastcoast", status: ready.

Service "readwrite.msrw.oradbcloud" has 1 instance(s). Affinity: ANYWHERE

Instance "msrw%1", name: "d1c2d537", db: "d1c2d537", region: "westcoast", status: ready.

 

 

The service readwrite.msrw.oradbcloud has registered to the gsm listener and primary database listener as shown below:

gsm listener status

============

lsnrctl status GSM_LIST1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 26-MAR-2018 09:11:11

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(HOST=zlp348yy.vci.att.com)(PORT=1522)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=GDS$CATALO G.oradbcloud)))

STATUS of the LISTENER

------------------------

Alias GSM_LIST1

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 23-MAR-2018 10:33:42

Uptime 2 days 22 hr. 37 min. 29 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/p1c4d284/oracle/product/gsm/12.1.0.2/network/admin/gsm.ora

Listener Log File /opt/app/p1c4d284/oracle/diag/gsm/zlp348yy/gsm_list1/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zlp348yy.vci.att.com)(PORT=1522)))

Services Summary...

Service "GDS$CATALOG.oradbcloud" has 1 instance(s).

Instance "p1c4d284", status READY, has 1 handler(s) for this service...

Service "_MONITOR" has 1 instance(s).

Instance "GSM_LIST1", status READY, has 1 handler(s) for this service...

Service "_PINGER" has 1 instance(s).

Instance "GSM_LIST1", status READY, has 1 handler(s) for this service...

Service "readonly.msrw.oradbcloud" has 1 instance(s).

Instance "msrw", status READY, has 1 handler(s) for this service...

Service "readwrite.msrw.oradbcloud" has 1 instance(s).

Instance "msrw%1", status READY, has 1 handler(s) for this service...

The command completed successfully

 

primary database listener status

=====================

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2018 13:55:57

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=listener_d1c2d537))

STATUS of the LISTENER

------------------------

Alias listener_d1c2d537

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 02-MAR-2018 13:35:17

Uptime 24 days 23 hr. 20 min. 39 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/d1c2d537/oracle/product/12.1.0.2/network/admin/listener.ora

Listener Log File /opt/app/d1c2d537/oracle/diag/tnslsnr/zld060xx/listener_d1c2d537/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=listener_d1c2d537)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zld060xx.vci.att.com)(PORT=1524)))

 

Service "edfrw.db.att.com" has 1 instance(s).

Instance "d1c2d537", status READY, has 1 handler(s) for this service...

Service "readwrite.msrw.oradbcloud" has 1 instance(s).

Instance "d1c2d537", status READY, has 1 handler(s) for this service...

The command completed successfully

 

Primary database local_listener

======================

SQL> show parameter local

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

local_listener string LISTENER_D1C2D537     

 

Primary tnsnames.ora

================

listener_d1c2d537 , listener_d1c2d537.db.att.com = (DESCRIPTION = (ADDRESS=(PROTOCOL=IPC)(KEY=listener_d1c2d537))(ADDRESS=(PROTOCOL=tcp)(HOST=zld060xx.vci.att.com)(PORT=1524)) )

 

Client tnsnames.ora

=============

MSUSERS_readwrite.db.att.com =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS=(HOST=zlp348yy.vci.att.com)(PORT=1522)(PROTOCOL=tcp))

)

(CONNECT_DATA =

(SERVICE_NAME = readwrite.msrw.oradbcloud)(REGION=westcoast)

)

)

 

Tnsping works fine

$ tnsping MSUSERS_readwrite.db.att.com_reg

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 23-MAR-2018 10:15:44

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:

/opt/app/d1c2d537/oracle/product/12.1.0.2/network/admin/sqlnet.ora

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = zlp348yy.vci.att.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = readwrite.msrw.oradbcloud)(REGION=westcoast)))

OK (190 msec)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms