This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 2, 2012 11:59 PM by MahirM.Quluzade RSS

Standby error.

788793 Newbie
Currently Being Moderated
Hi All,

Configured Oracle 11gR2 Data Guard. No DG Broker.
Having issues with log shipping and applying; Errors as below;
Primary -
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           796

Standby -
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           790

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
           761
Standby -
SQL> SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY;

   THREAD#  SEQUENCE# PROCESS   CLIENT_P STATUS           BLOCK#     BLOCKS
---------- ---------- --------- -------- ------------ ---------- ----------
         0          0 ARCH      ARCH     CONNECTED             0          0
         0          0 ARCH      ARCH     CONNECTED             0          0
         0          0 ARCH      ARCH     CONNECTED             0          0
         0          0 ARCH      ARCH     CONNECTED             0          0
         1        766 RFS       UNKNOWN  RECEIVING         67585       2048
         1        767 RFS       UNKNOWN  RECEIVING          6145       2048
         1        768 RFS       UNKNOWN  RECEIVING             1       2048
         1        762 MRP0      N/A      WAIT_FOR_GAP          0          0

Primary Alert log -
Archived Log entry 551 added for thread 1 sequence 796 ID 0xb92fa192 dest 1:
...
...                                                                                 rc:
ORA-12152: TNS:unable to send break message
LNS: Failed to archive log 2 thread 1 sequence 797 (12152)
.......
ORA-12592: TNS:bad packet
DR Alert Log -

CORRUPTION DETECTED: In redo blocks starting at block 327420count 2048 for threa                                                                                        d 1 sequence 794
RFS[174]: Possible network disconnect with primary database

RFS[175]: Assigned to RFS process 10361
RFS[175]: Opened log for thread 1 sequence 763 dbid -1188064878 branch 784059669

RFS[176]: Assigned to RFS process 10356
RFS[176]: Opened log for thread 1 sequence 764 dbid -1188064878 branch 784059669

RFS[177]: Assigned to RFS process 10365
RFS[177]: Opened log for thread 1 sequence 762 dbid -1188064878 branch 784059669

