/*
|| Oracle 10gR2 RAC LBA Features Listing
||
|| Demonstrates Oracle 10gR2 Load Balancing Advisory (LBA) features for
|| Real Application Clusters, including:
|| - How to set up client-side load balancing and failover
|| - How to set up server-side load balancing
|| - How to set up Load Balancing Advisory features
|| - How to monitor the efficiency and outcomes of the Load Balancing Advisory
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10gR2
|| Load Balancing Advisor, and it should be carefully proofread before
|| executing it against any existing Oracle database to insure that no
|| potential damage can occur.
*/
/*
|| Listing 1: Setting up client-side connection load balancing
*/
#####
# Add these entries to each client's TNSNAMES.ORA configuration file
# to enable Client-Side Load Balancing ONLY (i.e., no failover)
#####
CSLB_ONLY =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
/*
|| Listing 2: Setting up client-side connection load balancing plus failover
*/
#####
# Add these entries to each client's TNSNAMES.ORA configuration file
# to enable Client-Side Load Balancing PLUS Failover
#####
CSLB_FAILOVER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2-vip)(PORT = 1521))
(LOAD_BALANCE = ON) # Activates load balancing
(FAILOVER = ON) # Activates failover
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
/*
|| Listing 3: Setting up server-side connection load balancing features.
|| Note that server-side load balancing requires:
|| 1.) New entries in every client's TNSNAMES.ORA file for the new alias
|| 2.) New entries in the TNSNAMES.ORA file of every node in the cluster
|| to include the REMOTE_LISTENER setting
|| 3.) The addition of *.REMOTE_LISTENER parameter to all nodes in cluster
|| to force each node's Listener to register with each other
*/
#####
# Add these entries to each server's TNSNAMES.ORA file to enable Server-Side
# Load Balancing:
#####
LISTENERS_RACDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2-vip)(PORT = 1521))
)
SSLB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux2-vip)(PORT = 1521))
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
-----
-- Run this command to add the REMOTE_LISTENERS initialization parameter to
-- the common SPFILE for all nodes in the RAC clustered database:
-----
ALTER SYSTEM SET REMOTE_LISTENER = LISTENERS_RACDB SID='*' SCOPE=BOTH;
/*
|| Listing 4: Setting up Load Balancing Advisory features in an Oracle 10g
|| Real Applications Cluster (RAC) clustered database environment
*/
#####
# Create, register, and start three new services with
# Cluster-Ready Services
#####
srvctl add service -d racdb -s ADHOC -r racdb1,racdb2
srvctl start service -d racdb -s ADHOC
srvctl add service -d racdb -s DSS -r racdb1,racdb2
srvctl start service -d racdb -s DSS
srvctl add service -d racdb -s OLTP -r racdb1,racdb2
srvctl start service -d racdb -s OLTP
/*
|| Listing 5: Using DBMS_SERVICE.MODIFY_SERVICE to configure RAC services
|| to use Load Balancing Advisory features in an Oracle 10g
|| Real Applications Cluster (RAC) clustered database environment
*/
-----
-- Configuring existing RAC services to use the Load Balancing Advisory:
-- 1.) ADHOC: No Load Balancing Advisory
-- 2.) DSS: Load Balancing Advisory with Service Time goal
-- 3.) OLTP: Load Balancing Advisory with Throughput goal
-- Note that Advanced Queueing (AQ) tracking is also activated.
-----
BEGIN
DBMS_SERVICE.MODIFY_SERVICE(
service_name => 'ADHOC'
,aq_ha_notifications => TRUE
,goal => DBMS_SERVICE.GOAL_NONE
,clb_goal => DBMS_SERVICE.CLB_GOAL_LONG
);
DBMS_SERVICE.MODIFY_SERVICE(
service_name => 'DSS'
,aq_ha_notifications => TRUE
,goal => DBMS_SERVICE.GOAL_SERVICE_TIME
,clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT
);
DBMS_SERVICE.MODIFY_SERVICE(
service_name => 'OLTP'
,aq_ha_notifications => TRUE
,goal => DBMS_SERVICE.GOAL_THROUGHPUT
,clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT
);
END;
/
-----
-- Confirm these services' configuration by querying DBA_SERVICES:
-----
SET PAGESIZE 50
SET LINESIZE 11O
TTITLE 'Services Configured to Use Load Balancing Advisory (LBA) Features|
(From DBA_SERVICES)'
COL name FORMAT A16 HEADING 'Service Name' WRAP
COL created_on FORMAT A20 HEADING 'Created On' WRAP
COL goal FORMAT A12 HEADING 'Service|Workload|Management|Goal'
COL clb_goal FORMAT A12 HEADING 'Connection|Load|Balancing|Goal'
COL aq_ha_notifications FORMAT A16 HEADING 'Advanced|Queueing|High-|Availability|Notification'
SELECT
name
,TO_CHAR(creation_date, 'mm-dd-yyyy hh24:mi:ss') created_on
,goal
,clb_goal
,aq_ha_notifications
FROM dba_services
WHERE goal IS NOT NULL
AND name NOT LIKE 'SYS%'
ORDER BY name
;
TTITLE OFF
/*
|| Listing 6: Using the GV$SERVICEMETRIC global view to track how RAC
|| services are responding to the Load Balancing Advisor
*/
TTITLE 'Current Service-Level Metrics|(From GV$SERVICEMETRIC)'
BREAK ON service_name NODUPLICATES
COL service_name FORMAT A08 HEADING 'Service|Name' WRAP
COL inst_id FORMAT 9999 HEADING 'Inst|ID'
COL beg_hist FORMAT A10 HEADING 'Start Time' WRAP
COL end_hist FORMAT A10 HEADING 'End Time' WRAP
COL intsize_csec FORMAT 9999 HEADING 'Intvl|Size|(cs)'
COL goodness FORMAT 999999 HEADING 'Good|ness'
COL delta FORMAT 999999 HEADING 'Pred-|icted|Good-|ness|Incr'
COL cpupercall FORMAT 99999999 HEADING 'CPU|Time|Per|Call|(mus)'
COL dbtimepercall FORMAT 99999999 HEADING 'Elpsd|Time|Per|Call|(mus)'
COL callspersec FORMAT 99999999 HEADING '# 0f|User|Calls|Per|Second'
COL dbtimepersec FORMAT 99999999 HEADING 'DBTime|Per|Second'
COL flags FORMAT 999999 HEADING 'Flags'
SELECT
service_name
,TO_CHAR(begin_time,'hh24:mi:ss') beg_hist
,TO_CHAR(end_time,'hh24:mi:ss') end_hist
,inst_id
,goodness
,delta
,flags
,cpupercall
,dbtimepercall
,callspersec
,dbtimepersec
FROM gv$servicemetric
WHERE service_name IN ('OLTP','DSS','ADHOC')
ORDER BY service_name, begin_time DESC, inst_id
;
CLEAR BREAKS
TTITLE OFF