Oracle RAT Step by Step -Part 2

Oracle RAT – Step by Step – Part 2

 

Step Source database for capture.

 

Steps:

 

Create Directory

 

–Check for existing dir’s

 

set lines 1024;col DIRECTORY_PATH for a90

 

select * from dba_directories;

 

exit;

 

In this step, directory is created to store capture transactions that will stored while capture is runing

 

 

 

create or replace directory <CAPTURE_DIRECTORY> as ‘<Directory_Filesystem_Location>’;

 

 

Example:

 

create or replace directory CAPTURE_DIRECTORY as ‘/backup/oracle/CAPTURE’;

Current Time and SCN #

 

ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH:MI:SS’;

select sysdate from dual;

 

Example:

 

SQL> ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH:MI:SS’;

 

Session altered.

 

SQL> select sysdate from dual;

 

SYSDATE

——————-

2014-03-25 12:44:12

 

SQL> SELECT name, TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

 

NAME     TO_CHAR(CURRENT_SCN)

——— —————————————-

RPSTST   10863666225560

 

Capture SCN and start time can also be gathered after the capture workload report is created.

 

 

 

 

Start Capture

 

Backup

 

If there is existing level 0 backup prior to starting capture, we will only need to take backup of archivelogs backup. Otherwise Level 0 backup will need to be taken.

Note: If Level 0 backup is not taken, take a backup before the capture process starts to eliminate RMAN backup noise. Otherwise capture will track RMAN activity.

 

 

connect target;connect catalog catalog_user/catalog_password@<Catalog_Database>

run {

backup archivelog all not backed up delete all input;

}

 

Example:

connect target;

connect catalog rcat_user/<password>@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname1)(PORT=1521)))(CONNECT_DATA=(SID=catalog_database_sid)(SERVER=DEDICATED)))

run {

backup archivelog all not backed up delete all input;

}

Capture

 

 

EXEC DBMS_WORKLOAD_CAPTURE.START_CAPTURE(name=>'<CAPTURE_NAME>’,dir=>'<CAPTURE_DIRECTORY>’, duration=><TIME_IN_SECS>); 

Example:

 

EXEC DBMS_WORKLOAD_CAPTURE.START_CAPTURE(name=>’CAPTURE_RPSTST_1′,dir=>’CAPTURE_DIRECTORY’, duration=>10800);

 

 

 

Monitor Capture

 

Monitor Capture progress 

 

–Time remaining for capture

select dbms_workload_replay.process_capture_remaining_time from dual;

 

 

–Capture ID

 

SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info(‘CAPTURE_DIRECTORY’) FROM     dual;

 

COLUMN name FORMAT A30

SELECT id, name FROM dba_workload_captures;

 

 

–Get status of capture with incomplete:

 

select id, NAME, DBNAME, DBVERSION, STATUS, START_TIME from DBA_WORKLOAD_CAPTURES

where STATUS <> ‘COMPLETED’;

 

Get status of All capture:

 

Set lines 150

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

;

 

Connection count:

For RAC:

select count(*) , S.status, S.INST_ID, G.INSTANCE_NAME from gV$SESSION S, gV$instance G where S.INST_ID=G.INST_ID   group by S.status, S.INST_ID, G.INSTANCE_NAME;

For single instance:

select count(*) , status, INST_ID from gV$SESSION group by status, INST_ID;

 

select dbms_workload_replay.process_capture_remaining_time from dual;

 

 

 

 

 

 

 

 

 

 

 

 

Capture Reports

 

Generate HTML Capture report

–Get Capture ID

 

select id, name from dba_workload_captures where name='<CAPTURE_NAME>’;

 

Example:

SQL> select id, name from dba_workload_captures where name=’CAPTURE_RPSTST_1′;

 

ID NAME

———- ——————————

52 CAPTURE_RPSTST_1

 

 

 

DECLARE

l_report   CLOB;

BEGIN

l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id =><CAPTURE_ID>,

format     => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);

END;

/

 

 

Example:

DECLARE

l_report   CLOB;

BEGIN

l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 52,

format     => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);

END;

/

 

 

Step above will generate HTML report for capture from start to end in “cap” sub directory

 

<CAPTURE_DIR> à CAP à wcr_cr.html

 

Sample wcr_capture report for RPSTST

Export AWR snapshot

–Get Capture ID

 

select id, name from dba_workload_captures where name='<CAPTURE_NAME>’;

 

Example:

SQL> select id, name from dba_workload_captures where name=’CAPTURE_RPSTST_1′;

 

ID NAME

———- ——————————

52 CAPTURE_RPSTST_1

 

 

–Export AWR Data –

 

BEGIN

DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => <CAPTURE_ID>);

END;

/

 

Example:

BEGIN

DBMS_WORKLOAD_CAPTURE.export_awr (capture_id =>52);

END;

/

 

 

Step above will generate awr dmp file for capture in “cap” sub directory

 

  • wcr_ca.dmp – Data Pump export
  • wcr_ca.log – Data Pump log file
  • wcr_cap_uc_graph.extb – User calls graph

 

 

 

##—————————————————————–##

##               End of Capture                                                        ##

##—————————————————————–##

Oracle RAT Step by Step

Oracle RAT – Step by Step – Part 1

 

I am currently working on gig where Oracle RAT will be system stress test tool to simulate production load.  Oracle RAT is multi step process and it consist of following.

Here is breakdown on Oracle RAT from my experience.

  • Identify system workload – Source
  • Baseline backup of Source environment
  • Staging capture directory and making sure there is plenty of space
  • Starting Capture process and monitoring capture/database while it is running
  • Generate and export capture workload
  • Identify target system
  • Moving capture transactions file to target system
  • Processing capture data
  • Re-Map target connections for replay
  • Start Replay and monitor database load – Active Session History and SQL monitoring reports can be used
  • Generate reports and analysis of replay