1 2 Previous Next 17 Replies Latest reply: Oct 3, 2012 1:59 AM by Mahir M. Quluzade RSS

    Standby error.

    788793
      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.
          Mahir M. Quluzade
          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
            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
              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:
                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
                  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
                    What are the steps you have followed?

                    I doubt, you haven't restored new standby control file.
                    • 7. Re: Standby error.
                      788793
                      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
                        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
                          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
                            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
                              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.
                                orawarebyte
                                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
                                  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.
                                    orawarebyte
                                    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