Unable to Connect to Database 12c

Question

I have installed Oracle Database 12.1.0.2 on my linux machine. For the very first time everything works perfectly fine but when i restarted my machine then i was unable to connect to the database and it throws this error

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

My listerner.ora file is as follows:

LISTERNER=
(DESCRIPTON_LIST=
  (DESCRIPTION=
   (ADDRESS = (PROTOCOL = TCP)(HOST=oracle.localdomain)(PORT=1521))
   (ADDRESS= (PROTOCOL = IPC)(KEY=EXTPROC1521))
  )
 )
 SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
           (SID_NAME=orcl)
           (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
           (PROGRAM = /u01/app/oracle/product/12.1.0.2/db_1/bin/dg4odbc)
       )
   )
  DEFAULT_SERVICE_LISTERNER=(orcl.localdomain)

My tnsnames.ora file is as follows:

orcl=
 (DESCRIPTION=
    (ADDRESS=(PROTOCOL = TCP)(HOST=oracle.localdomain)(PORT=1521))
    (CONNECT_DATA=
      (SERVER = DEDICATED)
      (SERVICE_NAME=orcl.localdomain)
     )
   )

Kindly help i am stuck with this issue for over 2 weeks.

tnsping orcl command gives the following output:

TNS Ping Utility ffor Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015       15:21:46
Copyright (c) 1997, 2014, Oracle. All Rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=
    (ADDRESS=(PROTOCOL = TCP)(HOST=oracle.localdomain)(PORT=1521))
    (CONNECT_DATA=
      (SERVER = DEDICATED)
      (SERVICE_NAME=orcl.localdomain)
     )
   )
OK (0msec)

lsnrctl status command gives this output:

[oracle@oracle ~]$ lsnrctl status

 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015 15:38:50

 Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.localdomain)    (PORT=1521)))
 STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
 Version                  TNSLSNR for Linux: Version 12.1.0.2.0 -Production
Start Date                01-DEC-2015 14:08:29
Uptime                    0 days 1 hr. 30 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           orcl.localdomain
Listener Parameter File   
/u01/app/oracle/product/12.1.0.2 /db_1/network/admin/listener.ora
Listener Log File         
/u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Is it possible that the command you are using to connect is not correct? — Álvaro González, Dec 01 '15 at 09:53
please run the command "tnsping orcl" in your terminal and post the results. — Yaron Idan, Dec 01 '15 at 10:18
@daniyal.bashir What does `tnsping` has to do with `listener service`? Also, tnsping is primitive and checks nothing but only the port and host. Post details of `lsnrctl status`. And always mention your **Operating System**. After restarting the server did you check whether listener is up and running. Also, **to connect locally you do not need a listener, it is needed only for remote connections. In your case, as you use **@orcl** you are connecting over the network per your tnsnames.ora. — Lalit Kumar B, Dec 01 '15 at 10:34
I am using Oracle Linux 6.5. Yeah you are right but i am not able to connect locally as well wheni use this command: sqlplus / as sysdba it throws the same error. Kindly see the post for the lsnrctl status command result — daniyal.bashir, Dec 01 '15 at 10:40
@daniyal.bashir Remove the `SID_LIST` from the **listener.ora**, let the database register to the listener **dynamically**, no need to do it statically. Edit the listener.ora, remove the sid_list, restart the listener `lsnrctl start`. Anyway, first you need to connect locally, `export ORACLE_HOME, PATH and ORACLE_SID` and then do `sqlplus / as sysdba` to connect locally. — Lalit Kumar B, Dec 01 '15 at 10:48
It is Connecting to the idle instance [oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:52:24 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> — daniyal.bashir, Dec 01 '15 at 10:52
@daniyal.bashir Great! Now start the database, see my answer. — Lalit Kumar B, Dec 01 '15 at 11:09

Lalit Kumar B · Accepted Answer · 2015-12-01T11:31:18.223

Per our conversation in the comments, you are now able to connect locally. You just need to start the database. Let me explain in details:

To connect to database locally, you do not need a listener. You need it only to receive remote connections.

To connect locally, you just need a combination of ORACLE_HOME and ORACLE_SID environment variables.

export ORACLE_HOME=path of ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
sqlplus / as sysdba

Now, you are connected to an idle instance. You need to start the database:

startup;

On a side note, remember, tnsping is very primitive. it only validates the host and port and nothing else. It doesn't validate the service_name or SID. It does not determine database instance and database service availability. See Billy's reply here.

Unable to Connect to Database 12c

1 Answers1