You can create a role awradmin and grant all necessary privileges to this role. Then you can grant any user awradmin role to generate AWR and ADDM report
— First connect as sys user.
SQL> CONN / as sysdba
connected
SQL>CREATE USER awrtest IDENTIFIED BY awrtest DEFAULT TABLESPACE users;
User created.
SQL> GRANT CONNECT , RESOURCE TO awrtest;
Grant succeeded.
SQL> create role awradmin;
Role created.
SQL> GRANT SELECT ON SYS.V_$DATABASE TO awradmin;
Grant succeeded.
SQL> GRANT SELECT ON SYS.V_$INSTANCE TO awradmin;
Grant succeeded.
SQL> GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awradmin;
Grant succeeded.
SQL> GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awradmin;
Grant succeeded.
SQL> GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awradmin;
Grant succeeded.
SQL> GRANT ADVISOR TO awradmin;
Grant succeeded.
SQL> GRANT awradmin TO awrtest;
Grant succeeded.
— Now connect as awrtest user
SQL> conn awrtest/awrtest
connected
SQL> @/opt/ora10g/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
———– ———— ——– ————
1388814149 ORCL 1 orcl
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
* 1388814149 1 ORCL orcl eps70
Using 1388814149 for database Id
Using 1 for instance number
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.
Enter value for num_days: 1
Listing the last day’s Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
orcl ORCL 7439 28 Sep 2015 00:30 1
7440 28 Sep 2015 01:30 1
7441 28 Sep 2015 02:31 1
7442 28 Sep 2015 03:30 1
7443 28 Sep 2015 04:30 1
7444 28 Sep 2015 05:30 1
7445 28 Sep 2015 06:30 1
7446 28 Sep 2015 07:30 1
7447 28 Sep 2015 08:30 1
7448 28 Sep 2015 09:30 1
7449 28 Sep 2015 10:31 1
7450 28 Sep 2015 11:30 1
7451 28 Sep 2015 12:30 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 7450
Begin Snapshot Id specified: 7450
Enter value for end_snap: 7451
End Snapshot Id specified: 7451
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_7450_7451.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: ashishawr.html
Using the report name ashishawr.html
.
.
.
.
.
.
.
Report written to ashishawr.html
In similar way you can generate ADDM report.
SQL>@/opt/ora10g/rdbms/admin/addmrpt.sql
Christena
Great delivery. Solid arguments. Keep up the amazing effort.