Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-3866

Allow reading from read-only Oracle standby disaster/recovery

    XMLWordPrintable

Details

    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

        Activity

          People

            ccranfor@redhat.com Chris Cranford
            juarezr@gmail.com Juarez Rudsatz (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated: