/*
|| Oracle 11g DataGuard Listing 3
||
|| Demonstrates how to utilize Enterprise Manager Grid Control to manage the
|| states of an Oracle Database 11g primary and standby Data Guard environment,
|| including:
|| - Using Grid Control to perform a switchover operation
|| - Using Grid Control to perform a switchback operation
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Data Guard features, and they should be carefully proofread before being executed 
|| against any existing Oracle database to avoid potential damage!
*/

/* 
|| Listing 3.1:
|| Performing a switchover using Enterprise Manager Grid Control
*/

-----
-- Results of the switchover operation from the "original" primary database's alert log ...
-----
. . .
Fri Jun 05 16:46:43 2009
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (orcl)
Fri Jun 05 16:46:45 2009
Thread 1 cannot allocate new log, sequence 156
Private strand flush not complete
  Current log# 1 seq# 155 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log
Thread 1 advanced to log sequence 156
  Current log# 2 seq# 156 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Waiting for all non-current ORLs to be archived...
Fri Jun 05 16:48:01 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
Fri Jun 05 16:48:07 2009
Thread 1 cannot allocate new log, sequence 157
Private strand flush not complete
  Current log# 2 seq# 156 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Thread 1 advanced to log sequence 157
  Current log# 3 seq# 157 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Fri Jun 05 16:48:08 2009
ARCH: Standby redo logfile selected for thread 1 sequence 156 for destination LOG_ARCHIVE_DEST_2
Fri Jun 05 16:48:08 2009
Stopping background process CJQ0
Fri Jun 05 16:48:08 2009
. . .
<< results truncated for brevity's sake >>
. . .
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/flash_recovery_area/ORCL/
LOG_ARCHIVE_DEST_2 is a potential switchover target
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/primary_db/orcl/trace/orcl_rsm0_5474.trc
Clearing standby activation ID 1214475032 (0x48636b18)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Archivelog for thread 1 sequence 157 required for standby recovery
Fri Jun 05 16:48:23 2009
MRP0 started with pid=16, OS id=24297 
MRP0: Background Managed Standby Recovery process started (orcl)
Fri Jun 05 16:48:24 2009
idle dispatcher 'D000' terminated, pid = (16, 1)
Fast Parallel Media Recovery NOT enabled
Managed Standby Recovery not using Real Time Apply
 parallel recovery started with 2 processes
Online logfile pre-clearing operation disabled by switchover
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/log_157_1_682541003.arc
Identified End-Of-Redo for thread 1 sequence 157
Resetting standby activation ID 1214475032 (0x48636b18)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 4154221
MRP0: Media Recovery Complete: End-Of-REDO (orcl)
MRP0: Background Media Recovery process shutdown (orcl)
Waiting for MRP0 pid 24297 to terminate
Switchover: Complete - Database shutdown required (orcl)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Fri Jun 05 16:48:43 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 24437
RFS[1]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
. . .
<< results truncated for brevity's sake >>
. . .
ARC2: Thread not mounted
Successful mount of redo thread 1, with mount id 1216558086
Physical Standby Database mounted.
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Jun 05 17:04:11 2009
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete
. . .
<< results truncated for brevity's sake >>
. . .
RFS[3]: Identified database type as 'physical standby'
Fri Jun 05 17:08:39 2009
Media Recovery Waiting for thread 1 sequence 163 (in transit)
Fri Jun 05 17:08:39 2009
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 4: '/u01/app/oracle/oradata/orcl/srl01.log'
Recovery of Online Redo Log: Thread 1 Group 4 Seq 163 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/srl01.log

-----
-- ... and from the alert log of the "original" standby database, now the new primary database.
-----
. . .
Fri Jun 05 16:01:04 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 7550
RFS[3]: Identified database type as 'physical standby'
. . .
<< results truncated for brevity's sake >>
. . .
Fri Jun 05 16:48:03 2009
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Fri Jun 05 16:48:03 2009
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/stdby_db/orcl/trace/orcl_mrp0_7497.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Shutting down recovery slaves due to error 16037
Recovery interrupted!
Waiting for MRP0 pid 7497 to terminate
Errors in file /u01/app/oracle/diag/rdbms/stdby_db/orcl/trace/orcl_mrp0_7497.trc:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process shutdown (orcl)
Managed Standby Recovery Canceled (orcl)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY
Media Recovery Start: Managed Standby Recovery (orcl)
Fast Parallel Media Recovery enabled
Managed Standby Recovery not using Real Time Apply
Media Recovery Log /u01/app/oracle/product/11.1.0/db_1/dbs/archlog_156_1_682541003.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/STDBY/log_157_1_682541003.arc
Identified End-Of-Redo for thread 1 sequence 157
Resetting standby activation ID 1214475032 (0x48636b18)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied through change 4154221
Media Recovery Complete: End-Of-REDO (orcl)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl)
Maximum wait for role transition is 15 minutes.
All dispatchers and shared servers shutdown
Active process 13377 user 'oracle' program 'oracle@11gStdby'
Active process 13321 user 'oracle' program 'oracle@11gStdby'
Active process 11514 user 'oracle' program 'oracle@11gStdby'
Active process 7474 user 'oracle' program 'oracle@11gStdby (TNS V1-V3)'
CLOSE: waiting for server sessions to complete.
CLOSE: all sessions shutdown successfully.
Fri Jun 05 16:48:09 2009
SMON: disabling cache recovery
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/stdby_db/orcl/trace/orcl_rsm0_7494.trc
SwitchOver after complete recovery through change 4154221
Online log /u01/app/oracle/oradata/stdby/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/stdby/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/stdby/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 4154219
Converting standby mount to primary mount.
Switchover: Complete - Database mounted as primary (orcl)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
. . .

/* 
|| Listing 3.2:
|| Performing a switchback using Enterprise Manager Grid Control
*/

-----
-- Results of the switchover operation from the "new" primary database's alert log ...
-----

. . .
Fri Jun 05 17:24:31 2009
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (orcl)
Fri Jun 05 17:24:33 2009
. . .
<< results truncated for brevity's sake >>
. . .
MRP0: Background Media Recovery process shutdown (orcl)
Switchover: Complete - Database shutdown required (orcl)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Fri Jun 05 17:25:18 2009
Shutting down instance (abort)
License high water mark = 11
USER (ospid: 26038): terminating the instance
Instance terminated by USER, pid = 26038
Fri Jun 05 17:25:19 2009
Instance shutdown complete
Fri Jun 05 17:25:24 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side pfile /u01/app/oracle/product/11.1.0/db_1/dbs/initorcl.ora
. . .
<< results truncated for brevity's sake >>
. . .
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 26176
RFS[1]: Identified database type as 'physical standby'
RFS LogMiner: Client disabled from further notification
RFS[1]: Successfully opened standby log 5: '/u01/app/oracle/oradata/stdby/srl02.log'
Fri Jun 05 17:25:48 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 26180
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 5: '/u01/app/oracle/oradata/stdby/srl02.log'
Fri Jun 05 17:25:51 2009
RSM0 started with pid=29, OS id=26190 
ALTER SYSTEM SET log_archive_dest_1='location="/u01/app/oracle/flash_recovery_area/STDBY/"','valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH SID='orcl';
ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH SID='orcl';
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='orcl';
ALTER SYSTEM SET log_archive_format='log_%s_%t_%r.arc' SCOPE=SPFILE SID='orcl';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/stdby/' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='orcl' SCOPE=BOTH;
ALTER SYSTEM SET fal_client='stdby' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (orcl)
Fri Jun 05 17:25:55 2009
MRP0 started with pid=30, OS id=26192 
MRP0: Background Managed Standby Recovery process started (orcl)
Fast Parallel Media Recovery enabled
Managed Standby Recovery starting Real Time Apply
 parallel recovery started with 2 processes
