A primeira ferramenta que podemos usar para monitorar um Logical Standby \u00e9 o Alert.log, conforme exemplo abaixo extra\u00eddo do primary, onde o mesmo n\u00e3o consegue se comunicar com o standby:<\/p>\n\n\n
\n[oracle@fornix1 trace]$ pwd\n\/oracle\/19.3.0\/base\/diag\/rdbms\/cortex\/cortex\/trace\n[oracle@fornix1 trace]$ tail -f alert_cortex.log\n \nTNS-12543: TNS:destination host unreachable\n ns secondary err code: 12560\n nt main err code: 513\n \nTNS-00513: Destination host unreachable\n nt secondary err code: 113\n nt OS err code: 0\n2021-04-08T04:02:49.765338-03:00\nTT00 (PID:3585): Error 12543 received logging on to the standby\n<\/pre><\/div>\n\n\nA view “V$DATAGUARD_STATUS” tamb\u00e9m nos reporta os eventos (incluindo mensagens de erro) reportados no alert:<\/p>\n\n\n
\n[oracle@fornix1 trace]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 8 04:03:37 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 SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';\nALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;\n \nSELECT FACILITY, ERROR_CODE, TIMESTAMP, MESSAGE\nFROM V$DATAGUARD_STATUS\nWHERE TRUNC(TIMESTAMP)= TRUNC(SYSTIMESTAMP)\nORDER BY TIMESTAMP;\nSession altered.\n \nSQL>\nSession altered.\n \nSQL> SQL> 2 3 4\n \n...\n \nFACILITY ERROR_CODE TIMESTAMP\n------------------------ ---------- ------------------\nMESSAGE\n--------------------------------------------------------------------------------\nLog Transport Services 0 08-Apr-21 03:59 AM\nBeginning to archive T-1.S-188 (SCN:0x0000000000622b1b-SCN:0x00000000006230fe)\n \nLog Transport Services 0 08-Apr-21 03:59 AM\nCompleted archiving T-1.S-188 (SCN:0x0000000000622b1b-SCN:0x00000000006230fe)\n \nLog Transport Services 12543 08-Apr-21 04:02 AM\nError 12543 received logging on to the standby\n \n \n24 rows selected.\n<\/pre><\/div>\n\n\nA view “DBA_LOGSTDBY_EVENTS” nos reporta as atividades do SQL Apply, incluindo eventuais erros:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 8 04:15:31 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 SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';\nALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;\n \nSELECT EVENT_TIME, STATUS, EVENT\nFROM DBA_LOGSTDBY_EVENTS\nORDER BY EVENT_TIMESTAMP, COMMIT_SCN, CURRENT_SCN;\nSession altered.\n \nSQL>\nSession altered.\n \nSQL> SQL> 2 3\n \n...\n \nEVENT_TIME\n------------------\nSTATUS\n--------------------------------------------------------------------------------\nEVENT\n--------------------------------------------------------------------------------\n06-Apr-21 05:07 AM\nORA-16128: User initiated stop apply successfully completed\n \n \n \n8 rows selected.\n<\/pre><\/div>\n\n\nJ\u00e1 a view “DBA_LOGSTDBY_LOG” fornece informa\u00e7\u00f5es sobre os archived logs que est\u00e3o sendo aplicados pelo SQL Apply:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 8 04:19:44 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 SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';\nALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;\n \nSELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS F_SCN#,\nNEXT_CHANGE# AS N_SCN#, TIMESTAMP,\nDICT_BEGIN, DICT_END,\nTHREAD# AS THR#, APPLIED\nFROM DBA_LOGSTDBY_LOG\nORDER BY SEQUENCE#;\nSession altered.\n \nSQL>\nSession altered.\n \nSQL> SQL> 2 3 4 5 6\n \nFILE_NAME\n--------------------------------------------------------------------------------\n SEQ# F_SCN# N_SCN# TIMESTAMP DIC DIC THR# APPLIED\n---------- ---------- ---------- ------------------ --- --- ---------- --------\n+DG_RECO\/cortexdr\/archivelog\/cortex1_177_1039033628.arc\n 177 6214935 6215286 05-Apr-21 02:25 PM YES YES 1 YES\n \n+DG_RECO\/cortexdr\/archivelog\/cortex1_178_1039033628.arc\n 178 6215286 6215305 05-Apr-21 02:25 PM NO NO 1 YES\n \n+DG_RECO\/cortexdr\/archivelog\/cortex1_179_1039033628.arc\n 179 6215305 6217319 05-Apr-21 02:26 PM NO NO 1 YES\n \n \nFILE_NAME\n--------------------------------------------------------------------------------\n SEQ# F_SCN# N_SCN# TIMESTAMP DIC DIC THR# APPLIED\n---------- ---------- ---------- ------------------ --- --- ---------- --------\n+DG_RECO\/cortexdr\/archivelog\/cortex1_180_1039033628.arc\n 180 6217319 6317466 06-Apr-21 04:17 AM NO NO 1 YES\n \n+DG_RECO\/cortexdr\/archivelog\/cortex1_181_1039033628.arc\n 181 6317466 6319659 06-Apr-21 04:21 AM NO NO 1 YES\n \n+DG_RECO\/cortexdr\/archivelog\/cortex1_182_1039033628.arc\n 182 6319659 6319951 06-Apr-21 04:22 AM NO NO 1 YES\n \n \nFILE_NAME\n--------------------------------------------------------------------------------\n SEQ# F_SCN# N_SCN# TIMESTAMP DIC DIC THR# APPLIED\n---------- ---------- ---------- ------------------ --- --- ---------- --------\n+DG_RECO\/cortexdr\/archivelog\/cortex1_183_1039033628.arc\n 183 6319951 6319964 06-Apr-21 04:22 AM NO NO 1 YES\n \n+DG_RECO\/cortexdr\/archivelog\/cortex1_184_1039033628.arc\n 184 6319964 6322560 06-Apr-21 04:25 AM NO NO 1 YES\n \n+DG_RECO\/cortexdr\/archivelog\/cortex1_185_1039033628.arc\n 185 6322560 6323095 06-Apr-21 04:29 AM NO NO 1 YES\n \n \nFILE_NAME\n--------------------------------------------------------------------------------\n SEQ# F_SCN# N_SCN# TIMESTAMP DIC DIC THR# APPLIED\n---------- ---------- ---------- ------------------ --- --- ---------- --------\n+DG_RECO\/cortexdr\/archivelog\/cortex1_186_1039033628.arc\n 186 6323095 6329394 06-Apr-21 04:59 AM NO NO 1 YES\n \n+DG_RECO\/cortexdr\/archivelog\/cortex1_187_1039033628.arc\n 187 6329394 6433563 08-Apr-21 04:15 AM NO NO 1 CURRENT\n \n+DG_RECO\/cortexdr\/archivelog\/cortex1_188_1039033628.arc\n 188 6433563 6435070 08-Apr-21 04:15 AM NO NO 1 NO\n \n \n12 rows selected.\n<\/pre><\/div>\n\n\nPara termos algumas estat\u00edsticas b\u00e1sicas sobre a opera\u00e7\u00e3o de SQL Apply no standby, podemos utilizar a view “V$LOGSTDBY_STATS”:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 8 04:25:05 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 DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;\n \nDatabase altered.\n \nSQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';\nALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;\n \nSELECT SUBSTR(name, 1, 40) AS NAME, SUBSTR(value, 1, 32) AS VALUE\nFROM V$LOGSTDBY_STATS;\nSession altered.\n \nSQL>\nSession altered.\n \nSQL> SQL> 2\n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nlogminer session id\n1\n \nnumber of preparers\n1\n \nnumber of appliers\n5\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nserver processes in use\n9\n \nmaximum SGA for LCR cache (MB)\n100\n \nmaximum events recorded\n10000\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\npreserve commit order\nTRUE\n \ntransaction consistency\nFULL\n \nrecord skipped errors\nY\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nrecord skipped DDLs\nY\n \nrecord applied DDLs\nN\n \nrecord unsupported operations\nN\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nrealtime apply\nY\n \napply delay (minutes)\n0\n \npeak apply rate (bytes\/sec)\n5223584\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nrecord skipped PLSQL\nN\n \nrecord applied PLSQL\nN\n \ncurrent apply rate (bytes\/sec)\n5223584\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nparallel read enabled\nN\n \ncoordinator state\nIDLE\n \ncoordinator startup time\n08-Apr-21 04:25 AM\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\ncoordinator uptime (seconds)\n10\n \ntxns received from logminer\n7\n \ntxns assigned to apply\n7\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\ntxns applied\n7\n \ntxns discarded during restart\n0\n \nlarge txns waiting to be assigned\n0\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nsession restart SCN\n6330796\n \nrolled back txns mined\n28\n \nDDL txns mined\n0\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nCTAS txns mined\n0\n \nbytes of redo mined\n11161948\n \nbytes paged out\n0\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\npageout time (seconds)\n0\n \nbytes checkpointed\n191096\n \ncheckpoint time (seconds)\n0\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nsystem idle time (seconds)\n5\n \nstandby redo logs mined\n0\n \narchived logs mined\n5\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\ngap fetched logs mined\n1\n \nstandby redo log reuse detected\n0\n \nlogfile open failures\n0\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\ncurrent logfile wait (seconds)\n0\n \ntotal logfile wait (seconds)\n0\n \nthread enable mined\n0\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nthread disable mined\n0\n \ndistinct txns in queue\n0\n \nnumber of logged PLSQL procedures mined\n7\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nwaits due to full transaction queue\n0\n \nreque due to full transaction queue\n0\n \nresize due to full transaction queue\n0\n \n \nNAME\n--------------------------------------------------------------------------------\nVALUE\n--------------------------------------------------------------------------------\nwaits due to full redo queue\n0\n \nwaits due to full merge queue\n29\n \n \n53 rows selected.\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 primeira ferramenta que podemos usar para monitorar um Logical Standby \u00e9 o Alert.log, conforme exemplo abaixo extra\u00eddo do primary, onde o mesmo n\u00e3o consegue se comunicar com o standby: A view “V$DATAGUARD_STATUS” tamb\u00e9m nos reporta os eventos (incluindo mensagens de erro) reportados no alert: A view “DBA_LOGSTDBY_EVENTS” nos reporta as atividades do SQL Apply, […]<\/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-3594","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\/3594","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=3594"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3594\/revisions"}],"predecessor-version":[{"id":9185,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3594\/revisions\/9185"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3594"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3594"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3594"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}