9 Replies Latest reply: Feb 8, 2013 6:04 AM by 989969 RSS

    problem in sync between primary and standby

    989969
      Hi Gurus,

      i have a problem in my dataguard environment.

      mrp process waiting for the sequence no :5728

      and

      my log _history view shows  "5855"  as ------- > max(sequence#)

      what i did is i have taken an incremental backup from primary and applied in standby (as there was many arc. log files are missing in standby)

      help me with this .......

      my spool file
      =========

      SQL> select max(sequence#) from v$log_history;

      MAX(SEQUENCE#)
      --------------
      5855

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

      PROCESS STATUS SEQUENCE#
      --------- ------------ ----------
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      ARCH CONNECTED 0
      MRP0 WAIT_FOR_GAP 5728

      11 rows selected.

      regards and thanks
      gold
        • 1. Re: problem in sync between primary and standby
          989969
          database details
          ============

          my version 10.2.0.3
          windows server 2003

          more over there is no archive gap.. and the tns pings are successful
          • 2. Re: problem in sync between primary and standby
            mseberg
            Hello;

            Is that sequence ( 5855 ) log available on either the Primary or Standby server?

            Can you post the results of this query :

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

            Best Regards

            mseberg
            • 3. Re: problem in sync between primary and standby
              989969
              here is th o/p for the query


              DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
              ---------- -------------- ------------ ----------- -------------- -------
              WPLQDM WDESPDHAN20


              for your questions :

              5855 is qvailable in primary and

              the max(seq#) from primary is 5866

              in standby thre are arc.log files upto 5827
              • 4. Re: problem in sync between primary and standby
                mseberg
                OK

                So you have a good gap going. Can you move the log ( scp ) to the standby server and tried a manual apply?

                Example

                ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/STANDBY/archive/PRIMARY_1_21_716110538.arc';

                Then run the query again to confirm it applied.

                I would check both the Primary and Standby alert logs for errors and post if you find them.

                Best Regards

                mseberg
                • 5. Re: problem in sync between primary and standby
                  989969
                  fyi :

                  there was some problems in sync between primary and standby .. so we took a incremental bkp using scn from primary and applied on standby.. above in my post you can see my

                  in standby
                  =========

                  log_history --- view shows seq as "5855" but mrp wait for "5728"


                  primary alert log
                  ===============
                  Fri Feb 08 11:14:04 2013
                  starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
                  Fri Feb 08 11:14:04 2013
                  starting up 1 shared server(s) ...
                  Fri Feb 08 11:15:39 2013
                  alter database mount
                  Fri Feb 08 11:15:43 2013
                  Setting recovery target incarnation to 1
                  ARCH: STARTING ARCH PROCESSES
                  ARC0 started with pid=16, OS id=2060
                  ARC1 started with pid=17, OS id=1936
                  ARC2 started with pid=18, OS id=1188
                  ARC3 started with pid=19, OS id=4132
                  ARC4 started with pid=20, OS id=4144
                  ARC5 started with pid=21, OS id=4764
                  ARC6 started with pid=22, OS id=1316
                  ARC7 started with pid=23, OS id=3456
                  ARC8 started with pid=24, OS id=3196
                  ARC9 started with pid=25, OS id=3176
                  Fri Feb 08 11:15:44 2013
                  ARC0: Archival started
                  Fri Feb 08 11:15:44 2013
                  ARC1: Archival started
                  ARC2: Archival started
                  ARC3: Archival started
                  ARC4: Archival started
                  ARC5: Archival started
                  ARC6: Archival started
                  ARC7: Archival started
                  ARC8: Archival started
                  ARC9: Archival started
                  ARCH: STARTING ARCH PROCESSES COMPLETE
                  Fri Feb 08 11:15:44 2013
                  ARC0: Becoming the 'no FAL' ARCH
                  ARC0: Becoming the 'no SRL' ARCH
                  Fri Feb 08 11:15:44 2013
                  ARC1: Becoming the heartbeat ARCH
                  Fri Feb 08 11:15:44 2013
                  ARC9: Thread not mounted
                  Fri Feb 08 11:15:44 2013
                  Successful mount of redo thread 1, with mount id 169643035
                  Fri Feb 08 11:15:44 2013
                  Physical Standby Database mounted.
                  Completed: alter database mount
                  Fri Feb 08 11:15:45 2013
                  ARC5: Thread not mounted
                  ARC1: Thread not mounted
                  Fri Feb 08 11:15:47 2013
                  ARC8: Thread not mounted
                  ARC0: Thread not mounted
                  Fri Feb 08 11:15:49 2013
                  ARC3: Thread not mounted
                  Fri Feb 08 11:15:49 2013
                  ARC6: Thread not mounted
                  Fri Feb 08 11:15:50 2013
                  ARC2: Thread not mounted
                  Fri Feb 08 11:15:51 2013
                  ARC7: Thread not mounted
                  Fri Feb 08 11:15:52 2013
                  ARC4: Thread not mounted
                  Fri Feb 08 11:17:43 2013
                  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
                  MRP0 started with pid=26, OS id=5004
                  Managed Standby Recovery starting Real Time Apply
                  parallel recovery started with 3 processes
                  Fri Feb 08 11:17:48 2013
                  Errors in file e:\oracle\product\10.2.0\admin\wplqdm\bdump\wplqdm_mrp0_5004.trc:
                  ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [J:\ORADATA\WPLQDM\REDO01_3.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [E:\ORADATA\WPLQDM\REDO01_1.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the fil

                  Fri Feb 08 11:17:48 2013
                  Errors in file e:\oracle\product\10.2.0\admin\wplqdm\bdump\wplqdm_mrp0_5004.trc:
                  ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [J:\ORADATA\WPLQDM\REDO01_3.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [I:]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [E:\ORADATA\WPLQDM\REDO01_1.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the fil

                  Clearing online redo logfile 1 E:\ORADATA\WPLQDM\REDO01_1.RDO
                  Clearing online log 1 of thread 1 sequence number 5853
                  Fri Feb 08 11:17:48 2013
                  Errors in file e:\oracle\product\10.2.0\admin\wplqdm\bdump\wplqdm_mrp0_5004.trc:
                  ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [J:\ORADATA\WPLQDM\REDO01_3.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [I:]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [E:\ORADATA\WPLQDM\REDO01_1.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the fil

                  Fri Feb 08 11:17:48 2013
                  Errors in file e:\oracle\product\10.2.0\admin\wplqdm\bdump\wplqdm_mrp0_5004.trc:
                  ORA-19527: Message 19527 not found; No message file for product=RDBMS, facility=ORA
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [E:\ORADATA\WPLQDM\REDO01_1.RDO]

                  Clearing online redo logfile 1 complete
                  Media Recovery Waiting for thread 1 sequence 5728
                  Fetching gap sequence in thread 1, gap sequence 5728-5782
                  Fri Feb 08 11:17:49 2013
                  Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
                  Fri Feb 08 11:18:19 2013
                  FAL[client]: Failed to request gap sequence
                  GAP - thread 1 sequence 5728-5782
                  DBID 66669641 branch 703873929
                  FAL[client]: All defined FAL servers have been attempted.
                  -------------------------------------------------------------
                  Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
                  parameter is defined to a value that is sufficiently large
                  enough to maintain adequate log switch information to resolve
                  archivelog gaps.
                  -------------------------------------------------------------
                  Fri Feb 08 11:29:06 2013
                  db_recovery_file_dest_size of 30000 MB is 5.99% used. This is a
                  user-specified limit on the amount of space that will be used by this
                  database for recovery-related files, and does not reflect the amount of
                  space available in the underlying filesystem or ASM diskgroup.


                  standby alert
                  ============

                  PMON started with pid=2, OS id=3484
                  PSP0 started with pid=3, OS id=2000
                  MMAN started with pid=4, OS id=3888
                  DBW0 started with pid=5, OS id=556
                  LGWR started with pid=6, OS id=1952
                  CKPT started with pid=7, OS id=4632
                  SMON started with pid=8, OS id=1920
                  RECO started with pid=9, OS id=4528
                  CJQ0 started with pid=10, OS id=4136
                  MMON started with pid=11, OS id=4700
                  MMNL started with pid=12, OS id=5060
                  Fri Feb 08 11:14:04 2013
                  starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
                  Fri Feb 08 11:14:04 2013
                  starting up 1 shared server(s) ...
                  Fri Feb 08 11:15:39 2013
                  alter database mount
                  Fri Feb 08 11:15:43 2013
                  Setting recovery target incarnation to 1
                  ARCH: STARTING ARCH PROCESSES
                  ARC0 started with pid=16, OS id=2060
                  ARC1 started with pid=17, OS id=1936
                  ARC2 started with pid=18, OS id=1188
                  ARC3 started with pid=19, OS id=4132
                  ARC4 started with pid=20, OS id=4144
                  ARC5 started with pid=21, OS id=4764
                  ARC6 started with pid=22, OS id=1316
                  ARC7 started with pid=23, OS id=3456
                  ARC8 started with pid=24, OS id=3196
                  ARC9 started with pid=25, OS id=3176
                  Fri Feb 08 11:15:44 2013
                  ARC0: Archival started
                  Fri Feb 08 11:15:44 2013
                  ARC1: Archival started
                  ARC2: Archival started
                  ARC3: Archival started
                  ARC4: Archival started
                  ARC5: Archival started
                  ARC6: Archival started
                  ARC7: Archival started
                  ARC8: Archival started
                  ARC9: Archival started
                  ARCH: STARTING ARCH PROCESSES COMPLETE
                  Fri Feb 08 11:15:44 2013
                  ARC0: Becoming the 'no FAL' ARCH
                  ARC0: Becoming the 'no SRL' ARCH
                  Fri Feb 08 11:15:44 2013
                  ARC1: Becoming the heartbeat ARCH
                  Fri Feb 08 11:15:44 2013
                  ARC9: Thread not mounted
                  Fri Feb 08 11:15:44 2013
                  Successful mount of redo thread 1, with mount id 169643035
                  Fri Feb 08 11:15:44 2013
                  Physical Standby Database mounted.
                  Completed: alter database mount
                  Fri Feb 08 11:15:45 2013
                  ARC5: Thread not mounted
                  ARC1: Thread not mounted
                  Fri Feb 08 11:15:47 2013
                  ARC8: Thread not mounted
                  ARC0: Thread not mounted
                  Fri Feb 08 11:15:49 2013
                  ARC3: Thread not mounted
                  Fri Feb 08 11:15:49 2013
                  ARC6: Thread not mounted
                  Fri Feb 08 11:15:50 2013
                  ARC2: Thread not mounted
                  Fri Feb 08 11:15:51 2013
                  ARC7: Thread not mounted
                  Fri Feb 08 11:15:52 2013
                  ARC4: Thread not mounted
                  Fri Feb 08 11:17:43 2013
                  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
                  MRP0 started with pid=26, OS id=5004
                  Managed Standby Recovery starting Real Time Apply
                  parallel recovery started with 3 processes
                  Fri Feb 08 11:17:48 2013
                  Errors in file e:\oracle\product\10.2.0\admin\wplqdm\bdump\wplqdm_mrp0_5004.trc:
                  ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [J:\ORADATA\WPLQDM\REDO01_3.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [I:]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [E:\ORADATA\WPLQDM\REDO01_1.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the fil

                  Fri Feb 08 11:17:48 2013
                  Errors in file e:\oracle\product\10.2.0\admin\wplqdm\bdump\wplqdm_mrp0_5004.trc:
                  ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [J:\ORADATA\WPLQDM\REDO01_3.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [I:]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [E:\ORADATA\WPLQDM\REDO01_1.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the fil

                  Clearing online redo logfile 1 E:\ORADATA\WPLQDM\REDO01_1.RDO
                  Clearing online log 1 of thread 1 sequence number 5853
                  Fri Feb 08 11:17:48 2013
                  Errors in file e:\oracle\product\10.2.0\admin\wplqdm\bdump\wplqdm_mrp0_5004.trc:
                  ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1]
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [J:\ORADATA\WPLQDM\REDO01_3.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [I:]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the file specified.
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [E:\ORADATA\WPLQDM\REDO01_1.RDO]
                  ORA-27041: Message 27041 not found; No message file for product=RDBMS, facility=ORA
                  OSD-04002: unable to open file
                  O/S-Error: (OS 2) The system cannot find the fil

                  Fri Feb 08 11:17:48 2013
                  Errors in file e:\oracle\product\10.2.0\admin\wplqdm\bdump\wplqdm_mrp0_5004.trc:
                  ORA-19527: Message 19527 not found; No message file for product=RDBMS, facility=ORA
                  ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [1] [1] [E:\ORADATA\WPLQDM\REDO01_1.RDO]

                  Clearing online redo logfile 1 complete
                  Media Recovery Waiting for thread 1 sequence 5728
                  Fetching gap sequence in thread 1, gap sequence 5728-5782
                  Fri Feb 08 11:17:49 2013
                  Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
                  Fri Feb 08 11:18:19 2013
                  FAL[client]: Failed to request gap sequence
                  GAP - thread 1 sequence 5728-5782
                  DBID 66669641 branch 703873929
                  FAL[client]: All defined FAL servers have been attempted.
                  -------------------------------------------------------------
                  Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
                  parameter is defined to a value that is sufficiently large
                  enough to maintain adequate log switch information to resolve
                  archivelog gaps.
                  -------------------------------------------------------------
                  Fri Feb 08 11:29:06 2013
                  db_recovery_file_dest_size of 30000 MB is 5.99% used. This is a
                  user-specified limit on the amount of space that will be used by this
                  database for recovery-related files, and does not reflect the amount of
                  space available in the underlying filesystem or ASM diskgroup.
                  • 6. Re: problem in sync between primary and standby
                    mseberg
                    Hello again;

                    These errors just confirm what you posted in your first post. I was hoping to see a cause.

                    Manually copy the gapped archivelogs to the standby and register them.

                    Then monitor apply closely. At this point we have no way of knowing if the INCREMENTAL was successful.

                    Best Regards

                    mseberg
                    • 7. Re: problem in sync between primary and standby
                      989969
                      well in that case there are more than 100 log files have to be registered .... should go with

                      alter database register logfile ...... for 100 + log files

                      or

                      some other ways ???

                      recover standby using timestamp or scn.... please suggest

                      regards
                      gold
                      • 8. Re: problem in sync between primary and standby
                        mseberg
                        Sorry;

                        I was going with these numbers
                        the max(seq#) from primary is 5866
                        
                        in standby thre are arc.log files upto 5827
                        If you want to try the register a script is the way to go. I'm a Unix guy but I think you could redirect in windows using the DIR command to get a list of the log and then use a Text editor like Textpad to build the rest, it has great block and fill options. Otherwise you can try the
                        incremental backup again. Given the 100 logs I'd probably try a sample set of 10 or so in a batch file to see if its worth it.

                        Later

                        Also double check your Standby alert log for recent "Failed to request gap sequence" messages. If you see them you may not have created a new standby controlfile after the incremental which is key.


                        Best Regards

                        mseberg

                        Edited by: mseberg on Feb 8, 2013 6:11 AM
                        • 9. Re: problem in sync between primary and standby
                          989969
                          k ill take a try........

                          thanks for your valuable time ....


                          regards
                          gold.