千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

AWR 导出/导入/生成报告

发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，客户的生产系统总是那么重要，一般不会让你长时间的在他们的生产机器上做操作，但是分析和生成AWR报告都是一个相对耗时的工作，当然只生成一个AWR报告是不费时的，但是要对系统进行详细分析的时候，我们可能需
千家信息网最后更新 2026年11月01日AWR 导出/导入/生成报告客户的生产系统总是那么重要，一般不会让你长时间的在他们的生产机器上做操作，但是分析和生成AWR报告都是一个相对耗时的工作，当然只生成一个AWR报告是不费时的，但是要对系统进行详细分析的时候，我们可能需要的是一段时间内的所有AWR数据，需要根据实际情况进行选取。这时我们就需要将客户的AWR数据导出，然后进行分析，这个操作主要涉及AWR数据导出、导入和生成报告三个阶段，下面对每个步骤进行详细描述。
1、导出

  1. SQL> @?/rdbms/admin/awrextr.sql
  2. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  4. Disclaimer: This SQL/Plus script should only be called under
  5. the guidance of Oracle Support.
  6. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  7. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


  10. ~~~~~~~~~~~~~
  11. AWR EXTRACT
  12. ~~~~~~~~~~~~~
  13. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  14. ~ This script will extract the AWR data for a range of snapshots ~
  15. ~ into a dump file. The script will prompt users for the ~
  16. ~ following information: ~
  17. ~ (1) database id ~
  18. ~ (2) snapshot range to extract ~
  19. ~ (3) name of directory object ~
  20. ~ (4) name of dump file ~
  21. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


  24. Databases in this Workload Repository schema
  25. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  27. DB Id DB Name Host
  28. ------------ ------------ ------------
  29. * 2182516689 GYL rac01
  30. * 2182516689 GYL rac02

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

  35. Enter value for dbid: 2182516689 <<<<<<<输入DBID

  37. Using 2182516689 for Database ID


  40. Specify the number of days of snapshots to choose from
  41. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  42. Entering the number of days (n) will result in the most recent
  43. (n) days of snapshots being listed. Pressing <return> without
  44. specifying a number lists all completed snapshots.


  47. Enter value for num_days: 7 <<<<<<<输入需要导出数据的天数

  49. Listing the last 7 days of Completed Snapshots

  51. DB Name Snap Id Snap Started
  52. ------------ --------- ------------------
  53. GYL 37 07 Nov 2014 10:05
  54. 38 07 Nov 2014 11:00
  55. 39 07 Nov 2014 12:00
  56. 40 07 Nov 2014 13:00
  57. 41 07 Nov 2014 14:00
  58. 42 07 Nov 2014 15:00
  59. 43 07 Nov 2014 16:00
  60. 44 10 Nov 2014 13:51
  61. 45 10 Nov 2014 15:00
  62. 46 10 Nov 2014 16:00
  63. 47 12 Nov 2014 14:11

  65. DB Name Snap Id Snap Started
  66. ------------ --------- ------------------
  67. GYL 48 12 Nov 2014 14:37
  68. 49 12 Nov 2014 16:00
  69. 50 12 Nov 2014 17:00
  70. 51 12 Nov 2014 18:00
  71. 52 13 Nov 2014 10:16
  72. 53 13 Nov 2014 11:00
  73. 54 13 Nov 2014 12:00
  74. 55 13 Nov 2014 13:00
  75. 56 13 Nov 2014 14:00
  76. 57 13 Nov 2014 16:21


  79. Specify the Begin and End Snapshot Ids
  80. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  81. Enter value for begin_snap: 52 <<<<<<< 输入起始snap_id
  82. Begin Snapshot Id specified: 52

  84. Enter value for end_snap: 57 <<<<<<< 输入结束snap_id
  85. End Snapshot Id specified: 57


  88. Specify the Directory Name
  89. ~~~~~~~~~~~~~~~~~~~~~~~~~~

  91. Directory Name Directory Path
  92. ------------------------------ -------------------------------------------------
  93. DATA_PUMP_DIR /oracle/app/oracle/product/10.2/db_1/rdbms/log/
  94. ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/10.2/db_1/ccr/state

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

  98. Enter value for directory_name: DATA_PUMP_DIR

  100. Using the dump directory: DATA_PUMP_DIR <<<<<<< 指定一个存放导出数据的directory（如果没有需要自己建立一个directory）

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

  108. Enter value for file_name: gyl_rac01

  110. ...........省略部分输出.............
  111. Master table \"SYS\".\"SYS_EXPORT_TABLE_01\" successfully loaded/unloaded
  112. ******************************************************************************
  113. Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  114. /oracle/app/oracle/product/10.2/db_1/rdbms/log/gyl_rac01.dmp
  115. Job \"SYS\".\"SYS_EXPORT_TABLE_01\" successfully completed at 12:38:07
