Thanks for the question, Vikram.

Asked: March 11, 2016 - 5:40 am UTC

Last updated: August 10, 2022 - 3:20 am UTC

Version: 12c & 11G

Viewed 10K+ times! This question is

You Asked

Hello.
I have a win 2012-R2 server in which I have installed Oracle DB- 11G & 12c in separate homes.
I contemplated on having multiple listeners for each install but was advised against it in the forums.
My lsnr service for 12c is set as the main service to handle connections etc.

My Oracle_home, oracle base are set as per 12c in the environment variables.
(After installing 12c, I did not see a listener.ora file or a tnsnames.ora file generated, so I manually created them looking at the files for 11G instance.)

Now, I'm able to connect to my 12c instance without issues, but am unable to connect to the 11G database.

I want both the databases to be up at same time.

An error was encountered performing the requested operation:
Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Vendor code 12514

Some info to
lsnrctl status-
PS C:\Users\Administrator> lsnrctl services

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 11-MAR-2016 00:18:16

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "oracle12c" has 1 instance(s).
Instance "oracle12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4 refused:0 state:ready
LOCAL SERVER
Service "oracle12cXDB" has 1 instance(s).
Instance "oracle12c", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: WIN2012-R2-120, pid: 1600>
(ADDRESS=(PROTOCOL=tcp)(HOST=WIN2012-R2-120)(PORT=49418))
Service "pdboracle12c" has 1 instance(s).
Instance "oracle12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4 refused:0 state:ready
LOCAL SERVER
The command completed successfully

--------------------------------------------------------------------------------------
Listener.ora file details-

# listener.ora Network Configuration File: D:\Oracle\Database\Oracle12C\product\12.1.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\Oracle\Database\Oracle12C\product\12.1.0\dbhome_2)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\Oracle\Database\Oracle12C\product\12.1.0\dbhome_2\BIN\OraClr12.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = WIN2012-R2-120)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\Oracle\Database\Oracle12C

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

tnsnames.ora file-

# tnsnames.ora Network Configuration File: D:\Oracle\Database\Oracle11G\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLE11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = WIN2012-R2-120)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORACLE11G)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
PDBORACLE12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = WIN2012-R2-120)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDBORACLE12C)
)
)
-------------------------------------------------------------------------------------------

What should I do to avoid this error?
What should I do to have both the databases up at same time?

Please advise

Vikram

and Connor said...

When you start the 11g service/instance, it will register with the listener, and then it should be able to recieve remote connections.

If you want to be able to connect to a currently-down 11g instance via the 12c listener, you might have to edit listener.ora to explicitly list the sid you want to accept connections from, eg, something like adding:

(SID_DESC =
(SID_NAME = xxx)
(ORACLE_HOME = c:\oracle\product\11.2.0.4)
(ORACLE_SID = xxx)
)

into the sid list.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Listener.ora entries for 11g, 12c (non-cdb) and 12c CDB

Rajeshwaran, Jeyabal, March 12, 2016 - 7:11 am UTC

I have three instance of Oracle running on my laptop. Oracle 11g, Oracle 12c (non-CDB) and 12c CDB architecture.

Here is the listener.ora for all three instance available in Oracle 12c ORACLE_HOME directory.

Listener.ora

SID_LIST_LISTENER_ORA12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORA12c)
      (ORACLE_HOME = D:\app\Vnameit\product\12.1.0\dbhome_1)
      (SID_NAME = ORA12c)
    )
  )

LISTENER_CDB1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1523))
    )
  )

SID_LIST_LISTENER_ORA11G =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORA11G)
      (ORACLE_HOME = D:\app\Vnameit\product\11.2.0\dbhome_1)
      (SID_NAME = ORA11G)
    )
  )

LISTENER_ORA12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1524))
      (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1522))
    )
  )

LISTENER_ORA11G =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_CDB1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = CDB1)
      (ORACLE_HOME = D:\app\Vnameit\product\12.1.0\dbhome_1)
      (SID_NAME = CDB1)
    )
  )

Here is the status of the listener from command line.

