-- Enable archivelog
conn sys/SECRET as sysdba
alter system set db_recovery_file_dest_size = 10G;
-- mkdir this path!
alter system set db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;

shutdown immediate
startup mount
alter database archivelog;
alter database open;

-- Must enable database supplemental logging (MIN)
-- check with: select supplemental_log_data_min from v$database; -> YES
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- To check per-table supplemental logging use
-- select table_name, always, log_group_type from all_log_groups;

-- not sure what these are for (from Debezium oracle setup guide)
conn sys/SECRET@//localhost:1521/MYSID as sysdba
CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/MYSID/logminer_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
conn sys/SECRET@//localhost:1521/MYPDB as sysdba
CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/MYSID/MYPDB/logminer_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- Make a logminer user
conn sys/SECRET@//localhost:1521/MYSID as sysdba
CREATE USER c##logminer IDENTIFIED BY lm
  DEFAULT TABLESPACE logminer_tbs
  QUOTA UNLIMITED ON logminer_tbs
  CONTAINER=ALL;

GRANT SELECT_CATALOG_ROLE TO c##logminer CONTAINER=ALL;
-- Check with: select privilege from user_role_privs;

GRANT CREATE SESSION TO c##logminer CONTAINER=ALL;
GRANT SET CONTAINER TO c##logminer CONTAINER=ALL;
-- Snapshot: Read data at particular time; could be per-table
GRANT FLASHBACK ANY TABLE TO c##logminer CONTAINER=ALL;
-- Snapshot: Lock tables during schema snapshot; could be per-table
GRANT LOCK ANY TABLE TO c##logminer CONTAINER=ALL;
GRANT CREATE TABLE TO c##logminer CONTAINER=ALL;
GRANT CREATE SEQUENCE TO c##logminer CONTAINER=ALL;

GRANT SELECT ON V_$DATABASE to c##logminer CONTAINER=ALL;
GRANT SELECT ON V_$LOG TO c##logminer CONTAINER=ALL;
GRANT SELECT ON V_$LOGFILE TO c##logminer CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##logminer CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##logminer CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_LOGS TO c##logminer CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##logminer CONTAINER=ALL;

GRANT LOGMINING TO c##logminer CONTAINER=ALL;
-- Check with: select privilege from user_sys_privs;

GRANT EXECUTE ON DBMS_LOGMNR TO c##logminer CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##logminer CONTAINER=ALL;
-- Check with: select privilege, table_name from user_tab_privs where table_name like 'DBMS_LOGMNR%';


-- Create user for use with the "schema" that the connector works with
conn sys/SECRET@//localhost:1521/MYPDB as sysdba
CREATE USER myuser IDENTIFIED BY SECRET;
GRANT ALL PRIVILEGES TO myuser;
GRANT UNLIMITED TABLESPACE TO myuser;

-- Create signaling table
CREATE TABLE C##LOGMINER.DEBEZIUM_SIGNAL (id VARCHAR(42) PRIMARY KEY, type VARCHAR(32) NOT NULL, data VARCHAR(2048) NULL);
ALTER TABLE C##LOGMINER.DEBEZIUM_SIGNAL ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
