1 2 Previous Next 20 Replies Latest reply: Sep 25, 2013 2:28 AM by yxes2013 RSS

    Monitoring DG Physical standby

    yxes2013

      Hi All

       

      11.2.0.1

      Aix 6.1

       

      How do I know if my standby db is currently updated to the latest redo or archive log?

       

      Is this procedure correct?

       

      At primary:

      ========

      SQL>  select process,status,sequence# from v$managed_standby;

       

      PROCESS   STATUS        SEQUENCE#

      --------- ------------ ----------

      ARCH      WRITING            2182

      ARCH      WRITING            2183

      ARCH      CLOSING            2196

      ARCH      WRITING            2184

      LNS       WRITING            2197

       

       

      At standby:

      ========

      SQL>  select process,status,sequence# from v$managed_standby;

       

      PROCESS   STATUS        SEQUENCE#

      --------- ------------ ----------

      ARCH      CLOSING            2195

      ARCH      CONNECTED             0

      ARCH      CONNECTED             0

      ARCH      CONNECTED             0

      MRP0      WAIT_FOR_LOG       2182

      RFS       IDLE               2183

      RFS       IDLE               2184

      RFS       IDLE               2182

      RFS       IDLE               2197

       

       

      Which row here do I need to check? Why is that MRP0 is waiting for log 2182 when it should wait for 2198?

       

      Thanks a lot,

      zxy

        • 1. Re: Monitoring DG Physical standby
          yxes2013

          At primary:

          ========

          SQL> select * from v$recovery_area_usage;

           

          FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

          -------------------- ------------------ ------------------------- ---------------

          CONTROL FILE                          0                         0               0

          REDO LOG                              0                         0               0

          ARCHIVED LOG                      35.08                     30.87             432

          BACKUP PIECE                       28.2                     18.74              30

          IMAGE COPY                            0                         0               0

          FLASHBACK LOG                         0                         0               0

          FOREIGN ARCHIVED LOG                  0                         0               0

           

          7 rows selected.


          At Standby:

          ===========

          SQL> select * from v$recovery_area_usage;

           

           

          FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

          -------------------- ------------------ ------------------------- ---------------

          CONTROL FILE                          0                         0               0

          REDO LOG                              0                         0               0

          ARCHIVED LOG                        .32                         0               4

          BACKUP PIECE                          0                         0               0

          IMAGE COPY                            0                         0               0

          FLASHBACK LOG                         0                         0               0

          FOREIGN ARCHIVED LOG                  0                         0               0

           

          7 rows selected.

           

          How do I reclaim space for arch and backup set at primary? I am afraid I will run out of space soon

           

          Thanks a lot,

          • 2. Re: Monitoring DG Physical standby
            Aman....

            In the simplest check, you can query the generated archived logs from the primary and compare them with the archive logs applied on the standby. You can use the view V$archived_log on both the db's for the same.

             

            Aman....

            • 3. Re: Monitoring DG Physical standby
              Aman....

              How do you reclaim space-by deleting the unnecessary files. So do what you normally do and if you think you would run out of space, get more space.

               

              Aman....

              • 4. Re: Monitoring DG Physical standby
                yxes2013

                Thanks Aman,

                 

                But I thought it is being managed AUTO by DataGuard to free up space once archivelogs are already applied at standby db and retention period is met? Just like in RMAN delete obsolete?

                • 5. Re: Monitoring DG Physical standby
                  yxes2013

                  Hi Aman,

                   

                  I selected the v$achived_log:

                   

                  At primary:

                  ========

                   

                  select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;

                   

                  SEQUENCE# FIRST_TIME           NEXT_TIME            ARC APPLIED

                  ---------- -------------------- -------------------- --- ---------

                        2189 24-SEP-2013 22:51:33 25-SEP-2013 00:24:36 YES NO

                        2190 25-SEP-2013 00:24:36 25-SEP-2013 02:06:41 YES NO

                        2190 25-SEP-2013 00:24:36 25-SEP-2013 02:06:41 YES NO

                        2191 25-SEP-2013 02:06:41 25-SEP-2013 04:00:23 YES NO

                        2191 25-SEP-2013 02:06:41 25-SEP-2013 04:00:23 YES NO

                        2192 25-SEP-2013 04:00:23 25-SEP-2013 06:55:45 YES NO

                        2192 25-SEP-2013 04:00:23 25-SEP-2013 06:55:45 YES NO

                        2193 25-SEP-2013 06:55:45 25-SEP-2013 08:55:40 YES NO

                        2193 25-SEP-2013 06:55:45 25-SEP-2013 08:55:40 YES NO

                        2194 25-SEP-2013 08:55:40 25-SEP-2013 10:06:10 YES NO

                        2194 25-SEP-2013 08:55:40 25-SEP-2013 10:06:10 YES NO

                        2195 25-SEP-2013 10:06:10 25-SEP-2013 11:10:04 YES NO

                        2195 25-SEP-2013 10:06:10 25-SEP-2013 11:10:04 YES NO

                        2196 25-SEP-2013 11:10:04 25-SEP-2013 11:10:18 YES NO

                        2197 25-SEP-2013 11:10:18 25-SEP-2013 12:07:52 YES NO

                        2197 25-SEP-2013 11:10:18 25-SEP-2013 12:07:52 YES NO

                        2198 25-SEP-2013 12:07:52 25-SEP-2013 13:08:32 YES NO

                   

                   

                  1568 rows selected.

                   

                  At standby:

                  ========

                  select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;

                   

                  SEQUENCE# FIRST_TIME           NEXT_TIME            ARC APPLIED

                  ---------- -------------------- -------------------- --- ---------

                        2179 24-SEP-2013 15:00:10 24-SEP-2013 16:10:06 YES YES

                        2180 24-SEP-2013 16:10:06 24-SEP-2013 17:23:16 YES YES

                        2181 24-SEP-2013 17:23:16 24-SEP-2013 19:02:40 YES YES

                        2182 24-SEP-2013 19:02:40 24-SEP-2013 21:39:53 YES YES

                        2183 24-SEP-2013 21:39:53 24-SEP-2013 22:00:56 YES YES

                        2184 24-SEP-2013 22:00:56 24-SEP-2013 22:49:26 YES YES

                        2185 24-SEP-2013 22:49:26 24-SEP-2013 22:50:20 YES YES

                        2195 25-SEP-2013 10:06:10 25-SEP-2013 11:10:04 YES NO

                        2197 25-SEP-2013 11:10:18 25-SEP-2013 12:07:52 YES NO

                   

                   

                  9 rows selected.

                   

                  What is that I do not have sequence 2186-2194 at standby? it this gaps normal?

                  I have just recovered this standby from incremental backup.

                   

                  Thanks...

                  • 6. Re: Monitoring DG Physical standby
                    saurabh

                    yes this is an archive log gap. You need to copy  the same from your production database and apply on the standby.

                     


                    • 7. Re: Monitoring DG Physical standby
                      Hemant K Chitale

                      No, DataGuard does not delete archivelogs.

                      Oracle may delete archivelogs if the FRA is running low on free space  (and if archivelogs are written using LOCATION=USE_DB_RECOVERY_FILE_DEST).

                      If you are running a DataGuard Standby , you should also configure the ARCHIVELOG DELETION POLICY at the Primary using the rman commandline.

                       

                      Hemant K Chitale


                      • 8. Re: Monitoring DG Physical standby
                        yxes2013

                        Hi Saur & Aman

                         

                        >>yes this is an archive log gap. You need to copy  the same from your production database and apply on the standby.


                        But I already command ALTER SYSTEM SWITCH LOGFILE;

                        Does it not auto send all the un-applied archive logs? Do I need to manually copy always?

                        • 9. Re: Monitoring DG Physical standby
                          saurabh

                          no. You need to manually copy the archive log of gap sequence.

                          • 10. Re: Monitoring DG Physical standby
                            yxes2013

                            Hi Aman,

                             

                            >>If you are running a DataGuard Standby , you should also configure the ARCHIVELOG DELETION POLICY at the Primary using the rman commandline.

                             

                            RMAN> show all;

                            using target database control file instead of recovery catalog

                            RMAN configuration parameters for database with db_unique_name PROD are:

                            CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;

                            CONFIGURE BACKUP OPTIMIZATION OFF; # default

                            CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

                            CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

                            CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

                            CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

                            CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

                            CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

                            CONFIGURE MAXSETSIZE TO UNLIMITED; # default

                            CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

                            CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

                            CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

                            CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

                            CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD.f'; # default



                            Does it mean I have 8 days retention or deletion policy?


                            Why is that my FRA archivelogs have 24 days retention?


                            prod:oracle[/backup/flash_recovery_area/PROD/archivelog] ls -lt

                            total 200

                            drwxr-x---    2 oracle   oinstall       4096 Sep 25 13:08 2013_09_25

                            drwxr-x---    2 oracle   oinstall       4096 Sep 24 22:51 2013_09_24

                            drwxr-x---    2 oracle   oinstall       4096 Sep 23 22:03 2013_09_23

                            drwxr-x---    2 oracle   oinstall       4096 Sep 22 23:02 2013_09_22

                            drwxr-x---    2 oracle   oinstall       4096 Sep 21 21:25 2013_09_21

                            drwxr-x---    2 oracle   oinstall       4096 Sep 20 22:05 2013_09_20

                            drwxr-x---    2 oracle   oinstall       4096 Sep 19 23:00 2013_09_19

                            drwxr-x---    2 oracle   oinstall       4096 Sep 18 22:03 2013_09_18

                            drwxr-x---    2 oracle   oinstall       4096 Sep 17 22:53 2013_09_17

                            drwxr-x---    2 oracle   oinstall       4096 Sep 16 23:00 2013_09_16

                            drwxr-x---    2 oracle   oinstall       4096 Sep 15 23:01 2013_09_15

                            drwxr-x---    2 oracle   oinstall       4096 Sep 15 02:45 2013_09_01

                            drwxr-x---    2 oracle   oinstall       4096 Sep 14 20:32 2013_09_14

                            drwxr-x---    2 oracle   oinstall       4096 Sep 13 23:29 2013_09_13

                            drwxr-x---    2 oracle   oinstall       4096 Sep 12 22:02 2013_09_12

                            drwxr-x---    2 oracle   oinstall       4096 Sep 11 23:43 2013_09_11

                            drwxr-x---    2 oracle   oinstall       4096 Sep 10 22:27 2013_09_10

                            drwxr-x---    2 oracle   oinstall       4096 Sep 09 23:10 2013_09_09

                            drwxr-x---    2 oracle   oinstall       4096 Sep 08 23:00 2013_09_08

                            drwxr-x---    2 oracle   oinstall       4096 Sep 07 23:30 2013_09_07

                            drwxr-x---    2 oracle   oinstall       4096 Sep 06 23:11 2013_09_06

                            drwxr-x---    2 oracle   oinstall       4096 Sep 05 22:02 2013_09_05

                            drwxr-x---    2 oracle   oinstall       4096 Sep 04 23:45 2013_09_04

                            drwxr-x---    2 oracle   oinstall       4096 Sep 03 22:09 2013_09_03

                            drwxr-x---    2 oracle   oinstall       4096 Sep 02 22:33 2013_09_02


                            Thanks.....

                            • 11. Re: Monitoring DG Physical standby
                              yxes2013

                              Hi Saur,

                               

                              >>no. You need to manually copy the archive log of gap sequence.


                              I checked the standby server and I saw that the arhivelogs missing/gap was already sent there? How do I manually apply it? Or why is the dataguard not able to apply it auto? 


                              standbyt:oracle[/backup/flash_recovery_area/PRODDR/archivelog/2013_09_24] ls -lt

                              total 3306728

                              -rw-r-----    1 oracle   oinstall   86327808 Sep 25 00:30 o1_mf_1_2189_943b244f_.arc

                              -rw-r-----    1 oracle   oinstall   93844480 Sep 24 23:37 o1_mf_1_2188_943cp8g1_.arc

                              -rw-r-----    1 oracle   oinstall   97719808 Sep 24 23:27 o1_mf_1_2186_9439nt1v_.arc

                              -rw-r-----    1 oracle   oinstall   87962112 Sep 24 23:25 o1_mf_1_2187_9439oojt_.arc

                              -rw-r-----    1 oracle   oinstall   87421952 Sep 24 23:24 o1_mf_1_2185_9439nn3w_.arc

                              -rw-r-----    1 oracle   oinstall   97719808 Sep 24 22:57 o1_mf_1_2184_9438fwy0_.arc

                              -rw-r-----    1 oracle   oinstall   96232448 Sep 24 22:29 o1_mf_1_2183_9435jhj0_.arc

                              -rw-r-----    1 oracle   oinstall   85931520 Sep 24 21:39 o1_mf_1_2182_942w9q7p_.arc

                              -rw-r-----    1 oracle   oinstall   85985792 Sep 24 19:02 o1_mf_1_2181_942phbrd_.arc

                              -rw-r-----    1 oracle   oinstall   86023168 Sep 24 17:22 o1_mf_1_2180_942l65dj_.arc

                              -rw-r-----    1 oracle   oinstall   86111744 Sep 24 16:09 o1_mf_1_2179_942g56yj_.arc

                              -rw-r-----    1 oracle   oinstall   87612416 Sep 24 15:00 o1_mf_1_2178_9429j94j_.arc

                              -rw-r-----    1 oracle   oinstall   86136832 Sep 24 13:41 o1_mf_1_2177_9424nkoo_.arc

                              -rw-r-----    1 oracle   oinstall   85966336 Sep 24 12:18 o1_mf_1_2176_941zvkgt_.arc

                              -rw-r-----    1 oracle   oinstall   85969920 Sep 24 10:56 o1_mf_1_2175_941tvj9x_.arc

                              -rw-r-----    1 oracle   oinstall   85942784 Sep 24 09:31 o1_mf_1_2174_941nt004_.arc

                              -rw-r-----    1 oracle   oinstall   85934080 Sep 24 07:48 o1_mf_1_2173_9418n71g_.arc

                              -rw-r-----    1 oracle   oinstall   97114624 Sep 24 04:20 o1_mf_1_2172_9411j969_.arc

                              -rw-r-----    1 oracle   oinstall   85926400 Sep 24 02:18 o1_mf_1_2171_9410wjro_.arc

                               

                               

                               

                              Thanks

                              • 12. Re: Monitoring DG Physical standby
                                Hemant K Chitale

                                You don't bother to read do you ?

                                 

                                See

                                CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

                                 

                                 

                                • 13. Re: Monitoring DG Physical standby
                                  yxes2013

                                  Hello Aman/Saur,

                                   

                                  Why is that when I check the gaps using:

                                  select instance_name from v$instance;

                                  SELECT high.thread#, "LowGap#", "HighGap#"

                                      FROM

                                           (

                                           SELECT thread#, MIN(sequence#)-1 "HighGap#"

                                           FROM

                                           (

                                               SELECT a.thread#, a.sequence#

                                               FROM

                                              (

                                                  SELECT *

                                                  FROM v$archived_log

                                              ) a,

                                              (

                                                  SELECT thread#, MAX(next_change#)gap1

                                                  FROM v$log_history

                                                  GROUP BY thread#

                                              ) b

                                              WHERE a.thread# = b.thread#

                                              AND a.next_change# > gap1

                                          )

                                          GROUP BY thread#

                                      ) high,

                                      (

                                          SELECT thread#, MIN(sequence#) "LowGap#"

                                          FROM

                                          (

                                              SELECT thread#, sequence#

                                              FROM v$log_history, v$datafile

                                              WHERE checkpoint_change# <= next_change#

                                              AND checkpoint_change# >= first_change#

                                          )

                                          GROUP BY thread#

                                      ) low

                                          WHERE low.thread# = high.thread#;




                                  I only get:


                                  INSTANCE_NAME

                                  ----------------

                                  PRODDR

                                   

                                   

                                   

                                   

                                     THREAD#    LowGap#   HighGap#

                                  ---------- ---------- ----------

                                           1       2191       2194

                                   

                                   

                                   

                                  Thanks..


                                  • 14. Re: Monitoring DG Physical standby
                                    saurabh

                                    cancel the recovery.

                                    and in sql prompt use recover standby database.

                                    and again start automatic recovery.

                                     

                                    The reason  for this would be  that the archive logs might have got corrupted while transporting from primary to standby.

                                    If this was the case then you need to  copy the archive log from production and then apply again.

                                    1 2 Previous Next