Oracle batch export AWR reports

In the project, all AWR reports in the production database must be dumped and imported to the database for testing convenience. The AWR report in the test database needs to be exported one by one based on dbid and Instance name.

In the project, all AWR reports in the production database must be dumped and imported to the database for testing convenience. The AWR report in the test database needs to be exported one by one based on dbid and Instance name.

Work requirement: in the project, all the AWR reports in the production database must be dumped and imported to the database for testing convenience. The AWR report in the test database needs to be exported one by one based on dbid and Instance name. If you encounter a lot of problems with the RAC system, it will be very troublesome. I found some scripts on the Internet and found that they were not suitable for myself. So I learned a stored procedure to implement this function.

Note: You can see in $ Oracle_HOME/rdbms/admin/awrrpti. SQL that the AWR report is generated mainly using the DBMS_WORKLOAD_REPOSITORY.awr_report_html package. The way you like to play depends on your personal preferences.

Generate and view Oracle AWR reports

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Steps for generating an Oracle AWR report

Stored Procedure Implementation Code:

Create or replace directory AWR_REPORTS_DIR as '/u01/awr /';
DECLARE

Rochelle snap_start NUMBER: = 14632;
Rochelle snap_end NUMBER: = 14643;
Rochelle dir VARCHAR2 (50): = 'awr _ REPORTS_DIR ';

Rochelle last_snap NUMBER: = NULL;
Rochelle dbid NUMBER: = 813977229;
L_file UTL_FILE.file_type;
Rochelle file_name VARCHAR (50 );
Cursor cur_inum is SELECT instance_number FROM dba_hist_snapshot WHERE dbid = l_dbid group by instance_number order by instance_number;

BEGIN

For l_instance_number in cur_inum loop
Rochelle last_snap: = NULL;
FOR cur_snap IN (SELECT snap_id
FROM dba_hist_snapshot
WHERE instance_number = l_instance_number.instance_number
AND snap_id BETWEEN l_snap_start AND l_snap_end
Order by snap_id)
LOOP
IF l_last_snap IS NOT NULL THEN
L_file: = UTL_FILE.fopen (l_dir, 'awr _ '| l_instance_number.instance_number |' _ '| l_last_snap |' _ '| cur_snap.snap_id | '.html', 'W ', 32767 );

FOR cur_rep IN (SELECT output
From table (DBMS_WORKLOAD_REPOSITORY.awr_report_html (l_dbid, l_instance_number.instance_number, l_last_snap, cur_snap.snap_id )))
LOOP
UTL_FILE.put_line (l_file, cur_rep.output );
End loop;
UTL_FILE.fclose (l_file );
End if;
Rochelle last_snap: = cur_snap.snap_id;
End loop;
End loop;

EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open (l_file) THEN
UTL_FILE.fclose (l_file );
End if;
RAISE;
END;
/

Shell script implementation reference:

#! /Bin/sh
# Version 1.0 created by sprilich 20121101
# Version 1.2 edited by sprilich 20121214
# Set the environment
# ORACLE_SID = eupdb
# ORACLE_HOME =/u01/oracle/product/db10gr2
ORACLE_SID = portaldb1
ORACLE_HOME = "$ ORACLE_HOME"
PATH = $ ORACLE_HOME/bin: $ PATH
CONNECTSTR = "/as sysdba"
# Maid = "223"
# END_ID = "226"
BEGIN_TIME = "20140504_00: 00: 00"
END_TIME = "20140506_12: 00: 00"
# FTPSERVERIP = "10.193.16.86"
# FTPUSER = "ftpuser"
# FTPPASS = "1qaz2wsx"
Export ORACLE_SID
Export ORACLE_HOME
Export PATH

Function Dbid {
Sqlplus-S $ CONNECTSTR < Set pages 0 termout off verify off feedback off
Select DBID from v \ $ database;
Exit
EOF
}

Instnum (){
Sqlplus-S $ CONNECTSTR < Set pages 0 termout off verify off feedback off
Select instance_number from v \ $ instance;
Exit
EOF
}

Instname (){
Sqlplus-S $ CONNECTSTR < Set pages 0 termout off verify off feedback off
Select instance_name from v \ $ instance;
Exit
EOF
}

Function Snap_id_like_time {
Sqlplus-S $ CONNECTSTR < Set pages 0 termout off verify off feedback off;
Select SNAP_ID from dba_hist_snapshot
Where to_char (end_interval_time, 'hh24') in (9, 10, 11, 12, 14, 15, 17)
And trunc (end_interval_time, 'mi')> trunc (sysdate-1)
And instance_number in (select instance_number from v \ $ instance)
Order by snap_id;
Exit
EOF
}

Function Snap_id_between_time {
Sqlplus-S $ CONNECTSTR < Set pages 0 termout off verify off feedback off;
Select SNAP_ID from dba_hist_snapshot
Where trunc (end_interval_time, 'hh')> = trunc (to_date ('$ BEGIN_TIME', 'yyyymmdd _ HH24: MI: ss'), 'hh ')
And trunc (end_interval_time, 'hh') <= trunc (to_date ('$ END_TIME', 'yyyymmdd _ HH24: MI: ss'), 'hh ')
And instance_number in (select instance_number from v \ $ instance)
Order by snap_id;
Exit
EOF
}