2、导入
先将上面生成的dump文件拷贝到目标机器的一个directory下，然后按如下操作进行导入。

  1. SQL> @$ORACLE_HOME/rdbms/admin/awrload.sql
  2. ~~~~~~~~~~
  3. AWR LOAD
  4. ~~~~~~~~~~
  5. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  6. ~ This script will load the AWR data from a dump file. The ~
  7. ~ script will prompt users for the following information: ~
  8. ~ (1) name of directory object ~
  9. ~ (2) name of dump file ~
  10. ~ (3) staging schema name to load AWR data into ~
  11. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  13. Specify the Directory Name
  14. ~~~~~~~~~~~~~~~~~~~~~~~~~~

  16. Directory Name Directory Path
  17. ------------------------------ -------------------------------------------------
  18. DATA_FILE_DIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
  19. a/sales_history/

  21. DATA_PUMP_DIR /oracle/app/oracle/admin/gyl/dpdump/
  22. LOG_FILE_DIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
  23. a/log/

  25. MEDIA_DIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
  26. a/product_media/

  28. ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/db_1/ccr/state

  30. Directory Name Directory Path
  31. ------------------------------ -------------------------------------------------
  32. SS_OE_XMLDIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
  33. a/order_entry/

  35. SUBDIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
  36. a/order_entry//2002/Sep

  38. XMLDIR /oracle/app/oracle/product/11.2.0/db_1/rdbms/xml

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

  42. Enter value for directory_name: DATA_PUMP_DIR <<<<<<< 输入dump所在directory

  44. Using the dump directory: DATA_PUMP_DIR

  46. Specify the Name of the Dump File to Load
  47. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  48. Please specify the prefix of the dump file (.dmp) to load:

  50. Enter value for file_name: gyl_rac01 <<<<<<< 输入dump文件名，不要带后缀

  52. Loading from the file name: gyl_rac01.dmp

  54. Staging Schema to Load AWR Snapshot Data
  55. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  56. The next step is to create the staging schema
  57. where the AWR snapshot data will be loaded.
  58. After loading the data into the staging schema,
  59. the data will be transferred into the AWR tables
  60. in the SYS schema.


  63. The default staging schema name is AWR_STAGE.
  64. To use this name, press <return> to continue, otherwise enter
  65. an alternative.

  67. Enter value for schema_name: AWR_STAGE <<<<<<< 输入一个schema名，用于导入，导入结束后自动会删除该schema

  69. Using the staging schema name: AWR_STAGE

  71. Choose the Default tablespace for the AWR_STAGE user
  72. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  73. Choose the AWR_STAGE users's default tablespace. This is the
  74. tablespace in which the AWR data will be staged.

  76. TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
  77. ------------------------------ --------- ------------------
  78. EXAMPLE PERMANENT
  79. SYSAUX PERMANENT *
  80. TEST PERMANENT
  81. USERS PERMANENT

  83. Pressing will result in the recommended default
  84. tablespace (identified by *) being used.

  86. Enter value for default_tablespace: SYSAUX <<<<<<< 输入存放AWR数据的表空间名

  88. Using tablespace SYSAUX as the default tablespace for the AWR_STAGE


  91. Choose the Temporary tablespace for the AWR_STAGE user
  92. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  93. Choose the AWR_STAGE user\'s temporary tablespace.

  95. TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
  96. ------------------------------ --------- -----------------------
  97. TEMP TEMPORARY *

  99. Pressing <return> will result in the database\
3、生成报告
在目标机器上生成报告

  1. SQL> @?/rdbms/admin/awrrpti.sql

  3. Specify the Report Type
  4. ~~~~~~~~~~~~~~~~~~~~~~~
  5. Would you like an HTML report, or a plain text report?
  6. Enter 'html' for an HTML report, or 'text' for plain text
  7. Defaults to 'html'
  8. Enter value for report_type: html

  10. Type Specified: html


  13. Instances in this Workload Repository schema
  14. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  16. DB Id Inst Num DB Name Instance Host
  17. ------------ -------- ------------ ------------ ------------
  18. * 2184766987 1 GYL gyl oracle11g
  19. 2182516689 2 GYL gyl2 rac02
  20. 2182516689 1 GYL gyl1 rac01

  22. Enter value for dbid: 2182516689 <<<<<<< 输入dbid
  23. Using 2182516689 for database Id
  24. Enter value for inst_num: 1 <<<<<<< 输入inst_num号
  25. Using 1 for instance number


  28. Specify the number of days of snapshots to choose from
  29. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  30. Entering the number of days (n) will result in the most recent
  31. (n) days of snapshots being listed. Pressing <return> without
  32. specifying a number lists all completed snapshots.


  35. Enter value for num_days: 2 <<<<<<< 输入生成报告的天数

  37. Listing the last 2 days of Completed Snapshots

  39. Snap
  40. Instance DB Name Snap Id Snap Started Level
  41. ------------ ------------ --------- ------------------ -----
  42. gyl1 GYL 52 13 Nov 2014 10:16 1
  43. 53 13 Nov 2014 11:00 1
  44. 54 13 Nov 2014 12:00 1
  45. 55 13 Nov 2014 13:00 1
  46. 56 13 Nov 2014 14:00 1

  48. 57 13 Nov 2014 16:21 1



  52. Specify the Begin and End Snapshot Ids
  53. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  54. Enter value for begin_snap: 52 <<<<<<< 输入起始snap_id
  55. Begin Snapshot Id specified: 52

  57. Enter value for end_snap: 56 <<<<<<< 输入结束snap_id
  58. End Snapshot Id specified: 56



  62. Specify the Report Name
  63. ~~~~~~~~~~~~~~~~~~~~~~~
  64. The default report file name is awrrpt_1_52_56.html. To use this name,
  65. press <return> to continue, otherwise enter an alternative.

  67. Enter value for report_name: /oracle/app/oracle/admin/gyl/dpdump/awr_rac.html
整个过程都很简单，但有几点需要注意：
1、不能将同一个数据库的AWR数据导出后再导入到自己，这样会遇到下面的错误
ERROR at line 1:
ORA-20105: unable to move AWR data to SYS
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 2950
ORA-20107: not allowed to move AWR data for local dbid
ORA-06512: at line 3
2、在导入的时候，在输入值时，尽量用大写；
3、如果不再需要的数据，可以用如下存储过程删除：
SQL> exec DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE(bdid); --将dbid换成需要删除的dbid号



很赞哦！
输入 数据 报告 生成 机器 分析 天数 客户 文件 时候 目标 系统 过程 生产 起始 重要 三个 后缀 大写 实际 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 广州大一互联网络科技有限公司 互联网科技巨头监管 上海挚品互联网科技 贷财互联网科技 云电互联网科技 米客互联网科技 掌上梦互联网科技 互联网科技的未来发展 互联网科技素材视频竖屏 现代科技和互联网有关系吗 中国互联网有什么科技含量 受益于互联网科技的高速发展 中国科技创新与世界互联网大会 长沙互联网科技 江苏苏星互联网科技有限公司 神州科技互联网有限公司 美国五大互联网科技公司市值多少 科技互联网企业在行动 恒生科技互联网指数十大成分股 武汉亿视互联网科技有限公司 湖南永感互联网科技有限公司 科技互联网企业 美国互联网保险保险科技 今日互联网科技 互联网科技革命 北京禾田雨橡互联网科技 哈尔滨秒房互联网科技有限公司 千方科技属于互联网企业吗 从那以后互联网科技有限公司 科技互联网行业投资研究

相关文章

0