Oracle veritabanında redo logların saatlik kaç kez switch olduğu bilgisini aşağıdaki sql komutu kullanarak öğrenebilirsiniz.
set linesize 300 set pagesize 5000 column 00 format 999 heading "00" column 01 format 999 heading "01" column 02 format 999 heading "02" column 03 format 999 heading "03" column 04 format 999 heading "04" column 05 format 999 heading "05" column 06 format 999 heading "06" column 07 format 999 heading "07" column 08 format 999 heading "08" column 09 format 999 heading "09" column 10 format 999 heading "10" column 11 format 999 heading "11" column 12 format 999 heading "12" column 13 format 999 heading "13" column 14 format 999 heading "14" column 15 format 999 heading "15" column 16 format 999 heading "16" column 17 format 999 heading "17" column 18 format 999 heading "18" column 19 format 999 heading "19" column 20 format 999 heading "20" column 21 format 999 heading "21" column 22 format 999 heading "22" column 23 format 999 heading "23" column 24 format 999 heading "24" column "Day" for a5 column INST_ID for a7 column DAY_S for a8 column date_s for a14 prompt prompt Redo Log Switches prompt select CASE INST_ID WHEN 1 THEN date_str END date_s, to_char(INST_ID) INST_ID, CASE INST_ID WHEN 1 THEN "Day" END day_s, "00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23" from (SELECT trunc (first_time) date_str, INST_ID, to_char (trunc (first_time),'Dy') "Day", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 0, 1)) "00", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 1, 1)) "01", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 2, 1)) "02", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 3, 1)) "03", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 4, 1)) "04", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 5, 1)) "05", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 6, 1)) "06", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 7, 1)) "07", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 8, 1)) "08", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 9, 1)) "09", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 10, 1)) "10", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 11, 1)) "11", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 12, 1)) "12", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 13, 1)) "13", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 14, 1)) "14", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 15, 1)) "15", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 16, 1)) "16", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 17, 1)) "17", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 18, 1)) "18", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 19, 1)) "19", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 20, 1)) "20", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 21, 1)) "21", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 22, 1)) "22", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 23, 1)) "23" from gv$log_history where trunc(first_time) > sysdate-30 group by INST_ID, trunc(first_time) order by trunc(first_time) DESC, INST_ID );
Redo logların anlık durumlarını kontrol etmek için aşağıdaki komutu kullanabilirsiniz.
set linesize 2000
set pagesize 2000
col REDOLOG_FILE_NAME for a70
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
(a.BYTES/1024/1024) AS SIZE_MB,
b.MEMBER AS REDOLOG_FILE_NAME
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY 2, a.GROUP# ASC;
Zaman içerisinde sql lerin devamını ekleyeceğim.