The migration or import of AWR metadata to a different database

We can migrate (import) the AWR metadata to a different database, import the lower version to a higher version, generate an AWR report with a higher version of the database, and use some new features such as

SQL ordered by physical Reads (unoptimized)
SQL ordered by User I/O Wait time

Export :

Testnode:/home/oracle$export oracle_sid=wy1
Testnode:/home/oracle$sqlplus/as SYSDBA
Sql> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script would extract the AWR data for a range of snapshots ~
~ into a dump file. The script would 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
------------ ------------ ------------
* 3988169241 WY Testnode1
* 3988169241 WY Testnode2

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

Enter value for dbid:

Using 3988169241 for Database ID

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) would result in the most recent
(n) days of snapshots being listed. Pressing <return> without
Specifying a number lists all completed snapshots.

Enter value for Num_days:

Listing all completed Snapshots

...
DB Name snap Id snap Started
------------ --------- ------------------
WY 4706 2015 18:00
4707 19:00 <<<
4708 20:00 <<<

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:4707 <<<< start snap
Begin Snapshot Id specified:4707 <<<< End Snap

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

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name Directory Path
------------------------------ -------------------------------------------------
data_pump_dir/u01/app/oracle/product/11.2.0/db_1/rdbms/log/
EXPDP/TMP/EXPDP <<<<<<<<<<<<<<<<
Oracle_ocm_config_dir/u01/app/oracle/product/11.2.0/db_1/ccr/state
Oracle_ocm_config_dir2/u01/app/oracle/product/11.2.0/db_1/ccr/state
Xmldir/u01/app/oracle/product/11.2.0/db_1/rdbms/xml

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

Enter value for DIRECTORY_NAME:EXPDP <<<<<<<<< enter directory, and to the right is the physical path to the directory. Note: If the snap span is large, Requires a larger space to store files

Using the Dump DIRECTORY:EXPDP

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

Enter value for file_name:

Using the dump file prefix:awrdat_4707_4708
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file would be located
| In the following directory/file:
| /TMP/EXPDP <<<< Catalogue
| Awrdat_4707_4708.dmp <<<< file name
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| AWR Extract Started ...
|
| This operation would take a few moments. The
| Progress of the AWR extract operation can be
| Monitored in the following directory/file:
| /tmp/expdp
| Awrdat_4707_4708.log
|

Import:
The import process is similar

Sql> @?/rdbms/admin/awrload
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script would load the AWR data from a dump file. The ~
~ script would 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
------------------------------ -------------------------------------------------
Agree_awrreport_dir/tmp/xunjian
data_pump_dir/u01/app/oracle/admin/orcl/dpdump/
EXPDP/ARCH/EXPDP <<<<<<<<<<<<<<<<<<<<<<
ora10g_pump/u01/app/oracle/datapump/ora10g
Oracle_ocm_config_dir/u01/app/oracle/product/11.2.0/db_1/ccr/state
Xmldir/u01/app/oracle/product/11.2.0/db_1/rdbms/xml

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

Enter value for DIRECTORY_NAME:EXPDP <<<<<<<<<<<<<<<< Enter directory name
Using the Dump DIRECTORY:EXPDP

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

Enter value for file_name:awrdat_4707_4708 <<<<<<<<<<<<<<< input file name (without suffix . DMP)

Loading from the file name:awrdat_4707_4708.dmp

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

The default staging schema name is awr_stage.
To use the this name, press <return> to continue, otherwise enter
An alternative.

Enter value for schema_name: <<<<<<<<<<<<<< return, take default value

Using the Staging schema Name:awr_stage

Choose the Default tablespace for the awr_stage user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the Awr_stage users ' s default tablespace. The
Tablespace in which the AWR data would be staged.

Tablespace_name
--------------------------------------------------------------------------------
CONTENTS DEFAULT tablespace
--------------------------- ------------------
Sysaux
PERMANENT *

USERS
PERMANENT

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

Enter value for Default_tablespace: <<<<<<<<<<<<< return, take default value

Using tablespace Sysaux as the default tablespace for the Awr_stage

Choose the temporary tablespace for the awr_stage user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the Awr_stage user ' s temporary tablespace.

Tablespace_name
--------------------------------------------------------------------------------
CONTENTS DEFAULT TEMP tablespace
--------------------------- -----------------------
TEMP
Temporary *

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

Enter value for Temporary_tablespace: <<<<<<<<<<<<< return, take default value

Using tablespace TEMP as the temporary tablespace for Awr_stage

... Creating awr_stage User

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| Directory/file:
| /arch/expdp
| Awrdat_4707_4708.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| AWR Load Started ...
|
| This operation would take a few moments. The
| Progress of the AWR load operation can be
| Monitored in the following directory/file:
| /arch/expdp
| Awrdat_4707_4708.log
|
...

---------------------------------------------------

Daoxing is still shallow, welcome to shoot bricks.

Reprint please indicate the source.

The migration or import of AWR metadata to a different database