一、查看当前AWR保留策略
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------- ------------------- ----------
1310729769 +00000 00:30:00.0 +00007 00:00:00.0 DEFAULT
每半个小时收集一次，保留七天。
二、调整当前基线
QL> exec dbms_workload_repository.modify_baseline_window_size(5);
PL/SQL procedure successfully completed
三、调整保留策略
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention => 8*24*60);
PL/SQL procedure successfully completed
Executed in 0.047 seconds
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------- ------------------- ----------
1310729769 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT
四、关闭AWR,设置interval为0即可
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
PL/SQL procedure successfully completed
Executed in 0.016 seconds
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------- ------------------- ----------
1310729769 +40150 00:00:00.0 +00008 00:00:00.0 DEFAULT
五、手工创建快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
PL/SQL procedure successfully completed
六、删除快照
SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 338, high_snap_id=>339, dbid=>1310729769 );
PL/SQL procedure successfully completed
七、创建BASELINE
SQL> exec dbms_workload_repository.create_baseline(284,285,'apply_awr_1');
PL/SQL procedure successfully completed
八、删除基线
SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'apply_awr_1',cascade=>false);
PL/SQL procedure successfully completed
九、抽取数据保存到某个目录
SQL> create or replace directory DIR_BDUMP as 'D:\ORACLE\';
Directory created
SQL> @D:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrextr.sql
根据提示输入DBID，目标，起始SNAP_ID等。
~~~~~~~~~~~~~
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DBBID DBB_NAME HOST
------------ ------------ ------------
* 1342507489 ORCL SUNGUIQUAN-P
C
The default database id is the local one: '1342507489'. To use this
database id, press to continue, otherwise enter an alternative.
Using 1342507489 for Database ID
dbid
---------
1342507489
dbid
---------
1342507489
max_snap_time
---------
06/01/2014
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 without
specifying a number lists all completed snapshots.
Listing the last 15 days of Completed Snapshots
DB_NAME SNAP_ID SNAPDAT
------------ -------- ------------------
ORCL 251 02 1月 2014 09:10
ORCL 252 02 1月 2014 10:00
ORCL 253 02 1月 2014 11:00
ORCL 254 02 1月 2014 12:00
ORCL 255 02 1月 2014 13:00
ORCL 256 02 1月 2014 14:00
ORCL 257 02 1月 2014 15:00
ORCL 258 02 1月 2014 16:00
ORCL 259 02 1月 2014 17:00
ORCL 260 02 1月 2014 18:00
ORCL 261 02 1月 2014 19:00
ORCL 262 02 1月 2014 20:00
ORCL 263 02 1月 2014 21:00
ORCL 264 02 1月 2014 22:00
ORCL 265 02 1月 2014 23:00
ORCL 266 03 1月 2014 00:00
ORCL 267 03 1月 2014 01:00
ORCL 268 03 1月 2014 02:00
ORCL 269 03 1月 2014 03:00
ORCL 270 03 1月 2014 04:00
DB_NAME SNAP_ID SNAPDAT
------------ -------- ------------------
ORCL 271 03 1月 2014 05:00
ORCL 272 03 1月 2014 06:00
ORCL 273 03 1月 2014 07:00
ORCL 274 03 1月 2014 08:00
ORCL 275 03 1月 2014 09:00
ORCL 276 03 1月 2014 10:00
ORCL 277 03 1月 2014 11:00
ORCL 278 03 1月 2014 12:00
ORCL 279 03 1月 2014 13:00
ORCL 280 03 1月 2014 14:00
ORCL 281 03 1月 2014 15:00
ORCL 282 03 1月 2014 16:00
ORCL 283 03 1月 2014 17:00
ORCL 284 03 1月 2014 18:00
ORCL 285 06 1月 2014 08:49
ORCL 286 06 1月 2014 10:00
dbid
---------
1342507489
max_snap_time
---------
06/01/2014
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Begin Snapshot Id specified: 284
End Snapshot Id specified: 285
bid
---------
284
eid
---------
285
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
DIRNAME DIRPATH
------------------------------ -------------------------------------------------
DATA_FILE_DIR D:\oracle\product\11.2.0\dbhome_1\demo\schema\sal
es_history\
DATA_PUMP_DIR D:\oracle\admin\orcl\dpdump\
DIR_BDUMP D:\ORACLE\
LOG_FILE_DIR D:\oracle\product\11.2.0\dbhome_1\demo\schema\log
\
MEDIA_DIR D:\oracle\product\11.2.0\dbhome_1\demo\schema\pro
duct_media\
ORACLE_OCM_CONFIG_DIR D:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR D:\oracle\product\11.2.0\dbhome_1\demo\schema\ord
er_entry\
SUBDIR D:\oracle\product\11.2.0\dbhome_1\demo\schema\ord
er_entry\/2002/Sep
XMLDIR c:\ade\aime_dadvfm0254\oracle\rdbms\xml
Choose a Directory Name from the above list (case-sensitive).
Using the dump directory: DIR_BDUMP
dmpdir
---------
DIR_BDUMP
dmppath
---------
D:\ORACLE\
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_284_285.
To use this name, press to continue, otherwise enter
an alternative.
Using the dump file prefix: awr_data.dmp
dmpfile
---------
awr_data.dmp
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| D:\ORACLE\
| awr_data.dmp.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:
| D:\ORACLE\
| awr_data.dmp.log
|
dmppath
---------
D:\ORACLE\
dmpfile
---------
awr_data.dmp
dmpfile
---------
awr_data.dmp
dmpdir
---------
DIR_BDUMP
bid
---------
284
eid
---------
285
dbid
---------
1342507489
End of AWR Extract
阅读(1142) | 评论(0) | 转发(0) |
