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

发表于 2011 年 11 月 1 日 由 惜分飞

在一台服务器上分别安装有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
 
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 
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
 
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
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
 
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 
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
 

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

发表在 Oracle 监听 | 一条评论

SP2-1503 SP2-0152 错误解决

发表于 2011 年 10 月 31 日 由 惜分飞
一、现场描述
服务器上有Oracle 10g环境变量分别为
Oracle 10g环境变量

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_SID=ecp
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin:$ORACLE_BASE:$ORACLE_HOME
export PATH=${PATH}:$ORACLE_HOME/bin:$ORA_CRS_HOME:$ORA_CRS_HOME/bin
export PATH=${PATH}:$LD_LIBRARY_PATH:$CLASSPATH:$ORACLE_PATH
 
现在在上面Oracle 11g，在shell中执行下面命令修改环境变量
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=ora11g
然后安装数据库，安装过程一切顺利，安装完成，当使用sqlplus的时候报如下错误：
[oracle@node1 tmp]$ sqlplus /nolog
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
 
二、问题原因
[oracle@node1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@node1 ~]$ export ORACLE_SID=ora11g
[oracle@node1 ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 31 17:04:17 2011
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@node1 ~]$ sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
因为系统的环境变量中的PATH没有修改过来，直接使用sqlplus的时候，还是会调用Oracle 10g中的sqlplus，而此时ORACLE_HOME与其不匹配，导致出现上面错误
 
三、证明猜想，试验如下
[oracle@node1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@node1 ~]$  env|grep ORACLE
ORACLE_PATH=/opt/oracle/common/oracle/sql:.:/opt/oracle/product/10.2.0/db_1/rdbms/admin
ORACLE_SID=ecp
ORACLE_BASE=/opt/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/opt/oracle/product/11.2.0/db_1
[oracle@node1 ~]$ sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
说明：修改ORACLE_HOME，指向Oracle 11g的目录，然后执行sqlplus(该程序属于10g)，报同样错误

四、补充说明
1、如果在一台服务器上安装不同版本数据库，建议使用不用用户安装，这样可以减少很多管理上没必要的麻烦，提高工作效率
2、sqlplus程序需要和ORACLE_HOME向对应，不然就会出现SP2-1503   SP2-0152错误
3、本次排错中学习到知识，如果要执行一个shell文件修改环境变量，可以使用source
4、如果必须使用同一个用户安装，那么建议新安装的oracle版本建一个shell文件，然后使用source shell_filename，修改环境变量（特别注意PATH)

发表在 ORA-xxxxx | 评论关闭

Linux防火墙配置

发表于 2011 年 10 月 28 日 由 惜分飞

一、Linux防火墙基础知识
1、常用命令
查看配置 iptables -L -n
修改配置 iptables -A INPUT -p tcp -s 192.168.1.12 –dport 22 -j ACCEPT
保存修改 /etc/init.d/iptables save
开启/关闭/重启/状态 /etc/init.d/iptables start/stop/restart/status

2、常用参数
–append -A chain Append to chain
–in-interface -i [!] input name[+] network interface name ([+] for wildcard)
–proto -p [!] proto protocol: by number or name, eg. `tcp’
–source -s [!] address[/mask] source specification
–destination -d [!] address[/mask] destination specification
–jump -j target target for rule (may load target extension)
–dport 目标端端口
–sport 源端端口

二、Liunx防火墙配置方法
1、setup配置

注意INPUT/FORWARD/OUTPUT设置

2、vi修改/etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
#表示进入服务器包，一般情况下修改为DROP
:INPUT ACCEPT [0:0]
#表示转发包，根据实际情况
:FORWARD ACCEPT [0:0]
#表示从服务器发出包，一般设置为ACCEPT
:OUTPUT ACCEPT [0:0]
#不清楚该含义
:RH-Firewall-1-INPUT – [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT

#表示所有eth1/eth0网卡包都可以被允许
-A RH-Firewall-1-INPUT -i eth1 -j ACCEPT
-A RH-Firewall-1-INPUT -i eth0 -j ACCEPT

-A RH-Firewall-1-INPUT -p icmp –icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp –dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp –dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp –dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 1521 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 25 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 80 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 21 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 443 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT –reject-with icmp-host-prohibited
COMMIT

service iptables restart

3、iptables命令配置
#注意是否已经配置了相关策略，不然可能导致ssh不能访问
iptables -P INPUT DROP
iptables -P OUTPUT DROP
#慎重，要不你的ssh也失去链接了
iptables -P FORWARD DROP

#如果iptables -P OUTPUT DROP配置，一定需要类此这两条配合
iptables -A INPUT -p tcp –dport 22 -j ACCEPT
iptables -A OUTPUT -p tcp –sport 22 -j ACCEPT

iptables -A INPUT -p tcp -s 10.218.32.153 –dport 1521 -j ACCEPT
iptables -A OUTPUT -p tcp -d 10.218.32.153 –sport 1521 -j ACCEPT

/etc/init.d/iptables save
service iptables restart

发表在 Linux | 一条评论