cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Oracle extension metric for Data Guard - NOLOGGING activity

mn_24
Participant

Hi,

does anybody know what exactly is the query by which this metric is checked OR to give an idea how to check if it is working in other words to replicate this metric and to see if it is working.

  

Metric name Metric key Description  Unit
NOLOGGING activity com.dynatrace.extension.sql-oracle.dataguard.nologgingActivity Counts the number of files which contain NOLOGGING activity in the last day. If the count is greater than 0 then the standby database is vulnerable; check the dimensions to find out which files must be refreshed on the standby. Count
4 REPLIES 4

Mike_L
Dynatrace Guru
Dynatrace Guru

Hi,

This is the query behind it:

          SELECT
              DF.FILE#,
              DF.NAME,
              DF.UNRECOVERABLE_CHANGE#,
              COUNT(*) AS FILE_COUNT
          FROM
              V$DATAFILE DF
          JOIN V$DATABASE DB ON DF.CON_ID = DB.CON_ID
          WHERE
              DB.DATABASE_ROLE = 'PRIMARY' AND
              DF.UNRECOVERABLE_TIME > (SYSDATE - 1)
          GROUP BY
              DF.FILE#,
              DF.NAME,
              DF.UNRECOVERABLE_CHANGE#
Mike

mn_24
Participant

Hi,

thank you. 

I understand that with it you monitor if someone on purpose or not used NOLOGGING transactions and by with you want to identify how many datafiles are reported as unrecoverable.

We've tested on Data Guard with PDBs and when the JOIN on CON_ID is in place the query doesn't return any data.

It should be edited a little bit for example(BUT add also to check from V$DATABASE if the role is PRIMARY !!):

"

SELECT con_id,file#,
name,
unrecoverable_time,
unrecoverable_change#
FROM v$datafile where UNRECOVERABLE_TIME > (SYSDATE - 1);

"

 

 

SQL> SELECT
DF.FILE#,
DF.NAME,
DF.UNRECOVERABLE_CHANGE#,
COUNT(*) AS FILE_COUNT
FROM
V$DATAFILE DF
GROUP BY
DF.FILE#,
DF.NAME,
DF.UNRECOVERABLE_CHANGE#;  

     FILE# NAME                                                                                                                     UNRECOVERABLE_CHANGE# FILE_COUNT
---------- ------------------------------------------------------------------------------------------------------------------------ --------------------- ----------
         4 +DATADISK/ORA19C/DATAFILE/undotbs1.312.1171006335                                                                                            0          1
        12 +DATADISK/ORA19C/1A4C5B290B813B27E065000000000001/DATAFILE/users.332.1171007223                                                              0          1
        13 /u00/app/oracle/product/19.3/dbs/tbs1_data.dbf                                                                                               0          1
        14 +DATADISK/ORA19C/DATAFILE/dynafras.590.1172210439                                                                                      5004769          1
         5 +DATADISK/ORA19C/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.323.1171006605                                                             0          1
         9 +DATADISK/ORA19C/1A4C5B290B813B27E065000000000001/DATAFILE/system.329.1171007215                                                             0          1
        10 +DATADISK/ORA19C/1A4C5B290B813B27E065000000000001/DATAFILE/sysaux.330.1171007215                                                             0          1
         7 +DATADISK/ORA19C/DATAFILE/users.313.1171006335                                                                                         4824476          1
         8 +DATADISK/ORA19C/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.325.1171006605                                                           0          1
        11 +DATADISK/ORA19C/1A4C5B290B813B27E065000000000001/DATAFILE/undotbs1.328.1171007215                                                           0          1
         3 +DATADISK/ORA19C/DATAFILE/sysaux.311.1171006309                                                                                              0          1

     FILE# NAME                                                                                                                     UNRECOVERABLE_CHANGE# FILE_COUNT
---------- ------------------------------------------------------------------------------------------------------------------------ --------------------- ----------
         1 +DATADISK/ORA19C/DATAFILE/system.310.1171006265                                                                                              0          1
         6 +DATADISK/ORA19C/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.324.1171006605                                                             0          1

13 rows selected.

SQL> SELECT
DF.FILE#,
DF.NAME,
DF.UNRECOVERABLE_CHANGE#,
COUNT(*) AS FILE_COUNT
FROM
V$DATAFILE DF
WHERE DF.UNRECOVERABLE_TIME > (SYSDATE - 1)
GROUP BY
DF.FILE#,
DF.NAME,
DF.UNRECOVERABLE_CHANGE#;         

     FILE# NAME                                                                                                                     UNRECOVERABLE_CHANGE# FILE_COUNT
---------- ------------------------------------------------------------------------------------------------------------------------ --------------------- ----------
        14 +DATADISK/ORA19C/DATAFILE/dynafras.590.1172210439                                                                                      5004769          1
         7 +DATADISK/ORA19C/DATAFILE/users.313.1171006335                                                                                         4824476          1

SQL> SELECT
  2  DF.FILE#,
  3  DF.NAME,
DF.UNRECOVERABLE_CHANGE#,
COUNT(*) AS FILE_COUNT
FROM
V$DATAFILE DF
JOIN V$DATABASE DB ON DF.CON_ID = DB.CON_ID
          WHERE
              DB.DATABASE_ROLE = 'PRIMARY' AND DF.UNRECOVERABLE_TIME > (SYSDATE - 1) 
GROUP BY
DF.FILE#,
DF.NAME,
DF.UNRECOVERABLE_CHANGE#;        

no rows selected

 

SQL> SELECT CON_ID FROM V$DATAFILE ;

    CON_ID
----------
         1
         1
         1
         2
         2
         1
         2
         3
         3
         3
         3

    CON_ID
----------
         1
         1

13 rows selected.

SQL> SELECT CON_ID FROM V$DATABASE;

    CON_ID
----------
         0

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED

TomásSeroteRoos
Dynatrace Enthusiast
Dynatrace Enthusiast

Hi mn_24,

You are indeed right, our JOIN isn't working properly.
This should be fixed on our next version v2.5.1 coming out soon.

Thank you very much for your feedback, it's thanks to users like you that we can keep improving our product!

Version 2.5.1 with the fix was just added to the Dynatrace hub: https://www.dynatrace.com/hub/detail/oracle-database/#release-notes

Mike

Featured Posts