1. 概述
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
------------ ------------ --------- ------------------ -----
orcl ORCL 4759 29 Mar 2028 17:04 1
现象：查看snapshot生成历史，发现很长一段时间都没有生成snapshot
这是一个开发数据库，经过和开发沟通，由于业务测试经常修改操作服务器系统时间。
原因分析：由于修改操作系统时间，在修改成正确的时间后，snapshot 无法自动生成
2. 解决办法
2.1 查看所有的snapshot
SQL> select snap_id,dbid from dba_hist_snapshot order by 1;
SNAP_ID DBID
---------- ----------
4752 1373768042
4753 1373768042
4754 1373768042
4755 1373768042
4756 1373768042
4757 1373768042
4758 1373768042
2.2 删掉所有的已经存在的snapshot
dbms_workload_repository.drop_snapshot_range(low_snap_id=>4752,high_snap_id=>4758,dbid=> 1373768042);
2.3 手工创建新的snapshot
exec dbms_workload_repository.create_snapshot();
2.4 修改自动收集snapshot的周期测试
收集间隔时间改为30 分钟一次。并且保留5天时间
exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);
2.5 查看收集周期
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
1373768042 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT
2.6 查看新生成的snapshot
SQL> select snap_id,dbid,BEGIN_INTERVAL_TIME from dba_hist_snapshot;
SNAP_ID DBID BEGIN_INTERVAL_TIME
---------- ---------- ---------------------------------------------------------------------------
4759 1373768042 29-MAR-17 04.51.37.190 PM
4760 1373768042 29-MAR-17 05.04.47.970 PM
2.7 正常生成AWR报告
SQL> @ $ORACLE_HOME/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:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1373768042 1 ORCL orcl ceshi
Enter value for dbid: 1373768042
Using 1373768042 for database Id
Enter value for 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.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 4759 29 Mar 2017 17:04 1
4760 29 Mar 2017 17:13 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: