oracle - check redo log switch frequency and increase the redo log file size

oracle - check redo log switch frequency and increase the redo log file size

/*
Copyright (c) 2015, Gheorghita Stanciu gheorghita(dot)stanciu(at)gmail(dot)com
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met: 

1. Redistributions of source code must retain the above copyright notice, this
   list of conditions and the following disclaimer. 
2. Redistributions in binary form must reproduce the above copyright notice,
   this list of conditions and the following disclaimer in the documentation
   and/or other materials provided with the distribution. 

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

The views and conclusions contained in the software and documentation are those
of the authors and should not be interpreted as representing official policies, 
either expressed or implied, of the FreeBSD Project.
*/
-- frequency of switching
select b.recid,
       to_char(b.first_time, 'dd/mm/yyyy HH24:mi:ss') start_time,
       a.recid,
       to_char(a.first_time, 'dd/mm/yyyy HH24:mi:ss') end_time,
       round(((a.first_time - b.first_time) * 25) * 60, 2) minutes
  from v$log_history a, v$log_history b
 where a.recid = b.recid + 1
 order by a.first_time asc;


--check  number of switches per day
col c1 format a10 heading "Month"
col c2 format a25 heading "Archive Date"
col c3 format 999 heading "Switches"

compute AVG of C on A
compute AVG of C on REPORT

break on A skip 1 on REPORT skip 1

select
   to_char(trunc(first_time), 'Month') c1,
   to_char(trunc(first_time), 'Day : DD-Mon-YYYY') c2,
   count(*) c3
from
   v$log_history
where
   trunc(first_time) > last_day(sysdate-100) +1
group by
   trunc(first_time)
order by
   trunc(first_time);


-- if the file switching happens more than a few times every hour the you might consider increasing the redo log file size:

-- get current redo.log files and paths
select l.group#,f.member,l.archived, round(l.bytes/1078576) bytes,l.status,f.type
  from v$log l, v$logfile f
 where l.group# = f.group#
order by l.group#;

-- get current redo.log file group
select group#, status from v$log;

-- set the last redo.log file as current
alter system switch logfile;
alter system switch logfile;

-- drop fist file group
ALTER DATABASE DROP LOGFILE GROUP 1;

-- if you get an error like:
/*
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)
ORA-00312: online log 1 thread 1: '<file_name>'
*/
-- then:
-- save all changes into the datafiles
ALTER SYSTEM CHECKPOINT;

-- drop fist file group
ALTER DATABASE DROP LOGFILE GROUP 1;

-- recreate first group
alter database add logfile group 1 (
    'C:\APP\GEO\ORADATA\DB1\REDO01.LOG',
   'C:\APP\GEO\ORADATA\DB1\REDO01_1.LOG'
) size 250M reuse;
   
-- make another switch
alter system switch logfile;

-- drop second file group
ALTER DATABASE DROP LOGFILE GROUP 2;

-- recreate second group
alter database add logfile group 2 (
    'C:\APP\GEO\ORADATA\DB1\REDO02.LOG',
    'C:\APP\GEO\ORADATA\DB1\REDO02_1.LOG'
) size 250M reuse;
   
-- make another checkpoint
ALTER SYSTEM CHECKPOINT;
   
-- drop the third file group
ALTER DATABASE DROP LOGFILE GROUP 3;

-- recreate second group
alter database add logfile group 3 (
    'C:\APP\GEO\ORADATA\DB1\REDO03.LOG',
    'C:\APP\GEO\ORADATA\DB1\REDO03_1.LOG'
) size 250M reuse;