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’;

 

 

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

 

 

 

 

 

 

SparkR config with Hadoop 2.0

I am engaged on gig where sparkR will be used to run R jobs and currently  I am working on config. Once I troubleshoot all issues I will post steps to get Spark cluster working.

Followup from my initial fustration with SparkR. I was pretty close to giving up as why SparkR would not work on cluster I was working on. After much follow up with Shivaram(SparkR package author) we were finally able to get SparkR working as cluster job.

SparkR can be downloaded from https://github.com/amplab-extras/SparkR-pkg

SparkR configuration

Install R

Instruction below are for Ubuntu

ALPHA root@host:~$ nano /etc/apt/sources.list
ALPHA root@host:~$ apt-key adv --keyserver keyserver.ubuntu.com --recv-keys E084DAB9
ALPHA root@host:~$ add-apt-repository ppa:marutter/rdev
ALPHA root@host:~$ apt-get install python-software-properties
ALPHA root@host:~$ add-apt-repository ppa:marutter/rdev
ALPHA root@host:~$ apt-get update
ALPHA root@host:~$ apt-get install r-base

Conf Java for R

wget http://cran.cnr.berkeley.edu/src/contrib/rJava_0.9-6.tar.gz

sudo R CMD INSTALL rJava_0.9-6.tar.gz

Modify spark-env.sh
#!/usr/bin/env bash

export STANDALONE_SPARK_MASTER_HOST=hostname.domain.com

export SPARK_MASTER_IP=$STANDALONE_SPARK_MASTER_HOST

export SPARK_LOCAL_IP=xxx.xxx.xxx.xxx

### Let’s run everything with JVM runtime, instead of Scala
export SPARK_LAUNCH_WITH_SCALA=0
export SPARK_LIBRARY_PATH=${SPARK_HOME}/lib
export SCALA_LIBRARY_PATH=${SPARK_HOME}/lib
export SPARK_MASTER_WEBUI_PORT=18080
export SPARK_MASTER_PORT=7077
export SPARK_WORKER_PORT=7078
export SPARK_WORKER_WEBUI_PORT=18081
export SPARK_WORKER_DIR=/var/run/spark/work
export SPARK_LOG_DIR=/var/log/spark

if [ -n “$HADOOP_HOME” ]; then
export SPARK_LIBRARY_PATH=$SPARK_LIBRARY_PATH:${HADOOP_HOME}/lib/native
fi

### Comment above 2 lines and uncomment the following if
### you want to run with scala version, that is included with the package
#export SCALA_HOME=${SCALA_HOME:-/usr/lib/spark/scala}
#export PATH=$PATH:$SCALA_HOME/bin

Note: This will need to done for worker nodes as well.

Switch user to HDFS
suhdfs
Git Clone

git clone https://github.com/amplab-extras/SparkR-pkg

Building SparkR

SPARK_HADOOP_VERSION=2.2.0-cdh5.0.0-beta-2
./install-dev.sh

Copy SparkRpkg to worker nodes

Example : scp –r SparkR-pkg hdfs@worker1:

Execute Test Job

cd SparkR-pkg/

export SPARK_HOME=/opt/cloudera/parcels/CDH/lib/spark

source /etc/spark/conf/spark-env.sh

./sparkR examples/pi.R spark://hostname.domain.com:7077

Sample results
hdfs@xxxx:~/SparkR-pkg$ ./sparkR examples/pi.R spark://xxxx.xxxxx.com:7077
./sparkR: line 13: /tmp/sparkR.profile: Permission denied
Loading required package: SparkR
Loading required package: methods
Loading required package: rJava
[SparkR] Initializing with classpath /var/lib/hadoop-hdfs/SparkR-pkg/lib/SparkR/sparkr-assembly-0.1.jar

14/02/27 16:29:09 INFO Slf4jLogger: Slf4jLogger started
Pi is roughly 3.14018
Num elements in RDD 200000
hdfs@xxxx:~/SparkR-pkg$I

Oracle 12c Cloud Control Agent new OMS repository

After working number of hours I found out that 12c does not support pointing existing agent to new OMS repository…really Sad, unfortunately there is no other way other than un-install and re-install agent…

See note from oracle below:

EM 12c : How to Point Existing 12c Agent to New OMS ? => Change Location of OMS and Repository of 12c Agent [ID 1490457.1] To BottomTo Bottom
Modified:Sep 24, 2012Type:HOWTOStatus:PUBLISHEDPriority:3
There are no commentsComments (0) Rate this document Email link to this document Open document in new window Printable Page

In this Document
Goal
Fix
References

Applies to:
Enterprise Manager Base Platform – Version 12.1.0.1.0 and later
Information in this document applies to any platform.
Goal

How to point existing 12c Agent to new 12c OMS ? Is it possible to change the location of the OMS/Repository of a 12c Agent ?
Fix

Reconfiguring a 12c Agent from one 12c OMS to an another 12c OMS, without a re-installation of the Agent, is currently not supported.

This is not possible to change the OMS/Repository location properties on 12c Agent’s side from one OMS/Repository to another new OMS/Repository,

like it was possible in EM 10g and EM 11g.

When a 12c Agent is re-configured to a new 12c OMS, OMS will not accept this Agent or its targets for monitoring.

In previous Enterprise Manager releases (10g and 11g), the targets discovery is initiated by the Agent.

The targets are discovered and first updated in the /sysman/emd/targets.xml file and then pushed to the OMS.

But in EM 12c, OMS is the source of truth. That means that the targets registration happens first at the OMS side and then the targets are pushed to the Agent in targets.xml file.

Hence, it is required to re-install 12c Agent from the new OMS when it is needed that an agent points to a new OMS.

There is an Enhancemenet request logged :

BUG:14532300 POINTING AGENT TO NEW 12C OMS

Note : Refer that document to help you to push/install an Agent from the EM 12c console :

Document:1360183.1 – Cloud Control Agent 12c Installation – How to install Enterprise Manager Cloud Control 12.1 Agent

References
BUG:14532300 – POINTING AGENT TO NEW 12C OMS
NOTE:1360183.1 – Cloud Control Agent 12c Installation – How to install Enterprise Manager Cloud Control 12.1 Agent From the EM 12c Console ?

Hadoop cluster deployment

I have successfully created multiple Hadoop clusters, the biggest hurdle i have ran into is documentation.

Document either missing key steps or due to environment differences documentation does not apply. following is list of clusters i have created:

Hadoop Cloudera single node Master/Datanode
Apache Hadoop manual install by downloading pkg’s
Apache Hadoop CDH4 3 node cluster.
Apache Hadoop CDH4 7 node cluster.

I’d like to hear what other people have to say about their experience.