SQL> alter database switchover to …

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.

[oracle@VM111 trace]$ dgmgrl sys/oracle
DGMGRL for Linux: Version 12.1.0.2.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…

Conclusion

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.