Let’s have look on some listener issues in the database.
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.
Example:
oracle@hostname:~$ sqlplus hr SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 26 15:34:14 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: ERROR: ORA-12162: TNS:net service name is incorrectly specified
Solution:
Windows: set ORACLE_SID=hrtest
Linux: Set oracle home if not set, you can check it by “echo $ORACLE_HOME”
oracle@hostname:~$ export ORACLE_HOME=/opt/oracle/product/ora11r2 oracle@hostname:~$ export ORACLE_SID=hrtest oracle@hostname:~$ sqlplus scott SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 26 15:39:22 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production With the Partitioning and Automatic Storage Management options SQL>
If you are using TNS files then you can check your TNS by the following way:
oracle@hostname:~$ sqlplus hr@hr_test SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 26 15:44:13 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: ERROR: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
Fix the TNS information in tnsnames.ora file and check it.
oracle@hostname:~$ tnsping hr_test TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 26-FEB-2014 15:45:54 Copyright (c) 1997, 2009, Oracle. All rights reserved. Used parameter files: /opt/oracle/product/ora11r2/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host.hr.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = scotttest))) OK (0 msec)
Now it should Work.
oracle@hostname:~$ sqlplus hr@hr_test SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 26 15:45:36 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL>
Note: If you are using client and tnping is not working then check your client have installed that features or not. Usually, you will get the tnsping.exe file under bin folder if this features installed.
Today’s thought
“You have to act and act now.” – Larry Ellison
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel : https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
