1 2 Previous Next 16 Replies Latest reply: Aug 28, 2010 2:35 PM by dbaforu RSS

    ADDM-Redo log size increase on asm

    dbaforu
      hii,
      i have run addm and hve following msg.

      FINDING 4: 5.8% impact (762 seconds)
      ------------------------------------
      Log file switch operations were consuming significant database time while
      waiting for checkpoint completion.

      RECOMMENDATION 1: DB Configuration, 5.8% benefit (762 seconds)
      ACTION: Verify whether incremental shipping was used for standby
      databases.

      RECOMMENDATION 2: DB Configuration, 5.8% benefit (762 seconds)
      ACTION: Increase the size of the log files to 1839 M to hold at least 20
      minutes of redo information.


      My database is in archive log mode..and have asm storage..how to increase size of redo log file..can i do it when instance is open?
      or i should have go to mount mode to do this operation.
        • 1. Re: ADDM-Redo log size increase on asm
          sybrand_b
          alter database drop logfile group
          followed by
          alter database add logfile group

          if a logfile group is in use

          alter system switch logfile

          This all can be done when the database is open.

          Your questions in this forum indicate you have very little knowledge of administrative procedures, and at the same time you use this forum as a replacement for reading documentation and tutorials.
          Please change that behavior. You should consult documentation prior to asking a question, as this is a forum of volunteers.

          ------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: ADDM-Redo log size increase on asm
            618702
            dbaforu,

            You have to drop the current redologs and create new redo log groups with the new size.

            Ogan
            • 3. Re: ADDM-Redo log size increase on asm
              618702
              sybrand_b,

              Switching the logfile before the drop is not enough. You need to checkpoint to make the unwanted redolog group's state to INACTIVE.

              Regards.

              Ogan
              • 4. Re: ADDM-Redo log size increase on asm
                Chinar
                To increasing size of online redologfiles you have to drop and create again these.But you can not drop current/active group,you can drop inactive/unused group.To providing this you need using alter system switch logfile; alter system checkpoint; for example
                SQL> select member from v$logfile;
                
                MEMBER
                --------------------------------------------------------------------------------
                
                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SB\REDO3.LOG
                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SB\REDO2.LOG
                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SB\REDO1.LOG
                
                SQL> select group#,status from v$log;
                
                    GROUP# STATUS
                ---------- ----------------
                         1 INACTIVE
                         2 CURRENT
                         3 INACTIVE
                
                SQL> alter database drop logfile group 1;
                
                Database altered.
                
                SQL> alter database add logfile group 1 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\SB\RE
                DO1.LOG') size 60M reuse;
                
                Database altered.
                
                SQL> select group#,status from v$log;
                
                    GROUP# STATUS
                ---------- ----------------
                         1 UNUSED
                         2 CURRENT
                         3 INACTIVE
                
                SQL> alter system switch logfile;
                
                System altered.
                
                SQL> alter system checkpoint;
                
                System altered.
                
                SQL> select group#,status from v$log;
                
                    GROUP# STATUS
                ---------- ----------------
                         1 CURRENT
                         2 INACTIVE
                         3 INACTIVE
                
                SQL> alter database drop logfile group 2;
                
                Database altered.
                
                SQL> alter database add logfile group 2 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\SB\RE
                DO2.LOG') size 60M reuse;
                
                Database altered.
                
                SQL>
                • 5. Re: ADDM-Redo log size increase on asm
                  618702
                  Chinar,

                  I was about to paste such information in the code tags :) Thank you.

                  Regards.

                  Ogan
                  • 6. Re: ADDM-Redo log size increase on asm
                    sybrand_b
                    Never needed to do that, so you might be mistaken.
                    As you however have developed a penchant for ridiculing and belittling me (see your ridiculous answer in the Apex forum) and you are seemingly a very arrogant person, who thinks he knows everything better, this time I won't hit 'Abuse'
                    Yet another similar reply, and I will.

                    ------------
                    Sybrand Bakker
                    Senior Oracle DBA
                    • 7. Re: ADDM-Redo log size increase on asm
                      618702
                      sybrand_b,

                      Please try it by yourself and see the difference. I am not mistaken here. I am not even arrogant and i have never said that i know everything better. That is your personal point of view. Also please stop those abusive posts about me and i am only trying to help here.

                      Your answer here is not acceptable and you are the rudest person that i have ever seen in my entire life.

                      I will hit the Abuse button, don't worry.

                      Ogan
                      • 8. Re: ADDM-Redo log size increase on asm
                        Chinar
                        Ogan Ozdogan wrote:
                        Chinar,

                        I was about to paste such information in the code tags :) Thank you.

                        Regards.

                        Ogan
                        ;-)
                        • 9. Re: ADDM-Redo log size increase on asm
                          618702
                          Chinar,

                          You are way too faster than me my friend :)

                          Ogan
                          • 10. Re: ADDM-Redo log size increase on asm
                            618702
                            Chinar,

                            Let me tell you something here about the code of yours.

                            You did not have to run the checkpoint command after the redolog switch because the logfile group was already "INACTIVE". BUT if they have not archived yet (if the archivelog mode is enabled) you have wait for the ARCH process to archive it. So there are two events here to drop an online redolog;

                            1) The online redo log should be archived.
                            2) The online redo log should be in inactive status.

                            After than that, you don't need to run the "alter system checkpoint [global];" command.

                            Regards.

                            Ogan
                            • 11. Re: ADDM-Redo log size increase on asm
                              Chinar
                              Ogan Ozdogan wrote:
                              Chinar,

                              Let me tell you something here about the code of yours.

                              You did not have to run the checkpoint command after the redolog switch because the logfile group was already "INACTIVE". BUT if they have not archived yet (if the archivelog mode is enabled) you have wait for the ARCH process to archive it. So there are two events here to drop an online redolog;

                              1) The online redo log should be archived.
                              2) The online redo log should be in inactive status.

                              After than that, you don't need to run the "alter system checkpoint [global];" command.

                              Regards.

                              Ogan
                              Yes you are right(because of this i first check status of these log group) ;-)
                              • 12. Re: ADDM-Redo log size increase on asm
                                sybrand_b
                                Apparently you are not only arrogant but you are also a liar. Did you ever see me? No, 'Sir'. As to the 'rudest' person, you don't even know what it is, as it is you. You are so conceited. You don't even admit you deliberately tried to humiliate me by providing an answer which was evidently wrong, as portrait is not a reserved word.

                                Please make sure you don't ever reply to any of my posts, or I will chase you until I have you removed from this forum.

                                BTW: I sent an abuse with respect to your reply. More will follow if you don't stop your little vendetta against me.

                                --------
                                Sybrand Bakker
                                Senior Oracle DBA

                                Edited by: sybrand_b on Aug 27, 2010 3:28 PM
                                • 13. Re: ADDM-Redo log size increase on asm
                                  618702
                                  sybrand_b,

                                  I don't have a "vendetta" against you. I will stop answering you from now on.

                                  +"+
                                  +I will chase you until I have you removed from this forum.+
                                  +"+

                                  Stop threatning people in the OTN forums.

                                  Regards.

                                  Ogan
                                  • 14. Re: ADDM-Redo log size increase on asm
                                    dbaforu
                                    hi Chinar

                                    Thanks for your replay...but you have shown here the procedure for normal database instance...i am working on 2-node RAC database instance with
                                    ASM. I have a disk group DATA_DG_01.What will be syntax for asm database to drop and add redolog group.And also i have log switch less ten 1 minute.


                                    Thanks
                                    dbaforu
                                    1 2 Previous Next