Solved

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

Posted on 2010-11-22
9
1,012 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle RMAN - trying to duplicate a database 5 52
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword 2 98
Oracle - SQL Query with Function 3 67
Fill Null values 5 36
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

726 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