PL/SQL 用户登录出现ORA-12170:TNS:连接超时 等问题时,建议参考如下解决方案。
1.cmd ------ ping ip地址
查看网络问题,能否ping通ip地址;
2.cmd ------ tnsping ip地址(或服务器的实例名SID
①如果出现"TNS-12535:操作超时" 提示,可能是服务器端防火墙没有关闭,关闭防火墙即可;
②如果出现下图显示,则证明没有问题;
3.cmd ------ netstat - na 查看1521端口是否关闭
①如果显示关闭,则需要在防火墙设置中将1521端口设为例外;
②如果出现下图显示,则证明没有问题;
4.cmd ------ lsnrctl status (重点:大部分错误集中于此)
lsnrctl是listener-control 监听器的缩写,查看监听的状态
当输入命令后出现下图所示
或出现监听不支持服务等文字,基本可以确定我们安装目录product\11.2.0\dbhome_1\NETWORK\ADMIN 下的tnsnames.ora文件或listener.ora文件出现问题。
①检查tnsnames.ora文件,查看代码中HOST后面对应的是不是你的IP地址,如果不是修改为正确的IP 即可。(连接无线网络的多注意,因为每次的IP是随机分配的)如下显示为正确的文件内容
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.245.68)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
②检查listener.ora文件,listener.ora文件我们需确保文件中至少存在如下代码
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = UPAS-20181130MI112)(PORT = 1521))
)
)
如果lsnrctl status命令下出现监听不支持服务问题我们还需要在上面的基础上加一段,如下为完整代码
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = UPAS-20181130MI112)(PORT = 1521))
)
)