If you are using Oracle dNFS (Direct NFS) to deliver or backup your database with Oracle RMAN (Recovery Manager) you may want to consider using Oracle MONDNFS rather than writing you own SQL monitoring scripts.
Oracle provide a number of database views to help with the initial configuration and monitoring of dNFS these include:
- v$dnfs_servers – Displays information about the NFS servers accessed by Direct NFS.
- v$dnfs_files – Displays information about the Oracle files opened using Direct NFS.
- v$dnfs_channels – Displays information about the network connections open to the NFS servers.
- v$dnfs_stats – Displays statistics of the different NFS operations issued by Direct NFS
If you are using NFS and have not already configured dNFS you may want to check-out a couple of my previous posts, the first where I document the configuration of Oracle dNFS multi-path on a PureStorage FlashBlade and the second where I share how to perform Oracle dNFS throughput testing.
Oracle have written a couple of monitoring scripts which allow us to monitor the Oracle dNFS IOPS (IO’s per second) and MBPS (MB per second). These can be pretty useful when you are trying to diagnose a network, storage or database configuration problem, and the output is pretty formatted.
You can download mondnfs.sql using your Oracle support account from MOS using note: Direct NFS monitoring and v$views (Doc ID 1495739.1)
Once you have downloads mondnfs.sql script creates 2 PL/SQL procedures, you will need to run it e.g.
$ sqlplus / as sysdba SQL*Plus: Release 188.8.131.52.0 - Production on Tue Mar 24 11:50:54 2020 Version 184.108.40.206.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 - Production Version 18.104.22.168.0 SQL> @mondnfs.sql Procedure created. Procedure created.
As you can see from the above the script creates 2 new PL/SQL procedures, these are called dnfs_monitor and dnfs_itermonitor.
This procedure takes time in seconds and returns the average IOPS and MBPS over that time period. For example to run dnfs_monitor for 1 minute try the following.
Please note you may need to turn SQL*Plus serveroutput on to see any results.
SQL> set serveroutput on; SQL> exec dnfs_monitor(60);
This procedure takes time in seconds and number of iterations as input. It returns ‘n’ iterations of the average IOPS and MBPS over the time duration specified. For example to run dnfs_itermonitor every 2 seconds 10 times try:
SQL> exec dnfs_itermonitor(2,10);Follow @RonEkins