ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用存储过程封装awrrpt脚本(四)

用存储过程封装awrrpt脚本(四)

原创 Linux操作系统 作者:yangtingkun 时间:2011-05-06 23:59:03 0 删除 编辑

做了一个存储过程,封装了awrreport的功能。

增加11.2的新功能,如果当前数据库是11.2以上版本,会自动将两个实例的报告生成到一个GLOBAL REPORT中。同时修正了输入结束时间为当前数据库时间时,出现NO_DATA_FOUND错误。

用存储过程封装awrrpt脚本(一):http://yangtingkun.itpub.net/post/468/515180

用存储过程封装awrrpt脚本(二):http://yangtingkun.itpub.net/post/468/515229

用存储过程封装awrrpt脚本(三):http://yangtingkun.itpub.net/post/468/515271

 

 

修改后的过程如下:

[oracle@xsh-server1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 5 13:55:11 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options

SQL> CREATE OR REPLACE PROCEDURE P_AWR_REPORT (
  2   P_BEGIN IN VARCHAR2,
  3   P_END IN VARCHAR2,
  4   P_DIR IN VARCHAR2,
  5   P_DBNAME IN VARCHAR2 DEFAULT '',
  6   P_PERINTERVAL IN BOOLEAN DEFAULT FALSE) AS
  7   TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER;
  8   V_REPORT T_VARCHAR;
  9   V_FILE UTL_FILE.FILE_TYPE;
 10   TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 11   TYPE T_DATE IS TABLE OF DATE INDEX BY BINARY_INTEGER;
 12   V_INSTANCE T_NUM;
 13   V_SNAP_BEG T_NUM;
 14   V_SNAP_END T_NUM;
 15   V_BEGIN_DATE T_DATE;
 16   V_END_DATE T_DATE;
 17   V_BEGIN NUMBER;
 18   V_END NUMBER;
 19   V_DBID NUMBER;
 20   V_DBNAME V$DATABASE.NAME%TYPE;
 21   V_VERSION V$VERSION.BANNER%TYPE;
 22  BEGIN
 23 
 24   IF P_DBNAME IS NOT NULL THEN
 25    SELECT DISTINCT DBID, DB_NAME
 26    INTO V_DBID, V_DBNAME
 27    FROM DBA_HIST_DATABASE_INSTANCE
 28    WHERE DB_NAME = P_DBNAME;
 29   ELSE
 30    SELECT DBID, NAME
 31    INTO V_DBID, V_DBNAME
 32    FROM V$DATABASE;
 33   END IF;
 34  
 35   SELECT DISTINCT INSTANCE_NUMBER,
 36    FIRST_VALUE(SNAP_ID) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_BEGIN, 'YYYYMMDDHH24MISS'))),  
 37    FIRST_VALUE(END_INTERVAL_TIME) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_BEGIN, 'YYYYMMDDHH24MISS')))
 38   BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG, V_BEGIN_DATE
 39   FROM DBA_HIST_SNAPSHOT
 40   WHERE DBID = V_DBID
 41   ORDER BY INSTANCE_NUMBER;
 42   
 43   FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
 44    BEGIN
 45     SELECT DISTINCT FIRST_VALUE(SNAP_ID) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_END, 'YYYYMMDDHH24MISS'))),
 46     FIRST_VALUE(END_INTERVAL_TIME) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_END, 'YYYYMMDDHH24MISS')))
 47     INTO V_SNAP_END(I), V_END_DATE(I)
 48     FROM DBA_HIST_SNAPSHOT
 49     WHERE SNAP_ID > V_SNAP_BEG(I)
 50     AND DBID = V_DBID
 51     AND INSTANCE_NUMBER = V_INSTANCE(I);
 52    EXCEPTION
 53     WHEN NO_DATA_FOUND THEN
 54      V_INSTANCE.DELETE(I);
 55    END;
 56   END LOOP;
 57 
 58   IF V_INSTANCE.COUNT = 1
 59   THEN
 60     V_BEGIN := V_SNAP_BEG(1);
 61    IF P_PERINTERVAL = FALSE
 62     THEN
 63      V_END := V_SNAP_END(1);
 64     ELSE
 65      V_END := V_SNAP_BEG(1) + 1;
 66      SELECT END_INTERVAL_TIME
 67     INTO V_END_DATE(1)
 68      FROM DBA_HIST_SNAPSHOT
 69      WHERE DBID = V_DBID
 70     AND INSTANCE_NUMBER = 1
 71     AND SNAP_ID = V_END;
 72     END IF;
 73    WHILE(V_END <= V_SNAP_END(1)) LOOP
 74     V_FILE := UTL_FILE.FOPEN(
 75      P_DIR,
 76      'awr_' || V_DBNAME || '_' || V_INSTANCE(1) || '_'
 77       || TO_CHAR(V_BEGIN_DATE(1), 'YYYYMMDDHH24MISS') || '_'
 78       || TO_CHAR(V_END_DATE(1), 'YYYYMMDDHH24MISS') || '.html',
 79      'w',
 80      32767);
 81     
 82     SELECT OUTPUT
 83     BULK COLLECT INTO V_REPORT
 84     FROM TABLE(
 85      DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
 86       V_DBID,
 87       V_INSTANCE(1),
 88       V_BEGIN,
 89       V_END,
 90       0));
 91     FOR I IN 1..V_REPORT.COUNT LOOP
 92      UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
 93     END LOOP;
 94     UTL_FILE.FCLOSE(V_FILE);
 95     V_BEGIN := V_BEGIN + 1;
 96     V_END := V_END + 1;
 97     V_BEGIN_DATE(1) := V_END_DATE(1);
 98     SELECT MAX(END_INTERVAL_TIME)
 99     INTO V_END_DATE(1)
