6.4. Debezium と連携させるための Oracle の設定

Oracle LogMiner に必要な設定

ORACLE_SID=ORACLCDB dbz_oracle sqlplus /nolog

CONNECT sys/top_secret AS SYSDBA
alter system set db_recovery_file_dest_size = 10G;
alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
-- Should now "Database log mode: Archive Mode"
archive log list

exit;

Oracle AWS RDS LogMiner に必要な設定

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

Oracle AWS RDS LogMiner に必要な設定

exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');

ALTER TABLE inventory.customers ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

コネクターの LogMiner ユーザーの作成

sqlplus sys/top_secret@//localhost:1521/ORCLCDB as sysdba
  CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf'
    SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
  exit;

sqlplus sys/top_secret@//localhost:1521/ORCLPDB1 as sysdba
  CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/logminer_tbs.dbf'
    SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
  exit;

sqlplus sys/top_secret@//localhost:1521/ORCLCDB as sysdba

  CREATE USER c##dbzuser IDENTIFIED BY dbz
    DEFAULT TABLESPACE logminer_tbs
    QUOTA UNLIMITED ON logminer_tbs
    CONTAINER=ALL;

  GRANT CREATE SESSION TO c##dbzuser CONTAINER=ALL; 1
  GRANT SET CONTAINER TO c##dbzuser CONTAINER=ALL; 2
  GRANT SELECT ON V_$DATABASE to c##dbzuser CONTAINER=ALL; 3
  GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL; 4
  GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL; 5
  GRANT SELECT_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL; 6
  GRANT EXECUTE_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL; 7
  GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL; 8
  GRANT LOGMINING TO c##dbzuser CONTAINER=ALL; 9

  GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL; 10
  GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL; 11
  GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL; 12

  GRANT EXECUTE ON DBMS_LOGMNR TO c##dbzuser CONTAINER=ALL; 13
  GRANT EXECUTE ON DBMS_LOGMNR_D TO c##dbzuser CONTAINER=ALL; 14

  GRANT SELECT ON V_$LOG TO c##dbzuser CONTAINER=ALL; 15
  GRANT SELECT ON V_$LOG_HISTORY TO c##dbzuser CONTAINER=ALL; 16
  GRANT SELECT ON V_$LOGMNR_LOGS TO c##dbzuser CONTAINER=ALL; 17
  GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##dbzuser CONTAINER=ALL; 18
  GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##dbzuser CONTAINER=ALL; 19
  GRANT SELECT ON V_$LOGFILE TO c##dbzuser CONTAINER=ALL; 20
  GRANT SELECT ON V_$ARCHIVED_LOG TO c##dbzuser CONTAINER=ALL; 21
  GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##dbzuser CONTAINER=ALL; 22
  GRANT SELECT ON V_$TRANSACTION TO c##dbzuser CONTAINER=ALL; 23

  GRANT SELECT ON V_$MYSTAT TO c##dbzuser CONTAINER=ALL; 24
  GRANT SELECT ON V_$STATNAME TO c##dbzuser CONTAINER=ALL; 25

  exit;