1

I installed Oracle 11g r2 fine, had 2 databases (se3 and mydb, both have db_domain .orcl i.e. mydb.orcl and se3.orcl) running on it fine till yesterday. but suddenly im getting ORA-12514 error (TNS:listener doesn't know of service requested in connect descriptor)..

My tnsnames.ora file:

# tnsnames.ora Network Configuration File: G:\oracledb\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_MYDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb.orcl)
    )
  )

SE3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = se3.orcl)
    )
  )

LISTENER_SE3 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

LISTENER.ORA FILE:
# listener.ora Network Configuration File: G:\oracledb\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = G:\oracledb\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:G:\oracledb\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = G:\oracledb

I am able to "tnsping mydb/se3" fine.. Tried to change tnsnames.ora and listener.ora files too (now restored) but cannot connect to either one of them through sqlplus or sqldeveloper/visual studio.. plz help me.. thanks!

Solat Ali
  • 39
  • 1
  • 6
  • Are your databases up? Can you connect locally (bypassing the listener)? Has anything changed - machine reboot, init parameter settings...? – Alex Poole Sep 16 '13 at 08:19
  • thanks for reply.. Yeah the databases are up (listener is running).. i have the databases on my system (localhost) and nothing has changed at all.. i really am not sure how to connect bypassing the listener? – Solat Ali Sep 16 '13 at 09:03
  • Listener running does not mean the databases are running. Does `lstnrctl status` or `lsnrctl services` show anything being handled? If you have SQL*Plus, can you set `ORACLE_SID` and use `sqlplus user/password`, i.e. without the `@mydb` or `@se3`? – Alex Poole Sep 16 '13 at 09:09
  • lsnrctl services output only recognizes "CLREXTPROC" and not any of my se3 or mydb databases.. also im able to set ORACLE_SID but cannot connect via sqlplus like you asked.. Error comes out.. ORA-01034 - Oracle not available.. another on also.. ORA- 27101 - shared memory realm doesn't exist..! – Solat Ali Sep 16 '13 at 09:13
  • So it looks like your databases are down. I'm not familiar with running Oracle on Windows (I assume, from the `G:\...` path in the `tnsnames.ora`) but [instructions are here](http://docs.oracle.com/cd/E11882_01/win.112/e10845/admin.htm). If the PC hasn't been restarted then you might need to investigate the logs to see why the databases have shut down. – Alex Poole Sep 16 '13 at 09:28
  • Bro thanks for reply.. the databases are up and running (i checked, via link you posted) cannot figure out what to do :( – Solat Ali Sep 16 '13 at 09:35

2 Answers2

0

Based on your own comment "im able to set ORACLE_SID but cannot connect via sqlplus ... ORA-01034 - Oracle not available" it's likely that your database instances are down. It is theoretically possible to have the underlying Windows service OracleServiceMYDB running and corresponding oracle.exe in processes, but the database instance being down. Your best bet is to look on the server for the trace directory containing alert_mydb.log and review the last entries.

If the database is simply not running and alert log shows no serious errors, start it like this:

set ORACLE_SID=MYDB
set ORACLE_HOME=D:\my\oracle\home

%ORACLE_HOME%\bin\sqlplus "/ as sysdba"
ORACLE not available.
SQL> STARTUP 
SQL> EXIT

lsnrctl services

The last command should show MYDB is registered. Proceed with ORACLE_SID=SE3

kubanczyk
  • 5,184
  • 1
  • 41
  • 52
0

In my case, I received the ORA-12514 error when attempting to connect via SQL*Plus from a client-only machine to a remote database:

C:\Temp>sqlplus username@connect_descriptor

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 24 09:43:04 2014

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

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

I found this puzzling because tnsping responded successfully:

C:\Temp>tnsping connect_descriptor

TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 24-SEP-2
014 09:48:04

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
D:\Oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host
.intranet.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
HOST13)))
OK (20 msec)

Ultimately, I tracked down my problem as an incorrect SERVICE_NAME value in my tnsnames.ora file:

CONNECT_DESCRIPTOR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host.intranet.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HOST13)
    )
  )

After changing HOST13 (via an ordinary text editor) to the correct value for SERVICE_NAME, I was able to connect to the remote Oracle database.

On my Oracle 11.2 client machine, here is the location of tnsnames.ora:

C:\Oracle\product\11.2.0\client_1\network\admin\tnsnames.ora
DavidRR
  • 18,291
  • 25
  • 109
  • 191