Whilst developing some administrative functions within PL/SQL I needed to know whether the database that the code was being run in was part of an Oracle DataGuard setup, which you would think would be a trivial exercise.
My first thought was that we could determine this via v$database. If the DATABASE_ROLE column in v$database contains STANDBY then we know it’s a DataGuard configuration… but what if the database role is PRIMARY? how then can we determine if that’s a stand-alone database or a DataGuard primary database?
Next, I thought looking in v$parameter might reveal some useful parameters such as dg_broker_config_file. However this only tells us whether a DataGuard configuration has been setup using DataGuard Broker – not whether the configuration is active, or whether it was configured manually (without broker).
My final solution was to query v$archive_dest_status to determine if there were any valid remote destinations. This isn’t foolproof by any means, but covers the most of the likely scenarios. If you have any better solutions then please post them in the comments.
Here’s the query I used within a PL/SQL block:
DECLARE l_isDataguarded number; BEGIN select count(*) into l_isDataguarded from v$archive_dest_status where status='VALID' and type!='LOCAL'; if (l_isDataguarded>0) then -- The database is part of a valid dataguard configuration else -- The database is standalone end if; END; /