ORA-20200: The instance was shutdown between snapshots
The AWR Report is only generated using snapshots from period that instance was Started. If any shutdown occurrs it break stats and AWR can’t generate a report comparing a period where stats belong a old Instance Startup.
This occurrs because Instance stats is not persistent accross reboots, (as the name says is a Instance), so all stats get reseted in every reboot.
When generating reports between hours is easy identify when instance was started, but when generating awr reports between many days this become a painfull task if instance was restarted multiples times during a desired period.
How to find the best Interval to Generate your AWR Reports?
set pagesize 1000
set linesize 1000
(or)
SET LINESIZE 200
SET PAGESIZE 200
UNDEF num_days
COL startup_time FOR a30
COL db_name FOR a10
COL snap_start FOR 9999999
COL snap_end FOR 9999999
COL start_interval FOR a25
COL end_interval FOR a25
COL range_interval FOR a40
COL qtd_snaps FOR 999
SELECT s.startup_time, di.instance_name, MIN(snap_id) snap_start, MAX(snap_id) snap_end, MIN(end_interval_time) start_interval, MAX(end_interval_time) end_interval, EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Days(s) ' || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Hour(s) ' || EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Minute(s) ' range_interval, MAX(snap_id) - MIN(snap_id) qtd_snaps FROM dba_hist_snapshot s, dba_hist_database_instance di WHERE di.dbid = s.dbid AND di.instance_number = s.instance_number AND end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy') - (&num_days - 1) ) GROUP BY s.startup_time, di.instance_name ORDER BY startup_time ASC;
STARTUP_TIME INSTANCE_NAME SNAP_START SNAP_END START_INTERVAL END_INTERVAL RANGE_INTERVAL QTD_SNAPS
------------------------------ ---------------- ---------- -------- ------------------------- ------------------------- ---------------------------------------- ---------
20-AUG-20 02.36.53.000 PM MQMPROD 1 4 20-AUG-20 03.30.09.329 PM 20-AUG-20 06.30.07.232 PM 0 Days(s) 2 Hour(s) 59 Minute(s) 3
20-AUG-20 07.41.25.000 PM MQMPROD 5 14 20-AUG-20 07.52.26.815 PM 21-AUG-20 02.31.00.607 AM 0 Days(s) 6 Hour(s) 38 Minute(s) 9
22-AUG-20 02.05.58.000 AM MQMPROD 15 36 22-AUG-20 02.16.38.541 AM 22-AUG-20 01.00.12.251 PM 0 Days(s) 10 Hour(s) 43 Minute(s) 21
In above output is easy identify what SNAP_ID to use without keep trying and getting ORA-20200 or by reading a huge list of snaps.
The above query is NOT valid to get SNAP_ID to generate AWR Global RAC Report.
No comments:
Post a Comment