The data guard configuration created earlier will be configured to be managed through dataguard broker. There are some dataguard broker specific setup and configuration need to be done to the earlier setup.
1. Data guard broker requires a special naming convention to be followed in the value entered for global_dbname in listener.ora. Database sid should be suffixed with _DGMGRL if not data guard broker will not function properly. See metalink note Automatic Restart of Databases during Switchover fail with ORA-12514 in DGMGRL [ID 308943.1]
Change the earlier listener.ora files as below, on standby rac4b
more listener.ora LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac11g2s.domain.net) (SID_NAME = rac11g2s1) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) (SID_DESC = (GLOBAL_DBNAME = rac11g2s_DGMGRL.domain.net) (SID_NAME = rac11g2s1) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) ) SID_LIST_LISTENER_SCAN1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac11g2s.domain.net) (SID_NAME = rac11g2s1) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) (SID_DESC = (GLOBAL_DBNAME = rac11g2s_DGMGRL.domain.net) (SID_NAME = rac11g2s1) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) )on rac5b
more listener.ora LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac11g2s.domain.net) (SID_NAME = rac11g2s2) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) (SID_DESC = (GLOBAL_DBNAME = rac11g2s_DGMGRL.domain.net) (SID_NAME = rac11g2s2) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) ) SID_LIST_LISTENER_SCAN1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac11g2s.domain.net) (SID_NAME = rac11g2s2) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) (SID_DESC = (GLOBAL_DBNAME = rac11g2s_DGMGRL.domain.net) (SID_NAME = rac11g2s2) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) )Stop and start the listeners and check the static listener is available
srvctl stop listener -n `hostname -s` srvctl start listener -n `hostname -s` srvctl stop scan_listener srvctl start scan_listener lsnrctl status listener LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 23-FEB-2011 17:51:05 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 23-FEB-2011 17:42:04 Uptime 0 days 0 hr. 9 min. 1 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora Listener Log File /opt/app/oracle/diag/tnslsnr/rac5b/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.93)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.97)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service... Service "rac11g2s.domain.net" has 1 instance(s). Instance "rac11g2s2", status UNKNOWN, has 1 handler(s) for this service... Service "rac11g2s.domain.net" has 1 instance(s). Instance "rac11g2s2", status READY, has 1 handler(s) for this service... Service "rac11g2s_DGMGRL.domain.net" has 1 instance(s). Instance "rac11g2s2", status UNKNOWN, has 1 handler(s) for this service... The command completed successfullyTo view the scan listener status it is important to set the ORACLE_HOME variable to GRID_HOME (or CRS_HOME whatever used) if not
lsnrctl status listener_scan1 TNS-01101: Could not find service name listener_scan1will be shown.
export ORACLE_HOME=$CRS_HOME
$CRS_HOME/bin/lsnrctl status listener_scan1
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 23-FEB-2011 17:50:18
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias LISTENER_SCAN1
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 23-FEB-2011 17:49:44
Uptime 0 days 0 hr. 0 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /opt/app/11.2.0/grid/log/diag/tnslsnr/rac5b/listener_scan1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.92)(PORT=1521)))
Services Summary...
Service "rac11g2s.domain.net" has 1 instance(s).
Instance "rac11g2s2", status UNKNOWN, has 1 handler(s) for this service...
Service "rac11g2s_DGMGRL.domain.net" has 1 instance(s).
Instance "rac11g2s2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfullySimilarly make the changes to primary listener.ora files as well. On rac4LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac11g2.domain.net) (SID_NAME = rac11g21) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) (SID_DESC = (GLOBAL_DBNAME = rac11g2_DGMGRL.domain.net) (SID_NAME = rac11g21) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) ) SID_LIST_LISTENER_SCAN1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac11g2.domain.net) (SID_NAME = rac11g21) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) (SID_DESC = (GLOBAL_DBNAME = rac11g2_DGMGRL.domain.net) (SID_NAME = rac11g21) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) )On rac5
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac11g2.domain.net) (SID_NAME = rac11g22) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) (SID_DESC = (GLOBAL_DBNAME = rac11g2_DGMGRL.domain.net) (SID_NAME = rac11g22) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) ) SID_LIST_LISTENER_SCAN1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = rac11g2.domain.net) (SID_NAME = rac11g22) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) (SID_DESC = (GLOBAL_DBNAME = rac11g2_DGMGRL.domain.net) (SID_NAME = rac11g22) (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1) ) )As with the standby restart the listeners and verify the static listener is available.
2. There are three initialization parameters that is related to data guard broker
SQL> show parameter dg NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string /opt/app/oracle/product/11.2.0 /db_1/dbs/dr1rac11g2.dat dg_broker_config_file2 string /opt/app/oracle/product/11.2.0 /db_1/dbs/dr2rac11g2.dat dg_broker_start boolean FALSEIn a RAC environment dg_broker_config_file1 and dg_broker_config_file2 must be stored in a shared location that could be accessed by all instances. The location could be Cluster file system,Raw devices or ASM. These location must be same on all instances. The file need not exist but if a new location is created to store them create it beforehand. In this case these files were stored in ASM and a directory was created inside asm diskgroup. On primary
ASMCMD>cd data/rac11g2 pwd +data/rac11g2 ASMCMD> mkdir dgbrokerOn standby
ASMCMD>cd data/rac11g2s pwd +data/rac11g2s ASMCMD> mkdir dgbrokerThen set the intialization parameters on primary with
SQL> alter system set dg_broker_config_file1='+DATA/rac11g2/dgbroker/dr1rac11g2.dat' scope=both sid='*'; System altered. SQL> alter system set dg_broker_config_file2='+DATA/rac11g2/dgbroker/dr2rac11g2.dat' scope=both sid='*'; System altered.On standby
SQL> alter system set dg_broker_config_file1='+DATA/rac11g2s/dgbroker/dr1rac11g2s.dat' scope=both sid='*'; System altered. SQL> alter system set dg_broker_config_file2='+DATA/rac11g2s/dgbroker/dr2rac11g2s.dat' scope=both sid='*'; System altered.3. Enterprise Manager will automatically set the next required initialization parameter DG_BROKER_START to TRUE for new standby databases that it creates. But as in this case when using DGMGRL, this must be explicitly set to true otherwise dataguard broker daemon will not start. Both on primary and standby set
SQL> alter system set dg_broker_start=true scope=both sid='*'; System altered.When this gets executed following message will appear on the alert log indicating the start of data guard broker daemon
Thu Feb 24 11:04:54 2011 DMON started with pid=56, OS id=16310 Starting Data Guard Broker (DMON) Thu Feb 24 11:05:08 2011 INSV started with pid=60, OS id=163274. Next step is to create the data guard broker configuration. This must be created on the primary running on standby would give the following error
DGMGRL> create configuration rac11g2_dgb as primary database is rac11g2 connect identifier is rac11g2; Error: ORA-16584: operation cannot be performed on a standby database Failed.5. On primary run dgmgrl command line tool to create the configuration
$ dgmgrl DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys Password: Connected. DGMGRL> create configuration rac11g2_dgb as primary database is rac11g2 connect identifier is rac11g2; Configuration "rac11g2_dgb" created with primary database "rac11g2" DGMGRL> show configuration Configuration - rac11g2_dgb Protection Mode: MaxPerformance Databases: rac11g2 - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLEDSyntax to add the physical standby could be of many forms. In 11gR1 documentation "maintained as physical" was used to explicity indicate the database added is a standby. But in 11gR2 this could be ommited and configuration recognize the database role and configure it accordingly. Both commands are shown below
DGMGRL> add database rac11g2s as connect identifier is rac11g2s;
Database "rac11g2s" added
DGMGRL> show configuration
Configuration - rac11g2_dgb
Protection Mode: MaxPerformance
Databases:
rac11g2 - Primary database
rac11g2s - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLEDTo remove a database from the configuration use DGMGRL> remove database rac11g2s; Removed database "rac11g2s" from the configurationThe 11gR1 syntax
DGMGRL> add database rac11g2s as connect identifier is rac11g2s maintained as physical; Database "rac11g2s" added DGMGRL> show configuration Configuration - rac11g2_dgb Protection Mode: MaxPerformance Databases: rac11g2 - Primary database rac11g2s - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED6. Even though configuration is created it is disabled. As the final step enable the configuration
DGMGRL> enable configuration; Enabled.Following could be observed on the primary alert log
Thu Feb 24 11:12:53 2011 RSM0 started with pid=61, OS id=16735 ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='rac11g21'; ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='rac11g21'; 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=10 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*'; ALTER SYSTEM SET db_file_name_convert='+DATA/rac11g2s','+DATA/rac11g2' SCOPE=SPFILE; ALTER SYSTEM SET log_file_name_convert='+FLASH/rac11g2s','+FLASH/rac11g2','+DATA/rac11g2s','+DATA/rac11g2' SCOPE=SPFILE; ALTER SYSTEM ARCHIVE LOG Thu Feb 24 11:13:01 2011 Thread 1 advanced to log sequence 125 (LGWR switch) Current log# 1 seq# 125 mem# 0: +DATA/rac11g2/onlinelog/group_1.261.740770161 Current log# 1 seq# 125 mem# 1: +FLASH/rac11g2/onlinelog/group_1.257.740770161 Thu Feb 24 11:13:03 2011 LNS: Standby redo logfile selected for thread 1 sequence 125 for destination LOG_ARCHIVE_DEST_1 Thu Feb 24 11:13:03 2011 Archived Log entry 353 added for thread 1 sequence 124 ID 0x16d045c1 dest 10:and on standby alert log
Thu Feb 24 12:13:50 2011 RSM0 started with pid=54, OS id=19480 ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='rac11g2s1'; ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='rac11g2s1'; 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=5 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*'; ALTER SYSTEM SET db_file_name_convert='+DATA/rac11g2','+DATA/rac11g2s' SCOPE=SPFILE; ALTER SYSTEM SET log_file_name_convert='+FLASH/rac11g2','+FLASH/rac11g2s','+DATA/rac11g2','+DATA/rac11g2s' SCOPE=SPFILE; ALTER SYSTEM SET fal_server='rac11g2' SCOPE=BOTH; Thu Feb 24 12:14:00 2011 RFS[2]: Selected log 5 for thread 1 sequence 125 dbid 379668842 branch 740770160 Thu Feb 24 12:14:00 2011 Archived Log entry 189 added for thread 1 sequence 124 ID 0x16d045c1 dest 10: Thu Feb 24 12:14:01 2011 RFS[5]: Selected log 8 for thread 2 sequence 117 dbid 379668842 branch 740770160 Thu Feb 24 12:14:01 2011 Archived Log entry 190 added for thread 2 sequence 116 ID 0x16d045c1 dest 10:7. Check the configuration status with
DGMGRL> show configuration Configuration - rac11g2_dgb Protection Mode: MaxPerformance Databases: rac11g2 - Primary database rac11g2s - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESSIf the status is not success then resolve any issues. Sometimes it is possible to have a warning status
DGMGRL> show configuration Configuration - rac11g2_dgb Protection Mode: MaxPerformance Databases: rac11g2 - Primary database Warning: ORA-16792: configurable property value is inconsistent with database setting rac11g2s - Physical standby database Fast-Start Failover: DISABLED Configuration Status: WARNINGLooking at the error number
oerr ora 16792 16792, 0000, "configurable property value is inconsistent with database setting" // *Cause: The values of one or more configurable properties were // inconsistent with database in-memory settings or server parameter // file settings. This may happen by directly altering initialization // parameters instead of editing configurable property values using // Data Guard broker. // *Action: Query the InconsistentProperties property on the database or check // the Data Guard broker log to find which properties are set // inconsistently. Reset these properties to make them consistent // with the database settings. Alternatively, enable the database // or the entire configuration to allow the configurable property // settings to be propagated to the initialization parameters.it was possible to bring all the instance into a consistent state with a database restart
srvctl stop database -d rac11g2 srvctl start database -d rac11g2 gmgrl DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys Password: Connected. DGMGRL> show configuration Configuration - rac11g2_dgb Protection Mode: MaxPerformance Databases: rac11g2 - Primary database rac11g2s - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS8. Check the status of each database in the data guard broker configuration
DGMGRL> show database verbose rac11g2 Database - rac11g2 Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): rac11g21 rac11g22 Properties: DGConnectIdentifier = 'rac11g2' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '10' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '+DATA/rac11g2s, +DATA/rac11g2' LogFileNameConvert = '+FLASH/rac11g2s, +FLASH/rac11g2, +DATA/rac11g2s, +DATA/rac11g2' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESS DGMGRL> show database verbose rac11g2s Database - rac11g2s Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds Apply Lag: 0 seconds Real Time Query: OFF Instance(s): rac11g2s1 (apply instance) rac11g2s2 Properties: DGConnectIdentifier = 'rac11g2s' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'MANDATORY' MaxFailure = '10' MaxConnections = '5' ReopenSecs = '180' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '5' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '+DATA/rac11g2, +DATA/rac11g2s' LogFileNameConvert = '+FLASH/rac11g2, +FLASH/rac11g2s, +DATA/rac11g2, +DATA/rac11g2s' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' SidName(*) StaticConnectIdentifier(*) StandbyArchiveLocation(*) AlternateLocation(*) LogArchiveTrace(*) LogArchiveFormat(*) TopWaitEvents(*) (*) - Please check specific instance for the property value Database Status: SUCCESSFrom 11gR2 data guard broker documentation "If the standby database is an Oracle RAC database, the broker starts Redo Apply on exactly one standby instance, called the apply instance. If this instance fails, the broker automatically chooses another instance that is either mounted or open read-only. This new instance then becomes the apply instance."
Switchover Using Data Guard Broker
To test all the configuration parameters are setup properly a switchover will be carried out using the data guard broker. Data guard broker will take care of all the steps that were carried out manually during earlier switchover scenario using sql commands.
To switchover using data guard broker all that is required is issue the command "switchover to" followed by the database name to switchover to.
DGMGRL> switchover to rac11g2s Performing switchover NOW, please wait... New primary database "rac11g2s" is opening... Operation requires shutdown of instance "rac11g21" on database "rac11g2" Shutting down instance "rac11g21"... ORACLE instance shut down. Operation requires startup of instance "rac11g21" on database "rac11g2" Starting instance "rac11g21"... ORACLE instance started. Database mounted. Switchover succeeded, new primary is "rac11g2s". At the time issuing this there were two primary instances (rac11g21 and rac11g22) in open read,write mode and two standby instance (rac11g2s1 and rac11g2s2) in mount mode.
The command was issued from rac11g21 instance and as soon as it was issued rac11g22 was shutdown by the data guard broker(with shutdown abort) as seen from rac11g22 alert log
Thu Feb 24 15:35:50 2011 Shutting down instance (abort) License high water mark = 5 USER (ospid: 22346): terminating the instance Instance terminated by USER, pid = 22346 Thu Feb 24 15:35:50 2011 Instance shutdown completerac11g21 disconnect all the sessions and commit to switchover with session shutdown as seen from rac11g21 alert log (only partial information is shown here, not all the entries generated at the time of switchover)
Thu Feb 24 15:35:53 2011 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 26886] (rac11g21) Thu Feb 24 15:35:53 2011 Thread 1 advanced to log sequence 184 (LGWR switch) Current log# 1 seq# 184 mem# 0: +DATA/rac11g2/onlinelog/group_1.261.740770161 Current log# 1 seq# 184 mem# 1: +FLASH/rac11g2/onlinelog/group_1.257.740770161 Thu Feb 24 15:35:53 2011 Stopping background process CJQ0 Stopping background process QMNC Thu Feb 24 15:35:54 2011 Archived Log entry 541 added for thread 2 sequence 173 ID 0x16d1ac74 dest 10: All dispatchers and shared servers shutdown CLOSE: killing server sessions. Active process 32445 user 'oracle' program 'oracle@rac4.domain.net (W000)' Active process 32445 user 'oracle' program 'oracle@rac4.domain.net (W000)' Active process 437 user 'oracle' program 'oracle@rac4.domain.net (TNS V1-V3)' Active process 32445 user 'oracle' program 'oracle@rac4.domain.net (W000)' .. .. Switchover: Complete - Database shutdown required Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWNOnce the shutdown is complete rac11g21 is started as the new standby and log apply process is started
ALTER SYSTEM SET fal_server='rac11g2s' SCOPE=BOTH; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Attempt to start background Managed Standby Recovery process (rac11g21) Thu Feb 24 15:36:29 2011 MRP0 started with pid=64, OS id=719 MRP0: Background Managed Standby Recovery process started (rac11g21) started logmerger process Thu Feb 24 15:36:34 2011 Managed Standby Recovery starting Real Time Apply Parallel Media Recovery started with 2 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Clearing online redo logfile 1 +DATA/rac11g2/onlinelog/group_1.261.740770161 Clearing online log 1 of thread 1 sequence number 189 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Clearing online redo logfile 1 completeThe other instance in this new standby (old primary) is also stated in the mount mode.
While the above is going on there's activity on the old standby (rac11g2s, new primary) as well. As soon as the switchover command is issued the apply instance (rac11g2s1) at standby disconnect from the primary and stop the log apply process
Thu Feb 24 16:36:47 2011
RFS[11]: Possible network disconnect with primary database
Thu Feb 24 16:36:47 2011
RFS[9]: Possible network disconnect with primary database
Thu Feb 24 16:36:47 2011
RFS[7]: Possible network disconnect with primary database
Thu Feb 24 16:36:47 2011
RFS[8]: Possible network disconnect with primary database
Thu Feb 24 16:36:47 2011
RFS[14]: Assigned to RFS process 29873
RFS[14]: Possible network disconnect with primary database
..
..
Resetting standby activation ID 382839924 (0x16d1ac74)
Thu Feb 24 16:36:56 2011
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Media Recovery Waiting for thread 1 sequence 185
MRP0: Background Media Recovery cancelled with status 16037Once this completes it switchover to the primary database role and open the databaseMRP0: Background Media Recovery process shutdown (rac11g2s1)
Managed Standby Recovery Canceled (rac11g2s1)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (rac11g2s1)
Maximum wait for role transition is 15 minutes.As soon as the switchover is complete other instances in the (old)standby are also brought up in open mode and the new primary is available for use. Following from the rac11g2s2 alert logThu Feb 24 16:36:56 2011 Managed Standby Recovery not using Real Time Apply Switchover: Complete - Database mounted as primary Thu Feb 24 16:37:02 2011 ALTER DATABASE OPEN Data Guard Broker initializing... Picked broadcast on commit scheme to generate SCNsChecking the new configuration
DGMGRL> show configuration Configuration - rac11g2_dgb Protection Mode: MaxPerformance Databases: rac11g2s - Primary database rac11g2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database verbose rac11g2 Database - rac11g2 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds Apply Lag: 0 seconds Real Time Query: OFF Instance(s): rac11g21 (apply instance) rac11g22 .. ..Unlike 11gR1 configuration 11gR2 does use the VIPs for StaticConnectIdentifier.
