Solved

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

Posted on 2010-11-22
9
1,011 Views
Last Modified: 2012-05-10
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
Comment
Question by:ion_
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 34193355
0
 

Author Comment

by:ion_
ID: 34193412
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
 
LVL 8

Assisted Solution

by:Chakravarthi Ayyala
Chakravarthi Ayyala earned 250 total points
ID: 34194075
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Assisted Solution

by:jocave
jocave earned 250 total points
ID: 34194238
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
 

Author Comment

by:ion_
ID: 34197171
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
 
LVL 7

Expert Comment

by:jocave
ID: 34200216
OK.  Then set the ORACLE_SID and connect

$ export ORACLE_SID = pwr
$ sqlplus / as sysdba

Open in new window

0
 
LVL 8

Expert Comment

by:Chakravarthi Ayyala
ID: 34201099
Then you need to have a listener, which is listening to the PWR database.
0
 

Accepted Solution

by:
ion_ earned 0 total points
ID: 34212455
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
 

Author Closing Comment

by:ion_
ID: 34308447
Problem is resolved.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question