How to debug problematic SQL query in target database when apply process crashes without tracefile

Problem Description

This article will describe how to debug problematic SQL query who caused apply process crash unexpectedly without trace file.

Solution

There could be many reasons why apply process will crash without any record in the log and no trace file too. Usually it's caused by OS in UNIX based machines. To be sure the problem is caused by OS you can check /var/log/messages (for linux Red Hat distributions; use different path for others) and see if there is note from time when apply process stopped working. If it's your case contact dbvisit support with these rows.

If it's not your case see below lines:

Start your apply process without deamon variable: i.e.:

EXEC="/home/oracle/dbvrep/280/dbvrep"
$EXEC --daemon --ddcfile /home/oracle/dbvrep/280/LOAD/LOAD-APPLY.ddc start APPLY

change to:

EXEC="/home/oracle/dbvrep/280/dbvrep"
$EXEC --ddcfile /home/oracle/dbvrep/280/LOAD/LOAD-APPLY.ddc start APPLY

Can happen apply process will start working without any problem for long time, but in the future stop again. You can combine screen application or it's equivalent and starting dbvrep without deamon variable. The variable means apply process is start in foreground instead of background. Therefore if you close your session apply process will be killed too.

When apply process will die you'll see more details, i.e.:

dbvrep --ddcfile LOAD-APPLY.ddc start apply
Initializing......done
DDC loaded from database (371 variables).
Dbvisit Replicate version 2.7.20_84_gc2b72a7_RQ_2611
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file LOAD-APPLY.ddc loaded.
Starting process APPLY...Created pid file /home/oracle/dbvrep/LOAD/log/dbvrep_APPLY_LOAD.pid.
started
Thread assert failed: -6199: [0004.01a.00152db6] Conflict on LCR 10543806 while trying to rollback/rollforward the changes. at apply_applier.c.285 for advance_to_next_LCR: Error 0
Perl exited with active threads:
10 running and unjoined
0 finished and unjoined
2 running and detached

In case above the problem is caused by conflict. Therefore you can change dbvisit settings and create logon trigger to audit all changes on target server for dbvrep user.

Edit apply ddc file with below rows:

memory_set _THREADED_OCI = NO
memory_set DEBUG_LEVEL=65536

Variable _THREADED_OCI could help with proper output to trace file. DEBUG_LEVEL is there to adjust logged data.

When conflict will come in to account pack all the trace files saved in directory with pattern DBVREP in <DIAG_DEST>/..../trace and upload them to dropbox link provided by dbvisit. Disable trigger and delete rows added to apply ddc file.

If trace file will be created bundle it too.