同台服务器多版本数据库监听配置

Posted on 2011 年 11 月 01 日 by 惜分飞

在一台服务器上分别安装有Oracle 10g和Oracle 11g，现在需要对这两个数据库都配置对应的监听，使其能够正常工作，本文对配置方法做了总结，供参考学习

一、动态监听
Oracle 10g listener.ora配置

[oracle@node1 admin]$ more listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = node1.srtcloud.com)(PORT = 1521))

)

Oracle 11g listener.ora配置

[oracle@node1 admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = node1.srtcloud.com)(PORT = 1522))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))

)

ADR_BASE_LISTENER = /opt/oracle

[oracle@node1 admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORA11G =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.140)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = ora11g)

)

SQL> show parameter local_lis

NAME TYPE VALUE

———————————— ———– ——————————

local_listener string ora11g

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

oracle 24713 1 0 10:30 ? 00:00:00 /opt/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit

oracle 24720 1 0 10:30 ? 00:00:00 /opt/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

事项说明：
1、10g和11g两个数据库都采用动态监听，但是由于默认情况下，动态注册只会注册1521端口，所以其中一个数据库需要设置local_listener参数，用于监听其他端口
2、10g和11g两个数据库都采用动态监听，会启动两个互不干扰的监听进程

二、静态监听
1）使用一个监听

[oracle@node1 admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = chf)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = chf)

)

(SID_DESC =

(GLOBAL_DBNAME = ecp)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = ecp)

)

(SID_DESC =

(GLOBAL_DBNAME = ora11g)

(ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)

(SID_NAME = ora11g)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =node1.srtcloud.com)(PORT = 1521))

)

[oracle@node1 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-NOV-2011 10:03:44

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start

Starting /opt/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 11.2.0.3.0 – Production

System parameter file is /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /opt/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.srtcloud.com)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-NOV-2011 10:03:45

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1521)))

Services Summary…

Service "chf" has 1 instance(s).

Instance "chf", status UNKNOWN, has 1 handler(s) for this service…

Service "ecp" has 1 instance(s).

Instance "ecp", status UNKNOWN, has 1 handler(s) for this service…

Service "ora11g" has 1 instance(s).

Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.srtcloud.com)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-NOV-2011 10:03:45

Uptime 0 days 0 hr. 3 min. 57 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1521)))

Services Summary…

Service "chf" has 2 instance(s).

Instance "chf", status UNKNOWN, has 1 handler(s) for this service…

Instance "chf", status READY, has 1 handler(s) for this service…

Service "chfXDB" has 1 instance(s).

Instance "chf", status READY, has 1 handler(s) for this service…

Service "chf_XPT" has 1 instance(s).

Instance "chf", status READY, has 1 handler(s) for this service…

Service "ecp" has 2 instance(s).

Instance "ecp", status UNKNOWN, has 1 handler(s) for this service…

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ecpXDB" has 1 instance(s).

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ecp_XPT" has 1 instance(s).

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ora11g" has 1 instance(s).

Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

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

oracle 23602 1 0 10:03 ? 00:00:00 /opt/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit

事项说明：
1、这种方法是采用一个监听实现监听所有实例，只会启动一个监听进程，只监听一个端口
2、实例中显示的为：11g一个实例，10g有两个实例的监听情况
3、这种监听方法可以在10g中配置，也可以在11g中配置，都能够正常工作

2）使用多个监听
Oracle 10g listener.ora配置

[oracle@node1 admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = chf)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = chf)

)

(SID_DESC =

(GLOBAL_DBNAME = ecp)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = ecp)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =node1.srtcloud.com)(PORT = 1521))

)

[oracle@node1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 – Production on 01-NOV-2011 10:35:12

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.srtcloud.com)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.4.0 – Production

Start Date 01-NOV-2011 10:30:34

Uptime 0 days 0 hr. 4 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File /opt/oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1521)))

Services Summary…

Service "chf" has 2 instance(s).

Instance "chf", status UNKNOWN, has 1 handler(s) for this service…

Instance "chf", status READY, has 1 handler(s) for this service…

Service "chfXDB" has 1 instance(s).

Instance "chf", status READY, has 1 handler(s) for this service…

Service "chf_XPT" has 1 instance(s).

