天道酬勤

最近一老失眠，今儿一大清早就爬起来上网，从google的订阅服务得知：由EYGLE与KAMUS二位大师发起并成立了中国Oracle用户组ACOUG（All China Oracle User Group）。届时，越来越多的数据库爱好者、Oracle爱好者将会出没这里。well，so……一线民工以此博文当贺电来表达喜悦心情，各位工友欲知更多详情请参考官网，谢谢!

-The End-

Wow，昨天在Eygle大师的Blog上看到《Oracle DBA手记》已经上架了，先恭喜一下这几位大牛（Eygle、Yangtingkun、老熊、zergduan、banping）出了新作。继续往下看，没想到第一本书是寄给我的，这里谢谢Eygle，谢谢几位大牛们，感谢Julia，因为我想大师业务缠身应该没有时间寄书：）。农历新年快到了，这本书对我来说是新年最好的礼物，同时也是莫大的鼓舞，告诫自己努力。图片作证：

-The End-

在一个10g RAC环境中测试拔掉网线，vip的切换花了很长时间，在oracle support上搜索如下：

[ID 403743.1]

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.1.0.7

This problem can occur on any platform.

Symptoms

This example is based on SUN Solaris platform, with IPMP configured for the public network. In this case, VIP failover takes almost 4 minutes to complete when both network cables of the public network are pulled from one node.

crsd.log shows:

2006-12-07 13:14:05.401: [ CRSAPP][4588] CheckResource error for ora.node1.vip error code = 1

2006-12-07 13:14:05.408: [ CRSRES][4588] In stateChanged, ora.node1.vip target is ONLINE

2006-12-07 13:14:05.409: [ CRSRES][4588] ora.node1.vip on node1 went OFFLINE unexpectedly

<<< detect network cable failure and VIP OFFLINE immediately

2006-12-07 13:14:05.410: [ CRSRES][4588] StopResource: setting CLI values

2006-12-07 13:14:05.420: [ CRSRES][4588] Attempting to stop `ora.node1.vip` on member `node1`

2006-12-07 13:14:06.651: [ CRSRES][4588] Stop of `ora.node1.vip` on member `node1` succeeded.

2006-12-07 13:14:06.652: [ CRSRES][4588] ora.node1.vip RESTART_COUNT=0 RESTART_ATTEMPTS=0

2006-12-07 13:14:06.667: [ CRSRES][4588] ora.node1.vip failed on node1 relocating.

2006-12-07 13:14:06.758: [ CRSRES][4588] StopResource: setting CLI values

2006-12-07 13:14:06.766: [ CRSRES][4588] Attempting to stop `ora.node1.LISTENER_NODE1.lsnr` on member `node1`

2006-12-07 13:17:41.399: [ CRSRES][4588] Stop of `ora.node1.LISTENER_NODE1.lsnr` on member `node1` succeeded.

<<< takes 3.5 minutes to stop listener

2006-12-07 13:17:41.402: Attempting to stop `ora.node1.ASM1.asm` on member `node1`

<<< stop dependant inst and ASM

2006-12-07 13:17:55.610: [ CRSRES][4588] Stop of `ora.node1.ASM1.asm` on member `node1` succeeded.

2006-12-07 13:17:55.661: [ CRSRES][4588] Attempting to start `ora.node1.vip` on member `node2`

2006-12-07 13:18:00.260: [ CRSRES][4588] Start of `ora.node1.vip` on member `node2` succeeded.

<<< now VIP failover complete after almost 4 mins

ora.node1.LISTENER_NODE1.lsnr.log shows:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1vip)(PORT=1521)(IP=FIRST)))

TNS-12535: TNS:operation timed

2006-12-07 13:17:41.329: [ RACG][1] [23916][1][ora.node1.LISTENER_NODE1.lsnr]: out

TNS-12560: TNS:protocol adapter error

TNS-00505: Operation timed out

Solaris Error: 145: Connection timed out

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.10.100)(PORT=1521)(IP=FIRST)))

The command completed successfully

Client connection hang during this failover time.

Changes

This may be a new setup, or a setup that was migrated from an earlier release.

Cause

This problem is caused by the first address in the listener.ora configuration being an address that uses the TCP protocol.

In this circumstance, when a network cable is pulled, “lsnrctl stop” listener has to wait for TCP timeout before it can check next address. On the Solaris platform, TCP timeout is defined by tcp_ip_abort_cinterval with a default value of 180000 (3 minutes).   That is why shutting down listener almost took 3.5 minutes. (TCP timeout on other platforms may vary).  The error message “Solaris Error: 145: Connection timed out” in ora.node1.LISTENER_NODE1.lsnr.log also indicates it is waiting for tcp timeout.

The listener.ora in this scenario is defined as:

LISTENER_NODE1 =
 