Waiting for all non-current ORLs to be archived...
Clearing online redo logfile 1 /u01/app/oracle/oradata/stdby/redo01.log
Clearing online log 1 of thread 1 sequence number 164
Fri Jun 05 17:26:02 2009
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/stdby/redo02.log
Clearing online log 2 of thread 1 sequence number 165
Fri Jun 05 17:26:06 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 26208
RFS[3]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 5: '/u01/app/oracle/oradata/stdby/srl02.log'
. . .
<< results truncated for brevity's sake >>
. . .

RFS[4]: Assigned to RFS process 26215
RFS[4]: Identified database type as 'physical standby'
RFS[4]: Archived Log: '/u01/app/oracle/flash_recovery_area/STDBY/log_166_1_682541003.arc'
Fri Jun 05 17:26:15 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 26221
RFS[5]: Identified database type as 'physical standby'
Fri Jun 05 17:26:40 2009
Media Recovery Log /u01/app/oracle/flash_recovery_area/STDBY/log_166_1_682541003.arc
Media Recovery Log /u01/app/oracle/product/11.1.0/db_1/dbs/archlog_167_1_682541003.arc
Media Recovery Log /u01/app/oracle/product/11.1.0/db_1/dbs/archlog_168_1_682541003.arc
Media Recovery Waiting for thread 1 sequence 169 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 169 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/stdby/srl02.log
. . .

-----
-- ... and from the alert log of the "original" primary database.
-----

. . .
Fri Jun 05 17:23:50 2009
Media Recovery Waiting for thread 1 sequence 164
Fri Jun 05 17:25:07 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 994
RFS[4]: Identified database type as 'physical standby'
RFS[4]: Successfully opened standby log 4: '/u01/app/oracle/oradata/orcl/srl01.log'
Fri Jun 05 17:25:07 2009
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/log_164_1_682541003.arc
Media Recovery Waiting for thread 1 sequence 165
Fri Jun 05 17:25:14 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 1186
RFS[5]: Identified database type as 'physical standby'
RFS[5]: Archived Log: '/u01/app/oracle/flash_recovery_area/ORCL/log_165_1_682541003.arc'
Fri Jun 05 17:25:14 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 1223
RFS[6]: Identified database type as 'physical standby'
Fri Jun 05 17:25:20 2009
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
. . .
<< results truncated for brevity's sake >>
. . .
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY
Media Recovery Start: Managed Standby Recovery (orcl)
Fast Parallel Media Recovery enabled
Managed Standby Recovery not using Real Time Apply
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER NODELAY
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl)
Maximum wait for role transition is 15 minutes.
. . .
<< results truncated for brevity's sake >>
. . .
Standby became primary SCN: 4156665
Converting standby mount to primary mount.
Switchover: Complete - Database mounted as primary (orcl)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
Fri Jun 05 17:25:27 2009
ARC0: STARTING ARCH PROCESSES
ARC4: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no SRL' ARCH
Fri Jun 05 17:25:27 2009
ARC4 started with pid=17, OS id=1232 
Fri Jun 05 17:25:32 2009
ALTER SYSTEM SET log_archive_dest_2='service="stdby"','   LGWR ASYNC NOAFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1   reopen=300 db_unique_name="stdby_db" net_timeout=30  valid_for=(online_logfile,primary_role)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER DATABASE OPEN
Fri Jun 05 17:25:32 2009
Assigning activation ID 1216558086 (0x48833406)
Fri Jun 05 17:25:32 2009
LNS1 started with pid=28, OS id=1246 
. . .
<< results truncated for brevity's sake >>
. . .
FBDA started with pid=26, OS id=1263 
Starting background process QMNC
Fri Jun 05 17:25:40 2009
QMNC started with pid=29, OS id=1269 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Fri Jun 05 17:25:53 2009
Completed: ALTER DATABASE OPEN