The Automatic Workload Repository (AWR) holds valuable data for Oracle DBAs to analyze database performance. However, accessing and analyzing this data can be time-consuming and resource-intensive. To overcome these challenges, Oracle offers tools to transport AWR data to a separate environment for analysis. This approach enables technicians to perform diagnostics and provide solution from the comfort of their homes without impacting production system, and facilitates long-term data retention.

Transporting AWR data involves two main steps:

1. Exporting AWR data from the source system.


2. Import AWR data into the target system.

Exporting AWR Data

The awrextr.sql script is used to export AWR data into a Data Pump export file. This script must be executed while connected to the database as the SYS user.

The example below demonstrates the exporting process, the bold characters indicate user input (some output is removed for brevity):

SQL> **@$ORACLE_HOME/rdbms/admin/awrextr.sql**
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the	   ~
~  following information:					   ~
~     (1) database id						   ~
~     (2) snapshot range to extract				   ~
~     (3) name of directory object				   ~
~     (4) name of dump file					   ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     DB Name	  Host
------------ ------------ ------------
* 1901052105 PROD4	  dell.scutech

The default database id is the local one: '1901052105'.  To use this
database id, press <return> to continue, otherwise enter an alternative.

Enter value for dbid:**1901052105**
...

Enter value for num_days: **7
...**
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: **255**
Begin Snapshot Id specified: 255

****Enter value for end_snap: **412
...**
Enter value for directory_name: **ORACLE_BASE**
Using the dump directory: ORACLE_BASE
****
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_255_412.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for file_name:

Using the dump file prefix: awrdat_255_412
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /u01/app/oracle
|   awrdat_255_412.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:
|   /u01/app/oracle
|   awrdat_255_412.log
|

****End of AWR Extract

Importing AWR Data

After exporting and transporting the dump file to the target system, use the awrload.sql script to import the AWR data. This script also needs to be run as the SYS user.

The example below demonstrates the importing process:

SQL>  @$ORACLE_HOME/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object			      ~
~     (2) name of dump file				      ~
~     (3) staging schema name to load AWR data into	      ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name		       Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR		       /u01/app/oracle/admin/yuan/dpdump/
JAVA$JOX$CUJS$DIRECTORY$       /u01/app/oracle/product/19.0.0/db_1/javavm/admin/
OPATCH_INST_DIR 	       /u01/app/oracle/product/19.0.0/db_1/OPatch
OPATCH_LOG_DIR		       /u01/app/oracle/product/19.0.0/db_1/rdbms/log
OPATCH_SCRIPT_DIR	       /u01/app/oracle/product/19.0.0/db_1/QOpatch
ORACLE_BASE		       /u01/app/oracle
ORACLE_HOME		       /u01/app/oracle/product/19.0.0/db_1
ORACLE_OCM_CONFIG_DIR	       /u01/app/oracle/product/19.0.0/db_1/ccr/state
ORACLE_OCM_CONFIG_DIR2	       /u01/app/oracle/product/19.0.0/db_1/ccr/state
SDO_DIR_ADMIN		       /u01/app/oracle/product/19.0.0/db_1/md/admin
SDO_DIR_WORK
XMLDIR			       /u01/app/oracle/product/19.0.0/db_1/rdbms/xml
XSDDIR			       /u01/app/oracle/product/19.0.0/db_1/rdbms/xml/schema

Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: **ORACLE_BASE**

Using the dump directory: ORACLE_BASE

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

Enter value for file_name: **awrdat_255_412**

Loading from the file name: awrdat_255_412.dmp

Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.

The default staging schema name is C##AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for schema_name:

Using the staging schema name: C##AWR_STAGE

Choose the Default tablespace for the C##AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the C##AWR_STAGE users's default tablespace.  This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME 	       CONTENTS 	     DEFAULT TABLESPACE
------------------------------ --------------------- ------------------
SYSAUX			       PERMANENT	     *
USERS			       PERMANENT
YUAN			       PERMANENT

Pressing <return> will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace:

Using tablespace SYSAUX as the default tablespace for the C##AWR_STAGE

Choose the Temporary tablespace for the C##AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the C##AWR_STAGE user's temporary tablespace.

TABLESPACE_NAME 	       CONTENTS 	     DEFAULT TEMP TABLESPACE
------------------------------ --------------------- -----------------------
TEMP			       TEMPORARY	     *

Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace:

Using tablespace TEMP as the temporary tablespace for C##AWR_STAGE

... Creating C##AWR_STAGE user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /u01/app/oracle
|   awrdat_255_412.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:
|   /u01/app/oracle
|   awrdat_255_412.log
|
... Dropping C##AWR_STAGE user

End of AWR Load

The output is not very long, so I have not removed any of it. I only input the directory name and dump file name, keeping other options as default.

Post-Import Verification

After the importing job is completed, check the relevant tables to verify the data:

SQL> select dbid,count(*) from dba_hist_snapshot group by dbid;
        DBID   COUNT(*)
   597872496       2897
  1901052105        158

SQL> select dbid,count(*) from dba_hist_active_sess_history group by dbid;
        DBID   COUNT(*)
   597872496    1047107
  1901052105      14003

SQL> select dbid,count(*) from dba_hist_sqlstat group by dbid;
        DBID   COUNT(*)
   597872496     252038
  1901052105      12335

The output shows these tables holds AWR data from two databases, the DBID of 597872496 is the local database, while 1901052105 is the DBID of the source database we just imported.