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