Transporting Automatic Workload Repository Data to Another System

Oracle Database enables you to transport AWR data between systems. This is useful in cases where you want to use a separate system to perform analysis of AWR data, so as to reduce the overhead caused by performance analysis on a production system.

To transport AWR data from one system to another, first export the AWR data from the database on the source system, and then import it into the database on the target system.

This section contains the following topics:

Exporting AWR Data

The awrextr.sql script exports AWR data for a range of snapshots from the database into a Data Pump export file. After it is created, you can transport this dump file to another database where you can import the exported AWR data. To run the awrextr.sql script, you must be connected to the database as the SYS user.

To export AWR data:

At the SQL prompt, enter:
```
@$ORACLE_HOME/rdbms/admin/awrextr.sql
```
A list of the databases in the AWR schema is displayed.
Specify the database from which AWR data needs to be exported:
```
Enter value for db_id: 1377863381
```
In this example, the database with the database identifier of 1377863381 is specified.
Specify the number of days for which you want to view all the snapshot IDs:
```
Enter value for num_days: 2
```
In this example, all the snapshots captured in the last 2 days are displayed.
Define the range of snapshots for which AWR data needs to be exported by specifying the beginning and the ending snapshot IDs:
```
Enter value for begin_snap: 30
Enter value for end_snap: 40
```
In this example, the snapshot ID of 30 is specified as the beginning snapshot, and the snapshot ID of 40 is specified as the ending snapshot.

A list of directory objects is displayed.
Specify the directory object pointing to the directory where the export dump file needs to be stored:
```
Enter value for directory_name: DATA_PUMP_DIR
```
In this example, the directory object DATA_PUMP_DIR is specified that points to the directory ORACLE_HOME/rdbms/log, where ORACLE_HOME is /u01/app/oracle/product/database_release_number/dbhome_1.
Specify a name for the export dump file without the file extension. By default, the file extension of .dmp is used.
```
Enter value for file_name: awrdata_30_40
```
In this example, an export dump file named awrdata_30_40.dmp is created in the directory specified in the directory object DATA_PUMP_DIR:
```
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/product/database_release_number/dbhome_1/rdbms/log/awrdata_30_40.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:58:20
```
Depending on the amount of AWR data that must be exported, the AWR export operation may take a while to complete. After the dump file is created, you can use Data Pump to transport the file to another system.

Importing AWR Data

After the export dump file is transported to the target system, import the exported AWR data using the awrload.sql script. The awrload.sql script creates a staging schema where the snapshot data is transferred from the Data Pump file into the database. The data is then transferred from the staging schema into the appropriate AWR tables. To run the awrload.sql script, you must be connected to the database as the SYS user.

To import AWR data:

At the SQL prompt, enter:
```
@$ORACLE_HOME/rdbms/admin/awrload.sql
```
A list of directory objects is displayed.
Specify the directory object pointing to the directory where the export dump file is located:
```
Enter value for directory_name: DATA_PUMP_DIR
```
In this example, the directory object DATA_PUMP_DIR is specified that points to the directory where the export dump file is located.
Specify the name of the export dump file without the file extension. By default, the file extension of .dmp is used.
```
Enter value for file_name: awrdata_30_40
```
In this example, the export dump file named awrdata_30_40.dmp is selected.

First the AWR data is imported into a set of temporary tables for the current user and then it is transferred to the AWR tables in the SYS schema:

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 113 CONSTRAINT objects in 11 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 1 REF_CONSTRAINT objects in 1 seconds
Job "SYS"."SYS_IMPORT_FULL_03" successfully completed at 09:29:30
... Dropping AWR_STAGE user
End of AWR Load

Depending on the amount of AWR data that must be imported, the AWR import operation may take a while to complete. After AWR data is imported, the temporary table will be dropped automatically.