Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
Can you show the query + output with the 100/hour log switches? –  Munchi Dec 16 '13 at 6:23
add comment

migrated from stackoverflow.com Dec 15 '13 at 19:58

This question came from our site for professional and enthusiast programmers.

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.