1 2 Previous Next 24 Replies Latest reply: Jul 11, 2012 6:33 AM by 767685 RSS

    Restore missing archivelogs on physical standby db

    767685
      Hello,

      db=11.2.0.3 on rhl

      db1rq - primary
      db2rq - Physcical standby

      I am informed that there are some missing archivelogs on the physical standby db. I have to find out which one are missing and restore from backup. I would appreciate the help.

      I dont know where to look and start. At the moment I have only found out this. I dont know its the right direction or not.
      # db2rq .. phy.standbydb
      1* SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO'
      
       SEQUENCE# APP
      ---------- ---
          188655 NO
          188656 NO
      
      # db1rq . Primary
      
      1* SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO'
      188657 NO
      188658 NO
      - - - - - -  
      196254 NO
      196255 NO
      7599 rows selected.       ------------------ Is this NORMAL?
      
      # db2rq. phystandby db
      $ rman target /
      
      CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 28 DAYS;
      CONFIGURE BACKUP OPTIMIZATION ON;
      CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
      CONFIGURE CONTROLFILE AUTOBACKUP ON;
      CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/remote/backup/cha/%F';
      CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
      CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
      CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
      CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/remote/backup/cha/%U' MAXPIECESIZE 2000 M;
      CONFIGURE MAXSETSIZE TO UNLIMITED; # default
      CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
      CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
      CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
      Does this NO in APPLIED column of standby db represents missing archivelogs or there is some other way to find out? Right now I have no such idea what happened, this is new DB and some archival stuck on standby db, something clearing Flash recovery area space..deleting pieces to free space doesnt on Primary doesnt applied to standby

      Thanks a lot

      Regards

      Edited by: John-M on Jun 11, 2012 9:05 AM
        • 1. Re: Restore missing archivelogs on physical standby db
          785101
          not necessarily:

          http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1016.htm#REFRN30011

          One view to use on the physical standby is:

          v$archive_gap

          http://docs.oracle.com/cd/E14072_01/server.112/e10820/dynviews_1014.htm

          you need to periodically query this view after each gap has been dealt with (you might have several that are spread out). If you encounter a gap in which you cannot get the archivelogs from backup then you might need to using backup incrementals to get the physical standby back in sync:

          this link explains the process quite well:

          http://taliphakanozturken.wordpress.com/2012/04/11/how-to-resolve-primarystandby-log-gap-in-case-of-deleting-archivelogs-from-primary/
          • 2. Re: Restore missing archivelogs on physical standby db
            L-MachineGun
            Sometimes V$ARCHIVE_GAP lies.

            Easiest way is:

            1) Execute this query on both the primary and standby:
            SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"
              FROM V$LOG_HISTORY
             GROUP BY THREAD#;
            2) Recover to the standby all archive log(s) between the results from above queries.

            3) Execute on standby:
            SQL> STARTUP MOUNT;
            SQL> RECOVER AUTOMATIC STANDBY DATABASE
            4) When it becomes consistent (applies all missing logs) then start the managed recovery.

            :p
            • 3. Re: Restore missing archivelogs on physical standby db
              785101
              v$log_history does not indicate missing log files - only what's been applied. You can have some log files on the standby but not others.
              • 4. Re: Restore missing archivelogs on physical standby db
                mseberg
                Hello John;


                This query is meaningless on the Primary :
                SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO'
                It will only return how many archive logs have been written.

                Something like this is better :
                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;
                  
                Change DEST_ID and SYSDATE - as needed.





                This is exactly how I setup PRIMARY RMAN if I do backups there
                CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
                On the Standby I set it to None.

                If you want to "Monitor Data Guard Transport" ( Will also show if you really have a gap )

                Try this query :

                http://www.visi.com/~mseberg/monitor_data_guard_transport.html

                Best Regards

                mseberg
                • 5. Re: Restore missing archivelogs on physical standby db
                  767685
                  Hello,

                  Thank you.
                  AS SYSDBA>  select * from v$archive_gap; ## on standby database
                  
                     THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
                  ---------- ------------- --------------
                           1           304            304
                  
                  Just tried to queried on Primary ...no rows selected (i think as expected)
                  Dont know how I should do the procedure from here on to cover this gap?

                  @mseberg
                  Oh nice to your input ..In fact its always honor to have your inputs and help

                  Regards

                  Edited by: John-M on Jun 11, 2012 10:48 AM
                  • 6. Re: Restore missing archivelogs on physical standby db
                    767685
                    Hello mseberg,

                    Thanks a lot.

                    Could you please check and suggest what to do next.....


                    db2rq....physical standby
                    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

                    db1rq..primary
                    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

                    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default .... on both
                    SELECT  
                      NAME AS STANDBY,
                      SEQUENCE#, 
                      APPLIED, 
                      COMPLETION_TIME 
                    FROM 
                      V$ARCHIVED_LOG 
                    WHERE  
                      DEST_ID = 1 
                    
                    571    NO                       11/06/2012 19:05:04   ### All with 'NO'
                     - - -  -
                    659 rows selected on PRIMARY
                    Result of v$archive_gap is in the above post.


                    # Result of your suggested query ..ON PRIMARY
                    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
                    ---------- -------------- ------------ ----------- -------------- -------
                    ECB          DB1RQ              572         303 11-JUN/09:58       269
                    
                    
                    # On Standby:
                    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
                    ---------- -------------- ------------ ----------- -------------- -------
                    ECB         DB2RQ              572         140 05-JUN/16:17       432
                    Edited by: John-M on Jun 11, 2012 11:05 AM

                    Edited by: John-M on Jun 11, 2012 11:06 AM

                    Edited by: John-M on Jun 11, 2012 11:12 AM

                    Edited by: John-M on Jun 11, 2012 11:16 AM
                    • 7. Re: Restore missing archivelogs on physical standby db
                      mseberg
                      John;

                      Sorry for the delay I was in a meeting.

                      You appear to have a large gap.

                      Depending upon where you do the RMAN backup these might be backwards.
                      db2rq....physical standby
                      CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
                      
                      db1rq..primary
                      CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
                      If the Archive made it to the Standby and is still there it would be great news. Then we can write a script to register them and resolve the Gap. If the Archive is missing you might consider using RMAN to rollback the Standby SCN number forward as shown here :

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


                      You probably remember CKPT from the Data Guard forum, he's a great source of information.

                      Best Regards

                      mseberg
                      • 8. Re: Restore missing archivelogs on physical standby db
                        767685
                        No problem at all. I know you never let somebody alone in this kind of situation :) and yes, can't forget CKPT by chance I was looking at his website this weekend - very helpful person.

                        But at the moment I am very confused, dont know how to start and where to start.
                        If the Archive made it to the Standby and is still there it would be great news. Then we can write a script to register them and resolve the Gap.
                        Do not understand the point :(

                        ASM disk groups is being used btw to keep log files. My colleague suggested that I might have to restore missing archive logs from the backup

                        Best Regards

                        Edited by: John-M on Jun 11, 2012 12:22 PM

                        Edited by: John-M on Jun 11, 2012 12:25 PM
                        • 9. Re: Restore missing archivelogs on physical standby db
                          mseberg
                          If I run this query on mine
                          SELECT  
                            NAME AS STANDBY,
                            SEQUENCE#, 
                            APPLIED, 
                            COMPLETION_TIME 
                          FROM 
                            V$ARCHIVED_LOG 
                          WHERE  
                            DEST_ID = 2 
                          AND 
                            NEXT_TIME > SYSDATE -1;
                          I get this

                          Checking last sequence in v$archived_log

                          STANDBY               SEQUENCE# APPLIED    COMPLETIO                                                
                          -------------------- ---------- ---------- ---------                                                
                          STANDBY                    7604 YES        03-JUN-12                                                
                          STANDBY                    7605 YES        03-JUN-12                                                
                          STANDBY                    7606 YES        03-JUN-12                                                
                          STANDBY                    7607 NO         03-JUN-12     
                          Based on the other query it looks like your apply stopped on 05-JUN/16:17 at log 140.

                          So you need all the logs from 141 upwards. Can you confirm if they exist on either database or if some of them exist?

                          Please post what numbers still exists.

                          And in waht location should they exist on the Standby?

                          Also can you post your OS?

                          With this information we can determine the next step.

                          Best Regards

                          mseberg

                          Just saw your last comment

                          Will post an example from mine here in a minute.


                          OK

                          My system is single Standby ( no RAC, no ASM ) so my archive collects here

                          /u01/app/oracle/flash_recovery_area/STANDBY/archivelog/2012_06_11


                          Oracle create the date folder as needed.


                          On you Standby where is the Archive? and what numbers higher than 140 exist?


                          File example

                          o1_mf_1_661_7xcf77y1_.arc


                          So on mine this would be 661 ( sorry you probably know that )

                          Looks like you are using ASM, so location will be different from mine.


                          Edited by: mseberg on Jun 11, 2012 2:34 PM

                          Edited by: mseberg on Jun 11, 2012 2:39 PM
                          • 10. Re: Restore missing archivelogs on physical standby db
                            767685
                            OS=RHLx64
                            ASM 11.2.0.3 DB
                            SELECT  
                              NAME AS STANDBY,
                              SEQUENCE#, 
                              APPLIED, 
                              COMPLETION_TIME 
                            FROM 
                              V$ARCHIVED_LOG 
                            WHERE  
                              DEST_ID = 2 
                            AND 
                              NEXT_TIME > SYSDATE -1;
                            
                            RESULT: only 1 row on standby
                            +REDO01/ecbacend_db2/archivelog/2012_06_11/thread_1_seq_305.814.785671981
                                   305 NO        11/06/2012 10:13:01
                            
                            query result on PRIMARY:
                            57 rows selected.
                            last one is:
                            573 NO                                   11/06/2012 21:05:03
                            
                            # SAME RESULT ON BOTH primary and standby
                            select thread#,max(sequence#) from v$archived_log group by thread#;
                            
                               THREAD# MAX(SEQUENCE#)
                            ---------- --------------
                                     1            573
                            
                            # on standby ...db2rq
                            select * from v$archive_gap;
                            
                            
                               THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
                            ---------- ------------- --------------
                                     1           304            304
                            
                            select 5040579-2757556 "prim minus standby scn" from dual;  PRIMRAY DB SCN 5040579
                            
                            prim minus standby scn
                            ----------------------
                                           2283023
                            Edited: log_archive_dest=db_recovery_file_location ... how can I query this location and find the number above 140?

                            Edited by: John-M on Jun 11, 2012 12:46 PM

                            Edited by: John-M on Jun 11, 2012 12:48 PM

                            Edited by: John-M on Jun 11, 2012 12:51 PM

                            Edited by: John-M on Jun 11, 2012 12:55 PM

                            Edited by: John-M on Jun 11, 2012 1:20 PM
                            • 11. Re: Restore missing archivelogs on physical standby db
                              mseberg
                              John;

                              Sorry for the delay, the forum appeared to crash and I was locked out until now.

                              Given the large gap I think you have to ask yourself would it be easier to rebuild or roll forward?

                              This note is worth a look since you appear to have an RMAN issue :

                              Configure RMAN to purge archivelogs after applied on standby [ID 728053.1]

                              I believe it explains the correct configure better than I can.

                              RMAN duplicate ( to recreate the standby ) might be another option.

                              My concern is by the time all the Archive is tracking down, put on the standby and registered it might be faster to just rebuild.

                              Does this make sense?

                              The other option is to use the link to CKPT's site and try to roll the Standby forward. I have not performed this and I should NOT try to help you given this.

                              I don't use ASM like you do or I would be more than happy to help with an RMAN duplicate.

                              Best Regards

                              mseberg
                              • 12. Re: Restore missing archivelogs on physical standby db
                                CKPT
                                John-M,

                                You did mistake, This question should have posted in Oracle Discussion Forums » High Availability » Data Guard , i missed this question.

                                I think Mseberg given his great views already. On top of that i need some more information. Sorry for asking again. :)

                                post from Primary:-
                                ============
                                SQL> select max(sequence#) from v$archived_log;
                                SQL> select bytes/1024/1024 from v$log;


                                Post from standby:-
                                SQL> select max(sequence#) from v$archived_log;
                                SQL> select max(sequence#) from v$archived_log where applied='YES'; --> take as value "A".

                                Now check how many archives are missing on standby.
                                SQL> select sequence# from v$archived_log where sequence# > "value A"; (A value from above output)

                                if you have missing only one or less archives, then check whether those exist on Primary? if not check whether they exist in Backups?
                                If no from above both the cases You have only option, to perform Incremental ROLL Forward.

                                Mseberg already pointed link to perform incremental rollforward, Please follow it. http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/

                                Calculate how many archives you are missing * size of Each online redo size = <need to transport over network>
                                If its less then go with filling GAPs , If its hectic then go for incremental roll forward.
                                • 13. Re: Restore missing archivelogs on physical standby db
                                  767685
                                  Hello

                                  Thank you :) Yes, you are right, I should have posted in Data Guard...but you got it now :)

                                  @mseberg
                                  yes forum got crashed yesterday, and me too :P ..back again and thanks

                                  Here are results of queries you posted:
                                  # Primary....db1rq
                                  AS SYSDBA> select max(sequence#) from v$archived_log;
                                  
                                  MAX(SEQUENCE#)
                                  --------------
                                          588
                                  AS SYSDBA> select bytes/1024/1024 from v$log;
                                  
                                  BYTES/1024/1024
                                  ---------------
                                          1024
                                          1024
                                          1024
                                          1024
                                          1024
                                          1024
                                          1024
                                  7 rows selected.
                                  
                                  # Phy. Standby ...db2rq
                                  AS SYSDBA> select max(sequence#) from v$archived_log;
                                  
                                  MAX(SEQUENCE#)
                                  --------------
                                          588
                                  select max(sequence#) from v$archived_log where applied='YES';
                                  
                                  MAX(SEQUENCE#)
                                  --------------
                                          303
                                  
                                  AS SYSDBA> select sequence# from v$archived_log where sequence# > 303;
                                  
                                   SEQUENCE#
                                  ----------
                                         305
                                         568
                                         569
                                         570
                                         571
                                         572
                                         573
                                         574
                                         575
                                         576
                                         577
                                         578
                                         579
                                         580
                                         581
                                         582
                                         583
                                         584
                                         585
                                         586
                                         587
                                         588
                                  22 rows selected.
                                  Meanwhile, I try to find where they exist...If you are there could you please guide ....how to check if they exist on Primary or backup? How to query this use_db_recovery_file_dest? ASM is used in this setup.
                                  ###db2rq....standby....AS SYSDBA> show parameter log_archive_dest_1
                                  
                                  NAME                         TYPE      VALUE
                                  ------------------------------------ ----------- ------------------------------
                                  log_archive_dest_1               string      LOCATION=use_db_recovery_file_dest, valid_for=(ALL_ROLES,ALL_LOGFILES)
                                  
                                  log_archive_format
                                  
                                  NAME                         TYPE      VALUE
                                  ------------------------------------ ----------- ------------------------------
                                  log_archive_format               string      %t_%s_%r.dbf
                                  ### If I understand correctly, according to your suggested queries,,,missing archive log on standby are these 22 files as shown above (ONLY 22 FILES are missing? getting confused). If yes, then I need to check them whether they exist on Primary? else on BACKUP? how please....I think I need to RESTORE THEM FROM BACKUP...they must be there in the backup...

                                  Regards

                                  EDITED: I just tried this but I think it didnt worked?
                                  [oracle@db2rq ~]$ rman target /
                                  
                                  RMAN> restore archivelog logseq 305;
                                  - - 
                                  Archive log for thread 1 with sequence 305 is already available on disk as file + REDO01/ecbacend_db2/archivelog/2012_06_11/thread_1_seq_305.814.785671981
                                  Rewriting is not, all files are read-only offline or already restored
                                  12/06/2012 10:23:43 Ends restore order
                                  Edited by: John-M on Jun 12, 2012 12:53 AM

                                  Edited by: John-M on Jun 12, 2012 12:56 AM

                                  Edited by: John-M on Jun 12, 2012 1:04 AM

                                  Edited by: John-M on Jun 12, 2012 1:30 AM

                                  Edited by: John-M on Jun 12, 2012 1:34 AM
                                  • 14. Re: Restore missing archivelogs on physical standby db
                                    CKPT
                                    There you have gap of *260* archives. From 306 to 567.

                                    So in average 260 * 1gb(size of redo) = so 260gb of redo need to be restored & recovered.

                                    I suggest you to go with incremental roll forward.
                                    In archives you will have both committed & uncommitted, uncommitted Which you really don't need.
                                    If you take backup using scn, it's only actual data, then why to waste time to restore and recover ?

                                    Hope you got my point what am saying. :)
                                    1 2 Previous Next