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.
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
~~~~~~~~~~~~~~~~~~~~~~~~~~
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
------------ ------------ ---------- ------------------ ----------
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.