Skip to main content

Configuring Oracle and Striim to use OJet

OJet requires a special license. For more information, Contact Striim support.

In an Oracle RAC environment, OJet must connect to a SCAN listener (see About Connecting to an Oracle RAC Database Using SCANs).

OJet can read from:

Oracle configuration varies depending on which of these topologies you use.

  1. If you will run OJet on a Forwarding Agent, Install the Oracle Instant Client in a Forwarding Agent.

  2. For all Oracle environments, Enable archivelog.

    When using OJet, it is unnecessary to enable supplemental logging manually, as that is done by the setup script.

  3. For logical standby only, set DATABASE GUARD to standby to prevent users other than SYS from making changes to the standby's data:

    ALTER DATABASE GUARD standby;

  4. Do one of the following:

    For a single primary database or logical standby, Running the OJet setup script on Oracle.

    For a downstream database without Active Data Guard, follow the instructions in Configuring a downstream database to use OJet (without Active Data Guard).

    For Active Data Guard, follow the instructions in Configuring OJet with Active Data Guard.

  5. Execute the following command to extract the database dictionary to the redo log. Going forward, run this command once a week.

    EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

  6. If there may be open transactions when you start an OJet application, see Initial load to CDC handover with open transactions.

Configure Amazon RDS for Oracle

In Amazon RDS for Oracle, OJet is not compatible with CDB as it requires access to the ROOT$CDB container, which Amazon does not allow. This means OJet is also not compatible with Oracle 21c in Amazon RDS for Oracle, since that release does not support non-CDB databases.

If you are using Amazon RDS for Oracle, perform the following steps before enabling archivelog or running the OJet setup script.

  1. Enable backups for your database instance (see Enabling automated backups). This is required to retain archivelog data for use by OJet.

  2. Create a new parameter group for your database instance (see Creating a DB parameter group).

  3. Set the value of ENABLE_GOLDENGATE_REPLICATION to TRUE.

  4. Associate the parameter group with your database instance (see Associating a DB parameter group with a DB instance).

  5. Reboot the database instance (see Rebooting a DB instance).

  6. Use the following command to verify that the parameter has been set:

    show parameter "ENABLE_GOLDENGATE_REPLICATION"

Enable archivelog

If not using Amazon RDS for Oracle:

  1. Log in to SQL*Plus as the sys user.

  2. Enter the following command:

    select log_mode from v$database;

    If the command returns ARCHIVELOG, it is enabled.

  3. If the command returns NOARCHIVELOG, enter: shutdown immediate

  4. Wait for the message ORACLE instance shut down, then enter: startup mount

  5. Wait for the message Database mounted, then enter:

    alter database archivelog;
alter database open;

  6. To verify that archivelog has been enabled, enter select log_mode from v$database; again. This time it should return ARCHIVELOG.

If using Amazon RDS for Oracle, enter the following command (see AWS > Documentation > Amazon RDS > User Guide > Setting supplemental logging):

exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD')
exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);

Running the OJet setup script on Oracle

The following instructions assume that OJet will be reading from a single primary database or a logical standby. If you are using Active Data Guard and reading from a downstream database, follow the instructions Configuring OJet with Active Data Guard instead.

Before running the setup script, create an Oracle user for use by OJet. In a CDB environment, this must be a common user. There is no need to assign privileges, those will be added by the setup script. Do not set IDLE_TIME on this user's session or the OJet application will halt.

Download and extract the setup utility as described in Enabling OJet on Striim Cloud. The setup script is setupOjet/striim/tools/bin/setupOJet.sh (or in Windows setupOJet.bat). The syntax is:

setupOJet.sh <connection URL> [check] <sys user name> <password> <ojet user name> [y [y] [<file name>]]

  • check: include the check option to check whether the Oracle environment is ready to work with OJet. The output will look something like this:

    Configuration check for OJet using user sys as sysdba started.
Settings for downstream capture connecting to the capture database, using user sys as sysdba

Checking existing configuration:
Apply server owners: 0
Rule owners: 0
Queue table owners: 0
Capture owners: 0
Queue owners: 0
Rule set owners: 0
Checking required database options
Checking if required patches are installed
Warning: patch 31254535 not found to be installed
Warning: patch 28209272 not found to be installed
Warning: patch 34470389 not found to be installed
Warning: patch 32338220 not found to be installed
Warning: patch 34010877 not found to be installed

    This indicates that you must install the five specified patches before running the setup script.

  • connection URL: either host:port:SID or host:port/service

  • sys user name: an Oracle user with DBA privilege that can connect as sysdba. You may need to configure REMOTE_LOGIN_PASSWORDFILE.

  • password: the specified sys user's password

  • ojet_user: the name of the Oracle user you created before running setupOJet.sh

  • remote (for downstream setup only): y

  • source (for downstream setup only): y

  • file (for downstream setup only): file name with tables for instantiation at downstream source