Primary database is in MAXIMUM PERFORMANCE mode
RFS[178]: Assigned to RFS process 10372
RFS[178]: No standby redo logfiles available for thread 1
RFS[178]: Opened log for thread 1 sequence 797 dbid -1188064878 branch 784059669..
...
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 762-769
 FAL[client]: All defined FAL servers have been attempted.
  • 1. Re: Standby error.
    MahirM.Quluzade Guru
    Currently Being Moderated
    Hi,
    FAL[client]: Failed to request gap sequence
     GAP - thread 1 sequence 762-769
     FAL[client]: All defined FAL servers have been attempted.
    You have redo gap.

    Did you backup archivelogs with sequence# 762 - 769?

    If you have backup
    please try following commands on primary
     
     
     RMAN TARGET / 
     RMAN> list backup of archivelog from logseq 762 until logseq 769;
    
     if you have any backup then 
    
     RMAN> restore archivelog from logseq 762 until logseq 769;
    This command will restore missing archivelogs nad transport service automatically transport this archivelogs to standby.

    If you haven't backup of archivelogs you must use steps of following link :

    http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/


    Regards
    Mahir M. Quluzade
  • 2. Re: Standby error.
    CKPT Guru
    Currently Being Moderated
    CORRUPTION DETECTED: In redo blocks starting at block 327420count 2048 for threa
    Looks this sequence 762 has corrupted.

    From Primary:
    select thread#,sequence#,name from v$archived_log where sequence# > 761;

    Check whether 762 sequence is available or not, If available can you try to manually copy, register and then start recovery. If you have any backup copy of this archivelog then restore it and then perform recovery.

    Even after copying this archive manually, if you unable to proceed with recovery then probably you have to use incremental roll forward technique as mentioned in below article.

    http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/

    If really there is any corruption then consider to take a FULL backup of database also.
  • 3. Re: Standby error.
    585179 Expert
    Currently Being Moderated
    Hi,

    It looks like you have issue with archivelog gap. Check if your FAL setting is correct
    Have a further read "Data Guard Gap Detection and Resolution [ID 232649.1]"


    Hope it helps

    Cheers
  • 4. Re: Standby error.
    abdul: Newbie
    Currently Being Moderated
    hello,

    what i have to do is in this kind of stipulation is:
    [on standby]
    alter database recover managed standby database cancel;
    and thereafter follow this simple steps to recover your standby db
    http://arup.blogspot.in/2009/12/resolving-gaps-in-data-guard-apply.html
    btw

    abdul
  • 5. Re: Standby error.
    788793 Newbie
    Currently Being Moderated
    Hi All,

    As suggested did the incremental recovery; still getting the errors;

    RMAN> recover database noredo;
    Alert - Primary
    
    krsv_proc_kill: Killing 1 processes (Process by index)
    ARC2: Detected ARCH process failure
    ARC2: STARTING ARCH PROCESSES
    Thu Sep 20 17:10:31 2012
    ARC0 started with pid=21, OS id=3502 
    ARC0: Archival started
    ARC2: STARTING ARCH PROCESSES COMPLETE
    Reclaiming FAL entry from dead process [pid 786]
    ARC0: Becoming the heartbeat ARCH
    Thu Sep 20 17:20:20 2012
    FAL[server, ARC1]: FAL archive failed, see trace file.
    ARCH: FAL archive failed. Archiver continuing
    
    Standby -
    
    RFS[9]: Opened log for thread 1 sequence 771 dbid -1188064878 branch 784059669
    Thu Sep 20 17:17:11 2012
    CORRUPTION DETECTED: In redo blocks starting at block 45057count 2048 for thread 1 sequence 772
    RFS[8]: Possible network disconnect with primary database
    Thu Sep 20 17:17:20 2012
    CORRUPTION DETECTED: In redo blocks starting at block 94209count 2048 for thread 1 sequence 775
    RFS[7]: Possible network disconnect with primary database
    Thu Sep 20 17:17:21 2012
    RFS[10]: Assigned to RFS process 7689
    RFS[10]: Opened log for thread 1 sequence 776 dbid -1188064878 branch 784059669
    Thu Sep 20 17:17:53 2012
    Archived Log entry 2 added for thread 1 sequence 776 rlc 784059669 ID 0xb92fa192 dest 2:
    Thu Sep 20 17:17:54 2012
    Fetching gap sequence in thread 1, gap sequence 762-775
    RFS[10]: Opened log for thread 1 sequence 777 dbid -1188064878 branch 784059669
    
    Standby -
    SQL> select process, status, group#, thread#, sequence# from v$managed_standby;
    
    PROCESS   STATUS       GROUP#                            THREAD#  SEQUENCE#
    --------- ------------ ---------------------------------------- ---------- ----------
    ARCH       CONNECTED    N/A                               0         0
    ARCH       CLOSING      9                               1       811
    ARCH       CONNECTED    N/A                               0         0
    ARCH       CONNECTED    N/A                               0         0
    MRP0       WAIT_FOR_GAP N/A                               1       762
    RFS       RECEIVING    N/A                               1       778
    RFS       RECEIVING    N/A                               1       779
    RFS       RECEIVING    N/A                               1       772
    Still its waiting for 762 which was a 2 days back archive log. While I rolled forward today.
  • 6. Re: Standby error.
    CKPT Guru
    Currently Being Moderated
    What are the steps you have followed?

    I doubt, you haven't restored new standby control file.
  • 7. Re: Standby error.
    788793 Newbie
    Currently Being Moderated
    CKPT wrote:
    What are the steps you have followed?

    I doubt, you haven't restored new standby control file.
    I followed the link ; http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/

    Only when I tried using RMAN> recover database; got error; Missing 762-804
    RMAN-03002: failure of recover command at 09/20/2012 16:36:50
    RMAN-06053: unable to perform media recovery because of missing log
    RMAN-06025: no backup of archived log for thread 1 with sequence 804 and starting SCN of 15528331207 found to restore
    ----------------------------------------
    ---------------------------------------
    ---------------------------------------
    RMAN-06025: no backup of archived log for thread 1 with sequence 763 and starting SCN of 15510266004 found to restore
    RMAN-06025: no backup of archived log for thread 1 with sequence 762 and starting SCN of 15508605875 found to restore
    Then I used ;
    RMAN> recover database noredo; as per the above link...
  • 8. Re: Standby error.
    CKPT Guru
    Currently Being Moderated
    Thats fine upto recovery point, My question was have you created standby controlfile on primary and have you restored in standby database?
    Then perform recovery using RMAN> recover database noredo;

    and ensure you have performed recovery using the same backup, which you took backup on primary.
    also check do you have data files with "OFFLINE" status.
  • 9. Re: Standby error.
    788793 Newbie
    Currently Being Moderated
    CKPT wrote:
    Thats fine upto recovery point, My question was have you created standby controlfile on primary and have you restored in standby database?
    Then perform recovery using RMAN> recover database noredo;
    Yes have followed the steps in the link.
    Except
    ---Remove old control files as per the value of “control_files” in PFILE/SPFILE.
    >
    and ensure you have performed recovery using the same backup, which you took backup on primary.
    also check do you have data files with "OFFLINE" status.
    All data files are available.
    SQL> select distinct status from dba_data_files;
    
    STATUS
    ---------
    AVAILABLE
    
    SQL> select distinct online_status from dba_data_files;
    
    ONLINE_
    -------
    ONLINE
    SYSTEM
    Edited by: user13355115 on Sep 20, 2012 11:10 PM
  • 10. Re: Standby error.
    CKPT Guru
    Currently Being Moderated
    Yes have followed the steps in the link.
    Except
    ---Remove old control files as per the value of “control_files” in PFILE/SPFILE.
    Still i doubt here, Before restoring standby control file either manually or by RMAN. Have you moved your current control files to different location or deleted them?
    Normally, if you took backup from the CURRENT_SCN of standby database to up to present in Primary database, then these backups will be generated from Database and not from archive log files.

    When you have all the datafiles are online, then you should able to recover using backups. Please crosscheck above point and next time post complete log file what you have done in primary and what you did on standby. Then certainly it can give more clarity and it will be easy to estimate.

    Thank you
  • 11. Re: Standby error.
    788793 Newbie
    Currently Being Moderated
    CKPT wrote:
    Please crosscheck above point and next time post complete log file what you have done in primary and what you did on standby. Then certainly it can give more clarity and it will be easy to estimate.
    Here are the steps ;
    Primary-
    
     CURRENT_SCN
    ------------
     15537481982
    
    Standby -
     CURRENT_SCN
    ------------
     15508606035
    -- Backup in primary. The incremental backup size was 3G.
    rman target /
    run { 
    allocate channel c1 type disk format '/u09/backup/incr_%U'; 
    backup incremental from scn 15508606035 database;
    }
    
    using target database control file instead of recovery catalog
    allocated channel: c1
    channel c1: SID=726 device type=DISK
    
    Starting backup at ....
    
    backup will be obsolete on date 27-SEP-12
    archived logs will not be kept or backed up
    channel c1: starting full datafile backup set
    channel c1: specifying datafile(s) in backup set
    -------
    -------
    including current control file in backup set
    ----
    piece handle=/u09/backup/incr_8knlm3vd_1_1 tag=TAG20120920T131429 comment=NONE
    channel c1: backup set complete, elapsed time: 00:00:01
    Finished backup .....
    released channel: c1
    rman> backup current controlfile for standby format '/u09/backup/bcg_standby_01.ctl';
    
    -- Copy the backups to the standby /u03/backup/ 
    
    Standby-
    alter database recover managed standby database cancel;
    
    create pfle ='/u03/pfile_standby.txt' from spfile;
    
    
    shu immediate
    --edit the pfile created above and delete the control files entries
    
    startup nomount pfile='/u03/pfile_standby.txt'
    
    RMAN> restore standby controlfile from '/u03/backup/bcg_standby_01.ctl';
    ----
    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    output file name=/u04/fra/omfXXXXX.ctl --- don't remember the output file exactly
    
    Finished restore .....
    sho parameter control_file ----> shows the bcg_standby_01.ctl
    
    RMAN> sql 'alter database mount standby database';
    
    RMAN> catalog start with '/u03/backup';
    
    RMAN> recover database noredo;
    ....
    using channel ORA_DISK_1
    Finished recover at ....
    -----> No other output here and was in 1 second.
    
    sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
    
    SQL> select current_scn from v$database;
    
     CURRENT_SCN
    ------------
     15538253425
    
    Primary -
    
    alter system set log_archive_dest_state_2='enable';
    alter system switch logfile;
    Issues post roll forward -

    1)The errors I am getting is same as posted above.

    2) Log shipping is moving slowly but my apply is almost stuck

    3) Also I see sometimes my archive files of smaller sizes around 30-70M compared to my Normal 900M.
    Primary
    -rw-r----- 1 oracle oinstall 901M Sep 19 02:15 arch_1_762_784059669.arc
    -rw-r----- 1 oracle oinstall 900M Sep 19 02:15 arch_1_763_784059669.arc
    -rw-r----- 1 oracle oinstall 900M Sep 19 02:15 arch_1_764_784059669.arc
    -rw-r----- 1 oracle oinstall 73M Sep 19 01:24 arch_1_784_784059669.arc
    -rw-r----- 1 oracle oinstall 46M Sep 19 00:25 arch_1_783_784059669.arc
    -rw-r----- 1 oracle oinstall 31M Sep 18 21:57 arch_1_777_784059669.arc
  • 12. Re: Standby error.
    316993 Pro
    Currently Being Moderated
    If i understand yours issue that could be an issue of a missing sequence # , you might have not this missing archive log available at Primary to copy at standby , one can cope it by using incremental level backup to roll forward the standby database for that i would say take at production site incremental level backup from the earliest checkpoint_change# (SCN) at standby i.e

    at standby
    --------------
    select min(checkpoint_change#)
     from v$datafile_header;
    at production take incremental level backup from the sequence which is the result of above query and then roll forward yours standby from this production backup.

    HTH

    Khurram
  • 13. Re: Standby error.
    788793 Newbie
    Currently Being Moderated
    orawarebyte wrote:
    at production take incremental level backup from the sequence which is the result of above query and then roll forward yours standby from this production backup.
    Hi,

    I dont have the old archive logs physically at Primary like 762..etc..(for which the mrp is waiting). Deleted by deletion policy.

    Also when I take the incremental backups should I backup the archivelogs too to reduce the gap.

    Thanks
  • 14. Re: Standby error.
    316993 Pro
    Currently Being Moderated
    No , just take an incremental level backup from the SCN of the following query result at Production.
    select min(checkpoint_change#)
     from v$datafile_header;
    copy that backup piece to standby database and rollforward it with this backup.I had been in the same archivelogs backup issue as you are and got the only that resolution which i am telling you right now , for a little background about this issue read this article
    http://oraware.blogspot.com.au/2009/04/media-recovery-start-point.html
    Khurram
1 2 Previous Next

Legend

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