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
please run the command "tnsping orcl" in your terminal and post the results. — Yaron Idan
@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

Lalit Kumar B Lalit Kumar B · Accepted Answer · 2015-12-01T11:09:10

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 Answers