Oracle extension metric for Data Guard - NOLOGGING activity

mn_24 — Mon, 24 Jun 2024 06:34:43 GMT

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

Re: Oracle extension metric for Data Guard - NOLOGGING activity

Mike_L — Fri, 21 Jun 2024 09:42:36 GMT

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#

Re: Oracle extension metric for Data Guard - NOLOGGING activity

mn_24 — Fri, 21 Jun 2024 14:59:05 GMT

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

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#;

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

Re: Oracle extension metric for Data Guard - NOLOGGING activity

TomásSeroteRoos — Thu, 04 Jul 2024 11:43:12 GMT

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!

Re: Oracle extension metric for Data Guard - NOLOGGING activity

Mike_L — Thu, 04 Jul 2024 13:27:21 GMT

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

topic Oracle extension metric for Data Guard - NOLOGGING activity in Extensions

Oracle extension metric for Data Guard - NOLOGGING activity

Re: Oracle extension metric for Data Guard - NOLOGGING activity

Re: Oracle extension metric for Data Guard - NOLOGGING activity

Re: Oracle extension metric for Data Guard - NOLOGGING activity

Re: Oracle extension metric for Data Guard - NOLOGGING activity