Loading...

Details

Type: Feature Request
Resolution: Done
Priority: Major
Fix Version/s: 2.2.0.Alpha1
Affects Version/s: 1.7.0.CR1
Component/s: oracle-connector
Labels:
None

Blocked:
False
Ready:
False
Hierarchy Progress:
0
Hierarchy Progress Bar:

0% 0%
Release Note Text:
undefined
Target Release:

2.2.GA
Git Pull Request:
https://github.com/debezium/debezium/pull/4170

SFDC Cases Links:
SFDC Cases Counter:

Description

Problem Information

It's not possible to use Dezezium with Oracle CDC when the database is "opened" as read-only.
Before extracting data from the archive log, Debezium creates a control table named LOG_MINING_FLUSH. This will fail in the case that the database is opened/mounted as read-only, and also when the login/user doesn't have CREATE TABLE priviledge/grant.

Use Case

Read-only Standby instances

Some companies provision an Oracle server replica for disaster recovery purposes. Using this replica as a source brings some benefits:

Avoid extra load on a production server that could affect the end-user.
Avoid concurrency with the database replication in the production server.
Avoid consuming throughput from the production server and network.
Avoid the necessity of buying more hardware/network links.
It reduces the procedures required in the case of a disaster happen with main database hardware.
It better utilizes an idle resource that is the replica server

However, a setup like this could raise latency, due to the two levels of replication. But it's a tradeoff that can be accepted in there is no real-time requirements for data freshness, like in most data warehouse and data lake use cases.

Read-only user/login privileges

Using a login with reduced privileges helps in compliance with external audit and corporate governance rules.

Context Information

Discussion on gitter.im

Juarez Rudsatz @juarezr 2021-08-11 00:15:
I have a standby Oracle RAC instance that is configured as a read-only replica for disaster recovery purposes. It has ARCHIVE_MODE enabled and SUPPLEMENTAL LOG for all DATABASE/SCHEMA. While testing, I have noticed that creates a table name LOG_MINING_FLUSH. This isn't possible/allowed for our standby database. There is an alternative configuration for using Debezium in this case? How can I accomplish this?

Chris Cranford @Naros 2021-08-11 09:41
Hi @juarezr unfortunately there is not; however could you raise a Jira issue on this with your environment details and we'll discuss to ssee if an alternative can be achieved for read-only standbys.

Database information

SQL> SELECT * FROM V$VERSION;

Name         |Value                                                                                 |
-------------+--------------------------------------------------------------------------------------+
BANNER       |Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production                   |
BANNER_FULL  |Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production¶Version 19.9.0.0.0|
BANNER_LEGACY|Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production                   |
CON_ID       |0                                                                                     |

SQL> SELECT *SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK FROM V$DATABASE;

LOG_MODE  |SUPPLEMENTAL_LOG_DATA_MIN|SUPPLEMENTAL_LOG_DATA_PK|SUPPLEMENTAL_LOG_DATA_ALL|
----------+-------------------------+------------------------+-------------------------+
ARCHIVELOG|YES                      |YES                     |NO                       |

SQL> SELECT * FROM USER_ROLE_PRIVS WHERE USERNAME='CDC_USER';

USERNAME        |GRANTED_ROLE        |ADMIN_OPTION|DELEGATE_OPTION|DEFAULT_ROLE|OS_GRANTED|COMMON|INHERITED|
----------------+--------------------+------------+---------------+------------+----------+------+---------+
CDC_USER        |EXECUTE_CATALOG_ROLE|NO          |NO             |YES         |NO        |NO    |NO       |SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='CDC_USER';

GRANTEE         |PRIVILEGE             |ADMIN_OPTION|COMMON|INHERITED|
----------------+----------------------+------------+------+---------+
CDC_USER        |SQL> SELECT *ANY TABLE      |NO          |NO    |NO       |
CDC_USER        |SQL> SELECT *ANY DICTIONARY |NO          |NO    |NO       |
CDC_USER        |SQL> SELECT *ANY TRANSACTION|NO          |NO    |NO       |
CDC_USER        |CREATE SESSION              |NO          |NO    |NO       |

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='CDC_USER';

GRANTEE         |PRIVILEGE             |ADMIN_OPTION|COMMON|INHERITED|
----------------+----------------------+------------+------+---------+
CDC_USER        |SQL> SELECT *ANY TABLE      |NO          |NO    |NO       |
CDC_USER        |SQL> SELECT *ANY DICTIONARY |NO          |NO    |NO       |
CDC_USER        |SQL> SELECT *ANY TRANSACTION|NO          |NO    |NO       |
CDC_USER        |CREATE SESSION        |NO          |NO    |NO       |

