set showmode off echo off

-- Script: dgstartup.sql
--
-- Purpose: Start up a Data Guard primary or physical standby database.  If
-- this is a primary database, it just has to be mounted and opened.  If
-- this is a physical standby database, the startup command will fail with
-- "ORA-01666: controlfile is for a standby database" when the mount is
-- attempted.  So, try mounting the database again, this time as a standby
-- database, and enable managed recovery, then, enable the archiving by the
-- primary database to this standby database (optional).  This script can
-- be run with the database shut down or started in the nomount state (which
-- is where a normal startup stops on a standby database), and must be run
-- when logged in as sysdba.
--
-- Notes: This optionally uses the dataguard_state routine called from the
-- limited dgstate Oracle user ID, both of which are described on my web
-- site at:
--   http://www.oracletips.info/dataguard.htm
-- This also requires the use of standby redo logs (which gives me a way
-- to test to see if the primary is up and to see if archiving to the
-- standby is active).
--
-- Be aware that this functionality can't be incorporated into a PL/SQL
-- database procedure (including a database startup trigger) because of
-- all of the restrictions on what can be run and accessed in PL/SQL from
-- a database that is not in the open state.
--
-- Author: Stephen Rea <srea@maristream.org>
-- Maristream, Inc.
-- Created: 7/14/05
--
-- Updates:
--   8/17/05 - Don't show connect statement before enabling archiving.
--   7/25/06 - Added set timing off and set showmode off to clean up the
--     output from the script.

-- Note: Enable archiving is optional here.  For now, don't enable archiving
-- since the dgshutdown.sql script didn't defer archiving to the standby (it
-- causes the primary to get "ORA-03113: end-of-file on communication channel"
-- on startup).  We may enable later (setting dg_enable to YES) if we can find
-- a way around that startup error.

define dg_enable = NO
set timing off

