Forum Stats

  • 3,780,538 Users
  • 2,254,408 Discussions
  • 7,879,374 Comments

Discussions

Consolidated report for Data Guard Status in OEM13c

Nishant Baurai
Nishant Baurai Member Posts: 215 Bronze Badge
edited Oct 18, 2017 10:24AM in Enterprise Manager

Hello Experts,

Is there any way to get a consolidated Data Guard Status report from OEM13c for all database.

I searched for MGMT views but could not find anything significant.

Thanks,

Nishant

Nishant BauraiUser_R8LB0Raul.Alvarenga

Best Answer

  • Ian Baugaard
    Ian Baugaard Member Posts: 530 Silver Badge
    edited Oct 18, 2017 5:28AM Accepted Answer

    Hi Nishant,

    I use this to identify targets that have an apply lag, you can modify it as you see fit

    WITH qb_get_dgmetrics AS (
         select
              target_name
              , MAX( case when column_label = 'Apply Lag (seconds)' then to_number(value) end ) as apply_lag
              , MAX( case when column_label = 'Transport Lag (seconds)' then to_number(value) end ) as transport_lag
              , collection_timestamp
         from mgmt$metric_current
         where metric_name like '%dataguard%'
         and metric_label = 'Data Guard Performance'
         and column_label in ('Apply Lag (seconds)', 'Transport Lag (seconds)' )
         group by target_name, collection_timestamp
    )
    select
         target_name
         , apply_lag
         , transport_lag
         , collection_timestamp
    from qb_get_dgmetrics
    where apply_lag > 300 ;

    Hope this helps

    Ian

    Nishant BauraiUser_R8LB0Raul.Alvarenga

Answers

  • 3334148
    3334148 Member Posts: 8
    edited Oct 16, 2017 2:29AM

    Hi Nishant,

    Yes.. You can get these reports through BI Publisher which is recommended by oracle from OEM 13C.

    Thanks

  • Nishant Baurai
    Nishant Baurai Member Posts: 215 Bronze Badge
    edited Oct 17, 2017 1:55PM

    I know that I can get the reports from BI publisher , but there is no such report for dataguard status.

    So I wanted to create a custom report for that, but can't find the correct MGMT view to do that.

    Regards,

    Nishant

  • Courtney Llamas-Oracle
    Courtney Llamas-Oracle Member Posts: 782 Employee
    edited Oct 17, 2017 2:13PM

    What type of information are you looking to report?   

    If you're lookign for the db role/open mode, that would be in mgmt$db_instanceinfo

    Configuration Management Views

    If you're looking for the value of specific DG metrics, they would have to be part of the configuration/collection such as Lag, etc.,.. and would come from the monitoring views

    Monitoring Views

    mgmt$alert_current or mgmt$metric_current, depending on what you're looking for.

    Nishant Baurai
  • Nishant Baurai
    Nishant Baurai Member Posts: 215 Bronze Badge
    edited Oct 17, 2017 4:59PM

    Courtney,

    I am looking for a report which tells me about the data guard sync status.

    e.g. like this one...

    Primary          Standby          Status

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

    PRD               PRD_DG          In Sync

    PRD2             PRD2_DG        Out of Sync

    Regards,
    Nishant

  • Ian Baugaard
    Ian Baugaard Member Posts: 530 Silver Badge
    edited Oct 18, 2017 5:28AM Accepted Answer

    Hi Nishant,

    I use this to identify targets that have an apply lag, you can modify it as you see fit

    WITH qb_get_dgmetrics AS (
         select
              target_name
              , MAX( case when column_label = 'Apply Lag (seconds)' then to_number(value) end ) as apply_lag
              , MAX( case when column_label = 'Transport Lag (seconds)' then to_number(value) end ) as transport_lag
              , collection_timestamp
         from mgmt$metric_current
         where metric_name like '%dataguard%'
         and metric_label = 'Data Guard Performance'
         and column_label in ('Apply Lag (seconds)', 'Transport Lag (seconds)' )
         group by target_name, collection_timestamp
    )
    select
         target_name
         , apply_lag
         , transport_lag
         , collection_timestamp
    from qb_get_dgmetrics
    where apply_lag > 300 ;

    Hope this helps

    Ian

    Nishant BauraiUser_R8LB0Raul.Alvarenga
  • Nishant Baurai
    Nishant Baurai Member Posts: 215 Bronze Badge
    edited Oct 18, 2017 10:24AM

    Thank you Ian, this is really helpful.

    Regards,

    Nishant

This discussion has been closed.