Rem
Rem $Header: rdbms/admin/awrextr.sql /main/4 2009/03/24 08:38:16 ilistvin Exp $
Rem
Rem awrextr.sql
Rem
Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem awrextr.sql - AWR Extract
Rem
Rem DESCRIPTION
Rem SQL/Plus script to help users extract data from the AWR
Rem
Rem NOTES
Rem User must be connected as SYS to run this SQL/Plus script.
Rem
Rem MODIFIED (MM/DD/YY)
Rem ilistvin 03/16/09 - remove disclaimer
Rem veeve 05/24/07 - show verbose msgs
Rem mlfeng 03/01/05 - Add Disclaimer for support
Rem mlfeng 06/01/04 - mlfeng_awr_import_export
Rem mlfeng 05/17/04 - Created
Rem
-- Use local dbid
-- define dbid = '';
--
-- List all snapshots
-- define num_days = '';
--
-- List no (i.e. 0) snapshots
-- define num_days = 0;
--
-- List past 3 day's snapshots
-- define num_days = 3;
--
-- Optionally, set the snapshots to export. If you do not set them,
-- you will be prompted for the values.
-- define begin_snap = 0;
-- define end_snap = 10000000;
--
-- Use the default directory name and file name
-- define directory_name = 'DATA_PUMP_DIR'
-- define file_name = ''
--
set echo off heading on underline on verify off
set feedback off linesize 80 termout on;
prompt ~~~~~~~~~~~~~
prompt AWR EXTRACT
prompt ~~~~~~~~~~~~~
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt ~ This script will extract the AWR data for a range of snapshots ~
prompt ~ into a dump file. The script will prompt users for the ~
prompt ~ following information: ~
prompt ~ (1) database id ~
prompt ~ (2) snapshot range to extract ~
prompt ~ (3) name of directory object ~
prompt ~ (4) name of dump file ~
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--
-- Get the current database information - this will be used as the
-- default for the database ID in the AWR schema to extract from.
set termout off;
column db_name heading "DB Name" format a12;
column db_dbid heading "DB Id" format 9999999999 just c new_value db_dbid;
select d.dbid db_dbid
, d.name db_name
from v$database d;
set termout on;
column dbb_name heading "DB Name" format a12;
column dbbid heading "DB Id" format a12 just c;
column host heading "Host" format a12;
prompt
prompt
prompt Databases in this Workload Repository schema
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select distinct
(case when cd.dbid = wr.dbid and
cd.name = wr.db_name
then '* '
else ' '
end) || wr.dbid dbbid
, wr.db_name dbb_name
, wr.host_name host
from dba_hist_database_instance wr, v$database cd
order by dbbid desc;
prompt
prompt The default database id is the local one: '&db_dbid'. To use this
prompt database id, press <return> to continue, otherwise enter an alternative.
prompt
set heading off;
column dbid new_value dbid noprint;
select 'Using ' || nvl('&&dbid','&db_dbid') || ' for Database ID'
, nvl('&&dbid','&db_dbid') dbid
from sys.dual;
-- Set up Bind for database ID
variable dbid number;
begin
:dbid := &dbid;
end;
/
--
-- Error reporting
whenever sqlerror exit;
variable max_snap_time char(10);
declare
cursor cidnum is
select 'X'
from dba_hist_database_instance
where dbid = :dbid;
cursor csnapid is
select to_char(max(end_interval_time),'dd/mm/yyyy')
from dba_hist_snapshot
where dbid = :dbid;
vx char(1);
begin
-- Check Database Id/Instance Number is a valid pair
open cidnum;
fetch cidnum into vx;
if cidnum%notfound then
raise_application_error(-20200,
'Database ' || :dbid ||
' does not exist in DBA_HIST_DATABASE_INSTANCE');
end if;
close cidnum;
-- Check Snapshots exist for Database Id/Instance Number
open csnapid;
fetch csnapid into :max_snap_time;
if csnapid%notfound then
raise_application_error(-20200,
'No snapshots exist for Database ' || :dbid);
end if;
close csnapid;
end;
/
--
-- Ask how many days of snapshots to display
set termout on;
column dbid_fmt noprint;
column db_name format a12 heading 'DB Name';
column snap_id format 99999990 heading 'Snap Id';
column snapdat format a18 heading 'Snap Started' just c;
prompt
prompt
prompt Specify the number of days of snapshots to choose from
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt Entering the number of days (n) will result in the most recent
prompt (n) days of snapshots being listed. Pressing <return> without
prompt specifying a number lists all completed snapshots.
prompt
prompt
set heading off;
column num_days new_value num_days noprint;
select 'Listing '
|| decode( nvl('&&num_days', 3.14)
, 0 , 'no snapshots'
, 3.14 , 'all Completed Snapshots'
, 1 , 'the last day''s Completed Snapshots'
, 'the last &num_days days of Completed Snapshots')
, nvl('&&num_days', 3.14) num_days
from sys.dual;
set heading on;
--
-- List available snapshots
break on db_name;
ttitle off;
select s.dbid dbid_fmt
, max(di.db_name) db_name
, s.snap_id snap_id
, to_char(max(s.end_interval_time), 'dd Mon YYYY HH24:mi') snapdat
from dba_hist_snapshot s
, dba_hist_database_instance di
where s.dbid = :dbid
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.end_interval_time >= decode( &num_days
, 0 , to_date('31-JAN-9999','DD-MON-YYYY')
, 3.14, s.end_interval_time
, to_date(:max_snap_time,'dd/mm/yyyy') -
(&num_days-1))
group by s.dbid, snap_id
order by s.dbid, snap_id;
clear break;
ttitle off;
--
-- Ask for the snapshots Id's which are to be compared
prompt
prompt
prompt Specify the Begin and End Snapshot Ids
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt Begin Snapshot Id specified: &&begin_snap
prompt
prompt End Snapshot Id specified: &&end_snap
prompt
--
-- Set up the snapshot-related binds
variable bid number;
variable eid number;
begin
:bid := &begin_snap;
:eid := &end_snap;
/* do a basic check to ensure end_snap >= begin_snap */
IF (:bid > :eid) THEN
RAISE_APPLICATION_ERROR(-20019, 'begin_snap must be less than or ' ||
'equal to end_snap.');
END IF;
end;
/
--
-- Ask User for Directory Name
--
prompt
prompt Specify the Directory Name
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~
set heading on;
column dirname format a30 heading 'Directory Name'
column dirpath format a49 heading 'Directory Path' wrap
select directory_name dirname, directory_path dirpath
from DBA_DIRECTORIES
order by directory_name;
set termout off;
column dflt_dir new_value dflt_dir noprint;
select '' dflt_dir from dual;
set termout on;
prompt
prompt Choose a Directory Name from the above list (case-sensitive).
prompt
set heading off;
column directory_name new_value directory_name noprint;
select 'Using the dump directory: ' || nvl('&&directory_name','&dflt_dir')
, nvl('&&directory_name','&dflt_dir') directory_name
from sys.dual;
variable dmpdir varchar2(30);
variable dmppath varchar2(4000)
declare
cursor dirpath (dirname varchar2) is
select directory_path
from dba_directories
where directory_name = dirname;
begin
:dmpdir := '&directory_name';
/* select the directory path into a variable */
open dirpath(:dmpdir);
fetch dirpath into :dmppath;
if (dirpath%NOTFOUND) then
RAISE_APPLICATION_ERROR(-20103,
'directory name ''' || :dmpdir ||
''' is invalid', TRUE);
end if;
close dirpath;
end;
/
set termout off;
column dflt_name new_value dflt_name noprint;
select 'awrdat'||'_'||:bid||'_'||:eid dflt_name from dual;
set termout on;
prompt
prompt Specify the Name of the Extract Dump File
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt The prefix for the default dump file name is &dflt_name..
prompt To use this name, press <return> to continue, otherwise enter
prompt an alternative.
prompt
set heading off;
column file_name new_value file_name noprint;
select 'Using the dump file prefix: ' || nvl('&&file_name','&dflt_name')
, nvl('&&file_name','&dflt_name') file_name
from sys.dual;
variable dmpfile varchar2(30);
begin
:dmpfile := '&file_name';
end;
/
set serveroutput on;
exec dbms_output.enable(500000);
set termout on;
column loc format a80 newline;
column locend format a80;
declare
begpos NUMBER;
numchar NUMBER := 74;
begin
dbms_output.put_line('|');
dbms_output.put_line('| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line('| The AWR extract dump file will be located ');
dbms_output.put_line('| in the following directory/file: ');
begpos := 1;
WHILE (begpos <= length(:dmppath)) LOOP
dbms_output.put_line('| ' || substr(:dmppath, begpos, numchar));
begpos := begpos + numchar;
END LOOP;
dbms_output.put_line('| ' || :dmpfile || '.dmp');
dbms_output.put_line('| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line('|');
dbms_output.put_line('| *** AWR Extract Started ...');
dbms_output.put_line('|');
dbms_output.put_line('| This operation will take a few moments. The ');
dbms_output.put_line('| progress of the AWR extract operation can be ');
dbms_output.put_line('| monitored in the following directory/file: ');
begpos := 1;
WHILE (begpos <= length(:dmppath)) LOOP
dbms_output.put_line('| ' || substr(:dmppath, begpos, numchar));
begpos := begpos + numchar;
END LOOP;
dbms_output.put_line('| ' || :dmpfile || '.log');
dbms_output.put_line('|');
end;
/
whenever sqlerror continue;
set heading off;
set linesize 110 pagesize 50000;
set echo off;
set feedback off;
set termout on;
begin
/* call PL/SQL routine to extract the data */
dbms_swrf_internal.awr_extract(dmpfile => :dmpfile,
dmpdir => :dmpdir,
bid => :bid,
eid => :eid,
dbid => :dbid);
dbms_swrf_internal.clear_awr_dbid;
end;
/
prompt
prompt End of AWR Extract
undefine dbid
undefine num_days
undefine begin_snap
undefine end_snap
undefine directory_name
undefine file_name