查看监听,使用grid用户
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的。
那么我们使用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)
)
)
使用DEVDB连接字符串登录数据库
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>
可见,在11g 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,添加一个连接到DEVDB_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上使用sys登录数据库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. 在11grac2上使用scott用户以DEVDB_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.在11grac1的sqlplus中查看scott连接的情况SQL> select inst_id,sid from gv$session where username='SCOTT';
INST_ID SID
---------- ----------
2 56
4. scott用户执行一个比较耗时的查询,在执行期间,将11grac2上的实例shutdown abortSQL> 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. 我们再去11grac1上,查看第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