Oracle extension metric for Data Guard - NOLOGGING activity

[mn_24]

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

[Mike_L]

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#

[mn_24]

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 · Accepted solution]

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!

[Mike_L · Accepted solution]

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