Importing AWR repositories from cloned databases

November 8th, 2011

I sometimes want to import AWR repositories from cloned databases to compare performance differences between the databases which should have the same performance characteristics. Unfortunately the clones have the same DBID this importing their AWR repositories into a central AWR repository would be probelematic as I wouldn’t have a way to tell which database the data came from as the DBID is the same. So here is a procedure to change the DBID. THe procedure requires importing the data into the awr_stage user, changing the DBID, then moving the data into the AWR repository:
(see for info on normal exporting and importing of AWR repositories)

The script uses a  awr_change_dbid.sql to defer the constraint on DBID, change the DBID and then put the constraint back

   create tablespace AWR datafile '/home/oracle/oradata/AWR_01.dbf' size 200M;
   Drop Directory AWR_DMP;
   Create Directory AWR_DMP AS '/home/oracle/awr_dumps';
-- create staging user user
   drop user awr_stage cascade; 
   create user awr_stage
     identified by awr_stage
     default tablespace awr
     temporary tablespace temp;
   grant connect to awr_stage;
   alter user awr_stage quota unlimited on awr;
   alter user awr_stage temporary tablespace temp;
-- load data
     dbms_swrf_internal.awr_load(schname  => 'AWR_STAGE',
                                 dmpfile  => 'my_awr_dump', -- file w/o .dmp extension
                                 dmpdir   => 'AWR_DMP');
-- change dbid
-- move data
   def schema_name='AWR_STAGE'
   select  '&schema_name' from dual;
   variable schname varchar2(30);
     :schname := '&schema_name';
     dbms_swrf_internal.move_to_awr(schname => :schname);
-- verify the new DBID in repository
   col host_name for a30
   select distinct dbid,  db_name, instance_name, host_name from



  1. Trackbacks


  2. Henry Poras
    November 9th, 2011 at 17:00 | #1

    I had some problems in the past when trying to save/import AWR repositories when testing runs using flashback database. Statspack imports are OK, but the PK for lots of the AWR tables are dbid and snap_id. These are duplicate values after flashing back, so it is difficult to compare different runs. I put in an enhancement request to add the incarnation to the PK, but nothing came of it.

You must be logged in to post a comment.