oracle wcr采集

1背景
某客户的现场oracle数据库迁移到国产数据库Goldendb,需要在Goldendb数据库进行性能测试，于是考虑使用RAT中的Database Replay，模拟现网的运行，来发现数据迁移、兼容性、性能等各种问题。
2RAT介绍
Oracle Real Application Testing（简称RAT）是oracle的一个重要的feature，其推出的初衷是为了满足数据中心变更后，有很好的方法和工具去衡量这些变更对于生产环境的应用带来的影响，更好地评估诸如硬件升级、软件升级、架构变化等等对于客户应用程序的影响。
Real Application Testing其实有两个解决方法，分别是Database Replay和SPA（SQL Performance Analyzer）。以前有用过SPA就不做介绍了，本次主要介绍RAT的使用以及我在使用过程中遇到的问题。
Database Replay，数据库回放顾名思义可以理解为一个录像机，通过在源系统上把实时应用产生的负载录制下来，并拿到变更后的环境进行播放，从而进行一个对比。数据库回放分为四个阶段完成：录制、预处理、回放、结果分析和报告
3RAT测试过程
3.1确定wcr采集数据库信息
根据业务侧的需求对相应的数据库以及对应用户进行采集，以下为例子。
数据库名称：pdborcl1
用户：CHNL、INF、ODSSTAT、BOSS、INFECHANNEL、ETL、EDP
3.2wcr采集文件共享目录挂载
首先创建一个操作系统目录，用于存放WCR录制的文件，由于是RAC环境，因此要求是一个4节点共享的目标，例如我们这里用的是NFS目录挂接到4个节点上，以下操作命令需要使用root用户执行：
1.创建共享盘目录
mkdir -p /wcr
2.挂载nfs共享盘，根据现场提供情况来执行
mount -t nfs -o vers=3,timeo=600,nolock 10.x.x.x: /QDJZH-300G /wcr
3.授权共享盘目录给oracle用户使用
chow\n -R oracle:oinstall /wcr

