unable to connect to oracle in linux terminal in oracle VM ,TNS listner not connected

All we need is an easy explanation of the problem, so here it is.

i installed oracle VM , new to oracle vm.
Can some help me in connecting to oracle in Linux termial and TNS listner is not responding with IO failure on sql developer when pressing test button .please sugggest how to resolve the issue.

https://infoallsite.wordpress.com/

you can see the images here :
tns issue

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

something is very “off” in that terminal session. You are not connecting with a net description, so you shouldn’t even be hitting the listener at all. I cannot reproduce that error with that syntax.

In any event, your syntax really should be

sqlplus / as sysdba

with a space between the slash and the ‘as’, and no quote marks.

Method 2

You try to connect with / as sysdba, which normally connects locally, and you still get errors related to TNS and listener. Actually, there is a not well-known environment variable, called TWO_TASK, and if you set that, SQL*Plus will automatically try to connect using the value of the variable as a TNS entry.

So if you use TWO_TASK, instead of this:

sqlplus user/[email protected]

you can do this:

export TWO_TASK=TNS
sqlplus user/password

The second method will have the same result as the first, the difference is you do not need to specify the TNS on every connection, you can simply put it in a variable, it may be useful when writing scripts, though I have never used it. Another use-case for it is when you have a CDB and you want to connect to a PDB. You can not connect a PDB directly locally, but you can kind of “emulate” that with TWO_TASK.

So I have the below environment:

[[email protected] ~]$ . oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /opt/oracle/base
[[email protected] ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-JAN-2016 23:15:23

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                15-JAN-2016 18:17:03
Uptime                    7 days 4 hr. 58 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/grid12102/network/admin/listener.ora
Listener Log File         /opt/oracle/base/diag/tnslsnr/o61/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.6.1)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Try to connect with / as sysdba:

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:15:28 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[[email protected] ~]$

So far so good, / as sysdba works like a charm. Now let’s say I have a TNS entry pointing to another service, that does not exist, for example:

[[email protected] ~]$ tnsping mydb

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-JAN-2016 23:20:47

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MYDB)))
OK (0 msec)
[[email protected] ~]$

tnsping say everything is OK. Well, tnsping does not check the specified service, it only checks the address and port. Still, it is a valid TNS entry anyway. If I try to connect using this TNS entry, it will obviously fail with the infamous 12514 error, as I do not have any service like this, as you could see from my lsnrctl status output. I do not even need to specify a valid username and password to get the error.

[[email protected] ~]$ sqlplus user/[email protected]

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:21:50 2016

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

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


Enter user-name:

And here comes TWO_TASK, the variable that was meant to make your life easier, but it is also a great feature to play a prank on someone who does not know about it. Even if you connect / as sysdba, SQL*Plus will try to open a remote connection because of it:

[[email protected] ~]$ export TWO_TASK=mydb
[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:29:21 2016

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

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


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


Enter user-name:

If you have TWO_TASK set, just unset it, and try to connect again.

[[email protected] ~]$ unset TWO_TASK
[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 22 23:31:24 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply