/*
|| Oracle 11g Database Replay, Part 3: Code Examples
||
|| Demonstrates advanced Oracle 11g Database Replay features,
|| including:
|| - Creation of 
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Database Replay (DBR), and they should be carefully proofread before being
|| executed against any existing Oracle database to avoid potential damage!
*/

/*  
|| Listing 3.1:
|| Adding filters to a workload before its capture
*/

BEGIN
	DBMS_WORKLOAD_CAPTURE.ADD_FILTER(
	     fname => 'ORACLE MANAGEMENT AGENT (DEFAULT)'
	    ,fattribute => 'PROGRAM'
	    ,fvalue => 'emagent%'
	);
	DBMS_WORKLOAD_CAPTURE.ADD_FILTER(
	     fname => 'ORACLE MANAGEMENT SERVICE (DEFAULT)'
	    ,fattribute => 'PROGRAM'
	    ,fvalue => 'OMS'
	);
END;
/

----- 
-- Listing 3.2:
-- Starting the capture of a database-wide workload. Note that for an Oracle
-- 10.2.0.4 DBR Workload Capture, the dynamic initialization parameter
-- PRE_11G_ENABLE_CAPTURE +must+ be set to true!
-----

ALTER SYSTEM SET PRE_11G_ENABLE_CAPTURE=TRUE;

BEGIN
    DBMS_WORKLOAD_CAPTURE.START_CAPTURE(
         name => 'DBR_CAPTURE_100'
        ,dir => 'DBRCONTROL'
        ,duration => NULL
        ,default_action => 'INCLUDE'
        ,auto_unrestrict => TRUE
    );
END;
/

/* 
|| Listing 3.3:
|| Stopping an active workload capture
*/
BEGIN
    DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(
         timeout => 90
        ,reason => 'Termination (after 90 seconds)'
    );
END;
/

/* 
|| Listing 3.4:
|| Printing the results of a successful DBR workload capture
*/

VARIABLE capture_report CLOB;
BEGIN
    :capture_report :=
        DBMS_WORKLOAD_CAPTURE.REPORT(
             capture_id => 2
            ,format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT
        );
END;
/
PRINT :capture_report

/* 
|| Listing 3.5:
|| Preparing for DBR Workload Replay
*/

#####
# Create and start new RAC Service(s) via SRVCTL
#####
$> srvctl add   service -d racdb -s TESTLBA -r racdb1,racdb2
$> srvctl start service -d racdb -s TESTLBA

-----
-- Set up Connection Load Balancing (CLB) and Load Balancing Advisor (LBA)
-- goals for TESTLBA Service so that it uses the Load Balancing Advisor for
-- the shortest connection times
-----
BEGIN   
    DBMS_SERVICE.MODIFY_SERVICE(
         service_name => 'TESTLBA'
        ,goal => DBMS_SERVICE.GOAL_SERVICE_TIME
        ,clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT
        ,aq_ha_notifications => TRUE
    );
END;
/

#####
# This network configuration information needs to be added on each node within
# file $ORACLE_HOME/network/admin/tnsnames.ora
#####

TESTLBA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
    (LOAD_BALANCE = YES)
    (FAILOVER = YES)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTLBA)
    )
  )

/* 
|| Listing 3.6:
|| Preprocessing ("massaging") the previously captured DBR Workload
*/

#####
# Create physical directories in each node's file system
#####
$> mkdir /home/oracle/DBRControl
$> ssh racnode2 "mkdir /home/oracle/DBRControl"

-----
-- Create a corresponding directory object in database RACDB. Note that this
-- directory object maps to either physical directory on nodes RACNODE1 and
-- RACNODE2.
-----
DROP DIRECTORY DBRControl;
CREATE DIRECTORY DBRControl AS '/home/oracle/DBRControl';
GRANT READ, WRITE ON DIRECTORY DBRControl TO PUBLIC;

-----
-- After all the files that represent the captured DBR workload have been
-- copied to the Database Replay workload in directory /home/oracle/DBRControl
-- on each node, prepare that workload for replay
-----
BEGIN
    DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(
        capture_dir => 'DBRCONTROL'
    );
END;
/

/* 
|| Listing 3.7:
|| Setting up DBR Replay of the processed, captured workload
*/

-----
-- Place the database into INIT FOR REPLAY mode. This is a prerequisite to
-- executing procedure PREPARE_REPLAY
-----
BEGIN
    DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
         replay_name => 'DBR_RPL_100'
        ,replay_dir => 'DBRCONTROL'
    );
END;
/

-----
-- Remap connections made during the last Workload Replay to the appropriate
-- connection identifier (TESTLBA) in the RAC clustered database
-----
BEGIN
    DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(
         connection_id => 1
        ,replay_connection => '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2-vip)(PORT=1521))(LOAD_BALANCE=YES)(FAILOVER=YES)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTLBA)))'
    );
END;
/

