Oracle Database Prerequisites for Oracle CDC Source Connector for Confluent Cloud

This page includes the prerequisite Oracle database configuration steps and post-configuration validation steps.

Important

You must complete the following steps before the Oracle CDC Source connector can produce expected results:

Configure database user privileges

The connector requires a database user with role privileges to use LogMiner and to select from all tables captured by the connector. For this reason, you may need to create a new database user for the connector. Note the following:

  • Create a local user for a non-container database. The username must not start with c## or C##.
  • Create a common user for a multitenant database. The username must start with c## or C##.

Non-container database

To set up a new user with the correct database privileges, log in as SYSDBA and enter the following commands to create a role for the user with the required privileges. You can use any preferred name for the role. Note that CDC_PRIVS is the role name used in the following steps:

  1. As SYSDBA, enter the following SQL commands to create the role and grant privileges to the role.

    CREATE ROLE CDC_PRIVS;
GRANT CREATE SESSION TO CDC_PRIVS;
GRANT LOGMINING TO CDC_PRIVS;

GRANT SELECT ON V_$DATABASE TO CDC_PRIVS;
GRANT SELECT ON V_$INSTANCE to CDC_PRIVS;
GRANT SELECT ON V_$THREAD TO CDC_PRIVS;
GRANT SELECT ON V_$PARAMETER TO CDC_PRIVS;
GRANT SELECT ON V_$NLS_PARAMETERS TO CDC_PRIVS;
GRANT SELECT ON V_$TIMEZONE_NAMES TO CDC_PRIVS;

-- Either grant SELECT on any table
GRANT SELECT ANY TABLE TO CDC_PRIVS;

-- Or specify grants to specific tables
GRANT SELECT ON <schema>.<table> TO CDC_PRIVS;

GRANT SELECT ON V_$LOG TO CDC_PRIVS;
GRANT SELECT ON V_$LOGFILE TO CDC_PRIVS;
GRANT SELECT ON V_$ARCHIVED_LOG TO CDC_PRIVS;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO CDC_PRIVS;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO CDC_PRIVS;

GRANT EXECUTE ON SYS.DBMS_LOGMNR TO CDC_PRIVS;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO CDC_PRIVS;

  2. Create a username and password, and grant privileges for the user (you may use any username and password):

    CREATE USER MYUSER IDENTIFIED BY password DEFAULT TABLESPACE USERS;
ALTER USER MYUSER QUOTA UNLIMITED ON USERS;
GRANT SELECT ON <schema>.<table> TO CDC_PRIVS;

GRANT CDC_PRIVS to MYUSER;

Multitenant database (CDB)

To set up a new user with the correct database privileges, log in as SYSDBA and enter the following commands to create a role for the user with the required privileges. You can use any preferred name for the role. Note that C##CDC_PRIVS is the role name used in the following example steps.

  1. As SYSDBA, enter the following SQL commands to create the role and grant privileges to the role.

    CREATE ROLE C##CDC_PRIVS;
GRANT CREATE SESSION TO C##CDC_PRIVS;
GRANT LOGMINING TO C##CDC_PRIVS;

GRANT SELECT ON V_$DATABASE TO C##CDC_PRIVS;
GRANT SELECT ON V_$INSTANCE to C##CDC_PRIVS;
GRANT SELECT ON V_$THREAD TO C##CDC_PRIVS;
GRANT SELECT ON V_$PARAMETER TO C##CDC_PRIVS;
GRANT SELECT ON V_$NLS_PARAMETERS TO C##CDC_PRIVS;
GRANT SELECT ON V_$TIMEZONE_NAMES TO C##CDC_PRIVS;

-- Either grant SELECT on any table
GRANT SELECT ANY TABLE TO CDC_PRIVS;

-- Or specify grants to specific tables
GRANT SELECT ON <schema>.<table> TO CDC_PRIVS;

GRANT SELECT ON V_$LOG TO C##CDC_PRIVS;
GRANT SELECT ON V_$LOGFILE TO C##CDC_PRIVS;
GRANT SELECT ON V_$ARCHIVED_LOG TO C##CDC_PRIVS;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO CDC_PRIVS;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO C##CDC_PRIVS;

GRANT EXECUTE ON SYS.DBMS_LOGMNR TO C##CDC_PRIVS;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO C##CDC_PRIVS;

  2. Create a username, password, and grant privileges for the user. You can use any username and password.

    CREATE USER C##MYUSER IDENTIFIED BY password DEFAULT TABLESPACE USERS;
ALTER USER C##MYUSER QUOTA UNLIMITED ON USERS;
GRANT SELECT ON <schema>.<table> TO C##CDC_PRIVS;

