ORA-12162
To know how to solve ORA-12162 depends on where the database is. A local database? or an external database?
- Local Database (ORACLE_SID)
- External Database (Connection String)
A local database means that the database is in the server where you are. We should talk about how to set the environment variable ORACLE_SID.
An external database means that the database is NOT in the server where you are. We should talk about how to properly set a connection string.
ORACLE_SID
Tried to connect to the database by sqlplus locally, we got ORA-12162.
sqlplus / as sysdba
[oracle@db-server ~]$ sqlplus / as sysdba
...
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
sqlplus username/password
[oracle@db-server ~]$ sqlplus system/password
...
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
ORA-12162 means that Oracle cannot find the target database you want to connect, since the environment variable ORACLE_SID does not been set.
Solutions
To use bequeath protocol to connect to a local database without going through any listener, we need to set ORACLE_SID for your working environment.
Linux
Use oraenv to set all required environment variables for accessing local database.
[oracle@db-server ~]$ . oraenv
ORACLE_SID = [oracle] ? ORCL
The Oracle base remains unchanged with value /u01/app/oracle
Or set the variable directly.
[oracle@db-server ~]$ export ORACLE_SID=ORCL
[oracle@db-server ~]$ echo $ORACLE_SID
ORCL
Windows
C:\Users\alex>set ORACLE_SID=ORCL
C:\Users\alex>echo %ORACLE_SID%
ORCL
Then do your connection again.
Connection String
Sometimes, clients may accidentally and unintentionally make a local connection. For example, an user from AP server tried to test the connection to an external database by sqlplus.
[erp@ap-server ~]$ sqlplus $CONN_STRING
...
Enter user-name: erpapp
Enter password:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
We saw ORA-12162. We should check what's been set in the connection string.
[erp@ap-server ~]$ echo $CONN_STRING
Nothing, the connection string we provided to sqlplus command is empty. In fact, empty or improper connection string is a common mistake made by developers.
Next, let's make it move a little further. This time, we provide only username and password, which means, no destination.
[erp@ap-server ~]$ export CONN_STRING=erpapp/password
[erp@ap-server ~]$ sqlplus $CONN_STRING
...
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
Without providing any destination, sqlplus thought we want to make a local connection, but it found no ORACLE_SID has been set, so we still got ORA-12162.
To make the string connectable, we need to additionally provide an usable destination.
[erp@ap-server ~]$ export CONN_STRING=erpapp/password@orcl
The destination we provided above is a TNS service name which simplifies complex connection setting. As long as it is valid, you won't see ORA-12162 again.