Example (replace the IP address, SID, and password with those for your environment):

setupOJet.sh 203.0.113.49:1521:orcl sys ******** OJET_USER 

If the script reports that an Oracle fix is missing, install it and run the script again.

The script's output should be similar to this:

./setupOJet.sh localhost:1521:ORCL sys oracle OJET_USER
  Configuration for OJet using user OJET_USER started: 
  Granted resources to user OJET_USER
  Granted select any dictionary privilege to user OJET_USER
  Enabled replication
  Enabled streaming
  Enabled supplemental logging
  Building dictionary log …
  Done

Configuring a downstream database to use OJet

Two types of downstream configurations are supported, real-time and archivelog. The difference between them is how redo changes are shipped from the source database to the downstream database, and where the capture process will run and OJet will connect. The remote file server (RFS) process receives redo data from the primary database either in the form of archived redo logs or standby redo logs

Configuring a downstream database to use OJet (without Active Data Guard)

If you are using Active Data Guard, see Configuring OJet with Active Data Guard.

Configure the primary database

  1. Add the downstream database connection details to $TNS_ADMIN/tnsnames.ora so the primary database can ship it the redo changes.

  2. Update the log_archive_config with the downstream database details. For example, to add ORCL as a downstream database:

    ALTER SYSTEM SET log_archive_config='DG_CONFIG=(oradb,oradb_s2,ORCL)'SCOPE=both;

  3. Add a new log_archive_dest., configured for either real-time or archived log capture.

    • For real-time downstream capture, for example using log_archive_dest_3, for the same ORCL database, and using INST1 as the service name of the downstream database:

      ALTER SYSTEM SET log_archive_dest_3='service=INST1 ASYNC NOREGISTER
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL' REOPEN=10 SCOPE=both;
ALTER SYSTEM SET log_archive_dest_state_3=enable SCOPE=both;

    • For archived log capture, for example using log_archive_dest_3, for the same ORCL database  and using INST1 as the service name of the downstream database. TEMPLATE represents the destination on the downstream database, which should be different from the primary database  archive log location.

      alter system set log_archive_dest_3='SERVICE=inst1 ASYNC NOREGISTER
  VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE)
  TEMPLATE= /opt/oracle/product/19c/dbhome_1/srl_dbs/dbs1_arch_%t_%s_%r.log
  DB_UNIQUE_NAME=ORCL' REOPEN=10 SCOPE=both;
ALTER SYSTEM SET log_archive_dest_state_3=enable SCOPE=both;

  4. Run the setup script (see Running the OJet setup script on Oracle), appending two y parameters. For example:

    setupOJet.sh 203.0.113.49:1521:orcl sys ******** OJET_USER y y

