By Franck Pachot
In 12c the manual switchover to physical standby requires only one command (ALTER DATABASE SWITCHOVER) instead of two (ALTER DATABASE COMMIT TO SWITCHOVER on each database). We usually recommend to use the broker and I wanted to see if the broker uses that new command or not. Let’s compare the alert.log for both manual and broker switchover.
One simple command
I have the broker configured, but try a manual switchover with that simple command:
SQL> alter database switchover to demo12; Database altered.
then let’s look at he alert.log on primary and standby. I put only the minimum I want to show here.
alert.log on primary
Here is the initialisation (I’ve run the ALTER DATABASE SWITCHOVER on the primary):
Wed Oct 21 22:57:10 2015 alter database switchover to demo12 Wed Oct 21 22:57:10 2015 Starting switchover [Process ID: 24349] Wed Oct 21 22:57:10 2015 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 24349] (DEMO11) Waiting for target standby to receive all redo
then after a few seconds:
Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required Sending request(convert to primary database) to switchover target DEMO12 Switchover complete. Database shutdown required USER (ospid: 24349): terminating the instance Wed Oct 21 22:57:17 2015 Instance terminated by USER, pid = 24349 Completed: alter database switchover to demo12 Shutting down instance (abort) License high water mark = 9 Wed Oct 21 22:57:17 2015 Instance shutdown complete
The minimum is done there on the primary: role is changed to STANDBY and instance is terminated (shutdown abort)
alert.log on standby
Nothing to run on the second database, the switchover request has been sent to it:
Wed Oct 21 22:57:13 2015 SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database. Wed Oct 21 22:57:13 2015 ALTER DATABASE SWITCHOVER TO PRIMARY (DEMO12)
(the typo in DTABASE is not mine)
and the role is changed to PRIMARY:
CLOSE: killing server sessions. CLOSE: all sessions shutdown successfully. SwitchOver after complete recovery through change 6571015 Standby became primary SCN: 6571013 Switchover: Complete - Database mounted as primary SWITCHOVER: completed request from primary database.
At that point, the primary is available, but it is not protected anymore: the new standby is down and the log shipping is not configured.
But if you used the broker
So I did a manual switchover but I had a broker configuration, so it is totally messed up. Let’s check it.
[[email protected] trace]$ dgmgrl sys/oracle DGMGRL for Linux: Version 220.127.116.11.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show configuration ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 Configuration details cannot be determined by DGMGRL
Yes, of course, the new standby has to be restarted. In 12c I can do it from dgmgrl:
DGMGRL> startup ORACLE instance started. Database mounted. Database opened. DGMGRL>
then check the configuration again:
DGMGRL> show configuration Configuration - demo11 Protection Mode: MaxPerformance Members: demo11 - Primary database demo12 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 12 seconds ago)
Humm… SUCESS, but totally messed-up. The broker still think that DEMO11 is the primary and DEMO12 the standby…
DGMGRL> show database demo12 Database - demo12 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: (unknown) Apply Lag: (unknown) Average Apply Rate: 6.00 KByte/s Real Time Query: OFF Instance(s): DEMO12 Database Error(s): ORA-16816: incorrect database role Database Status: ERROR
When connecting to what is supposed to be the standby, the broker sees it’s actually a primary, this the ‘incorrect role’ error.
Two solutions to that: recreate the broker configuration, or switchover to the other way.
But for the seconds solution, you need to finish some configuration:
SQL> alter database switchover to demo11; alter database switchover to demo11 * ERROR at line 1: ORA-16474: target_db_name not found in the LOG_ARCHIVE_DEST_n parameter
don’t think you can do it with the old syntax:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY * ERROR at line 1: ORA-16416: No viable Physical Standby switchover targets available
You need to set LOG_ARCHIVE_DEST_n or the instance does not know where the current standby is.
what does the broker in addition to that
In order to compare, I did before a switchover with the broker, and I’ll show what differs in the alert logs between broker and manual switchover.
Before the switchover, the broker does some verifications.
On the primary:
SWITCHOVER VERIFY: Send VERIFY request to switchover target demo12 SWITCHOVER VERIFY COMPLETE
On the standby:
SWITCHOVER VERIFY BEGIN SWITCHOVER VERIFY COMPLETE SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.
After the switchover (‘Completed: ALTER DATABASE SWITCHOVER’) the primary is only stopped when switchover is initiated from sqlplus:
Shutting down instance (abort) Instance shutdown complete
but now the broker restarts it :
Data Guard Broker operation requires restart; shutting down instance now Starting ORACLE instance (normal) (OS id: 24088) Physical Standby Database mounted. Completed: alter database mount alter database open Data Guard Broker initializing... Beginning Standby Crash Recovery. Completed Standby Crash Recovery.
and then the properties of the new primary are applied (no log shipping here as it is the standby):
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='DEMO11'; ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='DEMO11';
and it enters recovery mode:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
On the new primary, the broker set all the properties for log shipping: ALTER SYSTEM SET…
What I wanted to show you here is that even if the new 12c syntax is very similar to the dgmgrl command, it does nothing more than before.
Except if you want to make things complex (and who want to do that on a HA configuration?) you should use the broker.
When all the properties are set in the broker, and once you’ve validated it by a switchover test, you can rely on it with no stress.