set showmode off echo off

-- Script: dgshutdown.sql
--
-- Purpose: Shut down a Data Guard primary or physical standby database.
-- If this is a primary database, just do a log file switch before shutting
-- it down.  Otherwise, if this is a standby database, terminate any active
-- sessions on the standby and switch to managed recovery mode if currently
-- in read-only access, then, defer the archiving by the primary database to
-- this standby database (optional), and cancel managed recovery.  This script
-- must be run when logged in as sysdba from a UNIX shell.
--
-- 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 shutdown 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 - Changed checking for standby logs released from an unnamed
--     block to a generated script to keep from getting an error when in
--     the mount state (when defer archiving is not being done).
--   7/25/06 - Added set timing off and set showmode off to clean up the
--     output from the script.
--   6/12/07 - Added shutdown option (such as "@dgshutdown abort"), defaulting
--     to immediate.

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

define dg_defer = NO
set timing off
set heading on termout on feedback off verify off linesize 240 trimspool on

-- Get the shutdown option (such as "@dgshutdown abort"), if not immediate.

set termout off
define dg_shutopt = immediate
spool dgshutdown_do.sql
define 1
spool off
!cat dgshutdown_do.sql | sed 's/.*"\(.*\)".*/define dg_shutopt = \1/' | sed 's/.*UNDEFINED/define dg_shutopt = immediate/' >dgshutdown_do.sql
@dgshutdown_do.sql
undefine 1
set termout on

-- Get the 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).  (This also shows the instance
-- name for informational purposes.)

column dg_database_role new_value dg_database_role
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
column defer_logging format a13
define dg_fal_server = none
define dg_fal_client = none
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,
 decode('&dg_database_role','PRIMARY','N/A (PRIMARY)','&dg_defer') defer_logging
    from dual;
prompt
set heading off recsep off termout off

-- If this is a standby database and managed recovery is already cancelled,
-- we don't have to do anything. 

select 'CANCELLED' dg_database_role
  from dual where (select count(*) from v$managed_standby
    where process like 'MRP%') = 0
  and '&dg_open_mode' = 'MOUNTED'
  and '&dg_database_role' = 'PHYSICAL STANDBY';
set termout on
select 'Managed Recovery already cancelled' || chr(10) || chr(10)
  from dual where '&dg_database_role' = 'CANCELLED';
set termout off

-- If this is a primary database, all we need to do is a logfile switch to
-- archive the latest changes.

spool dgshutdown_do.sql
select 'set termout on feedback on echo on' || chr(10) ||
  'alter system switch logfile;' || chr(10) ||
  'set echo off termout off feedback off'
  from dual where '&dg_database_role' = 'PRIMARY';
spool off
@dgshutdown_do.sql

--   The remainder of this script is for a physical standby database.
-- Terminate any active sessions (except for this current session) and
-- switch back to managed recovery mode on the standby if it is currently
-- opened for read-only.  (Note: The "kill" command is a unix command that
-- kills a process.  Since the process isn't automatically killed at the
-- time that the associated session is killed, we must kill the process
-- explicitly, since managed recovery will not terminate while session
-- processes exist.  If you aren't on unix, replace the kill command with
-- it's equivalent on your O/S.)
  
spool dgshutdown_do.sql
select 'set termout on feedback on echo on'
  from dual where '&dg_database_role' = 'PHYSICAL STANDBY'
  and (('&dg_logging' = 'YES (RECEIVING)' and '&dg_defer' = 'YES')
    or ('&dg_open_mode' = 'READ ONLY'));
select 'alter system kill session ''' || sid || ',' || serial# || ''';'
  from v$session where username is not null
  and sid not in (select distinct sid from v$mystat)
  and '&dg_open_mode' = 'READ ONLY'
  and '&dg_database_role' = 'PHYSICAL STANDBY';
select '!kill -9 ' || vp.spid
  from v$session vs,v$process vp where vs.paddr = vp.addr
  and vs.username is not null
  and vs.sid not in (select distinct sid from v$mystat)
  and '&dg_open_mode' = 'READ ONLY'
  and '&dg_database_role' = 'PHYSICAL STANDBY';
select
  'alter database recover managed standby database disconnect from session;'
  from dual where '&dg_open_mode' = 'READ ONLY'
  and '&dg_database_role' = 'PHYSICAL STANDBY';

-- Optionally (via dg_defer), if archiving to the standby from the primary
-- is active, connect to the primary database (as user dgstate) and defer
-- archiving for this standby, and connect back to the standby.  (Note: The
-- sleep command tries to allow time (4 seconds) for the archiving from the
-- primary to finish before opening the database and getting into a loop to
-- wait on it.  If you aren't on unix, replace the sleep command with it's
-- equivalent on your O/S.)

select 'set termout off feedback off echo off' || chr(10) ||
  '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'',''DEFER''),' ||
    '''DEFER'',chr(10) || ''Archiving to standby deferred'' || chr(10)),' ||
    '''ENABLE'',chr(10) || ''Archiving to standby NOT DEFERRED'' || chr(10)));' ||
  chr(10) || 'set termout off' || chr(10) || 'connect / as sysdba' || chr(10) ||
  '!sleep 1'
  from dual where '&dg_logging' = 'YES (RECEIVING)' and '&dg_defer' = 'YES'
  and '&dg_database_role' = 'PHYSICAL STANDBY';
