Configure a source Oracle database

Overview

This page describes how to configure your source Oracle database so that Datastream can pull data from it. Datastream can pull changes to data from on-premises or cloud-hosted Oracle databases, including Oracle relational database service (RDS).

Datastream leverages Oracle LogMiner, which is part of Oracle Database, to query archived redo log files. When configuring your source Oracle database, you'll ensure that it runs in ARCHIVELOG mode, so that the logs are archived. Redo log files contain information about the history of activity on a database. For more information about how Datastream works with these log files, see Work with Oracle database redo log files.

Configure a source Oracle database

Before you can use Datastream to pull data from your source Oracle database, you must configure your database by:

Setting up archive logging to track changes in your database, such as the INSERT, UPDATE, DELETE, and RENAME operations.
Granting the appropriate privileges to the user account that will be used to connect to your database.
Defining a data retention policy for your database to determine which data will be archived, how long it will be kept, should the data at the end of the retention period be archived or destroyed, and so on.

Datastream currently works with the following types of Oracle databases:

Amazon RDS for Oracle
Self-hosted Oracle
Self-hosted Oracle pluggable database

For information about how to configure each of these Oracle database types, expand the sections that follow.

Configure an Amazon RDS for Oracle database

Verify that your database is running in ARCHIVELOG mode. To do so, log in to your Oracle database and run the following command at the SQL prompt:
SELECT LOG_MODE FROM V$DATABASE;
1. If the result is ARCHIVELOG, then move on to step c.
2. If the result is NOARCHIVELOG, then you'll need to enable ARCHIVELOG mode for your database.
  For your database to run in ARCHIVELOG mode, you must enable backups for the database.
3. 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.
4. Define a data retention policy for your database by running this command:
```
exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',96);
```
  We recommend that you retain backups and archive logs for a minimum of 4 days, and 7 days is recommended.
  Running this command will restart your database instance so that the changes you made to the database can take effect.
5. Configure the Oracle log file rotation policy. We recommend setting a maximum log file size to a value below 1GB.
Enable supplemental log data. To do so, first enable minimal database-level supplemental logging by running the following command:
```
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
```
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.

At the SQL prompt, run the following command to enable supplemental log data for the entire database:
```
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL');
```
The supplemental logging mode must be set to ALL. You can't set this mode to PK_ONLY.

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 TO USER_NAME;
GRANT CREATE SESSION TO USER_NAME;
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','USER_NAME','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','USER_NAME','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS','USER_NAME','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','USER_NAME','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','USER_NAME','EXECUTE');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D','USER_NAME','EXECUTE');
GRANT SELECT ANY TRANSACTION TO USER_NAME;
GRANT SELECT ANY TABLE TO USER_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;

For each command, replace USER_NAME with the name of the user account. Also, running these commands will grant privileges to the user to read from V$DATABASE (which is required for validation), start the log miner, and grant privileges to select from V$LOGMNR_CONTENT.

Configure a self-hosted Oracle database

