Oracle 11gr2 + ORA-12514: TNS:listener does not currently know of service requested in connect

Today i received email from one user regarding the below email.

He installed oracle 11gr2 on windows machine after that from client machine when he is trying to connect then getting following message

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

below is the output of LISTENER.ORA and LSNRCTL status/services

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = C:\app\Administrator

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-AUG-2011 15:19

:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ

ction

Start Date                25-AUG-2011 15:12:27

Uptime                    0 days 0 hr. 6 min. 50 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   C:\app\Administrator\product\11.2.0\dbhome_1\network\a

dmin\listener.ora

Listener Log File         c:\app\administrator\diag\tnslsnr\test\listener

\alert\log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1

521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

  Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...

Service "test" has 1 instance(s).

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

Service "testXDB" has 1 instance(s).

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

The command completed successfully

C:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-AUG-2011 15:22

:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

  Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

Service "test" has 1 instance(s).

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

    Handler(s):

      "DEDICATED" established:21 refused:0 state:ready

         LOCAL SERVER

Service "testXDB" has 1 instance(s).

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

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER {machine: TEST-SERVER, pid: 5532}

         (ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=2514))

The command completed successfully

Solution

Rename the current listener.ora file with listener.ora.old

and create new listener file and paste the following contents.

NOTE: replace HOST and SERVICE_NAME as per your environment.

 # listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER=

        (SID_LIST =

                     

                (SID_DESC =

                        (GLOBAL_DBNAME=test)

                        (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

                        (SID_NAME = test)

                )

                )

                )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521)                                                                             )

    )

  )