有时候在生产系统上分析AWR不太方便,这就需要把生产系统的AWR信息收集下来,然后导入到其他的测试机器,进行进一步的分析。
一,导出AWR信息
SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
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
------------ ------------ ------------
* 1656922614 ORCL dbserver
1491479622 ORCL hisdb1
1491479622 ORCL hisdb2
The default database id is the local one: '1656922614'. To use this
database id, press <return> to continue, otherwise enter an alternative.
输入 dbid 的值: 1491479622
输入 dbid 的值: 1491479622
Using 1491479622 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.
输入 num_days 的值: 2
Listing the last 2 days of Completed Snapshots
DB Name Snap Id Snap Started
------------ --------- ------------------
ORCL 45641 12 3月 2023 00:00
45642 12 3月 2023 01:00
45643 12 3月 2023 02:00
45644 12 3月 2023 03:00
45645 12 3月 2023 04:00
45646 12 3月 2023 05:00
45647 12 3月 2023 06:00
45648 12 3月 2023 07:00
45649 12 3月 2023 08:00
45650 12 3月 2023 09:00
45651 12 3月 2023 10:00
DB Name Snap Id Snap Started
------------ --------- ------------------
ORCL 45652 12 3月 2023 11:00
45653 12 3月 2023 12:00
45654 12 3月 2023 13:00
45655 12 3月 2023 14:00
45656 12 3月 2023 15:00
45657 12 3月 2023 16:00
45658 12 3月 2023 17:00
45659 12 3月 2023 18:00
45660 12 3月 2023 19:00
45661 12 3月 2023 20:00
45662 12 3月 2023 21:00
DB Name Snap Id Snap Started
------------ --------- ------------------
ORCL 45663 12 3月 2023 22:00
45664 12 3月 2023 23:00
45665 13 3月 2023 00:00
45666 13 3月 2023 01:00
45667 13 3月 2023 02:00
45668 13 3月 2023 03:00
45669 13 3月 2023 04:00
45670 13 3月 2023 05:00
45671 13 3月 2023 06:00
45672 13 3月 2023 07:00
45673 13 3月 2023 08:00
DB Name Snap Id Snap Started
------------ --------- ------------------
ORCL 45674 13 3月 2023 09:00
45675 13 3月 2023 10:00
45676 13 3月 2023 11:00
45677 13 3月 2023 12:00
45678 13 3月 2023 13:00
45679 13 3月 2023 14:00
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 45641
Begin Snapshot Id specified: 45641
输入 end_snap 的值: 45679
End Snapshot Id specified: 45679
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
AWRDIR /home/oracle/backup
DATA_PUMP_DIR /u01/app/oracle/admin/orcl/dpdump/
DBMS_OPTIM_ADMINDIR /u01/app/oracle/product/19.16.0/db_1/rdbms/admin
DBMS_OPTIM_LOGDIR /u01/app/oracle/product/19.16.0/db_1/cfgtoollogs
EXPDP_DIR /tmp/
EXTAB_DAT_DIR /home/oracle
JAVA$JOX$CUJS$DIRECTORY$ /u01/app/oracle/product/19.16.0/db_1/javavm/admin
/
OPATCH_INST_DIR /u01/app/oracle/product/19.16.0/db_1/OPatch
OPATCH_LOG_DIR /u01/app/oracle/product/19.16.0/db_1/rdbms/log
Directory Name Directory Path
------------------------------ -------------------------------------------------
OPATCH_SCRIPT_DIR /u01/app/oracle/product/19.16.0/db_1/QOpatch
ORACLE_BASE /u01/app/oracle
ORACLE_HOME /u01/app/oracle/product/19.16.0/db_1
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/19.16.0/db_1/ccr/state
ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/19.16.0/db_1/ccr/state
SDO_DIR_ADMIN /u01/app/oracle/product/19.16.0/db_1/md/admin
SDO_DIR_WORK
XMLDIR /u01/app/oracle/product/19.16.0/db_1/rdbms/xml
XSDDIR /u01/app/oracle/product/19.16.0/db_1/rdbms/xml/sc
hema
Directory Name Directory Path
------------------------------ -------------------------------------------------
XTTS_DIROBJ_DPDIR_88014 /u01/app/oracle/product/19.16.0/db_1/dbs
Choose a Directory Name from the above list (case-sensitive).
输入 directory_name 的值:
输入 directory_name 的值: AWRDIR ----此处要大写
Using the dump directory: AWRDIR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_45641_45679.
To use this name, press <return> to continue, otherwise enter
an alternative.
输入 file_name 的值:
输入 directory_name 的值: AWRDIR
Using the dump directory: AWRDIR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_45641_45679.
To use this name, press <return> to continue, otherwise enter
an alternative.
输入 file_name 的值:
Using the dump file prefix: awrdat_45641_45679 ------此处选择了默认名称
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /home/oracle/backup
| awrdat_45641_45679.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:
| /home/oracle/backup
| awrdat_45641_45679.log
|
| /home/oracle/backup
| awrdat_45641_45679.log
|
End of AWR Extract
SQL> exit
二,导入AWR信息
[oracle@dbserver backup]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on 星期二 3月 14 00:59:05 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
SQL> conn / as sysdba
已连接。
SQL>
SQL> @?/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 ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
AWRDIR /home/oracle/backup
DATA_PUMP_DIR /u01/app/oracle/admin/orcl/dpdump/
DBMS_OPTIM_ADMINDIR /u01/app/oracle/product/19.16.0/db_1/rdbms/admin
DBMS_OPTIM_LOGDIR /u01/app/oracle/product/19.16.0/db_1/cfgtoollogs
EXPDP_DIR /tmp/
EXTAB_DAT_DIR /home/oracle
JAVA$JOX$CUJS$DIRECTORY$ /u01/app/oracle/product/19.16.0/db_1/javavm/admin
/
OPATCH_INST_DIR /u01/app/oracle/product/19.16.0/db_1/OPatch
OPATCH_LOG_DIR /u01/app/oracle/product/19.16.0/db_1/rdbms/log
Directory Name Directory Path
------------------------------ -------------------------------------------------
OPATCH_SCRIPT_DIR /u01/app/oracle/product/19.16.0/db_1/QOpatch
ORACLE_BASE /u01/app/oracle
ORACLE_HOME /u01/app/oracle/product/19.16.0/db_1
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/19.16.0/db_1/ccr/state
ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/19.16.0/db_1/ccr/state
SDO_DIR_ADMIN /u01/app/oracle/product/19.16.0/db_1/md/admin
SDO_DIR_WORK
XMLDIR /u01/app/oracle/product/19.16.0/db_1/rdbms/xml
XSDDIR /u01/app/oracle/product/19.16.0/db_1/rdbms/xml/sc
hema
Directory Name Directory Path
------------------------------ -------------------------------------------------
XTTS_DIROBJ_DPDIR_88014 /u01/app/oracle/product/19.16.0/db_1/dbs
Choose a Directory Name from the list above (case-sensitive).
输入 directory_name 的值:
输入 directory_name 的值: AWRDIR -------此处要大写
Using the dump directory: AWRDIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
输入 file_name 的值:
输入 file_name 的值: awrdat_45641_45679 ----输入名字的时候不加后缀 .dmp
Loading from the file name: awrdat_45641_45679.dmp
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /home/oracle/backup
| awrdat_45641_45679.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Load Started ...
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| /home/oracle/backup
| awrdat_45641_45679.log
End of AWR Load
--注意字符集要与生产一样,就中文就不会出现乱码
三,在测试机上生成生产库的AWR报告
[oracle@dbserver backup]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on 星期二 3月 14 01:05:01 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
SQL> conn / as sysdba
已连接。
SQL> @?/rdbms/admin/awrrpti.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
输入 report_type 的值:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
1491479622 1 ORCL orcl1 hisdb1
1491479622 2 ORCL orcl2 hisdb2
* 1656922614 1 ORCL orcl dbserver
输入 dbid 的值: 1491479622
Using 1491479622 for database Id
输入 inst_num 的值: 1
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.
输入 num_days 的值: 2
Listing the last 2 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
orcl1 ORCL 45641 12 3月 2023 00:00 1
45642 12 3月 2023 01:00 1
45643 12 3月 2023 02:00 1
45644 12 3月 2023 03:00 1
45645 12 3月 2023 04:00 1
45646 12 3月 2023 05:00 1
45647 12 3月 2023 06:00 1
45648 12 3月 2023 07:00 1
45649 12 3月 2023 08:00 1
45650 12 3月 2023 09:00 1
45651 12 3月 2023 10:00 1
45652 12 3月 2023 11:00 1
45653 12 3月 2023 12:00 1
45654 12 3月 2023 13:00 1
45655 12 3月 2023 14:00 1
45656 12 3月 2023 15:00 1
45657 12 3月 2023 16:00 1
45658 12 3月 2023 17:00 1
45659 12 3月 2023 18:00 1
45660 12 3月 2023 19:00 1
45661 12 3月 2023 20:00 1
45662 12 3月 2023 21:00 1
45663 12 3月 2023 22:00 1
45664 12 3月 2023 23:00 1
45665 13 3月 2023 00:00 1
45666 13 3月 2023 01:00 1
45667 13 3月 2023 02:00 1
45668 13 3月 2023 03:00 1
45669 13 3月 2023 04:00 1
45670 13 3月 2023 05:00 1
45671 13 3月 2023 06:00 1
45672 13 3月 2023 07:00 1
45673 13 3月 2023 08:00 1
45674 13 3月 2023 09:00 1
45675 13 3月 2023 10:00 1
45676 13 3月 2023 11:00 1
45677 13 3月 2023 12:00 1
45678 13 3月 2023 13:00 1
45679 13 3月 2023 14:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 45676
Begin Snapshot Id specified: 45676
输入 end_snap 的值: 45677
End Snapshot Id specified: 45677
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_45676_45677.html. To use this name,
press <return> to continue, otherwise enter an alternative.
输入 report_name 的值:
...
|