sqloracle-databasesqlplus

Error: ORA-12154: TNS:could not resolve the connect identifier specified when entering username and password in sql plus

I'm trying to login to my oracle database using sql plus. But such error out

ORA-12154: TNS:could not resolve the connect identifier specified

here is my tnsnames.ora file, sqlnet.ora file and listener.ora file. It is because of i use symbol '@' in my password or what. I still cant find any solution from stackoverflow. I hope someone can help me with this. I show you how i do it in sql plus.

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Nov 20 00:26:52 2020
Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

For your information, I'm just a student that just learn how to setup oracle database.

Solution

  • You did not show your actual sqlplus connect command. But since you asked about '@' in your password, absolutely this can and most often will cause the exact error you mention. The '@' is used as a delimiter to mark the beginning of the net service name (tnsnames entry). For instance:

    oracle:orcl$ sqlplus scott/myp@ssword

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 19 11:31:03 2020

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

    In this example, it will see the '@' in my password and assume that the password is actually 'myp' and it is to look for a tnsnames.ora entry of 'ssword'

    Bottom line, your password verification function should disallow '@'.

    But also note that in this example, you are connecting 'as sysdba'. For this you really don't need any credentials at all, as you should be using an OS account that is a member of the 'dba' group, and thus can connect with os credentials:

    oracle:orcl$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 19 11:35:27 2020

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user
USER is "SYS"
SQL>

    But then, you really never should connect as SYS unless you absolutely need that elevated level of access. When you are SYS you are juggling chainsaws on a high-wire without a net.