1 2 Previous Next 22 Replies Latest reply: Dec 11, 2013 6:27 AM by Pradeepcmst RSS

    Standby logfile missing error

    Pradeepcmst

      Hello all,

      I am getting this following error in standby alertlog file.

       

      ORA-00313: open failed for members of log group 8 of thread 2

      ORA-00312: online log 8 thread 2: '+DG_FLASH/hsstl/onlinelog/group_8.273.779638759'

      ORA-17503: ksfdopn:2 Failed to open file +DG_FLASH/hsstl/onlinelog/group_8.273.779638759

      ORA-15012: ASM file '+DG_FLASH/hsipstl/onlinelog/group_8.273.779638759' does not exist

      ORA-00312: online log 8 thread 2: '+DG_DATA/hsstl/onlinelog/group_8.1766.779638759'

      ORA-17503: ksfdopn:2 Failed to open file +DG_DATA/hsstl/onlinelog/group_8.1766.779638759

      ORA-15012: ASM file '+DG_DATA/hsipstl/onlinelog/group_8.1766.779638759' does not exist

       

      When i manually check the diskgroup '+DG_FLASH, +DG_DATA, i can see the group_8 file with some other numbers.

      Kindly let me know how to proceed for that. oracle 11g database, standalone standby database in aix box

      My guess is control file is still thinking that the group 8 file is present and looks for it when starting the standby, but actually that file is not there in the diskgroup. instead some other filename is there for the same group.

       

      Either i want to update the control file about the new log group 8, or drop the log 8 group, delete the log 8 group files from the disgroups +DG_FLASH, +DG_DATA and create a new log 8 group

      Kindly suggest

       

      Regards,

        • 1. Re: Standby logfile missing error
          mseberg

          Question.

           

          Are you using different ASM diskgroups on Primary and Standby servers?

           

          Best Regards

           

          mseberg

          • 2. Re: Standby logfile missing error
            Pradeepcmst

            No, both are different servers but standby locations are configured as like in primary

             

             

            Regards,

            • 3. Re: Standby logfile missing error
              CKPT

              Even it is ASM or non-ASM, in such case it may work for you

              1) Stop MRP and set STANDBY_FILE_MANAMGEMT to MANUAL

              2) Drop the logfile group

               

              If any one of standby redo log is Clearing/Current, then after a switch oracle uses other standby redo log and then you can able to drop.

               

              HTH.

              • 4. Re: Standby logfile missing error
                Pradeepcmst

                HI CKPT,

                After dropping the group 8 standby redolog, do i need to recreate again?

                • 5. Re: Standby logfile missing error
                  CKPT

                  Yes, you have to create standby redo log files.

                  Note that you must have standby redo logs with same or more size of than online redo logfiles and you must have same or more number of redo log groups as well.

                   

                  Thanks.

                  • 6. Re: Standby logfile missing error
                    Pradeepcmst

                    Thanks.

                    So these steps wont impact production db in any ways right?

                     

                    1) Stop MRP and set STANDBY_FILE_MANAMGEMT to MANUAL

                    2) Drop the logfile group

                    3) create a new logfile group.

                     

                    One more question, Since we set STANDBY_FILE_MANAMGEMT to MANUAL, the prod controlfile wont be able to know the standby redo log changes in standby databases.

                    so whether the prod db should know about new standby redo log created, or it doesnt need to know about the changes what we do now

                     

                     

                    Regards,

                    • 7. Re: Standby logfile missing error
                      CKPT

                      When STANDBY_FILE_MANAGMENT is AUTO then data file additions and deletions on the primary database are replicated on the standby database.

                      Primary database doesn't really care whether the standby redo log files are created or not at standby database, Even you can have more standby redo logs on standby and again oracle is not going to ask you or standby. Redo will be transferred from primary and in case if you are using real time apply then of course it writes into standby redo log files continuosly(you can monitor from SQL> select blocks from v$managed_standby), if standby redo log files not available then it sends the archive log files.

                      HTH

                      • 8. Re: Standby logfile missing error
                        Pradeepcmst

                        Hi,

                        I recreated the standby redo log groups 8,9 and 10., then bounced the standby database and put it in managed recovery mode. Now i did not get the error mess which i posted at the top in alert file. Then i observed the archive log catch up in standby. initially i found all pending archives are applied to standby. but after some time i found some archivelogs not applied when i queried the v$archived_log view where applied=no. but i am sure that all archive logs are transferring to standby. but not appliying. So i just tried to stop the mrp process and restarted again. This time again the pending archives are applied and after some time the logs are not applied but transfering to standby database perfectly. something is wrong in standby , im not sure. When i query v$standby_log, i got the following output

                         

                        SQL> select * from v$standby_log;

                         

                         

                            GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME

                        ---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------

                                 5 3108685833                                        1      12259   52428800        512    2038784 YES ACTIVE        7.3211E+10 2013-11-28:16:01:41   7.3211E+10 2013-11-28:16:02:49   7.3211E+10 2013-11-28:16:02:49

                                 6 UNASSIGNED                                        1          0   52428800        512          0 NO  UNASSIGNED

                                 7 UNASSIGNED                                        1          0   52428800        512          0 NO  UNASSIGNED

                                 8 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED

                                 9 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED

                                10 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED

                         

                         

                        6 rows selected.

                         

                        I compared the same query with my other standby environment, and i can see the THREAD# column is having values for all standby log groups. But here i dont have any values assigned to the standby redo groups what i created( ie, group 8,9, and 10)...

                         

                        Kindly let me know how to proceed/ troubleshoot.

                         

                         

                        Regards,

                        • 9. Re: Standby logfile missing error
                          Pradeepcmst

                          Additional info:

                           

                          SQL> select process, sequence#, thread#, status from v$managed_standby;

                           

                           

                          PROCESS    SEQUENCE#    THREAD# STATUS

                          --------- ---------- ---------- ------------

                          ARCH               0          0 CONNECTED

                          ARCH           12251          1 CLOSING

                          ARCH               0          0 CONNECTED

                          ARCH           12257          1 CLOSING

                          ARCH           12258          1 CLOSING

                          ARCH           12254          1 CLOSING

                          ARCH           12255          1 CLOSING

                          ARCH               0          0 CONNECTED

                          ARCH           12256          1 CLOSING

                          ARCH               0          0 CONNECTED

                          MRP0           12410          2 WAIT_FOR_LOG

                          RFS            12259          1 IDLE

                          RFS                0          0 IDLE

                          RFS                0          0 IDLE

                          RFS                0          0 IDLE

                          RFS                0          0 IDLE

                           

                          here the sequence number 12251,12254,12256,12257,1228 are showing status as CLOSING ever.

                          • 10. Re: Standby logfile missing error
                            mseberg

                            Hello;

                             

                            I think I would try to clear the log :

                             

                            Example

                             

                            ALTER DATABASE CLEAR LOGFILE GROUP 3;

                             

                            Do this for any groups you are having this issue with.

                             

                            Best Regards

                             

                            mseberg

                            • 11. Re: Standby logfile missing error
                              CKPT

                              From standby with v$standby_log you have to compare in primary with v$log, If there are no standby redo logs and primary is sending redo by LGWR then you might see messages in standby alert log as "No standby log files available(kind of)"

                              So you just need to ensure you must have same or more size and same or more number of standby redo logs than online redo logs of primary.

                               

                              Now regarding the gap, from v$managed_standby it looks sequence 12410 is waiting to fetch from primary, you need to ensure all of the archives are available on primary database if they are not shipped to standby.

                               

                              You can use below query to check archives applied/difference status by running from standby database.

                               

                              SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

                              FROM

                              (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

                              (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

                              WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

                              • 12. Re: Standby logfile missing error
                                Pradeepcmst

                                HI,

                                I ran the above query

                                Thread Last Sequence Received Last Sequence Applied Difference

                                ---------- ---------------------- --------------------- ----------

                                         1                  12268                 12257         11

                                         2                  12409                 12409          0

                                But when i check the archive log location, i can see all logs which are not applied , but it was shipped perfectly to standby environment . i can also see no archive gaps.

                                So now the problem is all archive logs shipping from prod to standby db. but not applying...

                                 

                                mseberg: yes i thought of clearing the logfile group , but i fear that i might lose data....

                                Here is the redo and standby redo log structure.

                                in prod: totally 10 redo groups, out of which 4 online group(1,2,3,4)  and 6 standby redo log group(5,6,7,8,9,10) where each group consists of 2 members

                                in standby : totally 10 redo groups, out of which 4 online group(1,2,3,4)  and 6 standby redo log group(5,6,7,8,9,10) where each group consists of 2 members . and according to my issue at the top, i dropped the standby redo log group 8,9,10 since it does not exist and recreated 8, 9, 10 groups again.

                                 

                                Kindly suggest what can we do, if all logs are shipping perfect, but not applying in standby environment. it will just perform recovery in standby only if i restart the mrp process. and after some time it stalls. after restarting the mrp process, i checked the v$dataguard_status and found a line like "Fetching gap sequence in thread 2, gap sequence 12419-12419" , so i feel like thread 2 archive logs not assigning to any of the standby redo logs and something should be done with the standby redologs. might be clearing, but im too concerned about data loss since all logs shipping to standby perfectly.

                                 

                                Could someone throw some light..

                                 

                                Regards,

                                • 13. Re: Standby logfile missing error
                                  Pradeepcmst

                                  Additional info:

                                   

                                  SQL> select * from v$standby_log;

                                   

                                   

                                      GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME

                                  ---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------

                                           5 UNASSIGNED                                        1          0   52428800        512          0 NO  UNASSIGNED

                                           6 3108685833                                        1      12276   52428800        512   33756160 YES ACTIVE        7.3320E+10 2013-11-29:11:58:59   7.3323E+10 2013-11-29:12:50:36   7.3323E+10 2013-11-29:12:50:36

                                           7 UNASSIGNED                                        1          0   52428800        512          0 NO  UNASSIGNED

                                           8 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED

                                           9 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED

                                          10 UNASSIGNED                                        0          0   52428800        512          0 YES UNASSIGNED

                                   

                                  i can see that no threads are assigning to the newly created  standby redo log group 8,9,10... if anyone knows how to make it usable, we can try that.

                                  i started the db in managed recovery mode using this command. " alter database recover managed standby database disconnect;" . i dit not use 'using current controlfile" real time apply option

                                  Regards,

                                  • 14. Re: Standby logfile missing error
                                    mseberg

                                    Test

                                     

                                     

                                    SQL> Select GROUP#, THREAD#, SEQUENCE#, BYTES from v$standby_log;

                                     

                                      GROUP# THREAD# SEQUENCE# BYTES

                                    ---------- ---------- ---------- ----------

                                      4 1 0 52428800

                                      5 1 1327 52428800

                                      6 1 0 52428800

                                      

                                    SQL> alter database recover managed standby database cancel;

                                     

                                    Database altered.

                                     

                                    SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata//redo07.log' SIZE 50M;

                                     

                                    Database altered.

                                     

                                    SQL> alter database recover managed standby database disconnect from session;

                                     

                                    Database altered.

                                     

                                    SQL> Select GROUP#, THREAD#, SEQUENCE#, BYTES from v$standby_log;

                                     

                                      GROUP# THREAD# SEQUENCE# BYTES

                                    ---------- ---------- ---------- ----------

                                      4 1 0 52428800

                                      5 1 1327 52428800

                                      6 1 0 52428800

                                      7 0 0 52428800 

                                      

                                      

                                    ALTER DATABASE CLEAR LOGFILE GROUP 7; 

                                     

                                     

                                    SQL> alter database recover managed standby database cancel;

                                     

                                    Database altered.

                                     

                                    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;

                                     

                                    Database altered.

                                     

                                    SQL> alter database recover managed standby database disconnect from session;

                                     

                                    Database altered.

                                     

                                    SQL> Select GROUP#, THREAD#, SEQUENCE#, BYTES from v$standby_log;

                                     

                                      GROUP# THREAD# SEQUENCE# BYTES

                                    ---------- ---------- ---------- ----------

                                      4 1 0 52428800

                                      5 1 1327 52428800

                                      6 1 0 52428800

                                      7 0 0 52428800

                                      

                                      

                                    SQL> /

                                     

                                      GROUP# THREAD# SEQUENCE# BYTES

                                    ---------- ---------- ---------- ----------

                                      4 1 1330 52428800

                                      5 1 0 52428800

                                      6 1 0 52428800

                                      7 0 0 52428800

                                     

                                     

                                    What I noticed is no matter how many switches I do the Standby only uses group 4 and 5. If I recreate with a Thread number it seems to work.

                                     

                                    SQL> alter database recover managed standby database cancel;

                                     

                                    Database altered.

                                     

                                    SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;

                                     

                                    Database altered.

                                     

                                     

                                    SQL> !rm /u01/app/oracle/oradata//redo07.log

                                     

                                    SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 '/u01/app/oracle/oradata//redo07.log' SIZE 50M;

                                     

                                    Database altered.

                                     

                                    SQL> alter database recover managed standby database disconnect from session;

                                     

                                    Database altered.

                                     

                                    SQL> Select GROUP#, THREAD#, SEQUENCE#, BYTES from v$standby_log;

                                     

                                      GROUP# THREAD# SEQUENCE# BYTES

                                    ---------- ---------- ---------- ----------

                                      4 1 0 52428800

                                      5 1 1343 52428800

                                      6 1 0 52428800

                                      7 1 0 52428800

                                     

                                    SQL>

                                     

                                    Check

                                     

                                    SQL> select GROUP#,BYTES/1024/1024/1024 , STATUS, FIRST_TIME,NEXT_TIME from v$standby_log;

                                     

                                      GROUP# BYTES/1024/1024/1024 STATUS FIRST_TIM NEXT_TIME

                                    ---------- -------------------- ---------- --------- ---------

                                      4 .048828125 UNASSIGNED

                                      5 .048828125 ACTIVE 29-NOV-13

                                      6 .048828125 UNASSIGNED

                                      7 .048828125 UNASSIGNED

                                     

                                    SQL>

                                    1 2 Previous Next