Transfer AWR data from one system to another in Oracle

Sometime we need to compare the AWR report of two different system, this process will help.

Extract AWR data
For Extract the AWR data from source database run the awrextr.sql script which extracts AWR data for a range of snapshots from the database into a Data Pump export file.

1. Run the following script for extract AWR:

@?/rdbms/admin/awrextr.sql;


2. Script ask for select DBID

Enter value for dbid:


3. Enter the number of days backup export:

Enter value for num_days: 2


4. It will list the 2 days snapshot in AWR. Choose the begin and end snapshot for export:

Enter value for begin_snap: 76
Begin Snapshot Id specified: 76

Enter value for end_snap: 86
End Snapshot Id specified: 86


5. List the Directory present in Database, Choose the directory location and dump file name:

Enter value for directory_name: ORACLE_HOME
.....
.....
Enter value for file_name:


6. Now dump is generated.

The AWR extract dump file will be located
| in the following directory/file:
| C:\Oracle\dbhomeXE
| awrdat_76_86.dmp

See the example of complete AWR extract log file: AWR EXTRACT PROCESS

Load the AWR data to target
For loading the extracted AWR data with awrload.sql script. It will first create a staging schema where the snapshot data is transferred
from the Data Pump file into the database

1. Run the AWRload.sql script for start loading data.

@?/rdbms/admin/awrload.sql


2. Specify the directory name where export file exists.

Enter value for directory_name:


3. Put the prefix of name of dump file:

Enter value for file_name:


4. Specify the name of staging schema where data loaded i.e AWRSTAGE

Enter value for schema_name:


5. Specify the default or temporary tablespace.

Enter value for default_tablespace: SYSAUX
-----------
Enter value for temporary_tablespace: TEMP


6. Loading of data is successful.

*** AWR Load Started ...
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| C:\Oracle\dbhomeXE
| AWRDAT_76_86.log


7. Check the log file on load process as:AWR LOAD EXAMPLE