迁移AWR快照到其他库

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">  通常迁移AWR只需要两个步骤，非常简单</span>

  1、以SYS登陆到源库，执行脚本将AWR数据导出（datapump导出），这个步骤要求我们输入以下内容

 （1）dbid
 （2）选择导出snapshot的天数
 （3）开始的Snapshot ID
 （4）结束的Snapshot ID
 （5）目录名称
 （6）导出文件名称

 

[oracle@md ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 28 15:35:26 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/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
------------ ------------ ------------
* 1393943222 ORCL	  md

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

Enter value for dbid: 

Using 1393943222 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.


Enter value for num_days: 

Listing all Completed Snapshots

DB Name        Snap Id	  Snap Started
------------ --------- ------------------
ORCL		   551 26 Oct 2016 11:53
		   552 26 Oct 2016 13:00
		   553 27 Oct 2016 16:30
		   554 27 Oct 2016 18:00
		   555 27 Oct 2016 19:00
		   556 27 Oct 2016 20:00
		   557 27 Oct 2016 21:00
		   558 27 Oct 2016 22:00


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 551
Begin Snapshot Id specified: 551

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


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

Directory Name		       Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR		       /u01/app/oracle/admin/orcl/dpdump/
D_OUTPUT		       /home/oracle/exp
MD			       /tmp
ORACLE_OCM_CONFIG_DIR	       /u01/app/oracle/product/11.2.0/db_1/ccr/hosts/md/
			       state

ORACLE_OCM_CONFIG_DIR2	       /u01/app/oracle/product/11.2.0/db_1/ccr/state
QUEST_SOO_ADUMP_DIR	       /u01/app/oracle/admin/orcl/adump/
QUEST_SOO_BDUMP_DIR	       /u01/app/oracle/diag/rdbms/orcl/orcl/trace/
QUEST_SOO_CDUMP_DIR	       /u01/app/oracle/diag/rdbms/orcl/orcl/cdump/
QUEST_SOO_UDUMP_DIR	       /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

Directory Name		       Directory Path
------------------------------ -------------------------------------------------
UTLFILE 		       /soft/archivelog
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: MD

Using the dump directory: MD

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

Enter value for file_name: awrdata

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

End of AWR Extract

2、以SYS登陆到目标库，执行脚本将步骤1中导出的数据导入到目标库，需要输入以下内容

 （1）目录名称
 （2）文件名称
 （3）导入快照数据的 staging schema，会在导入结束后删除
 （4）staging schema的默认表空间
 （5）staging schema的默认临时表空间

SQL> @?/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/oracle/product/11.2.0/db/rdbms/log/
ORACLE_OCM_CONFIG_DIR	       /u01/oracle/product/11.2.0/db/ccr/hosts/rac1/stat
			       e

ORACLE_OCM_CONFIG_DIR2	       /u01/oracle/product/11.2.0/db/ccr/state

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

Enter value for directory_name: MD

Using the dump directory: MD

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: awrdata

Loading from the file name: awrdata.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
------------------------------ --------- ------------------
SYSAUX			       PERMANENT *
USERS			       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 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> 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 AWR_STAGE


... Creating AWR_STAGE user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /soft
|   awrdata.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:
|   /soft
|   awrdata.log
|
... Dropping AWR_STAGE user

End of AWR Load

此时，我们再生成AWR时，会提示我们选择dbid（由于库中包含了其他库的awr数据）

SQL> @?/rdbms/admin/awrrpt

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name	 Inst Num Instance
----------- ------------ -------- ------------
  923850398 RACDB		1 racdb1


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: 

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<strong><span style="color:#ff0000;">
   DB Id     Inst Num DB Name	   Instance	Host
------------ -------- ------------ ------------ ------------
  1393943222	    1 ORCL	   orcl 	md
* 923850398	    1 RACDB	   racdb1	rac1</span></strong>

Using  923850398 for database Id
Using	       1 for instance number


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.


Enter value for num_days: