ORACLE RAC DATABASE SWITCHOVER STEPS
Hello Friend's
In this post we will discuss about Switchover and Switchback procedure simplified in Oracle 12c.
Check the below link for steps to create Standby for 2 node rac :
The following is used in the Switchover scenario:
2 instances Oracle 12c RAC primary database (Findb)
Single instance Oracle 12c RAC Standby database (Findr)
Step 1 : Status of database (Primary & Standby)
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN Findb1 PRIMARY
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN Findr PHYSICAL STANDBY1.1) Check the log diff :
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 100 100 0
2 92 92 0If there is some number in Difference column wait for some time let standby sync
1.2) NO lag :Now verify the switchover :
alter database switchover to Findr verify;
SQL> alter database switchover to Findr verify;
alter database switchover to Findr verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details1.3) checking the alert log found that the error was as below :
alter database switchover to Findr verify
Wed May 06 02:55:36 2020
SWITCHOVER VERIFY WARNING: no standby redo logfiles configured. Standby redo logfiles are recommended configuration for physical standby database.
SWITCHOVER VERIFY: Send VERIFY request to switchover target FINDR
SWITCHOVER VERIFY COMPLETE
ORA-16475 signalled during: alter database switchover to Findr verify...1.4) After creating the standby logs recheck the status
--I stopped the redo apply on standby to add the logs so it gave me the error :
Wed May 06 03:23:21 2020
SWITCHOVER VERIFY: Send VERIFY request to switchover target FINDR
SWITCHOVER VERIFY WARNING: switchover target's apply lag is more than 300 seconds. It takes time for the recovery to catch up. This may slow down switchover process.
SWITCHOVER VERIFY COMPLETE
ORA-16475 signalled during: alter database switchover to Findr verify...--Started the recovery lets wait to standby get back in sync :
2) Once the standby is in sync : run the command again :
SQL> alter database switchover to Findr verify;
Database altered.
This time it completed without any errors
Below is the alert log out for the command :
2.1) Alert log Primary Side :
TT00: Standby redo logfile selected for thread 1 sequence 111 for destination LOG_ARCHIVE_DEST_2
alter database switchover to Findr verify
Wed May 06 03:26:00 2020
SWITCHOVER VERIFY: Send VERIFY request to switchover target FINDR
SWITCHOVER VERIFY COMPLETE
Completed: alter database switchover to Findr verify2.2) Alert log Standby Side :
Wed May 06 03:26:01 2020
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY COMPLETE3) After successful validation and confirmation about the DBs readiness for the role transition
execute the actual switchover command on the primary database.
(advised to view the alert.log files of PRIMARY and STANDBY instances).
4) Now execute the switchover command on primary
SQL> alter database switchover to Findr;
Database altered.4.1) Alert log output for Primary Switchover :
Wed May 06 03:38:13 2020
alter database switchover to Findr
Wed May 06 03:38:13 2020
Starting switchover [Process ID: 10980]
Wed May 06 03:38:13 2020
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 10980] (Findb1)
Wed May 06 03:38:13 2020
Thread 1 advanced to log sequence 112 (LGWR switch)
Current log# 2 seq# 112 mem# 0: +DATA/FINDB/ONLINELOG/group_2.263.1038428391
Current log# 2 seq# 112 mem# 1: +FRA/FINDB/ONLINELOG/group_2.258.1038428393
Wed May 06 03:38:13 2020
Waiting for target standby to receive all redo
Wed May 06 03:38:13 2020
ARC3: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
Wed May 06 03:38:13 2020
Waiting for all non-current ORLs to be archived...
Wed May 06 03:38:13 2020
Waiting for the ORL for thread 1 sequence 111 to be archived...
Wed May 06 03:38:14 2020
ARC3: Archive log rejected (thread 1 sequence 111) at host 'Findr'
Wed May 06 03:38:14 2020
Archived Log entry 368 added for thread 1 sequence 111 ID 0x2d970f60 dest 1:
Wed May 06 03:38:15 2020
ORL for thread 1 sequence 111 has been archived...
Wed May 06 03:38:15 2020
All non-current ORLs have been archived.
Wed May 06 03:38:15 2020
Waiting for all FAL entries to be archived...
Wed May 06 03:38:15 2020
All FAL entries have been archived.
Wed May 06 03:38:15 2020
Waiting for dest_id 2 to become synchronized...
Wed May 06 03:38:16 2020
Active, synchronized Physical Standby switchover target has been identified
Preventing updates and queries at the Primary
Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 112 has been fixed
Switchover End-Of-Redo Log thread 2 sequence 104 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x256a90
ARCH: Noswitch archival of thread 2, sequence 104
ARCH: End-Of-Redo Branch archival of thread 2 sequence 104
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 2 sequence 104 for destination LOG_ARCHIVE_DEST_2
ARCH: Noswitch archival of thread 1, sequence 112
ARCH: End-Of-Redo Branch archival of thread 1 sequence 112
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 112 for destination LOG_ARCHIVE_DEST_2
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
Backup controlfile written to trace file /oracle/app/orawork/diag/rdbms/findb/Findb1/trace/Findb1_ora_10980.trc
Converting the primary database to a new standby database
Clearing standby activation ID 764874592 (0x2d970f60)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 188 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
Archivelog for thread 1 sequence 112 required for standby recovery
Archivelog for thread 2 sequence 104 required for standby recovery
Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 7 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Switchover: Primary controlfile converted to standby controlfile succesfully.
Wed May 06 03:38:29 2020
Reconfiguration started (old inc 4, new inc 6)
List of instances:
1 (myinst: 1)
Global Resource Directory frozen
* dead instance detected - domain 0 invalid = TRUE
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Wed May 06 03:38:29 2020
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Wed May 06 03:38:29 2020
Post SMON to start 1st pass IR
Wed May 06 03:38:29 2020
Switchover: Complete - Database shutdown required
Sending request(convert to primary database) to switchover target FINDR
Wed May 06 03:38:29 2020
minact-scn: Inst 1 is now the master inc#:4 mmon proc-id:8732 status:0x7
minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00251b7f gcalc-scn:0x0000.00251b89
minact-scn: master found reconf/inst-rec before recscn scan old-inc#:4 new-inc#:4
Wed May 06 03:38:29 2020
Instance recovery: looking for dead threads
Process (ospid 8722) is suspended due to switchover to physical standby operation.
Wed May 06 03:38:29 2020
Submitted all GCS remote-cache requests
Fix write in gcs resources
Wed May 06 03:38:29 2020
Reconfiguration complete (total time 0.6 secs)
Wed May 06 03:38:34 2020
Switchover complete. Database shutdown required
USER (ospid: 10980): terminating the instance
Wed May 06 03:38:36 2020
Instance terminated by USER, pid = 10980
Completed: alter database switchover to Findr
Shutting down instance (abort)
License high water mark = 25
Wed May 06 03:38:36 2020
Instance shutdown complete4.2) Alert log output for standby Side :
RFS[2]: Selected log 8 for thread 2 sequence 104 dbid 764828770 branch 1038428388
Wed May 06 03:38:14 2020
Archived Log entry 149 added for thread 2 sequence 103 ID 0x2d970f60 dest 1:
Wed May 06 03:38:14 2020
Media Recovery Waiting for thread 2 sequence 104 (in transit)
Wed May 06 03:38:15 2020
Recovery of Online Redo Log: Thread 2 Group 8 Seq 104 Reading mem 0
Mem# 0: +FRA/FINDR/ONLINELOG/group_8.392.1039663037
Mem# 1: +DATA/FINDR/ONLINELOG/group_8.276.1039663049
Media Recovery Waiting for thread 1 sequence 112
Wed May 06 03:38:15 2020
Archived Log entry 150 added for thread 1 sequence 111 ID 0x2d970f60 dest 1:
Wed May 06 03:38:17 2020
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 3727
Wed May 06 03:38:18 2020
RFS[7]: Assigned to RFS process (PID:10332)
RFS[7]: Selected log 8 for thread 2 sequence 104 dbid 764828770 branch 1038428388
Wed May 06 03:38:18 2020
Archived Log entry 151 added for thread 2 sequence 104 ID 0x2d970f60 dest 1:
Wed May 06 03:38:18 2020
RFS[8]: Assigned to RFS process (PID:10335)
RFS[8]: Selected log 5 for thread 1 sequence 112 dbid 764828770 branch 1038428388
Wed May 06 03:38:19 2020
Recovery of Online Redo Log: Thread 1 Group 5 Seq 112 Reading mem 0
Mem# 0: +FRA/FINDR/ONLINELOG/group_5.395.1039663093
Mem# 1: +DATA/FINDR/ONLINELOG/group_5.279.1039663107
Wed May 06 03:38:19 2020
Resetting standby activation ID 764874592 (0x2d970f60)
Wed May 06 03:38:19 2020
Media Recovery End-Of-Redo indicator encountered
Wed May 06 03:38:19 2020
Media Recovery Continuing
Media Recovery Waiting for thread 2 sequence 105
Wed May 06 03:38:19 2020
Archived Log entry 152 added for thread 1 sequence 112 ID 0x2d970f60 dest 1:
Wed May 06 03:38:21 2020
RFS[3]: Possible network disconnect with primary database
Wed May 06 03:38:22 2020
RFS[6]: Possible network disconnect with primary database
Wed May 06 03:38:22 2020
RFS[9]: Assigned to RFS process (PID:6529)
RFS[9]: Possible network disconnect with primary database
Wed May 06 03:38:30 2020
SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.
Wed May 06 03:38:30 2020
ALTER DATABASE SWITCHOVER TO PRIMARY (Findr)
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
Wed May 06 03:38:32 2020
MRP0: Background Media Recovery cancelled with status 16037
Wed May 06 03:38:32 2020
Errors in file /oracle/app/orawork/diag/rdbms/findr/Findr/trace/Findr_mrp0_9021.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Wed May 06 03:38:32 2020
MRP0: Background Media Recovery process shutdown (Findr)
Wed May 06 03:38:32 2020
Role Change: Canceled MRP
Killing 3 processes (PIDS:10326,6524,6755) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 10349
Backup controlfile written to trace file /oracle/app/orawork/diag/rdbms/findr/Findr/trace/Findr_rmi_10349.trc
SwitchOver after complete recovery through change 2452112
Online logfile pre-clearing operation disabled by switchover
Online log +DATA/FINDR/ONLINELOG/group_1.269.1041899703: Thread 1 Group 1 was previously cleared
Online log +FRA/FINDR/ONLINELOG/group_1.258.1041899709: Thread 1 Group 1 was previously cleared
Online log +DATA/FINDR/ONLINELOG/group_2.270.1041899715: Thread 1 Group 2 was previously cleared
Online log +FRA/FINDR/ONLINELOG/group_2.259.1041899721: Thread 1 Group 2 was previously cleared
Online log +DATA/FINDR/ONLINELOG/group_3.271.1041899727: Thread 2 Group 3 was previously cleared
Online log +FRA/FINDR/ONLINELOG/group_3.260.1041899733: Thread 2 Group 3 was previously cleared
Online log +DATA/FINDR/ONLINELOG/group_4.272.1041899739: Thread 2 Group 4 was previously cleared
Online log +FRA/FINDR/ONLINELOG/group_4.261.1041899743: Thread 2 Group 4 was previously cleared
Standby became primary SCN: 2452110
Switchover: Complete - Database mounted as primary
SWITCHOVER: completed request from primary database.
Wed May 06 03:39:44 2020
ARC0: Becoming the 'no SRL' ARCH
Wed May 06 03:39:45 2020
ARC1: Becoming the 'no SRL' ARCH5) New Primary Status (Findr)
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- --------------------
1 Findr FINDB dm01dr01.database.com PRIMARY ARCHIVELOG MOUNTED 12.1.0.1.0 ALLOWED 06-MAY-2020 02:44:35
SQL> alter database open;
Database altered.
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- --------------------
1 Findr FINDB dm01dr01.database.com PRIMARY ARCHIVELOG READ WRITE 12.1.0.1.0 ALLOWED 06-MAY-2020 02:44:35
6) Now Start the old primary (new standby)
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 6 03:48:28 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@dm01db01 ~]$
[oracle@dm01db01 ~]$ srvctl start database -d Findb -o mount
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- --------------------
1 Findb1 FINDB dm01db01.database.com PHYSICAL STANDBY ARCHIVELOG MOUNTED 12.1.0.1.0 ALLOWED 06-MAY-2020 03:49:33
2 Findb2 FINDB dm01db02.database.com PHYSICAL STANDBY ARCHIVELOG MOUNTED 12.1.0.1.0 ALLOWED 06-MAY-2020 03:49:35
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
6.1) Alter log file while starting the recover on new standby
Wed May 06 03:54:22 2020
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has b een deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has b een deprecated.
ALTER DATABASE RECOVER managed standby database using current logfile disconnect fr om session
Wed May 06 03:54:22 2020
Attempt to start background Managed Standby Recovery process (Findb1)
Starting background process MRP0
Wed May 06 03:54:22 2020
MRP0 started with pid=45, OS id=19201
Wed May 06 03:54:22 2020
MRP0: Background Managed Standby Recovery process started (Findb1)
Wed May 06 03:54:27 2020
Serial Media Recovery started
Wed May 06 03:54:27 2020
Managed Standby Recovery starting Real Time Apply
Wed May 06 03:54:28 2020
Waiting for all non-current ORLs to be archived...
Wed May 06 03:54:28 2020
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/FINDB/ONLINELOG/group_1.262.1038428389
Clearing online log 1 of thread 1 sequence number 115
Completed: ALTER DATABASE RECOVER managed standby database using current logfile di sconnect from session
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/FINDB/ONLINELOG/group_2.263.1038428391
Clearing online log 2 of thread 1 sequence number 114
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/FINDB/ONLINELOG/group_3.269.1038428699
Clearing online log 3 of thread 2 sequence number 104
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 +DATA/FINDB/ONLINELOG/group_4.270.1038428721
Clearing online log 4 of thread 2 sequence number 103
Wed May 06 03:54:39 2020
Clearing online redo logfile 4 complete
Wed May 06 03:54:39 2020
Media Recovery Log +FRA/FINDB/ARCHIVELOG/2020_05_06/thread_2_seq_103.475.1039664293
Wed May 06 03:54:39 2020
Media Recovery Log +FRA/FINDB/ARCHIVELOG/2020_05_06/thread_1_seq_110.474.1039663541
Wed May 06 03:54:39 2020
Media Recovery Log +FRA/FINDB/ARCHIVELOG/2020_05_06/thread_1_seq_111.476.1039664295
Wed May 06 03:54:40 2020
Media Recovery Log +FRA/FINDB/ARCHIVELOG/2020_05_06/thread_2_seq_104.478.1039665007
Wed May 06 03:54:40 2020
Media Recovery Log +FRA/FINDB/ARCHIVELOG/2020_05_06/thread_1_seq_112.477.1039665007
Wed May 06 03:54:40 2020
Resetting standby activation ID 0 (0x0)
Wed May 06 03:54:40 2020
Media Recovery End-Of-Redo indicator encountered
Wed May 06 03:54:40 2020
Media Recovery Continuing
Wed May 06 03:54:40 2020
Media Recovery Log +FRA/FINDB/ARCHIVELOG/2020_05_06/thread_1_seq_113.479.1039665011
Wed May 06 03:54:40 2020
Media Recovery Log +FRA/FINDB/ARCHIVELOG/2020_05_06/thread_2_seq_105.481.1039665013
Wed May 06 03:54:40 2020
Media Recovery Log +FRA/FINDB/ARCHIVELOG/2020_05_06/thread_1_seq_114.480.1039665011
Media Recovery Waiting for thread 1 sequence 115
Wed May 06 03:55:11 2020
Using STANDBY_ARCHIVE_DEST parameter default value as +FRA
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Assigned to RFS process (PID:19310)
RFS[1]: Selected log 5 for thread 1 sequence 115 dbid 764828770 branch 1038428388
Wed May 06 03:55:17 2020
Recovery of Online Redo Log: Thread 1 Group 5 Seq 115 Reading mem 0
Mem# 0: +FRA/FINDB/ONLINELOG/group_5.450.1039662481
Mem# 1: +DATA/FINDB/ONLINELOG/group_5.276.1039662697
7) Post Switchover, run the following commands:
7.1)On New Primary :
SQL> alter system switch logfile;
System altered.
SQL> col error for a25
SQL> select dest_id,error,status from v$archive_dest where dest_id=2;
DEST_ID ERROR STATUS
---------- ------------------------- ---------
2 VALID
SQL> select max(sequence#),thread# from v$log_history group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
119 1
105 2
SQL> select max(sequence#) from v$archived_log where applied='YES' and dest_id=2;
MAX(SEQUENCE#)
--------------
118
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FINPDB READ WRITE NO
8) Switchover complete one thread 1 is getting the archive because there was only single node standby
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 127 127 0
2 105 105 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 FINPDB MOUNTED
SQL> ***********************************Switchover Completed*********************************
Comments
Post a Comment