MONITORING DATA GUARD LOGICAL STANDBY

High Availability / 14/07/2022

A primeira ferramenta que podemos usar para monitorar um Logical Standby é o Alert.log, conforme exemplo abaixo extraído do primary, onde o mesmo não consegue se comunicar com o standby:

[oracle@fornix1 trace]$ pwd
/oracle/19.3.0/base/diag/rdbms/cortex/cortex/trace
[oracle@fornix1 trace]$ tail -f alert_cortex.log
 
TNS-12543: TNS:destination host unreachable
    ns secondary err code: 12560
    nt main err code: 513
 
TNS-00513: Destination host unreachable
    nt secondary err code: 113
    nt OS err code: 0
2021-04-08T04:02:49.765338-03:00
TT00 (PID:3585): Error 12543 received logging on to the standby

A view “V$DATAGUARD_STATUS” também nos reporta os eventos (incluindo mensagens de erro) reportados no alert:

[oracle@fornix1 trace]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 8 04:03:37 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> ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
 
SELECT FACILITY, ERROR_CODE, TIMESTAMP, MESSAGE
FROM V$DATAGUARD_STATUS
WHERE TRUNC(TIMESTAMP)= TRUNC(SYSTIMESTAMP)
ORDER BY TIMESTAMP;
Session altered.
 
SQL>
Session altered.
 
SQL> SQL>   2    3    4
 
...
 
FACILITY                 ERROR_CODE TIMESTAMP
------------------------ ---------- ------------------
MESSAGE
--------------------------------------------------------------------------------
Log Transport Services            0 08-Apr-21 03:59 AM
Beginning to archive T-1.S-188 (SCN:0x0000000000622b1b-SCN:0x00000000006230fe)
 
Log Transport Services            0 08-Apr-21 03:59 AM
Completed archiving T-1.S-188 (SCN:0x0000000000622b1b-SCN:0x00000000006230fe)
 
Log Transport Services        12543 08-Apr-21 04:02 AM
Error 12543 received logging on to the standby
 
 
24 rows selected.

A view “DBA_LOGSTDBY_EVENTS” nos reporta as atividades do SQL Apply, incluindo eventuais erros:

[oracle@fornix2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 8 04:15:31 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> ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
 
SELECT EVENT_TIME, STATUS, EVENT
FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIMESTAMP, COMMIT_SCN, CURRENT_SCN;
Session altered.
 
SQL>
Session altered.
 
SQL> SQL>   2    3
 
...
 
EVENT_TIME
------------------
STATUS
--------------------------------------------------------------------------------
EVENT
--------------------------------------------------------------------------------
06-Apr-21 05:07 AM
ORA-16128: User initiated stop apply successfully completed
 
 
 
8 rows selected.

Já a view “DBA_LOGSTDBY_LOG” fornece informações sobre os archived logs que estão sendo aplicados pelo SQL Apply:

[oracle@fornix2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 8 04:19:44 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> ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
 
SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS F_SCN#,
NEXT_CHANGE# AS N_SCN#, TIMESTAMP,
DICT_BEGIN, DICT_END,
THREAD# AS THR#, APPLIED
FROM DBA_LOGSTDBY_LOG
ORDER BY SEQUENCE#;
Session altered.
 
SQL>
Session altered.
 
SQL> SQL>   2    3    4    5    6
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP          DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------ --- --- ---------- --------
+DG_RECO/cortexdr/archivelog/cortex1_177_1039033628.arc
       177    6214935    6215286 05-Apr-21 02:25 PM YES YES          1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_178_1039033628.arc
       178    6215286    6215305 05-Apr-21 02:25 PM NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_179_1039033628.arc
       179    6215305    6217319 05-Apr-21 02:26 PM NO  NO           1 YES
 
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP          DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------ --- --- ---------- --------
+DG_RECO/cortexdr/archivelog/cortex1_180_1039033628.arc
       180    6217319    6317466 06-Apr-21 04:17 AM NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_181_1039033628.arc
       181    6317466    6319659 06-Apr-21 04:21 AM NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_182_1039033628.arc
       182    6319659    6319951 06-Apr-21 04:22 AM NO  NO           1 YES
 
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP          DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------ --- --- ---------- --------
+DG_RECO/cortexdr/archivelog/cortex1_183_1039033628.arc
       183    6319951    6319964 06-Apr-21 04:22 AM NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_184_1039033628.arc
       184    6319964    6322560 06-Apr-21 04:25 AM NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_185_1039033628.arc
       185    6322560    6323095 06-Apr-21 04:29 AM NO  NO           1 YES
 
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP          DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------ --- --- ---------- --------
+DG_RECO/cortexdr/archivelog/cortex1_186_1039033628.arc
       186    6323095    6329394 06-Apr-21 04:59 AM NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_187_1039033628.arc
       187    6329394    6433563 08-Apr-21 04:15 AM NO  NO           1 CURRENT
 
+DG_RECO/cortexdr/archivelog/cortex1_188_1039033628.arc
       188    6433563    6435070 08-Apr-21 04:15 AM NO  NO           1 NO
 
 
12 rows selected.

Para termos algumas estatísticas básicas sobre a operação de SQL Apply no standby, podemos utilizar a view “V$LOGSTDBY_STATS”:

[oracle@fornix2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 8 04:25: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> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
 
Database altered.
 
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-Mon-RR HH12:MI AM';
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
 
SELECT SUBSTR(name, 1, 40) AS NAME, SUBSTR(value, 1, 32) AS VALUE
FROM V$LOGSTDBY_STATS;
Session altered.
 
SQL>
Session altered.
 
SQL> SQL>   2
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
logminer session id
1
 
number of preparers
1
 
number of appliers
5
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
server processes in use
9
 
maximum SGA for LCR cache (MB)
100
 
maximum events recorded
10000
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
preserve commit order
TRUE
 
transaction consistency
FULL
 
record skipped errors
Y
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
record skipped DDLs
Y
 
record applied DDLs
N
 
record unsupported operations
N
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
realtime apply
Y
 
apply delay (minutes)
0
 
peak apply rate (bytes/sec)
5223584
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
record skipped PLSQL
N
 
record applied PLSQL
N
 
current apply rate (bytes/sec)
5223584
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
parallel read enabled
N
 
coordinator state
IDLE
 
coordinator startup time
08-Apr-21 04:25 AM
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
coordinator uptime (seconds)
10
 
txns received from logminer
7
 
txns assigned to apply
7
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
txns applied
7
 
txns discarded during restart
0
 
large txns waiting to be assigned
0
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
session restart SCN
6330796
 
rolled back txns mined
28
 
DDL txns mined
0
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
CTAS txns mined
0
 
bytes of redo mined
11161948
 
bytes paged out
0
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
pageout time (seconds)
0
 
bytes checkpointed
191096
 
checkpoint time (seconds)
0
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
system idle time (seconds)
5
 
standby redo logs mined
0
 
archived logs mined
5
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
gap fetched logs mined
1
 
standby redo log reuse detected
0
 
logfile open failures
0
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
current logfile wait (seconds)
0
 
total logfile wait (seconds)
0
 
thread enable mined
0
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
thread disable mined
0
 
distinct txns in queue
0
 
number of logged PLSQL procedures mined
7
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
waits due to full transaction queue
0
 
reque due to full transaction queue
0
 
resize due to full transaction queue
0
 
 
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
waits due to full redo queue
0
 
waits due to full merge queue
29
 
 
53 rows selected.

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.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish