[Fault Handling] ORA-12162: TNS: net service name is incorrectly specified

This article will explain a case of ORA-12162 error caused by the absence of the system environment variable oracle_sid. I hope you will think about it.

1. Get General Expression Information about ORA-12162 error messages
[Oracle @ asdlabdb01 ~] $ Oerr
Ora 12162
12162,000 00, "TNS: net service name is incorrectly specified"
// * Cause: the connect descriptor corresponding to the net service name in
// Tnsnames. ora or in the Directory Server (Oracle Internet Directory) is
// Incorrectly specified.
// * Action: If using local naming make sure there are no syntax errors in
// The corresponding connect descriptor in the tnsnames. ora file. If using
// Directory naming check the information provided through the administration
// Used for directory naming.

2. Fault symptom
The fault described in this article is different from the general problem description. The database server uses tnsnames. there is no problem with the connection string recorded in ora, but if no connection string is specified, a ORA-12162 error will be reported.

1) if you use the System user to log on to the system, the following error message is returned:
[Oracle @ asdlabdb01 ~] $ Sqlplus system/sys

SQL * Plus: Release 10.2.0.3.0-production on Sat Aug 27 22:54:02 2010

Copyright (c) 1982,200 6, Oracle. All rights reserved.

Error:
ORA-12162: TNS: net service name is incorrectly specified


Enter user-Name:

2) the same error message is returned when you log on as sysdba.
[Oracle @ asdlabdb01 ~] $ Sqlplus/As sysdba

SQL * Plus: Release 10.2.0.3.0-production on Sat Aug 27 22:54:48 2010

Copyright (c) 1982,200 6, Oracle. All rights reserved.

Error:
ORA-12162: TNS: net service name is incorrectly specified


Enter user-Name:

3) at this time, if you use the service name to connect to the database, it will be successful, which is also a strange phenomenon.
[Oracle @ asdlabdb01 ~] $ Sqlplus system/sys @ ora10g

SQL * Plus: Release 10.2.0.3.0-production on Sat Aug 27 22:53:41 2010

Copyright (c) 1982,200 6, Oracle. All rights reserved.


Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options

System @ ora10g>

Imagine that your goal is to quickly enter the database for maintenance operations. However, at this time, login encounters obstacles and you will feel awkward. Even worse, if you use a connection string to log on to the database and stop the database at this time, the result will be even worse: the database will not be able to start because it cannot log on normally.

3. Fault Cause
The reason behind the strange fault is that oracle_sid is not specified!
Check the system's current ORACLE_HOME and oracle_sid environment variables.
[Oracle @ asdlabdb01 ~] $ Echo $ ORACLE_HOME
/Oracle/APP/Oracle/product/10.2.0/db_1
[Oracle @ asdlabdb01 ~] $ Echo $ oracle_sid

[Oracle @ asdlabdb01 ~] $

It can be seen that only the ORACLE_HOME environment variable is set, but oracle_sid is empty at this time, which is the real cause of this problem.

4. troubleshooting
Give oracle_sid and log on again.
[Oracle @ asdlabdb01 ~] $ Export oracle_sid = ora10g
[Oracle @ asdlabdb01 ~] $ Echo $ oracle_sid
Ora10g
[Oracle @ asdlabdb01 ~] $ Sqlplus/As sysdba

SQL * Plus: Release 10.2.0.3.0-production on Sat Aug 27 23:27:34 2010

Copyright (c) 1982,200 6, Oracle. All rights reserved.


Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options

Sys @ ora10g>

OK. Now the problem is handled.

5. Summary
What is the inspiration from this case?
1) The default error message is sometimes not of reference value. In some cases, these error messages may mislead us. The cause is simple. The default error message cannot cover all failures;
2) any system-level setup problems may cause database system exceptions;
3) when encountering a fault, we need to be calm and calm. In some cases, we may need to check the original information.

Suggestion: to avoid the problems mentioned in the article, first, you can write environment variables such as oracle_sid to the system profile, but you must ensure the validity of the system profile file. Second, do not fill in the profile, and manually specify the database environment variables each time you log on to the database server. Although this method adds a keyboard strike, it is more intuitive and more secure.

Good luck.