監控備庫效能,為Active DataGuard的備庫生成statspack報告並實現定時傳送

db_wjw發表於2016-06-14
因為備庫的資料與主庫完全一樣且只讀,所以備庫無法收集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.sql指令碼來新增備庫例項:
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使用者下面。

6、刪除snapshot
$ sqlplus stdbyperf/bbb;
SQL> @?/rdbms/admin/sbpurge.sql

二、配置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

四、定時刪除statspack歷史資料的指令碼:
vi /home/oracle/scripts/del_sp_his_snapshot_for_nicaifub.sh
內容:
. /home/oracle/.bash_profile
export DB_UNIQUE_NAME=nicaifub
export INSTANCE_NAME=nicaifu
export DATE=`date '+%Y%m%d'`

echo $DATE

lowid=`sqlplus -s / as sysdba <<EOF
set pagesize 0 feedback off verify off heading off echo off
select min(snap_id) from stdbyperf.stats\\$snapshot;
exit;
EOF`

highid=`sqlplus -s / as sysdba <<EOF
set pagesize 0 feedback off verify off heading off echo off
select max(snap_id)-24 from stdbyperf.stats\\$snapshot;
exit;
EOF`

echo $lowid
echo $highid

sqlplus stdbyperf/statspack_1qaz2wsx <<EOF
@$ORACLE_HOME/rdbms/admin/sbpurge.sql
$DB_UNIQUE_NAME
$INSTANCE_NAME
$lowid
$highid
exit;
EOF

chmod u+x /home/oracle/scripts/del_sp_his_snapshot_for_nicaifub.sh
定時任務crontab -e
10 03 * * * sh /home/oracle/scripts/del_sp_his_snapshot_for_nicaifub.sh >> /home/oracle/log/del_sp_his_snapshot_for_nicaifub.log




附:
執行job:
SQL> exec dbms_job.run(4);
停止job:
SQL> exec dbms_job.broken(4,true);
刪除job:
SQL> dbms_job.remove(4);

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26194851/viewspace-2120135/,如需轉載,請註明出處,否則將追究法律責任。

相關文章