Scripting: Is the DB DataGuarded?

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:

  l_isDataguarded number;
  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
    -- The database is standalone
 end if; END; /


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

Up ↑

%d bloggers like this: