Monday, November 22, 2010

Redo logs

===========================================================================================

Being Oracle database administrator you must be aware that the current redo log file gets filled up so frequently and so oftten we have to resize the redo logs. Basically when an online redo log gets filled up then Oracle automatically performs a log switch and starts writing change vectors to the next redo log group.

Although it is a simple task to resize online redo logs on a RAC environment but it should be handled with great care because if your online redo logs are damaged then your whole database gets affected. Today we will discuss some great tips that will help you resize online redo logs while the database is running. Article Continues below...

Get Current Online Redo Log Groups:

First of all we will run below command to get current online redo log groups and their sizes.

SELECT LF.GROUP#, LF.MEMBER, L.BYTES FROM V$LOGFILE LF, V$LOG L WHERE LF.GROUP# = L.GROUP#;

GROUP#                                        MEMBER                                                  BYTES
------------- -------------------------------------------------------------------            -------------------
    1             /U03/APP/ORADATA/ORA920/REDO_G01A.LOG            401,439,200
    1             /U04/APP/ORADATA/ORA920/REDO_G01B.LOG            401,439,200
    1             /U05/APP/ORADATA/ORA920/REDO_G01C.LOG            401,439,200
    1             /U06/APP/ORADATA/ORA920/REDO_G01D.LOG            401,439,200
    2             /U03/APP/ORADATA/ORA920/REDO_G02A.LOG            401,439,200
    2             /U04/APP/ORADATA/ORA920/REDO_G02B.LOG            401,439,200
    2             /U05/APP/ORADATA/ORA920/REDO_G02C.LOG            401,439,200
    2             /U06/APP/ORADATA/ORA920/REDO_G02D.LOG            401,439,200
    3             /U03/APP/ORADATA/ORA920/REDO_G03A.LOG            401,439,200
    3             /U04/APP/ORADATA/ORA920/REDO_G03B.LOG            401,439,200
    3             /U05/APP/ORADATA/ORA920/REDO_G03C.LOG            401,439,200
    3             /U06/APP/ORADATA/ORA920/REDO_G03D.LOG            401,439,200
    4             /U03/APP/ORADATA/ORA920/REDO_G04A.LOG            401,439,200
    4             /U04/APP/ORADATA/ORA920/REDO_G04B.LOG            401,439,200
    4             /U05/APP/ORADATA/ORA920/REDO_G04C.LOG            401,439,200
    4             /U06/APP/ORADATA/ORA920/REDO_G04D.LOG            401,439,200

Force a Log Switch:

Now we will force a log switch until the last redo log is marked as CURRENT. First we will view V$LOG to determine if our last redo log is marked CURRENT or not.

SELECT GROUP#, STATUS FROM V$LOG;

GROUP#       STATUS
-------------    -------------------
      1             CURRENT
      2             INACTIVE
      3             INACTIVE
      4             INACTIVE

As our last redo log is not marked CURRENT so we will keep on running below command until we get last redo log marked as CURRENT.

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;

SELECT GROUP#, STATUS FROM V$LOG;

GROUP#       STATUS
-------------    -------------------
      1             INACTIVE
      2             INACTIVE
      3             INACTIVE
      4             CURRENT

Drop First Online Redo Log:

In this step we will be dropping our first online redo log group.

ALTER DATABASE DROP LOGFILE GROUP 1;

If your logfile group has an active status then you can avoid errors by performing a checkpoint on the database.

ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER DATABASE DROP LOGFILE GROUP 1;

Recreating Dropped Online Redo Log Group:

Now if required then you can recreate the dropped redo log group with different size.

ALTER DATABASE ADD LOGFILE GROUP 1
(
2 '/U03/APP/ORADATA/ORA920/REDO_G01A.LOG',
3 '/U04/APP/ORADATA/ORA920/REDO_G01B.LOG',
4 '/U05/APP/ORADATA/ORA920/REDO_G01C.LOG'
5 '/U06/APP/ORADATA/ORA920/REDO_G01D.LOG'
)
SIZE 250M REUSE;

Force log switch:

Now we will be forcing another log switch so as to make our newly created online redo log groups as CURRENT.

SELECT GROUP#, STATUS FROM V$LOG;

GROUP#       STATUS
-------------    -------------------
      1             UNUSED
      2             INACTIVE
      3             INACTIVE
      4             CURRENT

ALTER SYSTEM SWITCH LOGFILE;

SELECT GROUP#, STATUS FROM V$LOG;

GROUP#       STATUS
-------------    -------------------
      1             CURRENT
      2             INACTIVE
      3             ACTIVE
      4             ACTIVE

Step Back:

Now you will have to step back and follow below mentioned steps as we did them before. This will resize all online redo log groups until all of them are rebuilt.

   1. Drop First Online Redo Log
   2. Recreating Dropped Online Redo Log Group
   3. Force log switch

Get Current Online Redo Log Groups:

Once all all online redo log groups are resized then we can get the snapshot of all physical files.

SELECT LF.GROUP#, LF.MEMBER, L.BYTES FROM V$LOGFILE LF, V$LOG L WHERE LF.GROUP# = L.GROUP#;

GROUP#                                        MEMBER                                                  BYTES
------------- -------------------------------------------------------------------            -------------------
      1             /U03/APP/ORADATA/ORA920/REDO_G01A.LOG            508,810,000
      1             /U04/APP/ORADATA/ORA920/REDO_G01B.LOG            508,810,000
      1             /U05/APP/ORADATA/ORA920/REDO_G01C.LOG            508,810,000
      1             /U06/APP/ORADATA/ORA920/REDO_G01D.LOG            508,810,000
      2             /U03/APP/ORADATA/ORA920/REDO_G02A.LOG            508,810,000
      2             /U04/APP/ORADATA/ORA920/REDO_G02B.LOG            508,810,000
      2             /U05/APP/ORADATA/ORA920/REDO_G02C.LOG            508,810,000
      2             /U06/APP/ORADATA/ORA920/REDO_G02D.LOG            508,810,000
      3             /U03/APP/ORADATA/ORA920/REDO_G03A.LOG            508,810,000
      3             /U04/APP/ORADATA/ORA920/REDO_G03B.LOG            508,810,000
      3             /U05/APP/ORADATA/ORA920/REDO_G03C.LOG            508,810,000
      3             /U06/APP/ORADATA/ORA920/REDO_G03D.LOG            508,810,000
      4             /U03/APP/ORADATA/ORA920/REDO_G04A.LOG            508,810,000
      4             /U04/APP/ORADATA/ORA920/REDO_G04B.LOG            508,810,000
      4             /U05/APP/ORADATA/ORA920/REDO_G04C.LOG            508,810,000
      4             /U06/APP/ORADATA/ORA920/REDO_G04D.LOG            508,810,000 Read Again!!

More Database Articles
 Database Security: Step by step guideline  Database Security: Step by step guideline
 SQL FROM Clause - Do not misuse  SQL FROM Clause - Do not misuse
 Great Tips on Reusing Space after deletion of database data!!  Great Tips on Reusing Space after deletion of database data!!
 SQL Injection Attacks - Are you safe?  SQL Injection Attacks - Are you safe?
 Great Tips for improving join queries performance!!  Great Tips for improving join queries performance!!
===========================================================================================


col member format a40 trunc
col logsize format 9999 head MBSize
select l.group#,l.status,l.bytes/1024/1024 logsize,f.member
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,3
/

===========================================================================================


/* redostats.sql
     see the critical stats for redo and archive logs
     SDR-Oracle
     Note: this script is the v8-compatible version
*/
set pages 0
set lines 132
set pause off
set feedback off
set verify off
col dsnbr noprint

/* is the archiver falling behind, count should be 1 or 0 */
select decode(count(*),0,'Archiver is current',
               1,'Archiver is archiving ' || count(*) || ' log...',
               'Archiver has fallen behind by ' || count(*) || ' logs!')
  from v$log
 where status != 'CURRENT'
   and archived = 'NO';

/* what is the current log  COL::::<# members> */
select 'Current Online Log:' || chr(9) ||
       substr(f.member,1,(instr(f.member,'/',1,2) -1))
       || '/.../' ||
       substr(f.member,(instr(f.member,'/',-1,1)+1))
       || chr(9) || 'Sequence #' ||
       l.sequence#
  from v$logfile f, v$log l
 where l.group# = f.group#
   and l.status = 'CURRENT'
   and l.archived = 'NO'
   and rownum = 1;

