A opera\u00e7\u00e3o de switchover no Data Guard corresponde a uma mudan\u00e7a planejada\/desejada de fun\u00e7\u00e3o, seja para testes peri\u00f3dicos de estrat\u00e9gia de DR, migra\u00e7\u00f5es ou upgrades. Nela, um dos ambientes physical standby recebe do primary os redo logs restantes, e trocam de role\/fun\u00e7\u00e3o, ou seja, o standby passa a operar como primary.<\/p>\n\n\n\n
Algumas etapas preliminares que seriam importantes antes de efetuar esta atividade:<\/p>\n\n\n\n
Rodando o comando abaixo no primary, que reporta que a sequence do current \u00e9 48:<\/p>\n\n\n
\n[oracle@fornix1 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 17 16:01:00 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> SELECT THREAD#,SEQUENCE#, STATUS FROM V$LOG;\n \n THREAD# SEQUENCE# STATUS\n---------- ---------- ----------------\n 1 46 INACTIVE\n 1 47 ACTIVE\n 1 48 CURRENT\n<\/pre><\/div>\n\n\nA consulta abaixo no ambiente standby nos reporta que n\u00e3o temos Lag:<\/p>\n\n\n
\n[oracle@fornix2 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 17 16:02:50 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> SELECT CLIENT_PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;\n \nCLIENT_P SEQUENCE# STATUS\n-------- ---------- ------------\nARCH 0 CONNECTED\nN\/A 0 ALLOCATED\nN\/A 0 ALLOCATED\nARCH 45 CLOSING\nARCH 0 CONNECTED\nARCH 47 CLOSING\nArchival 0 IDLE\nLGWR 48 RECEIVING\nN\/A 48 APPLYING_LOG\nUNKNOWN 0 IDLE\nUNKNOWN 0 IDLE\n \n11 rows selected.\n<\/pre><\/div>\n\n\nA consulta abaixo no primary nos reporta que o Data Guard est\u00e1 configurado em maximum performance:<\/p>\n\n\n
\nSQL> SELECT DB_UNIQUE_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS, SYNCHRONIZED FROM V$ARCHIVE_DEST_STATUS\nWHERE DB_UNIQUE_NAME='cortexDR'; 2\n \nDB_UNIQUE_NAME PROTECTION_MODE SYNCHRONIZATION_STATUS SYN\n------------------------------ -------------------- ---------------------- ---\ncortexDR MAXIMUM PERFORMANCE CHECK CONFIGURATION NO\n<\/pre><\/div>\n\n\nVerificando se h\u00e1 gap de Redo Apply<\/h2>\n\n\n\n
No physical standby, antes de efetuarmos o switchover, \u00e9 bom termos o processo MRPn no status de “Applying_Log”, conforme exemplo abaixo no standby. Caso este status esteja como “Wait_for_gap”, o ideal \u00e9 mister resolv\u00ea-lo.<\/p>\n\n\n
\n[oracle@fornix2 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 17 16:13:32 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> SELECT STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';\n \nSTATUS\n------------\nAPPLYING_LOG\n<\/pre><\/div>\n\n\nRealizando o Switchover<\/h2>\n\n\n\n
Temos a op\u00e7\u00e7\u00e3o de verificar se o standby est\u00e1 pronto para a opera\u00e7\u00e3o, usando o comando abaixo no primary. Caso o output seja diferente do exemplo (como um ORA-16475 \/ ORA-16470), deve ser investigado e resolvido:<\/p>\n\n\n
\nSQL> ALTER DATABASE SWITCHOVER TO cortexDR VERIFY;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nFinalmente realizando o switchover no primary. Como este \u00e9 um ambiente de laborat\u00f3rio, nao foi necess\u00e1rio checar se havia Scheduled ou RMAN jobs em execu\u00e7\u00e3o, mas esses detalhes devem ser vistos em um banco produtivo.<\/p>\n\n\n
\nSQL> ALTER DATABASE SWITCHOVER TO cortexDR;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nAlert do primary. Destaco aqui as mensagens: “Waiting for target standby to receive all redo”, “Waiting for target standby to apply all redo”, “Converting the primary database to a new standby database”, “Switchover complete. Database shutdown required”:<\/p>\n\n\n
\nALTER DATABASE SWITCHOVER TO cortexDR\n2021-04-17T16:21:01.074995-03:00\nNET (PID:5036): The Time Management Interface (TMI) is being enabled for role transition\nNET (PID:5036): information. This will result in messages beingoutput to the alert log\nNET (PID:5036): file with the prefix 'TMI: '. This is being enabled to make the timing of\nNET (PID:5036): the various stages of the role transition available for diagnostic purposes.\nNET (PID:5036): This output will end when the role transition is complete.\nTMI: dbsdrv switchover to target BEGIN 2021-04-17 16:21:01.075367\nNET (PID:5036): Starting switchover [Process ID: 5036]\nTMI: kcv_switchover_to_target convert to physical BEGIN 2021-04-17 16:21:01.324806\n2021-04-17T16:21:01.324887-03:00\nALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 5036] (cortex)\nNET (PID:5036): Waiting for target standby to receive all redo\n2021-04-17T16:21:01.389990-03:00\nNET (PID:5036): Waiting for all non-current ORLs to be archived\n2021-04-17T16:21:01.391569-03:00\nNET (PID:5036): All non-current ORLs have been archived\n2021-04-17T16:21:01.391633-03:00\nNET (PID:5036): Waiting for all FAL entries to be archived\n2021-04-17T16:21:01.391677-03:00\nNET (PID:5036): All FAL entries have been archived\n2021-04-17T16:21:01.391728-03:00\nNET (PID:5036): Waiting for LAD:2 to become synchronized\n2021-04-17T16:21:02.394356-03:00\nNET (PID:5036): Active, synchronized Physical Standby switchover target has been identified\nNET (PID:5036): Preventing updates and queries at the Primary\n2021-04-17T16:21:03.397979-03:00\nNET (PID:5036): Generating and shipping final logs to target standby\nSwitchover End-Of-Redo Log thread 1 sequence 48 has been fixed\nSwitchover: Primary highest seen SCN set to 0x00000000004d0639\nNET (PID:5036): Noswitch archival of T-1.S-48\nNET (PID:5036): End-Of-Redo Branch archival of T-1.S-48\nNET (PID:5036): LGWR is scheduled to archive to LAD:2 after log switch\nNET (PID:5036): SRL selected for T-1.S-48 for LAD:2\nNET (PID:5036): Archiving is disabled due to current logfile archival\nPrimary will check for some target standby to have received all redo\nNET (PID:5036): Waiting for target standby to apply all redo\n2021-04-17T16:21:05.354878-03:00\nBackup controlfile written to trace file \/oracle\/19.3.0\/base\/diag\/rdbms\/cortex\/cortex\/trace\/cortex_ora_5036.trc\nNET (PID:5036): Converting the primary database to a new standby database\nClearing standby activation ID 548987031 (0x20b8e097)\nThe primary database controlfile was created using the\n'MAXLOGFILES 16' clause.\nThere is space for up to 13 standby redo logfiles\nUse the following SQL commands on the standby database to create\nstandby redo logfiles that match the primary database:\nALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;\nALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;\nALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;\nALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;\nArchivelog for thread 1 sequence 48 required for standby recovery\nSwitchover: Primary controlfile converted to standby controlfile succesfully.\nSwitchover: Complete - Database shutdown required\nTMI: kcv_switchover_to_target convert to physical END 2021-04-17 16:21:05.511197\nNET (PID:5036): Sending request(convert to primary database) to switchover target CORTEXDR\n2021-04-17T16:21:09.103894-03:00\nNET (PID:5036): Switchover complete. Database shutdown required\nUSER (ospid: 5036): terminating the instance\n<\/pre><\/div>\n\n\nAlert do Standby. Destaco as mensagens: “‘ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY’ from primary database”, “Standby became primary SCN: 5047863” e “Switchover: Complete – Database mounted as primary”<\/p>\n\n\n
\nSWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.\n2021-04-17T16:21:05.379653-03:00\nALTER DATABASE SWITCHOVER TO PRIMARY (CORTEXDR)\nMaximum wait for role transition is 15 minutes.\nTMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2021-04-17 16:21:05.379734\nSwitchover: Media recovery is still active\n rmi (PID:6287): Role Change: Canceling MRP - no more redo to apply\n2021-04-17T16:21:05.412861-03:00\nMRP0 (PID:4394): MRP0: Background Media Recovery cancelled with status 16037\n2021-04-17T16:21:05.412967-03:00\nErrors in file \/oracle\/19.3.0\/base\/diag\/rdbms\/cortexdr\/CORTEXDR\/trace\/CORTEXDR_mrp0_4394.trc:\nORA-16037: user requested cancel of managed recovery operation\nMRP0 (PID:4394): Managed Standby Recovery not using Real Time Apply\nRecovery interrupted!\nstopping change tracking\n2021-04-17T16:21:05.441252-03:00\nErrors in file \/oracle\/19.3.0\/base\/diag\/rdbms\/cortexdr\/CORTEXDR\/trace\/CORTEXDR_mrp0_4394.trc:\nORA-16037: user requested cancel of managed recovery operation\n2021-04-17T16:21:05.441292-03:00\nBackground Media Recovery process shutdown (CORTEXDR)\n2021-04-17T16:21:06.393276-03:00\n rmi (PID:6287): Role Change: Canceled MRP\nTMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2021-04-17 16:21:06.393344\nTMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2021-04-17 16:21:06.394600\n rmi (PID:6287): Killing 3 processes (PIDS:6257,4533,4535) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 6287\n2021-04-17T16:21:06.397786-03:00\nProcess termination requested for pid 6257 [source = rdbms], [info = 2] [request issued by pid: 6287, uid: 54322]\n2021-04-17T16:21:06.397900-03:00\nProcess termination requested for pid 4533 [source = rdbms], [info = 2] [request issued by pid: 6287, uid: 54322]\n2021-04-17T16:21:06.398006-03:00\nProcess termination requested for pid 4535 [source = rdbms], [info = 2] [request issued by pid: 6287, uid: 54322]\n2021-04-17T16:21:08.485740-03:00\nBackup controlfile written to trace file \/oracle\/19.3.0\/base\/diag\/rdbms\/cortexdr\/CORTEXDR\/trace\/CORTEXDR_rmi_6287.trc\nSwitchOver after complete recovery through change 5047865\n rmi (PID:6287): ORL pre-clearing operation disabled by switchover\nOnline log +DG_FRA\/CORTEXDR\/ONLINELOG\/group_1.261.1066479861: Thread 1 Group 1 was previously cleared\nOnline log +DG_DATA\/CORTEXDR\/ONLINELOG\/group_1.263.1066479867: Thread 1 Group 1 was previously cleared\nOnline log +DG_FRA\/CORTEXDR\/ONLINELOG\/group_2.262.1066479871: Thread 1 Group 2 was previously cleared\nOnline log +DG_DATA\/CORTEXDR\/ONLINELOG\/group_2.262.1066479877: Thread 1 Group 2 was previously cleared\nOnline log +DG_FRA\/CORTEXDR\/ONLINELOG\/group_3.263.1066479879: Thread 1 Group 3 was previously cleared\nOnline log +DG_DATA\/CORTEXDR\/ONLINELOG\/group_3.261.1066479885: Thread 1 Group 3 was previously cleared\nStandby became primary SCN: 5047863\n rmi (PID:6287): RT: Role transition work is not done\n rmi (PID:6287): The Time Management Interface (TMI) is being enabled for role transition\n rmi (PID:6287): information. This will result in messages beingoutput to the alert log\n rmi (PID:6287): file with the prefix 'TMI: '. This is being enabled to make the timing of\n rmi (PID:6287): the various stages of the role transition available for diagnostic purposes.\n rmi (PID:6287): This output will end when the role transition is complete.\n rmi (PID:6287): Redo network throttle feature is disabled at mount time\n2021-04-17T16:21:08.936921-03:00\n rmi (PID:6287): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]\nSwitchover: Complete - Database mounted as primary\nTMI: kcv_commit_to_so_to_primary Switchover from physical END 2021-04-17 16:21:08.937582\nSWITCHOVER: completed request from primary database.\n2021-04-17T16:22:01.293623-03:00\nARC0 (PID:3566): Becoming the 'no SRL' ARCH\n<\/pre><\/div>\n\n\nAbrindo o novo primary database:<\/p>\n\n\n
\n[oracle@fornix2 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 17 16:32:27 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> select instance_name,status from v$instance;\n \nINSTANCE_NAME STATUS\n---------------- ------------\nCORTEXDR MOUNTED\n \nSQL> ALTER DATABASE OPEN;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nMontando o novo standby e habilitando o sincronismo:<\/p>\n\n\n
\n[oracle@fornix1 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 17 16:33:39 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \nConnected to an idle instance.\n \nSQL> startup mount;\nORACLE instance started.\n \nTotal System Global Area 2583690520 bytes\nFixed Size 8899864 bytes\nVariable Size 553648128 bytes\nDatabase Buffers 2013265920 bytes\nRedo Buffers 7876608 bytes\nDatabase mounted.\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\n \nDatabase altered.\n<\/pre><\/div>\n\n\nGerando redo no novo primary:<\/p>\n\n\n
\n[oracle@fornix2 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 17 16:35:42 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n<\/pre><\/div>\n\n\nChecando que o alert do novo standby o recebeu e vai proceder com o processo:<\/p>\n\n\n
\nCompleted: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT\n2021-04-17T16:34:36.972515-03:00\nMRP0 (PID:9153): Resetting standby activation ID 0 (0x0)\n2021-04-17T16:34:36.985072-03:00\nMedia Recovery End-Of-Redo indicator encountered\n2021-04-17T16:34:36.985125-03:00\nMedia Recovery Continuing\n2021-04-17T16:34:36.990195-03:00\nMRP0 (PID:9153): Media Recovery Log +DG_RECO\/CORTEX\/ARCHIVELOG\/2021_04_17\/thread_1_seq_49.300.1070123641\nMRP0 (PID:9153): Media Recovery Waiting for T-1.S-50 (in transit)\n2021-04-17T16:34:37.061145-03:00\nRecovery of Online Redo Log: Thread 1 Group 4 Seq 50 Reading mem 0\n Mem# 0: +DG_FRA\/CORTEX\/ONLINELOG\/group_4.261.1066367139\n2021-04-17T16:35:55.941372-03:00\n rfs (PID:9244): Primary database is in MAXIMUM PERFORMANCE mode\n rfs (PID:9244): Re-archiving LNO:4 T-1.S-50\n2021-04-17T16:35:56.544876-03:00\n rfs (PID:9244): Selected LNO:5 for T-1.S-51 dbid 548968087 branch 1039033628\n2021-04-17T16:35:56.582024-03:00\nARC3 (PID:9106): Archived Log entry 59 added for T-1.S-50 ID 0x22930349 LAD:1\n2021-04-17T16:35:56.686164-03:00\nMRP0 (PID:9153): Media Recovery Waiting for T-1.S-51 (in transit)\n2021-04-17T16:35:56.688068-03:00\nRecovery of Online Redo Log: Thread 1 Group 5 Seq 51 Reading mem 0\n Mem# 0: +DG_FRA\/CORTEX\/ONLINELOG\/group_5.262.1066367153\n<\/pre><\/div>\n\n\nObs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
A opera\u00e7\u00e3o de switchover no Data Guard corresponde a uma mudan\u00e7a planejada\/desejada de fun\u00e7\u00e3o, seja para testes peri\u00f3dicos de estrat\u00e9gia de DR, migra\u00e7\u00f5es ou upgrades. Nela, um dos ambientes physical standby recebe do primary os redo logs restantes, e trocam de role\/fun\u00e7\u00e3o, ou seja, o standby passa a operar como primary. Algumas etapas preliminares que […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-3883","post","type-post","status-publish","format-standard","hentry","category-high-availability"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3883","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=3883"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3883\/revisions"}],"predecessor-version":[{"id":9166,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3883\/revisions\/9166"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3883"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3883"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3883"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}