Oracle RAC測試--failover and balance

oracle RAC測試--failover and balance

今天重新測試了RAC的failover和balance，分別使用真實IP和VIP來進行測試。結果發現兩個IP都可以實現這兩種特性。


RAC相關信息：
RAC1:10.10.10.1(vip：10.10.10.11) devdb1
RAC2:10.10.10.2(vip：10.10.10.12) devdb2
racdb：devdb
創建的有CRM服務

1、在OCI客戶端tnsname中配置如下，首先用vip進行測試。

RACDB2=
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.12)(PORT = 1521))
   (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = racdb)
    (INSTANCE_NAME = racdb2)
   )
  )
RACDB1 =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.11)(PORT = 1521))
   (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = devdb1)
    (INSTANCE_NAME = devdb1)
   )
  )
LISTENERS_RACDB =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.11)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.12)(PORT = 1521))
  )
RACDB =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.11)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.12)(PORT = 1521))
   (LOAD_BALANCE = yes)
   (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = devdb)
    (FAILOVER_MODE =
     (TYPE = SELECT)
     (METHOD = BASIC)
     (RETRIES = 180)
     (DELAY = 5)
    )
   )
  )
 
2、配置完成，在OCI客戶端做以下測試：
C:\Documents and Settings\Administrator>tnsping racdb1

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 15-3月-
2009 13:02:11

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

已使用的參數文件:
e:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora


已使用TNSNAMES適配器來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10 .11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb1) (INSTANCE_NAME = devdb1)))
OK (10毫秒)

C:\Documents and Settings\Administrator>tnsping racdb2

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 15-3月-
2009 13:02:12

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

已使用的參數文件:
e:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora


已使用TNSNAMES適配器來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10 .12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) ( INSTANCE_NAME = racdb2)))
OK (0毫秒)

C:\Documents and Settings\Administrator>

可以看出，都是通的。

3.使用SQLPLUS連接：
C:\Documents and Settings\Administrator>sqlplusnam/nam@racdb

SQL*Plus: Release 11.1.0.6.0 - Production on星期日3月15 13:04:11 2009

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


連接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
devdb2

SQL>

目前我連接的是devdb2，現在我們驗證failover：

登陸到rac2上，shutdown devdb2：

Last login: Fri Feb 27 21:28:08 2009 from 10.10.10.5
rac2-> sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 15 13:07:09 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

再回到OCI客戶端的SQLPLUS中，測試：

SQL>select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
devdb1

SQL>

實現了無縫FAILOVER。

4、以下測試balance:

分別在rac1和rac2上配置一下參數，以避免ora-12545的錯誤:
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))' SID='devdb1';

System altered.

SQL>

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.12)(PORT=1521))' SID='devdb2';

System altered.

SQL>

在OCI客戶端編寫BAT腳本，腳本內容如下：
for /L %%G in (1,1,10) do start /b sqlplusnam/nam@racdb
目的，創造10個連接。

驗證兩台機器目前的連接數：

SQL> select count(*),machine from v$session group by machine;

   COUNT(*) MACHINE
---------- ---------------------------------------- ------------------------
         30 rac1
       

SQL> select count(*),machine from v$session group by machine;

   COUNT(*) MACHINE
---------- ---------------------------------------- ------------------------
         29 rac2

運行程序後再次驗證：

SQL> select count(*),machine from v$session group by machine;

   COUNT(*) MACHINE
---------- ---------------------------------------- ------------------------
         30 rac1
          5 WORKGROUP\WWW-CD2CAF03643

SQL>

SQL> /

   COUNT(*) MACHINE
---------- ---------------------------------------- ------------------------
          5 WORKGROUP\WWW-CD2CAF03643
         29 rac2

SQL>

10個連接每台機器5個，實現了balance的目的。


5、再次測試，使用實際IP地址測試，將OCI客戶端的TNSNAME裡VIP全部改為實際IP，並修改RAC1和RAC2的local_listener地址

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.1)(PORT=1521))' SID='devdb1';

System altered.

SQL>

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.2)(PORT=1521))' SID='devdb2';

System altered.

SQL>

6、使用tnsping測試連通性：
C:\Documents and Settings\Administrator>tnsping racdb

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 15-3月-
2009 14:44:02

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

已使用的參數文件:
e:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora


已使用TNSNAMES適配器來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10 .1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.2)(PORT = 1521)) ( LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb)
(FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))

OK (5330毫秒)

C:\Documents and Settings\Administrator>tnsping racdb1

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 15-3月-
2009 14:44:10

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

已使用的參數文件:
e:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora


已使用TNSNAMES適配器來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10 .1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb1) ( INSTANCE_NAME = devdb1)))
OK (0毫秒)

C:\Documents and Settings\Administrator>tnsping racdb2

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 15-3月-
2009 14:44:11

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

已使用的參數文件:
e:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora


已使用TNSNAMES適配器來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10 .2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (I NSTANCE_NAME = racdb2)))
OK (0毫秒)

C:\Documents and Settings\Administrator>

7、測試failover:

C:\Documents and Settings\Administrator>sqlplusnam/nam@racdb

SQL*Plus: Release 11.1.0.6.0 - Production on星期日3月15 14:45:09 2009

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


連接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
devdb2

SQL>

登陸RAC2，shutdown abort:

......

SQLPLUS測試：

devdb2

SQL> /

INSTANCE_NAME
--------------------------------
devdb1

SQL>

實現無縫切換。

8、測試實際地址的balance，方法同上：

SQL> select count(*),machine from v$session group by machine;

   COUNT(*) MACHINE
---------- ---------------------------------------- ------------------------
         32 rac1
          5 WORKGROUP\WWW-CD2CAF03643

SQL>

SQL> select count(*),machine from v$session group by machine;

   COUNT(*) MACHINE
---------- ---------------------------------------- ------------------------
          5 WORKGROUP\WWW-CD2CAF03643
         31 rac2

SQL>

實現balance。