Copy AWR Snapshots Data to Different Database

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.