C:\Users\179818>lsnrctl status LISTENER_ORA11G

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 12-MAR-2016 12:27:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_ORA11G
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                09-MAR-2016 13:36:28
Uptime                    2 days 22 hr. 51 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\Vnameit\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\Vnameit\diag\tnslsnr\LT035221\listener_ora11g\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT035221.cts.com)(PORT=1521)))
Services Summary...
Service "ORA11G" has 2 instance(s).
  Instance "ORA11G", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "ORA11GXDB" has 1 instance(s).
  Instance "ora11g", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Users\179818>lsnrctl status LISTENER_ORA12c

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 12-MAR-2016 12:28:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1524)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_ORA12C
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                09-MAR-2016 13:36:28
Uptime                    2 days 22 hr. 51 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\Vnameit\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\Vnameit\diag\tnslsnr\LT035221\listener_ora12c\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1524ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT035221.cts.com)(PORT=1522)))
Services Summary...
Service "ORA12CXDB" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "ORA12c" has 2 instance(s).
  Instance "ORA12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ora12c", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Users\179818>lsnrctl status LISTENER_CDB1

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 12-MAR-2016 12:28:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LT035221)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_CDB1
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                12-MAR-2016 12:24:03
Uptime                    0 days 0 hr. 4 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\Vnameit\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\Vnameit\diag\tnslsnr\LT035221\listener_cdb1\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT035221.cts.com)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=LT035221.cts.com)(PORT=5503))(Security=(my_wallet_directory=D:\APP\VNAMEIT\admin\CDB1\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CDB1" has 2 instance(s).
  Instance "CDB1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Users\179818>

Also see this for configuring multiple listeners.

http://docs.oracle.com/database/121/NETAG/listenercfg.htm#NETAG1470

March 13, 2016 - 1:20 am UTC

Thanks for the additional input.

listener unavailable.

Rajeshwaran, Jeyabal, July 28, 2022 - 12:03 pm UTC

Team,

could you help us how to resolve this below error ?
got a local install of 21c, able to connect without TNS entries, but fails with TNS entries in place.

waited for many hours but the "Instance "ORCL", status UNKNOWN," was still unknown only, have not become "READY" yet. any inputs/directions please ?

C:\Users\admin>lsnrctl status

LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 28-JUL-2022 17:25:30

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
Start Date                28-JUL-2022 17:24:20
Uptime                    0 days 0 hr. 1 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Oracle\ORA21C\homes\OraDB21Home1\network\admin\listener.ora
Listener Log File         C:\Oracle\ORA21C\diag\tnslsnr\myhost\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\Users\admin>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 28-JUL-2022 17:25:50

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

Used parameter files:
C:\Oracle\TNSnames\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
OK (0 msec)

C:\Users\admin>sqlplus /nolog

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Jul 28 17:26:13 2022
Version 21.3.0.0.0

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

idle> conn c##rajesh/Password-1
Connected.
c##rajesh@ORCL> conn c##rajesh/Password-1@ORCL
ERROR:
ORA-12518: TNS:listener could not hand off client connection


Warning: You are no longer connected to ORACLE.
c##rajesh@ORCL>

Here is what i have in my listener.ora entries..

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    )
  )
  
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\Oracle\ORA21C\homes\OraDB21Home1)
      (SID_NAME = ORCL)
    )
  )

August 08, 2022 - 1:14 am UTC

CDB? PDB?

Does 'alter system register' assist?

Is that user a SYSDBA or normal?

listener unavailable

Rajeshwaran, Jeyabal, August 08, 2022 - 5:19 am UTC

Connor - thanks for helping on this, it is at both CDB and PDB not able to connect using listener.
and we did "alter system register" and even with SYSDBA listener based connection is not working.

Here is the complete details

idle> conn sys/Password-1 as sysdba
Connected.
sys@ORCL> show con_name

CON_NAME
------------------------------
CDB$ROOT
sys@ORCL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
sys@ORCL>
sys@ORCL> $ tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 08-AUG-2022 10:44:03

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

Used parameter files:
C:\Oracle\TNSnames\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rajeyaba-3WH3DK3) (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (0 msec)

sys@ORCL> $ lsnrctl status

LSNRCTL for 64-bit Windows: Version 21.0.0.0.0 - Production on 08-AUG-2022 10:44:15

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 21.0.0.0.0 - Production
Start Date                03-AUG-2022 22:01:25
Uptime                    4 days 12 hr. 42 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Oracle\ORA21C\homes\OraDB21Home1\network\admin\listener.ora
Listener Log File         C:\Oracle\ORA21C\diag\tnslsnr\rajeyaba-3WH3DK3\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rajeyaba-3WH3DK3)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rajeyaba-3WH3DK3)(PORT=5500))(Security=(my_wallet_directory=C:\ORACLE\ORA21C\admin\ORCL\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "274b75ce3bc5465392a5360f121f53fe" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
Service "ORCL" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
The command completed successfully

sys@ORCL> conn sys/Password-1@orcl as sysdba
ERROR:
ORA-12518: TNS:listener could not hand off client connection


Warning: You are no longer connected to ORACLE.
sys@ORCL> conn sys/Password-1@rajeyaba-3WH3DK3:1521/orcl as sysdba
ERROR:
ORA-12518: TNS:listener could not hand off client connection


sys@ORCL>

August 09, 2022 - 3:14 am UTC

Few things to try

1) see if its a network.host resolution issue - configure your listener on 'localhost' and see if the issue still persists

2) enable low level tracing on the listener

LSNRCTL> set current_listener ...
LSNRCTL> set trc_level 16
LSNRCTL> set trc_directory ...

and see if we get anything useful in the logs

listener unavailable

Rajeshwaran, Jeyabal, August 09, 2022 - 4:43 pm UTC

Thanks Connor - that helps.

enable the trace in listener using "set trc_level 16" and then the trace file returned this error.

TNS-12518: TNS:listener could not hand off client connection
 TNS-12560: TNS:protocol adapter error
  TNS-00534: Failed to grant connection ownership to child
   64-bit Windows Error: 10022: Unknown error

then did a search for the error code TNS-00534 and followed the steps from this link

https://itsiti.com/tns-00534-failed-grant-connection-ownership-child/

and post that was able to connect successfully using TNS entries.

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Aug 9 22:11:23 2022
Version 21.3.0.0.0

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

idle> conn sys/Password-1 as sysdba
Connected.
sys@ORCL> conn sys/Password-1@orcl as sysdba
Connected.
sys@ORCL> conn sys/Password-1@pdb1 as sysdba
Connected.
sys@PDB1>

Thank you.

August 10, 2022 - 3:20 am UTC

glad the trace helped dig down to the error

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Listener.ora entries for 11g, 12c (non-cdb) and 12c CDB

listener unavailable.

listener unavailable

listener unavailable

More to Explore

Multitenant

Installed multiple home(11g and 12c on same windows server) unable to connect to 11G DB- Error - ORA-12514

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Listener.ora entries for 11g, 12c (non-cdb) and 12c CDB

listener unavailable.

listener unavailable

listener unavailable

More to Explore

Multitenant