1. 导出AWR
1.1 创建导出文件目录
切换到oracle用户，创建一个导出文件存放的目录
~]# su - oracle
~]$ mkdir -p /home/oracle/expawr
1.2 登录sqlplus
在Oracle用户下登录sqlplus
~]$
sqlplus / as sysdba
1.3. 在数据库中创建导出文件目录
SQL>
create directory expawr as '/home/oracle/expawr';
1.4. 在数据库中执行导出命令
SQL> @?/rdbms/admin/awrextr
~~~~~~~~~~~~~
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
------------ ------------ ------------
* 1386205165 EASYTONG fbyzhytqd_sr
v
The default database id is the local one: '1386205165'. To use this
database id, press <return> to continue, otherwise enter an alternative.
-- 输入数据库的DBID
Enter value for dbid: 1386205165
Using 1386205165 for Database ID
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
--输入要提取AWR数据的天数
Enter value for num_days: 5
Listing the last 5 days of Completed Snapshots
DB Name Snap Id Snap Started
------------ --------- ------------------
EASYTONG 8429 17 Jul 2021 00:00
8430 17 Jul 2021 01:00
8431 17 Jul 2021 02:00
……
8543 21 Jul 2021 17:00
8544 21 Jul 2021 18:00
-- 输入要提取AWR报告的开始 Snap Id
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 8429
Begin Snapshot Id specified: 8429
-- 输入要提取AWR报告的结束 Snap Id
Enter value for end_snap: 8544
End Snapshot Id specified: 8544
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR /u01/app/oracle/admin/easytongdb/dpdump/
DIR_SHR /u01/app/ora_backups
DPDATA /home/OracleBack/expdpbak
DUMP_DIR /home/dpdata
EXPAWR /home/oracle/expawr
-- 选择要导出文件存放的 Directory Name（大小写敏感）
Choose a Directory Name from the above list (case-sensitive).
Enter value for directory_name: EXPAWR
Using the dump directory: EXPAWR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_8429_8544.
To use this name, press <return> to continue, otherwise enter
an alternative.
-- 输入导出的文件名，导出文件时不需要增加.dmp后缀，系统会自己加后缀名。
Enter value for file_name: awrexp_20210721
Using the dump file prefix: awrexp_20210721
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /home/oracle/expawr
| awrexp_20210721.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:
| /home/oracle/expawr
| awrexp_20210721.dmp.log
|
End of AWR Extract
2. 导入AWR
在另外一台Oracle数据库中，数据库版本无特殊要求，可以比源数据库高，也可以比源数据库低。
我导出的是11g数据库的AWR，导入到的是18c的PDB数据库中。
2.1 创建导出文件目录
切换到oracle用户，创建一个导出文件存放的目录
~]# su - oracle
~]$ mkdir -p /home/oracle/expawr
并且将第1步中导出的AWR文件awrexp_20210721.dmp拷贝到该目录中。
2.2 登录sqlplus
在Oracle用户下登录sqlplus
~]$
sqlplus / as sysdba
2.3. 在数据库中创建导出文件目录
SQL>
create directory expawr as '/home/oracle/expawr';
2.4. 执行导入AWR命令
SQL> @?/rdbms/admin/awrload
~~~~~~~~~~
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/ykt/dpdump/AAB092FDCE1C3CD7E053670510AC6355
EXPAWR /home/oracle/expawr
EXPDPBAK /u01/orabak/
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: EXPAWR
Using the dump directory: EXPAWR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
-- 需要是导出文件不加.dmp后缀的文件名。
Enter value for file_name: awrexp_20210721
Loading from the file name: awrexp_20210721.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 AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.
Enter value for schema_name:
Using the staging schema name: AWR_STAGE
Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's default tablespace. This is the
tablespace in which the AWR data will be staged.
TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
------------------------------ --------------------- ------------------
ET_AC PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT
Pressing <return> will result in the recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: ET_AC
Using tablespace ET_AC 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
------------------------------ --------------------- -----------------------
ET_TEMP TEMPORARY
TEMP TEMPORARY *
Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: TEMP
Using tablespace TEMP as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /home/oracle/expawr
| awrexp_20210721.dmp.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:
| /home/oracle/expawr
| awrexp_20210721.dmp.log
|
... Dropping AWR_STAGE user
End of AWR Load
schemas 小插曲：schema名称必须是oracle数据库中不存在的，不然会异常退出.
2.5. 对导入的AWR快照进行验证
SQL>
SELECT snap_id,dbid,instance_number,to_char(begin_interval_time,'yyyy-MM-dd hh24:mi:ss.ff')
FROM dba_hist_snapshot
WHERE dbid= 1386205165
ORDER BY 1,3;
