AWR Reports automation
We can find the retention and snap interval from below query.
select * from DBA_HIST_WR_CONTROL;
DBID SNAP_INTERVAL RETENTION TOPNSQL
3439180179 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
we want to change the retention to 15 days and intervel to 30 mins. For that we executed below scripts
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 21600, -- in minutes
interval => 30);
END;
/
## We can automate this awr report generation process by following scripts. Before we start we need to keep in mind that a table contains username and password of a user who will generate awr report from different databases. Let say the Role is role_awr and the user name is usr_awr. This user and role will need to be created on each database.
create role role_awr;
GRANT SELECT ON SYS.V_$DATABASE TO role_awr;
GRANT SELECT ON SYS.V_$INSTANCE TO role_awr;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO role_awr;
GRA