7 Replies Latest reply on Apr 19, 2012 10:33 AM by Oceaner

    How to drop not existing STANDBY LOGFILE from Controlfile

    Nosmoht
      Hi all,

      i have trouble with standby logfiles in a database which is restored from standby database backup.

      First some information:
      - 2 Node RAC running Oracle Database 11.2.0.3.0 on Linux (LIVE)
      - Data Guard running which applies logs on another 2 node rac which acts in standby mode (STANDBY)

      A full backup is taking on STANDBY db. The backup will than be used on Test environment (same hardware as in LIVE and also a 2 node RAC) to restore the database.
      Restore and recovery on Test system is working fine and database is up and running.

      Now my problem:
      The controlfile still contains information about the standby log files and i can't drop this with ALTER DATABASE DROP STANDBY LOGFILE because i do NOT have them in test system. The test system does not have a standby database, so there is no need for the standby logfiles.

      If i try to delete the standby logfiles i get the following error:

      SQL> alter database drop standby logfile group 206;
      alter database drop standby logfile group 206
      *
      ERROR at line 1:
      ORA-00313: open failed for members of log group 206 of thread 2
      ORA-00312: online log 206 thread 2:
      '+STBY_REDO2/stby/onlinelog/group_206.364.778300599'
      ORA-17503: ksfdopn:2 Failed to open file
      +STBY_REDO2/stby/onlinelog/group_206.364.778300599
      ORA-15012: ASM file '+STBY_REDO2/stby/onlinelog/group_206.364.778300599'
      does not exist
      ORA-00312: online log 206 thread 2:
      '+STBY_REDO1/stby/onlinelog/group_206.364.778300599'
      ORA-17503: ksfdopn:2 Failed to open file
      +STBY_REDO1/stby/onlinelog/group_206.364.778300599
      ORA-15012: ASM file '+STBY_REDO1/stby/onlinelog/group_206.364.778300599'
      does not exist


      The main problem now is that my alert.log is growing up very fast. Every second i get a message in the log file that the files are missing. So after a few hours the alert.log is ~1GB in size.

      Now my question:
      How can i drop the standby logfiles from controlfile? Is it only possible by recreating the controlfile without the information about the standby logfiles?


      Thanks to All
      Thomas
        • 1. Re: How to drop not existing STANDBY LOGFILE from Controlfile
          904735
          Hi,

          Welcome to the Oracle forum.

          Do you want to drop alert log file, Right??

          If yes,

          You can remove that from os Level, ]$ rm <ur alert logfile name>.

          If alert log file size increases you can remove alert log file with out downtime, No need to recreate the control file.

          Just i am asking why you want to drop the logfile(Redo Log file)???

          Thanks,

          Srini...

          Edited by: Srini on Apr 19, 2012 2:29 AM
          • 2. Re: How to drop not existing STANDBY LOGFILE from Controlfile
            839439
            Hii

            Follow the below steps

            1.) Cancel the media recovery as
            SQL> alter database recover managed standby database cancel


            2.) change the standby auto management parameter to manual as
            SQL> alter system set standby_file_management='MANUAL'


            3.) Now drop the standby redolog files as
            SQL>alter database drop standby logfile group n


            Check the below link for more details

            http://neeraj-dba.blogspot.in/2011/10/how-to-droprename-standby-redolog-file.html


            Hope this may help you


            --neeraj                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
            • 3. Re: How to drop not existing STANDBY LOGFILE from Controlfile
              Nosmoht
              I tried to execute the steps from Vishen. But the first statement is running in an error. I have to say that i am not very familar with data gurad, so at the moment i do not realy know what the statement is for.

              Here is the output:

              SQL> alter database recover managed standby database cancel;
              alter database recover managed standby database cancel
              *
              ERROR at line 1:
              ORA-01665: control file is not a standby control file


              SQL> alter system set standby_file_management='MANUAL';

              System altered.

              SQL> alter database drop standby logfile group 206;
              alter database drop standby logfile group 206
              *
              ERROR at line 1:
              ORA-00313: open failed for members of log group 206 of thread 2
              ORA-00312: online log 206 thread 2:
              '+STBY_REDO2/stby/onlinelog/group_206.364.778300599'
              ORA-17503: ksfdopn:2 Failed to open file
              +STBY_REDO2/stby/onlinelog/group_206.364.778300599
              ORA-15012: ASM file '+STBY_REDO2/stby/onlinelog/group_206.364.778300599'
              does not exist
              ORA-00312: online log 206 thread 2:
              '+STBY_REDO1/stby/onlinelog/group_206.364.778300599'
              ORA-17503: ksfdopn:2 Failed to open file
              +STBY_REDO1/stby/onlinelog/group_206.364.778300599
              ORA-15012: ASM file '+STBY_REDO1/stby/onlinelog/group_206.364.778300599'
              does not exist

              Edited by: 928544 on 19.04.2012 11:46
              • 4. Re: How to drop not existing STANDBY LOGFILE from Controlfile
                839439
                ERROR
                ORA-01665: control file is not a standby control file

                This error occur because you actually did not create standby controlfile,or may you created but did not change control_files initialization parameter in standby database pfile/spfile. First check this.


                Error Description:
                Control file is not a standby control file

                Error Cause:
                Attempting to mount, recover or activate a standby database without a standby control file.

                Action:
                Create a standby control file before attempting to use the database as a standby database.


                Good Luck


                --neeraj                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                • 5. Re: How to drop not existing STANDBY LOGFILE from Controlfile
                  Nosmoht
                  Thanks for that information. I will try this and give a feedback. Thanks.
                  • 6. Re: How to drop not existing STANDBY LOGFILE from Controlfile
                    Seberg
                    Why drop when you can REUSE?

                    ALTER DATABASE ADD STANDBY LOGFILE '<your_path_name' SIZE ??M REUSE;

                    Cooper
                    • 7. Re: How to drop not existing STANDBY LOGFILE from Controlfile
                      Oceaner
                      Hi,

                      What you can do is, on your test system where you have done restore of database from standby database..

                      1. Do a backup of controlfile to trace.
                      2. Shutdown the database (Shutdown immediate)
                      3. Create a create controlfile script from the backup of the controlfile taken at step1.
                      4. Edit the create controlfile script to remove the standby log files.
                      5. Open database in Nomount state.
                      6. Re-create controlfile.
                      7. open the database.

                      Regards