This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Sep 25, 2013 12:28 AM by yxes2013 RSS

Monitoring DG Physical standby

yxes2013 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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.... Oracle ACE
    Currently Being Moderated

    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.... Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

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

  • 10. Re: Monitoring DG Physical standby
    yxes2013 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    You don't bother to read do you ?

     

    See

    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

     

     

  • 13. Re: Monitoring DG Physical standby
    yxes2013 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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

Legend

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