select 'set echo off termout off feedback off'
  from dual where '&dg_database_role' = 'PHYSICAL STANDBY'
  and (('&dg_logging' = 'YES (RECEIVING)' and '&dg_defer' = 'YES')
    or ('&dg_open_mode' = 'READ ONLY'));
spool off
@dgshutdown_do.sql

-- Optionally, if there are still standby log files being received, open
-- the standby for read-only, wait until all of the standby log files have
-- been received, and switch back to managed recovery mode to apply the
-- remaining standby log files.  (Notes: v_$standby_log is queried instead
-- of v$standby_log when in a PL/SQL block running from the database read-
-- only state.  Limit the wait to 10 seconds - a defer sometimes doesn't
-- release or clear the v_$standby_log entries.)

select decode(count(*),0,'NO (RECEIVING)','YES (RECEIVING)') dg_logging
  from v$standby_log
  where status != 'UNASSIGNED' and '&dg_database_role' = 'PHYSICAL STANDBY'
  and dg_defer = 'YES';
spool dgshutdown_do.sql
select 'set termout on feedback on echo on' || chr(10) ||
  'alter database recover managed standby database cancel;' || chr(10) ||
  'alter database open read only;' || chr(10) ||
  'set echo off termout off feedback off'
  from dual where '&dg_logging' = 'YES (RECEIVING)' and '&dg_defer' = 'YES'
  and '&dg_database_role' = 'PHYSICAL STANDBY';
spool off
@dgshutdown_do.sql

set termout on
select 'Waiting for remaining standby logs to be applied ...' 
  from dual where 0 < (select count(*) from v$standby_log
  where status != 'UNASSIGNED'
  and '&dg_logging' = 'YES (RECEIVING)' and '&dg_defer' = 'YES'
  and '&dg_database_role' = 'PHYSICAL STANDBY');
set termout off

spool dgshutdown_do.sql
select
  'set serveroutput on termout on' || chr(10) ||
  'declare' || chr(10) ||
  '  cnt  number;' || chr(10) ||
  'begin' || chr(10) ||
  '  for i in 1..5 loop' || chr(10) ||
  '    select count(*) into cnt from v$standby_log' || chr(10) ||
  '      where status != ''UNASSIGNED'';' || chr(10) ||
  '    exit when cnt = 0;' || chr(10) ||
  '    dbms_lock.sleep(2);' || chr(10) ||
  '    if i = 5 then' || chr(10) ||
  '      dbms_output.put_line(''Standby log(s) not released after 10 seconds.'');' || chr(10) ||
  '    end if;' || chr(10) ||
  '  end loop;' || chr(10) ||
  '  dbms_output.put_line(chr(13));' || chr(10) ||
  'end;' || chr(10) ||
  '/' || chr(10) ||
  'set termout off'
  from dual where '&dg_logging' = 'YES (RECEIVING)' and '&dg_defer' = 'YES'
  and '&dg_database_role' = 'PHYSICAL STANDBY';
spool off
@dgshutdown_do.sql

spool dgshutdown_do.sql
select 'set termout on feedback on echo on' || chr(10) ||
  'alter database recover managed standby database disconnect from session;' ||
  chr(10) || 'set echo off termout off feedback off'
  from dual where '&dg_logging' = 'YES (RECEIVING)' and '&dg_defer' = 'YES'
  and '&dg_database_role' = 'PHYSICAL STANDBY';
spool off
@dgshutdown_do.sql

-- Terminate the managed recovery processing on the standby database,
-- if needed, and shut down the database.

spool dgshutdown_do.sql
select 'set termout on feedback on echo on' from dual;
select 'alter database recover managed standby database cancel;'
  from dual where '&dg_database_role' = 'PHYSICAL STANDBY';
select 'shutdown &dg_shutopt' || chr(10) || chr(10) from dual;
spool off
set verify on heading on recsep wrap linesize 80 
@dgshutdown_do.sql