You can configure your self-managed Oracle database to work with the following CDC methods:
These methods specify how Datastream accesses your log files. For more information about the binary log reader and the LogMiner API, see Work with Oracle database redo log files.
For detailed instructions about how to configure your source Oracle database for each of these methods, see the sections that follow.
Configure your self managed Oracle database for binary log reader
To configure your self-managed Oracle database for use with the binary log reader CDC method, perform the following steps:
Select one of the following methods to access your log files:
- Automatic Storage Management (ASM): if you select this option, you need to create a dedicated ASM connection, and provide its details when you create your connection profile.
- Database directories: if you select this option, you need to create database
directory objects that point to the redo log files and the archived log files,
and grant
READ
access to the directories to your database user:
CREATE DIRECTORY
DIRECTORY_NAME_1 as 'ONLINE_REDO_LOGS_PATH '; CREATE DIRECTORYDIRECTORY_NAME_2 as 'ARCHIVED_REDO_LOGS_PATH '; GRANT READ ON DIRECTORYDIRECTORY_NAME_1 toUSER_NAME ; GRANT READ ON DIRECTORYDIRECTORY_NAME_2 toUSER_NAME ;Replace the following:
- DIRECTORY_NAME_1: the name of the directory for the online redo log files.
- DIRECTORY_NAME_2: the name of the directory for the archived redo log files.
- ONLINE_REDO_LOGS_PATH: the path to the directory where online redo log files are to be stored.
- ARCHIVED_REDO_LOGS_PATH: the path to the directory where archived log files are to be stored.
- USER_NAME: the name of the database user to whom you want to grant
READ
access.
If you select the binary log reader method and use database directories, you need to provide the redo log and archived log directory names when you create your stream.
Verify that your database is running in
ARCHIVELOG
mode.To do so, sign in to your Oracle database and run the following command at the SQL prompt:
SELECT LOG_MODE FROM V$DATABASE;
- If the result is
ARCHIVELOG
, then move on to the next step. - If the result is
NOARCHIVELOG
, then you'll need to enableARCHIVELOG
mode for your database. Run the following commands when logged in as
SYSDBA
:SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;
Archived log files consume disk space, so you'll want to configure the DB_RECOVERY_FILE_DEST_SIZE parameter for your database. Use this parameter to specify (in bytes) the hard limit on the total space to be used by target database recovery files. By setting this parameter, you can manage the tradeoff between protecting the database from running out of disk space and the stream failing because of log position loss.
- If the result is
Define a data retention policy for your database by running these Oracle Recovery Manager (RMAN) commands:
TARGET / CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
We recommend that you retain backups and archive logs for a minimum of 4 days, and 7 days is recommended.
Return to the SQL prompt of the database tool that you're using to configure the Oracle log file rotation policy. We recommend setting a maximum log file size of no more than 512MB.
Enable supplemental log data. To do so, first enable minimal database-level supplemental logging by running the following command:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Next, choose whether to turn on logging for specific tables or the entire database.
To log changes only for specific tables, run the following command for each table that you want to replicate:
ALTER TABLE
SCHEMA .TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;Replace the following:
- SCHEMA: the name of the schema that contains the table.
- TABLE: the name of the table for which you want to log changes.
To replicate most or all tables in your database, consider turning logging on for the entire database. Run the following command to enable supplemental log data for the entire database:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Grant the appropriate privileges to the user account that will be used to connect to your database. To do so, run the following commands:
GRANT SELECT ON GV_$LOG TO
USER_NAME ; GRANT SELECT ON GV_$LOGFILE TOUSER_NAME ; GRANT SELECT ON GV_$ARCHIVED_LOG TOUSER_NAME ; GRANT SELECT ON GV_$INSTANCE TOUSER_NAME ; GRANT SELECT ON GV_$STANDBY_LOG TOUSER_NAME ; GRANT SELECT ON V_$INSTANCE TOUSER_NAME ; GRANT SELECT ON V_$PDBS TOUSER_NAME ; GRANT SELECT ON V_$TRANSPORTABLE_PLATFORM TOUSER_NAME ; GRANT SELECT ON V_$DATABASE TOUSER_NAME ; GRANT SELECT ON COL$ TOUSER_NAME ; GRANT SELECT ON DBA_OBJECTS TOUSER_NAME ; GRANT SELECT ON DBA_TABLESPACES TOUSER_NAME ; GRANT SELECT ON DBA_ENCRYPTED_COLUMNS TOUSER_NAME ; GRANT SELECT ON DBA_EXTENTS TOUSER_NAME ; GRANT CONNECT TOUSER_NAME ; GRANT CREATE SESSION TOUSER_NAME ; GRANT SELECT ANY TABLE TOUSER_NAME ; GRANT READ ON DIRECTORY ONLINELOG_DIR TOUSER_NAME ; GRANT READ ON DIRECTORY ARCHIVELOG_DIR TOUSER_NAME ;Replace USER_NAME with the name of the user account that you intend to use to connect to your database.
Configure your self managed Oracle database for LogMiner
Verify that your database is running in
ARCHIVELOG
mode.To do so, sign in to your Oracle database and run the following command at the SQL prompt:
SELECT LOG_MODE FROM V$DATABASE;
- If the result is
ARCHIVELOG
, then move on to step 2. - If the result is
NOARCHIVELOG
, then you'll need to enableARCHIVELOG
mode for your database. Run the following commands when logged in as
SYSDBA
:SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;
Archived log files consume disk space, so you'll want to configure the DB_RECOVERY_FILE_DEST_SIZE parameter for your database. Use this parameter to specify (in bytes) the hard limit on the total space to be used by target database recovery files. By setting this parameter, you can manage the tradeoff between protecting the database from running out of disk space and the stream failing because of log position loss.
- If the result is
Define a data retention policy for your database by running these Oracle Recovery Manager (RMAN) commands:
TARGET / CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
We recommend that you retain backups and archive logs for a minimum of 4 days, and 7 days is recommended.
Return to the SQL prompt of the database tool that you're using to configure the Oracle log file rotation policy. We recommend setting a maximum log file size of no more than 512MB.
Enable supplemental log data. To do so, first enable minimal database-level supplemental logging by running the following command:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Next, choose whether to turn on logging for specific tables or the entire database.
To log changes only for specific tables, run the following command for each table that you want to replicate:
ALTER TABLE
SCHEMA .TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;Replace the following:
- SCHEMA: the name of the schema that contains the table.
- TABLE: the name of the table for which you want to log changes.
To replicate most or all tables in your database, consider turning logging on for the entire database. Run the following command to enable supplemental log data for the entire database:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Grant the appropriate privileges to the user account that will be used to connect to your database. To do so, run the following commands:
GRANT EXECUTE_CATALOG_ROLE TO
USER_NAME ; GRANT CONNECT TOUSER_NAME ; GRANT CREATE SESSION TOUSER_NAME ; GRANT SELECT ON SYS.V_$DATABASE TOUSER_NAME ; GRANT SELECT ON SYS.V_$ARCHIVED_LOG TOUSER_NAME ; GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TOUSER_NAME ; GRANT EXECUTE ON DBMS_LOGMNR TOUSER_NAME ; GRANT EXECUTE ON DBMS_LOGMNR_D TOUSER_NAME ; GRANT SELECT ANY TRANSACTION TOUSER_NAME ; GRANT SELECT ANY TABLE TOUSER_NAME ;If your organization doesn't permit granting the
GRANT SELECT ANY TABLE
permission, use the solution described in the Oracle change data capture (CDC) section of the Datastream FAQ page.If your source database is Oracle 12c or newer, then grant the following additional privilege:
GRANT LOGMINING TO
USER_NAME ;Grant the user account
SELECT
access to theDBA_EXTENTS
view in your database. This privilege lets you use theROWID
based backfill for your Oracle source:GRANT SELECT ON DBA_EXTENTS TO
USER_NAME ;
What's next
- Learn more about how Datastream works with Oracle sources.