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

Oracle RAT Step by Step -Part 3

Replay workload on target database – Part 1

Restore Target database

 

RMAN or export/import can be used to stage target/replay database. To restore database using RMAN please refer to Oracle RMAN documentation.

Drop Db_links

 

Drop db_links that may be pointing to external systems or re-point db_links to test systems.

set lines 150

col db_link for a40

select OWNER,DB_LINK,USERNAME,to_char(CREATED, ‘MM/DD/YYYY HH24:MI:SS’) as CREATED from dba_db_links;

 

–generate drop statement for public database links

sqlplus -s / as sysdba <<EOF

set feedback 0 pages 0 lines 132 echo on timing off

set echo off

set term off

spool /tmp/drop_db_links.sql

 

select ‘DROP ‘||OWNER||’ DATABASE LINK ‘||DB_LINK||’ ; ‘ from dba_db_links where OWNER=’PUBLIC’;

 

spool off

EOF

 

Note: If db_links can’t be dropped using “drop database link” use below.

 

Check sys db_link tables –

select * from sys.link$;

select db_link, username, host from user_db_links;

 

Delete records manually –

delete from sys.link$;

commit;

 

Gather Schema Stats

 

BEGIN

DBMS_STATS.gather_schema_stats (ownname => ‘SCHEMA1’ ,

cascade =>true,

estimate_percent => dbms_stats.auto_sample_size,

method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,

degree => 8);

END;

/

 

 

BEGIN

DBMS_STATS.gather_schema_stats (ownname => ‘SCHEMA2’ ,

cascade =>true,

estimate_percent => dbms_stats.auto_sample_size,

method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,

degree => 8);

END;

/

 

 

 

 

BEGIN

DBMS_STATS.gather_schema_stats (ownname => ‘SCHEMA3’,

cascade =>true,

estimate_percent => dbms_stats.auto_sample_size,

method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,

degree => 8);

END;

/

 

 

Create Flashback Restore Point

 

To replay capture data multiple times, Oracle database flashback will allow us to restore database to SCN # without having to use RMAN restore.

—Check for existing restore point

 

set linesize 121

col name format a15

col time format a32

 

SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size

FROM gv$restore_point;

–CREATE GUARANTEED RESTORE POINT

 

 

CREATE RESTORE POINT <RESTORE POINT NAME> GUARANTEE FLASHBACK DATABASE;

 

Example:

 

CREATE RESTORE POINT PRE_REPLAY_RAT GUARANTEE FLASHBACK DATABASE;

 

 

 

 

 

 

 

Create Directory

 

–Check the Directory

 

set lines 1024;

col DIRECTORY_PATH for a90

 

select * from dba_directories;

 

—CREATE RAT REPLAY DIRECTORY

 

 

create or replace directory <DIRECTORY NAME> as ‘<DIRECTORY LOCATION>’;

 

example : create or replace directory REPLAY_TGT_1 as ‘/export/home/oracle/rat/traget_db’;