/* what are the previous online logs */
select lc_1.sequence# dsnbr,
       'Previous Online Logs:' || chr(9) ||
       substr(f.member,1,(instr(f.member,'/',1,2) -1))
       || '/.../' ||
       substr(f.member,(instr(f.member,'/',-1,1)+1))
       || chr(9) || 'Sequence #' ||
       lc_1.sequence#
  from v$log lc, v$log lc_1, v$logfile f
 where lc.sequence# = (select max(l.sequence#) from v$log l)
   and lc_1.sequence# = lc.sequence# -1
   and f.group# = lc_1.group#
union
select lc_2.sequence# dsnbr,
       '                  ' || chr(9) ||
       substr(f.member,1,(instr(f.member,'/',1,2) -1))
       || '/.../' ||
       substr(f.member,(instr(f.member,'/',-1,1)+1))
       || chr(9) || 'Sequence #' ||
       lc_2.sequence#
  from v$log lc, v$log lc_2, v$logfile f
 where lc.sequence# = (select max(l.sequence#) from v$log l)
   and lc_2.sequence# = lc.sequence# -2
   and f.group# = lc_2.group#
union
select lc_3.sequence# dsnbr,
       '                  ' || chr(9) ||
       substr(f.member,1,(instr(f.member,'/',1,2) -1))
       || '/.../' ||
       substr(f.member,(instr(f.member,'/',-1,1)+1))
       || chr(9) || 'Sequence #' ||
       lc_3.sequence#
  from v$log lc, v$log lc_3, v$logfile f
 where lc.sequence# = (select max(l.sequence#) from v$log l)


   and lc_3.sequence# = lc.sequence# -3
   and f.group# = lc_3.group#
order by 1 desc;

/* what is the latest archived log LAL:: */
select 'Latest Archived Log:' || chr(9) ||
       substr(h.name,(instr(h.name,'/',-1,1)+1))
       || chr(9) || 'Sequence #' ||
       max(h.sequence#)
  from v$archived_log h
 where h.sequence# = (select max(l.sequence#)
                         from v$log l
                 where l.archived = 'YES'
                   and l.status = 'INACTIVE')
group by 'Latest Archived Log:' || chr(9) ||
         substr(h.name,(instr(h.name,'/',-1,1)+1));

/* how many minutes since the last log switch MSL: */
select 'Elapsed Minutes Since Last Log Switch:' || chr(9) ||
       trunc((sysdate - first_time) * 24 * 60)
from v$log
where status = 'CURRENT'
and sequence# = (select max(sequence#) + 1
           from v$log_history);

/* what are the last 3 individual switch intervals L3I:<1>:<2>:<3> */
select 'Prior 3 Actual Switch Intervals:' || chr(9) || '[' ||
   round((lc.first_time - lc_1.first_time) * 24 * 60,1)
   || ']' || chr(9) || '[' ||
   round((lc_1.first_time - lc_2.first_time) * 24 * 60,1)
   || ']' || chr(9) || '[' ||
   round((lc_2.first_time - lc_3.first_time) * 24 * 60,1)
   || ']'
from v$log lc, v$log_history lc_1, v$log_history lc_2, v$log_history lc_3
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_1.sequence# = lc.sequence# -1
and lc_2.sequence# = lc.sequence# -2
and lc_3.sequence# = lc.sequence# -3;

/* what is the avg interval for last 3 log switches L3S: */
select 'Prior 3 Average Switch Interval:' || chr(9) ||
round( (
   round((lc.first_time - lc_1.first_time) * 24 * 60,2) +
   round((lc_1.first_time - lc_2.first_time) * 24 * 60,2) +
   round((lc_2.first_time - lc_3.first_time) * 24 * 60,2) ) / 3, 1)
from v$log lc, v$log_history lc_1, v$log_history lc_2, v$log_history lc_3
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_1.sequence# = lc.sequence# -1
and lc_2.sequence# = lc.sequence# -2
and lc_3.sequence# = lc.sequence# -3;

set pages 24

===========================================================================================