GRANT C##CDC_PRIVS to C##MYUSER;

Important

When you configure the connector, use this user account for the JDBC credentials. Use the entire user name, including the c##, as the JDBC user name.

Multitenant database (PDB)

To set up a common user with the correct database privileges, log in as SYSDBA and enter the following SQL commands to create a role for the user with the required privileges (you can use any preferred name for the role):

Note

Common user accounts are created in cdb$root and must use the convention: c##<name> or C##<name>. The user C##MYUSER and the role C##CDC_PRIVS are used in the following example.

ALTER SESSION SET CONTAINER=CDB$ROOT;
CREATE ROLE C##CDC_PRIVS;
CREATE USER C##MYUSER IDENTIFIED BY password CONTAINER=ALL;
ALTER USER C##MYUSER QUOTA UNLIMITED ON USERS;
ALTER USER C##MYUSER SET CONTAINER_DATA = (CDB$ROOT, <pdb-name>) CONTAINER=CURRENT;
GRANT C##CDC_PRIVS to C##MYUSER CONTAINER=ALL;

GRANT CONNECT TO C##CDC_PRIVS CONTAINER=ALL;
GRANT CREATE SESSION TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SET CONTAINER TO C##CDC_PRIVS CONTAINER=ALL;
GRANT LOGMINING TO C##CDC_PRIVS CONTAINER=ALL;

GRANT SELECT ON V_$DATABASE TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_$INSTANCE to C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_$THREAD TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_$PARAMETER TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_$NLS_PARAMETERS TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_$TIMEZONE_NAMES TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON DBA_PDBS TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON CDB_TABLES TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON CDB_TAB_PARTITIONS TO C##CDC_PRIVS CONTAINER=ALL;

ALTER SESSION SET CONTAINER=<pdb-name>;
-- Either grant SELECT on any table
GRANT SELECT ANY TABLE TO CDC_PRIVS;

-- Or specify grants to specific tables
GRANT SELECT ON <schema>.<table> TO CDC_PRIVS;

ALTER SESSION SET CONTAINER=CDB$ROOT;
GRANT SELECT ON V_$LOG TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_$LOGFILE TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVED_LOG TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO C##CDC_PRIVS CONTAINER=ALL;

GRANT EXECUTE ON SYS.DBMS_LOGMNR TO C##CDC_PRIVS CONTAINER=ALL;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO C##CDC_PRIVS CONTAINER=ALL;

-- Need this only if using Full Supplemental Logging for only a select few tables
-- GRANT SELECT ON DBA_SUPPLEMENTAL_LOGGING TO C##CDC_PRIVS CONTAINER=ALL;

Important

When you configure the connector, use this user account for the JDBC credentials. Use the entire user name, including the c##, as the JDBC user name.

Amazon RDS

Important

The Oracle CDC Source connector does not work with an Oracle read-only replica for Amazon RDS. The connector uses the Oracle-recommended Online Catalog, which requires the database to be open for write access. For related details, see Working with an Oracle read-only replica for Amazon RDS.

Amazon RDS doesn’t allow users to log in as SYSDBA, so it is impossible to create a role–first, you must create a user and then grant the necessary privileges by completing the following steps:

  1. Create a user by entering the following commands:

    CREATE USER DB_USER IDENTIFIED BY PASSWORD DEFAULT TABLESPACE USERS;
ALTER USER DB_USER QUOTA UNLIMITED ON USERS;

GRANT CREATE SESSION TO DB_USER;
GRANT SELECT ON DBA_TABLESPACES TO DB_USER;
GRANT LOGMINING TO DB_USER;

GRANT SELECT ON <schema>.<table> TO DB_USER;

  2. Enter the following command to grant SELECT and EXECUTE permissions on SYS objects using the Amazon RDS procedure:

    exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$INSTANCE', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$THREAD', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PARAMETER', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$NLS_PARAMETERS', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$TIMEZONE_NAMES', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$TRANSACTION', 'DB_USER', 'SELECT');

exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGFILE', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','DB_USER','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG', 'DB_USER', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVE_DEST_STATUS', 'DB_USER', 'SELECT');

exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR', 'DB_USER', 'EXECUTE');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD');

Turn on ARCHIVELOG mode

To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode. Note that you shut down the database when completing the following steps:

Note

