TNS-12546 TNS-12560 TNS-00516

最新推荐文章于 2021-05-25 11:19:18 发布

原创最新推荐文章于 2021-05-25 11:19:18 发布 · 7k 阅读

0 ·

CC 4.0 BY-SA版权

Troubleshooting 专栏收录该内容

5 篇文章

订阅专栏

本文记录了在Oracle 11.2.0.1环境下，配置sqlnet.ora文件限制只允许特定IP登录数据库服务器的过程中遇到的问题及解决方法。在配置TCP.VALIDNODE_CHECKING和TCP.INVITED_NODES后，遇到TNS-12546和TNS-12560错误，原因是主机名未正确设置。修复主机名后，通过重新启动监听器解决了权限被拒绝的问题。

部署运行你感兴趣的模型镜像

目的：在server 21上增加只允许指定IP登录

步骤：cp server23上的sqlnet.ora配置文件到server21上，由于疏忽导致的一些问题

oracle：11.2.0.1 os：rhel5.3

[oracle@mlydserver21 admin]$ pwd

/app/soft/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@mlydserver21 admin]$ vim sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/sqlnet.ora:q
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
TRACE_LEVEL_CLIENT = OFF
SQLNET.INBOUND_CONNECT_TIMEOUT = 0
ADR_BASE = /app/soft/oracle

TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(192.168.8.*,10.13.12.*,10.12.36.74,10.23.36.19,mlydserver23.yyy.com,10.13.28.*,192.168.1.*)

[oracle@mlydserver21 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-MAY-2014 10:31:16

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /app/soft/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /app/soft/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mlydserver21.yyy.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mlydserver21.yyy.com)(PORT=1521)))
TNS-12537: TNS:connection closed
TNS-12560: TNS:protocol adapter error
TNS-00507: Connection closed

[oracle@mlydserver21 admin]$ tail -100f /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/trace/listener.log

Mon May 05 10:29:34 2014
System parameter file is /app/soft/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/alert/log.xml
Trace information written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/trace/ora_22294_47080735486752.trc
Trace level is currently 0

Started with pid=22294
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mlydserver21.yyy.com)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
05-MAY-2014 10:29:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=mlydserver21.yyy.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
Mon May 05 10:29:47 2014
05-MAY-2014 10:29:42 * service_register * qunfa * 0
Mon May 05 10:30:11 2014
05-MAY-2014 10:30:11 * service_update * qunfa * 0
Mon May 05 10:30:25 2014
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mlydserver21.yyy.com)(PORT=1521)))
Listener completed notification to CRS on stop
05-MAY-2014 10:30:25 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=mlydserver21.yyy.com)(USER=oracle))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * stop * 0
Mon May 05 10:31:16 2014
System parameter file is /app/soft/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/alert/log.xml
Trace information written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/trace/ora_22334_47877025887008.trc
Trace level is currently 0

Started with pid=22334
Mon May 05 10:31:35 2014
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mlydserver21.yyy.com)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
05-MAY-2014 10:31:35 * 12546
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
Linux Error: 2: No such file or directory
.......
Mon May 05 10:36:47 2014
05-MAY-2014 10:36:47 * 12546
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
Linux Error: 2: No such file or directory

--发现主机名没更改过来，导致上面错误，更正

[oracle@mlydserver21 admin]$ vim sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/sqlnet.ora:q
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
TRACE_LEVEL_CLIENT = OFF
SQLNET.INBOUND_CONNECT_TIMEOUT = 0
ADR_BASE = /app/soft/oracle

TCP.VALIDNODE_CHECKING=yes
TCP.INVITED_NODES=(192.168.8.*,10.13.12.*,10.12.36.74,10.23.36.19,mlydserver21.yyy.com,10.13.28.*,192.168.1.*)

[oracle@mlydserver21 admin]$ ps -ef |grep tns

oracle 22110 21611 0 10:20 pts/1 00:00:00 tail -100f /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/trace/listener.log
oracle 22334 1 0 10:31 ? 00:00:00 /app/soft/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle 22413 22127 0 10:37 pts/2 00:00:00 grep tns
[oracle@mlydserver21 admin]$ kill -9 22334
[oracle@mlydserver21 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-MAY-2014 10:37:29

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /app/soft/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /app/soft/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mlydserver21.yyy.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mlydserver21.yyy.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 05-MAY-2014 10:37:29
Uptime 0 days 0 hr. 0 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/soft/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mlydserver21.yyy.com)(PORT=1521)))
Services Summary...
Service "qunfa" has 1 instance(s).
Instance "qunfa", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

启动成功，但有warning

Mon May 05 10:37:29 2014
System parameter file is /app/soft/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/alert/log.xml
Trace information written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/trace/ora_22424_47218071348000.trc
Trace level is currently 0

Started with pid=22424
Mon May 05 10:37:39 2014
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mlydserver21.yyy.com)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
05-MAY-2014 10:37:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=mlydserver21.yyy.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
Mon May 05 10:37:55 2014
05-MAY-2014 10:37:50 * service_register * qunfa * 0

[oracle@mlydserver21 admin]$ vim listener.ora 增加下面配置

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

[oracle@mlydserver21 admin]$ lsnrctl stop

[oracle@mlydserver21 admin]$ lsnrctl start

listener.log：

Mon May 05 11:14:17 2014
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mlydserver21.yyy.com)(PORT=1521)))
Listener completed notification to CRS on stop
05-MAY-2014 11:14:17 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=mlydserver21.yyy.com)(USER=oracle))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * stop * 0

Mon May 05 11:14:27 2014
System parameter file is /app/soft/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/alert/log.xml
Trace information written to /app/soft/oracle/diag/tnslsnr/mlydserver21/listener/trace/ora_22770_47571540947744.trc
Trace level is currently 0

Started with pid=22770
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mlydserver21.yyy.com)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
05-MAY-2014 11:14:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=mlydserver21.yyy.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0