Moving AWR data

Note – features in this post require the Diagnostics Pack license

[I originally had the first section at the end of the blog post, but then realised I might as well get the bad news out of the way to save you wasting your time if you’re not interested]

A small section of the course covers moving AWR data between instances to run the AWR comparison report against different environments.

The Bad News

As I mentioned before, one of Oracle’s local Pre-Sales Technical guys was on the course and he approached me at a break and said that he thought that these scripts were only to be used by Oracle Support. When I got home I re-ran the awrextr.sql script and, sure enough :-

sys@GP06PROD> @?/rdbms/admin/awrextr 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
Disclaimer: This SQL/Plus script should only be called under 
the guidance of Oracle Support. 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

So an interesting demo, but only for home experimentation or if Oracle Support are involved and I certainly wouldn’t expect to see the script converted to use old exp/imp utilities rather than the Data Pump equivalent, as I was asked during the course. In retrospect, I imagine Oracle might request you run awrextr.sql and send the output dump file to them so they can use awrload.sql to load it for further analysis.

How to move AWR data to another repository

Connect to Source Instance as SYS

@?/rdbms/admin/awrextr

The script will prompt for

1. dbid
2. Number of days worth of snapshots to display for selection (just like awrrpt.sql)
3. Begin Snap
4. End Snap
5. Directory Object Name for Data Pump (e.g. DATA_PUMP_DIR)
6. Dump File Name

Connect to Target Instance as SYS

@?/rdbms/admin/awrload

The script will prompt for

1. Directory Object Name for Data Pump (e.g. DATA_PUMP_DIR)
2. Dump File Name
3. Schema Name for staging schema that data will be imported into (the default of AWR_STAGE is fine)
4. Default and Temporary tablespaces for the staging schema

@?/rdbms/admin/awrddrpi (Note the ‘i’ which allows you to specify which database/instances to report against)

The script will prompt for

1. HTML or Text report format
2. First dbid and instance number
3. Number of days snapshots to display for selection from first repository
4. Begin and end snapshot for first repository
5. Second dbid and instance number
6. Number of days snapshots to display for selection from second repository
7. Begin and end snapshot for second repository
8. Report file name