-----
-- View:    DBA_WORKLOAD_CONNECTION_MAP
-- Purpose: Shows which connections have been captured and
--          how they have been remapped for replay (if applicable)
-----
TTITLE 'Currently Remapped Connections|(From DBA_WORKLOAD_CONNECTION_MAP)'
COL replay_id       FORMAT 99999    HEADING 'Rply|ID'
COL conn_id         FORMAT 99999    HEADING 'Conn|ID'
COL capture_conn    FORMAT A60      HEADING 'Captured Connection String' WRAP
COL replay_conn     FORMAT A60      HEADING 'Replay Connection String' WRAP
SELECT
     replay_id
    ,conn_id
    ,capture_conn
    ,replay_conn
  FROM dba_workload_connection_map
 ORDER BY replay_id, conn_id
;
TTITLE OFF

/* 
|| Listing 3.8:
|| Placing the target database into PREPARE FOR REPLAY mode (i.e., just before
|| starting up Workload Replay Client session(s))
*/

-----
-- Procedure: PREPARE_REPLAY
-- Places the database into PREPARE FOR REPLAY mode, setting
-- the following values for the impending replay of the workload:
-- 1.) SYNCHRONIZATION: TRUE (default) preserves the order of 
--     transactions.
-- 2.) CONNECT_TIME_SCALE: 100 (default) means that the same number
--     of user connections will be created; setting this higher or 
--     lower will create either proportionally more or fewer user
--     connections.
-- 3.) THINK_TIME_SCALE: 100 (default) means that the same delay 
--     time between each user call within a session will be preserved;
--     setting this higher or lower will either increase or decrease 
--     the amount of time between calls.
-- 4.) THINK_TIME_AUTO_CORRECT: TRUE (default) insures that DBR 
--     automatically adjusts the overall execution time of the replay
--     so that it completes in the same amount of time as it originally
--     occurred.
-----
BEGIN
    DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(
         synchronization => TRUE
        ,connect_time_scale => 100
        ,think_time_scale => 100
        ,think_time_auto_correct => TRUE
    );
END;
/

/* 
|| Listing 3.9:
|| Starting the DBR Workload Replay
*/

BEGIN
	DBMS_WORKLOAD_REPLAY.START_REPLAY;
END;
/

/* 
|| Listing 3.10:
|| Monitoring the progress of the DBR Workload Replay
*/

TTITLE 'Replayed Workloads|(From DBA_WORKLOAD_REPLAYS)'
COL id                  FORMAT 999              HEADING 'Rply|ID#'
COL capture_id          FORMAT 999              HEADING 'Cptr|ID#'
COL name                FORMAT A12              HEADING 'Replay|Operation'
COL status              FORMAT A12              HEADING 'Status'
COL dbname              FORMAT A12              HEADING 'Replayed|On Database'
COL dbversion           FORMAT A10              HEADING 'Replay|DB Version'
COL directory           FORMAT A16              HEADING 'Replay|Directory'
COL prepare_dtm         FORMAT A11              HEADING 'Replay|Prepare|Time' WRAP
COL start_dtm           FORMAT A11              HEADING 'Replay|Start|Time' WRAP
COL end_dtm             FORMAT A11              HEADING 'Replay|End|Time' WRAP
COL duration_secs       FORMAT 99999999         HEADING 'Replay|Time (s)'
COL user_calls          FORMAT 99999            HEADING '# of|User|Calls'
COL num_clients         FORMAT 99999            HEADING 'Total|Clnt|Sess'
COL num_clients_done    FORMAT 99999            HEADING 'Clnt|Sess|Done'
SELECT
     id
    ,name
    ,dbname
    ,dbversion
    ,directory
    ,capture_id
    ,user_calls
    ,status
    ,TO_CHAR(prepare_time,'yyyy-mm-dd hh24:mi:ss') prepare_dtm
    ,TO_CHAR(start_time,'yyyy-mm-dd hh24:mi:ss') start_dtm
    ,TO_CHAR(end_time,'yyyy-mm-dd hh24:mi:ss') end_dtm
    ,duration_secs
    ,num_clients
    ,num_clients_done
  FROM dba_workload_replays
;
TTITLE OFF

/* 
|| Listing 3.11:
|| Printing a summary DBR Workload Replay report
*/
VARIABLE replay_report CLOB;
VARIABLE capture_id NUMBER;
VARIABLE replay_id NUMBER;
BEGIN
    :capture_id := 
		DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(
			dir => 'DBRCONTROL'
		);

    -- Get last replay for the captured workload
    SELECT max(id) 
      INTO :replay_id 
      FROM dba_workload_replays
     WHERE capture_id = :capture_id;
     
    BEGIN
        :replay_report := 
			DBMS_WORKLOAD_REPLAY.REPORT(
             	 replay_id => :replay_id
				,format => 'TEXT'
			);
    END;

END;
/
SET LONGCHUNKSIZE 1000
SET LONG 30000000
SET LINESIZE 2000
SET PAGESIZE 0
PRINT :replay_report

/* 
|| Listing 3.12:
|| Creating AWR Reports for multiple RAC instances after DBR Workload Replay
|| operations are completed
*/

DEFINE  dbid         = 646658104;
DEFINE  db_name      = 'RACDB';
DEFINE  inst_name    = 'RACDB1';
DEFINE  inst_num     = 1;
DEFINE  num_days     = 1;
DEFINE  begin_snap   = 70;
DEFINE  end_snap     = 71;
DEFINE  report_type  = 'TEXT';
DEFINE  report_name  = /home/oracle/DBR_RACDB1_AWR_70_71.log
@@?/rdbms/admin/awrrpti

DEFINE  dbid         = 646658104;
DEFINE  db_name      = 'RACDB';
DEFINE  inst_name    = 'RACDB2';
DEFINE  inst_num     = 2;
DEFINE  num_days     = 1;
DEFINE  begin_snap   = 70;
DEFINE  end_snap     = 71;
DEFINE  report_type  = 'TEXT';
DEFINE  report_name  = /home/oracle/DBR_RACDB2_AWR_70_71.log
@@?/rdbms/admin/awrrpti

/* 
|| Listing 3.13:
|| Displaying DBR Workload metadata from other data dictionary views:
|| - DBA_WORKLOAD_CAPTURES
|| - DBA_WORKLOAD_REPLAY_DIVERGENCE
|| - V$WORKLOAD_REPLAY_THREAD
*/

TTITLE 'Captured Workloads|(From DBA_WORKLOAD_CAPTURES)'
COL id                  FORMAT 999          HEADING 'Cptr|ID#'
COL name                FORMAT A16          HEADING 'Capture Name'
COL dbname              FORMAT A12          HEADING 'DB Name'
COL dbversion           FORMAT A10          HEADING 'Captured|DB Version'
COL lastprocver         FORMAT A10          HEADING 'Last|Processed|DB Version'
COL directory           FORMAT A16          HEADING 'Capture|Directory'
COL status              FORMAT A10          HEADING 'Status'
COL start_dtm           FORMAT A11          HEADING 'Start|Time' WRAP
COL end_dtm             FORMAT A11          HEADING 'End|Time' WRAP
COL capture_size        FORMAT 99999999     HEADING 'Capture|Size'
COL duration_secs       FORMAT 99999999     HEADING 'Capture|Time (s)'
COL default_action      FORMAT A12          HEADING 'Default|Action'
COL filters_used        FORMAT 9999         HEADING '# of|Fltr|Used'
SELECT
     id
    ,name
    ,dbname
    ,dbversion
    ,last_processed_version lastprocver
    ,directory
    ,status
    ,TO_CHAR(start_time,'yyyy-mm-dd hh24:mi:ss') start_dtm
    ,TO_CHAR(end_time,'yyyy-mm-dd hh24:mi:ss') end_dtm
    ,duration_secs
    ,capture_size
    ,default_action
    ,filters_used
  FROM dba_workload_captures
;
TTITLE OFF

-----
-- View:    DBA_WORKLOAD_REPLAY_DIVERGENCE
-- Purpose: Is used to monitor workload divergence
-----
TTITLE 'Workload Replay Divergence Statistics|(From DBA_WORKLOAD_REPLAY_DIVERGENCE)'
SELECT 
     replay_id
    ,timestamp
    ,divergence_type
    ,is_query_data_divergence
    ,is_dml_data_divergence
    ,is_error_divergence
    ,is_thread_failure
    ,expected_row_count
    ,observed_row_count
    ,expected_error#
    ,observed_error#
    ,stream_id
    ,call_counter
    ,sql_id
    ,session_id
    ,session_serial#
    ,service
    ,module
    ,action
  FROM dba_workload_replay_divergence
;
TTITLE OFF

-----
-- View:    V$WORKLOAD_REPLAY_THREAD
-- Purpose: Monitors the status of external replay clients
-----
TTITLE 'Current External Replay Clients Status|(From V$WORKLOAD_REPLAY_THREAD)'
COL wrc_id          FORMAT 99999        HEADING 'WRC|ID'
COL sid		        FORMAT 99999        HEADING 'SessID'
COL serial#         FORMAT 99999        HEADING 'Serial#'
COL logon_user      FORMAT A8           HEADING 'User|Login'
COL logon_dtm       FORMAT A11          HEADING 'Login|Time' WRAP
COL session_type    FORMAT A10          HEADING 'Sess|Type'
COL action_type     FORMAT 999          HEADING 'Action|Type'
COL file_name       FORMAT A24          HEADING 'Replay File Name' WRAP
COL dbtime          FORMAT 99999999     HEADING 'DB|Time'
COL network_time    FORMAT 99999        HEADING 'Ntwrk|Time'
COL think_time      FORMAT 9999999      HEADING 'Think|Time'
COL time_gain       FORMAT 999999999    HEADING 'Time|Gain'
COL time_loss       FORMAT 999999999    HEADING 'Time|Lost'
COL user_calls      FORMAT 99999        HEADING 'User|Calls'
SELECT 
     wrc_id
    ,sid
    ,serial#
    ,logon_user
    ,TO_CHAR(logon_time,'yyyy-mm-dd hh24:mi:ss') logon_dtm
    ,session_type
    ,action_type
    ,file_name
    ,dbtime
    ,network_time
    ,think_time
    ,time_gain
    ,time_loss
    ,user_calls
  FROM v$workload_replay_thread
 ORDER BY wrc_id, sid
;
TTITLE OFF