Apr 17: 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
Posted by Doug Burns Comments: (4) Trackbacks: (0)
Defined tags for this entry:
Related entries by tags:
Network Events in ASH
Alternative Pictures Demo
That Pictures demo in full
MMON Sampling ASH Data
"How useful are diagnostic/optimization tools?" - Another View
AWR Licensing

Trackbacks
Trackback specific URI for this entry

No Trackbacks

Comments
Display comments as (Linear | Threaded)

#1 - David Krch 2008-04-19 19:57 - (Reply)

Doug,
as it was me, who came with the bad news in Prague, I owe you a good news. And the good news is that you were right during our dialogue that you have seen the AWR Transport documented somewhere. I also made my homework and found out that the difference is in the version - it is not documented for 10g, but it is for 11g http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/autostat.htm#CHDCGJFA. So it's still worth to draw attention to this interesting functionality.

I really enjoyed your course, it was very inspiring for me. And of course, I'm glad that you like our penguins :-)

#2 - Doug Burns said:
2008-04-20 09:17 - (Reply)

David,

Thanks for the penguin, thanks for your kind comments and thanks for checking the docs!

Now I just need to make sure I have two 11g instances running if I'm going to show that.

Sorry for forgetting your name - I am *terrible* at remembering names - so I'm glad you visited :-)

Cheers,

Doug

#3 - Mathew Butler said:
2010-06-18 10:51 - (Reply)

Hi Doug,

I read this when it was first posted and having just had a requirement to move AWR data for analysis have come back (via google) to pick up the details.

This note is just to let you know that this post has been useful to someone and
to encourage you to keep up the good work.

I just wish I had enough time to blog to share my experience...

Hope you are well.

Cheers,

Mat.

#4 - Doug Burns said:
2010-06-21 17:35 - (Reply)

Hi Mat,

Thanks very much for the comment. It was weird because it turned up just as I was preparing for a day teaching at Amis and I was deciding not to include this stuff.

Changed my mind after your comment and it was obviously a good decision!

Cheers,

Doug


Add Comment

Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
BBCode format allowed