Gavin Soorma has a nice blog on exporting and importing AWR repositories http://gavinsoorma.com/2009/07/exporting-and-importing-awr-snapshot-data/

I  thought I would use this technique to load the AWR repositories of two databases I wanted to compare.  I  loaded up the two repositories onto my laptop databases, and low and behold I could only find one new DBID in the data. Turns out I have two databases on different machines which are both the copy of the same database, so the have the same DBID.
I didn’t realize this until I had imported the two different AWR repositories into the database on my laptop but only found one new DBID. Then I realized, “oh they have the same DBID!”
So, OK, I’ll tell awrload to load the data into different schemas.
Well, awrload drops the staging user. First step was to stop awrload from dropping the staging user. I went  into @?/rdbms/admin/awrload.sql and commented out the drop user section.
I then ran awrload and tried to give it a unique name:

begin
*
ERROR at line 1:
ORA-20102: user name 'toto' is invalid
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 412
ORA-01918: user '' does not exist
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1713
ORA-06512: at line 3

it only seemed to accept the default user AWR_STAGE
ok, so I load with AWR_STAGE and try to rename the user using

update user$ set name='T_NEWNAME' where name='T';

see http://arjudba.blogspot.com/2008/05/how-to-rename-user-through-oracle.html

no go, awrload gives me an error AWR_STAGE already exists even after the rename.
OK, so I export the data from the first AWR_STAGE user

 expdp schemas=awr_stage
 create a new user:
 create user usrtst
 identified by usrtst
 default tablespace sysaux
 temporary tablespace  temp;
 grant connect to usrtst;
 alter user usrtst quota unlimited on sysaux;

then import the data:
impdp REMAP_SCHEMA=awr_stage:usrtst directory=AWR_DATA DUMPFILE=usrtst_awr.DMP FULL=y
I do this same technique with the second database manually dropping the AWR_STAGE user between awrload loads.

 create user chprod
 identified by chprod
 default tablespace sysaux
 temporary tablespace  temp;
 grant connect to chprod;
 alter user chprod quota unlimited on sysaux;
 impdp REMAP_SCHEMA=awr_stage:chprod directory=AWR_DATA DUMPFILE=chprod_awr.DMP FULL=y

Now I have two schemas with the AWR data from the two different databases.
Unfortunately now I don’t have the benefit of the views on the WRH tables, so I have to create them myself.
First I have to give the new users access to some views:

 grant create view to chprod;
 grant create view to usrtst;
 grant select on sys.v_$event_name to usrtst;
 grant select on sys.v_$event_name to chprod;

Now I connect as each user and create an ASH view

 create or replace view v$ash as select
 en.name  event,
 decode(wait_time,0,'WAITING','ON CPU') session_state,
 ash.*
 from
 WRH$_ACTIVE_SESSION_HISTORY ash,
 v$event_name en
 where en.event_id = ash.event_id
 /

The “event_id” column comes in handy and I don’t have to work about event# mapping to different names.

Now I can runs some of my  favorite queries on the ASH data at least. More work to be done for sure to figure out the best method consolidating multiple AWR repositories with the same DBID.

Kyle Hailey
Uncategorized