You can skip the following steps if ARCHIVELOG mode is already enabled for the database. ARCHIVELOG mode is enabled by default for AWS RDS Oracle instances.

  1. Connect as a user with SYSDBA privileges. Check the database ARCHIVELOG mode. If the LOG_MODE shows ARCHIVELOG, you can skip the Turn on ARCHIVELOG mode section.

    SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

  2. Shut down the database instance using the command SHUTDOWN IMMEDIATE.

    SQL> SHUTDOWN IMMEDIATE;

  3. Create a full database backup including all data and control files. You can use operating system commands or RMAN to perform this operation. This backup can be used in the future for recovery with archived redo log files created once the database is in ARCHIVELOG mode.

  4. Start the instance and mount the database using the command STARTUP MOUNT.

    SQL> STARTUP MOUNT;

  5. Place the database in ARCHIVELOG mode using the command ALTER DATABASE ARCHIVELOG and open the database using the command ALTER DATABASE OPEN.

    SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

  6. Check the database ARCHIVELOG mode.

    SQL> SELECT LOG_MODE FROM V$DATABASE;

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

If you use Oracle Real Application Clusters (RAC), follow the following steps to enable ARCHIVELOG.

   SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
   ------------
NOARCHIVELOG

  1. Stop the database service.

    srvctl stop database -d <Service Name>

  2. Start the database in mount state.

    srvctl start database -d <Service Name> -o mount

  3. Enable ARCHIVELOG mode.

    SQL> ALTER DATABASE ARCHIVELOG;
Database altered.

  4. Restart the database service (using SRVCTL)

    srvctl stop database -d <Service Name>

srvctl start database -d <Service Name>

  5. Check the database ARCHIVELOG mode.

    SQL> SELECT LOG_MODE FROM V$DATABASE;

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

Enable supplemental logging

This section includes the commands to enable supplemental logging.

Non-container and multitenant databases

Enter the following commands to specify that when a row is updated, all columns of that row (except for LOBs, LONGS, and ADTs) are placed in the redo log file ( you must have correct privileges to execute the command):

  1. Set the session container:

    ALTER SESSION SET CONTAINER=cdb$root;

  2. To enable full supplemental logging for all tables, enter the following commands:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    Or, to enable full supplemental logging for specific tables, run the following commands:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SESSION SET CONTAINER=<pdb-name>; -- ONLY FOR PDB
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

  3. To enable minimal supplemental logging (PRIMARY KEY) for specific tables, run the following commands:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SESSION SET CONTAINER=<pdb-name>; -- ONLY FOR PDB
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    Or, to enable minimal supplemental logging (UNIQUE INDEX) for specific tables, run the following commands:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SESSION SET CONTAINER=<pdb-name>; -- ONLY FOR PDB
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG GROUP <LogGroupName> (UniqueIndexColumn1[, UniqueIndexColumn2] ...) ALWAYS;

    For more help with enabling minimal supplemental logging, see Minimal Supplemental Logging. Note that this is information for the self-managed connector which also applies to the fully-managed cloud connector.

Note

At minimum for a PDB database, minimal supplemental logging must be enabled for CDB$ROOT, then full supplemental logging can be enabled at the PDB level. Alternatively, full supplemental logging can be enabled on CDB$ROOT and applied across the CDB.

Amazon RDS Oracle 19c/12c instance

Important

The Oracle CDC Source connector does not work with an Oracle read-only replica for Amazon RDS. The connector uses the Oracle-recommended Online Catalog, which requires the database to be open for write access. For related details, see Working with an Oracle read-only replica for Amazon RDS.

  1. Run the following exec command:

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

  2. To enable full supplemental logging for all tables, run this command:

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

    Or, to enable full supplemental logging for individual tables, run these commands:

    exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
ALTER TABLE <table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

  3. To enable minimal supplemental logging (PRIMARY KEY) for specific tables, run the following commands:

    exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

    Or, to enable minimal supplemental logging (UNIQUE INDEX) for specific tables, run the following commands:

    exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG GROUP <LogGroupName> (UniqueIndexColumn1[, UniqueIndexColumn2] ...) ALWAYS;

Grant the User Flashback Query Privileges

To perform snapshots on captured tables, the connector requires the user to have privileges to perform Flashback queries (that is, SELECT AS OF) on the captured tables. Enter the following command to grant the user privileges to perform Flashback queries on the database:

GRANT FLASHBACK ANY TABLE TO myuser;

To enable FLASHBACK query for specific tables:

GRANT FLASHBACK ON <schema>.<table> TO myuser;

Note

This command is not required if you want to capture the redo log (without generating change events) or generate change events starting from a known System Change Number (SCN) or timestamp using start.from.

For example, you enter the following commands to grant the example role created previously FLASHBACK privileges for a non-container database:

GRANT FLASHBACK ON <schema>.<table> TO CDC_PRIVS

For example, you enter the following commands to grant the example role created above FLASHBACK privileges for a container database or multitenant database:

