Copy AWR Snapshots Data to Different Database

Copy AWR snapshots data from production to test database, where performance comparison can be held.

EXTRACT AWR DATA FROM SOURCE

Connect to source database as SYS user.

@$ORACLE_HOME/rdbms/admin/awrextr.sql
......
.....
....
...
Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_92100_92110.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for file_name: awrdat_92100_92110

Using the dump file prefix: awrdat_92100_92110
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /u02/app/oracle/admin/TESTDB/dpdump/
| awrdat_92100_92110.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Extract Started ...
|
| This operation will take a few moments. The
| progress of the AWR extract operation can be
| monitored in the following directory/file:
| /u02/app/oracle/admin/TESTDB/dpdump/
| awrdat_92100_92110.log
|

End of AWR Extract

The script will prompt and ask for the following information :

  1. dbid
  2. The number of days of snapshots to choose from
  3. Begin Snapshot Id
  4. End Snapshot ID
  5. Value for directory_name
  6. Dump file name to be created ( prefix only ): awrdat_92100_92110

LOAD AWR DATA INTO TARGET

Connect to target database as SYS user.

@$ORACLE_HOME/rdbms/admin/awrload.sql

......
.....
....
...
... Creating AWR_STAGE_LOAD user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /tmp
| awrdat_92100_92110.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** 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:
| /tmp
| awrdat_92100_92110.log
|

The script will prompt and ask for :

  1. The name of directory object.
  2. The name of dump file.
  3. The staging schema name to load AWR data into. ( will be dropped automatically after AWR loading ).
  4. The default tablespace name for staging schema.
  5. The temporary tablespace name.