create or replace function system.dataguard_state (
  standby_connect_string varchar2 default null,
  set_state varchar2 default null)
  return varchar2 authid definer is

-- If an archiving state is specified (usually ENABLE or DEFER), enable or
-- defer archiving to the given standby database if it's connect string is
-- specified, or to all standby databases if no connect string is specified.
-- Then, return the archiving state for that given standby database or the
-- first standby database found.  This must be run on the primary database.
--
-- After creating this function, create a limited user (dgstate) to run it,
-- so that the script that calls this function doesn't have to connect to
-- system to run it (run it from dgstate instead), and so that the dgstate
-- user doesn't have to be granted alter system privileges.
--
--   from user sys:
--     grant select on v_$parameter to system;
--   from user system:
--     create user dgstate identified by dgstatepw
--       default tablespace users temporary tablespace temp
--       quota 0 on users quota 0 on temp;
--     grant create session to dgstate;
--     grant alter system to system;
--     grant execute on system.dataguard_state to dgstate;
--
-- Author: Stephen Rea <srea@maristream.org>
-- Maristream, Inc.
-- Created: 7/14/05
--
-- Updates:

  dg_dest   v$parameter.value%TYPE;
  dg_state  v$parameter.value%TYPE := 'UNPROTECTED';
  sqlcmd    varchar2(240);
  i1        number := 1;
  i10       number := 10;

begin

-- If a connect string for the standby database was passed, find the
-- log_archive_dest parameter number that contains that connect string,
-- and set the loop to only look at that log_archive_dest number.

  if standby_connect_string is not null then
    begin
      select to_number(substr(name,18)) into i1 from v$parameter
        where name like 'log_archive_dest_%'
        and name not like 'log_archive_dest_state_%'
        and upper(value) like upper('%SERVICE=' || standby_connect_string ||
        ' %');
      i10 := i1;
    exception
    when others then
      return 'UNDEFINED';
    end;
  end if;

-- Loop through the log_archive_dest parameters to find the standby databases
-- listed, if any, then, set their state, if requested, and get the resulting
-- state to return of the first (or only) standby database listed.

  for i in i1 .. i10 loop
    select nvl(value,'~') into dg_dest from v$parameter
      where name = 'log_archive_dest_' || i;
    if instr(upper(dg_dest),'SERVICE=') > 0 then
      if set_state is not null then
        sqlcmd := 'alter system set log_archive_dest_state_' || i ||
          ' = ' || set_state || ' scope=both';
        execute immediate sqlcmd;
      end if;
      if dg_state = 'UNPROTECTED' then
        select value into dg_state from v$parameter
          where name = 'log_archive_dest_state_' || i;
      end if;
    end if;
  end loop;

-- Do a logfile switch to signal the standby databases that archiving to them
-- has been altered, if the state was set above.

  if set_state is not null and dg_state != 'UNPROTECTED' then
    execute immediate 'alter system switch logfile';
  end if;
  return dg_state;
end;
/