Importing Multiple Databases AWR repositories
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.