Automatically Collect Multiple AWR Reports: # Step1: Type The Above DB Id Here
Automatically Collect Multiple AWR Reports: # Step1: Type The Above DB Id Here
Automatically Collect Multiple AWR Reports: # Step1: Type The Above DB Id Here
Reports
For example, in order to collect AWR reports from all instances of the dbm01 database during the
period from 00:00 on Jan 1st, 2019 to 00:00 on Jan 2nd, 2019 , the steps are:
1 SQL> @mkawrscript
2
3 Instances in this Workload Repository schema
4 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5 DB Id Inst Num DB Name Instance Host
6 ------------ ---------- --------- ---------- ------
7 1230376656 2 DBM01 dbm012 dbsys2
8 * 1230376656 1 DBM01 dbm011 dbsys1
9
10 Enter value for dbid: 1230376656 # Step1: Type the above DB Id here
11 Using 1230376656 for database Id
12 Enter value for inst_num: 1 # Step2: Just type "1", as we don't
actually
13 Using 1 for instance number # use this input anymore
14
15
16 Specify the number of days of snapshots to choose from
17 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
18 Entering the number of days (n) will result in the most recent
19 (n) days of snapshots being listed. Pressing <return> without
20 specifying a number lists all completed snapshots.
21
22
23 Enter value for num_days: # Step3: Just type <RETURN> here
24
25
26 Listing all Completed Snapshots
27 Instance DB Name Snap Id Snap Started Snap Level
28 ------------ ------------ ---------- ------------------ ----------
29 dbm011 DBM01 688 01 Jan 2020 00:47 1 #<=begin_snap
30 689 01 Jan 2020 01:47 1
31 690 01 Jan 2020 02:47 1
32 ...
33 710 01 Jan 2020 22:48 1
34 711 01 Jan 2020 23:48 1
35 712 02 Jan 2020 00:48 1 #<=end_snap
36 713 02 Jan 2020 01:47 1
37 714 02 Jan 2020 02:47 1
38
39 Specify the Begin and End Snapshot Ids
40 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
41 Enter value for begin_snap: 688 # Step4: Input begin_snap ID for 01 Jan
2020 00:47
42 Begin Snapshot Id specified: 688
43
44 Enter value for end_snap: 712 # Step5: Input end_snap ID for 02 Jan 2020
00:48
45 End Snapshot Id specified: 712
46
47
48 Building the SQL script of getawr.sql
49 Done with getawr.sql
50 ===========================================================================
=
51 Next, check and launch:
52
53 @getawr.sql
54
55 to generate AWR reports in the current folder.
56 ===========================================================================
=
1 SQL> @getawr.sql
2 Beginning AWR generation...
3 Creating AWR report awrrpt_20191231_2348-20200101_0048_INST_1_688-689.html
for INST#1 from snapshot 688 to 689...
4 Creating AWR report awrrpt_20200101_0048-20200101_0148_INST_1_689-690.html
for INST#1 from snapshot 689 to 690...
5 Creating AWR report awrrpt_20200101_0148-20200101_0248_INST_1_690-691.html
for INST#1 from snapshot 690 to 691...
6 ...
7
8 Creating AWR report awrrpt_20200101_2048-20200101_2148_INST_2_709-710.html
for INST#2 from snapshot 709 to 710...
9 Creating AWR report awrrpt_20200101_2148-20200101_2248_INST_2_710-711.html
for INST#2 from snapshot 710 to 711...
10 Creating AWR report awrrpt_20200101_2248-20200101_2348_INST_2_711-712.html
for INST#2 from snapshot 711 to 712...
11 AWR generation completed with 48 reports.
If everything goes well, a bunch of AWR HTML reports will be generated in /home/oracle/dbm01-
awr/ .
Note that if the target database consists of several instances, mkawrscript.sql will iterate
all instances to collect the reports.
1 $ ls
2 awrrpt_20191231_2348-20200101_0048_INST_1_688-689.html
3 awrrpt_20191231_2348-20200101_0048_INST_2_688-689.html
4 awrrpt_20200101_0048-20200101_0148_INST_1_689-690.html
5 awrrpt_20200101_0048-20200101_0148_INST_2_689-690.html
6 ...
7 awrrpt_20200101_2248-20200101_2348_INST_1_711-712.html
8 awrrpt_20200101_2248-20200101_2348_INST_2_711-712.html
9 getawr.sql
10 mkawrscript.sql