Physical Standby Switchover Best Practices for 18c & 19c

In this article, we are going to show the best practices of physical standby databases 18c & 19c switchover using SQL*PLUS.

Step 1: Prerequisites check before performing switchover activities

a. Setup/configuration verification

- Primary & Standby should be running with the same version of RDBMS 

- Verify the alert log files and make sure there are no errors 

- Run select on v$database_block_corruption & v$nonlogged_block from primary and standby and make sure there are no corruptions 

- Make sure primary and physical standby configurations are good and there are no errors in redo transport and redo apply

You can also optionally use the below queries to check the redo transport and apply status.

On Primary Database:

SQL> col DEST_NAME for a20 

SQL> col DESTINATION for a25 

SQL> col ERROR for a15 

SQL> col ALTERNATE for a20 

SQL> set lines 1000 

SQL> select DEST_NAME, DESTINATION, ERROR, ALTERNATE, TYPE, status, VALID_TYPE, VALID_ROLE from V$ARCHIVE_DEST where STATUS <>'INACTIVE';

Check the last archivelog created at the primary.

SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from gv$archived_log val, gv$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

On Standby Database:

Using the below query, check the last received archivelog from primary database (RAC database result will be displayed for each thread).

SQL> select thread#, max(sequence#) "Last Standby Seq Received" from gv$archived_log val, gv$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

Check the last archive log sequence applied at standby.

SQL> select thread#, max(sequence#) "Last Standby Seq Applied" from gv$archived_log val, gv$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

b. Verify Initialization Parameters

The below parameter should have been configured correctly. 

log_archive_config: should include primary and standby database (if multiple standby databases are existing, then all the standby database details should be included) 

fal_server: remote server from where archivelog can be fetched 

db_unique_name: unique name under this configuration

log_archive_dest_n: for remote database to send archives

Note: Ensure the 'compatible' parameter is set to the same value at primary and standby.

Step 2: Pre-Switchover Check

Ensure Prerequisites are completely verified and these steps should be executed before the real planned outage starts and ensure no issues.

a. Verify Redo/Archive log apply is good and that there are no gaps

On Standby Database:

Run the below query in physical standby to check the last archive log sequence received and applied from all the threads. This will not include a current sequence of the primary database as the SQL is extracting details from v$archived_log.

SQL> select thread#, max(sequence#) "Last Standby Seq Applied"

from gv$archived_log val, gv$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

and val.applied in ('YES','IN-MEMORY')

group by thread# order by 1; 

Check the MRP(Managed Recovery Process) process status (it should be started, running, and applying the logs). 

SQL> select * from gv$dataguard_process; 

Commands to stop & start the managed recovery process.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Note: If standby database recovery (MRP) started with delay OR if the standby is always maintained with lag, then switchover will consume time to apply the logs to be synchronized.

Before switchover, try to maintain minimal archive log apply lag, which will reduce the total switchover time window

b. Check the datafiles & Tempfiles status

Expected all the datafiles should be online in primary and standby, in case if there are files in offline (OR) NOT in online status, then restore the file and recover so standby database datafiles are same as primary database datafiles.

On Primary & Standby Database:

Check the file status and use the below command to make files online.

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS=’OFFLINE’;

SQL> ALTER DATABASE DATAFILE 'datafile-name' ONLINE;

For tempfiles:

SQL> select tf.name filename, bytes, ts.name tablespace from v$tempfile tf, v$tablespace ts where tf.ts#=ts.ts#;

c. Online and standby redo logfile configuration

Check the online redo and standby redo logs in the primary and standby database using the below queries.

SQL> set linesize 1000

SQL> set pagesize 2000

SQL> select group#, status, type, member from v$logfile;

Note: Make sure the below rules are followed by creating standby redo logs 

i. The number of Standby redo logs will be 1 more than the number of online redo logs on the primary & standby. 

ii. The size of the standby redo log should be the same as that of the online redo log on primary & standby.

