Finding the root cause of sudden increase in the archive log generation on the database.

Find tables that have maximum number of changes during the period there was huge archive generation.

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
SUM(db_block_changes_delta)
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('2015_05_23 12','YYYY_MM_DD HH24')
AND to_date('2015_05_25 12','YYYY_MM_DD HH24')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhsso.object_name order by SUM(db_block_changes_delta) desc;

Read more...

If the processes do not match for Output Post Processor

CONCURRENT_QUEUE_NAME RUNNING_PROCESSES TARGET_PROCESSES MAX_PROCESSES MIN_PROCESSES
-------------------- ----------------- ---------------- ------------- -------------
FNDCPOPP 6 3 3 3

Check Internal Manager log

Starting FNDCPOPP Concurrent Manager : 14-APR-2015 17:26:18
Starting FNDCPOPP Concurrent Manager : 14-APR-2015 17:26:18
Starting FNDCPOPP Concurrent Manager : 14-APR-2015 17:26:18
Process monitor session started : 15-APR-2015 14:05:45
Internal Concurrent Manager found node APPROD to be down. Adding it to the list of unavailable nodes.
Internal Concurrent Manager found node DBPROD to be down. Adding it to the list of unavailable nodes.
CONC-SM TNS FAIL
Call to PingProcess failed for XDPCTRLS
CONC-SM TNS FAIL
Call to PingProcess failed for XDPQORDS

Call to PingProcess failed for FNDOPP
CONC-SM TNS FAIL
Call to PingProcess failed for FNDOPP
CONC-SM TNS FAIL
Call to PingProcess failed for FNDOPP

Starting FNDCPOPP Concurrent Manager : 15-APR-2015 14:08:11
Starting FNDCPOPP Concurrent Manager : 15-APR-2015 14:08:11
Starting FNDCPOPP Concurrent Manager : 15-APR-2015 14:08:12

Read more...

This script will report on all backups – full, incremental and archivelog backups.

col STATUS format a9
col hrs format 999.99
select session_key, input_type, status,to_char(start_time,'mm/dd/yy hh24:mi') start_time,to_char(end_time,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrs from V$rman_backup_job_details order by session_key;

SESSION_KEY  INPUT_TYPE    STATUS              START_TIME           END_TIME              HRS
--------------------------------------------------------------------------------------------------------------------------------
378                   DB FULL          RUNNING            02/25/15 08:10      02/25/15 10:15     2.08416
378                   DB FULL          COMPLETED       02/25/15 08:10      02/25/15 10:31     2.35944
386                   BACKUPSET    RUNNING           02/25/15 10:31      02/25/15 11:03     0.53305
386                   BACKUPSET    COMPLETED       02/25/15 10:31      02/25/15 11:20     0.81388
========================================================================================

Monitor RMAN Progress

Read more...

Cause:

Blocking sessions occur when one session holds an exclusive lock on an object and doesn't release it before another session wants to update the same data. This will block the second until the first one has done its work.
From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You will often have to identify these sessions in order to improve your application performance to avoid as many blocking sessions as possible.

Solution:

The following query shows all the blocking sessions which can help you to identify the problem.

col WAIT_CLASS for a12;
select blocking_session,sid,serial#,wait_class,seconds_in_wait,status from v$session where blocking_session is not NULL order by blocking_session;
Read more...