今天在进行修改oracle_sid环境变量的时候,将相关的环境变量值去掉,从而不能进入sqlplus,报错如下:
[oracle@kel ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 8 19:19:21 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
Enter user-name:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
Enter user-name:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
查看错误信息:
[oracle@kel ~]$ oerr ora 12162
12162, 00000, "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.
错误信息里描述的是:指定的连接字符串错误,服务名称出错,从而使用netmgr命令来检查连接字符串的正确性:
[oracle@kel ~]$ netmgr
在这里可以测试连接字符串是否出错,但是在这里我测试是失败的,从而查看1521端口是否启动监听:
[oracle@kel ~]$ netstat -tnlp
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:37667 0.0.0.0:* LISTEN 5140/ora_d000_orcl
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -
从而可以看到1521端口未启动监听,从而查看监听的状态:
[oracle@kel ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-JUN-2014 19:31:45
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
在这里看到监听是未启动的状态,从而打开数据库监听:
[oracle@kel ~]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-JUN-2014 19:31:49
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /home/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Log messages written to /home/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kel)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 08-JUN-2014 19:31:50
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /home/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kel)(PORT=1521)))
The listener supports no services
The command completed successfully
再次测试,发现是可以成功连接的,从而利用连接字符串来连接服务器:
[oracle@kel ~]$ sqlplus system/orcl@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 8 19:34:53 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
发现使用连接字符串是可以连接数据库的,再次使用本地进行连接发现还是不可以的,如下:
[oracle@kel ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 8 19:45:49 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
最后查看环境变量ORACLE_SID的值:
[oracle@kel ~]$ echo $ORACLE_SID
[oracle@kel ~]$
发现环境变量未设置,从而导致连接出错,将ORACLE_SID设置为数据库实例名称:
[oracle@kel ~]$ export ORACLE_SID=orcl
[oracle@kel ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 8 19:48:51 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
当将环境变量ORACLE_SID的值设置为正确的值的时候,发现已经可以连接。