If you can use following to drop/recreate/import/enable AWR in the target
database.
You have to truncate the few AWR control tables before you import AWR extracted
data into database. Otherwise it gives errors. I have done it quite a few times
below procedure works
Version: 11gR2
----------------------------------------------------------------
-- Drop AWR
----------------------------------------------------------------
sqlplus / as sysdba
spool awr_drop.log
alter system set "_awr_restrict_mode" = TRUE scope=spfile sid='*';
shutdown immediate
startup
@?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;
spool off
----------------------------------------------------------------
-- Create AWR
----------------------------------------------------------------
sqlplus / as sysdba
spool awr_create.log
@?/rdbms/admin/catawr.sql
@ ?/rdbms/admin/utlrp.sql
@ ?/rdbms/admin/execsvrm.sql
alter system reset "_awr_restrict_mode" scope=spfile sid='*';
shutdown immediate
startup
exec dbms_workload_repository.create_snapshot;
select snap_id from dba_hist_snapshot;
exec dbms_workload_repository.create_snapshot;
select snap_id from dba_hist_snapshot;
spool off
----------------------------------------------------------------
-- Import AWR Data
----------------------------------------------------------------
TRUNCATE TABLE sys.wrm$_wr_control;
TRUNCATE TABLE sys.wrm$_wr_usage;
TRUNCATE TABLE sys.wrm$_snapshot_details;
impdp "'/ as sysdba'" parallel=16 directory=BETA_REFRESH dumfile=AWR_exp.dmp
logfile=AWR.imp.log
----------------------------------------------------------------
-- Enable AWR Data
----------------------------------------------------------------
sqlplus / as sysdba
spool awr_drop.log
alter system reset "_awr_restrict_mode" = TRUE scope=spfile sid='*';
shutdown immediate
startup
exec dbms_workload_repository.create_snapshot;
exec dbms_workload_repository.create_snapshot;
select max(snap_id), max(end_interval_time) max_time, min(snap_id),
min(end_interval_time) min_time
from dba_hist_snapshot;
Regards,
Vishal Gupta
From: <oracle-l-bounce@xxxxxxxxxxxxx> on behalf of Wilhelm Klotz
<willyk@xxxxxxxxxxx>
Reply-To: <willyk@xxxxxxxxxxx>
Date: Tuesday, 12 September 2017 at 07:52
To: <oracle-l@xxxxxxxxxxxxx>, <carlospena999@xxxxxxxxx>
Subject: Re: awrextr
You do not need to run the reports. awrextr extracts all of the awr data, when
you need it then you import it back to a database and run the required reports.
Regards
Wilhelm
-----Original-Nachricht-----
Von: "Cee Pee" <carlospena999@xxxxxxxxx>
An: oracle-l@xxxxxxxxxxxxx
Datum: 09/12/17 03:27
Betreff: awrextr
Trying to extract lots of data from a multinode RAC prod system using awrextr.
I want to know if I need to run the report from every node for the same period.
For some reason I am unable to find answer for that in the manuals or online. I
am assuming one run from one node is enough, can someone pl confirm.
CP