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
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.