ALTER SESSION SET CONTAINER=<pdb-name>;
GRANT FLASHBACK ON <schema>.<table> TO C##CDC_PRIVS

Amazon RDS for Oracle 19c/12c instance

GRANT FLASHBACK ON <schema>.<table> TO DB_USER;

Validate start-up configuration and prerequisite completion

Use this section to ensure all prerequisites are met. You can perform an automate readiness check or choose to validate readiness manually.

Automated readiness check

Download the oracle-readiness.sql script to ensure your database is ready to use the Oracle CDC connector. Be sure to run the script with the user having the DBA role and a PDB name if you are working with a multitenant database. For example:

SQLPlus > @oracle-readiness.sql C##MYUSER ORCLPDB1

If you’re not using PDB:

SQLPlus > @oracle-readiness.sql C##MYUSER ''

Note that the use of the oracle-readiness.sql script is subject to the same usage rights and terms as the use of the Oracle CDC connector.

Manual readiness check

If you’d like to verify all prerequisites manually, follow the steps in this section. The following sample values are used:

This section includes steps to take to ensure that all prerequisites are met. The following sample values are used:

  • Role name: CDB_PRIVS and C##CDC_PRIVS
  • Usernames: MYUSER, C##MYUSER and DB_USER
  • Table name: CUSTOMERS. The CUSTOMERS table is created under one of the following schema:
    • ANOTHERUSER: for Non-container database including 11g, 12c, 18c, and 19c
    • C##ANOTHERUSER: for Container Database (CDB) and Multitenant Database (PDB) including Oracle 12c, 18c, 19c and 21c
    • ADMIN: for Amazon RDS for Oracle

Note that numeric values used in the following subsections may be different from the values you see when you run through the validation example.

Non-container database

  1. Log in as SYSDBA and enter the following commands:

    -- Use the following SQL statement if privileges granted to a role (``CDC_PRIVS``) and the role granted to a user (``MYUSER``).

SELECT GRANTEE, OWNER, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT granted_role
                  FROM DBA_ROLE_PRIVS
                  WHERE GRANTEE = 'MYUSER')
AND (TABLE_NAME='DBMS_LOGMNR' OR TABLE_NAME='V_$LOGMNR_CONTENTS');

GRANTEE           OWNER          TABLE_NAME
-------------------------------------------
CDC_PRIVS         SYS            DBMS_LOGMNR

CDC_PRIVS         SYS            V_$LOGMNR_CONTENTS

-- Use the following SQL statement if privileges granted directly to a user (``MYUSER``).

SELECT GRANTEE, OWNER, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'MYUSER'
AND (TABLE_NAME='DBMS_LOGMNR' OR TABLE_NAME='V_$LOGMNR_CONTENTS');

GRANTEE           OWNER          TABLE_NAME
-------------------------------------------
MYUSER            SYS            DBMS_LOGMNR

MYUSER            SYS            V_$LOGMNR_CONTENTS

  2. Create check_prerequisites.sql with the following commands.

    SELECT * FROM SESSION_PRIVS;
SELECT LOG_MODE FROM V$DATABASE;
SELECT COUNT(*) FROM V$DATABASE;
SELECT COUNT(*) FROM V$THREAD;
SELECT COUNT(*) FROM V$PARAMETER;
SELECT COUNT(*) FROM V$NLS_PARAMETERS;
SELECT COUNT(*) FROM V$TIMEZONE_NAMES;
SELECT COUNT(*) FROM ALL_INDEXES;
SELECT COUNT(*) FROM ALL_OBJECTS;
SELECT COUNT(*) FROM ALL_USERS;
SELECT COUNT(*) FROM ALL_CATALOG;
SELECT COUNT(*) FROM ALL_CONSTRAINTS;
SELECT COUNT(*) FROM ALL_CONS_COLUMNS;
SELECT COUNT(*) FROM ALL_TAB_COLS;
SELECT COUNT(*) FROM ALL_IND_COLUMNS;
SELECT COUNT(*) FROM ALL_ENCRYPTED_COLUMNS;
SELECT COUNT(*) FROM ALL_LOG_GROUPS;
SELECT COUNT(*) FROM ALL_TAB_PARTITIONS;
SELECT COUNT(*) FROM ANOTHERUSER.CUSTOMERS;
-- Flashback query privilege
SELECT COUNT(*) FROM ANOTHERUSER.CUSTOMERS AS OF TIMESTAMP SYSDATE;

