TNS-12546 TNS-12560 TNS-00516

目的：在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

warning消失