Skip to content

Multiple ORACLE_HOME’s(10g,11g) on Windows and LISTENER running from 11G HOME

January 10, 2014

As we know Single Listener would support Multiple ORACLE_HOME Databases and we need to start Listener with higher version.

In my case, connection to 10g (dbname q041) works fine, but Connection to 11g (dbname d041) fails as below with Error : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

DBNAME : d041 –> 11g
DBNAME : q041 –> 10g

c:\Oracle>set oracle
ORACLE_HOME=c:\oracle\product\10.2.0\db_1
oracle_sid=q041

c:\Oracle>set tns
TNS_ADMIN=C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN

c:\Oracle>sqlplus system/xxxx@q041

SQL*Plus: Release 10.2.0.2.0 – Production on Fri Jan 10 20:15:11 2014

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select INSTANCE_NAME,VERSION from v$instance;

INSTANCE_NAME    VERSION
—————- —————–
q041             10.2.0.2.0

c:\Oracle>sqlplus system/xxxx@d041

SQL*Plus: Release 10.2.0.2.0 – Production on Fri Jan 10 19:54:29 2014

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

c:\Oracle>lsnrctl stat listener       => Below Out put looks good

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 – Production on 10-JAN-2014 20:17:54

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias                     listener
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 – Production
Start Date                10-JAN-2014 20:09:35
Uptime                    0 days 0 hr. 8 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         c:\oracle\diag\tnslsnr\MNGLR\listener\alert\log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MNGLR)(PORT=1521)))
Services Summary…
Service “CLRExtProc” has 1 instance(s).
Instance “CLRExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “d041” has 1 instance(s).
Instance “d041“, status READY, has 1 handler(s) for this service…
Service “d041XDB” has 1 instance(s).
Instance “d041”, status READY, has 1 handler(s) for this service…
Service “q041” has 1 instance(s).
Instance “q041“, status READY, has 1 handler(s) for this service…
Service “q041_XPT” has 1 instance(s).
Instance “q041”, status READY, has 1 handler(s) for this service…
Service “q041dr” has 1 instance(s).
Instance “q041dr”, status READY, has 1 handler(s) for this service…
Service “q041dr_XPT” has 1 instance(s).
Instance “q041dr”, status READY, has 1 handler(s) for this service…
The command completed successfully

On checking, I found multiple Listener services was running for each Home as below:

c:\Oracle>NET START | FIND /N “TNSListener”
[52]   OracleOraDb10g_home1TNSListener
[53]   OracleOraDb11g_home1TNSListener

c:\Oracle>sc query OracleOraDb11g_home1TNSListener

SERVICE_NAME: OracleOraDb11g_home1TNSListener
TYPE               : 10  WIN32_OWN_PROCESS
STATE              : 4  RUNNING
(STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)

c:\Oracle>sc query OracleOraDb10g_home1TNSListener

SERVICE_NAME: OracleOraDb10g_home1TNSListener
TYPE               : 10  WIN32_OWN_PROCESS
STATE              : 4  RUNNING
(STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)

I Stopped OLD 10g TNS Listener Service from Windows as below

c:\Oracle>net stop OracleOraDb10g_home1TNSListener
The OracleOraDb10g_home1TNSListener service is stopping.
The OracleOraDb10g_home1TNSListener service was stopped successfully.

c:\Oracle>sc query OracleOraDb10g_home1TNSListener

SERVICE_NAME: OracleOraDb10g_home1TNSListener
TYPE               : 10  WIN32_OWN_PROCESS
STATE              : 1  STOPPED

After stopping old 10g listener, Connection to 11g DB started working fine as below.

c:\Oracle>set oracle
ORACLE_HOME=c:\oracle\product\10.2.0\db_1

c:\Oracle>set tns
TNS_ADMIN=C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN

c:\Oracle>sqlplus system/xxxx@d041

SQL*Plus: Release 10.2.0.2.0 – Production on Fri Jan 10 20:21:28 2014

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select INSTANCE_NAME,VERSION from v$instance;

INSTANCE_NAME    VERSION
—————- —————–
d041             11.2.0.1.0

Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: