An ORA-12514 Oracle 11gr2 error occurred unexpectedly

Question

I installed Oracle 11g r2 fine, had 2 databases (se3 and mydb, both have db_domain.orcl ie mydb.orcl and se3.orcl) work on it until yesterday. but suddenly it becomes error ORA-12514 (TNS: the listener does not know about the service request in the connection 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 can "tnsping mydb / se3" perfectly. Tried to change tnsnames.ora and listener.ora files too (now restored), but cannot connect to any of them via sqlplus or sqldeveloper / visual studio .. plz help me .. thanks!

Answer 1

Based on your own comment, I can set ORACLE_SID, but I can’t connect via sqlplus ... ORA-01034 - Oracle is unavailable "it is likely that your database instances are not working. It is theoretically possible that the underlying Windows services OracleServiceMYDB are running and the corresponding oracle. exe is in the process, but the database instance is not working.It is best to look at the server for the trace directory containing alert_mydb.log and view the latest entries.

If the database just does not start, and the warning log does not show serious errors, run it as follows:

 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. Perform ORACLE_SID = SE3

Answer 2

In my case, I received ORA-12514 error when trying to connect via SQL * Plus from the client only . 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 cryptic because tnsping answered 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 found my problem as an incorrect SERVICE_NAME value in the 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 a regular 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