Manually recreate online and standby redo log if miss the above rule in primary or standby database.

Online redo logfile (ORL):

SQL> set lines 150 

SQL> col member for a50 

SQL> select a.thread#, a.group#, a.bytes, a.blocksize, b.type, a.status, b.member from v$log a,v$logfile b where a.group#=b.group#;

Note: At primary when the above command executed, we may get a.status in (INACTIVE,ACTIVE,CURRENT). Expected a.status from Standby is UNUSED, CLEARING or CLEARING_CURRENT, if output has different result, then manually clear redo logfiles.

Command to clear ORL group: 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP <ORL Group#>; 

If ORL or SRL needs to be cleared in the standby, Managed recovery process has to be stopped.

Note: During switchover ORLs will be cleared if ORLs were not cleared previously, but switchover will be consuming time to complete. Switchover session will wait for 15 min to complete otherwise timeout will occur. if the switchover is terminated due to timeout, then retry again until switchover is successful.

For Standby redo logfile(SRL):

SQL> select s.thread#,s.group#,s.status,s.bytes,l.type,l.member from v$logfile l,v$standby_log s where s.group#=l.group#;

Standby redo logfile status would be in UNASSIGNED OR ACTIVE.

d. Checking the alert logfiles

Primary database alert log:

* Check for issue reported for redo transport 

* Ensure there is no password file issue 

* Ensure there are no TNS or connection issue

Standby database alert log:

There are no error related to Managed recovery 

* Recovery is moving forward by applying the archive log / redo log 

* There are no TNS or connection issue 

* There are no I/O issue or corruption issue 

* select * from v$database_block_corruption; -- it should return no rows 

* select * from v$nonlogged_block; -- it should return no rows

e. Check Archive log GAP & Redo Delay apply

You must configure the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters for each standby database. When a switchover or failover occurs, all standby databases continues to receive redo logs / Archivelogs from the new primary database.

 On Primary Database:

Considering log_archive_dest_2 is configured for the redo shipping.

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

Note: STATUS should be Valid GAP_STATUS should be NO GAP. If different result is reported, then switchover should NOT be tried.

Step 3: Switchover Activities

a. Verify the switchover

On Primary Database:

If the below command executes successfully, then "Database Altered" message should be returned (Execute the below SQL in the primary).

SQL> ALTER DATABASE SWITCHOVER TO <standby db_name> VERIFY;

In case of an error, fix an issue and then rerun switchover verify command.

b. Switchover steps

If the switchover verify is successful, then execute the command to switchover the database.

1) Execute in the current primary 

SQL> ALTER DATABASE SWITCHOVER TO <standby db_name>; 

if  step 1 is successful, then follow step 2 to open the new primary database in open mode. 

2) Execute in the new primary database 

SQL> ALTER DATABASE OPEN; 

3) Old primary (current/new standby) should be mounted OR opened depends on the case If standby is Oracle Active data guard physical standby: 

SQL> STARTUP; 

If the standby is NOT Oracle Active data guard physical standby: 

SQL> STARTUP MOUNT; 

4) Start redo apply in the new standby 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Step 4: Post-Switchover Activities

In primary:

In primary:

Check, if the archive logs are being transferred to the standby and getting applied?

SQL> alter system archive log current; 

SQL>select dest_id,error,status from v$archive_dest where dest_id=>; 

SQL>select max(sequence#),thread# from v$log_history group by thread#;

Incase remote archivelog destination 2 is set in primary (log_archive_dest_2 ), we can use the below   query to check the maximum sequence transferred and applied in the standby.

SQL>select max(sequence#) from v$archived_log where applied='YES' and dest_id=2;

In standby:

Verify the archivelog availability and the application of the archive log files.

SQL>select max(sequence#),thread# from v$archived_log group by thread#; 

SQL> select name,role,instance,thread#,sequence#,action from gv$dataguard_process;

Additionally, Alert logfiles can be viewes to confirm the archivelog transfer and archivelog apply in standby.