Andrew Fraser DBA

Oracle AwrExtr cron to export backup AWR and ASH data

Oracle supplies the awrextr and awrload scripts to backup and copy AWR and ASH data by datapump.

Those are designed to work interactively, prompting for user input.

Script below will run awrextr from cron, without the interactive user input.

#!/bin/bash
# 1) Set environment variables
export ORACLE_SID=myOracleSID
export PATH=$PATH:/usr/local/bin
export ORAENV_ASK=NO
. oraenv
# 2) Remove previous dump file.
rm -f /fullPathtoDirectory/awrexport_${ORACLE_SID}.dmp
# 3) AwrExtract out 5 days of history.
sqlplus -s /nolog << END_SQL
conn / as sysdba
set pages 9999
define dbid = '' ;
define num_days = 1 ;
column begin_snap new_value begin_snap
column end_snap new_value end_snap
SELECT MIN ( snap_id ) AS begin_snap , MAX ( snap_id ) AS end_snap FROM dba_hist_snapshot WHERE end_interval_time > SYSDATE - 5 ;
define directory_name = 'MYDIRNAME' ;
define file_name = 'awrexport_${ORACLE_SID}' ;
@?/rdbms/admin/awrextr.sql
END_SQL