This discussion is archived
2 Replies Latest reply: May 9, 2012 7:44 AM by 656218 RSS

recovery process applies old archivelogs on standby database

656218 Newbie
Currently Being Moderated
Right now my standby database is in sync with my primary database and is waiting for the archived log sequeuence# 8378 to arrive.

But when I stop the recovery process (alter database recover managed standby database cancel;) and re-start it (alter database recover managed standby database disconnect), it starts all over again and starts applying archive logs starting from the sequence# 5739 (looks like its scanning thru the logs). To catchup with primary it takes 2+ hours as it need to skim thru all the logs starting from 5739 to 8377.

Please let me know if you need any further information to fix this.

Thank you
Sunny boy

Details:

Database version: 11.2.0.3
OS : RHEL 5

On Standby Database

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"
FROM V$LOG_HISTORY
GROUP BY THREAD#; 2 3

THREAD# LAST_APPLIED_LOG
---------- ----------------
1 8377

Alert log

alter database recover managed standby database disconnect
Attempt to start background Managed Standby Recovery process (MNODWDR)
Tue May 08 16:13:09 2012
MRP0 started with pid=28, OS id=26150
MRP0: Background Managed Standby Recovery process started (MNODWDR)
started logmerger process
Tue May 08 16:13:15 2012
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 8 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: alter database recover managed standby database disconnect
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/arch/mnodw_1_5739_765032423.arc
Tue May 08 16:13:48 2012
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5740.1466.781015749
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5741.1468.781017203
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5742.1474.781017203
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5743.1473.781017203
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5744.1477.781017203
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5745.1478.781017203
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5746.1472.781017203
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5747.1475.781017203
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5748.1469.781017203
Media Recovery Log +MNODW_FRA_GRP/mnodwdr/archivelog/2012_04_19/thread_1_seq_5749.1470.781017203
Tue May 08 16:13:57 2012

Edited by: Sunny boy on May 8, 2012 5:29 PM
  • 1. Re: recovery process applies old archivelogs on standby database
    mseberg Guru
    Currently Being Moderated
    Hello;

    V$LOG_HISTORY is the information from the control file. I would use a different query to check :

    From the Primary :
    SET PAGESIZE 140
    COL DB_NAME FORMAT A10
    COL HOSTNAME FORMAT A14
    COL LOG_ARCHIVED FORMAT 999999
    COL LOG_APPLIED FORMAT 999999
    COL LOG_GAP FORMAT 9999
    COL APPLIED_TIME FORMAT A14
    SELECT
       DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP
    FROM
    ( SELECT
       NAME DB_NAME
    FROM
       V$DATABASE
    ),
    (
    SELECT
       UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) HOSTNAME
    FROM
       V$INSTANCE
    ),
    (
    SELECT
       MAX(SEQUENCE#) LOG_ARCHIVED
    FROM
       V$ARCHIVED_LOG
    WHERE
       DEST_ID=1
    AND
       ARCHIVED='YES'
    ),
    (
    SELECT
       MAX(SEQUENCE#) LOG_APPLIED
    FROM
       V$ARCHIVED_LOG
    WHERE
       DEST_ID=2
    AND
       APPLIED='YES'
    ),
    (
    SELECT
       TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
    FROM
       V$ARCHIVED_LOG
    WHERE
       DEST_ID=2
    AND
       APPLIED='YES'
    );
    Change DEST_ID as needed for your system. I would also bump the parameter LOG_ARCHIVE_MAX_PROCESSES assuming its set to default to a higher value up to 30.

    Maybe instead of stopping the recovery process you should DEFER on the Primary
    alter system set log_archive_dest_state_2=defer;
    Change the _n from 2 to what your system requires. I use this and have watch DG catch up 200 archives in about 15 minutes.

    You have Standby Redo setup and are using the same size as your redo right?

    Have never seen the Standby try to apply twice.

    ORA-600 [3020] "Stuck Recovery" [ID 30866.1] ( But I do not see your issue )

    Metalink Note 241438.1 Script to Collect Data Guard Physical Standby Diagnostic Information
    Metalink Note 241374.1 Script to Collect Data Guard Primary Site Diagnostic Information


    Best Regards

    mseberg

    Edited by: mseberg on May 8, 2012 5:16 PM
  • 2. Re: recovery process applies old archivelogs on standby database
    656218 Newbie
    Currently Being Moderated
    mseberg,

    Thank you for your reply!!

    I was finally able to resolve the issue.

    Here is how I identified the issue:

    I tried to open the database in read only mode (alter database open read only;) and noticed this Rereading datafile 362 header failed with ORA-01204 in the alert log.


    Resolution:

    1. Took a rman backup of datafile 362 on primary (BACKUP AS BACKUPSET DATAFILE ' <path>/<filename>')
    2. Copied backup file over to Standby
    3. Stop managed recovery process (alter database recover managed standby database cancel;)
    4. Restored datafile (restore datafile 362;)
    5. Started managed recover process (alter database recover managed standby database disconnect;)

    I stopped and started recovery process several time to see if the issue got resolved and it works perfect.

    Once again thank you for your help and time.

    Regards,
    Sunny boy

    Edited by: Sunny boy on May 9, 2012 10:43 AM

    Edited by: Sunny boy on May 9, 2012 10:44 AM

Legend

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