AWR 导出/导入/生成报告

发布时间：2020-08-12 10:51:11 来源：ITPUB博客阅读：183 作者：不一样的天空w 栏目：关系型数据库

客户的生产系统总是那么重要，一般不会让你长时间的在他们的生产机器上做操作，但是分析和生成AWR报告都是一个相对耗时的工作，当然只生成一个AWR报告是不费时的，但是要对系统进行详细分析的时候，我们可能需要的是一段时间内的所有AWR数据，需要根据实际情况进行选取。这时我们就需要将客户的AWR数据导出，然后进行分析，这个操作主要涉及AWR数据导出、导入和生成报告三个阶段，下面对每个步骤进行详细描述。
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 <<<<<<<输入DBID
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

2、导入
先将上面生成的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 <return> 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\

3、生成报告
在目标机器上生成报告

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号

向AI问一下细节

AWR 导出/导入/生成报告

猜你喜欢

最新资讯

相关推荐

相关标签