Inhaltsverzeichnis
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_awr_user_role.sql
--============================================================================== -- 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 <user_anme;“ zuordnen.
AWR Berichtsaufruf
Snapshot ID's ermitteln und Bericht aufrufen:
- awr_call_report.sql
--============================================================================== -- 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!
- awr_call_ash_report.sql
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