客户的生产系统总是那么重要,一般不会让你长时间的在他们的生产机器上做操作,但是分析和生成AWR报告都是一个相对耗时的工作,当然只生成一个AWR报告是不费时的,但是要对系统进行详细分析的时候,我们可能需要的是一段时间内的所有AWR数据,需要根据实际情况进行选取。这时我们就需要将客户的AWR数据导出,然后进行分析,这个操作主要涉及AWR数据导出、导入和生成报告三个阶段,下面对每个步骤进行详细描述。
1、导出
2、导入
先将上面生成的dump文件拷贝到目标机器的一个directory下,然后按如下操作进行导入。
3、生成报告
在目标机器上生成报告
整个过程都很简单,但有几点需要注意:
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号
1、导出
- 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 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
- ------------ ------------ ------------
- * 2182516689 GYL rac01
- * 2182516689 GYL rac02
-
- The default database id is the local one: '2182516689'. To use this
- database id, press <return> to continue, otherwise enter an alternative.
-
- Enter value for dbid: 2182516689 <<<<<<
-
- Using 2182516689 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: 7 <<<<<<<输入需要导出数据的天数
-
- Listing the last 7 days of Completed Snapshots
-
- DB Name Snap Id Snap Started
- ------------ --------- ------------------
- GYL 37 07 Nov 2014 10:05
- 38 07 Nov 2014 11:00
- 39 07 Nov 2014 12:00
- 40 07 Nov 2014 13:00
- 41 07 Nov 2014 14:00
- 42 07 Nov 2014 15:00
- 43 07 Nov 2014 16:00
- 44 10 Nov 2014 13:51
- 45 10 Nov 2014 15:00
- 46 10 Nov 2014 16:00
- 47 12 Nov 2014 14:11
-
- DB Name Snap Id Snap Started
- ------------ --------- ------------------
- GYL 48 12 Nov 2014 14:37
- 49 12 Nov 2014 16:00
- 50 12 Nov 2014 17:00
- 51 12 Nov 2014 18:00
- 52 13 Nov 2014 10:16
- 53 13 Nov 2014 11:00
- 54 13 Nov 2014 12:00
- 55 13 Nov 2014 13:00
- 56 13 Nov 2014 14:00
- 57 13 Nov 2014 16:21
-
-
- Specify the Begin and End Snapshot Ids
- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- Enter value for begin_snap: 52 <<<<<<< 输入起始snap_id
- Begin Snapshot Id specified: 52
-
- Enter value for end_snap: 57 <<<<<<< 输入结束snap_id
- End Snapshot Id specified: 57
-
-
- Specify the Directory Name
- ~~~~~~~~~~~~~~~~~~~~~~~~~~
-
- Directory Name Directory Path
- ------------------------------ -------------------------------------------------
- DATA_PUMP_DIR /oracle/app/oracle/product/10.2/db_1/rdbms/log/
- ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/10.2/db_1/ccr/state
-
- Choose a Directory Name from the above list (case-sensitive).
-
- Enter value for directory_name: DATA_PUMP_DIR
-
- Using the dump directory: DATA_PUMP_DIR <<<<<<< 指定一个存放导出数据的directory(如果没有需要自己建立一个directory)
-
- Specify the Name of the Extract Dump File
- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- The prefix for the default dump file name is awrdat_52_57.
- To use this name, press <return> to continue, otherwise enter
- an alternative.
-
- Enter value for file_name: gyl_rac01
-
- ...........省略部分输出.............
- Master table \"SYS\".\"SYS_EXPORT_TABLE_01\" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
- /oracle/app/oracle/product/10.2/db_1/rdbms/log/gyl_rac01.dmp
- Job \"SYS\".\"SYS_EXPORT_TABLE_01\" successfully completed at 12:38:07
先将上面生成的dump文件拷贝到目标机器的一个directory下,然后按如下操作进行导入。
- SQL> @$ORACLE_HOME/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_FILE_DIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
- a/sales_history/
-
- DATA_PUMP_DIR /oracle/app/oracle/admin/gyl/dpdump/
- LOG_FILE_DIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
- a/log/
-
- MEDIA_DIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
- a/product_media/
-
- ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/db_1/ccr/state
-
- Directory Name Directory Path
- ------------------------------ -------------------------------------------------
- SS_OE_XMLDIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
- a/order_entry/
-
- SUBDIR /oracle/app/oracle/product/11.2.0/db_1/demo/schem
- a/order_entry//2002/Sep
-
- XMLDIR /oracle/app/oracle/product/11.2.0/db_1/rdbms/xml
-
- Choose a Directory Name from the list above (case-sensitive).
-
- Enter value for directory_name: DATA_PUMP_DIR <<<<<<< 输入dump所在directory
-
- Using the dump directory: DATA_PUMP_DIR
-
- 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: gyl_rac01 <<<<<<< 输入dump文件名,不要带后缀
-
- Loading from the file name: gyl_rac01.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: AWR_STAGE <<<<<<< 输入一个schema名,用于导入,导入结束后自动会删除该schema
-
- 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
- ------------------------------ --------- ------------------
- EXAMPLE PERMANENT
- SYSAUX PERMANENT *
- TEST PERMANENT
- USERS PERMANENT
-
- Pressing will result in the recommended default
- tablespace (identified by *) being used.
-
- Enter value for default_tablespace: SYSAUX <<<<<<< 输入存放AWR数据的表空间名
-
- 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\
在目标机器上生成报告
- SQL> @?/rdbms/admin/awrrpti.sql
-
- 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: html
-
- Type Specified: html
-
-
- Instances in this Workload Repository schema
- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-
- DB Id Inst Num DB Name Instance Host
- ------------ -------- ------------ ------------ ------------
- * 2184766987 1 GYL gyl oracle11g
- 2182516689 2 GYL gyl2 rac02
- 2182516689 1 GYL gyl1 rac01
-
- Enter value for dbid: 2182516689 <<<<<<< 输入dbid
- Using 2182516689 for database Id
- Enter value for inst_num: 1 <<<<<<< 输入inst_num号
- 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: 2 <<<<<<< 输入生成报告的天数
-
- Listing the last 2 days of Completed Snapshots
-
- Snap
- Instance DB Name Snap Id Snap Started Level
- ------------ ------------ --------- ------------------ -----
- gyl1 GYL 52 13 Nov 2014 10:16 1
- 53 13 Nov 2014 11:00 1
- 54 13 Nov 2014 12:00 1
- 55 13 Nov 2014 13:00 1
- 56 13 Nov 2014 14:00 1
-
- 57 13 Nov 2014 16:21 1
-
-
-
- Specify the Begin and End Snapshot Ids
- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- Enter value for begin_snap: 52 <<<<<<< 输入起始snap_id
- Begin Snapshot Id specified: 52
-
- Enter value for end_snap: 56 <<<<<<< 输入结束snap_id
- End Snapshot Id specified: 56
-
-
-
- Specify the Report Name
- ~~~~~~~~~~~~~~~~~~~~~~~
- The default report file name is awrrpt_1_52_56.html. To use this name,
- press <return> to continue, otherwise enter an alternative.
-
- 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号
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29821678/viewspace-1377260/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29821678/viewspace-1377260/