9 Replies Latest reply: Nov 15, 2012 11:31 AM by Meenakshy singh RSS

    Log out of sync

    Meenakshy singh
      Hi Gurus,

      Need urgent help .....My Primary and Secondary database logs are out of sync. I am new to this Dataguard and want help in fixing this issue. I went through some blogs and found people suggesting a restart of DR system .Not sure is it correct or not . Have posted below my Primary and DR databae current state :-

      PRIMARY DB
      =================
      Thread Last Sequence Generated
      ---------- -----------------------
      1 52293
      1 52293

      SQL> archive log list
      Database log mode Archive Mode
      Automatic archival Enabled
      Archive destination E:\oracle\P01\oraarch\P01arch
      Oldest online log sequence 52291
      Next log sequence to archive 52294
      Current log sequence 52294


      SQL> show parameter DG_BROKER_START

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      dg_broker_start boolean FALSE


      +++++++++++++++++
      SECONDARY
      +++++++++++++++++
      After sequence 52223 next log is missing ,after that all log recieved but not applied


      SEQUENCE# APP FIRST_TIM NEXT_TIME
      ---------- --- --------- ---------
      52215 YES 06-NOV-12 06-NOV-12
      52216 YES 06-NOV-12 06-NOV-12
      52217 YES 06-NOV-12 06-NOV-12
      52218 YES 06-NOV-12 06-NOV-12
      52219 YES 06-NOV-12 06-NOV-12
      52220 YES 06-NOV-12 06-NOV-12
      52221 YES 06-NOV-12 06-NOV-12
      52222 YES 06-NOV-12 06-NOV-12
      52223 YES 06-NOV-12 06-NOV-12
      52225 NO 06-NOV-12 07-NOV-12
      52226 NO 07-NOV-12 07-NOV-12

      SEQUENCE# APP FIRST_TIM NEXT_TIME
      ---------- --- --------- ---------
      52227 NO 07-NOV-12 07-NOV-12
      52228 NO 07-NOV-12 07-NOV-12
      52229 NO 07-NOV-12 07-NOV-12
      52230 NO 07-NOV-12 07-NOV-12
      52231 NO 07-NOV-12 07-NOV-12
      52232 NO 07-NOV-12 07-NOV-12
      52233 NO 07-NOV-12 07-NOV-12
      52234 NO 07-NOV-12 07-NOV-12
      52235 NO 07-NOV-12 07-NOV-12
      52236 NO 07-NOV-12 07-NOV-12
      52237 NO 07-NOV-12 07-NOV-12

      SEQUENCE# APP FIRST_TIM NEXT_TIME
      ---------- --- --------- ---------
      52238 NO 07-NOV-12 07-NOV-12
      52239 NO 07-NOV-12 07-NOV-12
      52240 NO 07-NOV-12 07-NOV-12
      52241 NO 07-NOV-12 07-NOV-12
      52242 NO 07-NOV-12 07-NOV-12
      52243 NO 07-NOV-12 07-NOV-12
      52244 NO 07-NOV-12 07-NOV-12
      52245 NO 07-NOV-12 07-NOV-12
      52246 NO 07-NOV-12 07-NOV-12
      52247 NO 07-NOV-12 07-NOV-12
      52248 NO 07-NOV-12 07-NOV-12

      SEQUENCE# APP FIRST_TIM NEXT_TIME
      ---------- --- --------- ---------
      52249 NO 07-NOV-12 07-NOV-12
      52250 NO 07-NOV-12 07-NOV-12
      52251 NO 07-NOV-12 07-NOV-12
      52252 NO 07-NOV-12 07-NOV-12
      52253 NO 07-NOV-12 07-NOV-12
      52254 NO 07-NOV-12 07-NOV-12
      52255 NO 07-NOV-12 07-NOV-12
      52256 NO 07-NOV-12 07-NOV-12
      52257 NO 07-NOV-12 07-NOV-12
      52258 NO 07-NOV-12 07-NOV-12
      52259 NO 07-NOV-12 07-NOV-12

      SEQUENCE# APP FIRST_TIM NEXT_TIME
      ---------- --- --------- ---------
      52260 NO 07-NOV-12 08-NOV-12
      52261 NO 08-NOV-12 08-NOV-12
      52262 NO 08-NOV-12 08-NOV-12
      52263 NO 08-NOV-12 08-NOV-12
      52264 NO 08-NOV-12 08-NOV-12
      52265 NO 08-NOV-12 08-NOV-12
      52266 NO 08-NOV-12 08-NOV-12
      52267 NO 08-NOV-12 08-NOV-12
      52268 NO 08-NOV-12 08-NOV-12
      52269 NO 08-NOV-12 08-NOV-12
      52270 NO 08-NOV-12 08-NOV-12

      SEQUENCE# APP FIRST_TIM NEXT_TIME
      ---------- --- --------- ---------
      52271 NO 08-NOV-12 08-NOV-12
      52272 NO 08-NOV-12 08-NOV-12
      52273 NO 08-NOV-12 08-NOV-12
      52274 NO 08-NOV-12 08-NOV-12
      52275 NO 08-NOV-12 08-NOV-12
      52276 NO 08-NOV-12 08-NOV-12
      52277 NO 08-NOV-12 08-NOV-12
      52278 NO 08-NOV-12 08-NOV-12
      52279 NO 08-NOV-12 08-NOV-12
      52280 NO 08-NOV-12 08-NOV-12
      52281 NO 08-NOV-12 08-NOV-12

      SEQUENCE# APP FIRST_TIM NEXT_TIME
      ---------- --- --------- ---------
      52282 NO 08-NOV-12 08-NOV-12
      52283 NO 08-NOV-12 08-NOV-12
      52284 NO 08-NOV-12 08-NOV-12
      52285 NO 08-NOV-12 08-NOV-12
      52286 NO 08-NOV-12 08-NOV-12
      52287 NO 08-NOV-12 08-NOV-12
      52288 NO 08-NOV-12 08-NOV-12
      52289 NO 08-NOV-12 08-NOV-12
      52290 NO 08-NOV-12 08-NOV-12
      52291 NO 08-NOV-12 08-NOV-12
      52292 NO 08-NOV-12 08-NOV-12

      SEQUENCE# APP FIRST_TIM NEXT_TIME
      ---------- --- --------- ---------
      52293 NO 08-NOV-12 08-NOV-12




      Thread Last Sequence Received Last Sequence Applied Difference
      ---------- ---------------------- --------------------- ----------
      1 52293 52223 70


      SQL> archive log list
      Database log mode Archive Mode
      Automatic archival Enabled
      Archive destination E:\oracle\P01\oraarch\P01arch
      Oldest online log sequence 52291
      Next log sequence to archive 0
      Current log sequence 52294
      SQL> select process,status,sequence# from v$managed_standby;

      PROCESS STATUS SEQUENCE#
      --------- ------------ ----------
      ARCH CLOSING 52292
      ARCH CLOSING 52293
      ARCH CLOSING 52289
      ARCH CLOSING 52290
      ARCH CLOSING 52291
      MRP0 WAIT_FOR_LOG 52224
      RFS IDLE 52294
      RFS IDLE 0
      RFS IDLE 0
      RFS IDLE 52224



      The MRP0 process is in WAIT_FOR_LOG. I think its hanged .Please let me know what i need to do in order to bring both primary ad secondary in sync.

      Please let me know how to do it manually and what other options are available for me.

      Thanks
      Meena
        • 1. Re: Log out of sync
          mseberg
          Meena;

          Missed this

          After sequence 52223 next log is missing

          Is this log available on the Primary still??

          If yes move with scp and register

          Example
          ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/STANDBY/archive/PRIMARY_1_20_716110538.arc';
          Can you run this query instead and post the results?

          http://www.visi.com/~mseberg/monitor_data_guard_transport.html

          Also

          Can you check both the Primary and Standby alert.logs for errors and post the errors?

          Best Regards

          mseberg

          Edited by: mseberg on Nov 8, 2012 10:40 AM
          • 2. Re: Log out of sync
            LaserSoft
            Hi,

            52224 Log is missing. If it is there then you move this file to standby database and you can register with the following command :

            ALTER DATABASE REGISTER LOGFILE 'E:\oracle\P01\oraarch\P01arch\52224.arc';

            Check the both primary and standby database alertlogfiles.

            Suggestions (In future) :

            1. Create one more archive log location (second location), so that logs will have in two locations.

            Thanks
            LaserSoft
            • 3. Re: Log out of sync
              CKPT
              Hello,

              So overall only one sequence *52224* is not appear on standby.
              Now first see whether this particular archive sequence is exist or not. If it is exist and not transferred to standby do as follows

              1) stop MRP
              2) start MRP
              3) post 100 lines of alert log file
              If you gather any info from primary log then that's great. And if there is no clue use this script and post output from primary and standby databases.
              http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/

              If there is no archive exist, try to Restore from backup if available. If there is no backup then you have only option of incremental roll forward. Use below link.
              http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/

              Thanks.
              • 4. Re: Log out of sync
                Meenakshy singh
                Hi mseberg,

                I have fired the below command ad have following output :-
                SQL> select sequence#,name from v$archived_log where sequence#=52224;

                no rows selected


                But if i go to physical directory where log are present i can see the log file present already.

                So I went and fired the 'ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\P01\ORAARCH\P01ARCHARC52225_0631237764.001' and it showed Database Altered.

                But still the MRP0 processing showign WAIT_FOR_LOG

                select process,status,sequence# from v$managed_standby;

                PROCESS STATUS SEQUENCE#
                --------- ------------ ----------
                ARCH CLOSING 52304
                ARCH CLOSING 52300
                ARCH CLOSING 52301
                ARCH CLOSING 52302
                ARCH CLOSING 52303
                MRP0 WAIT_FOR_LOG 52224
                RFS IDLE 52294
                RFS IDLE 0
                RFS IDLE 0
                RFS IDLE 52224
                RFS IDLE 52305

                PROCESS STATUS SEQUENCE#
                --------- ------------ ----------
                RFS IDLE 0


                Thanks
                Meena
                • 5. Re: Log out of sync
                  Meenakshy singh
                  Hi CKPT,

                  I can see the log file on the DR system but when i fire below command no output.

                  SQL> select sequence#,name from v$archived_log where sequence#=52224;

                  no rows selected

                  Thanks
                  Meena
                  • 6. Re: Log out of sync
                    Meenakshy singh
                    Hi All the Gurus,

                    The issue is been solved what I did is fired the alter register command and then started the MRP0 process which solved the issue.

                    Really thankful to all the people for your support and help which made my life easy as Oracle DBA.


                    Thanks
                    Meena
                    • 7. Re: Log out of sync
                      Meenakshy singh
                      Hi Gurus,
                      I have a query regarding interpretaion of the logs
                      can anybody help me interpreting this commands output ?

                      Have fired the below query twices once with applied='YES' and next time applied = NO. for the same sequence i can see applied to be Yes and No both.

                      1.I have query this command in primary database and the output is given below.
                      SQL>select sequence#,applied,first_time,next_time from v$archived_log where applied='YES' ;


                      SEQUENCE# APP FIRST_TIM NEXT_TIME
                      ---------- --- --------- ---------
                      51997 YES 26-OCT-12 26-OCT-12
                      51998 YES 26-OCT-12 26-OCT-12
                      51999 YES 26-OCT-12 26-OCT-12
                      52000 YES 26-OCT-12 26-OCT-12
                      52001 YES 26-OCT-12 26-OCT-12
                      52002 YES 26-OCT-12 26-OCT-12
                      52003 YES 26-OCT-12 27-OCT-12
                      52004 YES 27-OCT-12 27-OCT-12
                      52005 YES 27-OCT-12 27-OCT-12
                      52006 YES 27-OCT-12 27-OCT-12
                      52007 YES 27-OCT-12 27-OCT-12

                      SEQUENCE# APP FIRST_TIM NEXT_TIME
                      ---------- --- --------- ---------
                      52008 YES 27-OCT-12 27-OCT-12
                      52009 YES 27-OCT-12 27-OCT-12
                      52010 YES 27-OCT-12 27-OCT-12
                      52011 YES 27-OCT-12 27-OCT-12
                      $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
                      ##############################################


                      2.Again i fired query with applied = NO.

                      SQL>select sequence#,applied,first_time,next_time from v$archived_log where applied='NO' ;

                      SEQUENCE# APP FIRST_TIM NEXT_TIME
                      ---------- --- --------- ---------
                      51991 NO 26-OCT-12 26-OCT-12
                      51992 NO 26-OCT-12 26-OCT-12
                      51993 NO 26-OCT-12 26-OCT-12
                      51994 NO 26-OCT-12 26-OCT-12
                      51995 NO 26-OCT-12 26-OCT-12
                      51996 NO 26-OCT-12 26-OCT-12
                      51997 NO 26-OCT-12 26-OCT-12
                      51998 NO 26-OCT-12 26-OCT-12
                      51999 NO 26-OCT-12 26-OCT-12
                      52000 NO 26-OCT-12 26-OCT-12
                      52001 NO 26-OCT-12 26-OCT-12

                      SEQUENCE# APP FIRST_TIM NEXT_TIME
                      ---------- --- --------- ---------
                      52002 NO 26-OCT-12 26-OCT-12
                      52003 NO 26-OCT-12 27-OCT-12
                      52004 NO 27-OCT-12 27-OCT-12
                      52005 NO 27-OCT-12 27-OCT-12
                      52006 NO 27-OCT-12 27-OCT-12
                      52007 NO 27-OCT-12 27-OCT-12
                      52008 NO 27-OCT-12 27-OCT-12
                      52009 NO 27-OCT-12 27-OCT-12
                      52010 NO 27-OCT-12 27-OCT-12
                      52011 NO 27-OCT-12 27-OCT-12
                      52012 NO 27-OCT-12 27-OCT-12

                      SEQUENCE# APP FIRST_TIM NEXT_TIME
                      ---------- --- --------- ---------
                      52012 NO 27-OCT-12 27-OCT-12
                      52013 NO 27-OCT-12 27-OCT-12
                      52013 NO 27-OCT-12 27-OCT-12
                      52014 NO 27-OCT-12 27-OCT-12
                      52014 NO 27-OCT-12 27-OCT-12
                      52015 NO 27-OCT-12 27-OCT-12
                      52015 NO 27-OCT-12 27-OCT-12
                      52016 NO 27-OCT-12 27-OCT-12
                      52016 NO 27-OCT-12 27-OCT-12
                      52017 NO 27-OCT-12 27-OCT-12
                      52017 NO 27-OCT-12 27-OCT-12

                      can anybody help me interpreting this command output? The query result for paremeter = YES and NO is same . I am unable to understand weather the logs are applied or not.
                      • 8. Re: Log out of sync
                        mseberg
                        Hello again;

                        If you take both the YES and the NO out you will see there is a LOG for the Primary side and one for the Standby side.

                        I have changed this query will post in a minute

                        Change SYSDATE -1 and DEST_ID = 2 as needed for your system.
                        clear screen
                        set linesize 100
                         
                        column STANDBY format a20
                        column applied format a10
                        
                        
                        SELECT  
                          NAME AS STANDBY, 
                          SEQUENCE#, 
                          APPLIED, 
                          COMPLETION_TIME 
                        FROM 
                         V$ARCHIVED_LOG 
                        WHERE  
                          DEST_ID = 2 
                        AND 
                         NEXT_TIME > SYSDATE -1 
                        ORDER BY 
                          SEQUENCE#;
                        Also if you add DEST_ID to your query it make help explain the first question.

                        Example ( without YES or NO )
                        SELECT 
                          SEQUENCE#,
                          APPLIED,
                          FIRST_TIME,
                          NEXT_TIME,
                          DEST_ID 
                        FROM 
                          V$ARCHIVED_LOG;   
                        Best Regards

                        mseberg

                        Edited by: mseberg on Nov 15, 2012 10:02 AM
                        • 9. Re: Log out of sync
                          Meenakshy singh
                          Hi mseberg,

                          I got your point , but I have a got some query after firing the commands given by you.I can see before 27 Oct the Primary DB logs are in NO whereas the secondary aare in YES. But after 27 Oct my log are not getting applied on secondary as well. So i this somethign alarming for me and my database .If so then what steps i need to take to prevent it.

                          SQL> select sequence#,applied,first_time,next_time,dest_id from v$archived_log;

                          52008 NO 27-OCT-12 27-OCT-12 1
                          52009 YES 27-OCT-12 27-OCT-12 2
                          52009 NO 27-OCT-12 27-OCT-12 1
                          52010 NO 27-OCT-12 27-OCT-12 1
                          52010 YES 27-OCT-12 27-OCT-12 2
                          52011 YES 27-OCT-12 27-OCT-12 2
                          52011 NO 27-OCT-12 27-OCT-12 1
                          52012 NO 27-OCT-12 27-OCT-12 1
                          52012 NO 27-OCT-12 27-OCT-12 2
                          52013 NO 27-OCT-12 27-OCT-12 1

                          52013 NO 27-OCT-12 27-OCT-12 2
                          52014 NO 27-OCT-12 27-OCT-12 2
                          52014 NO 27-OCT-12 27-OCT-12 1
                          52015 NO 27-OCT-12 27-OCT-12 1
                          52015 NO 27-OCT-12 27-OCT-12 2
                          52016 NO 27-OCT-12 27-OCT-12 1
                          52016 NO 27-OCT-12 27-OCT-12 2
                          52017 NO 27-OCT-12 27-OCT-12 2
                          52017 NO 27-OCT-12 27-OCT-12 1
                          52018 NO 27-OCT-12 27-OCT-12 1
                          52018 NO 27-OCT-12 27-OCT-12 2


                          select name as standby,sequence#,applied,completion_time from v$archived_log where dest_id=2 and next_time > sysdate -1 order by sequence#;

                          STANDBY
                          --------------------------------------------------------------------------------
                          SEQUENCE# APP COMPLETIO
                          ---------- --- ---------
                          P01_DR
                          52414 NO 15-NOV-12

                          P01_DR
                          52415 NO 15-NOV-12

                          P01_DR
                          52416 NO 15-NOV-12


                          STANDBY
                          --------------------------------------------------------------------------------
                          SEQUENCE# APP COMPLETIO
                          ---------- --- ---------
                          P01_DR
                          52418 NO 15-NOV-12

                          P01_DR
                          52419 NO 15-NOV-12

                          P01_DR
                          52420 NO 15-NOV-12


                          STANDBY
                          --------------------------------------------------------------------------------
                          SEQUENCE# APP COMPLETIO
                          ---------- --- ---------
                          P01_DR
                          52421 NO 15-NOV-12

                          P01_DR
                          52422 NO 15-NOV-12

                          P01_DR
                          52423 NO 15-NOV-12


                          STANDBY
                          --------------------------------------------------------------------------------
                          SEQUENCE# APP COMPLETIO
                          ---------- --- ---------
                          P01_DR
                          52424 NO 15-NOV-12

                          P01_DR
                          52425 NO 15-NOV-12

                          P01_DR
                          52426 NO 15-NOV-12


                          STANDBY
                          --------------------------------------------------------------------------------
                          SEQUENCE# APP COMPLETIO
                          ---------- --- ---------
                          P01_DR
                          52427 NO 15-NOV-12

                          P01_DR
                          52428 NO 15-NOV-12

                          P01_DR
                          52429 NO 15-NOV-12


                          STANDBY
                          --------------------------------------------------------------------------------
                          SEQUENCE# APP COMPLETIO
                          ---------- --- ---------
                          P01_DR
                          52430 NO 15-NOV-12

                          P01_DR
                          52431 NO 15-NOV-12

                          P01_DR
                          52432 NO 16-NOV-12


                          18 rows selected.


                          Thanks
                          Meena