5 Replies Latest reply: Sep 21, 2008 3:07 AM by 660722 RSS

    alter database clear logfile group 1  - question

    DBA2011
      10.2.0.x versioin
      will this create/fix new member if one member in in-active multiplexed group has problem?
        • 1. Re: alter database clear logfile group 1  - question
          EdStevens
          DBA2008 wrote:
          10.2.0.x versioin
          will this create/fix new member if one member in in-active multiplexed group has problem?
          That would probably depend on the nature of the problem ... which you haven't described.

          If I replace the battery in my car, will that fix the problem with my car?
          • 2. Re: alter database clear logfile group 1  - question
            DBA2011
            A damaged online redo logfile can be regenerated. Oracle provides an ALTER
            DATABASE CLEAR LOGFILE GROUP # command (where # is the number of the group
            with a damaged member), which will delete and re-create the members of a logfile group.
            If the database is running in archivelog mode (and it should be), the logfile group must
            have been archived before Oracle will permit execution of the clear logfile command.
            This is because clearing an unarchived logfile group would mean that the archivelog
            stream would be missing one logfile, and therefore that recovery would not be possible.
            There is a variation on the command, ALTER DATABASE CLEAR UNARCHIVED
            LOGFILE GROUP #, which will delete and re-create a logfile even if it has not been
            successfully archived, but after executing this command it is absolutely vital to perform
            a whole database backup.
            • 3. Re: alter database clear logfile group 1  - question
              247514
              The document says,

              Use the CLEAR LOGFILE clause to reinitialize an online redo log, optionally without archiving the redo log. CLEAR LOGFILE is similar to adding and dropping a redo log, except that the statement may be issued even if there are only two logs for the thread and may be issued for the current redo log of a closed thread.

              http://68.142.116.68/docs/cd/B19306_01/server.102/b14200/statements_1004.htm#SQLRF00802
              • 4. Re: alter database clear logfile group 1  - question
                26741
                Are you answering your own question ?
                Or
                Are you quoting some documentation or note or article ?
                • 5. Re: alter database clear logfile group 1  - question
                  660722
                  Hello !!

                  What I understood from your post is you didnt understand the meaning of the paragraph you posted.
                  Okay, lets clear this step by step.

                  This is related to redo logfile failures in different scenario . The scenario could be one of the given below in short.

                  1] Recovering after loss of all members of an INACTIVE Redo log group.

                  --> You can recreate the logfile using "alter database clear logfile...". First you need to check the status "ARCHIVED" = yes/no FROM v$log.
                  Then depending on that you need to use "alter database clear (unarchived) logfile group <n>" command. INACTIVE status means that the group is no
                  llonger needed for recovery.The command recreates all members of the group.

                  2] Recovering after loss of all members of an ACTIVE Redo log group.

                  --> As this group is required for recovery you must attempt to issue checkpoint to clear the logfile group with the above command.

                  Moreover if you loose the current redo log group you need to perform SCN based recovery.

                  Hope this will help.

                  Regards,
                  Pravin Baldawa