oracle awrdiff,测试AWR EXTRACT&AWR Baseline

AWR EXTRACT&AWR Baseline

1.AWR EXTRACT(生产数据库)

--查询是否创建导出目录，如没有可自行创建一个

create or replace directory IMP_PATH as '/u01/replay';

select DIRECTORY_NAME, DIRECTORY_PATH from

dba_directories;

--查询现有快照点

select min(SNAP_ID) from

sys.wrh$_active_session_history;

select * from dba_hist_active_sess_history ；

--导出快照点11100至11106的性能监视

exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(DMPFILE

=>'awr_data_cqabdt0',DMPDIR =>'IMP_PATH',BID =>11100,EID =>11106 );

or

@$ORACLE_HOME/rdbms/admin/awrextr.sql

2.AWR 导入(测试数据库)

select DIRECTORY_NAME, DIRECTORY_PATH from

dba_directories;

exec

DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME =>'TEST',dmpfile

=>'awr_data_cqabdt0',dmpdir =>'IMP_PATH');

exec

DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');

3.AWR Baseline(测试数据库)

set pagesize 0

set linesize 32767

select * from

table(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(1273272014,1,11100,11102,1273272014,1,11103,11104));

或者

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

其他参考

####

#

#/bin/sh

result=`sqlplus -s system@xitest01 << EOF

set pagesize 0

set linesize 32767

select *

from

table(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(1273272014,1,11100,11102,1273272014,1,11103,11104));

EOF`

echo

"ok1"

echo

"$result" > awr_baseline_html.html

#通过sh脚本导出html静态网页

#

#

#

###############

##        BEGIN

##                DBMS_SWRF_INTERNAL.AWR_EXTRACT

(DMPFILE => 'awr_data.dmp',

##                DMPDIR

=> 'DB_DIR', -- Database directory

##                BID

=> 4283, -- Begin snapshot

##                EID

=> 4315 -- End snapshot

##                );

##        END;

##        /

##

##        BEGIN

##                DBMS_SWRF_INTERNAL.AWR_LOAD

(SCHEMA => 'MYSCHEMA',

##                DMPFILE

=> 'awr_data.dmp',

##                DMPDIR

=> 'DB_DIR', -- Database directory );

##        END;

##        /

##

##        BEGIN

##                DBMS_SWRF_INTERNAL.MOVE_TO_AWR

(SCHEMA => 'MYSCHEMA');

##        END;

##        /

###############