Oracle无法自动生成snapshot

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: