TNS-12546 TNS-12560 TNS-00516

目的：在server 21上增加隻允許指定IP登入

步驟：cp server23上的sqlnet.ora配置檔案到server21上，由于疏忽導緻的一些問題

oracle：11.2.0.1 os：rhel5.3

[[email protected] admin]$ pwd

/app/soft/oracle/product/11.2.0/dbhome_1/network/admin

[[email protected] 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.*)

[[email protected] 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

[[email protected] 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

--發現主機名沒更改過來，導緻上面錯誤，更正

[[email protected] 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.*)

[[email protected] 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

[[email protected] admin]$ kill -9 22334

[[email protected] 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

[[email protected] admin]$ vim listener.ora  增加下面配置

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

[[email protected] admin]$ lsnrctl stop

[[email protected] 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消失