SQL> SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='CDC_USER';

GRANTEE|OWNER|TABLE_NAME|GRANTOR|PRIVILEGE|GRANTABLE|HIERARCHY|COMMON|TYPE|INHERITED|
-------+-----+----------+-------+---------+---------+---------+------+----+---------+
NO ROWS FOUND;

SQL> SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SQL> SELECT *GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'CDC_USER');

GRANTEE             |OWNER |TABLE_NAME                  |GRANTOR|PRIVILEGE|GRANTABLE|HIERARCHY|COMMON|TYPE   |INHERITED|
--------------------+------+----------------------------+-------+---------+---------+---------+------+-------+---------+
EXECUTE_CATALOG_ROLE|SYS   |PIDL                        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_LOCK                   |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_INTERNAL_SAFE_SCN      |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_PIPE                   |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_ALERT                  |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_RULE_EXIMP             |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_PLUGTS                 |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_PLUGTSP                |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_TTS                    |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_PITR                   |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_DISTRIBUTED_TRUST_ADMIN|SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_RLS                    |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_XDS                    |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_LOGMNR                 |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_FGA                    |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_REDEFINITION           |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_FILE_TRANSFER          |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_SHARED_POOL            |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_TRANSFORM_EXIMP        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |OUTLN_PKG                   |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_METADATA_BUILD         |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_METADATA_DPBUILD       |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPP$PROC                   |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPD$DATA                   |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPD$DATA_INT               |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPV$FT_INT                 |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_SERVER_ALERT_EXPORT    |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_WORKLOAD_CAPTURE       |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_WORKLOAD_REPLAY        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_REPCAT                 |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_STREAMS_TABLESPACE_ADM |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_STREAMS_ADM            |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_STREAMS_AUTH           |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_APPLY_ADM              |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_CAPTURE_ADM            |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_PROPAGATION_ADM        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_FILE_GROUP             |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_COMPARISON             |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_XSTREAM_ADM            |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_STREAMS_LCR_INT        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_STREAMS_MT             |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_STREAMS_SM             |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_STREAMS_ADM_UTL_INT    |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_APPLY_POSITION         |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_STREAMS_RPC            |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_CAPTURE_SWITCH_ADM     |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_XSTREAM_ADM_UTL        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_XSTREAM_UTL_IVK        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_XSTREAM_GG             |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_XSTREAM_GG_ADM         |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_DST                    |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_LOGMNR_D               |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPU$UTILITIES_INT          |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_METADATA_INT           |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_METADATA_UTIL          |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPV$FT                     |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPC$QUEUE                  |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPC$QUEUE_INT              |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPC$QUE_INT                |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |KUPF$FILE_INT               |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_AUTO_TASK_EXPORT       |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMSHSXP                    |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMSZEXP_SYSPKGGRNT         |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_LOGMNR_LOGREP_DICT     |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |LOGMNR_EM_SUPPORT           |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_RULE_COMPATIBLE_90     |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |AS_REPLAY                   |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_AQADM                  |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_AQ_IMPORT_INTERNAL     |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_AQ                     |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_AQIN                   |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_AQJMS_INTERNAL         |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_AQELM                  |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_SQLPATCH               |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DIANA                       |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_REDACT                 |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |ORDIMDPCALLOUTS             |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |SDO_RDF_EXP_IMP             |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |CDBVIEW                     |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_REGISTRY               |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_PDB_ALTER_SHARING      |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_PDB_APP_CON            |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_PDB                    |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_EXPORT_EXTENSION_I     |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |AMGT$DATAPUMP               |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_WRR_STATE              |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_GOLDENGATE_ADM         |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_STREAMS_ADM_IVK        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_APPLY_ADM_IVK          |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_CAPTURE_ADM_IVK        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |TSDP$DATAPUMP               |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_HADOOP_INTERNAL        |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_DATAPUMP_INT           |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |DBMS_NETWORK_ACL_ADMIN      |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|SYS   |PSTDY_DATAPUMP_SUPPORT      |SYS    |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |
EXECUTE_CATALOG_ROLE|AUDSYS|DBMS_AUDIT_MGMT             |AUDSYS |EXECUTE  |NO       |NO       |NO    |PACKAGE|NO       |

Attachments

Issue Links

account is impacted by

DBZ-6025 Allow reading from Oracle read-only physical Oracle

Open

links to

RHEA-2023:120698 Red Hat build of Debezium 2.3.4 release

Allow reading from read-only Oracle standby disaster/recovery