Determine redo log size, number of redo log files

oracleoracle-asmrac

Having 2 node RAC instance using ASM
Log switches happening are too high about 100 per hour causing db to hang for few minutes sometimes

   GROUP#  THREAD#    MEMBER                                    ARC STATUS   FSIZE(MB)
---------- ---------- ----------------------------------------- --- -------- ----------
1          1 +CCFA_DATA01/ccfada/group_1.327.956874159          YES ACTIVE      50

2          1 +CCFA_DATA01/ccfada/group_2.329.956874159          NO  CURRENT     50

3          2 +CCFA_DATA01/ccfada/group_3.328.956874142          YES ACTIVE      50

4          2 +CCFA_DATA01/ccfada/group_4.326.956874161          NO  CURRENT     50

Could you help determine the right size of redo log files, right number of redo log files and steps to resize redo log files in ASM (DB size is approx. 6.5Gb)

Best Answer

The recommendation is to have log switches occur a few times an hour. If you have a 100 log switches an hour with 50MB redo logs, you need e.g 1GB redo logs for 5 log switches/hour. 100*50 MB redo/hour for a 6.5GB database is unusually much though.

You can add the new logs, e.g:

ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 1 SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 SIZE 1G;

Then force log switches until the new logs are used (CURRENT status) and old logs become ACTIVE/INACTIVE by:

ALTER SYSTEM SWITCH LOGFILE;

Then drop the old logs:

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;

If you can't drop a logfile group because it is ACTIVE, do

ALTER SYSTEM CHECKPOINT;

so it becomes INACTIVE and try again.

Related Question