Oracle RAT Step by Step -Part 3

Replay workload on target database – Part 2

 

Prepare Capture data

Copy Capture data

Copy the capture files from source system(Production) to the directory created in above step.

 

Preprocessing Capture data

 

 

BEGIN

dbms_workload_replay.process_capture ( capture_dir => ‘<REPLAY_DIR_NAME_WHERE_CAPTURE_FILES_BEEN_STAGED>’ );

END;

/

 

Example:

BEGIN

dbms_workload_replay.process_capture ( capture_dir => ‘REPLAY_TGT_1’ );

END;

/

 

— Monitor time to process data –-

 

The statements below will give EST time remaining, and data set size in MB which has been processed. The statements below and only informational.

 

 

SELECT dbms_workload_replay.process_capture_remaining_time FROM dual;

SELECT dbms_workload_replay.process_capture_completion FROM dual;

select sum(bytes_done/1024/1024/1024) as GB from wrr$_processing_progress;

 

 

example Output:

 

SQL> SELECT dbms_workload_replay.process_capture_remaining_time FROM dual;

 

PROCESS_CAPTURE_REMAINING_TIME

——————————

5.55503472

 

 

SQL> SELECT dbms_workload_replay.process_capture_completion FROM dual;

 

PROCESS_CAPTURE_COMPLETION

————————–

4.47761194

 

In Oracle 11.2.0.3 the PROCESS_CAPTURE procedure creates a new subdirectory called pp11.2.0.3.0 in the replay directory containing the following files:

  • wcr_calibrate.html
  • wcr_commits.extb
  • wcr_conn_data.extb
  • wcr_data.extb
  • wcr_dep_graph.extb
  • wcr_login.pp
  • wcr_process.wmd
  • wcr_references.extb
  • wcr_scn_order.extb
  • wcr_seq_data.extb

 

 

Work Load analyzer

 

Java program that analyses a workload capture directory

java -classpath $ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/rdbms/jlib/dbrparser.jar:$ORACLE_HOME/rdbms/jlib/dbranalyzer.jar: oracle.dbreplay.workload.checker.CaptureChecker <OS location of Capture Staged files> jdbc:oracle:thin:@<Hostname>:<Listener Port>:<Service_name/SID>

 

 

Example:

java -classpath $ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/rdbms/jlib/dbrparser.jar:$ORACLE_HOME/rdbms/jlib/dbranalyzer.jar: oracle.dbreplay.workload.checker.CaptureChecker /export/home/oracle/rat/target_db jdbc:oracle:thin:@hostname1-vip:1521:TARGET

 

—Results are stored in capture directory in the following files:

<REP_DIR>/wcr_cap_analysis.html

<REP_DIR>/wcr_cap_analysis.xml

–END—

Initialize Replay

 

 

–Get Capture name

 

set lines 150

col NAME for a45

select id, NAME, DBNAME, DBVERSION, STATUS, to_char(START_TIME, ‘MM/DD/YYYY HH24:MI:SS’) as START_TIME , to_char(END_TIME, ‘MM/DD/YYYY HH24:MI:SS’) as END_TIME from DBA_WORKLOAD_CAPTURES

;

BEGIN

dbms_workload_replay.initialize_replay ( replay_name => ‘<REPLAY_NAME>’,

replay_dir => ‘<REPLAY_DIRECTORY_NAME>’ );

END;

/

 

Example:

BEGIN

dbms_workload_replay.initialize_replay ( replay_name => ‘REPLAY_QECMTRP_1’,

replay_dir => ‘REPLAY_TGT_1’ );

END;

/

 

—Known to populate the following base tables:

WRR$_REPLAYS

WRR$_CONNECTION_MAP

–END—

Generate Re-Map Connection

 

–get connection info

set pages 100

set lines 150

col CAPTURE_CONN for a50

col REPLAY_CONN for a50

spool save_pre_remap.out

select * from dba_workload_connection_map;

spool off

 

— SQL will generate re-map connection SQL which will need to be executed to connect to REPLAY HOST —

Warning: This is important steps and verification must be done before proceeding to next step.

 

 

 

set lines 189

set echo off

set head off

set term off

set long 200

set feedback off

spool /tmp/remap_replay_connection.sql

SELECT ‘EXEC dbms_workload_replay.remap_connection (connection_id=>’||a.conn_id||’,replay_connection => ”(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=<TARGET DB LISTENER HOST>)(PORT=<LISTENER-PORT>)) (CONNECT_DATA=(SID=<DB SID/SERVICE NAME>)))”);’

FROM dba_workload_connection_map a, dba_workload_replays b WHERE a.replay_id = b.id AND b.status =’INITIALIZED’ ORDER BY a.conn_id;   spool off

 

 

Example:

 

 

Sqlplus –s

set lines 250

set echo off

set head off

set term off

set long 2000

set feedback off

spool /tmp/remap_replay_connection.sql

SELECT ‘EXEC dbms_workload_replay.remap_connection (connection_id=>’||a.conn_id||’,replay_connection => ”(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=Hostname1-vip)(PORT=1521)) (CONNECT_DATA=(SID=TARGET1)))”);’

FROM dba_workload_connection_map a, dba_workload_replays b WHERE a.replay_id = b.id AND b.status =’INITIALIZED’ ORDER BY a.conn_id;

spool off

exit;

 

 

 

Execute Re-Map connection script

In this step execute script that was generated above and verify the results

Execute remap script

SQL> @/tmp/remap_replay_connection.sql

 

Validate Replay Connections

  • –Save Post re-map connection info

 

set pages 100

set lines 150

col CAPTURE_CONN for a50

col REPLAY_CONN for a50

spool ${ORACLE_SID}_post_remap_connection.out

select * from dba_workload_connection_map;

spool off

 

  • –Get Count of null connections

 

select count(*) from dba_workload_connection_map where REPLAY_CONN is null;

 

–if count is > 0 then, generate new connection re-map script and run again

 

 

  • –Get count by connection name and status

 

set pages 100

set lines 180

col CAPTURE_CONN for a50

col REPLAY_CONN for a50

SELECT count(*) , a.REPLAY_CONN, a.REPLAY_ID , b.NAME, b.STATUs

FROM dba_workload_connection_map a, dba_workload_replays b WHERE a.replay_id = b.id group by a.REPLAY_CONN, a.REPLAY_ID , b.NAME,b.STATUs;

 

set pages 100

set lines 150

col CAPTURE_CONN for a50

col REPLAY_CONN for a50

select count(*) , REPLAY_CONN, REPLAY_ID from dba_workload_connection_map group by REPLAY_CONN, REPLAY_ID ;

Advertisements

Author: Abdul H Khan

Trying to be cloudy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s