Huge archive log generation .. what to do? what is causing it
Step 1:
Know which table / object has more number of changes during the problematic window:
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
sum(db_block_changes_delta) as maxchages
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time BETWEEN to_date('2013_05_22 17','YYYY_MM_DD HH24')
AND to_date('2013_05_22 21','YYYY_MM_DD HH24')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhsso.object_name order by maxchages asc;
The below objects have max number of changes in them during the high archive generation period.
SNAP_TIME OBJECT_NAME MAXCHAGES
---------------- ------------------------------ ----------
2013_05_22 17:00 MGMT_CURRENT_AVAILABILITY_PK 7104
2013_05_22 17:00 MGMT_AVAIL_MARKER_PK 7312
2013_05_22 18:00 MGMT_METRICS_1HOUR_PK_INT 20112
2013_05_22 17:00 MGMT_METRICS_1HOUR_PK_INT 20864
2013_05_22 20:00 MGMT_METRICS_1HOUR_PK_INT 21152
2013_05_22 19:01 MGMT_METRICS_1HOUR_PK_INT 23088
2013_05_22 17:00 MGMT_COLLECTIONS 95392
2013_05_22 20:00 MGMT_COLLECTIONS 110080
2013_05_22 17:00 MGMT_CURRENT_METRICS_PK 110512
2013_05_22 20:00 MGMT_CURRENT_METRICS_PK 110992
2013_05_22 18:00 MGMT_CURRENT_METRICS_PK 112304
2013_05_22 19:01 MGMT_CURRENT_METRICS_PK 113584
2013_05_22 19:01 MGMT_COLLECTIONS 114656
2013_05_22 18:00 MGMT_COLLECTIONS 114752
2013_05_22 20:00 MGMT_METRICS_RAW_PK_INT 127744
2013_05_22 18:00 MGMT_METRICS_RAW_PK_INT 129392
2013_05_22 17:00 MGMT_METRICS_RAW_PK_INT 129888
2013_05_22 19:01 MGMT_METRICS_RAW_PK_INT 130512
2013_05_22 17:00 MGMT_POLICY_ASSOC 292576
2013_05_22 17:00 MGMT_POLICY_ASSOC_CFG 294128
2013_05_22 18:00 MGMT_POLICY_ASSOC 367312
2013_05_22 20:00 MGMT_POLICY_ASSOC_CFG 368544
2013_05_22 20:00 MGMT_POLICY_ASSOC 368560
2013_05_22 19:01 MGMT_POLICY_ASSOC_CFG 370432
2013_05_22 18:00 MGMT_POLICY_ASSOC_CFG 371504
2013_05_22 19:01 MGMT_POLICY_ASSOC 377728
2013_05_22 17:00 MGMT_POLICY_ASSOC_CFG_PARAMS 434720
2013_05_22 18:00 MGMT_POLICY_ASSOC_CFG_PARAMS 551968
2013_05_22 20:00 MGMT_POLICY_ASSOC_CFG_PARAMS 554816
2013_05_22 19:01 MGMT_POLICY_ASSOC_CFG_PARAMS 560944
2013_05_22 17:00 MGMT_POLICY_ASSOC_CFG_PK_IDX 587168
2013_05_22 19:01 MGMT_POLICY_ASSOC_CFG_PK_IDX 737296
2013_05_22 20:00 MGMT_POLICY_ASSOC_CFG_PK_IDX 739120
2013_05_22 18:00 MGMT_POLICY_ASSOC_CFG_PK_IDX 742656
Step 2:
Once you know the objects ,get the SQL information realted to those objects:
The below are the SQLs causing more changes to MGMT_POLICY_ASSOC_CFG%.
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dbms_lob.substr(sql_text,4000,1),
dhss.instance_number,
dhss.sql_id,executions_delta,rows_processed_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%MGMT_POLICY_ASSOC_CFG%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND begin_interval_time BETWEEN to_date('2013_05_22 17','YYYY_MM_DD HH24')
AND to_date('2013_05_22 21','YYYY_MM_DD HH24')
AND dhss.sql_id = dhst.sql_id;
INSERT INTO MGMT_POLICY_ASSOC_CFG(CLEAR_MESSAGE, CLEAR_MESSAGE_NLSID, COLL_NAME, CONDITION_OPERATOR, EVAL_ORDER, FIXIT_J
OB, IS_PUSH, KEY_OPERATOR, KEY_VALUE, MESSAGE, MESSAGE_NLSID, NO_CLEAR_ON_NULL, NUM_OCCURRENCES, OBJECT_GUID, POLICY_GUI
D) VALUES ( :1, :2, NVL(:3, ' '), :4, :5, :6, :7, NVL(:8, 0), NVL(:9, ' '), :10, :11, :12, :13, :14, :15)
1 71t5d196acgr9 186462 0
2013_05_22 18:00
UPDATE MGMT_POLICY_ASSOC_CFG SET CLEAR_MESSAGE= :1 , CLEAR_MESSAGE_NLSID= :2 , CONDITION_OPERATOR= :3 , EVAL_ORDER= :4 ,
FIXIT_JOB= :5 , IS_PUSH= :6 , MESSAGE= :7 , MESSAGE_NLSID= :8 , NO_CLEAR_ON_NULL= :9 , NUM_OCCURRENCES= :10 WHERE COLL
_NAME= NVL(:11, ' ') AND KEY_VALUE= NVL(:12, ' ') AND POLICY_GUID= HEXTORAW(:13) AND KEY_OPERATOR= NVL(:14, 0) AND OBJEC
T_GUID= HEXTORAW(:15)
1 5xmfm787py0a3 186462 186462
2013_05_22 18:00
UPDATE MGMT_POLICY_ASSOC_CFG_PARAMS SET CRIT_THRESHOLD= :1 , WARN_THRESHOLD= :2 WHERE COLL_NAME= NVL(:3, ' ') AND KEY_V
ALUE= NVL(:4, ' ') AND POLICY_GUID= HEXTORAW(:5) AND KEY_OPERATOR= NVL(:6, 0) AND OBJECT_GUID= HEXTORAW(:7) AND PARAM_NA
ME= NVL(:8, ' ')
1 dxdzv7ymv7vrk 186487 186487
2013_05_22 17:00
INSERT INTO MGMT_POLICY_ASSOC_CFG_PARAMS(COLL_NAME, CRIT_THRESHOLD, KEY_OPERATOR, KEY_VALUE, OBJECT_GUID, POLICY_GUID, W
ARN_THRESHOLD) VALUES ( NVL(:1, ' '), :2, NVL(:3, 0), NVL(:4, ' '), :5, :6, :7)
1 a8d5br4z9m251 147609 0
Step 3: Once you know the SQL ID , get the program and userid who is running it and intimate the user to take care of this query / Program.
As a DBA , you can increase the archive log backup frequency of the database / once you get the permission to kill the program /sql you can kill them as well.
Know which program is causing huge redo using the SQL IDs.
SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
user_id,
program
FROM dba_hist_active_sess_history
WHERE sql_id in ('5xmfm787py0a3','bq28gpz5w5qqg','71t5d196acgr9','a8d5br4z9m251')
AND snap_id BETWEEN 31856 AND 31860 2 3 4 5 6 ;
INSTANCE_NUMBER TO_CHAR(SAMPLE_TIME USER_ID PROGRAM
--------------- ------------------- ---------- ----------------------------------------------------------------
1 2013_05_22 17:26:22 25 OMS
1 2013_05_22 18:20:22 25 OMS
1 2013_05_22 18:40:31 25 OMS
1 2013_05_22 19:56:41 25 OMS
1 2013_05_22 19:34:41 25 OMS
1 2013_05_22 19:14:11 25 OMS
1 2013_05_22 20:49:00 25 OMS
1 2013_05_22 20:36:20 25 OMS
1 2013_05_22 20:25:50 25 OMS
