# Oracle Database Prerequisites This page includes the prerequisite Oracle database configuration steps and post-configuration validation steps. Note that you must complete the following steps before the Oracle CDC Source connector can produce expected results: - [Configure database user privileges](#connect-oracle-cdc-source-prereqs-user-privileges) - [Turn on ARCHIVELOG mode](#connect-oracle-cdc-source-prereqs-archivelog-mode) - [Enable supplemental logging](#connect-oracle-cdc-source-prereqs-enable-supplemental-logging) - [Grant the User Flashback Query Privileges](#connect-oracle-cdc-source-prereqs-grant-user-flashback) - [Validate start-up configuration and prerequisite completion](#connect-oracle-cdc-source-prereqs-check-prerequisites) ## 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 The following steps are for Oracle Database 19c. 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. ```sql 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; GRANT SELECT ON . TO CDC_PRIVS; GRANT SELECT ON V_$LOG TO CDC_PRIVS; GRANT SELECT ON V_$LOGFILE TO CDC_PRIVS; GRANT SELECT ON V_$LOGMNR_CONTENTS TO CDC_PRIVS; GRANT SELECT ON V_$ARCHIVED_LOG TO CDC_PRIVS; GRANT SELECT ON V_$ARCHIVE_DEST_STATUS 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): ```sql CREATE USER MYUSER IDENTIFIED BY password DEFAULT TABLESPACE USERS; ALTER USER MYUSER QUOTA UNLIMITED ON USERS; GRANT SELECT ON . TO CDC_PRIVS; GRANT CDC_PRIVS to MYUSER; ``` ### Multitenant database (CDB) The following steps are for Oracle Database 19c and 21c. 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. ```sql 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; GRANT SELECT ON . TO C##CDC_PRIVS; GRANT SELECT ON V_$LOG TO C##CDC_PRIVS; GRANT SELECT ON V_$LOGFILE TO C##CDC_PRIVS; GRANT SELECT ON V_$LOGMNR_CONTENTS 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 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. ```sql CREATE USER C##MYUSER IDENTIFIED BY password DEFAULT TABLESPACE USERS; ALTER USER C##MYUSER QUOTA UNLIMITED ON USERS; GRANT SELECT ON . 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) The following steps are for Oracle Database 19c and 21c. 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 that common user accounts are created in `cdb$root` and must use the convention: `c##` or `C##`. The user `C##MYUSER` and the role `C##CDC_PRIVS` are used in the following example. ```sql 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, ) 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 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=; GRANT SELECT ON . TO C##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_$LOGMNR_CONTENTS 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 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 The following steps are for Oracle Database 19c. #### 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](https://docs.oracle.com/database/121/SUTIL/GUID-1D510A2F-4CE8-4D69-AB18-CDD58FB3458C.htm), which requires the database to be open for write access. For related details, see [Working with an Oracle read-only replica for Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-read-replicas.html). 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: ```sql 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 . TO DB_USER; ``` 2. Enter the following command to grant `SELECT` and `EXECUTE` permissions on SYS objects using the Amazon RDS procedure: ```sql 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 that 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 SQL> SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ NOARCHIVELOG ``` 2. Shut down the database instance using the command `SHUTDOWN IMMEDIATE`. ```sql 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 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 SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; ``` 6. Check the database `ARCHIVELOG` mode. ```sql SQL> SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ ARCHIVELOG ``` If you use Oracle Real Application Clusters (RAC), please follow the following steps to enable `ARCHIVELOG`. ```sql SQL> SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ NOARCHIVELOG ``` 1. Stop the database service. ```text srvctl stop database -d ``` 2. Start the database in mount state. ```text srvctl start database -d -o mount ``` 3. Enable `ARCHIVELOG` mode. ```sql SQL> ALTER DATABASE ARCHIVELOG; Database altered. ``` 4. Restart the database service (using SRVCTL) ```text srvctl stop database -d srvctl start database -d ``` 5. Check the database `ARCHIVELOG` mode. ```sql 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: ```sql ALTER SESSION SET CONTAINER=cdb$root; ``` 2. To enable full supplemental logging for all tables, enter the following commands: ```sql ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ``` Or, to enable full supplemental logging for specific tables, run the following commands: ```sql ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER SESSION SET CONTAINER=; -- ONLY FOR PDB ALTER TABLE . ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ``` 3. To enable minimal supplemental logging (`PRIMARY KEY`) for specific tables, run the following commands: ```sql ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER SESSION SET CONTAINER=; -- ONLY FOR PDB ALTER TABLE . ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ``` Or, to enable minimal supplemental logging (`UNIQUE INDEX`) for specific tables, run the following commands: ```sql ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER SESSION SET CONTAINER=; -- ONLY FOR PDB ALTER TABLE . ADD SUPPLEMENTAL LOG GROUP (UniqueIndexColumn1[, UniqueIndexColumn2] ...) ALWAYS; ``` For more help with enabling minimal supplemental logging, see [Minimal Supplemental Logging for Oracle CDC Source Connector for Confluent Platform](minimal-supplemental-logging.md#connect-oracle-cdc-source-minimal-supplemental-logging). #### NOTE For a pluggable databases: - Up to version 21c, enable minimal supplemental logging for the root container (CDB$ROOT). In version 23ai, enable minimal supplemental logging at either the CDB$ROOT or the specific PDB level. - Finally, enable full supplemental logging either for the entire database or specifically for the tables of interest within the PDB (recommended). ### Amazon RDS Oracle 19c 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](https://docs.oracle.com/database/121/SUTIL/GUID-1D510A2F-4CE8-4D69-AB18-CDD58FB3458C.htm), which requires the database to be open for write access. For related details, see [Working with an Oracle read-only replica for Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-read-replicas.html). 1. Run the following `exec` command: ```text exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24); ``` 2. To enable full supplemental logging for all tables, run this command: ```text exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL'); ``` Or, to enable full supplemental logging for individual tables, run these commands: ```sql exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD'); ALTER TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ``` 3. To enable minimal supplemental logging (PRIMARY KEY) for specific tables, run the following commands: ```sql exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD'); ALTER TABLE . ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ``` Or, to enable minimal supplemental logging (UNIQUE INDEX) for specific tables, run the following commands: ```sql exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD'); ALTER TABLE . ADD SUPPLEMENTAL LOG GROUP (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: ```sql GRANT FLASHBACK ANY TABLE TO myuser; ``` To enable FLASHBACK query for specific tables: ```sql GRANT FLASHBACK ON . TO myuser; ``` Note that 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: ```sql GRANT FLASHBACK ON . 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: ```sql ALTER SESSION SET CONTAINER=; GRANT FLASHBACK ON . TO C##CDC_PRIVS ``` ### Amazon RDS for Oracle 19c instance ```sql GRANT FLASHBACK ON . 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](#automated-readiness-check), or choose to [validate readiness manually](#manual-readiness-check). ### Automated readiness check Download the [`oracle-readiness.sql`](/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: ```text SQLPlus > @oracle-readiness.sql C##MYUSER ORCLPDB1 ``` If you’re not using PDB: ```text 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: * 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 19c - `C##ANOTHERUSER`: for Container Database (CDB) and Multitenant Database (PDB) including 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 The following steps are for Oracle Database 19c. 1. Log in as `SYSDBA` and enter the following commands: ```sql -- 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. ```sql 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 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 `MYUSER` (created previously) and then enter the following commands: ```sql 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) The following steps are for Oracle Database 19c and 21c. 1. Log in as `SYSDBA` and enter the following commands: ```sql -- 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: ```sql 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 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: ```sql 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 ``` 5. Create `check_prerequisites.sql` with the following commands: ```sql 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; ``` 6. Log in using `C##MYUSER` (created previously) and then enter the following: ```sql 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 ``` 7. Log in using `C##MYUSER` (created previously) and then enter the following commands: ```sql 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_C006989 CUSTOMERS ALL COLUMN LOGGING ALWAYS GENERATED NAME ``` ### Multitenant database (PDB) The following steps are for Oracle Database 19c and 21c. 1. Log in as `SYSDBA` and enter the following commands: ```sql 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. ```sql 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=; 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 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(*) ---------- 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: ```sql 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 The following are steps are for an Oracle 19c instance. 1. Log in as `ADMIN` and enter the following: ```sql -- 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: ```sql 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 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: ```sql 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 ```