This discussion is archived
9 Replies Latest reply: Nov 15, 2012 9:31 AM by Meenakshy singh RSS

Log out of sync

Meenakshy singh Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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