Oracle技术之AWR概述—导出
AWR的SNAP生成后，不一定要在原数据库上生成报告，可以利用AWR提供的导出、导入功能，将AWR数据迁移到测试数据库上进一步分析。AWR的导出并不复杂，利用有权限的用户调用$ORACLE_HOME/rdbms/admin/awrextr.sql脚本就可以了：
SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Disclaimer: This SQL/Plus script. should only be called under
the guidance of Oracle Support.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~
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> ~ (2) snapshot range to extract ~
~ (3) name of directory object ~
~ (4) name of dump file ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB> ------------ ------------ ------------
* 96312462 TESTRAC racnode1
* 96312462 TESTRAC racnode2
* 96312462 TESTRAC racnode3
3944144691 CCICDB ccicdbsrv5
The default database>
database> 输入dbid的值:
Using 96312462 for Database> 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.Pressingwithout
specifying a number lists all completed snapshots.
输入num_days的值:3
Listing the last 3 days of Completed Snapshots
DB Name Snap> ------------ --------- ------------------
TESTRAC 2758 14 11月2010 00:00
2759 14 11月2010 01:00
2760 14 11月2010 02:00
.
.
.
2780 14 11月2010 22:00
2781 14 11月2010 23:00
2782 15 11月2010 00:00
2783 15 11月2010 01:00
2784 15 11月2010 02:00
2785 15 11月2010 03:00
2786 15 11月2010 04:00
2787 15 11月2010 05:00
2788 15 11月2010 06:00
2789 15 11月2010 07:00
2790 15 11月2010 08:00
2791 15 11月2010 09:00
2792 15 11月2010 10:00
2793 15 11月2010 11:00
2794 15 11月2010 12:00
2795 15 11月2010 13:00
2796 15 11月2010 14:00
2797 15 11月2010 15:00
2798 15 11月2010 16:00
2799 15 11月2010 17:00
2800 15 11月2010 18:00
2801 15 11月2010 19:00
2802 15 11月2010 20:00
2803 15 11月2010 21:00
2804 15 11月2010 22:00
2805 15 11月2010 23:00
2806 16 11月2010 00:00
.
.
.
2822 16 11月2010 16:00
2823 16 11月2010 17:00
Specify the Begin and End Snapshot> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入begin_snap的值:2782
Begin Snapshot> 输入end_snap的值:2806
End Snapshot> Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
ADMIN_DIR /data/oracle/product/10.2/database/md/admin
DATA_PUMP_DIR /data/oracle/product/10.2/database/rdbms/log/
D_AWR /data1/awr
ORACLE_OCM_CONFIG_DIR /data/oracle/product/10.2/database/ccr/state
WORK_DIR /data/oracle/product/10.2/database/work
Choose a Directory Name from the above list (case-sensitive).
输入directory_name的值:D_AWR
Using the dump directory: D_AWR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_2782_2806.
To use this name, pressto continue, otherwise enter
an> 输入file_name的值:awr_testrac_2782_2806
Using the dump file prefix: awr_testrac_2782_2806
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|The AWR extract dump file will be located
|in the following directory/file:
| /data1/awr
| awr_testrac_2782_2806.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:
| /data1/awr
| awr_testrac_2782_2806.log
|
WHERE (dbid, snap_id) IN (SELECT dbid, snap_id FROM SYS.WRM$_SNAPSHOT WHERE DBID = 96312462 AND SNAP_ID >=
2782 AND SNAP_IDhost ls -l /data1/awr/awr_testrac*
-rw-rw---- 1 oracle oinstall 17158144 Nov 16 17:32 /data1/awr/awr_testrac_2782_2806.dmp
-rw-rw-r-- 1 oracle oinstall 25939 Nov 16 17:32 /data1/awr/awr_testrac_2782_2806.log
其中dmp文件就是脚本利用数据泵导出的snap数据，将这个文件拷贝到目标数据库上执行导入，就可以实现AWR数据的迁移。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]