Oracle Learning RAC (ix) cluster load balancing and failover

1. Viewing listening, using a grid user

11GRAC1:

11grac1-> lsnrctl statuslsnrctl for linux: version 11.2.0.3.0 -  production on 14-aug-2015 00:30:26copyright  (c)  1991, 2011, Oracle.   all rights reserved. connecting to  (description= (address= (PROTOCOL=IPC) (Key=listener)) status of the  LISTENER------------------------alias                      LISTENERVersion                    tnslsnr for linux:  Version 11.2.0.3.0 - ProductionStart Date                 13-AUG-2015 22:17:25Uptime                     0 days 2 hr. 13 min. 1 sectrace level                offSecurity                   on: local os authenticationsnmp                        offlistener parameter file   /u01/app/11.2.0/grid/network/ admin/listener.oralistener log file         /u01/app/ grid/diag/tnslsnr/11grac1/listener/alert/log.xmllistening 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 statuslsnrctl for linux: version 11.2.0.3.0 -  production on 14-aug-2015 00:28:31copyright  (c)  1991, 2011, Oracle.   all rights reserved. connecting to  (description= (address= (PROTOCOL=IPC) (Key=listener)) status of the  LISTENER------------------------alias                      LISTENERVersion                    tnslsnr for linux:  Version 11.2.0.3.0 - ProductionStart Date                 13-AUG-2015 22:21:38Uptime                     0 days 2 hr. 6 min. 52 sectrace level                offSecurity                   on: local os authenticationsnmp                        offlistener parameter file   /u01/app/11.2.0/grid/network/ admin/listener.oralistener log file         /u01/app/ grid/diag/tnslsnr/11grac2/listener/alert/log.xmllistening 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 statuslsnrctl for linux: version 11.2.0.3.0 -  production on 14-aug-2015 00:29:21copyright  (c)  1991, 2011, Oracle.   all rights reserved. connecting to  (description= (address= (PROTOCOL=IPC) (Key=listener)) status of the  LISTENER------------------------alias                      LISTENERVersion                    tnslsnr for linux:  Version 11.2.0.3.0 - ProductionStart Date                 13-AUG-2015 22:53:28Uptime                     0 days 1 hr. 35 min. 53 sectrace level                offSecurity                   on: local os authenticationsnmp                        offlistener parameter file   /u01/app/11.2.0/grid/network/ admin/listener.oralistener log file         /u01/app/ grid/diag/tnslsnr/11grac3/listener/alert/log.xmllistening 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

Each machine listens to its own public-ip and VIP.

No one is listening to Scanip.

So let's try using Scanip to connect to the database.

View the configuration of the 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)))

Log in to the database using the DEVDB connection string

1.

11grac2-> sqlplus Sys/[email protected] as sysdbasql> select instance_name from V$instance;instance_ NAME------------------------------------------------DEVDB3

2.

11grac2-> sqlplus Sys/[email protected] as sysdbasql> select instance_name from V$instance;instance_ NAME------------------------------------------------devdb1sql>

3.

11grac2-> sqlplus Sys/[email protected] as sysdbasql> select instance_name from V$instance;instance_ NAME------------------------------------------------devdb3sql>

4.

11grac2-> sqlplus Sys/[email protected] as sysdbasql> select instance_name from V$instance;instance_ NAME------------------------------------------------devdb2sql>

As can be seen, Oracle introduces the concept of scan in 11g RAC. Load balancing is automatically implemented. You simply connect to the scan, and the scan then forwards the connection to the database. And load balancing is achieved.

So let's see how to implement failover. And when the client-connected instance crashes, the connection automatically drifts to the other instance.

To implement failover, we want to do the following configuration

[Email protected] bin]# su-oracle11grac2-> srvctl add service-d devdb-s devdb_taf-r devdb1,devdb2,devdb311grac2-  > Srvctl start service-d devdb-s devdb_taf11grac2-> srvctl Modify service-d devdb-s devdb_taf-q true-p BASIC -E SELECT

Modify Tnsname.ora to add a connection string to 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) &Nbsp;   )   ) 

Verifying failover

1. Log on to the database using SYS on 11GRAC1

11grac1-> sqlplus/as sysdbasql*plus:release 11.2.0.3.0 Production on Fri 01:01:54 2015Copyright (c) 1982, 201  1, Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit productionwith The partitioning, Real appli Cation Clusters, Automatic Storage Management, Olap,data Mining and Real application testing optionssql> Select Instanc E_name from V$instance;instance_name------------------------------------------------devdb1sql> Grant Select any Table to Scott; Grant succeeded. Sql>

2. Use the Scott user on 11grac2 to log in to the database using the DEVDB_TAF connection string.

11grac2-> sqlplus scott/[email protected]_tafsql*plus:release 11.2.0.3.0 Production on Fri 01:03:28 2015Copyrig  HT (c) 1982, Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit productionwith The partitioning, Real appli Cation Clusters, Automatic Storage Management, Olap,data Mining and Real application testing optionssql>

3. View the Scott connection in 11grac1 's Sqlplus

Sql> Select Inst_id,sid from gv$session where username= ' SCOTT '; inst_id SID--------------------2 56

4. The Scott user executes a more time-consuming query that shutdown the instance on 11GRAC2 during execution to 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 abortoracle instance shut down.

5. We'll go to the 11GRAC1 and see the results of step 3rd.

Sql> Select Inst_id,sid from gv$session where username= ' SCOTT '; inst_id SID--------------------1 64

Scott's connection has shifted from instance 2 to instance 1.

And the 4th step of the query, the results are also out.

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

This article is from the "Ding Dong" blog, please be sure to keep this source http://lqding.blog.51cto.com/9123978/1684774

Oracle Learning RAC (ix) cluster load balancing and failover