Logo Background

Managing v$log_history In Oracle Database

  • By on August 13, 2010 | No Comments

    Oracle v$log_history view show the relative update activity of your database and it’s one of many tools used by database administratorl. A redo record or known as redo entry is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

    Oracle Database maintains redo log files to minimize the loss of data in the case of unexpected shutdown in v$log_history and v$archived_log. v$log_history displays log history information from the control file.

    While v$archived_log displays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared).

    If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN COPY command.

    As Oracle Database offloads redo log images from the redo log filesystem into the archived redo log filesystem, excessive I/O can occur and the archived redo log must be promptly written to tape to keep the free space in the filesystem.

    You can use the script as below to see the statistics of archived log generation in every hour group by day. The result will aid DBA to estimate on the archived redo log backup frequency to tape.

    SELECT  trunc(first_time) "Date",
    to_char(first_time, 'Dy') "Day",
    COUNT(1) "Total",
    SUM(decode(to_char(first_time, 'hh24'),'00',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "0-1",
    SUM(decode(to_char(first_time, 'hh24'),'02',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "2-3",
    SUM(decode(to_char(first_time, 'hh24'),'04',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "4-5",
    SUM(decode(to_char(first_time, 'hh24'),'06',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "6-7",
    SUM(decode(to_char(first_time, 'hh24'),'08',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "8-9",
    SUM(decode(to_char(first_time, 'hh24'),'10',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "10-11",
    SUM(decode(to_char(first_time, 'hh24'),'12',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "12-13",
    SUM(decode(to_char(first_time, 'hh24'),'14',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "14-15",
    SUM(decode(to_char(first_time, 'hh24'),'16',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "16-17",
    SUM(decode(to_char(first_time, 'hh24'),'18',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "18-19",
    SUM(decode(to_char(first_time, 'hh24'),'20',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "20-21",
    SUM(decode(to_char(first_time, 'hh24'),'22',1,0))
    + SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "22-23"
    FROM v$log_history
    WHERE first_time > sysdate-14
    GROUP BY trunc(first_time), to_char(first_time, 'Dy')
    ORDER BY 1
    Previous
    Next
    » Unix Typeset Command
Leave a Comment