问题1现象
数据导出脚本执行失败，报错如下
ORA-12537
到服务器上查看，报错：
[oracle@test ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-APR-2019 11:17:10 Copyright (c) 1991, 2013, 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@test ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-APR-2019 11:18:08 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production Log messages written to /app/oracle/diag/tnslsnr/ggfwdb1/listener/alert/log.xml Error listening on: (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) TNS-12542: TNS:address already in use TNS-12560: TNS:protocol adapter error TNS-00512: Address already in use Linux Error: 98: Address already in use Listener failed to start. See the error message(s) above...
解决：
思路，报错提示地址已经使用，百度查找结果，大多都是hosts文件配置问题，但我的hosts文件正常的
最后真实原因是有其它监听已启用，kill掉重新注册监听
1）ps查看已有的监听并清除
$ ps -ef|grep LISTENER oracle 1781 1373 0 11:38 pts/0 00:00:00 grep LISTENER grid 5685 1 0 2018 ? 00:06:51 /grid/11.2.0/grid_1/bin/tnslsnr LISTENER_SCAN2 -inherit grid 5687 1 0 2018 ? 00:09:32 /grid/11.2.0/grid_1/bin/tnslsnr LISTENER_SCAN3 -inherit daemon 26031 1 0 11:10 ? 00:00:00 /grid/11.2.0/grid_1/bin/tnslsnr LISTENER -inherit $ kill -9 5685 5687 26031
2）动态注册恢复监听状态
$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 2 11:41:31 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> alter system set local_listener=''; System altered. SQL> alter system register; System altered. SQL> exit
查看：
[oracle@ggfwdb1 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-APR-2019 11:42:14 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 02-APR-2019 11:39:23 Uptime 0 days 0 hr. 2 min. 50 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /app/oracle/diag/tnslsnr/ggfwdb1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ggfwdb1)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "xxxx" has 2 instance(s). Instance "xxxx", status READY, has 2 handler(s) for this service... Instance "xxxx", status READY, has 1 handler(s) for this service... Service "xxxx" has 2 instance(s). Instance "xxxx", status READY, has 1 handler(s) for this service... Instance "xxxx", status READY, has 1 handler(s) for this service... The command completed successfully
问题二现象：
本以为可以连接了，然后执行远程登录测试，出现报错ORA-12502
C:UsersAdministrator>sqlplus user/passwd@hostname.:1521/sid SQL*Plus: Release 11.2.0.1.0 Production on 星期二 4月 2 13:14:11 2019 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-12502: TNS: 监听程序没有从客户机收到 CONNECT_DATA
解决：
设置所有节点的LOCAL_LISTENER参数，即可解决。host为vip
SQL> show parameter local_listener; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.173)(PORT=1521))'; System altered. SQL> show parameter local_listener; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (ADDRESS=(PROTOCOL=tcp)(HOST=1 92.168.10.173)(PORT=1521))
成功远程登录，问题解决
