Solved

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

Posted on 2010-11-22
9
1,008 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:ReliableDBA
ReliableDBA 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
 
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
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.

 

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:ReliableDBA
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

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.

Join & Write a Comment

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now