站在前人的肩膀上:
https://www.cnblogs.com/david-zhang-index/p/4916770.html
https://blog.csdn.net/breaker892902/article/details/17607479
https://blog.csdn.net/sunxiaoju/article/details/78890663
解决SQL PLUS可以连接,外部工具不能连接的问题?
环境
Windows Server 2008
Oracle 11g
排错过程
1、外部应用不能访问数据库了 (只有数据端的SQL PLUS能访问数据库),故障发生。
2、查看监听状态,发现有TNS-12541 ,TNS-12560等错误。
3、查看服务里面监听器的状态,如下图,先重启1,再重启2
4、问题还存在,此时进入DOS ( CMD ),执行 lsnrctl stat,显示问题如下:
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production
Copyright © 1991, 2010, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS: 无监听程序
TNS-12560: TNS: 协议适配器错误
TNS-00511: 无监听程序
64-bit Windows Error: 61: Unknown error
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS: 无监听程序
TNS-12560: TNS: 协议适配器错误
TNS-00511: 无监听程序
64-bit Windows Error: 61: Unknown error
5、查询资料,GOOGLE了一下,发现有人也有类似的问题。应该是ORACLE的一个BUG.当日志文件达到4GB的时候会出现。
BUG号为9879101 : THE CONNECT THROUGH LISTENER WAS SLOW WHEN LISTNER LOG GROWED 4GB。
6、于是按照上面的描述执行
C:>LSNRCTL
LSNRCTL>set log_file mylogs
LSNRCTL>set current_listener <listener_name>
LSNRCTL>set log_status OFF
LSNRCTL>save_config
即如下:
1)LSNRCTL进入交互模式
2)执行set current_listener LISTENER
3)set log_status off
4)stop 停止监听器
5)手工删除ADR指定的监听日志路径下的listener.log文件
6)start重启监听器
7)status查看状态
遇到 TNS-01190: 用户无权执行所请求的监听程序命令"问题解决
方法简单粗暴:
如果不想每次都去目录%ORACLE_HOME%\bin下找文件LSNRCTL.EXE,可以改变LSNRCTL.EXE文件的属性。到“兼容性”选项卡下找到“以管理员身份运行此程序”,选中,点确定。
启动时提示不存在该监听器
状态: 失败 -测试失败: Listener refused the connection with the following error: ORA-12505, TNS:listener does
修改原 Listener.ora 文件
# listener.ora Network Configuration File: D:\app\administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\app\administrator
添加如下配置
(SID_DESC =
(GLOBAL_DBNAME = LONGFANGYUN)
(SID_NAME = LONGFANGYUN)
(ORACLE_HOME = D:\app\administrator\product\11.2.0\dbhome_1)
)
修改后的listener.ora文件
# listener.ora Network Configuration File: D:\app\administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = LONGFANGYUN)
(SID_NAME = LONGFANGYUN)
(ORACLE_HOME = D:\app\administrator\product\11.2.0\dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\app\administrator
** 里面涉及的LONGFANGYUN改成自己的SID就行了 **