Locations of visitors to this page
Showing posts with label sqlnet. Show all posts
Showing posts with label sqlnet. Show all posts

Tuesday, February 24, 2009

remote listener - 远程监听器

remote listener



配置远程监听器

远程监听器将客户端的连接重新定向(re-direct)到数据库服务器上的本地监听器

1.
数据库服务器上启一个本地监听器
grep -q "^LISTENER_LOCAL" $ORACLE_HOME/network/admin/listener.ora || \
cat >>$ORACLE_HOME/network/admin/listener.ora <<'EOF'
LISTENER_LOCAL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1541)))
    )
  )
EOF
lsnrctl start LISTENER_LOCAL

[oracle@MGT ~]$ lsnrctl start LISTENER_LOCAL

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 24-FEB-2009 12:00:52

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

Starting /home/oracle/app/oracle/product/10.2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /home/oracle/app/oracle/product/10.2/network/admin/listener.ora
Log messages written to /home/oracle/app/oracle/product/10.2/network/log/listener_local.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MGT.s3lab.mot.com)(PORT=1541)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1541)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_LOCAL
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                24-FEB-2009 12:00:52
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/10.2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/product/10.2/network/log/listener_local.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MGT.s3lab.mot.com)(PORT=1541)))
The listener supports no services
The command completed successfully
[oracle@MGT ~]$


tail -f $ORACLE_HOME/network/log/listener_local.log



2.
另找一台机器作为监听器服务器, 用来监听远程数据库
grep -q "^LISTENER_REMOTE" $ORACLE_HOME/network/admin/listener.ora || \
cat >>$ORACLE_HOME/network/admin/listener.ora <<'EOF'
LISTENER_REMOTE =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1531)))
    )
  )
EOF
lsnrctl start LISTENER_REMOTE

[oracle@DEV-RPT-2 ~]$ lsnrctl start LISTENER_REMOTE

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 24-FEB-2009 12:03:35

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

Starting /home/oracle/app/oracle/product/10.2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /home/oracle/app/oracle/product/10.2/network/admin/listener.ora
Log messages written to /home/oracle/app/oracle/product/10.2/network/log/listener_remote.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DEV-RPT-2.s3lab.mot.com)(PORT=1531)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1531)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_REMOTE
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                24-FEB-2009 12:03:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/10.2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/product/10.2/network/log/listener_remote.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DEV-RPT-2.s3lab.mot.com)(PORT=1531)))
The listener supports no services
The command completed successfully
[oracle@DEV-RPT-2 ~]$


tail -f $ORACLE_HOME/network/log/listener_remote.log


3.
在数据库上配置本地/远程监听器参数
alter system set local_listener="(address=(protocol=tcp)(host=MGT.s3lab.mot.com)(port=1541))";
alter system set remote_listener="(address=(protocol=tcp)(host=DEV-RPT-2.s3lab.mot.com)(port=1531))";

SQL> alter system set local_listener="(address=(protocol=tcp)(host=MGT.s3lab.mot.com)(port=1541))";

System altered.

SQL> alter system set remote_listener="(address=(protocol=tcp)(host=DEV-RPT-2.s3lab.mot.com)(port=1531))";

System altered.

SQL>

在远程监听器服务器上显示
24-FEB-2009 12:04:26 * service_register * test * 0
24-FEB-2009 12:05:26 * service_update * test * 0
24-FEB-2009 12:05:38 * service_update * test * 0

查看监听器状态, 已经注册上了
lsnrctl status listener_remote
lsnrctl service listener_remote

[oracle@DEV-RPT-2 ~]$ lsnrctl status LISTENER_REMOTE

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 24-FEB-2009 12:14:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1531)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_REMOTE
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                24-FEB-2009 12:03:35
Uptime                    0 days 0 hr. 10 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/10.2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/product/10.2/network/log/listener_remote.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DEV-RPT-2.s3lab.mot.com)(PORT=1531)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "test_XPT" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@DEV-RPT-2 ~]$ lsnrctl service LISTENER_REMOTE

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 24-FEB-2009 12:14:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1531)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (address=(protocol=tcp)(host=MGT.s3lab.mot.com)(port=1541))
Service "test_XPT" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (address=(protocol=tcp)(host=MGT.s3lab.mot.com)(port=1541))
The command completed successfully
[oracle@DEV-RPT-2 ~]$


本地监听器显示:
24-FEB-2009 12:05:46 * service_register * test * 0
24-FEB-2009 12:14:02 * service_update * test * 0

监听器状态
[oracle@MGT ~]$ lsnrctl status listener_local

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 24-FEB-2009 12:12:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1541)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_LOCAL
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                24-FEB-2009 12:00:52
Uptime                    0 days 0 hr. 12 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/10.2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/product/10.2/network/log/listener_local.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MGT.s3lab.mot.com)(PORT=1541)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "test_XPT" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@MGT ~]$ lsnrctl service listener_local

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 24-FEB-2009 12:13:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1541)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "test_XPT" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
[oracle@MGT ~]$


4.
客户端通过远程监听器连接远程的数据库
conn kitty/kitty@DEV-RPT-2:1531/test

SQLL> conn kitty/kitty@DEV-RPT-2:1531/test
Connected.
SQLL>
远程监听器重定向(re-direct)了客户端的连接
实际上, 客户端最终连的还是数据库所在服务器上的监听器