-- Added for 19C
SELECT count(*) FROM V$ARCHIVED_LOG;
SELECT count(*) FROM V$LOG;
SELECT count(*) FROM V$LOGFILE;
SELECT count(*) FROM V$INSTANCE;
SELECT SEQUENCE# FROM V$LOG WHERE STATUS = 'CURRENT';

  3. Log in using MYUSER (created previously) and then enter the following:

    SQL> @check_prerequisites.sql

PRIVILEGE
----------------------------------------
CREATE SESSION
LOGMINING

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

  COUNT(*)
----------
    1

  COUNT(*)
----------
    1

  COUNT(*)
----------
        344

  COUNT(*)
----------
    19

  COUNT(*)
----------
      2164

  COUNT(*)
----------
    88

  COUNT(*)
----------
      7261

  COUNT(*)
----------
    14

  COUNT(*)
----------
      5656

  COUNT(*)
----------
        197

  COUNT(*)
----------
        266

  COUNT(*)
----------
      20926

  COUNT(*)
----------
        142

  COUNT(*)
----------
    0

  COUNT(*)
----------
    2

  COUNT(*)
----------
    0

  COUNT(*)
----------
    5

  COUNT(*)
----------
      19512

  COUNT(*)
----------
    5

  COUNT(*)
----------
      18658

  COUNT(*)
----------
    0

  COUNT(*)
----------
    10

  COUNT(*)
----------
    10

  COUNT(*)
----------
    2

  COUNT(*)
----------
    3

  COUNT(*)
----------
    3

  COUNT(*)
----------
    1

 SEQUENCE#
----------
    20

  4. Log in using MYUSER (created previously) and then enter the following commands:

    SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

-- If full supplemental logging is enabled for all tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES      NO  YES


-- If full supplemental logging is enabled for specific tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES      NO  NO


-- If full supplemental logging is enabled for specific tables, enter the following command.

SELECT * FROM ALL_LOG_GROUPS WHERE LOG_GROUP_TYPE='ALL COLUMN LOGGING' and OWNER='ANOTHERUSER' and TABLE_NAME='CUSTOMERS';

OWNER          LOG_GROUP_NAME          TABLE_NAME           LOG_GROUP_TYPE          ALWAYS            GENERATED
-------------- ----------------------- -------------------- ----------------------- ----------------- -------------------------
ANOTHERUSER SYS_C007542             CUSTOMERS            ALL COLUMN LOGGING      ALWAYS            GENERATED NAME

