O processo de Switchover utilizando o Data Broker é basicamente o mesmo, para ambientes physical ou logical standby. Neste artigo simularei o processo em cima de um physical.
Checando a condição atual do ambiente:
[oracle@fornix1 ~]$ dgmgrl sys/oracle@CORTEX
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 04:46:58 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cortex"
Connected as SYSDBA.
DGMGRL> SHOW CONFIGURATION;
Configuration - cortex
Protection Mode: MaxPerformance
Members:
cortex - Primary database
cortexdr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 22 seconds ago)
DGMGRL> SHOW DATABASE CORTEX;
Database - cortex
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
cortex
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE CORTEXDR;
Database - cortexdr
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
CORTEXDR
Database Status:
SUCCESS
DGMGRL>
É possível ver que o Redo Apply ainda está OFF no standby. Porém, antes de ligá-lo, vamos criar o Flashback Database Guaranteed Restore Point (GRP) nos bancos envolvidos, para termos esse método de rollback em caso de falha na operação de Switchover:
[oracle@fornix1 ~]$ sqlplus sys/oracle@CORTEX as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 04:50:38 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE RESTORE POINT before_switchover GUARANTEE FLASHBACK DATABASE;
Restore point created.
[oracle@fornix2 ~]$ sqlplus sys/oracle@CORTEXDR as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 04:51:21 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE RESTORE POINT before_switchover GUARANTEE FLASHBACK DATABASE;
Restore point created.
Ligando o Redo Apply:
[oracle@fornix1 ~]$ dgmgrl sys/oracle@CORTEX
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 04:52:25 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cortex"
Connected as SYSDBA.
DGMGRL> EDIT DATABASE CORTEXDR SET STATE=APPLY-ON;
Succeeded.
DGMGRL> SHOW DATABASE CORTEXDR;
Database - cortexdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
CORTEXDR
Database Warning(s):
ORA-16854: apply lag could not be determined
Database Status:
WARNING
DGMGRL> SHOW DATABASE CORTEXDR;
Database - cortexdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 105.00 KByte/s
Real Time Query: OFF
Instance(s):
CORTEXDR
Database Status:
SUCCESS
É necessário confirmar se os bancos (primary e standby) estão registrados de forma estática no listener. No meu caso, já estavam registrados com sucesso:
[grid@fornix1 admin]$ cat listener.ora
#Backup file is /grid/19.3.0/base/crsdata/fornix1/output/listener.ora.bak.fornix1.grid line added by Agent
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON # line added by Agent
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = fornix1.localdomain) (PORT = 1521))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CORTEX_DGMGRL.localdomain)
(ORACLE_HOME = /grid/19.3.0/product)
(SID_NAME = CORTEX)
)
)
[grid@fornix2 admin]$ cat listener.ora
#Backup file is /grid/19.3.0/base/crsdata/fornix2/output/listener.ora.bak.forni x2.grid line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON # line added by Agent
LISTENERDR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = fornix2.localdomain) (PORT = 1522))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1522))
)
)
SID_LIST_LISTENERDR=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=CORTEXDR.localdomain)
(SID_NAME=CORTEXDR)
(ORACLE_HOME=/oracle/19.3.0/product)
)
( SID_DESC=
(GLOBAL_DBNAME=CORTEXDR_DGMGRL.localdomain)
(ORACLE_HOME=/grid/19.3.0/product)
(SID_NAME=CORTEXDR)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENERDR=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENERDR=ON # line added by Agent
Confirmando se o ambiente standby está recebendo todos os redos:
[oracle@fornix1 ~]$ sqlplus sys/oracle@CORTEX as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 04:58:15 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT THREAD#,SEQUENCE#,STATUS FROM V$LOG;
THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 70 INACTIVE
1 71 INACTIVE
1 72 CURRENT
[oracle@fornix2 ~]$ sqlplus sys/oracle@CORTEXDR as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 04:58:48 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
CLIENT_P PROCESS SEQUENCE# STATUS
-------- --------- ---------- ------------
LGWR RFS 72 IDLE
UNKNOWN RFS 0 IDLE
Archival RFS 0 IDLE
ARCH ARCH 0 CONNECTED
N/A DGRD 0 ALLOCATED
N/A DGRD 0 ALLOCATED
ARCH ARCH 0 CONNECTED
ARCH ARCH 71 CLOSING
ARCH ARCH 0 CONNECTED
N/A MRP0 72 APPLYING_LOG
10 rows selected.
A forma alternativa de confirmar isso é pelo utilitário do broker:
[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 04:59:43 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cortexDR"
Connected as SYSDBA.
DGMGRL> SHOW DATABASE CORTEXDR;
Database - cortexdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 12.00 KByte/s
Real Time Query: OFF
Instance(s):
CORTEXDR
Database Status:
SUCCESS
Confirmando que o status do processo MRP está como “APPLYING_LOG”:
[oracle@fornix2 ~]$ sqlplus sys/oracle@CORTEXDR as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 05:00:51 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
STATUS
------------
APPLYING_LOG
Nesta etapa já estamos prontos para realizar o validate no atual standby, para verificar se o mesmo está apto a virar o novo primary. Podemos assim já ficar monitorando o alert.log dos 2 bancos, além do alert do próprio broker. Realizando o validate:
[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 05:02:25 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cortexDR"
Connected as SYSDBA.
DGMGRL> VALIDATE DATABASE CORTEXDR;
Database Role: Physical standby database
Primary Database: cortex
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
cortex : YES
cortexdr: YES
Log Files Cleared:
cortex Standby Redo Log Files: Cleared
cortexdr Online Redo Log Files: Not Cleared
cortexdr Standby Redo Log Files: Available
Transport-Related Property Settings:
Property cortex Value cortexdr Value
ReopenSecs 300 20
NetTimeout 30 20
DGMGRL>
No alert do standby:
2021-04-20T05:02:45.120419-03:00
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY COMPLETE
Realizando finalmente o switchover:
[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 05:06:28 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cortexDR"
Connected as SYSDBA.
DGMGRL> SWITCHOVER TO CORTEXDR;
Performing switchover NOW, please wait...
New primary database "cortexdr" is opening...
Oracle Clusterware is restarting database "cortex" ...
Connected to "cortex"
Connected to "cortex"
Switchover succeeded, new primary is "cortexdr"
DGMGRL>
Alert do antigo primary e novo standby:
2021-04-20T05:06:47.157685-03:00
RSM0 (PID:3680): Switchover complete. Database shutdown required
TMI: dbsdrv switchover to target END 2021-04-20 05:06:47.157717
Completed: ALTER DATABASE SWITCHOVER TO 'cortexdr'
2021-04-20T05:06:47.319037-03:00
Starting background process NSV0
2021-04-20T05:06:47.329677-03:00
NSV0 started with pid=53, OS id=5572
2021-04-20T05:06:58.081998-03:00
Clusterware restarting instance for Data Guard Broker operation; shutting down instance now
Alert do antigo standby e novo primary:
2021-04-20T05:06:47.554811-03:00
rmi (PID:5162): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2021-04-20 05:06:47.556114
SWITCHOVER: completed request from primary database.
Alert do Broker:
2021-04-20T05:06:47.158-03:00
SQL [ALTER DATABASE SWITCHOVER TO 'cortexdr'] executed successfully
Switchover successful
Verificando a nova configuração do ambiente:
[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 05:14:43 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cortexDR"
Connected as SYSDBA.
DGMGRL> SHOW CONFIGURATION;
Configuration - cortex
Protection Mode: MaxPerformance
Members:
cortexdr - Primary database
cortex - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 55 seconds ago)
DGMGRL> SHOW DATABASE CORTEX;
Database - cortex
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 18.00 KByte/s
Real Time Query: OFF
Instance(s):
cortex
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE CORTEXDR;
Database - cortexdr
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
CORTEXDR
Database Status:
SUCCESS
Realizando o Switchback:
[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 05:16:25 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cortexDR"
Connected as SYSDBA.
DGMGRL> SWITCHOVER TO CORTEX;
Performing switchover NOW, please wait...
Operation requires a connection to database "cortex"
Connecting ...
Connected to "cortex"
Connected as SYSDBA.
New primary database "cortex" is opening...
Oracle Clusterware is restarting database "cortexdr" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "cortexDR"
Connected to "cortexDR"
Switchover succeeded, new primary is "cortex"
DGMGRL> SHOW CONFIGURATION;
Configuration - cortex
Protection Mode: MaxPerformance
Members:
cortex - Primary database
cortexdr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 48 seconds ago)
DGMGRL> SHOW DATABASE CORTEX;
Database - cortex
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
cortex
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE CORTEXDR;
Database - cortexdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 56.00 KByte/s
Real Time Query: OFF
Instance(s):
CORTEXDR
Database Status:
SUCCESS
DGMGRL>
Removendo os GRPs criados:
[oracle@fornix2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 05:22:05 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> DROP RESTORE POINT before_switchover;
Restore point dropped.
[oracle@fornix1 ~]$ sqlplus sys/oracle@CORTEX as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 05:22:41 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> DROP RESTORE POINT before_switchover;
Restore point dropped.
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish