因為備庫的資料與主庫完全一樣且只讀，所以備庫無法收集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;

格式為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);

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

相關文章