12c Dataguard Switchover Best Practices using DGMGRL-摸

DG Broker will not downgrade the PROTECTION mode during the process of Switchover.
Configure suitable REDO transport mode on current standby that becomes Primary after switchover.
Configure SRL (Standby Redo Logs) and local archiving destination on current Primary.

Broker switchover command verifies the availability of SRL on current Primary Database and suitable Redo transport method on current Standby.

DB Restart Considerations:

If the switchover target is Physical Standby then Broker shutdown the Primary as a process of switchover.
If the switchover environment in MAX_PROTECTION mode and primary has only one Standby then broker shuts down both primary and standby as a process of switchover. If primary has multiple standby then broker shuts down only the new Primary.

I. Pre-Switchover Checks

Verify Dataguard Broker Configuration

Use following command s to verify broker status before switchover.

											DGMGRL>show configuration;
										
											DGMGRL>show database ;
										
											DGMGRL>show database ;
										
											DGMGRL>show database verbose ;
										
											DGMGRL>show database verbose ;

For example,

Dg Configuration name =12c

Primary db_unique_name = boston

Standby db_unique_name =chicago

											DGMGRL> show configuration;
										
											Configuration - 12c
										
											  Protection Mode: MaxPerformance
										
											  Databases:
										
											  boston  - Primary database
										
											    chicago - Physical standby database
										
											Fast-Start Failover: DISABLED
										
											Configuration Status:
										
											SUCCESS
										
											DGMGRL> show database boston;
										
											Database - boston
										
											  Role:              PRIMARY
										
											  Intended State:    TRANSPORT-ON
										
											  Instance(s):
										
											    boston
										
											Database Status:
										
											SUCCESS
										
											DGMGRL> show database chicago
										
											Database - chicago
										
											  Role:              PHYSICAL STANDBY
										
											  Intended State:    APPLY-ON
										
											  Transport Lag:     0 seconds (computed 1 second ago)
										
											  Apply Lag:         0 seconds (computed 1 second ago)
										
											  Apply Rate:        0 Byte/s
										
											  Real Time Query:   ON
										
											  Instance(s):
										
											    chicago
										
											Database Status:
										
											SUCCESS

NOTE : Further troubleshoot use,

DGMRL>show database ‘<>’ logxptstatus;

DGMGRL>show database ‘<> ‘inconsistentlogxptprops’;

Validate Database

Validate database verify following, no need to explicitly check whether ORLs/SRLS cleared.

Whether there is missing redo data on a standby database
Whether flashback is enabled
The number of temporary tablespace files configured
Whether an online data file move is in progress
Whether online redo logs are cleared for a physical standby database
Whether standby redo logs are cleared for a primary database
The online log file configuration
The standby log file configuration
Apply-related property settings
Transport-related property settings

											DGMGRL> validate database boston
										
											  Database Role:    Primary database
										
											  Ready for Switchover:  Yes    <-----------Redy for Switchover

											  Flashback Database Status:
										
											    boston:  Off
										
											DGMGRL> validate database chicago
										
											  Database Role:     Physical standby database
										
											  Primary Database:  boston
										
											  Ready for Switchover:  Yes    <--------This confirms Switchover readiness

											  Ready for Failover:    Yes (Primary Running)
										
											  Flashback Database Status:
										
											    boston:   Off
										
											    chicago:  Off
										
											  Future Log File Groups Configuration:
										
											    Thread #  Online Redo Log Groups   Standby Redo Log Groups
										
											              (chicago)                (boston)
										
											    1         4                        4

NOTE : For further troubleshoot use validate database verbose.

DGMGRL>validate database verbose boston

DGMGRL>validate database verbose chicago

Preset Redo Transport related parameter

Verify below parameters are set based on PROTECTION MODE.

LogXptMode, NetTimeout, StandbyArchiveLocation, AlternateLocation, and RedoRoutes.

II SWITCHOVER

Disable Apply Delay

To increase the speed of switchover disable delaymins property.

DGMGRL>edit database chicago set property delaymins=0;

Turn on Data Guard tracing on primary and standby

											DGMGRL> edit configuration set property tracelevel=support;
										
											DGMGRL> edit database boston set property LogArchiveTrace=8191;
										
											DGMGRL> edit database chicago set property LogArchiveTrace=8191;

Tail Alert Logs and DRC (optional) on all instances

Tail alert log and DRC log which is present in same location of alert log(text) from all the nodes of primary and standby.

											tail –f 
										
											tail –f 
										
											tail –f  log of primary>
										
											tail –f  log of standby>

Perform Switchover

Connect to sysdg

											DGMGRL> connect sysdg
										
											Password:
										
											Connected as SYSDG.
										
											DGMGRL> switchover to chicago
										
											Performing switchover NOW, please wait...
										
											Operation requires a connection to instance "chicago" on database "chicago"
										
											Connecting to instance "chicago"...
										
											Connected as SYSDBA.
										
											New primary database "chicago" is opening...
										
											Operation requires startup of instance "boston" on database "boston"
										
											Starting instance "boston"...
										
											ORACLE instance started.
										
											Database mounted.
										
											Database opened.
										
											Switchover succeeded, new primary is "chicago"

--------------Primary Alert log- alert_boston.log--------------

ALTER DATABASE SWITCHOVER TO 'chicago'

Starting switchover [Process ID: 10402]

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 10402] (boston)

Switchover End-Of-Redo Log thread 1 sequence 131 has been fixed

Switchover: Primary highest seen SCN set to 0x0.0x310a3a

ARCH: Noswitch archival of thread 1, sequence 131

ARCH: End-Of-Redo Branch archival of thread 1 sequence 131

ARCH: Evaluating archive log 1 thread 1 sequence 131

Switchover complete. Database shutdown required

Completed: ALTER DATABASE SWITCHOVER TO 'chicago'

--------------Standby Alert log - alert_chicago.log------------

SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.

ALTER DATABASE SWITCHOVER TO PRIMARY (chicago)

Standby became primary SCN: 3213880 <----------Record this SCN

Switchover: Complete - Database mounted as primary

SWITCHOVER: completed request from primary database.

----------------Primary Drc log -"/u01/app/oracle/diag/rdbms/boston/boston/trace/drcboston.log"-----------

SWITCHOVER TO chicago

Broker Configuration: "12c"

Protection Mode: Maximum Performance

Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0

Primary Database: chicago (0x02010000) <-------Chicago as Primary

---------------Standby DRC log - "/u01/app/oracle/diag/rdbms/chicago/chicago/trace/drcchicago.log"--------------

Notifying Oracle Clusterware to prepare target standby database for switchover

Notifying DMON of db close

Notifying RSM0 of db close

Deferring associated archivelog destinations of sites permanently disabled due to Switchover

Notifying Oracle Clusterware to buildup primary database after switchover

III. Post-Switchover Steps

Reset Delaymins property

Change the delaymins property to prior on new standby after switchover based on business requirement.

										DGMGRL> edit database   set property delaymins=;
									

Set Trace to Prior Value

											DGMGRL> edit configuration reset property tracelevel ;
										
											DGMGRL> edit database boston reset property logarchivetrace;
										
											DGMGRL> edit database chicago reset property logarchivetrace;

Verify Broker Configuration

											DGMGRL> show configuration;
										
											Configuration - 12c
										
											  Protection Mode: MaxPerformance
										
											  Databases:
										
											  chicago - Primary database
										
											  boston  - Physical standby database <-----Boston is Physical Standby

											Fast-Start Failover: DISABLED
										
											Configuration Status:
										
											SUCCESS