=====Oracle AWR Reports 10g/11g/12c/19c als Datenbank User ohne DBA Rechte erstellen=====
** Anlegt 2015/10 **
Aufgabe: Als normaler Entwickler auf einer Datenbank AWR Berichte/Auswertungen erstellen
**Voraussetzung: Oracle Diagnostic Pack Lizenz steht zur Verfügung!**
Als "normaler User" fehlen Rechte um AWR Reports erzeugen zu können.
Diese Rechte sollte als Role gebündelt werden und dann wird diese Rolle dem entsprechenden Anwender zu geordnet.
==== Notwendige Rolle anlegen ====
--==============================================================================
-- create the role for the usage of the AWR repository for none DBA user
-- run as sys
--==============================================================================
set echo on
create role call_awr_reports;
grant select on sys.gv_$database to call_awr_reports;
grant select on sys.gv_$instance to call_awr_reports;
grant select on sys.v_$database to call_awr_reports;
grant select on sys.v_$instance to call_awr_reports;
grant execute on sys.dbms_workload_repository to call_awr_reports;
grant select on sys.dba_hist_database_instance to call_awr_reports;
grant select on sys.dba_hist_snapshot to call_awr_reports;
set echo off
Rolle mit "grant call_awr_reports to
--==============================================================================
-- GPI - Gunther Pippèrr
-- Desc: create AWR report from sql*Plus
-- Date: 10.2015
--==============================================================================
prompt
prompt !!!!You need the Tuning Pack for this feature!!!!
prompt
column end_interval_time format a18 heading "End Interval|Time"
break on dbid
ttitle left "Overview over the snapshots in the last days" skip 2
select dbid
, instance_number
, snap_id
, to_char(end_interval_time,'hh24:mi dd.mm.yyyy') as end_interval_time
from dba_hist_snapshot
where end_interval_time > trunc(sysdate-1)
order by snap_id, instance_number
/
clear break
ttitle off
set feedback off
set heading off
set termout off
column spool_name_col new_val spool_name
column instance_number new_val inst_nr
column aktdbid new_val databaseid
SELECT replace(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'_awr_report.html','\','_')
--' resolve syntax highlight bug FROM my editer .-(
AS SPOOL_NAME_COL
,SYS_CONTEXT('USERENV','INSTANCE') as instance_number
FROM dual
/
select dbid as aktdbid
from v$database
/
set feedback on
set heading on
set termout on
set verify off
SET linesize 250 pagesize 2000 recsep off
set long 64000
accept snapshot_id_begin number prompt 'Enter Frist Snapshot Begin ID : '
accept snapshot_id_end number prompt 'Enter Snapshot End Id to compare : '
spool &&SPOOL_NAME
select * from table(sys.dbms_workload_repository.awr_report_html(&&databaseid,&&inst_nr,&&snapshot_id_begin,&&snapshot_id_end));
spool off
prompt ... check the created report &&SPOOL_NAME
host &&SPOOL_NAME
Optional kann dbms_workload_repository.awr_report_html noch ein Parameter mehr übergeben werden , siehe Doku unter http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS69120
----
==== ASH Report aufrufen ====
Mit der gleichen Methodik kann einfach auch ein ASH Bericht aufgerufen werden, dazu Funktion ASH_REPORT_HTML aufrufen.
siehe http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS72974 für die notwendigen Parametern, diesmal direkt das Datum und nicht mehr die Snap ID's!
prompt
prompt !!!!You need the Tuning Pack for this feature!!!!
prompt
-- =====
-- get the spoolfile name and instance_number + DB ID
-- =====
set feedback off
set heading off
set termout off
column spool_name_col new_val spool_name
column instance_number new_val inst_nr
column aktdbid new_val databaseid
SELECT replace(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'_ash_report.html','\','_')
--' resolve syntax highlight bug FROM my editer .-(
AS SPOOL_NAME_COL
,SYS_CONTEXT('USERENV','INSTANCE') as instance_number
FROM dual
/
select dbid as aktdbid
from v$database
/
set feedback on
set heading on
set termout on
-- =====
-- ask for the Start and endtime of the report
-- =====
set verify off
SET linesize 120 pagesize 300 recsep off
define TIME_FORMAT='dd.mm.yyyy hh24:mi'
column min_start_time format a18 heading "Early Start|Date"
column max_start_time format a18 heading "Latest Start|Date"
ttitle left "Overview over the possible timeframe to get an ash report " skip 2
select to_char(min(s.sample_time),'&&TIME_FORMAT') as min_start_time
, to_char(max(s.sample_time),'&&TIME_FORMAT') as max_start_time
from dba_hist_active_sess_history s
where dbid = &&databaseid
and instance_number = &&inst_nr
/
--
-- fix and snap_id in (... ) like seelect min(snap_id), max(snap_id) from dba_hist_snapshot where dbid = s.dbid and instance_number = s.inst_num )
--
ttitle off
accept l_btime date prompt 'Enter start time (format &&TIME_FORMAT): '
accept l_etime date prompt 'Enter end time (format &&TIME_FORMAT): '
-- =====
-- create the ASH Report
-- =====
SET linesize 500 pagesize 9000 recsep off
set long 64000
set feedback off
set heading off
spool &&SPOOL_NAME
select *
from table(sys.dbms_workload_repository.ash_report_html( &&databaseid
, &&inst_nr
, to_date('&&l_btime','&&TIME_FORMAT')
, to_date('&&l_etime','&&TIME_FORMAT')
)
)
/
spool off
set heading on
set feedback on
prompt ... check the created report &&SPOOL_NAME
host &&SPOOL_NAME
set linesize 130 pagesize 300 recsep off
----
==== Quellen ====
Oracle Blogs:
* http://marcel.vandewaters.nl/oracle/database-oracle/privileges-for-awr-reporting
* https://oraclefunda.wordpress.com/2009/10/29/how-to-create-awr-report-manually/
Übersicht ASH
* http://www.oracle.com/technetwork/database/manageability/ppt-active-session-history-129612.pdf