Configure the downstream database

  1. Add the primary database connection details to $TNS_ADMIN/tnsnames.ora so the downstream database can connect to the primary.

  2. Update the log_archive_config with the primary database details. For example, to add ORADB as a downstream database:

     ALTER SYSTEM SET  log_archive_config='DG_CONFIG=(oradb,orcl)' SCOPE=both;

  3. Ensure the locally generated redo is in a different location than the source database redo.

    ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/product/19c/dbhome_1/dbs/
 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' SCOPE=both;

  4. For real time capture only,  add the destination and standby redo logfiles, similar as for setting up a standby database.

    ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/opt/oracle/product/19c/dbhome_1/srl_dbs/
  VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)' SCOPE=both;

    The downstream database needs one more standby redo log file than the primary database. Use (SELECT COUNT(GROUP#) FROM GV$LOG) to get the group count.

    ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 'slog4a.rdo' SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 'slog5a.rdo' SIZE 200M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 'slog6a.rdo' SIZE 200M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 'slog7a.rdo' SIZE 200M;

  5. Run the setup script (see Running the OJet setup script on Oracle), appending a single y parameter. For example:

    setupOJet.sh 203.0.113.49:1521:orcl sys ******** OJET_USER y

  6. To verify that the configuration is correct and the remote file server processes are connected, run the following command:

    SELECT COUNT(*) FROM V$MANAGED_STANDBY WHERE PROCESS='RFS';

    If the command returns 0, the configuration is incorrect. Review the above steps or Contact Striim support.

Configuring OJet with Active Data Guard

In an Active Data Guard environment, the physical standby database is in read-only mode, so OJet cannot attach directly to it. Thus a cascaded setup is required,. A cascaded redo transport destination (also known as a terminal destination) receives the primary database redo indirectly from a standby database, rather than directly from a primary database. Oracle documentation for setting up a cascaded set up needs to be followed. For more information, see Cascaded Redo Transport Destinations.

For Active Data Guard, the standby database needs to be in recovery mode so that metadata is in sync with the primary database. The required steps for setup are outlined below, with differences between the two setups. Refer to Oracle documentation for the steps to perform these changes.

The passwords for the sys and OJet users must be the same on the standby and downstream databases.

Primary database setup

Primary database setup

Run the DBMS_CAPTURE_ADM.BUILD procedure on the primary database to extract the data dictionary to the redo log when a capture process is created:

DBMS_CAPTURE_ADM.BUILD();

Primary or standby database setup

Configure the following settings on both the primary or standby and the source databases:

  1. Enable archivelog.

  2. Add the connection details for the downstream database to: $TNS_ADMIN/tnsnames.ora

  3. To configure the standby to replicate to the downstream database, add a new log_archive_dest in the standby, depending on the type of downstream configuration (change the identifiers log_archive_dest_3, ORCL, and INST1 to reflect your environment).

    • For real-time capture:

      ALTER SYSTEM set log_archive_dest_3='service=INST1 ASYNC NOREGISTER
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL' scope=both;
SHOW PARAMETER log_archive_dest_3;
ALTER SYSTEM set log_archive_dest_state_3=enable scope=both;

    • For archivelog capture:

      ALTER SYSTEM set log_archive_dest_3='SERVICE=inst1 ASYNC NOREGISTER
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  TEMPLATE=/opt/oracle/product/19c/dbhome_1/srl_dbs/dbs1_arch_%t_%s_%r.log
  DB_UNIQUE_NAME=ORCL' scope=both;
SHOW PARAMETER log_archive_dest_state_3;
ALTER SYSTEM set log_archive_dest_state_3=enable scope=both;

  4. Before running the setup script, create an Oracle user for use by OJet. In a CDB environment, this must be a common user. There is no need to assign privileges, those will be added by the setup script. Do not set IDLE_TIME on this user's session or the OJet application will halt.

  5. Run the setup script (see Running the OJet setup script on Oracle), appending a single y parameter. For example:

    setupOJet.sh 203.0.113.49:1521:orcl sys ******** OJET_USER y y

Downstream database setup with Active Data Guard

Configure the following settings on the downstream database:

  1. Add the source database connection details to: $TNS_ADMIN/tnsnames.ora.

  2. Update the log_archive_config with the source database. For example (change oradb.orcl to reflect your environment):

    ALTER SYSTEM set log_archive_config='DG_CONFIG=(oradb,orcl)' scope=both;

  3. Ensure that the local generated redo is at a different location than the source database redo:

    ALTER SYSTEM set
  LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/product/19c/dbhome_1/dbs/
  VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope=both;

  4. For real-time capture only,  add the standby log file: 

    ALTER SYSTEM set
  LOG_ARCHIVE_DEST_2='LOCATION=/opt/oracle/product/19c/dbhome_1/srl_dbs/
  VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)' scope=both;

    There must be one more standby log file than in the source (see Create the Standby Redo Log Files). Use(SELECT COUNT(GROUP#) FROM GV$LOG to verify this:

    ALTER DATABASE add standby logfile group 4 'slog4a.rdo' SIZE 200M;
ALTER DATABASE add standby logfile group 5 'slog5a.rdo' SIZE 200M; 
ALTER DATABASE add standby logfile group 6 'slog6a.rdo' SIZE 200M; 
ALTER DATABASE add standby logfile group 7 'slog7a.rdo' SIZE 200M;

  5. Before running the setup script, create an Oracle user for use by OJet. In a CDB environment, this must be a common user. There is no need to assign privileges, those will be added by the setup script. Do not set IDLE_TIME on this user's session or the OJet application will halt.

  6. Run the setup script (see Running the OJet setup script on Oracle), appending two y parameters. For example:

    setupOJet.sh 203.0.113.49:1521:orcl sys ******** OJET_USER y

  7. To verify that the configuration is correct and the remote file server processes are connected, run the following command:

    SELECT COUNT(*) FROM V$MANAGED_STANDBY WHERE PROCESS='RFS';

    If the command returns 0, the configuration is incorrect. Review the above steps or Contact Striim support.