Debezium Oracle Connector - additional jmx streaming metrics empty

[Peter Fischer]

Hello,

we configured successfully the Oracle Debezium connector and are receiving messages for any related database changes. We also activated the jmx exporter to get the metrics into prometheus to build grafana dashboards upon these dates.

The issue we are facing is, that the metrics for the additional streaming  mbean are not showing any values when looking at them in jconsole. But not all for them are empty. For example LastEvent is populated and is showing a SCN number, but the value for CurrentScn is Null. Also for all other SCN related topics the value is Null.

The question we got is, if we have to configure something, for example in the connect.yaml, that these values are getting set? Furthermore we would like to know, if this is also the reason, why we don´t see these metrics in Prometheus. We see for example ScnFreezeCount in Prometheus but not CurrentScn.

If anyone could help us out, this would be great.

Thanks and regards

Peter

[Shiraz Kamal]

Are you using xstream or logminer

--
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/2e999a1f-5d33-44a3-8f34-d8852905a567n%40googlegroups.com.

--

Thanks & Regards

Shiraz

[Peter Fischer]

Hello, 

we are using XStream on an Oracle RDS, because our DBA said, it is not possible to use LogMiner on a Oracle because of insufficient privilieges. 

[Jailton Silva]

Hello Team, 

Out of curiosity, what version of the oracle bank?

Jailton Silva

Sales Account Manager

Red Hat

jaisilva@redhat.com
M: +551194191-6381

@RedHat   Red Hat  Red Hat

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/431560bc-1e74-4f23-8a33-65849fbd1b0cn%40googlegroups.com.

[Peter Fischer]

We are using an Oracle 19c

[Chris Cranford]

Hi Peter -

So depending on the Oracle adapter you're using (in your case Xstream), not every metric is going to be populated.  In fact, I would say a vast majority of the specialized streaming metrics for the Oracle connector are specific to LogMiner and not to Xstream.  We could probably do better in the documentation by clarifying this to avoid any confusion.  We could also probably look at using different MBean implementations as another way to reduce what you see to avoid the confusion too, but in either case, that explains the behavior.

Regarding LogMiner vs Xstream, I'm a bit perplexed about the privileges concern.  We do cover all the grants needed in the documentation [1], but none of them that I am aware are problematic on Amazon RDS.  Perhaps your DBA simply does not want to expose LogMiner for other reasons.

[1]: https://debezium.io/documentation/reference/stable/connectors/oracle.html#creating-users-for-the-connector

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/785f655d-c1e3-44d7-8908-7dff776b67fan%40googlegroups.com.

[Peter Fischer]

Hi Chris,

thanks for your reply. So if we would like to know the CurrentSCN, could we then instead parse the LastEvent metric and use the SCN number, listed there? Is this the same as CurrentSCN when using LogMiner?

In regards to AWS RDS and LogMiner, our DBA said, then standard AWS admin user has not all the rights to do all the configurations described in the documentation. That is why we switched to Xstream. Do you ever used an Oracle RDS with Debezium, so that we could say to our DBA, please look into this again, because it should work!

Regards

Peter 

[Chris Cranford]

Hi Peter -

So the LastEvent metric is in most cases close but will naturally lag behind slightly to the real CURRENT_SCN for a variety of reasons.  Keep in mind that when using Xstream, all transactions are captured by the capture process, batched into transactions by the coordinator process, and then queued for delivery, which is where the Xstream client gets the notification of the event on the callback queue.  There are some corner case scenarios where this metric may be much farther behind such as situations where someone performs a bulk operation and the connector does not capture that table or if some integration or hot back up process causes the SCN number to jump by a large factor.  In these cases, the LastEvent metric will catch-up to the CURRENT_SCN only once a new event is read and dispatched to the connector's callback handler.

This entire process differs with the LogMiner integration because we require knowledge of the CURRENT_SCN, so we get this value directly from V$DATABASE on each mining loop.  This value is then published to the streaming metrics on each loop, which keeps it relatively up-to-date with the current state of the database.  The main concern with doing this in Xstream for the sake of metrics is that it would require a separate connection as we cannot perform specific actions on the same connection used for the Xstream LCR callback handler.

As for AWS, it would be good to know what you were unable to do.  I haven't tested this myself due to the licensing models AWS has, but knowing what the limitations seem to be might help us work through those.

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/e8efae97-9b8d-4dd2-821b-0a5c0959c120n%40googlegroups.com.

[David Ridge]

Hi Peter and Chris,

Just on the AWS Oracle RDS question. I just ran some tests and Debezium is working fine on RDS with LogMiner.

With RDS, you don't have full SYSDBA privileges but there is equivalent RDS API's for most commands, see:  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.System.html#Appendix.Oracle.CommonDBATasks.TransferPrivileges.

Things like system permissions and supplemental logging require a different set of commands (not dissimilar to RDS Postgres where you don't have superuser, just rds_superuser).

The DBA's shouldn't be pushing back on this, they need to learn these AWS equivalents to survive in the Cloud.

Peter, also, just in case you're not aware, (as per the warning in the Debezium documentation) using XStreams invokes the (very expensive) Golden Gate license.

Below, I'll paste a few of the equivalent commands, I hope that helps.

Cheers

  --GRANT EXECUTE ON DBMS_LOGMNR TO dbzuser;
  exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','DBZUSER','EXECUTE');
  --GRANT EXECUTE ON DBMS_LOGMNR_D TO dbzuser;
  exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D','DBZUSER','EXECUTE');
  --GRANT SELECT on V_$LOGMNR_LOGS to db_user
  exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS','DBZUSER','SELECT');  
  --GRANT SELECT on V_$LOGMNR_CONTENTS to db_user
  exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','DBZUSER','SELECT');  
  GRANT LOGMINING to dbzuser; -– Required only if the Oracle version is 12c or later.
   
 
--- Supplemental Logging
  SELECT supplemental_log_data_min FROM v$database;
  --ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
  begin
    rdsadmin.rdsadmin_util.alter_supplemental_logging(
        p_action => 'ADD');
end;
/
-- Archive Retention
begin
    rdsadmin.rdsadmin_util.set_configuration(
        name  => 'archivelog retention hours',
        value => '24');
end;
/
commit;

 -- Grants
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG','DBZUSER','SELECT');  
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG_HISTORY','DBZUSER','SELECT');  
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS','DBZUSER','SELECT');  
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','DBZUSER','SELECT');  
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_PARAMETERS','DBZUSER','SELECT');  
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGFILE','DBZUSER','SELECT');  
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','DBZUSER','SELECT');  
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVE_DEST_STATUS','DBZUSER','SELECT');  
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$TRANSACTION','DBZUSER','SELECT'); 

[Chris Cranford]

Hi David,

Thanks a lot for this information.  I've logged DBZ-5391 [1] with these details.  If there is anything else you believe we need in the documentation, feel free to annotate the issue with those details as I think this will help everyone going forward if this is documented well.

Thanks,
Chris

[1]: https://issues.redhat.com/browse/DBZ-5391

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/1841cdb1-856a-43ad-a519-a5959951001cn%40googlegroups.com.