• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1028
  • Last Modified:

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

Our DBA has abruptly left the company and I know almost nothing about databases. I need however to figure out how to start the database instance that was once shut down.

Here's what I'm getting when I try to connect to that instance:


$ sqlplus sys/<****>@pwr as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 22 19:38:47 2010

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

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


listener.ora:


SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /app/oracle/product/11.2.0/dbhome_1)
     (PROGRAM = extproc)
   )

    (SID_DESC =
      (SID_NAME = irn)
      (ORACLE_HOME = /app/oracle/product/11.2.0/dbhome_1)
    )

    (SID_DESC =
      (SID_NAME = pwr)
      (ORACLE_HOME = /app/oracle/product/11.2.0/dbhome_1)
    )
)


LISTENER =
  (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
     (ADDRESS = (PROTOCOL = TCP)(HOST = irn.domain.local)(PORT = 1522))
   )
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = irn.domain.local)(PORT = 1521))
   )
  )

ADR_BASE_LISTENER = /app/oracle

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

tnsnames.ora:

IRN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = irn.domain.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = irn.domain.local)
    )
  )

PWR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = irn.domain.local)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pwr.domain.local)
    )
  )

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

lsnrctl services:



LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-NOV-2010 19:47:42

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "irn" has 1 instance(s).
  Instance "irn", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "irn.domain.local" has 1 instance(s).
  Instance "irn", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "irnXDB.domain.local" has 1 instance(s).
  Instance "irn", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: irn.domain.local, pid: 28998>
         (ADDRESS=(PROTOCOL=tcp)(HOST=irn)(PORT=40018))
Service "pwr" has 1 instance(s).
  Instance "pwr", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully




0
ion_
Asked:
ion_
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
ion_Author Commented:
Didn't help. First one doesn't apply at all and I've tried the suggestion from the second about changing the SID, it didn't do anything.
0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
In your listener, there is no service for "pwr.domain.local".
Hence, you are facing the issue.
In the tns entry for PWR, please change "pwr.domain.local" either to "irn" or  "irn.domain.local" and retry your operation.

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jocaveCommented:
Assuming you are logged in to the linux box where the database is running as the user "oracle" (or a different user in the linux group "dba"), can you connect to the database without going through the listener

$ sqlplus / as sysdba

Open in new window


If that connects you to a running instance, the problem is likely that the database is set to register with the listener at startup but the listener wasn't up when the database started.  In that case, try

SQL> ALTER SYSTEM REGISTER;

Open in new window


and then try to connect via the listener.  If this is the problem, it implies that your system startup scripts are not set to start the listener before the database.

If, on the other hand, the "sqlplus / as sysdba" connects you to an idle instance, just issue the startup command

SQL> startup

Open in new window


The third alternative is that you get an ORA-27101: shared memory realm does not exist which often implies that the ORACLE_SID is not set.  In that case

$ export ORACLE_HOME =  /app/oracle/product/11.2.0/dbhome_1
$ export ORACLE_SID = pwr
$ sqlplus / as sysdba

Open in new window

0
 
ion_Author Commented:
ReliableDBA:

I tried your suggestion and was able to connect, however, which of the two instances am I connected to? how can I check? When I try "SQL> startup" I get:
ORA-01081: cannot start already-running ORACLE - shut it down first
so, it looks like i'm connected to IRN instance which is already running but I need to to connect to PWR instance to start it up.

jocave:

same question, "sqlplus / as sysdba" works but it looks like it's connecting to the primary (if there's such a thing) instance called IRN which is listening on port 1521. The PWR which is listening on 1522 is the one I need to connect to to start it up.
0
 
jocaveCommented:
OK.  Then set the ORACLE_SID and connect

$ export ORACLE_SID = pwr
$ sqlplus / as sysdba

Open in new window

0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
Then you need to have a listener, which is listening to the PWR database.
0
 
ion_Author Commented:
not exactly sure why but it worked when I changed the line:
(SERVICE_NAME = pwr.domain.local) to (SERVICE_NAME = pwr) in tnsnames.ora
0
 
ion_Author Commented:
Problem is resolved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now