Time for action – preliminary tests before performing switchover



In order to perform switchover, we have to prepare and verify both the primary and standby databases. Perform the following steps:

1 ) Check the standby redo logfile status on the primary database as follows:


SQL> select group#,member,type from v$logfile where type='STANDBY';

    GROUP# MEMBER                                            TYPE
---------- ---------------------------------------------- -------
        ..   ........
     14 /u01/app/oracle/oradata/orcl/standby_redo05.log    STANDBY
     16 /u01/app/oracle/oradata/orcl/standby_redo06.log    STANDBY
6 rows selected.
Standby redo logfiles should have been created on the primary database; this is so that after performing switchover, the new standby database can receive redo using standby redo logfiles. This will help us save time in the post-configuration steps.


2 Verify the log archive destination on the standby database, which will be active after the switchover and will be used to transfer redo to the new standby database as follows:
SQL> show parameter log_archive_dest_2
NAME                 TYPE    VALUE
-------------------- ------  ------------------------------
log_archive_dest_2   string   SERVICE=TURKEY LGWR ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=turkey_un'



Verify if the temporary files of the temporary tablespaces are created on the standby database. Compare the result of the following query from the primary and standby databases.
SQL>  select file_name,bytes/1024/1024 "SizeMB",maxbytes/1024/1024 "MaxSize MB",autoextensible fromdba_temp_files;

FILE_NAME                                Size MB MaxSize MB AUT
---------------------------------------- ------- ---------- ---
/u02/app/oracle/oradata/orcl/temp01.dbf       20 32767.9844 YES




3 If temporary files don't exist on the standby database or the number and size of temporary files don't match in the primary and standby databases, create or modify the temporary files on the standby database properly.

TIP
If you have created a standby database using the RMAN command DUPLICATE in Oracle 11gR2, the temporary files will be created by default.

4 Check if any offline datafiles exist on primary as well as standby. If they do exist, bring them online using the following code:
SQL> select name from v$datafile where status='OFFLINE';




5 Verify the status of the redo transport and apply services against any gap and synchronization issues as follows:
SQL> select db_unique_name, status, protection_mode, synchronization_status, synchronized from v$archive_dest_status where dest_id=2;

DB_UNIQUE_NAME STATUS  PROTECTION_MODE  SYNCHRONIZATION_STATUS SYN
-------------- ------- ----------------- --------------------- ---
INDIA_UN      VALID  MAXIMUM PERFORMANCE CHECK CONFIGURATION   YES




6   In the previous output, you can ignore the synchronization status CHECK CONFIGURATION if the database is in Maximum Performance mode. If the configuration is either Maximum Protection or Availability, the status OK will be returned when there are no synchronization issues. Check the maximum archived log sequences on the primary and standby databases.


From primary – to obtain the maximum number of archived log sequences for each instance, the following code can be used:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            335


From standby – to obtain the maximum number of archived log sequences for each instance, the following code can be used:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
  335



Now verify if the MRP process is running or not by running the following statement on the standby database:

SQL> select thread#,sequence#,process,status,client_process from v$managed_standby where thread#=1;

   THREAD#  SEQUENCE# PROCESS   STATUS       CLIENT_P
---------- ---------- --------- ------------ --------
         1        335 ARCH      CLOSING      ARCH
         1        333 ARCH      CLOSING      ARCH
         1        334 ARCH      CLOSING      ARCH
         1        336 MRP0      APPLYING_LOG N/A
         1        336 RFS       IDLE         LGWR

The current sequence 336 is being written into the standby redo logfiles and the MRP process is applying this sequence at the same time.



It's also possible to query the v$dataguard_stats view on the standby database to check the synchronization status:


SQL> select name,value,time_computed from v$dataguard_stats;

NAME                   VALUE         TIME_COMPUTED                
---------------------- ------------- --------------------
transport lag          +00 00:00:00  10/10/2012 15:07:51  
apply lag              +00 00:00:00  10/10/2012 15:07:51  
apply finish time      +00 00:00:00  10/10/2012 15:07:51
estimated startup time           16  10/10/2012 15:07:51

SQL> !date
Wed Oct 10 15:07:52 IST 2012


Ensure that no backup jobs are running. Disable the RMAN and EXP/EXPDP backup jobs from CRONTAB if they exist.

If the primary and standby databases are monitored with EM Cloud/Grid Control and you're performing switchover using SQL*Plus or Data Guard broker, black out the database until the task is completed.


Set the JOB_QUEUE_PROCESSES parameter value to 0 so that no more jobs will be started. After the completion of switchover, reset it with the previous value.




SQL> alter system set JOB_QUEUE_PROCESSES=0 scope=both sid='*';
If the primary database is RAC, ensure all the remaining primary instances except one are shut down. If Active Data Guard is in use, disable it and ensure that all standby instances are in the mount state.
It's advisable to take a full backup of the database either from primary or standby.