This discussion is archived
9 Replies Latest reply: Feb 8, 2013 4:04 AM by 989969 RSS

problem in sync between primary and standby

989969 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    k ill take a try........

    thanks for your valuable time ....


    regards
    gold.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points