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
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:
轉載于:https://www.cnblogs.com/rencheng/p/6640888.html
分類: Oracle 12-0 AWR與Snapshot