溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

AWR 導(dǎo)出/導(dǎo)入/生成報(bào)告

發(fā)布時(shí)間：2020-08-12 10:51:11 來源：ITPUB博客 閱讀：168 作者：不一樣的天空w 欄目：關(guān)系型數(shù)據(jù)庫
      客戶的生產(chǎn)系統(tǒng)總是那么重要，一般不會(huì)讓你長時(shí)間的在他們的生產(chǎn)機(jī)器上做操作，但是分析和生成AWR報(bào)告都是一個(gè)相對(duì)耗時(shí)的工作，當(dāng)然只生成一個(gè)AWR報(bào)告是不費(fèi)時(shí)的，但是要對(duì)系統(tǒng)進(jìn)行詳細(xì)分析的時(shí)候，我們可能需要的是一段時(shí)間內(nèi)的所有AWR數(shù)據(jù)，需要根據(jù)實(shí)際情況進(jìn)行選取。這時(shí)我們就需要將客戶的AWR數(shù)據(jù)導(dǎo)出，然后進(jìn)行分析，這個(gè)操作主要涉及AWR數(shù)據(jù)導(dǎo)出、導(dǎo)入和生成報(bào)告三個(gè)階段，下面對(duì)每個(gè)步驟進(jìn)行詳細(xì)描述。
     1、導(dǎo)出

  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                  <<<<<<<輸入需要導(dǎo)出數(shù)據(jù)的天數(shù)

  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              <<<<<<< 輸入結(jié)束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             <<<<<<< 指定一個(gè)存放導(dǎo)出數(shù)據(jù)的directory（如果沒有需要自己建立一個(gè)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、導(dǎo)入
       先將上面生成的dump文件拷貝到目標(biāo)機(jī)器的一個(gè)directory下，然后按如下操作進(jìn)行導(dǎo)入。

  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          <<<<<<< 輸入一個(gè)schema名，用于導(dǎo)入，導(dǎo)入結(jié)束后自動(dòng)會(huì)刪除該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 <return> will result in the recommended default
  84. tablespace (identified by *) being used.

  86. Enter value for default_tablespace: SYSAUX           <<<<<<< 輸入存放AWR數(shù)據(jù)的表空間名

  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、生成報(bào)告
      在目標(biāo)機(jī)器上生成報(bào)告

  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號(hào)
  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                <<<<<<< 輸入生成報(bào)告的天數(shù)  

  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                  <<<<<<< 輸入結(jié)束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
       整個(gè)過程都很簡(jiǎn)單，但有幾點(diǎn)需要注意：
      1、不能將同一個(gè)數(shù)據(jù)庫的AWR數(shù)據(jù)導(dǎo)出后再導(dǎo)入到自己，這樣會(huì)遇到下面的錯(cuò)誤
            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、在導(dǎo)入的時(shí)候，在輸入值時(shí)，盡量用大寫；
       3、如果不再需要的數(shù)據(jù)，可以用如下存儲(chǔ)過程刪除：
            SQL> exec DBMS_SWRF_INTERNAL.UNREGISTER_DATABASE(bdid);     --將dbid換成需要?jiǎng)h除的dbid號(hào)
向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手