Verify that your database is running in ARCHIVELOG mode. To do so, log in to your Oracle database and run the following command at the SQL prompt:
SELECT LOG_MODE FROM V$DATABASE;
1. If the result is ARCHIVELOG, then move on to step 2.
2. If the result is NOARCHIVELOG, then you'll need to enable ARCHIVELOG mode for your database.
3. Run the following commands when logged in as SYSDBA:
```
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
```
4. 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.
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;
```
You use the TARGET / command to start an RMAN client and connect to the source database. See Making Database Connections with RMAN.

We recommend that you retain backups and archive logs for a minimum of 4 days, and 7 days is recommended.

Running this command will restart your database instance so that the changes you made to the database can take effect.
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;
```
The supplemental logging mode must be set to ALL. You can't set this mode to PK_ONLY.
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 TO USER_NAME;
GRANT CREATE SESSION TO USER_NAME;
GRANT SELECT ON SYS.V_$DATABASE TO USER_NAME;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO USER_NAME;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO USER_NAME;
GRANT EXECUTE ON DBMS_LOGMNR TO USER_NAME;
GRANT EXECUTE ON DBMS_LOGMNR_D TO USER_NAME;
GRANT SELECT ANY TRANSACTION TO USER_NAME;
GRANT SELECT ANY TABLE TO USER_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;
```
For each command, replace USER_NAME with the name of the user account. Also, running these commands will grant privileges to the user to read from V$DATABASE (which is required for validation), start the log miner, and grant privileges to select from V$LOGMNR_CONTENT.

Configure a self-hosted Oracle pluggable database

Datastream supports Oracle multi-tenant architecture, where a single container database (CDB)contains one or more pluggable databases (PDBs). Each pluggable database is a self-contained database with a unique ID and name, and can be managed independently.

To connect to a container in a multi-tenant architecture setup, or to switch between container sessions, use the following command:

ALTER SESSION SET CONTAINER = CONTAINER_NAME;

Replace CONTAINER_NAME with the name of your container, for example your pluggable database or the CDB$ROOT container.

To configure a self-hosted Oracle pluggable database so that you can use it with Datastream, perform the following steps:

Verify that your database is running in ARCHIVELOG mode. To do so, run the following command from the CDB$ROOT container:
SELECT LOG_MODE FROM V$DATABASE;
1. If the result is ARCHIVELOG, then move on to step 2.
2. If the result is NOARCHIVELOG, then you'll need to enable ARCHIVELOG mode for your database.
3. Run the following commands when logged in as SYSDBA:
```
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
```
4. 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.
Define a data retention policy for your database by running the following Oracle Recovery Manager (RMAN) command from the CDB$ROOT container:
```
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
```
The command defines the data retention policy for all pluggable databases in your container database.

You can use the TARGET / command to start an RMAN client and connect to the CDB$ROOT container of your source database. For more information, see Making Database Connections with RMAN.

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 supplemental logging on the database at the CDB$ROOT container level by running the following command:
```
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
```
Next, choose whether to turn on logging for specific tables or the entire pluggable database.

To log changes only for specific tables, connect to the pluggable database container and 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 multiple 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;
```
The supplemental logging mode must be set to ALL. You can't set this mode to PK_ONLY.
Create a common user. A common user has the same identity in the CDB$ROOT container and in the pluggable databases. A common user can connect to and perform operations within the CDB$ROOT container, and within any pluggable database in which it has privileges. The common user name must start with C## or c##.

Grant the appropriate privileges to the common user that will be used to connect to your database. Different permissions are required at the CDB$ROOT container and pluggable database levels.

Connect to the CDB$ROOT container and run the following commands:

GRANT CREATE SESSION TO USER_NAME;
GRANT SET CONTAINER TO USER_NAME;
GRANT SELECT ON SYS.V_$DATABASE TO USER_NAME;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO USER_NAME;
GRANT EXECUTE ON DBMS_LOGMNR TO USER_NAME;
GRANT EXECUTE ON DBMS_LOGMNR_D TO USER_NAME;
GRANT LOGMINING TO USER_NAME;
GRANT EXECUTE_CATALOG_ROLE TO USER_NAME;

Connect to the pluggable database and run the following commands:

GRANT CREATE SESSION TO USER_NAME;
GRANT SET CONTAINER TO USER_NAME;
GRANT SELECT ANY TABLE TO USER_NAME;
GRANT SELECT ON SYS.V_$DATABASE TO USER_NAME;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO USER_NAME;
GRANT SELECT ON DBA_SUPPLEMENTAL_LOGGING TO USER_NAME;

For each command, replace USER_NAME with the name of the common user account. Running these commands will grant privileges to the user to read from V$DATABASE (which is required for validation), start the log miner, and grant privileges to select from V$LOGMNR_CONTENT.