100      FROM DBA_HIST_SNAPSHOT
101      WHERE DBID = V_DBID
102     AND INSTANCE_NUMBER = 1
103     AND SNAP_ID = V_END;
104    END LOOP;
105   ELSE
106    SELECT BANNER
107    INTO V_VERSION
108    FROM V$VERSION
109    WHERE BANNER LIKE 'CORE%';
110    IF TO_NUMBER(LTRIM(SUBSTR(V_VERSION, 5, INSTR(V_VERSION, '.', 1) - 5), CHR(9))) > 11
111     OR (TO_NUMBER(LTRIM(SUBSTR(V_VERSION, 5, INSTR(V_VERSION, '.', 1) - 5), CHR(9))) = 11
112      AND TO_NUMBER(SUBSTR(V_VERSION, INSTR(V_VERSION, '.', 1) + 1, INSTR(V_VERSION, '.', 1, 2) - INSTR(V_VERSION, '.', 1))) = 2)
113    THEN
114      V_BEGIN := V_SNAP_BEG(1);
115     IF P_PERINTERVAL = FALSE
116      THEN
117       V_END := V_SNAP_END(1);
118      ELSE
119       V_END := V_SNAP_BEG(1) + 1;
120       SELECT END_INTERVAL_TIME
121      INTO V_END_DATE(1)
122       FROM DBA_HIST_SNAPSHOT
123       WHERE DBID = V_DBID
124      AND INSTANCE_NUMBER = 1
125      AND SNAP_ID = V_END;
126      END IF;
127     WHILE(V_END <= V_SNAP_END(1)) LOOP
128      V_FILE := UTL_FILE.FOPEN(
129       P_DIR,
130       'awr_' || V_DBNAME || '_' || 'RAC' || '_'
131        || TO_CHAR(V_BEGIN_DATE(1), 'YYYYMMDDHH24MISS') || '_'
132        || TO_CHAR(V_END_DATE(1), 'YYYYMMDDHH24MISS') || '.html',
133       'w',
134       32767);
135      
136      EXECUTE IMMEDIATE
137       'SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:DBID, '''', :BEGIN, :END, 0))'
138       BULK COLLECT INTO V_REPORT
139       USING V_DBID, V_BEGIN, V_END;
140      
141      FOR I IN 1..V_REPORT.COUNT LOOP
142       UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
143      END LOOP;
144      UTL_FILE.FCLOSE(V_FILE);
145      V_BEGIN := V_BEGIN + 1;
146      V_END := V_END + 1;
147      V_BEGIN_DATE(1) := V_END_DATE(1);
148      SELECT MAX(END_INTERVAL_TIME)
149      INTO V_END_DATE(1)
150       FROM DBA_HIST_SNAPSHOT
151       WHERE DBID = V_DBID
152      AND INSTANCE_NUMBER = 1
153      AND SNAP_ID = V_END;
154     END LOOP;
155    ELSE
156     FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP
157       V_BEGIN := V_SNAP_BEG(I);
158      IF P_PERINTERVAL = FALSE
159       THEN
160        V_END := V_SNAP_END(I);
161       ELSE
162        V_END := V_SNAP_BEG(I) + 1;
163        SELECT END_INTERVAL_TIME
164       INTO V_END_DATE(I)
165        FROM DBA_HIST_SNAPSHOT
166        WHERE DBID = V_DBID
167       AND INSTANCE_NUMBER = V_INSTANCE(I)
168       AND SNAP_ID = V_END;
169       END IF;
170      WHILE(V_END <= V_SNAP_END(I)) LOOP
171       V_FILE := UTL_FILE.FOPEN(
172        P_DIR,
173        'awr_' || V_DBNAME || '_' || V_INSTANCE(I) || '_'
174         || TO_CHAR(V_BEGIN_DATE(I), 'YYYYMMDDHH24MISS') || '_'
175         || TO_CHAR(V_END_DATE(I), 'YYYYMMDDHH24MISS') || '.html',
176        'w',
177        32767);
178    
179       SELECT OUTPUT
180       BULK COLLECT INTO V_REPORT
181       FROM TABLE(
182        DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
183         V_DBID,
184         V_INSTANCE(I),
185         V_BEGIN,
186         V_END,
187         0));
188       FOR I IN 1..V_REPORT.COUNT LOOP
189        UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I));
190       END LOOP;
191       UTL_FILE.FCLOSE(V_FILE);
192       V_BEGIN := V_BEGIN + 1;
193       V_END := V_END + 1;
194       V_BEGIN_DATE(I) := V_END_DATE(I);
195       SELECT MAX(END_INTERVAL_TIME)
196       INTO V_END_DATE(I)
197        FROM DBA_HIST_SNAPSHOT
198        WHERE DBID = V_DBID
199       AND INSTANCE_NUMBER = V_INSTANCE(I)
200       AND SNAP_ID = V_END;
201      END LOOP;
202     END LOOP;
203    END IF;
204   END IF;
205  END;
206  /

过程已创建。

运行过程:

SQL> EXEC P_AWR_REPORT('20110505100000', '20110505140000', 'EXP_DIR', null, true)

PL/SQL procedure successfully completed.

检查文件:

[oracle@xsh-server1 dump]$ ls -l
total 310772
-rw-r--r-- 1 oracle dba 396881 May 5 14:37 awr_XSHDB_RAC_20110505100001_20110505110007.html
-rw-r--r-- 1 oracle dba 396122 May 5 14:38 awr_XSHDB_RAC_20110505110007_20110505120013.html
-rw-r--r-- 1 oracle dba 394586 May 5 14:38 awr_XSHDB_RAC_20110505120013_20110505130022.html
-rw-r--r-- 1 oracle dba 398420 May 5 14:38 awr_XSHDB_RAC_20110505130022_20110505140005.html
-rw-r----- 1 oracle oinstall 138874880 Jan 6 19:20 backupcard0106.dmp
-rw-r----- 1 oracle oinstall 143302656 Jan 6 20:58 backupvip0106.dmp
-rw-r----- 1 oracle dba 23433378 Jan 6 20:46 dmpfile_host_10.11_10.12.dmp.gz
-rw-r----- 1 oracle dba 1012137 Jan 6 20:30 dmpfile_sc_10.11_10.12.dmp.gz
-rw-r----- 1 oracle dba 9611686 Jan 6 21:28 dmpfile_vip_08.01_10.12.dmp.gz

对于11.2版本,RAC环境的两个实例生成到同一个报告中。

 

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

下一篇: ORA-600(2878)错误
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10526225