Instance "chf", status READY, has 1 handler(s) for this service…

Service "ecp" has 2 instance(s).

Instance "ecp", status UNKNOWN, has 1 handler(s) for this service…

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ecpXDB" has 1 instance(s).

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ecp_XPT" has 1 instance(s).

Instance "ecp", status READY, has 1 handler(s) for this service…

The command completed successfully

Oracle 11g listener.ora配置

[oracle@node1 admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ora11g)

(ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)

(SID_NAME = ora11g)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =node1.srtcloud.com)(PORT = 1522))

)

[oracle@node1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-NOV-2011 10:37:04

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.srtcloud.com)(PORT=1522)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-NOV-2011 10:30:25

Uptime 0 days 0 hr. 6 min. 39 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1522)))

Services Summary…

Service "ora11g" has 2 instance(s).

Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service…

Instance "ora11g", status READY, has 1 handler(s) for this service…

Service "ora11gXDB" has 1 instance(s).

Instance "ora11g", status READY, has 1 handler(s) for this service…

The command completed successfully

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

oracle 24713 1 0 10:30 ? 00:00:00 /opt/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit

oracle 24720 1 0 10:30 ? 00:00:00 /opt/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

事项说明：使用了两个监听，分别监听不同的端口，对应不同的数据库

RAC负载均衡配置

Posted on 2011 年 08 月 11 日 by 惜分飞

1、客户端均衡(Client-Side LB)
工作原理：当客户端发起连接时，会从地址列表中随机选取一个，再使用随机算法把连接请求分散到各个实例。

存在缺点：
1.1)分配连接时没有考虑每个节点的真实负载，最后分配不过不一定是平衡
1.2)随机算法需要长时间片，如果在短时间内同时发起多个连接，这些连接有可能被分配到一个节点上
1.3)有些情况下，连接可能被分配到故障节点上

配置方法：在tns中添加LOAD_BALANCE = YES条目

2、服务器端均衡(Server-Side LB)
工作原理：
2.1)该均衡实现是依赖于Listener收集的负载信息。在数据库运行过程中，PMON后台进程会收集数系统的负载信息，然后登记到Listener中。
2.2)PMON进程不仅会向本地的Listener注册，也会想其他节点上的Listener注册，但到底向何处注册，是由Remote_Listeners和Local_Listener这两个参数决定。Local_Listener不用设置，而Remote_Listeners需要设置，参数值有一个tnsnames项。
2.3)当收到客户端连接请求时，就会把连接转给负载最小的节点，这个节点可能是自己，也可能是其他节点，也就是Listener会转发客户端的连接请求。

配置方法：

SQL> show parameter listener;        

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
remote_listener                      string      LISTENERS_DEVDB

tnsnames.ora
LISTENERS_DEVDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
  )

listener.ora(除掉SID_LIST_LISTENER_NAME项)
LISTENER_RAC1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521)(IP = FIRST))
    )
  )

3、两者联合使用
Server-Side LB和Client-Side LB不是互斥的，两者可以一起工作，这个时候客户端的连接请求会先从地址列表中随机选择一个地址，然后向该地址的Listener发送请求；Listener接到请求后，根据各个节点负载情况从中挑选出最合适的节点转发连接请求。

Oracle限制IP访问

Posted on 2011 年 07 月 15 日 by 惜分飞

通过修改sqlnet.ora文件实现，如果没有在$ORACLE_HOME/network/admin中没有该文件，自己创建一个或者从samples中复制一个出来。

#是否检测上述参数的设置
tcp.validnode_checking=yes
#允许访问的ip
tcp.invited_nodes =(ip1,ip2,……)
#不允许访问的ip
tcp.excluded_nodes=(ip1,ip2,……)

需要注意的问题：
1、需要设置参数为YES,这样才能激活。
2、建议设置允许访问的IP，因为IP地址有可能被随意修改，就不能起到自己的目的。
3、 TCP当参数TCP.INVITED_NODES和TCP.EXCLUDED_NODES设置的地址相同的时候将覆盖TCP.INVITED_NODES设置（10G）。
4、需要重启监听器才能生效。
5、这个方式只是适合TCP协议。
6、这个配置适用于9i以上版本。在9i之前的版本使用文件protocol.ora。
7、在服务器上直接连接数据库不受影响。
8、这种限制方式事通过监听器来限制的。
9、这个限制只是针对IP检测，对于用户名检测事不支持的。
10、不能设置ip段和通配符
11、如果配置TCP.INVITED_NODES就必须配置TCP.EXCLUDED_NODES，否则监听不能正常启动，报如下错误
TNS-12560: TNS:protocol adapter error
TNS-00584: Valid node checking configuration error

