How To Export The AWR Repository

Last week I asked the question: “What do you do with the AWR data?”

Here are some of the responses I received:

Some DBAs leave the AWR data to age out and not do anything about it. (I used to do that too, until I knew better).
Some DBAs have increased the retention to 3 weeks or more. (I have done that too!)
Some DBAs have created an AWR data warehouse to collect the AWR data from multiple databases and never age out the data.

In last week’s post, I recommended a few things you could do with the AWR data. Today’s post will look in more detail at exporting the AWR repository.

Why Do You Want To Export The AWR Repository?

Oracle has created a script, to transport the AWR data between databases. That is, to be able to extract the AWR repository from one database, and load it into another database.
If you want to create an AWR data warehouse, then you would need to transport the AWR data from your source database to the AWR data warehouse.

You could also export the AWR data for backup purposes. That is what I do. You extract the data to a dump file, and compress it, so it takes up less space. When I think of backing up the AWR, I think about a process, that runs regularly, and not just once in a while.

What script does Oracle provide?

Under the Oracle home, there is a script to export the AWR repository. This script is called: awrextr.sql, and it is located right here: $ORACLE_HOME/rdbms/admin/awrextr.sql

When you run the script interactively, it will ask you for the following things:

• database id
• number of days to list the snapshot IDs
• provide the snapshot IDs for begin and end time, (what interval you want to extract)
• directory object where the data should be extracted
• name for the dumpfile

If you only want to export the AWR on demand, every once in a while, then this script is for you! No need to do anything further, just run the script!

But what if you want to automate the process and schedule it?

It’s simple, you create your own script!

Keep reading to find out how!

What can you do to automate the extraction of AWR data?

Oracle’s awrextr.sql script, is calling the following package: dbms_swrf_internal.awr_extract.
Fortunately this package only needs a few parameters:

• directory name
• dumpfile name
• begin snap id
• end snap id
• dbid

Once you determine the values for these parameters, you call the package and export the AWR data. And your script is done!

In order to build your script, you will need to decide on some naming conventions.
Here are some ideas:

The directory name: AWR_DATA, or AWR_BCK, or DATA_PUMP_DIR, or anything you want. I like to make the script bulletproof. If the directory object doesn’t exist, then the script would create it.
The dumpfile name: for this name, you must have a naming convention. I usually capture the database name, the start time, the end time, start snap_id, end snap_id. You could have a name similar to this format:
awr_extract_HRPRD_20180801_20180831_12345_13456.dmp
Begin Snap ID: First determine what exactly are you going to export. Is it all the AWR that you have available? Figure out the min(snap_id) that is available. I’d like to export the previous month’s AWR data. In that case, you need to find out the min(snap_id) from the first day of the previous month.
End Snap ID: By now you should know the interval you are going to export. Is it all the AWR that you have available? Then you need to figure out the max(snap_id) that is available. I’d like to export the previous month’s AWR data. In that case, you need to find out the max(snap_id) from the last day of the previous month.
DBID: this is easy. you can get it from v$database.

Once you get all this information figured out in the script, then you can pass the parameters to dbms_swrf_internal.awr_extract and export the AWR data.

Once you have the script created, you can schedule it to run once a month, once a week…depending on the AWR retention, and on what you are exporting.

Happy scripting!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana