5 Replies Latest reply on Aug 4, 2011 5:45 PM by mseberg

    Need to drop a current redo log on a Standby

    user13129655
      Oracle 11.2.0.2

      I have 3 redo logs on a newly created Standby database.

      SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

      GROUP# ARC STATUS
      ---------- --- ----------------
      1 YES UNUSED
      3 NO CURRENT
      2 YES UNUSED

      The redo log in Group 3 needs to be dropped and recreated. How can I force a log switch on a Standby in mount mode?

      SQL> alter database drop logfile group 3;
      alter database drop logfile group 3
      *
      ERROR at line 1:
      ORA-01623: log 3 is current log for instance ufms216 (thread 1) - cannot drop
      ORA-00312: online log 3 thread 1: '/path/redo03.dbf'


      SQL> alter system switch logfile;
      alter system switch logfile
      *
      ERROR at line 1:
      ORA-01109: database not open


      Please help !!!

      Thanks!
      Dave
        • 1. Re: Need to drop a current redo log on a Standby
          mseberg
          Stop recovery first
          alter database recover managed standby database cancel;
          The shutdown and restart and retry.

          When complete shutdown, startup mount, and restart the recovery.

          Best Regards

          mseberg
          • 2. Re: Need to drop a current redo log on a Standby
            sb92075
            user13129655 wrote:
            Oracle 11.2.0.2

            I have 3 redo logs on a newly created Standby database.

            SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

            GROUP# ARC STATUS
            ---------- --- ----------------
            1 YES UNUSED
            3 NO CURRENT
            2 YES UNUSED

            The redo log in Group 3 needs to be dropped and recreated. How can I force a log switch on a Standby in mount mode?

            SQL> alter database drop logfile group 3;
            alter database drop logfile group 3
            *
            ERROR at line 1:
            ORA-01623: log 3 is current log for instance ufms216 (thread 1) - cannot drop
            ORA-00312: online log 3 thread 1: '/path/redo03.dbf'


            SQL> alter system switch logfile;
            alter system switch logfile
            *
            ERROR at line 1:
            ORA-01109: database not open


            Please help !!!

            Thanks!
            Dave
            on Primary

            ALTER SYSTEM SWITCH LOGFILE;
            • 3. Re: Need to drop a current redo log on a Standby
              user13129655
              SAME ERROR/SITUATION

              SQL> startup mount
              ORACLE instance started.

              Total System Global Area 1.2527E+10 bytes
              Fixed Size 2231240 bytes
              Variable Size 3657434168 bytes
              Database Buffers 8858370048 bytes
              Redo Buffers 8691712 bytes
              Database mounted.
              SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

              GROUP# ARC STATUS
              ---------- --- ----------------
              1 YES UNUSED
              3 NO CURRENT
              2 YES UNUSED

              SQL> alter database drop logfile group 3;
              alter database drop logfile group 3
              *
              ERROR at line 1:
              ORA-01623: log 3 is current log for instance ufms216 (thread 1) - cannot drop
              ORA-00312: online log 3 thread 1: '/a0253/d01/oradata/ufms216/redo03.dbf'


              SQL> alter system switch logfile;
              alter system switch logfile
              *
              ERROR at line 1:
              ORA-01109: database not open


              SQL> alter database recover managed standby database cancel;
              alter database recover managed standby database cancel
              *
              ERROR at line 1:
              ORA-16136: Managed Standby Recovery not active


              SQL>


              HELP!
              • 4. Re: Need to drop a current redo log on a Standby
                user13129655
                anyone have ideas - i am stuck.
                • 5. Re: Need to drop a current redo log on a Standby
                  mseberg
                  My bad. I was thinking about "Standby redo".

                  There's an Oracle note which covers you.

                  Online Redo Logs on Physical Standby [ID 740675.1]

                  Sorry for the mistake and the delay.

                  Best Regards

                  mseberg