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

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

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

    
Can you show the query + output with the 100/hour log switches? –  Munchi Dec 16 '13 at 6:23

1 Answer 1

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

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.