Verificando que o Redo Apply est\u00e1 OFF:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ dgmgrl sys\/oracle@CORTEXDR\nDGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 04:27:05 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates. All rights reserved.\n \nWelcome to DGMGRL, type "help" for information.\nConnected to "cortexDR"\nConnected as SYSDBA.\nDGMGRL> SHOW DATABASE VERBOSE CORTEXDR;\n \nDatabase - cortexdr\n \n Role: PHYSICAL STANDBY\n Intended State: APPLY-OFF\n Transport Lag: 0 seconds (computed 0 seconds ago)\n Apply Lag: (unknown)\n Average Apply Rate: (unknown)\n Active Apply Rate: (unknown)\n Maximum Apply Rate: (unknown)\n Real Time Query: OFF\n Instance(s):\n CORTEXDR\n...\n<\/pre><\/div>\n\n\nInicializando o apply:<\/p>\n\n\n
\nDGMGRL> EDIT DATABASE CORTEXDR SET STATE=APPLY-ON;\nSucceeded.\nDGMGRL> SHOW DATABASE VERBOSE CORTEXDR;\n \nDatabase - cortexdr\n \n Role: PHYSICAL STANDBY\n Intended State: APPLY-ON\n Transport Lag: 0 seconds (computed 1 second ago)\n Apply Lag: 0 seconds (computed 1 second ago)\n Average Apply Rate: 685.00 KByte\/s\n Active Apply Rate: 0 Byte\/s\n Maximum Apply Rate: 0 Byte\/s\n Real Time Query: OFF\n Instance(s):\n CORTEXDR\n...\n<\/pre><\/div>\n\n\nNo ambiente primary, vamos criar a procedure abaixo para podermos realizar um teste durante o artigo:<\/p>\n\n\n
\nSET SERVEROUTPUT ON\nCREATE OR REPLACE PROCEDURE HR.TEST_UPDATE( N NUMBER )\nIS\n \/* This procedure performs sequential update on HR.EMPLOYEES table for testing\n purposes. It doesn\u2019t make any real changes on data but real transactions\n will be generated\n *\/\n T1 DATE;\n TYPE EMP_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;\n EMPS EMP_TABLE;\nBEGIN\n IF N IS NULL OR N < 1 THEN\n DBMS_OUTPUT.PUT_LINE('INVALID VALUE OF PASSED PARAMETER.');\n RETURN;\n END IF;\n SELECT SYSDATE INTO T1 FROM DUAL;\n SELECT EMPLOYEE_ID\n BULK COLLECT\n INTO EMPS\n FROM HR.EMPLOYEES\n ORDER BY 1;\n \n FOR I IN 1..N LOOP\n FOR J IN 1..EMPS.COUNT LOOP\n UPDATE HR.EMPLOYEES SET SALARY = SALARY * 1 WHERE EMPLOYEE_ID=EMPS(J) ;\n COMMIT;\n END LOOP;\n END LOOP;\n DBMS_OUTPUT.PUT_LINE(TO_CHAR( (SYSDATE-T1)*24*60*60 , '999,999.99') || ' s');\nEND TEST_UPDATE;\n\/\n<\/pre><\/div>\n\n\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 23 04:41:43 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---------------- ------------\ncortex OPEN\n \nSQL> SET SERVEROUTPUT ON\nCREATE OR REPLACE PROCEDURE HR.TEST_UPDATE( N NUMBER )\nIS\n \/* This procedure performs sequential update on HR.EMPLOYEES table for testing\n purposes. It doesn\u2019t make any real changes on data but real transactions\n will be generated\n *\/\n T1 DATE;\n TYPE EMP_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;\n EMPS EMP_TABLE;\nBEGIN\n IF N IS NULL OR N < 1 THEN\n DBMS_OUTPUT.PUT_LINE('INVALID VALUE OF PASSED PARAMETER.');\n RETURN;\n END IF;\n SELECT SYSDATE INTO T1 FROM DUAL;\n SELECT EMPLOYEE_ID\n BULK COLLECT\n INTO EMPS\n FROM HR.EMPLOYEES\n ORDER BY 1;\n \n FOR I IN 1..N LOOP\n FOR J IN 1..EMPS.COUNT LOOP\n UPDATE HR.EMPLOYEES SET SALARY = SALARY * 1 WHERE EMPLOYEE_ID=EMPS(J) ;\n COMMIT;\n END LOOP;\n END LOOP;\n DBMS_OUTPUT.PUT_LINE(TO_CHAR( (SYSDATE-T1)*24*60*60 , '999,999.99') || ' s');\nEND TEST_UPDATE;\n\/SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30\n \nProcedure created.\n<\/pre><\/div>\n\n\nComo podemos ver, o modo vigente \u00e9 o Maximum Performance (que \u00e9 o valor padr\u00e3o):<\/p>\n\n\n
\n[oracle@fornix2 ~]$ dgmgrl sys\/oracle@CORTEXDR\nDGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 04:47:14 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates. All rights reserved.\n \nWelcome to DGMGRL, type "help" for information.\nConnected to "cortexDR"\nConnected as SYSDBA.\nDGMGRL> SHOW CONFIGURATION;\n \nConfiguration - cortex\n \n Protection Mode: MaxPerformance\n Members:\n cortex - Primary database\n cortexdr - Physical standby database\n \nFast-Start Failover: Disabled\n \nConfiguration Status:\nSUCCESS (status updated 45 seconds ago)\n<\/pre><\/div>\n\n\nAntes de iniciar os testes, vamos definir os par\u00e2metros “NetTimeout”, “ReopenSecs” e “LogXptMode”:<\/p>\n\n\n
\nDGMGRL> EDIT DATABASE CORTEXDR SET PROPERTY NetTimeout=20;\nProperty "nettimeout" updated\nDGMGRL> EDIT DATABASE CORTEXDR SET PROPERTY ReopenSecs=20;\nProperty "reopensecs" updated\nDGMGRL> EDIT DATABASE CORTEXDR SET PROPERTY LogXptMode='SYNC';\nProperty "logxptmode" updated\n<\/pre><\/div>\n\n\nJ\u00e1 \u00e9 poss\u00edvel ver a mudan\u00e7a de valor do par\u00e2metro tamb\u00e9m no primary:<\/p>\n\n\n
\nSQL> SHO PARAMETER ARCHIVE_DEST_2\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\nlog_archive_dest_2 string service="cortexdr", SYNC AFFIR\n M delay=0 optional compression\n =disable max_failure=0 reopen=\n 20 db_unique_name="cortexdr" n\n et_timeout=20, valid_for=(onli\n ne_logfile,all_roles)\n<\/pre><\/div>\n\n\nFinalmente, alterando o Protection Mode para Maximum Availability no Standby:<\/p>\n\n\n
\nDGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;\nSucceeded.\nDGMGRL> SHOW CONFIGURATION;\n \nConfiguration - cortex\n \n Protection Mode: MaxAvailability\n Members:\n cortex - Primary database\n cortexdr - Physical standby database\n \nFast-Start Failover: Disabled\n \nConfiguration Status:\nSUCCESS (status updated 23 seconds ago)\n<\/pre><\/div>\n\n\nAgora vamos simular o comportamento do Data Guard quando o Standby ficar indispon\u00edvel. Executando a procedure que criamos no Primary, e analisando o seu tempo de execu\u00e7\u00e3o (49 segundos):<\/p>\n\n\n
\nSQL> set serveroutput on\nSQL> exec HR.TEST_UPDATE(1000);\n49.00 s\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\nExecutando novamente a procedure no primary, mas ap\u00f3s o seu disparo, realizando um abort no ambiente standby:<\/p>\n\n\n
\nSQL> exec HR.TEST_UPDATE(1000);\n<\/pre><\/div>\n\n\nSQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;\n \nINSTANCE_NAME STATUS\n---------------- ------------\nCORTEXDR MOUNTED\n \nSQL> SHU ABORT;\nORACLE instance shut down.\n<\/pre><\/div>\n\n\nPercebemos que o processo continuou no primary e finalizou com sucesso:<\/p>\n\n\n
\nSQL> exec HR.TEST_UPDATE(1000);\n42.00 s\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\nEnquanto isso, no alert do Data Broker, temos o report de que o standby est\u00e1 indispon\u00edvel:<\/p>\n\n\n
\n[oracle@fornix1 trace]$ cd \/oracle\/19.3.0\/base\/diag\/rdbms\/cortex\/cortex\/trace\n[oracle@fornix1 trace]$ tail -1000f drccortex.log\n \n...\n \n2021-03-23T05:03:58.284-03:00\nError: The actual protection level 'Resynchronization' is different from the configured protection mode 'MaxAvailability'.\nRedo transport problem detected: redo transport to database cortexdr has the following error:\n ORA-01034: ORACLE not available\nData Guard Broker Status Summary:\n Type Name Severity Status\n Configuration cortex Warning ORA-16607: one or more members have failed\n Primary Database cortex Error ORA-16810: multiple errors or warnings detected for the member\n Physical Standby Database cortexdr Error ORA-01034: ORACLE not available\n<\/pre><\/div>\n\n\nNo configuration do Data Broker tamb\u00e9m \u00e9 reportado o problema:<\/p>\n\n\n
\nDGMGRL> SHOW CONFIGURATION;\n \nConfiguration - cortex\n \n Protection Mode: MaxAvailability\n Members:\n cortex - Primary database\n Error: ORA-16810: multiple errors or warnings detected for the member\n \n cortexdr - Physical standby database\n Error: ORA-1034: ORACLE not available\n \nFast-Start Failover: Disabled\n \nConfiguration Status:\nERROR (status updated 8 seconds ago)\n<\/pre><\/div>\n\n\nMontando o standby:<\/p>\n\n\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>\n<\/pre><\/div>\n\n\nAp\u00f3s 20 segundos (que foi o valor definido por n\u00f3s), a comunica\u00e7\u00e3o \u00e9 restabelecida:<\/p>\n\n\n
\nDGMGRL> SHOW CONFIGURATION;\n \nConfiguration - cortex\n \n Protection Mode: MaxAvailability\n Members:\n cortex - Primary database\n Warning: ORA-16629: database reports a different protection level from the protection mode\n \n cortexdr - Physical standby database\n Error: ORA-16525: The Oracle Data Guard broker is not yet available.\n \nFast-Start Failover: Disabled\n \nConfiguration Status:\nERROR (status updated 50 seconds ago)\n \nDGMGRL> SHOW CONFIGURATION;\n \nConfiguration - cortex\n \n Protection Mode: MaxAvailability\n Members:\n cortex - Primary database\n cortexdr - Physical standby database\n \nFast-Start Failover: Disabled\n \nConfiguration Status:\nSUCCESS (status updated 8 seconds ago)\n<\/pre><\/div>\n\n\nAlterando o Standby para MAXPROTECTION:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ dgmgrl sys\/oracle@CORTEXDR\nDGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 05:16:43 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates. All rights reserved.\n \nWelcome to DGMGRL, type "help" for information.\nConnected to "cortexDR"\nConnected as SYSDBA.\nDGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;\nSucceeded.\nDGMGRL> SHOW CONFIGURATION;\n \nConfiguration - cortex\n \n Protection Mode: MaxProtection\n Members:\n cortex - Primary database\n cortexdr - Physical standby database\n \nFast-Start Failover: Disabled\n \nConfiguration Status:\nSUCCESS (status updated 19 seconds ago)\n<\/pre><\/div>\n\n\nExecutando novamente a procedure no primary, e logo em seguida realizando o abort do Standby:<\/p>\n\n\n
\nSQL> set serveroutput on\nSQL> exec HR.TEST_UPDATE(1000);\n<\/pre><\/div>\n\n\nSQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;\n \nINSTANCE_NAME STATUS\n---------------- ------------\nCORTEXDR MOUNTED\n \nSQL> SHU ABORT;\nORACLE instance shut down.\n<\/pre><\/div>\n\n\nNo alert do primary j\u00e1 \u00e9 poss\u00edvel ver o problema sendo reportado:<\/p>\n\n\n
\n2021-03-23T05:26:50.793285-03:00\nLGWR (PID:3541): LAD:2 is UNSYNCHRONIZED\nLGWR (PID:3541): All standby destinations have failed\nLGWR (PID:3541): *************************************************************************\nLGWR (PID:3541): WARN: All standby database destinations have failed\nLGWR (PID:3541): WARN: Instance shutdown required to protect primary\nLGWR (PID:3541): *************************************************************************\nLGWR (ospid: ): terminating the instance due to ORA error\n<\/pre><\/div>\n\n\nE a execu\u00e7\u00e3o, que at\u00e9 o momento estava “travada”, finalmente reporta erro (que era o comportamento esperado):<\/p>\n\n\n
\nSQL> set serveroutput on\nSQL> exec HR.TEST_UPDATE(1000);\n \nBEGIN HR.TEST_UPDATE(1000); END;\n \n*\nERROR at line 1:\nORA-03113: end-of-file on communication channel\nProcess ID: 7976\nSession ID: 98 Serial number: 35155\n<\/pre><\/div>\n\n\nPodemos ver que o ambiente primary tamb\u00e9m ficou indispon\u00edvel:<\/p>\n\n\n
\nSQL> exit\nDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n[oracle@fornix1 trace]$\n[oracle@fornix1 trace]$\n[oracle@fornix1 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 23 05:29:10 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle. All rights reserved.\n \nConnected to an idle instance.\n \nSQL> !ps -ef | grep pmon\ngrid 3380 1 0 04:18 ? 00:00:00 asm_pmon_+ASM\noracle 8673 8657 0 05:29 pts\/0 00:00:00 \/bin\/bash -c ps -ef | grep pmon\noracle 8675 8673 0 05:29 pts\/0 00:00:00 grep pmon\n<\/pre><\/div>\n\n\nMontando o standby:<\/p>\n\n\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.\n<\/pre><\/div>\n\n\nSubindo o primary:<\/p>\n\n\n
\nSQL> STARTUP;\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.\nDatabase opened.\n<\/pre><\/div>\n\n\nValidando se est\u00e1 tudo OK:<\/p>\n\n\n
\n[oracle@fornix1 trace]$ dgmgrl sys\/oracle@CORTEX\nDGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 05:33:07 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates. All rights reserved.\n \nWelcome to DGMGRL, type "help" for information.\nConnected to "cortex"\nConnected as SYSDBA.\nDGMGRL> SHOW CONFIGURATION VERBOSE;\n \nConfiguration - cortex\n \n Protection Mode: MaxProtection\n Members:\n cortex - Primary database\n cortexdr - Physical standby database\n \n Properties:\n FastStartFailoverThreshold = '30'\n OperationTimeout = '30'\n TraceLevel = 'USER'\n FastStartFailoverLagLimit = '0'\n CommunicationTimeout = '180'\n ObserverReconnect = '0'\n FastStartFailoverAutoReinstate = 'TRUE'\n FastStartFailoverPmyShutdown = 'TRUE'\n BystandersFollowRoleChange = 'ALL'\n ObserverOverride = 'FALSE'\n ExternalDestination1 = ''\n ExternalDestination2 = ''\n PrimaryLostWriteAction = 'CONTINUE'\n ConfigurationWideServiceName = 'cortex_CFG'\n \nFast-Start Failover: Disabled\n \nConfiguration Status:\nSUCCESS\n<\/pre><\/div>\n\n\nVoltando o Data Guard ao modo original do ambiente (MAXPERFORMANCE):<\/p>\n\n\n
\n[oracle@fornix2 ~]$ dgmgrl sys\/oracle@CORTEXDR\nDGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 05:35:46 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates. All rights reserved.\n \nWelcome to DGMGRL, type "help" for information.\nConnected to "cortexDR"\nConnected as SYSDBA.\nDGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;\nSucceeded.\nDGMGRL> EDIT DATABASE CORTEXDR SET PROPERTY LogXptMode='ASYNC';\nProperty "logxptmode" updated\nDGMGRL> SHOW CONFIGURATION;\n \nConfiguration - cortex\n \n Protection Mode: MaxPerformance\n Members:\n cortex - Primary database\n cortexdr - Physical standby database\n \nFast-Start Failover: Disabled\n \nConfiguration Status:\nSUCCESS (status updated 48 seconds ago)\n \nDGMGRL>\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":"
Verificando que o Redo Apply est\u00e1 OFF: Inicializando o apply: No ambiente primary, vamos criar a procedure abaixo para podermos realizar um teste durante o artigo: Como podemos ver, o modo vigente \u00e9 o Maximum Performance (que \u00e9 o valor padr\u00e3o): Antes de iniciar os testes, vamos definir os par\u00e2metros “NetTimeout”, “ReopenSecs” e “LogXptMode”: J\u00e1 […]<\/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-3038","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\/3038","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=3038"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3038\/revisions"}],"predecessor-version":[{"id":9209,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3038\/revisions\/9209"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3038"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3038"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3038"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}