/*
|| 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