5 Replies Latest reply: Jan 17, 2013 11:40 AM by mseberg RSS

    standby redo log does not exist...

    977635
      Hello,

      Oracle 11.2.0.2, running on Solaris

      This is kind of a continuation of a previous thread, but this is a different question:

      I have a DG configuration, with the primary database having 2 redo logs per redo log group.
      And, it has 2 redo logs for the standby redo log groups.

      But I just found out something very strange:

      For the standby redo log group, even though the database shows it has two redo log files per group, the second file does not actually exist on the file system.
      And, I have no idea where it is, or why the database is not complaining about it.

      See below:
      SQL> select * from v$logfile;
      
               GROUP# STATUS    TYPE    MEMBER                                        IS_
      --------------- --------- ------- --------------------------------------------- ---
                   11           STANDBY /opt/oracle/oradata2/PROD/REDO01A_STDBY.log NO
                   11           STANDBY /opt/oracle/oradata3/PROD/REDO01B_STDBY.log NO    <== does not exist on file system
                   12           STANDBY /opt/oracle/oradata2/PROD/REDO02A_STDBY.log NO
                   12           STANDBY /opt/oracle/oradata3/PROD/REDO02B_STDBY.log NO    <== does not exist on file system
                   13           STANDBY /opt/oracle/oradata2/PROD/REDO03A_STDBY.log NO
                   13           STANDBY /opt/oracle/oradata3/PROD/REDO03B_STDBY.log NO    <== does not exist on file system
                   14           STANDBY /opt/oracle/oradata2/PROD/REDO04A_STDBY.log NO
                   14           STANDBY /opt/oracle/oradata3/PROD/REDO04B_STDBY.log NO    <== does not exist on file system
                   15           STANDBY /opt/oracle/oradata2/PROD/REDO05A_STDBY.log NO
                   15           STANDBY /opt/oracle/oradata3/PROD/REDO05B_STDBY.log NO    <== does not exist on file system
                    5           ONLINE  /opt/oracle/oradata1/PROD/REDO05A.log       NO
                    5           ONLINE  /opt/oracle/oradata2/PROD/REDO05B.log       NO
                    6           ONLINE  /opt/oracle/oradata1/PROD/REDO06A.log       NO
                    6           ONLINE  /opt/oracle/oradata2/PROD/REDO06B.log       NO
                    7           ONLINE  /opt/oracle/oradata1/PROD/REDO07A.log       NO
                    7           ONLINE  /opt/oracle/oradata2/PROD/REDO07B.log       NO
                    8           ONLINE  /opt/oracle/oradata1/PROD/REDO08A.log       NO
                    8           ONLINE  /opt/oracle/oradata2/PROD/REDO08B.log       NO
      
      18 rows selected.
      
      Notice below that the "B" redo logs do not exist.
      
      SQL> !ls -l /opt/oracle/oradata3/PROD/REDO01B_STDBY.log
      /opt/oracle/oradata3/PROD/REDO01B_STDBY.log: No such file or directory
      
      SQL> !ls -l /opt/oracle/oradata3/PROD/REDO02B_STDBY.log
      /opt/oracle/oradata3/PROD/REDO02B_STDBY.log: No such file or directory
      
      SQL> !ls -l /opt/oracle/oradata3/PROD/REDO03B_STDBY.log
      /opt/oracle/oradata3/PROD/REDO03B_STDBY.log: No such file or directory
      
      SQL> !ls -l /opt/oracle/oradata3/PROD/REDO04B_STDBY.log
      /opt/oracle/oradata3/PROD/REDO04B_STDBY.log: No such file or directory
      
      SQL> !ls -l /opt/oracle/oradata3/PROD/REDO05B_STDBY.log
      /opt/oracle/oradata3/PROD/REDO05B_STDBY.log: No such file or directory
      
      But here, you can see that the "A" redo logs actually do exist.
      
      SQL> !ls -l /opt/oracle/oradata2/PROD/REDO01A_STDBY.log
      -rw-r-----   1 oracle   dba      536871424 Jan  7  2011 /opt/oracle/oradata2/PROD/REDO01A_STDBY.log
        • 1. Re: standby redo log does not exist...
          mseberg
          Hello;

          I'm able to recreate
          SQL> select * from v$logfile;
          
              GROUP# STATUS  TYPE    MEMBER                                             IS_
          ---------- ------- ------- -------------------------------------------------- ---
                   3         ONLINE  /u01/app/oracle/flash_recovery_area/RECOVER2/onlin YES
                                     elog/o1_mf_3_8gtxxrl6_.log
          
                   2         ONLINE  /u01/app/oracle/flash_recovery_area/RECOVER2/onlin YES
                                     elog/o1_mf_2_8gtxxr4f_.log
          
                   1         ONLINE  /u01/app/oracle/flash_recovery_area/RECOVER2/onlin YES
                                     elog/o1_mf_1_8gtxxqng_.log
          
                   4         STANDBY /u01/app/oracle/oradata/RECOVER2/redo04.log        NO
                   5         STANDBY /u01/app/oracle/oradata/RECOVER2/redo05.log        NO
                   6         STANDBY /u01/app/oracle/oradata/RECOVER2/redo06.log        NO
          And then
          SQL> !ls -al /u01/app/oracle/oradata/RECOVER2/redo04.log
          ls: /u01/app/oracle/oradata/RECOVER2/redo04.log: No such file or directory
          Checking... *Not there, but Oracle ( 11.2.0.3 ) allows clean up without barking. Had only one member so I did GROUP drop.
          SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
          
          Database altered.
          
          SQL> select * from v$logfile;
          
              GROUP# STATUS  TYPE    MEMBER                                             IS_
          ---------- ------- ------- -------------------------------------------------- ---
                   3         ONLINE  /u01/app/oracle/flash_recovery_area/RECOVER2/onlin YES
                                     elog/o1_mf_3_8gtxxrl6_.log
          
                   2         ONLINE  /u01/app/oracle/flash_recovery_area/RECOVER2/onlin YES
                                     elog/o1_mf_2_8gtxxr4f_.log
          
                   1         ONLINE  /u01/app/oracle/flash_recovery_area/RECOVER2/onlin YES
                                     elog/o1_mf_1_8gtxxqng_.log
          
                   5         STANDBY /u01/app/oracle/oradata/RECOVER2/redo05.log        NO
                   6         STANDBY /u01/app/oracle/oradata/RECOVER2/redo06.log        NO
          
          SQL> 
          Best Regards

          mseberg
          • 2. Re: standby redo log does not exist...
            977635
            How is that possible? And, how can the standby log file be used by the standby database if it doesn't exist on the primary?

            Also, for the ones in my database that "do" exist, I notice they are about half the size of the primary (regular) redo logs.

            This is really strange stuff...
            • 3. Re: standby redo log does not exist...
              mseberg
              Hello again;

              All I can think of is when you move a datafile ( not redo ).
              You take the tablespace offline
              
              You move the file at the OS level
              
              You do an "alter database" to tell Oracle where the file is
              
              You bring the tablespace back online
              So Oracle did not know where the file is, and because its redo or standby redo is does not bark as fast as a datafile.

              Check the second piece of my first reply where Oracle lets me remove it.

              A file has been removed, but Oracle does not know it yet


              Best Regards

              mseberg
              • 4. Re: standby redo log does not exist...
                977635
                Well, that sure is some strange behavior.
                So, i went ahead and dropped the files from the database that did not exist on the OS.
                No complaints, and everything still works normally (next day).
                • 5. Re: standby redo log does not exist...
                  mseberg
                  Kind of.

                  I ran into this testing loss of Redo awhile back.

                  Deleted the Redo at the OS and Oracle never did bark. ( later it made me pay but it was a test )

                  Best Regards

                  mseberg