使用Oracle自带脚本awrextr.sql和awrload.sql进行AWR数据迁移（转）

Oracle的AWR保存了数据库性能数据供DBA分析数据库性能。然而，访问和分析这些数据可能既耗时又消耗资源。一种有效的解决方案是使用Oracle内置工具将AWR数据传输到单独的系统进行分析。这种方法允许技术人员在家中舒适地进行诊断和提供解决方案，而不会影响生产系统性能或数据保留。
英文原文： Transporting AWR Data Using Oracle’ Built-in scripts: awrextr.sql and awrload.sql

传输AWR数据涉及两个主要步骤：

1. 从源系统导出AWR数据。
2. 将AWR数据导入目标系统。
   这里姚远给大家演示一下导入和导出的过程：

导出AWR数据

awrextr.sql脚本用于将AWR数据导出到Data Pump文件中。必须以SYS用户身份连接到数据库执行此脚本。

以下示例演示了导出过程。加粗字符表示用户输入（为了简洁，删除了一些输出）：

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

复制

导入AWR数据

在将转储文件导出并传输到目标系统后，使用awrload.sql脚本将AWR数据导入。此脚本也需要以SYS用户身份运行。

以下示例演示了导入过程：

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
复制

输出并不长，所以我没有删除任何内容。只需输入目录名称和转储文件名称，其他选项保持默认即可。

后期检查

在导入作业完成后，检查相关表以验证数据：

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
复制

可以看到这些表包含了两个数据库的AWR数据。DBID为597872496的是本地数据库，而1901052105是我们刚刚导入的源数据库的DBID。

转自：https://blog.csdn.net/weixin_43424368/article/details/140717193