How to move AWR data to another repository
Connect to Source Instance as SYS
@?/rdbms/admin/awrextr.sql
The script will prompt for
dbid
Number of days worth of snapshots to display for selection (just like awrrpt.sql)
Begin Snap
End Snap
Directory Object Name for Data Pump (e.g. DUMP_DIR)
Dump File Name
Connect to Target Instance as SYS
@?/rdbms/admin/awrload.sql
The script will prompt for
Directory Object Name for Data Pump (e.g. DUMP_DIR)
Dump File Name
Schema Name for staging schema that data will be imported into (the default of AWR_STAGE is fine)
Default and Temporary tablespaces for the staging schema
@?/rdbms/admin/awrddrpi,sql (Note the 'i' which allows you to specify which database/instances to report against)
The script will prompt for
HTML or Text report format
First dbid and instance number
Number of days snapshots to display for selection from first repository
Begin and end snapshot for first repository
Second dbid and instance number
Number of days snapshots to display for selection from second repository
Begin and end snapshot for second repository
Report file name
the other way, fyi,
select dbid from v$database;
col SNAP_INTERVAL format a20
col RETENTION format a20
select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- --------------------
3258538501 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
exec dbms_workload_repository.modify_snapshot_settings(interval=>10, retention=>30*24*60);
Close AWR, the interval set to 0 turn off automatic capture snapshots
exec dbms_workload_repository.modify_snapshot_settings (interval => 0);
CREATE OR REPLACE DIRECTORY dump_dir AS '/db/pdhptlt10g/dump_dir/';
GRANT READ, WRITE ON DIRECTORY dump_dir TO EXP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY dump_dir TO IMP_FULL_DATABASE;
create user AWR_STAGE identified by AWR_STAGE
default tablespace support
temporary tablespace temp
/
grant connect,appsrole,dba to AWR_STAGE
/
select directory_name dirname, directory_path dirpath
from DBA_DIRECTORIES order by directory_name;
begin
dbms_swrf_internal.awr_extract(dmpfile => 'v9.6_C1_pdhptlt10g_AWR_3258538501_58_63_2010112514.dmp',
dmpdir => 'DUMP_DIR',
bid => 58,
eid => 63,
dbid => '3258538501');
end;
exec DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME => 'AWR_STAGE',dmpfile => 'v9.6_C1_pdhptlt10g_AWR_3258538501_58_63_2010112514',dmpdir =>'DUMP_DIR');
exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR(SCHNAME => 'AWR_STAGE');
*
ERROR at line 1:
ORA-20105: unable to move AWR data to SYS
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1811
ORA-20107: not allowed to move AWR data for local dbid
ORA-06512: at line 1
http://oracledoug.com/serendipity/index.php?/archives/1403-Moving-AWR-data.html
http://www.disperu.com/oracle10g-awr-use-and-analysis/
