1 2 Previous Next 22 Replies Latest reply: Dec 11, 2013 6:27 AM by Pradeepcmst Go to original post RSS
      • 15. Re: Standby logfile missing error
        Pradeepcmst

        Hi ,

        Thanks for the input.

        in prod , we have 2 nodes, hsprm1, hsprm2

        and standby is a standalone one hsstl..

        now when i dig deeper, i got this error from primary node 2 alert log file

        ( " Error 1031 received logging on to the standby

        Fri Nov 29 14:51:36 2013

        Error 1031 received logging on to the standby

        Fri Nov 29 14:52:37 2013

        Error 1031 received logging on to the standby ")

         

        so now it seems related to password file.

        can you tell me whether i need to transfer both primary node 1 and node2  password file to standby oracle_home/dbs location

        or we can create a password file directly in standby using orapwd utility

         

        Regards

        • 16. Re: Standby logfile missing error
          mseberg

          Hello;

           

          Yes you need to move a copy of the password file to all nodes. All nodes should be using the same password file.

           

          Then stop recovery and shutdown so everything starts up using the password file your moved.

           

          Then start recovery again.

           

          Best Regards

           

          mseberg

          • 17. Re: Standby logfile missing error
            CKPT

            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.

            How can you check whether archives are applied or not by checking log file locations from OS?

            You can check only from views

            primary: select thread#,max(sequence#) from v$archived_log group by thread#;

            Standby: select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

             

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

            I won't agree if primary is shipping and standby is not applying on standby, until unless there is any issues with datafiles header or something else, You can stop and start MRP again and monitor alert log (or) share the alert log content after you started MRP.

             

            In your first output from v$standby_log

            GROUP# DBID                                        THREAD#  SE

             

            QUENCE#      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

             

            From your recent output 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

             


            So if you see in first output the group# 5 is been used, and in the second output the group#6 been used. Redo logs will be used in cyclic format. So once group6 is archived as soon as then the next standby log wil be in ACTIVE/Assigned, until that the status will be unassigned.

             

            If your primary is RAC, then you need to create standby log file groups for each thread. If each thread have 5 redo log groups for 2-node primary RAC, then you have to create 5 standby log groups for thread 1 and another 5 stadnby log file groups for thread 2 also....

             

            Apart from that any question?

            HTH

            • 18. Re: Standby logfile missing error
              Pradeepcmst

              HI ckpt,

              Yes, Thanks for the idea. i created the standby redo logs .. but still i can see all archive logs are shipping, all redo logs applying in thread 1 alone if i restart mrp process, and thread 2 not applying and also i noticed that the standby redo logs( group 8,9,10) are not used yet since its creation. So when i investigated further,

              i got this error from primary node 2 alert log file

              ( " Error 1031 received logging on to the standby

              Fri Nov 29 14:51:36 2013

              Error 1031 received logging on to the standby

              Fri Nov 29 14:52:37 2013

              Error 1031 received logging on to the standby ")

              . So as mseberg said, my understanding is  i need to create a password file in primary node1 db and pass it to primary node 2 and standby db as well...I will try and you know guys..

               

              By the way, i have another doubt here.

              Primary node 1: --> 4 online redo groups

              SQL> select * from v$log;

               

               

                  GROUP#   THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

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

                       1         1      12309   52428800        512          2 YES INACTIVE            7.3461E+10 2013-12-02:09:00:07   7.3466E+10 2013-12-02:10:03:51

                       2             12310   52428800        512          2 NO  CURRENT             7.3466E+10 2013-12-02:10:03:51   2.8147E+14

                       3             12449   52428800        512          2 NO  CURRENT             7.3462E+10 2013-12-02:09:09:20   2.8147E+14

                       4          2      12448   52428800        512          2 YES INACTIVE            7.3451E+10 2013-12-02:04:39:36   7.3462E+10 2013-12-02:09:09:20

               

              Primary node 2: --> 4 online redo groups

              SQL> select * from v$log;

               

               

                  GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

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

                       1          1      12309   52428800        512          2 YES INACTIVE            7.3461E+10 2013-12-02:09:00:07   7.3466E+10 2013-12-02:10:03:51

                       2         1      12310   52428800        512          2 NO  CURRENT             7.3466E+10 2013-12-02:10:03:51   2.8147E+14

                       3          2      12449   52428800        512          2 NO  CURRENT             7.3462E+10 2013-12-02:09:09:20   2.8147E+14

                       4          2      12448   52428800        512          2 YES INACTIVE            7.3451E+10 2013-12-02:04:39:36   7.3462E+10 2013-12-02:09:09:20

               

              you said "If your primary is RAC, then you need to create standby log file groups for each thread".. As i can see from above query, thread 1 for redo group 1 and 2 is assigned and thread 2 for redo group 3 and 4... So what is meant by a thread actually? When i check online , i found that a thread is a collection of log files. kindly explain what is thread and y its used?

              • 19. Re: Standby logfile missing error
                CKPT

                i created the standby redo logs .. but still i can see all archive logs are shipping, all redo logs applying in thread 1 alone if i restart mrp process, and thread 2 not applying and also i noticed that the standby redo logs( group 8,9,10) are not used yet since its creation. So when i investigated further,

                 

                If you have shared the content of the alert log after starting MRP then it could have much helpful to us.  standby redo log groups used not at apply and they used when it writing redo into standby redo log groups. So according to that you have issues to fetch the archive/redo from second node. I would say fix password file issue and then you resolved monitor and check still whether standby able to fetch archives or not. If still there are issues then post output from primary and stadnby using the below URL.

                 

                http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/

                 

                 

                Error 1031 received logging on to the standby ")

                . So as mseberg said, my understanding is  i need to create a password file in primary node1 db and pass it to primary node 2 and standby db as well...I will try and you know guys..

                You no need to create password file again on primary, If you are unsure the password hasn't changed by mistake in any one node, Then create a fresh password file in primary node 1 and then copy to all the other nodes and rename the password file according to the SID, Ex: orapw$ORACLE_SID

                 

                 

                Primary node 1: --> 4 online redo groups

                Primary node 2: --> 4 online redo groups

                SQL> select * from v$log;

                    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

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

                         1          1      12309   52428800        512          2 YES INACTIVE            7.3461E+10 2013-12-02:09:00:07   7.3466E+10 2013-12-02:10:03:51

                         2         1      12310   52428800        512          2 NO  CURRENT             7.3466E+10 2013-12-02:10:03:51   2.8147E+14

                         3          2      12449   52428800        512          2 NO  CURRENT             7.3462E+10 2013-12-02:09:09:20   2.8147E+14

                         4          2      12448   52428800        512          2 YES INACTIVE            7.3451E+10 2013-12-02:04:39:36   7.3462E+10 2013-12-02:09:09:20

                Redo log groups you can check from any node and you can view from any node, you no need to check from each node. By the above output. You have 2 redo log groups for each thread.  So if you have two standby redo log groups for each thread then you are fine.

                • 20. Re: Standby logfile missing error
                  Pradeepcmst

                  Hi Really thanks a lot.

                  But still i dint get the clear explanation. sorry. from below info

                  SQL> select * from v$log;

                      GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

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

                           1          1      12309   52428800        512          2 YES INACTIVE            7.3461E+10 2013-12-02:09:00:07   7.3466E+10 2013-12-02:10:03:51

                           2         1      12310   52428800        512          2 NO  CURRENT             7.3466E+10 2013-12-02:10:03:51   2.8147E+14

                           3          2      12449   52428800        512          2 NO  CURRENT             7.3462E+10 2013-12-02:09:09:20   2.8147E+14

                           4          2      12448   52428800        512          2 YES INACTIVE            7.3451E+10 2013-12-02:04:39:36   7.3462E+10 2013-12-02:09:09:20

                  I have two redo log group for each thread. I agree. but what is that thread actually means here.? y do they introduce the name thread? we can directly say as node 1 having 4 redo log groups(1,2,3,4) and node 2 having 4 redo log groups(1,2,3,4) , right? i know that the number of threads directly proportional to number of nodes in a RAC environment. Since i have two nodes, i have two threads totally. That i agree. but really wanted to know what is the use of thread... and y they introduced, what will happen if no thread is used,,,,

                  • 21. Re: Standby logfile missing error
                    CKPT

                    I have two redo log group for each thread. I agree. but what is that thread actually means here.? y do they introduce the name thread? we can directly say as node 1 having 4 redo log groups(1,2,3,4) and node 2 having 4 redo log groups(1,2,3,4) , right? i know that the number of threads directly proportional to number of nodes in a RAC environment. Since i have two nodes, i have two threads totally. That i agree. but really wanted to know what is the use of thread... and y they introduced, what will happen if no thread is used,,,,

                     

                    Any data, like update, delete, insert or any transactions data writtened into redo log files.

                    when comes to RAC as you know each instance have own thread, if there are two nodes/instances then two threads vice versa if you have 50 node/instance RAC then 50 threads.

                    Briefing more.....if you have instance PROD1, then only the PROD1 instance genereated redo will be wrttened in the redo logs of PROD1 vice versa the PROD2 instance generated redo will be writtened in the redo logs of PROD2 and the redo logs are not shared among the instances.

                    If you are more interested why it is named as thread, i would like to provide link, it might useful to you.

                     

                    Redo Threads

                    http://docs.oracle.com/cd/B19306_01/server.102/b14231/onlineredo.htm#i1006163

                     

                    HTH.

                    • 22. Re: Standby logfile missing error
                      Pradeepcmst

                      Hi mseberg,

                      I have transferred a copy of password file from primary nod1 to node2. But dint restart the node2 instance. It works!!.. All logs(thread 1  and thread 2) are moving to standby in a jiffy and applying as well..

                       

                       

                      Thanks all a lot.

                       

                       

                      Regards,

                      1 2 Previous Next