CODEWITHSUNDEEP
oracle-databasesqlplus
Shenald
Shenald

Reputation: 143

ORA-12154 Oracle DB Windows Can't Connect SQLPlus

I have checked the other answers in stackoverflow but didn't work any of it. I checked the tnsnames.ora there is no error I can find. This the code of my tnsnames.ora file.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-69D6TBS)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-69D6TBS)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

This is my sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Service is also running

enter image description here

enter image description here

enter image description here

Upvotes: 0

Views: 553

Answers (1)

EdStevens
EdStevens

Reputation: 3872

As is so often the case when this very question comes up, you did not show the initial command you used to start sqlplus. You should start it like this, following your username with an '@' sign, followed by the net service name, aka tnsnames entry.

c:\sqlplus myuser@XE

But lacking that, once it start prompting you for username and password, you need to follow the prompted username with with the spec for the net service name:

Enter user-name:  username@XE

But the fact that you got that particular error is a bit puzzling. Without specifying the net service name, it should have attempted a BEQ connection (not using tns at all) to connect to a local database identified by the value of %ORACLE_SID%. IN that case it should either connect or give a "protocol adapter error" The fact that it got this particular error in this particular usage would indicate to me that you have %TWO_TASK% set. This overrides ORACLE_SID and tells sqlplus to attempt a tns connection for the value of %TWO_TASK%.

As a side note, the nature of the reported error means the request never got anywhere near a listener, so the listener config is of no concern at this point. ORA-12154 is the equivalent of not being able to find someone's name in the telephone book. If you can't find it (it's unlisted or you are using the wrong telephone book) then you can't even place the call (send the request to the listener).

And for future reference, screen shots of command windows are not as helpful as copying and pasting the text from the window, then formatting the pasted text as 'code'. If you had done that here, you could have scrolled the window up so as to copy/include the sqlplus command you used.

Upvotes: 1

Related Questions