Solved

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

Posted on 2010-11-22
9
1,009 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

867 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

17 Experts available now in Live!

Get 1:1 Help Now