-- Startup the database in the nomount state.  (This command will fail
-- if the database is already started - just ignore the error message.
-- We have to connect again after the error in order to clear out the
-- error so that we can set a comment field later on; otherwise, it
-- doesn't get set for some reason.)

startup nomount
connect / as sysdba
prompt

set heading off recsep off feedback off termout off verify off
set linesize 240 trimspool on
column dg_database_role new_value dg_database_role
column dg_status new_value dg_status
select status dg_status from v$instance;
set termout on
set serveroutput on

declare
  dg_status         varchar2(16) := '&dg_status';
  dg_sqlcode        integer := 0;
begin

-- If the database is started and in the nomount state, try bringing it to
-- the mount state.  If that works, then this must be a primary database.
-- If the mount didn't work because of an ORA-01666 error ("controlfile is
-- for a standby database"), mount it as a standby database.

  if dg_status = 'STARTED' then
    begin
      execute immediate 'alter database mount';
      dbms_output.put_line('Primary database mounted');
    exception
    when others then
      dg_sqlcode := sqlcode;
      if dg_sqlcode = -1666 then
        begin
          execute immediate 'alter database mount standby database';
          dbms_output.put_line('Standby database mounted');
          dg_sqlcode := 0;
        exception
        when others then
          dbms_output.put_line(sqlerrm);
          return;
        end;
      else
        dbms_output.put_line(sqlerrm);
        return;
      end if;
    end;
  end if;
end;
/

set termout off
select status dg_status from v$instance;
select decode(count(*),0,'&dg_status','MANAGED RECOVERY') dg_status
  from v$managed_standby where process like 'MRP%'; 
select database_role dg_database_role from v$database;
column dg_state_10 new_value dg_state_10
select value dg_state_10 from v$parameter
  where name = 'log_archive_dest_state_10';
set termout on

declare
  dg_status         varchar2(16) := '&dg_status';
  dg_database_role  varchar2(16) := '&dg_database_role';
  dg_sqlcode        integer := 0;
  dg_msg            varchar2(80) := null;
begin

-- If the database is in the mount state, just open it if it is a primary
-- database.  If it is a physical standby database, enable managed recovery.

  if dg_status = 'MOUNTED' then
    if dg_database_role = 'PRIMARY' then
      begin 
        execute immediate 'alter database open';
        dg_msg := 'Primary database opened';
      exception
      when others then
        dg_msg := sqlerrm;
      end;
    elsif dg_database_role = 'PHYSICAL STANDBY' then
      begin
        execute immediate 'alter database recover managed standby database' ||
           ' disconnect from session'; 
        dg_msg := 'Managed recovery started';
      exception
      when others then
        dg_msg := sqlerrm;
      end;
    else
      dg_msg := 'Database with role ' || dg_database_role || ' not mounted';
    end if;
  elsif dg_status = 'MANAGED RECOVERY' then
    dg_msg := 'Managed recovery is already running';
  elsif dg_status = 'OPEN' then
    dg_msg := 'Primary database is already open';
  end if;

-- Store the results into the log_archive_dest_state_10 comments field
-- (this is the cleanest way I could get a value passed back to the SQL
-- script from an anonymous PL/SQL block).  For some reason, dbms_output
-- doesn't work after an "alter database open" command is done in a block. 

  if dg_msg is not null then
    execute immediate 'alter system set log_archive_dest_state_10 = ' ||
      '&dg_state_10 comment = ''' || dg_msg || ''' scope = memory';
  end if;
end;
/

-- Display the resulting message from the PL/SQL block above.

set termout on
select update_comment from v$parameter where name = 'log_archive_dest_state_10';
prompt
set termout off

-- Optionally (via dg_enable), connect to the primary database (as user
-- dgstate) and enable archiving for this standby, and connect back to the
-- standby.

column dg_fal_server new_value dg_fal_server
column dg_fal_client new_value dg_fal_client
define dg_fal_server = none
define dg_fal_client = none
select value dg_fal_server from v$parameter where name = 'fal_server'
  and '&dg_database_role' != 'PRIMARY';
select value dg_fal_client from v$parameter where name = 'fal_client'
  and '&dg_database_role' != 'PRIMARY';
prompt

spool dgstartup_do.sql
select 'connect dgstate/dgstatepw@' || '&dg_fal_server' || chr(10) ||
  'set termout on serveroutput on' || chr(10) ||
  'execute dbms_output.put_line(replace(replace(system.dataguard_state(' ||
    '''&dg_fal_client'',''ENABLE''),' ||
    '''ENABLE'',''Archiving to standby enabled''),' ||
    '''DEFER'',''Archiving to standby NOT ENABLED''));' || chr(10) ||
  'set termout off' || chr(10) || 'connect / as sysdba'
  from v$parameter where name = 'log_archive_dest_state_10'
  and update_comment = 'Managed recovery started'
  and '&dg_enable' = 'YES'
  and '&dg_database_role' = 'PHYSICAL STANDBY';
spool off
@dgstartup_do.sql
spool dgstartup_do.sql
spool off

-- Display the instance name, current database role (such as PRIMARY or
-- PHYSICAL STANDBY) and open mode (such as MOUNTED or READ ONLY or READ
-- WRITE), along with the fetch archive log server and client (which are
-- the primary and standby connect strings), and whether logging to the
-- standby is active (which also indicates that the primary is up).

set heading on termout on verify off
column dg_open_mode new_value dg_open_mode format a12
column dg_fal_server new_value dg_fal_server format a30
column dg_fal_client new_value dg_fal_client format a30
column dg_logging new_value dg_logging format a15
select instance_name,database_role dg_database_role,open_mode dg_open_mode
  from v$instance,v$database;
select value dg_fal_server from v$parameter where name = 'fal_server'
  and '&dg_database_role' != 'PRIMARY';
select value dg_fal_client from v$parameter where name = 'fal_client'
  and '&dg_database_role' != 'PRIMARY';
select decode('&dg_database_role','PRIMARY',
  (select decode(count(*),0,'NO (SENDING)','YES (SENDING)')
    from v$managed_standby where status = 'WRITING'
    and process in ('LGWR','ARCH')),
  (select decode(count(*),0,'NO (RECEIVING)','YES (RECEIVING)')
    from v$standby_log where status != 'UNASSIGNED')) dg_logging
    from dual;
prompt
set timing on
set feedback 6 verify on recsep wrap linesize 80 echo on showmode on