Multitenant database (CDB)

  1. Log in as SYSDBA and enter the following commands:

    -- Use the following SQL statement if privileges granted to a role (``C##CDC_PRIVS``) and the role granted to a user (``C##MYUSER``).

    SELECT GRANTEE, OWNER, TABLE_NAME
    FROM DBA_TAB_PRIVS
    WHERE GRANTEE IN (SELECT granted_role
                      FROM DBA_ROLE_PRIVS
                      WHERE GRANTEE = 'C##MYUSER')
    AND (TABLE_NAME='DBMS_LOGMNR' OR TABLE_NAME='V_$LOGMNR_CONTENTS');

    GRANTEE           OWNER          TABLE_NAME
    -------------------------------------------
    C##CDC_PRIVS      SYS            DBMS_LOGMNR

    C##CDC_PRIVS      SYS            V_$LOGMNR_CONTENTS


    -- Use the following SQL statement if privileges granted directly to a user (``C##MYUSER``).

    SELECT GRANTEE, OWNER, TABLE_NAME
    FROM DBA_TAB_PRIVS
    WHERE GRANTEE = 'C##MYUSER'
    AND (TABLE_NAME='DBMS_LOGMNR' OR TABLE_NAME='V_$LOGMNR_CONTENTS');

    GRANTEE           OWNER          TABLE_NAME
    -------------------------------------------
    C##MYUSER      SYS            DBMS_LOGMNR

    C##MYUSER      SYS            V_$LOGMNR_CONTENTS

  2. Create check_prerequisites.sql with the following commands:

    SELECT * FROM SESSION_PRIVS;
SELECT LOG_MODE FROM V$DATABASE;
SELECT COUNT(*) FROM V$DATABASE;
SELECT COUNT(*) FROM V$THREAD;
SELECT COUNT(*) FROM V$PARAMETER;
SELECT COUNT(*) FROM V$NLS_PARAMETERS;
SELECT COUNT(*) FROM V$TIMEZONE_NAMES;
SELECT COUNT(*) FROM ALL_INDEXES;
SELECT COUNT(*) FROM ALL_OBJECTS;
SELECT COUNT(*) FROM ALL_USERS;
SELECT COUNT(*) FROM ALL_CATALOG;
SELECT COUNT(*) FROM ALL_CONSTRAINTS;
SELECT COUNT(*) FROM ALL_CONS_COLUMNS;
SELECT COUNT(*) FROM ALL_TAB_COLS;
SELECT COUNT(*) FROM ALL_IND_COLUMNS;
SELECT COUNT(*) FROM ALL_ENCRYPTED_COLUMNS;
SELECT COUNT(*) FROM ALL_LOG_GROUPS;
SELECT COUNT(*) FROM ALL_TAB_PARTITIONS;
SELECT COUNT(*) FROM C##ANOTHERUSER.CUSTOMERS;
-- Flashback query privilege
SELECT COUNT(*) FROM C##ANOTHERUSER.CUSTOMERS AS OF TIMESTAMP SYSDATE;

-- Added for 19c and 21c
SELECT count(*) FROM V$ARCHIVED_LOG;
SELECT count(*) FROM V$LOG;
SELECT count(*) FROM V$LOGFILE;
SELECT count(*) FROM V$INSTANCE;
SELECT SEQUENCE# FROM V$LOG WHERE STATUS = 'CURRENT';

  3. Log in using C##MYUSER (created previously) and then enter the following:

    SQL> @check_prerequisites.sql

PRIVILEGE
----------------------------------------
CREATE SESSION
LOGMINING

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

  COUNT(*)
----------
    1

  COUNT(*)
----------
    1

  COUNT(*)
----------
      344

  COUNT(*)
----------
  19

  COUNT(*)
----------
      2164

  COUNT(*)
----------
  88

  COUNT(*)
----------
      7261

  COUNT(*)
----------
  14

  COUNT(*)
----------
      5656

  COUNT(*)
----------
      197

  COUNT(*)
----------
      266

  COUNT(*)
----------
    20926

  COUNT(*)
----------
      142

  COUNT(*)
----------
    0

  COUNT(*)
----------
    2

  COUNT(*)
----------
    0

  COUNT(*)
----------
  10

  COUNT(*)
----------
  10

  COUNT(*)
----------
  2

  COUNT(*)
----------
  3

  COUNT(*)
----------
  3

  COUNT(*)
----------
  1
SEQUENCE#
----------
    20

  4. Log in using C##MYUSER (created previously) and then enter the following commands:

    SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

-- If full supplemental logging is enabled for all tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES      NO  YES


-- If full supplemental logging is enabled for specific tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES      NO  NO


-- If full supplemental logging is enabled for specific tables, enter the following command.

SELECT * FROM ALL_LOG_GROUPS WHERE LOG_GROUP_TYPE='ALL COLUMN LOGGING' and OWNER='C##ANOTHERUSER' and TABLE_NAME='CUSTOMERS';

OWNER          LOG_GROUP_NAME          TABLE_NAME           LOG_GROUP_TYPE          ALWAYS            GENERATED
-------------- ----------------------- -------------------- ----------------------- ----------------- -------------------------
C##ANOTHERUSER SYS_C007542             CUSTOMERS            ALL COLUMN LOGGING      ALWAYS            GENERATED NAME

Multitenant database (PDB)

  1. Log in as SYSDBA and enter the following commands:

    ALTER SESSION SET CONTAINER=CDB$ROOT;

-- Use the following SQL statement if privileges granted to a role (``C##CDC_PRIVS``) and the role granted to a user (``C##MYUSER``).

SELECT GRANTEE, OWNER, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT granted_role
                  FROM DBA_ROLE_PRIVS
                  WHERE GRANTEE = 'C##MYUSER')
AND (TABLE_NAME='DBMS_LOGMNR' OR TABLE_NAME='V_$LOGMNR_CONTENTS');

GRANTEE           OWNER          TABLE_NAME
-------------------------------------------
C##CDC_PRIVS      SYS            DBMS_LOGMNR

C##CDC_PRIVS      SYS            V_$LOGMNR_CONTENTS

-- Use the following SQL statement if privileges granted directly to a user (``C##MYUSER``).

SELECT GRANTEE, OWNER, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'C##MYUSER'
AND (TABLE_NAME='DBMS_LOGMNR' OR TABLE_NAME='V_$LOGMNR_CONTENTS');

GRANTEE           OWNER          TABLE_NAME
-------------------------------------------
C##MYUSER      SYS            DBMS_LOGMNR

C##MYUSER      SYS            V_$LOGMNR_CONTENTS

  2. Create check_prerequisites.sql with the following commands.

    ALTER SESSION SET CONTAINER=CDB$ROOT;
SELECT * FROM SESSION_PRIVS;
SELECT LOG_MODE FROM V$DATABASE;
SELECT COUNT(*) FROM V$DATABASE;
SELECT COUNT(*) FROM V$THREAD;
SELECT COUNT(*) FROM V$PARAMETER;
SELECT COUNT(*) FROM V$NLS_PARAMETERS;
SELECT COUNT(*) FROM V$TIMEZONE_NAMES;
SELECT COUNT(*) FROM ALL_INDEXES;
SELECT COUNT(*) FROM ALL_OBJECTS;
SELECT COUNT(*) FROM ALL_USERS;
SELECT COUNT(*) FROM ALL_CATALOG;
SELECT COUNT(*) FROM ALL_CONSTRAINTS;
SELECT COUNT(*) FROM ALL_CONS_COLUMNS;
SELECT COUNT(*) FROM ALL_TAB_COLS;
SELECT COUNT(*) FROM ALL_IND_COLUMNS;
SELECT COUNT(*) FROM ALL_ENCRYPTED_COLUMNS;
SELECT COUNT(*) FROM ALL_LOG_GROUPS;
SELECT COUNT(*) FROM ALL_TAB_PARTITIONS;
SELECT COUNT(*) FROM DBA_PDBS;
SELECT COUNT(*) FROM CDB_TABLES;
SELECT COUNT(*) FROM CDB_TAB_PARTITIONS;

ALTER SESSION SET CONTAINER=<pdb-name>;
SELECT COUNT(*) FROM C##ANOTHERUSER.CUSTOMERS;
-- Flashback query privilege
SELECT COUNT(*) FROM C##ANOTHERUSER.CUSTOMERS AS OF TIMESTAMP SYSDATE;

-- Added for 19c and 21c
SELECT count(*) FROM V$ARCHIVED_LOG;
SELECT count(*) FROM V$LOG;
SELECT count(*) FROM V$LOGFILE;
SELECT count(*) FROM V$INSTANCE;
SELECT SEQUENCE# FROM V$LOG WHERE STATUS = 'CURRENT';

-- Uncomment this if Using Full Supplemental Logging on Specific Tables.
-- SELECT count(*) FROM DBA_SUPPLEMENTAL_LOGGING;

  3. Log in using C##MYUSER (created previously) and then enter the following:

    SQL> @check_prerequisites.sql

SQL> @check_prerequisites.sql

Session altered.

PRIVILEGE
----------------------------------------
CREATE SESSION
LOGMINING
SET CONTAINER

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


  COUNT(*)
----------
    1

  COUNT(*)
----------
    1

  COUNT(*)
----------
        344

  COUNT(*)
----------
    19

  COUNT(*)
----------
      2164

  COUNT(*)
----------
    88

  COUNT(*)
----------
      7261

  COUNT(*)
----------
    14

  COUNT(*)
----------
      5656

  COUNT(*)
----------
        197

  COUNT(*)
----------
        266

  COUNT(*)
----------
      20926

  COUNT(*)
----------
        142

  COUNT(*)
----------
    0

  COUNT(*)
----------
    2

  COUNT(*)
----------
    0

  COUNT(*)
----------
    5

  COUNT(*)
----------
      19512

  COUNT(*)
----------
          1

  COUNT(*)
----------
      4213

  COUNT(*)
----------
      14390

Session altered.

  COUNT(*)
----------
          15

  COUNT(*)
----------
    15

  COUNT(*)
----------
    0

  COUNT(*)
----------
    3

  COUNT(*)
----------
    3

  COUNT(*)
----------
    1

  SEQUENCE#
----------
    20

  4. Log in using C##MYUSER (created previously) and then enter the following commands:

    SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

-- If full supplemental logging is enabled for all tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES      NO  YES

-- If full supplemental logging is enabled for specific tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES      NO  NO

-- If full supplemental logging is enabled for specific tables, enter the following command.
ALTER SESSION SET CONTAINER=ORCLPDB1;
SELECT * FROM ALL_LOG_GROUPS WHERE LOG_GROUP_TYPE='ALL COLUMN LOGGING' and OWNER='C##ANOTHERUSER' and TABLE_NAME='CUSTOMERS';

OWNER          LOG_GROUP_NAME          TABLE_NAME           LOG_GROUP_TYPE          ALWAYS            GENERATED
-------------- ----------------------- -------------------- ----------------------- ----------------- -------------------------
C##ANOTHERUSER SYS_C007542             CUSTOMERS            ALL COLUMN LOGGING      ALWAYS            GENERATED NAME

Amazon RDS

  1. Log in as ADMIN and enter the following:

    -- Use the following SQL statement if privileges granted directly to a user (``DB_USER``).

SELECT GRANTEE, OWNER, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'DB_USER'
AND (TABLE_NAME='DBMS_LOGMNR' OR TABLE_NAME='V_$LOGMNR_CONTENTS');

GRANTEE           OWNER          TABLE_NAME
-------------------------------------------
DB_USER          SYS            DBMS_LOGMNR

DB_USER          SYS            V_$LOGMNR_CONTENTS

  2. Create check_prerequisites.sql with the following commands:

    SELECT COUNT(*) FROM DBA_TABLESPACES;
SELECT * FROM SESSION_PRIVS;
SELECT LOG_MODE FROM V$DATABASE;
SELECT COUNT(*) FROM ALL_VIEWS;
SELECT COUNT(*) FROM ALL_TAB_PARTITIONS;
SELECT COUNT(*) FROM ALL_INDEXES;
SELECT COUNT(*) FROM ALL_OBJECTS;
SELECT COUNT(*) FROM ALL_TABLES;
SELECT COUNT(*) FROM ALL_USERS;
SELECT COUNT(*) FROM ALL_CATALOG;
SELECT COUNT(*) FROM ALL_CONSTRAINTS;
SELECT COUNT(*) FROM ALL_CONS_COLUMNS;
SELECT COUNT(*) FROM ALL_TAB_COLS;
SELECT COUNT(*) FROM ALL_IND_COLUMNS;
SELECT COUNT(*) FROM ALL_LOG_GROUPS;
SELECT COUNT(*) FROM V$ARCHIVED_LOG;
SELECT COUNT(*) FROM V$LOG;
SELECT COUNT(*) FROM V$LOGFILE;
SELECT COUNT(*) FROM V$DATABASE;
SELECT COUNT(*) FROM V$THREAD;
SELECT COUNT(*) FROM V$PARAMETER;
SELECT COUNT(*) FROM V$NLS_PARAMETERS;
SELECT COUNT(*) FROM V$TIMEZONE_NAMES;
SELECT COUNT(*) FROM V$TRANSACTION;
SELECT COUNT(*) FROM ALL_ENCRYPTED_COLUMNS;
SELECT COUNT(*) FROM V$LOGMNR_LOGS;
SELECT COUNT(*) FROM ADMIN.CUSTOMERS;
-- Flashback query privilege
SELECT COUNT(*) FROM ADMIN.CUSTOMERS AS OF TIMESTAMP SYSDATE;
SELECT count(*) FROM V$INSTANCE;
SELECT SEQUENCE# FROM V$LOG WHERE STATUS = 'CURRENT';

  3. Log in using DB_USER (created previously) and then enter the following:

    SQL> @check_prerequisites.sql

  COUNT(*)
----------
  6


PRIVILEGE
----------------------------------------
LOGMINING
CREATE SESSION

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

  COUNT(*)
----------
      1765


  COUNT(*)
----------
  0


  COUNT(*)
----------
  57


  COUNT(*)
----------
      9368


  COUNT(*)
----------
  53


  COUNT(*)
----------
  25


  COUNT(*)
----------
      7700


  COUNT(*)
----------
  80


  COUNT(*)
----------
  73


  COUNT(*)
----------
    22606


  COUNT(*)
----------
      101


  COUNT(*)
----------
  1


  COUNT(*)
----------
  57


  COUNT(*)
----------
  4


  COUNT(*)
----------
  4


  COUNT(*)
----------
  1


  COUNT(*)
----------
  1


  COUNT(*)
----------
      420


  COUNT(*)
----------
  19


  COUNT(*)
----------
      2408


  COUNT(*)
----------
  0


  COUNT(*)
----------
  4


  COUNT(*)
----------
  0


  COUNT(*)
----------
  8


  COUNT(*)
----------
  8


  COUNT(*)
----------
    1

 SEQUENCE#
----------
    20

  4. Log in using DB_USER (created previously) and then enter the following commands:

    SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;

-- If full supplemental logging is enabled for all tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES NO  YES


-- If full supplemental logging is enabled for specific tables, the following is returned.

SUPPLEME SUP SUP
-------- --- ----
YES      NO  NO


-- If full supplemental logging is enabled for specific tables, enter the following command.

SELECT * FROM ALL_LOG_GROUPS WHERE LOG_GROUP_TYPE='ALL COLUMN LOGGING' and OWNER='ADMIN' and TABLE_NAME='CUSTOMERS';

OWNER          LOG_GROUP_NAME          TABLE_NAME           LOG_GROUP_TYPE          ALWAYS            GENERATED
-------------- ----------------------- -------------------- ----------------------- ----------------- -------------------------
ADMIN          SYS_C006989             CUSTOMERS            ALL COLUMN LOGGING      ALWAYS            GENERATED NAME