外部链接:
How To Configure a Dispatcher or Instance to Register Against a Remote Listener
Init.ora Parameter "REMOTE_LISTENER" Reference Note
How To Set Up a 9i Database to Contact a Remote Listener
Conntinuing question "Remote Listener Registration"



-fin-
Posted by wen xie at 8:30 PM 0 comments
Labels: ,

Monday, October 13, 2008

inbound connect timout - INBOUND_CONNECT_TIMEOUT参数

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/10/13
Subject: inbound_connect_time参数
To: xiewenxiewen at gmail.com


1. sqlnet.ora 参数 SQLNET.INBOUND_CONNECT_TIMEOUT
表示等待用户认证超时的时间，单位是秒，缺省值是60秒
如果用户认证超时了，服务器日志alert.log显示出错信息
"WARNING: inbound connection timed out (ORA-3136)"

举例：
连接监听器，不输入用户名/密码
sqlplus /@test
[oracle@MGT ~]$ sqlplus /@test

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 20 07:53:42 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

等一分钟后,alert.log报ora-3136错
Tue Jan 20 07:54:42 2009
WARNING: inbound connection timed out (ORA-3136)


编辑$ORACLE_HOME/network/admin/sqlnet.ora，设置参数SQLNET.INBOUND_CONNECT_TIMEOUT等于12秒
grep SQLNET.INBOUND_CONNECT_TIMEOUT $ORACLE_HOME/network/admin/sqlnet.ora
[oracle@MGT ~]$ grep SQLNET.INBOUND_CONNECT_TIMEOUT $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=12
[oracle@MGT ~]$

再连接监听器，不输入用户名/密码
sqlplus /@test
[oracle@MGT ~]$ sqlplus /@test

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 20 07:59:39 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

过了12秒，alert.log又报错了
Tue Jan 20 07:59:51 2009
WARNING: inbound connection timed out (ORA-3136)

这个参数只要修改了sqlnet.ora配置文件，便立即生效，不需要重启监听器/数据库


2. listener.ora的参数 inbound_connect_timeout_监听器名
表示等待用户连接请求超时的时间，单位是秒，缺省值是60秒
如果连接请求超时了，监听器日志listener.log显示出错信息
"TNS-12525: TNS:listener has not received client's request in time allowed"
服务器日志alert.log里不报错

举例:
用telnet命令在监听器端口建立连接
time telnet localhost 1521
过一分钟后，连接被断开
[oracle@MGT ~]$ time telnet localhost 1521
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

real 1m0.015s
user 0m0.001s
sys 0m0.002s
[oracle@MGT ~]$

同时监听器日志报错
20-JAN-2009 08:01:29 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
20-JAN-2009 08:02:32 * * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3871)) * establish * * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12535: TNS:operation timed out
TNS-12606: TNS: Application timeout occurred

把参数修改成9秒
lsnrctl set inbound_connect_timeout 9
[oracle@MGT ~]$ lsnrctl set inbound_connect_timeout 9

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-JAN-2009 08:03:06

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 9
The command completed successfully
[oracle@MGT ~]$

再试
time telnet localhost 1521
[oracle@MGT ~]$ time telnet localhost 1521
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

real 0m9.004s
user 0m0.000s
sys 0m0.002s
[oracle@MGT ~]$

20-JAN-2009 08:03:28 * * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3891)) * establish * * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12535: TNS:operation timed out
TNS-12606: TNS: Application timeout occurred
连接超时缩短为9秒

这个参数也是立即生效的，不需要重启监听器或数据库

也可以修改listener.ora配置文件，然后reload监听器，使新的参数生效
grep INBOUND_CONNECT_TIMEOUT $ORACLE_HOME/network/admin/listener.ora
lsnrctl reload
[oracle@MGT ~]$ grep INBOUND_CONNECT_TIMEOUT $ORACLE_HOME/network/admin/listener.ora
INBOUND_CONNECT_TIMEOUT_LISTENER=5
[oracle@MGT ~]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-JAN-2009 08:05:44

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@MGT ~]$

指定时间后连接超时
[oracle@MGT ~]$ time telnet localhost 1521
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

real 0m5.006s
user 0m0.002s
sys 0m0.002s
[oracle@MGT ~]$

20-JAN-2009 08:06:06 * * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3916)) * establish * * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12535: TNS:operation timed out
TNS-12606: TNS: Application timeout occurred



外部链接:
ORA-12525
ORA-03136
SQLNET.INBOUND_CONNECT_TIMEOUT
Connections that Used to Work in Oracle 10.1 Now Intermittently Fail with ORA-3113,ORA-3106 or ORA-3136 in 10.2
Troubleshooting ORA - 3136 WARNING Inbound Connection Timed Out
When do SQLNET.ORA changes take effect ?



Xie Wen (谢文)
Network & Operations,
Multimedia Applications & Services (MDB)
MOTOROLA Inc.
NO.104 mail box, 8th floor, Motorola Tower,
No. 1 Wang Jing East Road, Chao Yang District,
Beijing 100102 P. R. China
e-mail wenxie at motorola.com


-fin-
Posted by wen xie at 6:10 PM 0 comments
Labels: ,
Subscribe to: Posts (Atom)
Website Analytics

Followers