ITPub博客

首页 > 数据库 > Oracle > 为Active DataGuard的备库生成statspack报告并实现定时发送

为Active DataGuard的备库生成statspack报告并实现定时发送

原创 Oracle 作者:db_wjw 时间:2015-10-28 15:58:10 0 删除 编辑
因为备库的数据与主库完全一样且只读,所以备库无法收集AWR报告,在备库上收集到的AWR其实是主库的数据信息。针对需要监控备库的性能这种情况,11g提供了一种办法,可以收集备库的statspack。
本文说明如何配置statspack并实现定时通过邮件发送报表到指定人员,下面为详细步骤:
注意,下面所有操作都是在主库上执行:

一、安装配置statspack
1、创建单独的表空间
$ sqlplus / as sysdba
SQL> create tablespace statspack '/oracle/data/statspack01.dbf' size 500m autoextend on maxsize unlimited;

2、安装statspack用户和对象
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/spcreate.sql
输入PERFSTAT用户的密码,这里为aaa
默认表空间为sysaux,可以指定为statspack表空间
指定临时表空间为temp

SQL> @?/rdbms/admin/sbcreate.sql
输入STDBYPERF用户的密码,这里为bbb
默认表空间为sysaux,可以指定为statspack表空间
指定临时表空间为temp

3、添加备库实例
在运行sbcreate.sql脚本时,创建完对象时会自动调用/rdbms/admin/sbaddins脚本来添加备库实例:
THE INSTANCE YOU ARE GOING TO ADD MUST BE ACCESSIBLE AND OPEN READ ONLY

Do you want to continue (y/n) ?
Enter value for key: y
You entered: y
需要输入standby数据库在主库中的tns的名称:
Enter the TNS ALIAS that connects to the standby database instance
-----------------------------------------------------------------
Make sure the alias connects to only one instance (without load balancing).
Enter value for tns_alias: orclb

需要输入standby数据库中perfstat用户的密码:
Enter the PERFSTAT user's password of the standby database
---------------------------------------------------------
Performance data will be fetched from the standby database via
database link. We will connect to user PERFSTAT.
Enter value for perfstat_password: aaa

4、收集快照:
$ sqlplus stdbyperf/bbb;
SQL> exec statspack_orclb_orcl.snap;
SQL> exec statspack_orclb_orcl.snap;
格式为statspack_(db_unique_name)_(instance_name).snap

5、生成报表:
SQL> @?/rdbms/admin/sbreport.sql

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Unique Name                 Instance Name
------------------------------ ----------------
orclb                 orcl

Enter the DATABASE UNIQUE NAME of the standby database to report
Enter value for db_unique_name: orclb
You entered: orclb

Enter the INSTANCE NAME of the standby database instance to report
Enter value for inst_name: orcl
You entered: orcl

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                           Snap
Instance       Snap Id     Snap Started     Level Comment
------------ --------- ----------------- ----- --------------------
orcl           1 27 Oct 2015 18:38     5
               2 27 Oct 2015 18:46     5



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sb_orclb_orcl_1_2.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /tmp/sb_orclb_orcl_1_2


所有收集到的数据会存储在stdbyperf用户下面。

二、配置statspack定时收集snapshot
1、以使STATSPACK每小时收集一次备库的snapshot为例说明

在/tmp目录下创建sbauto.sql脚本,内容如下(从$ORACLE_HOME/rdbms/admin/spauto.sql脚本改编而来):
spool sbauto.lis

variable jobno number;
variable instno number;
begin
  select 1 into :instno from dual;
  dbms_job.submit(:jobno, 'statspack_orclb_orcl.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;
/

prompt  the job:
print jobno

prompt  The next scheduled run for this job is:
select job, next_date, next_sec
  from user_jobs
where job = :jobno;

spool off;

2、执行脚本:
在主库上使用stdbyperf用户登录
$ sqlplus stdbyperf/bbb;
SQL> @/tmp/sbauto.sql

3、查询任务是否创建成功
$ sqlplus stdbyperf/bbb;
SQL> select job,log_user,priv_user,schema_user,next_date,next_sec from user_jobs;



三、设置定时产生报表并发送:
1、创建生成报表并发送的脚本:
改脚本范例为当天12点到14点的statspack报告。
$ vi /home/oracle/scripts/get_statspack.sh
内容如下:
. /home/oracle/.bash_profile
LOGDIR=/u02/monitor/log
date=`date '+%Y%m%d'`
filename=statspack_$date.txt
db_unique_name=orclb
inst_name=orcl

minid=`sqlplus -s / as sysdba <<EOF
set pagesize 0 feedback off verify off heading off echo off
select min(snap_id) from stdbyperf.stats\\$snapshot where snap_time between trunc(Sysdate)+12/24 And trunc(Sysdate)+14/24;
exit;
EOF`

maxid=`sqlplus -s / as sysdba <<EOF
set pagesize 0 feedback off verify off heading off echo off
select max(snap_id) from stdbyperf.stats\\$snapshot where snap_time between trunc(Sysdate)+12/24 And trunc(Sysdate)+14/24;
exit;
EOF`

sqlplus -S stdbyperf/bbb <<EOF
@$ORACLE_HOME/rdbms/admin/sbreport.sql
$db_unique_name
$inst_name
$minid
$maxid
$LOGDIR/statspack_$date.txt
exit;
EOF

mutt -s "statspack daily report" xxx@163.com -a $LOGDIR/$filename

加上执行权限:
chmod u+x /home/oracle/scripts/get_statspack.sh


2、创建定时任务:
$ crontab -e
30 15 * * * sh /home/oracle/scripts/get_statspack.sh >> /home/oracle/log/get_statspack.log

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26194851/viewspace-1817451/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-21

  • 博文量
    96
  • 访问量
    452142