如（测试192.168.9.215地址不能登录）：
tcp.validnode_checking = yes
tcp.excluded_nodes=(192.168.9.215)
tcp.included_nodes=(192.168.9.215,192.168.11.12,127.0.0.1,211.155.227.172)

单网卡绑定多IP导致TNS-12542等错误

Posted on 2011 年 07 月 02 日 by 惜分飞

今天想在家中访问下公司的oracle数据库，我了解的情况是那台服务器是有内外网ip，内网可以访问数据库。所以按照常理推断我只要配置下listener，外网应该也就可以正常访问
于是我就登陆到服务器上，修改listener.ora文件

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.12)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 211.155.227.172)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

lsnrctl start 不能正常启动，报错如下：

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=211.155.227.172)(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

根据错误提示，意思是HOST=211.155.227.172这个(地址+端口+协议)已经被占用
第一反应：使用netstat -an|grep 1521没有发现该地址有1521端口启动，说明没有被占用
第二反应：防火墙，通过查看发现防火墙是关闭
通过以上两项查看都没有问题，那我修改下监听端口尝试下，然后我把监听端口改成了1522，监听能够正常启动，并且开始监听1522端口。通过实验证明1522端口是正常的，那问题出在哪里呢？为什么1521不行，我查看下ip地址的设置情况

eth0      Link encap:Ethernet  HWaddr 00:E0:4D:C3:D5:18  
          inet addr:192.168.11.12  Bcast:192.168.11.255  Mask:255.255.252.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:5000774 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1610691 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:1828268348 (1.7 GiB)  TX bytes:436101782 (415.8 MiB)

eth0:1    Link encap:Ethernet  HWaddr 00:E0:4D:C3:D5:18  
          inet addr:211.155.227.172  Bcast:211.155.227.175  Mask:255.255.255.240
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

发现192.168.11.12和211.155.227.172都是绑定在eth0的网卡上，因为监听在192.168.11.12启动了1521端口，所以211.155.227.172上的1521不能起来（因为同一张网卡）
我想既然是公用同一张网卡，那么监听了192.168.11.12:1521，那我用211.155.227.172:1521应该可以正常访问，除掉监听中的(ADDRESS = (PROTOCOL = TCP)(HOST = 211.155.227.172)(PORT = 1522))，然后直接在自己的电脑上修改tns，使用 211.155.227.172地址访问，果然能够访问。
通过这次事件得出结论：单网卡绑定多IP，只要监听主IP地址，其他绑定的IP均可以访问，不需要修改任何监听信息

WARNING: inbound connection timed out (ORA-3136)

Posted on 2011 年 06 月 27 日 by 惜分飞

1、现象
alert文件中
Mon Jun 27 11:12:34 2011
WARNING: inbound connection timed out (ORA-3136)

sqlnet.log文件中
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 10.2.0.4.0 – Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 – Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 – Production
Time: 27-JUN-2011 11:12:34
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.227.20)(PORT=2104))

2、原因
Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database. In this case, SQLNET.INBOUND_CONNECT__TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 seconds in Oracle 10.2. It is these timeout values that can cause the errors described in this note.
Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.

3、解决
1). set INBOUND_CONNECT_TIMEOUT_listenername=0 in listener.ora
2). set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.
3). stop and start both listener and database.
4). Now try to connect to DB and observe the behaviour

4、具体操作
4.1）修改INBOUND_CONNECT_TIMEOUT_listenername
4.1.1)lsnrctl命令修改
LSNRCTL> set inbound_connect_timeout 0
LSNRCTL>save_config

4.1.2)vi修改
修改listener.ora文件，加入: INBOUND_CONNECT_TIMEOUT_listenername=0

4.2）修改SQLNET.INBOUND_CONNECT__TIMEOUT
修改sqlnet.ora文件，加入: SQLNET.INBOUND_CONNECT__TIMEOUT=0