Importing Multiple Databases AWR repositories

April 16th, 2011

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.


Uncategorized

  1. Trackbacks

  1. Comments

  2. November 8th, 2011 at 01:52 | #1

    For importing two AWR repositories from two copies (clones) of the same database, found it easier to just import the first AWR into the staging user and then update all the occurances of the DBID, then move the staging data to the AWR repository.
    Here is the procedure (the procedure calls awr_change_dbid.sql)

       create tablespace AWR datafile '/home/oracle/oracle/product/10.2.0/oradata/orcl/AWR_01.dbf' size 200M;
       Drop Directory AWR_DMP;
       Create Directory AWR_DMP AS 'C:\Users\Kyle\Desktop\data\awrs';
    -- create 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
       begin
         dbms_swrf_internal.awr_load(schname  => 'AWR_STAGE',
     				 dmpfile  => 'awrHMCP', -- file w/o .dmp
                                     dmpdir   => 'AWR_DMP');
       end;
    /
    -- change dbid
       def dbid=&DBID;
       @awr_change_dbid
       commit;
    -- move data
       def schema_name='AWR_STAGE'
       select  '&schema_name' from dual;
       variable schname varchar2(30);
       begin
         :schname := '&schema_name';
         dbms_swrf_internal.move_to_awr(schname => :schname);
       end;
    /
       col host_name for a30
       select distinct dbid,  db_name, instance_name, host_name from
       dba_hist_database_instance;
    
    


+ 3 = four