Oracle (Generic)

-- Replace the {...} placeholders as required
alter system set db_recovery_file_dest_size = {recovery_file_size};
alter system set db_recovery_file_dest = '{recovery_file_location}' scope=spfile;

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

archive log list;

-- Enable database supplement logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- To enable all supplemental logging, run the following SQL statement for each table:
ALTER TABLE {schema}.{table} ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

SELECT NAME, SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;

SELECT GROUP#, LOCATION FROM V$LOGFILE ORDER BY 1, 2;

-- Execute these statements as many times as required for existing INACTIVE and UNUSED log groups
-- Replace {group_number} placeholder and {log_location}, using a comma separated list of filenames if database uses log multiplexing
ALTER DATABASE CLEAR LOGFILE GROUP {group_number};
ALTER DATABASE DROP LOGFILE GROUP {group_number};
ALTER DATABASE ADD LOGFILE GROUP {group_number} ('{log_location}') size 1024M REUSE;

ALTER SYSTEM SWITCH LOGFILE;

-- Execute these statements as many times as required for existing INACTIVE and UNUSED log groups
-- Replace {group_number} placeholder and {log_location}, using a comma separated list of filenames if database uses log multiplexing
ALTER DATABASE CLEAR LOGFILE GROUP {group_number};
ALTER DATABASE DROP LOGFILE GROUP {group_number};
ALTER DATABASE ADD LOGFILE GROUP {group_number} ('{log_location}') size 1024M REUSE;

SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;

-- Execute these statements as many times as required
-- Replace {group_number} placeholder and {log_location}, using a comma separated list of filenames if database uses log multiplexing
ALTER DATABASE ADD LOGFILE GROUP {group_number} ('{log_location}') size 1024M;

SELECT GROUP#, BYTES/1024/1024 SIZE_MB, STATUS FROM V$LOG ORDER BY 1;

-- Replace {...} placeholders as needed
ALTER SESSION SET CONTAINER=CDB$ROOT;
CREATE USER C##STREAMKAP_USER IDENTIFIED BY {password};

ALTER SESSION SET CONTAINER={PDB};
CREATE TABLESPACE STREAMKAP_LOGMINER_TBS DATAFILE {filename} SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
ALTER USER C##STREAMKAP_USER DEFAULT TABLESPACE STREAMKAP_LOGMINER_TBS;
ALTER USER C##STREAMKAP_USER QUOTA UNLIMITED ON STREAMKAP_LOGMINER_TBS;

-- Grant permissions
GRANT CREATE SESSION TO C##STREAMKAP_USER CONTAINER=ALL; 
GRANT SET CONTAINER TO C##STREAMKAP_USER CONTAINER=ALL; 

-- Allows the Connector to use LogMiner
GRANT LOGMINING TO C##STREAMKAP_USER CONTAINER=ALL;

-- Flashback queries used for performing initial snapshots of the data
GRANT FLASHBACK ANY TABLE TO C##STREAMKAP_USER CONTAINER=ALL; 
GRANT SELECT ANY TRANSACTION TO C##STREAMKAP_USER CONTAINER=ALL; 

-- Required for schema history when performing initial snapshots
GRANT SELECT_CATALOG_ROLE TO C##STREAMKAP_USER CONTAINER=ALL; 
GRANT EXECUTE_CATALOG_ROLE TO C##STREAMKAP_USER CONTAINER=ALL; 

-- Connector creates a table for explicitly managing the flushing of internal log buffers (LGWR)
GRANT CREATE TABLE TO C##STREAMKAP_USER CONTAINER=ALL;

GRANT CREATE SEQUENCE TO C##STREAMKAP_USER CONTAINER=ALL; 

-- Grant the Streamkap user permission to read each schema and table you wish to sync
ALTER SESSION SET CONTAINER={PDB};
GRANT SELECT ON {schema}.{table} TO C##STREAMKAP_USER CONTAINER=ALL;
-- Alternatively, you can grant access to all
-- GRANT SELECT ANY TABLE TO C##STREAMKAP_USER CONTAINER=ALL;

-- Grant the Streamkap user access to the DBA_EXTENTS, DBA_TABLESPACES, DBA_SEGMENTS, and TRANSACTION system views. 
GRANT SELECT ON DBA_EXTENTS TO C##STREAMKAP_USER;
GRANT SELECT ON DBA_TABLESPACES TO C##STREAMKAP_USER;
GRANT SELECT ON DBA_SEGMENTS TO C##STREAMKAP_USER;
GRANT SELECT ANY TRANSACTION TO C##STREAMKAP_USER;

-- Grant the Streamkap user permission to run LogMiner
ALTER SESSION SET CONTAINER=CDB$ROOT;
GRANT SELECT ON SYS.V_$DATABASE TO C##STREAMKAP_USER;
GRANT SELECT ON SYS.V_$PARAMETER TO C##STREAMKAP_USER;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO C##STREAMKAP_USER;
GRANT SELECT ON SYS.V_$ARCHIVE_DEST TO C##STREAMKAP_USER;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO C##STREAMKAP_USER;
GRANT EXECUTE ON DBMS_LOGMNR TO C##STREAMKAP_USER;
GRANT EXECUTE ON DBMS_LOGMNR_D TO C##STREAMKAP_USER;
GRANT SELECT ANY TRANSACTION TO C##STREAMKAP_USER;
GRANT EXECUTE_CATALOG_ROLE TO C##STREAMKAP_USER;

-- Replace {...} placeholders as needed

ALTER SESSION SET CONTAINER={PDB};

-- Create a local (PDB) user
CREATE USER STREAMKAP_USER IDENTIFIED BY {password};
GRANT CREATE SESSION TO STREAMKAP_USER; 
GRANT SET CONTAINER TO STREAMKAP_USER;

ALTER USER STREAMKAP_USER DEFAULT TABLESPACE STREAMKAP_LOGMINER_TBS;
ALTER USER STREAMKAP_USER QUOTA UNLIMITED ON STREAMKAP_LOGMINER_TBS;

-- Create the table
CREATE TABLE STREAMKAP_USER.STREAMKAP_SIGNAL (
  id VARCHAR2(255) PRIMARY KEY, 
  type VARCHAR2(32) NOT NULL, 
  data VARCHAR2(2000) NULL
);

-- Grant necessary privileges on the table to the common user
GRANT SELECT, UPDATE, INSERT, DELETE ON STREAMKAP_USER.STREAMKAP_SIGNAL TO C##STREAMKAP_USER;

-- Replace {...} placeholders as needed
ALTER SESSION SET CONTAINER={PDB};

-- Create the heartbeat table with id, text, and last_update fields
CREATE TABLE STREAMKAP_USER.STREAMKAP_HEARTBEAT (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    text VARCHAR2(4000),
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grant permission to the Streamkap user
GRANT SELECT, INSERT, UPDATE ON STREAMKAP_USER.STREAMKAP_HEARTBEAT TO STREAMKAP_USER;

-- Grant necessary privileges on the table to the common user
GRANT SELECT, INSERT, UPDATE ON STREAMKAP_USER.STREAMKAP_HEARTBEAT TO C##STREAMKAP_USER;

-- Insert the first row into the heartbeat table
INSERT INTO STREAMKAP_USER.STREAMKAP_HEARTBEAT (text) VALUES ('test_heartbeat');