(DESCRIPTION_LIST =
 
(DESCRIPTION =
 
(ADDRESS_LIST =
 
(ADDRESS = (PROTOCOL = TCP)(HOST = node1vip)(PORT = 1521)(IP = FIRST))
 
)
 
(ADDRESS_LIST =
 
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.100)(PORT = 1521)(IP = FIRST))
 
)
 
(ADDRESS_LIST =
 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
 
)
 
)
 
)

Solution

To prevent this, move the IPC address to be the first address for the listener in the listener.ora, eg:

LISTENER_NODE1 =
 
(DESCRIPTION_LIST =
 
(DESCRIPTION =
 
(ADDRESS_LIST =
 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
 
)
 
(ADDRESS_LIST =
 
(ADDRESS = (PROTOCOL = TCP)(HOST = node1vip)(PORT = 1521)(IP = FIRST))
 
)
 
(ADDRESS_LIST =
 
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.100)(PORT = 1521)(IP = FIRST))
 
)
 
)
 
)

When lsnrctl tries to stop the listener, it will now connect to the IPC address first, which is available during that time. It will not have to wait for tcp timeout.

After the above change, the VIP failover only takes 48 to 50 seconds to complete regardless of the tcp_ip_abort_cinterval setting.

Please note, listener.ora files newly created from 10.2.0.3 to 11.1.0.7 should have the IPC protocol as the first address in listener.ora in most cases.  However, if you have upgraded from a previous release, or manually modified/copied over a listener.ora from a previous install, you may not have the IPC protocol as the first address, regardless of your version. Manual modification is required to move IPC protocol to be the first address to avoid the problem described in this note.

BTW：梦想有多远的blog也有说明。

-The End-

Postgres Plus Advanced Server 又名EnterpriseDB，由前身PostgreSQL发展而来，一款真正意义上的开源数据库，加之有IBM和RedHat的鼎力相助，发展势头迅猛。由于ORACLE高昂的License费用，在如今的金融危机下，很多厂品都有价格的优势，而ORACLE不但没有且还提高了大约40%价格，无疑对很多的企业来说是雪上加霜，已无力承受，大家纷纷开始计划着将其迁移到其它产品上。EnterpriseDB吸引了众多企业的眼球是因为它与ORACLE有强大的兼容性，现有ORACLE系统上80%的应用不需做任何修改直接可以拿到EnterpriseDB上应用。有人一定会先想到MySQL，其性能勿庸置疑，加上高可用都有，目前国内的众多WEB2.0公司都在用，taobao、google等等。MySQL在处理WEB方面的应用确实不错，但在其它方面不一定是最好的，加上被收购了二次之后，现在在ORACLE手可前途未卜，个人认为MySQL不能算真正的开源数据库，而EnterpriseDB是。我相信在未来几年，会有越来越多的中小企业因为看重EnterpriseDB的众多优越特性而选择它。

EnterpriseDB的安装是我见过的RDBMS产品中安装最简单的，简单介绍for linux x86 platform的安装步骤。

1. 获得安装文件
2. # tar -zxvf pgplus-advsvr-linux-x86_830112.tar.gz
3. # setenforce Permissive
4. Graphical Installer:

# ./pgplus-advsvr-linux-x86_830112.bin

Command Line Installer:

# ./pgplus-advsvr-linux-x86_830112.bin -console

输入上面的命令之后剩下的工作就在对话中完成。

默认安装路径如下：

/opt/PostgresPlus/8.3R2AS

/opt/PostgresPlus/8.3R2AS/data

/opt/PostgresPlus/8.3R2AS/data/pg_xlog

5.# setenforce Enforcing

6.配置环境变量：安装完成之后，在每个用户的家目录下会有一个edb_83.env的文本文件，与$EDBHOME/dbserver/scripts/edb_83.env文件一样。

To load the environment for a single user:

#chown <username> /home/<username>/edb_83.env

#vi /home/<username>/.bash_profile

增加如下一行:

. /home/<username>/edb_83.env 注意. /之间有空格

To load the environment for all users:

#vi /etc/profile

增加如下一行:

. /etc/edb_83.env   注意. /之间有空格

我的实验环境：

[enterprisedb@postgres ~]$ edbplus

User: enterprisedb

Enter Password:

Connected to EnterpriseDB 8.3.0.112 (localhost:5444/edb) AS enterprisedb

EDB*Plus: Release 8.3 (Build 19)

Copyright (c) 2008-2009, EnterpriseDB Corporation.  All rights reserved.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SQL>select * FROM v$version;
 
BANNER
 
--------------------------------------------------
 
Oracle DATABASE 10g Edition Release 10.2.0.1.0 - P
 
roduct IS similar
 
PL/SQL Release 10.2.0.1.0 - Production IS similar
 
CORE 10.2.0.1.0 Production IS similar
 
SQL>

更详细内容请参考官方文档。

-The End-