OracleラーニングRAC(9)クラスタ負荷分散およびフェイルオーバ
11grac1:
11grac1-> lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-AUG-2015 00:30:26
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 13-AUG-2015 22:17:25
Uptime 0 days 2 hr. 13 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/11grac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.199.10)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.199.20)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "devdb" has 1 instance(s).
Instance "devdb1", status READY, has 1 handler(s) for this service...
Service "devdbXDB" has 1 instance(s).
Instance "devdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
11grac2:
11grac2-> lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-AUG-2015 00:28:31
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 13-AUG-2015 22:21:38
Uptime 0 days 2 hr. 6 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/11grac2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.199.11)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.199.21)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "devdb" has 1 instance(s).
Instance "devdb2", status READY, has 1 handler(s) for this service...
Service "devdbXDB" has 1 instance(s).
Instance "devdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
11grac3:
11grac3-> lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-AUG-2015 00:29:21
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 13-AUG-2015 22:53:28
Uptime 0 days 1 hr. 35 min. 53 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/11grac3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.199.12)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.199.22)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM3", status READY, has 1 handler(s) for this service...
Service "devdb" has 1 instance(s).
Instance "devdb3", status READY, has 1 handler(s) for this service...
Service "devdbXDB" has 1 instance(s).
Instance "devdb3", status READY, has 1 handler(s) for this service...
The command completed successfully
各機器の傍受は、自分のpublic-ipとvipを傍受している.
scanipを傍受している台は1台もありません.
では、scanipを使用してデータベースに接続してみましょう.
tnsnameの構成の表示
11grac2-> cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
11grac2-> cat tnsnames.ora
# tnsnames.ora.11grac2 Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora.11grac2
# Generated by Oracle configuration tools.
DEVDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = devdb)
)
)
DEVDDBを使用して文字列を接続してデータベースにログインする
1.
11grac2-> sqlplus sys/Ora600oracle@DEVDB as sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
devdb3
2.
11grac2-> sqlplus sys/Ora600oracle@DEVDB as sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
devdb1
SQL>
3.
11grac2-> sqlplus sys/Ora600oracle@DEVDB as sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
devdb3
SQL>
4.
11grac2-> sqlplus sys/Ora600oracle@DEVDB as sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
devdb2
SQL>
11 g RACでは、OracleがSCANの概念を導入していることがわかります.自動的に負荷分散を実現した.SCANに接続するだけで、SCANはデータベースに接続を渡します.負荷分散を実現します.
では、フェイルオーバの実現方法を見てみましょう.また、クライアント接続のインスタンスがクラッシュすると、接続は他のインスタンスに自動的にドリフトします.
フェイルオーバを実現するには、次のような構成を行います.
[root@11grac2 bin]# su - oracle
11grac2-> srvctl add service -d devdb -s DEVDB_TAF -r devdb1,devdb2,devdb3
11grac2-> srvctl start service -d devdb -s DEVDB_TAF
11grac2-> srvctl modify service -d devdb -s DEVDB_TAF -q TRUE -P BASIC -e SELECT
tnsnameを修正する.ora、DEVDDB_への接続を追加TAFの接続文字列
11grac2-> cat tnsnames.ora
# tnsnames.ora.11grac2 Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora.11grac2
# Generated by Oracle configuration tools.
DEVDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = devdb)
)
)
DEVDB_TAF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = devdb_taf)
)
)
フェイルオーバの検証
11grac1-> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 14 01:01:54 2015
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
devdb1
SQL> grant select any table to scott;
Grant succeeded.
SQL>
2.11 grac 2でscottユーザーを使用してDEVDD_TAF接続文字列登録データベース.
11grac2-> sqlplus scott/tiger@DEVDB_TAF
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 14 01:03:28 2015
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
3.11 grac 1のsqlplusでscott接続を表示する場合
SQL> select inst_id,sid from gv$session where username='SCOTT';
INST_ID SID
---------- ----------
2 56
4.scottユーザーは比較的時間のかかるクエリーを実行し、実行中に11 grac 2上のインスタンスshutdown abortを実行する
SQL> select count(*) from (
2 select * from dba_source union
3 select * from dba_source union
4 select * from dba_source union
5 select * from dba_source union
6 select * from dba_source union
7 select * from dba_source union
8 select * from dba_source union
9 select * from dba_source union
10 select * from dba_source union
11 select * from dba_source);
SQL> shutdown abort
ORACLE instance shut down.
5.さらに11 grac 1に行って、手順3の結果を確認します.
SQL> select inst_id,sid from gv$session where username='SCOTT';
INST_ID SID
---------- ----------
1 64
scottの接続はインスタンス2からインスタンス1に移行した.
そして4ステップ目のクエリは、結果も出てきました.
SQL> select count(*) from (
2 select * from dba_source union
3 select * from dba_source union
4 select * from dba_source union
5 select * from dba_source union
6 select * from dba_source union
7 select * from dba_source union
8 select * from dba_source union
9 select * from dba_source union
10 select * from dba_source union
11 select * from dba_source);
COUNT(*)
----------
632805