{"id":1403,"date":"2008-04-17T12:00:00","date_gmt":"2008-04-17T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1403"},"modified":"2008-04-17T12:00:00","modified_gmt":"2008-04-17T12:00:00","slug":"moving-awr-data","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2008\/04\/17\/moving-awr-data\/","title":{"rendered":"Moving AWR data"},"content":{"rendered":"

Note – features in this post require the Diagnostics Pack license<\/em><\/p>\n

[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<\/em>]<\/p>\n

A small section of the course covers moving AWR data between instances to run the AWR comparison report against different environments.<\/p>\n

The Bad News<\/strong><\/u><\/p>\n

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 :- <\/p>\n

\nsys@GP06PROD> @?\/rdbms\/admin\/awrextr \n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ \n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ \nDisclaimer: This SQL\/Plus script should only be called under \nthe guidance of Oracle Support. \n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/pre>\n
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.<\/p>\n
How to move AWR data to another repository <\/strong><\/u><\/p>\n
Connect to Source Instance as SYS <\/p>\n
@?\/rdbms\/admin\/awrextr <\/p>\n
The script will prompt for <\/p>\n
    \n
  1. dbid <\/li>\n
  2. Number of days worth of snapshots to display for selection (just like awrrpt.sql) <\/li>\n
  3. Begin Snap <\/li>\n
  4. End Snap <\/li>\n
  5. Directory Object Name for Data Pump (e.g. DATA_PUMP_DIR) <\/li>\n
  6. Dump File Name <\/li>\n<\/ol>\n
    Connect to Target Instance as SYS <\/p>\n
    @?\/rdbms\/admin\/awrload <\/p>\n
    The script will prompt for <\/p>\n
      \n
    1. Directory Object Name for Data Pump (e.g. DATA_PUMP_DIR) <\/li>\n
    2. Dump File Name <\/li>\n
    3. Schema Name for staging schema that data will be imported into (the default of AWR_STAGE is fine) <\/li>\n
    4. Default and Temporary tablespaces for the staging schema <\/li>\n<\/ol>\n
      @?\/rdbms\/admin\/awrddrpi (Note the ‘i’ which allows you to specify which database\/instances to report against)<\/p>\n
      The script will prompt for <\/p>\n
        \n
      1. HTML or Text report format<\/li>\n
      2. First dbid and instance number<\/li>\n
      3. Number of days snapshots to display for selection from first repository<\/li>\n
      4. Begin and end snapshot for first repository <\/li>\n
      5. Second dbid and instance number<\/li>\n
      6. Number of days snapshots to display for selection from second repository<\/li>\n
      7. Begin and end snapshot for second repository <\/li>\n
      8. Report file name<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
        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… Continue reading Moving AWR data<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1403","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1719,"url":"http:\/\/orcldoug.com\/blog\/2014\/07\/24\/recurring-conversations-awr-intervals-part-2\/","url_meta":{"origin":1403,"position":0},"title":"Recurring Conversations: AWR Intervals (Part 2)","date":"July 24, 2014","format":false,"excerpt":"(Reminder, just in case we still need it, that the use of features in this post require Diagnostics Pack license.) Damn me for taking so long to write blog posts these days. By the time I get around to them, certain very knowledgeable people have commented on part 1 and\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1553,"url":"http:\/\/orcldoug.com\/blog\/2009\/12\/13\/my-favourite-oracle-blog\/","url_meta":{"origin":1403,"position":1},"title":"My Favourite Oracle Blog","date":"December 13, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.I think there a quite a few decent Oracle blogs around. There are links to some of them over there on the right. But by far my favourite this year has been Kerry Osborne's. I think there are a number of\u2026","rel":"","context":"With 9 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1713,"url":"http:\/\/orcldoug.com\/blog\/2014\/07\/07\/recurring-conversations-awr-intervals-part-1\/","url_meta":{"origin":1403,"position":2},"title":"Recurring Conversations: AWR Intervals (Part 1)","date":"July 7, 2014","format":false,"excerpt":"I've seen plenty of blog posts and discussions over the years about the need to increase the default AWR retention period beyond the default value of 8 days. Experienced Oracle folk understand how useful it is to have a longer history of performance metrics to cover an entire workload period\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1289,"url":"http:\/\/orcldoug.com\/blog\/2007\/07\/04\/awr-licensing\/","url_meta":{"origin":1403,"position":3},"title":"AWR Licensing","date":"July 4, 2007","format":false,"excerpt":"I thought I'd wait for a few days to see how the open letter to Larry Ellison on the subject of AWR licencing panned out.I love AWR, ASH and I even have an increasing, slightly grudging and cynical respect for ADDM. In fact, I'm in the course of putting together\u2026","rel":"","context":"With 15 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1513,"url":"http:\/\/orcldoug.com\/blog\/2009\/07\/30\/awr-differences-report\/","url_meta":{"origin":1403,"position":4},"title":"AWR Differences Report","date":"July 30, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.This morning I had an opportunity to use one of my favourite AWR tools, the differences report. Our system has a fairly involved overnight batch schedule consisting of multiple concurrent job streams that starts at 02:00 and usually completes at about\u2026","rel":"","context":"With 12 comments","img":{"alt_text":"","src":"\/serendipity\/uploads\/awrdd2.png","width":350,"height":200},"classes":[]},{"id":1402,"url":"http:\/\/orcldoug.com\/blog\/2008\/04\/17\/mmon-sampling-ash-data\/","url_meta":{"origin":1403,"position":5},"title":"MMON Sampling ASH Data","date":"April 17, 2008","format":false,"excerpt":"When I was working on the course, I noticed this White Paper (PDF) on Oracle 10g Self-Management Framework Internals: Exploring the Automatic Workload Repository. The paper describes the way that MMON selects 1 in 10 of the ASH samples for storage in DBA_HIST_ACTIVE_SESS_HISTORY (I suppose it would be more correct\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1403","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1403"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1403\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1403"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1403"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1403"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}