Configure your Oracle database

​

Prerequisites

• Access to the SYSDBA account. Many of the steps require administrator privileges.
• An understanding of which database and schemas you will be targeting for streaming.
• A list of tables that you want to monitor and capture changes for.

​

Configuring your Oracle database for streaming

ARCHIVE LOG LIST;

-----------------------------------------------------------------------------
Database log mode                         Archive Mode
Automatic archival                          Enabled
Archive destination                         USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence          8
Next log sequence to archive        9
Current log sequence                9

1. Enable the database archive log by running the SQL statements below:
   ALTER DATABASE ARCHIVELOG;
   ALTER DATABASE OPEN;
   
   -- You can then view the log status again which should have updated:
   ARCHIVE LOG LIST;
   
2. To adjust the size of the recovery file destination or the path to this destination, run the following SQL statements:
   ALTER SYSTEM SET db_recovery_file_dest_size = <REPLACE_WITH_YOUR_SIZE>;
   ALTER SYSTEM SET db_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
   
   -- If the above has been set, you will need to restart your database
   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   
3. Enable SUPPLEMENTAL logging for any tables you would like to monitor and consume changes from, using the following commands:
   -- It is recommended to enable supplemental logging for individual tables to minimize the amount of information captured in the redo logs
   ALTER TABLE inventory.customers ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   
   -- Minimal supplemental logging must be enabled at the database level and can be configured as follows.
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   
   -- You can view the status of logging for all tables owned by the current user using the following query
   SELECT OWNER, LOG_GROUP_NAME, TABLE_NAME, LOG_GROUP_TYPE, "ALWAYS", "GENERATED"
   FROM DBA_LOG_GROUPS
   WHERE OWNER = '<YOUR_USER>'
   
   To enable supplemental logging for your selected tables, you may need to log in with a different user who has access to the correct schemas and tables.
   LogMiner requires table or column names that are 30 characters or less. The LogMiner utility (DBMS_LOGMNR) doesn’t support long table or column names when supplemental logging is enabled. For more information, read the Oracle documentation related to your database version.
4. Create a new TABLESPACE with these specific size and auto extend settings:
   -- Note that the size properties can be adjusted as required
   -- Additionally, there is an option to configure MAXSIZE UNLIMITED. Monitoring of disk usage should be considered if applying this setting
   CREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT 10M;
   

​

Setting up permissions

1. Create a new user following the recommended naming convention:
   -- Ensure a secure password is provided
   -- The tablespace created earlier should be set as the default
   CREATE USER C##STREAMING_USER IDENTIFIED BY password DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs CONTAINER=ALL;
   
2. A number of permissions will need to be granted to this user to enable correct monitoring of the archive logs. These are detailed below:
   GRANT CREATE SESSION TO c##cdcuser CONTAINER=ALL;
   GRANT SET CONTAINER TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$DATABASE to c##cdcuser CONTAINER=ALL;
   GRANT FLASHBACK ANY TABLE TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ANY TABLE TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT_CATALOG_ROLE TO c##cdcuser CONTAINER=ALL;
   GRANT EXECUTE_CATALOG_ROLE TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ANY TRANSACTION TO c##cdcuser CONTAINER=ALL;
   GRANT LOGMINING TO c##cdcuser CONTAINER=ALL;
   GRANT CREATE TABLE TO c##cdcuser CONTAINER=ALL;
   GRANT LOCK ANY TABLE TO c##cdcuser CONTAINER=ALL;
   GRANT CREATE SEQUENCE TO c##cdcuser CONTAINER=ALL;
   GRANT EXECUTE ON DBMS_LOGMNR TO c##cdcuser CONTAINER=ALL;
   GRANT EXECUTE ON DBMS_LOGMNR_D TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$LOG TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$LOG_HISTORY TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$LOGMNR_LOGS TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$LOGFILE TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$ARCHIVED_LOG TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$TRANSACTION TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$MYSTAT TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$STATNAME TO c##cdcuser CONTAINER=ALL;
   

​

Setting up permissions in a non-CDB environment

1. Create a new user following the recommended naming convention:
   -- Ensure a secure password is provided
   -- The tablespace created earlier should be set as the default
   CREATE USER STREAMING_USER IDENTIFIED BY password DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs;
   
2. A number of permissions will need to be granted to this user to enable correct monitoring of the archive logs. These are detailed below:
   GRANT CREATE SESSION TO cdcuser;
   GRANT SET CONTAINER TO cdcuser;
   GRANT SELECT ON V_$DATABASE to cdcuser;
   GRANT FLASHBACK ANY TABLE TO cdcuser;
   GRANT SELECT ANY TABLE TO cdcuser;
   GRANT SELECT_CATALOG_ROLE TO cdcuser;
   GRANT EXECUTE_CATALOG_ROLE TO cdcuser;
   GRANT SELECT ANY TRANSACTION TO cdcuser;
   GRANT LOGMINING TO cdcuser;
   GRANT CREATE TABLE TO cdcuser;
   GRANT LOCK ANY TABLE TO cdcuser;
   GRANT CREATE SEQUENCE TO cdcuser;
   GRANT EXECUTE ON DBMS_LOGMNR TO cdcuser;
   GRANT EXECUTE ON DBMS_LOGMNR_D TO cdcuser;
   GRANT SELECT ON V_$LOG TO cdcuser;
   GRANT SELECT ON V_$LOG_HISTORY TO cdcuser;
   GRANT SELECT ON V_$LOGMNR_LOGS TO cdcuser;
   GRANT SELECT ON V_$LOGMNR_CONTENTS TO cdcuser;
   GRANT SELECT ON V_$LOGMNR_PARAMETERS TO cdcuser;
   GRANT SELECT ON V_$LOGFILE TO cdcuser;
   GRANT SELECT ON V_$ARCHIVED_LOG TO cdcuser;
   GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO cdcuser;
   GRANT SELECT ON V_$TRANSACTION TO cdcuser;
   GRANT SELECT ON V_$MYSTAT TO c##cdcuser CONTAINER=ALL;
   GRANT SELECT ON V_$STATNAME TO c##cdcuser CONTAINER=ALL;
   

​

Descriptions of grants