How to config online redo logs processing on physical standby

[haohoang]

Hello,
It is indicated that Oracdc v2.0.0 has support for online redo logs processing. So can I use this feature on Oracle physical read-only standby?
Thanks,

[averemee-si]

Hi Haohoang,

Thanks for your question!

I will soon add support for checks over V$STANDBY_LOG, at the moment only V$LOG checks are implemented.
I will keep you informed.

Regards,
Aleksei

[averemee-si]

Hi Haohoang,

While I'm working on deploying the standby database, could you please send the result of following query from your standby instance:

select GROUP#,SEQUENCE#, ARCHIVED, STATUS, FIRST_CHANGE#, NEXT_CHANGE#
from   V$STANDBY_LOG

Thanks! 🙏

Regards,
Aleksei

[haohoang]

Thank for your replies!

This is the result when I query on my standby instance:

SQL> select GROUP#,SEQUENCE#, ARCHIVED, STATUS, FIRST_CHANGE#, NEXT_CHANGE#
from  V$STANDBY_LOG 2
 3 ;

  GROUP# SEQUENCE# ARC STATUS   FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- --- ---------- ------------- ------------
    10    1561 YES ACTIVE     11881586
    11     0 NO UNASSIGNED
    12     0 YES UNASSIGNED
    13     0 YES UNASSIGNED

Regards,
Haohoang

[averemee-si]

Thanks!!!

Working on it!

[averemee-si]

Working on it, I'll be back with progress/update next week

[haohoang]

I am looking forward to your new release. Vào 9:48, Th 3, 26 thg 12, 2023 averemee-si ***@***.***> đã viết:

…

Working on it, I'll be back with progress/update next week — Reply to this email directly, view it on GitHub <#53 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMENMG77JH4FAYU5XUN56GDYLI3HJAVCNFSM6AAAAABAZDXUFGVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TSNBWGIZTS> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[averemee-si]

Hi Haohoang,

Please execute using SQL*Plus on physical standby instance connected as SYSDBA (this is just attempt to start Log Miner on physical standby using active standby log)

set serveroutput on
declare
  current_log varchar2(255);
  start_scn number;
  end_scn number;
  no_of_changes number;
begin
  select L.FIRST_CHANGE#, D.CURRENT_SCN, F.MEMBER
  into   start_scn, end_scn, current_log
  from   V$STANDBY_LOG L, V$LOGFILE F, V$DATABASE D
  where  L.STATUS = 'ACTIVE'
    and  L.NEXT_CHANGE# is null
    and  L.GROUP# = F.GROUP#
    and  L.THREAD#=1
    and  F.STATUS is null
    and  rownum = 1;
  DBMS_OUTPUT.PUT_LINE('Working with logfile:' || current_log);
  DBMS_OUTPUT.PUT_LINE('First change=' || start_scn || ', last change=' || end_scn);
  DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => current_log,
    OPTIONS => DBMS_LOGMNR.NEW);
  DBMS_LOGMNR.START_LOGMNR(
    STARTSCN => start_scn,
    ENDSCN => end_scn,
    OPTIONS =>  
      DBMS_LOGMNR.SKIP_CORRUPTION +
      DBMS_LOGMNR.NO_SQL_DELIMITER +
      DBMS_LOGMNR.NO_ROWID_IN_STMT);
  select count(*)
  into   no_of_changes
  from   V$LOGMNR_CONTENTS;
  DBMS_OUTPUT.PUT_LINE('Number of changes in current standby log=' || no_of_changes);  
end;
/

and send me output of this script.
Thanks!!!

[haohoang]

I run the script but it doesn't return anything.
I check the alert log and there are some logs related to the script as follows

LOGMINER: summary for session# = 2147485697
LOGMINER: StartScn: 15480399 (0x0000.00ec364f)
LOGMINER: EndScn: 15492038 (0x0000.00ec63c6)
LOGMINER: HighConsumedScn: 0
LOGMINER: session_flag: 0x0
LOGMINER: SessionFlags_krvxsctx: 0x4000461
LOGMINER: SessionFlags2_krvxsctx: 0x0
LOGMINER: Read buffers: 8
LOGMINER: Memory LWM: limit 10M, LWM 8M, 80%
LOGMINER: Memory Release Limit: 0M

[averemee-si]

Hi Haohoang,

Thanks!
Same as in my two environments (19c on Linux x86_64 and ARM), looks like RDBMS prohibit reading from active standby log. I have the same in alert.log and in session trace file

Trace file /opt/oracle/diag/rdbms/dg11_stby/DG11/trace/DG11_ora_27739.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
Build label:    RDBMS_19.19.0.0.0DBRU_LINUX.ARM64_230331.230608
ORACLE_HOME:    /opt/oracle/product/19c/dbhome_1
System name:    Linux
Node name:      837e4fbcf688
Release:        6.4.16-linuxkit
Version:        #1 SMP PREEMPT Thu Nov 16 10:49:20 UTC 2023
Machine:        aarch64
Instance name: DG11
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 27739, image: oracle@837e4fbcf688 (TNS V1-V3)


*** 2023-12-31T13:20:50.576897+00:00 (CDB$ROOT(1))
*** SESSION ID:(618.50714) 2023-12-31T13:20:50.576974+00:00
*** CLIENT ID:() 2023-12-31T13:20:50.576993+00:00
*** SERVICE NAME:() 2023-12-31T13:20:50.577003+00:00
*** MODULE NAME:(sqlplus@837e4fbcf688 (TNS V1-V3)) 2023-12-31T13:20:50.577007+00:00
*** ACTION NAME:() 2023-12-31T13:20:50.577011+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2023-12-31T13:20:50.577015+00:00
*** CONTAINER ID:(1) 2023-12-31T13:20:50.577020+00:00

krsa_bts_acq_mode_inst_fnl: Unable to acquire global enqueue on B-1156136443.T-1.S-19 mode 3, error=1013 [krvx.c:8478]
<error barrier> at 0xffffe1634540 placed krsa.c@1333
ORA-01013: user requested cancel of current operation

I will try one more trick and will keep you informed

Regards,
Aleksei

[averemee-si]

Hi Haohoang,

Unfortunately, MRPn (Managed Recovery Process(es)) and RFS (Remote File Server) processes on standby locks standby log file in exclusive mode and LogMiner is unable to open standby log file with status='ACTIVE'.

Regards,
Aleksei

[haohoang]

Hi Averemee-si,
Thanks for letting me know.
I really appreciate your work.

Regards,
HaoHoang

[averemee-si]

Hi Haohang,

This issue is completed in #113
For a sample configuration and architecture diagram, see Make physical standby Active (Great?!) Again

Regards,
Alelsei