3.3创建RAT文件目录以及数据库目录
3.3.1服务器创建目录操作
SQL> with t as (select substr(to_char(to_date('09:00','hh24:mi')+(rownum-1)*1/24,'hh24mi'),1,2) i from dual connect by rownum<=24) select 'mkdir -p /wcr/rat/rat'||i from t;
输出批量执行结果在服务器上面执行：
mkdir -p /wrc/rat/rat01
mkdir -p /wrc/rat/rat02
mkdir -p /wrc/rat/rat03
mkdir -p /wrc/rat/rat04
mkdir -p /wrc/rat/rat05
mkdir -p /wrc/rat/rat06
mkdir -p /wrc/rat/rat07
mkdir -p /wrc/rat/rat08
mkdir -p /wrc/rat/rat09
mkdir -p /wrc/rat/rat10
mkdir -p /wrc/rat/rat11
mkdir -p /wrc/rat/rat12
mkdir -p /wrc/rat/rat13
mkdir -p /wrc/rat/rat14
mkdir -p /wrc/rat/rat15
mkdir -p /wrc/rat/rat16
mkdir -p /wrc/rat/rat17
mkdir -p /wrc/rat/rat18
mkdir -p /wrc/rat/rat19
mkdir -p /wrc/rat/rat20
mkdir -p /wrc/rat/rat21
mkdir -p /wrc/rat/rat22
mkdir -p /wrc/rat/rat23
mkdir -p /wrc/rat/rat00
3.3.2数据库创建目录以及赋权
SQL> with t as (select substr(to_char(to_date('09:00','hh24:mi')+(rownum-1)*1/24,'hh24mi'),1,2) i from dual connect by rownum<=24) select 'create directory rat'||i||' as ''/wcr/rat/rat'||i||''';' directory from t union all select 'grant read,write on directory rat'||i||' to public;' from t;
输出结果：
create directory rat10 as '/wrc/rat/rat10';
create directory rat11 as '/wrc/rat/rat11';
create directory rat12 as '/wrc/rat/rat12';
create directory rat13 as '/wrc/rat/rat13';
create directory rat14 as '/wrc/rat/rat14';
create directory rat15 as '/wrc/rat/rat15';
create directory rat16 as '/wrc/rat/rat16';
create directory rat17 as '/wrc/rat/rat17';
create directory rat18 as '/wrc/rat/rat18';
create directory rat19 as '/wrc/rat/rat19';
create directory rat20 as '/wrc/rat/rat20';
create directory rat21 as '/wrc/rat/rat21';
create directory rat22 as '/wrc/rat/rat22';
create directory rat23 as '/wrc/rat/rat23';
create directory rat00 as '/wrc/rat/rat00';
create directory rat01 as '/wrc/rat/rat01';
create directory rat02 as '/wrc/rat/rat02';
create directory rat03 as '/wrc/rat/rat03';
create directory rat04 as '/wrc/rat/rat04';
create directory rat05 as '/wrc/rat/rat05';
create directory rat06 as '/wrc/rat/rat06';
create directory rat07 as '/wrc/rat/rat07';
create directory rat08 as '/wrc/rat/rat08';
create directory rat09 as '/wrc/rat/rat09';

grant read,write on directory rat10 to public;
grant read,write on directory rat11 to public;
grant read,write on directory rat12 to public;
grant read,write on directory rat13 to public;
grant read,write on directory rat14 to public;
grant read,write on directory rat15 to public;
grant read,write on directory rat16 to public;
grant read,write on directory rat17 to public;
grant read,write on directory rat18 to public;
grant read,write on directory rat19 to public;
grant read,write on directory rat20 to public;
grant read,write on directory rat21 to public;
grant read,write on directory rat22 to public;
grant read,write on directory rat23 to public;
grant read,write on directory rat00 to public;
grant read,write on directory rat01 to public;
grant read,write on directory rat02 to public;
grant read,write on directory rat03 to public;
grant read,write on directory rat04 to public;
grant read,write on directory rat05 to public;
grant read,write on directory rat06 to public;
grant read,write on directory rat07 to public;
grant read,write on directory rat08 to public;
grant read,write on directory rat09 to public;
3.4创建自动采集脚本
[oracle@19c wcr]$ v\i wcr.sh
#!/bin/sh
if [[ -f ~/.profile ]]
then
. ~/.profile
fi

if [[ -f ~/.bash_profile ]]
then
. ~/.bash_profile
fi
STTIME=`date "+%Y-%m-%d %H:%M:%S"`
echo 'current time is :' $STTIME
sqlplus -s / as sysdba <<EOF >>/wrc/rat/rat.log
alter session set container=pdborcl1;
set echo on
set serveroutput on
exec dbms_workload_capture.finish_capture;
exec dbms_workload_capture.add_filter(fname =>'FILTER_CHNL',fattribute => 'USER',fvalue => 'CHNL');
exec dbms_workload_capture.add_filter(fname =>'FILTER_ETL',fattribute => 'USER',fvalue => 'ETL');
exec dbms_workload_capture.add_filter(fname =>'FILTER_EDP',fattribute => 'USER',fvalue => 'EDP');
exec dbms_workload_capture.add_filter(fname =>'FILTER_ODSSTAT',fattribute => 'USER',fvalue => 'ODSSTAT');
exec dbms_workload_capture.add_filter(fname =>'FILTER_TNFBI',fattribute => 'USER',fvalue => 'INF');
exec dbms_workload_capture.add_filter(fname =>'FILTER_INFECHANNEL',fattribute => 'USER',fvalue => 'INFECHANNEL');
exec dbms_workload_capture.add_filter(fname =>'FILTER_BOSS',fattribute => 'USER',fvalue => 'BOSS');
DECLARE
n varchar2(10);
BEGIN
select substr(TO_CHAR(SYSDATE, 'HH24:MI:SS'),1,2) into n from dual;
dbms_output.put_line(n);
dbms_workload_capture.start_capture(name => 'GB_20241126_'||n, dir => 'RAT'||n, default_action=>'EXCLUDE', duration => 3600);
END;
/
EOF

脚本参数说明：
fname是本次捕捉任务的名称；
fattribute是固定USER；
fvalue是本次捕捉用户名
duration是捕捉时间，上面设置了3600，也就是捕捉1小时，该参数可以设置为NULL，就是无时间限制地录制，直到发起停止命令。

3.5添加定时
#本次任务计划每小时执行一次，抓取整整一天的报告
[oracle@19c wcr]$ crontab -l
0 0-23 * * * sh /wcr/wcr.sh >> /wcr/wcr..log


3.6数据库查看采集状态
SQL>set pagesize 300
set linesize 200
col NAME for a20
col dir_Path for a30
col status for a30
select id,name,status,start_time,end_time,connects,user_calls,dir_path,(sysdate-START_TIME)*3600*24fromdba_workload_captures;
3.7手动停止采集信息
SQL>exec dbms_workload_capture.finish_capture;

3.8手动删除采集信息
#id根据实际查询结果取值
SQL>exec dbms_workload_capture.delete_capture_info(id);