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                                                        ##

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

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