Query to check if your Oracle redo changing is too frequently

Everywhere is easy to find some paper regarding the frequency that redo should be filled up.

Personally I like the 1/2 hour changing, it doesn´t put a log of pressure at checkpoint, giving us a good balance between recovery time and wait of redo sync.

The query bellow will show if any changing during the working time (7AM to 7PM) is happening more often then twice an hour.

(It will check just the last ten days)

select to_number(to_char(t1.FIRST_TIME,’ddmm’)||t1.thread#||t1.sequence#) sequencia,
to_number(to_char(t1.FIRST_TIME,’hh24′))*60+ to_number(to_char(t1.FIRST_TIME,’mi’)) time ,
to_number(to_char(t2.FIRST_TIME,’hh24′))*60+ to_number(to_char(t2.FIRST_TIME,’mi’)) time2
from v$archived_log t1,v$archived_log t2
where
to_number(to_char(t1.FIRST_TIME,’ddmm’)||t1.thread#||t1.sequence#)=to_number(to_char(t2.FIRST_TIME,’ddmm’)||t2.thread#||t2.sequence#-1) and
(to_number(to_char(t2.FIRST_TIME,’hh24′))*60+ to_number(to_char(t2.FIRST_TIME,’mi’)) )
–
(to_number(to_char(t1.FIRST_TIME,’hh24′))*60+ to_number(to_char(t1.FIRST_TIME,’mi’)) )
<30  –redo changing more than 2 times per hour
/*  begining of time validation,
aiming to avoid batch operation */
and to_number(to_char(t1.FIRST_TIME,’hh24′))  between 7 and 19 and
to_number(to_char(t2.FIRST_TIME,’hh24′))  between 7 and 19
— end of time validation
and t1.first_time>sysdate-10
and t2.first_time>sysdate-10
;

Monitoring backup through Alert

This procedure will write into alert.log when the enviroment doesn´t have backup online working properly.

 

CREATE OR REPLACE PROCEDURE SYS.L3_BACKUP_CHECK
AS
STATUS_ONLINE VARCHAR2(10);
BEGIN
SELECT decode(count(*),0,’FAIL’,’OK’) INTO STATUS_ONLINE
FROM V$RMAN_BACKUP_JOB_DETAILS T1 WHERE START_TIME>SYSDATE-36/24
AND INPUT_TYPE NOT LIKE ‘%ARCHIVE%’;
IF STATUS_ONLINE=’FAIL’ THEN
dbms_system.ksdwrt(2,’ORA-00600: ONLINE BACKUP FAIL, NOT BACKING UP   +36 HOURS’);
END IF;
END;

 

 

Why Oracle automatic memory management is often a bad idea

Oracle might had created this feature for those whom doesn´t have a DBA onsite and need a database that can operate by itself.

The use of AMM is not free of charge, many peak of usage in one area often lead to future decrease in performance in another area.

 

e.g. If a bad query loads tons of cache in database buffer, and Oracle deallocate memory from shared pool to accomplish this request, eventually the whole database will pay a huge price with decreasing in performance due to lack of information at shared pool.

 

So the botton line is, if your database have someone watching close to it,  don´t use AMM.