做了一個儲存過程，封裝了awr的report的功能。

增加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

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環境的兩個例項生成到同一個報告中。

用儲存過程封裝awrrpt指令碼（四）

相關文章