Debezium Oracle Logminer - Archivelogs Retention

[Yuri Stapassoli de Sá]

Hi All,

I'm working with Debezium connector for Oracle with logminer and have some doubts about archivelog retention:

1. Is there a formula for setting the value of the db_recovery_file_dest_size parameter from the estimated volume for the database?

2. Is there any way to know which archivelogs can be deleted from the last readed record by debezium?

I imagine that there may be a periodic cleaning of the archivelogs, my concern is to delete some file that has not yet been consumed by debezium.

Thanks for any advice

[Chris Cranford]

Hi Yuri -

The db_recovery_file_dest_size is based on both needed retention and the frequency of logs generated.  Ideally, you want Oracle to only generate 5 log switches during peak DML operations, so your redo logs may need to be first resized to satisfy this point which directly relates to the size of one archive log.  With that information coupled with how long you want to retain archive logs online will then safely give you the expected volume size for this configuration parameter.  So if your seeing ~5 log switches per hour and your redo log size is 1GB per group and you want to retain archive logs for 48 hours, you would need approximately 168GB of space to satisfy normal operations load for that 48 hour period.

With regard to deleting archive logs, this is really tricky.  The best approach to guarantee no connector failure would be to look at the connector's offset or JMX metrics and only delete archive logs that have SCN ranges that are before the "CommitSCN".  This SCN value represents the high watermark point where the connector will safely resume from should the connector be restarted, or in Oracle terms represents the point where the oldest in-progress transaction that hasn't yet been rolled back or committed began.  If you don't leverage long running transactions in your system, normally this SCN stays relatively current.  If the connector is down for a period of time, this SCN will very likely be in some archive log.  It's probably a good idea to monitor the connector to see on average how far behind it may typically lag, usually this is a log switch or two depending on system load but does catch up to the current redo log relatively quickly assuming a properly tuned database.

As for periodically cleaning archive logs, be sure to use RMAN for this.  I've seen lately where users are just moving the files off the file-system but are not using RMAN to mark the V$ARCHIVED_LOG records that the files no longer exist.  We then see reports of "missing logfile" or that the file cannot be read when attempting to perform a mining session and it's almost always due to the file no longer being available but the records in Oracle were not cross-checked for consistency with RMAN, making Debezium believe those logs were available for mining.

HTH,
Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/160d869e-2954-484b-9da2-6602376bd580n%40googlegroups.com.