Switchover in Realtime Production database in Oracle 11gR2
Posted by Mir Sayeed Hassan on September 15th, 2018
Switchover in Realtime Production database in Oracle 11gR2
=================
PRIMARY DATABASE
=================
Pre-requisite
SQL> select INSTANCE_NAME,VERSION,STATUS from V$instance; INSTANCE_NAME VERSION STATUS ---------------- ----------------- ------------ primdb 11.2.0.4.0 OPEN
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- SESSIONS ACTIVE
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PRIMDB READ WRITE PRIMARY
[oracle@DB-AFC ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-SEP-2018 11:35:43 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PRIM-DB)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 13-MAY-2016 06:46:09 Uptime 355 days 2 hr. 21 min. 41 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/DB-AFC/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIM-DB)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "primdb" has 1 instance(s). Instance "primdb", status READY, has 1 handler(s) for this service... Service "primdbXDB" has 1 instance(s). Instance "primdb", status READY, has 1 handler(s) for this service... The command completed successfully
SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME STATUS ERROR --------------------------------------------------------------------------- LOG_ARCHIVE_DEST_2 VALID
SQL> select message from v$dataguard_status; MESSAGE -------------------------------------------------------------------------------- LNS: Beginning to archive log 2 thread 1 sequence 3168 ARCH: Completed archiving thread 1 sequence 3167 (231869253-231943366) LNS: Completed archiving log 2 thread 1 sequence 3168 ARC1: Beginning to archive thread 1 sequence 3168 (231943366-232002353) LNS: Standby redo logfile selected for thread 1 sequence 3169 for destination LO G_ARCHIVE_DEST_2 LNS: Beginning to archive log 3 thread 1 sequence 3169 ARC1: Completed archiving thread 1 sequence 3168 (231943366-232002353)
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 3171 15-SEP-18 11-SEP-18 YES 3172 15-SEP-18 12-SEP-18 NO 3173 15-SEP-18 12-SEP-18 YES 3174 15-SEP-18 12-SEP-18 NO 3175 15-SEP-18 12-SEP-18 YES
————————————————————-
Now start the realtime activity for switchover
————————————————————-
SQL> alter database commit to switchover to physical standby with session shutdown; Database altered.
SQL> shutdown immediate ORA-01012: not logged on SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@DB-AFC ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 15 15:06:17 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance
SQL> startup nomount ORACLE instance started. Total System Global Area 1.0021E+10 bytes Fixed Size 2261848 bytes Variable Size 2348813480 bytes Database Buffers 7650410496 bytes Redo Buffers 19894272 bytes
SQL> alter database mount standby database; Database altered.
SQL> select status from V$instance; STATUS ------------ MOUNTED
SQL> alter database recover managed standby database disconnect from session; Database altered.
SQL> SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,(SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#; THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_ ARC_DIFF ---------- ---------- ----------- --------- ---------------------------------- 1 3182 3179 15-SEP-18 3
[oracle@DB-AFC ~]$ !sq SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 3182 1 151 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CLOSING 1 3181 1 1 RFS IDLE 0 0 0 0 RFS IDLE 1 3183 1045 1 RFS IDLE 0 0 0 0 MRP0 WAIT_FOR_LOG 1 3183 0 0 8 rows selected.
SQL> SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 3182
==================
STANDBY DATABASE
==================
Pre-requisite
SQL> select INSTANCE_NAME,VERSION,STATUS from V$instance; INSTANCE_NAME VERSION STATUS ---------------- ----------------- ------------ standbydb 11.2.0.4.0 MOUNTED
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- NOT ALLOWED
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- PRIMDB MOUNTED PHYSICAL STANDBY
[oracle@standbydb ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-SEP-2018 11:36:16 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standbydb)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 31-JUL-2018 10:42:14 Uptime 43 days 0 hr. 54 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/standbydb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standbydb)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "standbydb" has 1 instance(s). Instance "standbydb", status READY, has 1 handler(s) for this service... The command completed successfully
SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME STATUS ERROR --------------------------------------------------------------------------- LOG_ARCHIVE_DEST_2 VALID
SQL> select message from v$dataguard_status; MESSAGE -------------------------------------------------------------------------------- Media Recovery Waiting for thread 1 sequence 3165 (in transit) ARC1: Completed archiving thread 1 sequence 3164 (0-0) ARC0: Beginning to archive thread 1 sequence 3165 (231660271-231799151) Media Recovery Waiting for thread 1 sequence 3166 ARC0: Completed archiving thread 1 sequence 3165 (0-0) ARC3: Beginning to archive thread 1 sequence 3166 (231799151-231869253) Media Recovery Waiting for thread 1 sequence 3167 (in transit) ARC3: Completed archiving thread 1 sequence 3166 (0-0) ARC1: Beginning to archive thread 1 sequence 3167 (231869253-231943366) Media Recovery Waiting for thread 1 sequence 3168 (in transit) ARC1: Completed archiving thread 1 sequence 3167 (0-0) MESSAGE -------------------------------------------------------------------------------- ARC1: Beginning to archive thread 1 sequence 3168 (231943366-232002353) Media Recovery Waiting for thread 1 sequence 3169 (in transit) ARC1: Completed archiving thread 1 sequence 3168 (0-0) 256 rows selected.
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 3170 15-SEP-18 11-SEP-18 YES 3171 15-SEP-18 11-SEP-18 YES 3172 15-SEP-18 11-SEP-18 YES 3173 15-SEP-18 11-SEP-18 YES 3174 15-SEP-18 12-SEP-18 YES 3175 15-SEP-18 12-SEP-18 YES SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ---------- --------- --------- --------- 3176 15-SEP-18 12-SEP-18 IN-MEMORY
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- NOT ALLOWED
SQL> alter database commit to switchover to primary; alter database commit to switchover to primary * ERROR at line 1: ORA-16139: media recovery required
SQL> RECOVER MANAGED STANDBY DATABASE FINISH; Media recovery complete
SQL> alter database commit to switchover to primary with session shutdown; Database altered.
SQL>select status from V$instance; STATUS ------------ MOUNTED
SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.
SQL> startup ORACLE instance started. Total System Global Area 1.0021E+10 bytes Fixed Size 2261848 bytes Variable Size 5234494632 bytes Database Buffers 4764729344 bytes Redo Buffers 19894272 bytes Database mounted. Database opened.
Note:
After successful switchover, active the standby database & perform some alter system switch logfile on primary database & check the mrp & rfs process on standby & it should sync and apply the logfile
======Hence switchover operation completed successfully in Production database======