1. On Primary:
a. Verify there is no log file gap between the primary and the standby database
SELECT status, gap_status
FROM v$archive_dest_status
WHERE dest_id = 2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
b. Verify that the primary can be switched to the standby role.
SET lines 4000
SELECT name, log_mode, controlfile_type, open_mode, database_role, switchover_status
FROM v$database;
NAME LOG_MODE CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------ ------- -------------------- ---------------- --------------------
ORADB ARCHIVELOG CURRENT READ WRITE PRIMARY TO STANDBY
c. Check Temp files match count in Primary and Standby
COL name FOR a45
SELECT ts#, name, status
FROM v$tempfile;
col name for a45
select ts#,name,ts#,status from v$tempfile;
TS# NAME TS# STATUS
---------- --------------------------------------------- ---------- -------
3 /u01/app/oracle/oradata/ORADB/temp01.dbf 3 ONLINE
3 /u01/app/oracle/oradata/ORADB/pdbseed/temp012 3 ONLINE
020-09-03_00-49-51-498-AM.dbf
3 /u01/app/oracle/oradata/ORADB/pdb1/temp01.dbf 3 ONLINE
d. Clear Potential Blocking Parameters & Jobs
SHOW PARAMETER job_queue_processes
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;
show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 40
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;
System altered.
COL owner FOR a30
COL job_name FOR a40
COL start_date FOR a30
COL end_date FOR a30
SELECT owner, job_name, start_date, end_date, enabled
FROM dba_scheduler_jobs
WHERE enabled='TRUE' AND owner NOT IN 'SYS'
OWNER JOB_NAME START_DATE END_DATE ENABL
-------------------- -------------------------------- ------------------------------ ------------------------------ -----
ORACLE_OCM MGMT_CONFIG_JOB 04-SEP-20 12.53.14.000000 AM + TRUE
04:00
ORACLE_OCM MGMT_STATS_CONFIG_JOB 04-SEP-20 12.53.14.000000 AM + TRUE
04:00
EXECUTE DBMS_SCHEDULER.DISABLE('ORACLE_OCM.MGMT_CONFIG_JOB');
PL/SQL procedure successfully completed.
EXECUTE DBMS_SCHEDULER.DISABLE('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
PL/SQL procedure successfully completed.
e. Create Guaranteed Restore Point on Primary and Standby and make sure they are dropped post successful switchover
SHOW PARAMETER db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fra
db_recovery_file_dest_size big integer 22732M
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 22732M
SQL> alter system set db_recovery_file_dest_size=5G;
System altered.
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 5G
SQL> create restore point switchover_restore_point guarantee flashback database;
Restore point created.
select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;
NAME TIME GUA STORAGE_SIZE/1024/1024/1024
------------------------------ ---------------------------------------- --- ---------------------------
SWITCHOVER_RESTORE_POINT 08-FEB-24 10.04.30.000000000 AM YES .1953125
2. On Primary:
a. Switch the Primary to the Standby Role:
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.
b. Verify the standby has received the end-of-redo (EOR) log(s) in standby alert log
Alert log sample message :
PR00 (PID:5275): Resetting standby activation ID 2803353007 (0xa717c5af)
2020-09-04T01:32:44.494716+04:00
Media Recovery End-Of-Redo indicator encountered
2020-09-04T01:32:44.494784+04:00
Media Recovery Continuing
PR00 (PID:5275): Media Recovery Waiting for T-1.S-32
2020-09-04T01:32:45.535769+04:00
.... (PID:5531): The Time Management Interface (TMI) is being enabled for role transition
.... (PID:5531): information. This will result in messages beingoutput to the alert log
.... (PID:5531): file with the prefix 'TMI: '. This is being enabled to make the timing of
.... (PID:5531): the various stages of the role transition available for diagnostic purposes.
.... (PID:5531): This output will end when the role transition is complete.
SWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.
2020-09-04T01:32:45.536371+04:00
ALTER DATABASE SWITCHOVER TO PRIMARY (oradb_s2)
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2020-09-04 01:32:45.536801
Switchover: Media recovery is still active
rmi (PID:5531): Role Change: Canceling MRP - no more redo to apply
2020-09-04T01:32:45.538289+04:00
PR00 (PID:5275): MRP0: Background Media Recovery cancelled with status 16037
c. Shutdown and restart the database in standby mode
[oracle@srv1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 8 10:10:15 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1073738888 bytes
Fixed Size 9143432 bytes
Variable Size 276824064 bytes
Database Buffers 780140544 bytes
Redo Buffers 7630848 bytes
SQL> alter database mount standby database
2 ;
Database altered.
3. On Standby:
a. Check the MRP process state
Select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 86
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 94
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 95
b. Switch the Standby to Primary
SQL> Alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> Alter database open;
Database altered.
SQL> Select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORADB READ WRITE PRIMARY
4. Start Redo Apply on the new physical standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
5. Enable log shipping on new primary
ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=BOTH;
System altered.
6. Set job_queue_processes parameter on new standby database
SHOW PARAMETER job_queue_processes
ALTER SYSTEM SET job_queue_processes=40 SCOPE=BOTH;
7. Start secondary new primary instance
In case of standalone database no action needed, as we already have database open. Start all the instances in RAC environment which were stopped before start of the activity.srvctl status database -d prod -v
srvctl start instance -d prod -i prod2
srvctl status database -d prod -v
8. Start secondary new standby instance
In case of standalone database no action needed, as we already have database open in mount mode. Start all the instances in RAC environment which were stopped before start of the activity.srvctl status database -d stby -v
srvctl start instance -d stby -i stby2
srvctl status database -d stby -v
9. Schedule backups on new primary and create DB links if they exist on primary new
START all cronjobs and DBMS_SCHEDULER jobs
EXECUTE DBMS_SCHEDULER.ENABLE('ORACLE_OCM.MGMT_CONFIG_JOB');
PL/SQL procedure successfully completed.
EXECUTE DBMS_SCHEDULER.ENABLE('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
PL/SQL procedure successfully completed.
SQL> SELECT OWNER, JOB_NAME, START_DATE, END_DATE, ENABLED FROM DBA_SCHEDULER_JOBS WHERE ENABLED='TRUE' AND OWNER NOT IN 'SYS';
OWNER JOB_NAME START_DATE END_DATE ENABL
---------- ------------------------------ ---------------------------------------- ------------------------------ -----
ORACLE_OCM MGMT_CONFIG_JOB 04-SEP-20 12.53.14.000000 AM +04:00 TRUE
ORACLE_OCM MGMT_STATS_CONFIG_JOB 04-SEP-20 12.53.14.000000 AM +04:00 TRUE
Note: Post switchover, ensure sufficient Temporary tablespace is available on the new primary. If not, add it.
On New Standby:
10. Drop the restore point created earlier
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;
NAME
--------------------------------------------------------------------------------
TIME GUA
--------------------------------------------------------------------------- ---
STORAGE_SIZE/1024/1024/1024
---------------------------
SWITCHOVER_RESTORE_POINT
08-FEB-24 10.04.30.000000000 AM YES
.1953125
SQL> DROP RESTORE POINT switchover_restore_point;
Restore point dropped.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT from session parallel 6;
Database altered.
