Amazon RDS for Oracle

  • Updated on Feb 12, 2025
  • Published on Mar 27, 2024
  • 17 minute(s) read
  • AF
  • Alejandro Millalen
 Prev Next

This page provides the required information for setting up replications using Amazon RDS for Oracle as a source database for Refresh, Mirroring, and synchronization. The setup process for a refresh replication can usually be completed using the Syniti Replicate wizards without additional documentation because it does not involve access to the Oracle logs. This page also describes the setup process using the Log Reader option for one-way mirroring and synchronization when replicating data from an Oracle database.

For mirroring and synchronization replications using Oracle as a source, Syniti Replicate offers several approaches:

  • Log Reader: Queries the Oracle Log Miner directly

  • Triggers: Uses Syniti Replicate triggers installed on the Oracle database to log changes.

    Note

    Ask the technical support team using the Help Center to see if this approach would work in your application. Download the separate document Setup Notes for Oracle Using Triggers.

Versions Supported

Amazon RDS for Oracle is supported as source and target systems in Refresh Mirroring mode with the following versions:

  • Oracle database 12c

  • Oracle database 19c

  • Oracle database 21c

Prerequisites

  • Ensure that you download the appropriate Oracle .NET Provider from Oracle with the Oracle Database Client.

  • Ensure that you have the Oracle.DataAccess (file name: Oracle.DataAccess.dll) assembly in your path name. For example, C:\oracle10_2\client\odp.net\bin\2.x\Oracle.DataAccess.dll

Limitations

  • Log-based replication is not supported when you use Oracle v19 or higher in a Real Applications Cluster (RAC) architecture as a source. Instead, use Triggers-based replication.

  • ASM and TDE are not supported using Log Reader for Mirroring.

Prepare your Amazon RDS for Oracle for CDC

You need to prepare the Amazon RDS before starting your replication using Change Data Capture (CDC) as a source.

Enable supplemental logging for Oracle

Syniti Replicate requires database-level supplemental logging to be enabled. Execute the following command:

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

Archive Logs Setting

To retain archived redo logs of your Oracle database instance for a specific time period, execute the below command, for example, 72 hours. Make sure that your storage has sufficient space to archive the redo logs during the specified period.

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

User Permissions

When setting up replications that use Amazon RDS for Oracle as either a source or target database, you need to ensure that the user ID used for making connections to the database has sufficient privileges to complete all the operations required for Syniti Replicate to perform a replication.

This section is organized by the type of replication you want to perform. It describes in detail all the user authorizations that will be required during the setup and execution of replications.

Refresh with Amazon RDS for Oracle as Source Database

Execute the following commands to grant the required access to perform refresh with Amazon RDS for Oracle as a source database:

grant create session to <sdruser>;
grant select on ALL_USERS to <sdruser>;
grant select on ALL_TABLES to <sdruser>;
grant select on ALL_TAB_COMMENTS to <sdruser>;
grant select on ALL_OBJECTS to <sdruser>;
grant select on ALL_VIEWS to <sdruser>;
grant select on ALL_TAB_COLUMNS to <sdruser>;
grant select on ALL_COL_COMMENTS to <sdruser>;
grant select on ALL_CONSTRAINTS to <sdruser>;
grant select on ALL_CONS_COLUMNS to <sdruser>;
grant select on SYS.USER_CONSTRAINTS to <sdruser>;
grant select on SYS.USER_CONS_COLUMNS to <sdruser>;
grant select on ALL_IND_COLUMNS to <sdruser>;
grant select on <table> to <UID>;

Refresh with Amazon RDS for Oracle as Target Database

Execute the following commands to grant the required access to perform refresh with Amazon RDS for Oracle as a target database:

grant create session to <sdruser>;
grant select on ALL_USERS to <sdruser>;
grant select on ALL_TABLES to <sdruser>;
grant select on ALL_TAB_COMMENTS to <sdruser>;
grant select on ALL_OBJECTS to <sdruser>;
grant select on ALL_VIEWS to <sdruser>;
grant select on ALL_TAB_COLUMNS to <sdruser>;
grant select on ALL_COL_COMMENTS to <sdruser>;
grant select on ALL_CONSTRAINTS to <sdruser>;
grant select on ALL_CONS_COLUMNS to <sdruser>;
grant select on SYS.USER_CONSTRAINTS to <sdruser>;
grant select on SYS.USER_CONS_COLUMNS to <sdruser>;
grant select on ALL_IND_COLUMNS to <sdruser>;
grant select on <table> to <sdruser>;
grant unlimited tablespace to <sdruser>; 
grant alter on <table> to <sdruser>;
grant drop on <table> to <sdruser>;

Transactional Replications or Refresh with Oracle as Either Source or Target Database

The following table includes information for mirroring where Amazon RDS for Oracle is the source data source. For synchronization, where Amazon RDS for Oracle can be either source or target data source.

1. Authorize to Connect the Database

You need to authorize your User ID to establish a connection to an Oracle database.

Command Syntax

grant create session to <uid>;

or

grant connect to <uid>;

Sample Command

grant create session to sdruser;

or

grant connect to sdruser;

where sdruser is the User ID

2. Authorize to Select Catalog

To display a list of tables and fields on a table in the Management Center (for selecting a source or target table and for setting the fields to replicate), Syniti Replicate runs a SELECT_CATALOG command.

If the user ID has insufficient privileges, an error is generated on the Oracle server.

Command Syntax

grant select on SYS.ALL_USERS to <uid>;
grant select on SYS.ALL_TABLES to <uid>;
grant select on SYS.ALL_TAB_COMMENTS to <uid>;
grant select on SYS.ALL_OBJECTS to <uid>;
grant select on SYS.ALL_VIEWS to <uid>;
grant select on SYS.ALL_TAB_COLUMNS to <uid>;
grant select on SYS.ALL_COL_COMMENTS to <uid>;
grant select on SYS.ALL_CONSTRAINTS to <uid>;
grant select on SYS.ALL_CONS_COLUMNS to <uid>;
grant select on SYS.USER_CONSTRAINTS to <uid>;
grant select on SYS.USER_CONS_COLUMNS to <uid>;
grant select on SYS.ALL_IND_COLUMNS to <uid>;

Sample Command

grant select on SYS.ALL_USERS to sdruser;

where sdruser is the User ID. Use the same User ID for other grant commands.

3. Authorize to Select Tables

Syniti Replicate runs a SELECT statement to identify records to replicate. Hence, the user ID used to make a connection must be authorized to run a SELECT statement for tables involved in replication.

Command Syntax

grant select on <table> to <UID>;

Sample Command

grant select on SAMPLE.EMPLOYEES to sdruser;

where sdruser is the User ID.

4. Authorize to Create and Update Tables (Optional)

Note

This authorization is needed only when Amazon RDS for Oracle is set as a target datasource.

To create a target table in the Management Center (as part of the Create Table Wizard), Syniti Replicate requires this permission.

You first need to grant a quota on the table space in which you want to create a table or index. Then you can grant create and update permissions. For example, insert, update and delete.

The insert, update and delete commands can be as broad as needed. They can also be granted to entire schemas or to all database tables.

Command Syntax

You can either grant unlimited tablespace to the user:

grant unlimited tablespace to <uid>;

Alternatively, just define a quota limit on a specific tablespace:

alter user <uid> QUOTA 100M on <tablespace name>;

Create and Update permissions:

grant create on <table> to <uid>;
grant insert, update, delete on <table> to <uid>;

Sample Command

grant unlimited tablespace to sdruser;
grant create on SAMPLE.EMPLOYEES to sdruser;
grant insert, update, delete on SAMPLE.EMPLOYEES to sdruser;

where sdruser is the User ID.

5. Authorize to Create and Update Tables (Optional)

Note

This authorization is needed only when Amazon RDS for Oracle is set as a target datasource.

The use of these commands in Syniti Replicate is entirely optional (not necessary for running a refresh replication).

They are used if you choose to remove a table from Oracle or change the table via the Management Center SQL Query tab. This command can be as broad as needed. They can also be granted to entire schema or to all database tables.

Command Syntax

grant alter on <table> to <uid>; 
grant drop on <table> to <uid>;

Sample Command

grant alter on Oracle.Table1 to sdruser;
grant drop on Oracle.Table1 to sdruser;

where sdruser is the User ID.

6. Authorize to set up a connection for transactional replications using log miner

Note

This authorization is needed only when Amazon RDS for Oracle is set as a source datasource in mirroring replications.

When setting up the connection for transactional replications (where Oracle is serving as the source of data, either for mirroring or for synchronization), you may need additional privileges that are not required for replication operations. You need to alter the database to enable SUPPLEMENTAL LOG.

In Syniti Replicate (DBMoto) versions 7.0.4 and above, the supplemental log is enabled at the table level by default rather than for the entire database. In the default case, you need an ALTER DATABASE privilege to set up supplemental logging and an ALTER TABLE privilege on each table to be replicated when setting up a replication. If the table already has a supplemental log set, then Syniti Replicate does not need this privilege. You can control whether supplemental logging is enabled at the table or database level using the Oracle Setup Info dialog box available from the Connection Properties dialog box under Transaction Log Type.

Command Syntax

To set up supplemental logging independent of Syniti Replicate (not through the Oracle Setup Info dialog box), use the following commands:

  • For database-level supplemental logging:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS

  • For minimal/table-level supplemental logging:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

  • In this last case, the ALTER TABLE statement will also be used on each replicated table:

    ALTER TABLE MYSCHEMA.MYTABLE ADD SUPPLEMENTAL LOG GROUP <groupname> (<pkList>) ALWAYS

where pkList is a comma-separated list of the primary keys of the table, while groupname is an internally generated name of the supplemental log group.

If using a dictionary file (required for Oracle 8.1, optional for later versions) you need execute permission to build the dictionary file.

grant alter on <table> to <uid>; 
grant alter database to <uid>; 
grant execute on sys.dbms_logmnr_d to <uid>;

Sample Command

grant alter on SAMPLE.EMPLOYEES to sdruser; 
grant alter database to sdruser; 
grant execute on sys.dbms_logmnr_d to sdruser;

where sdruser is the User ID.

7. Authorize to set up transactional replications and access redo or archived logs using log miner

Note

This authorization is needed only when Amazon RDS for Oracle is set as a source datasource in mirroring replications.

When setting up the connection for transactional replications (where Oracle is serving as the source of data, either for mirroring or for synchronization), the following permissions are also required:

  • SELECT_TABLE permissions on the source table (see above)

  • Execute permission to run the log miner

  • Access all system tables to retrieve log information and the last transaction ID

Command Syntax

grant execute on sys.dbms_logmnr to <uid>; 
grant select on sys.v_$parameter to <uid>; 
grant select on sys.v_$log to <uid>; 
grant select on sys.v_$logfile to <uid>; 
grant select on sys.V_$logmnr_contents to <uid>; 
grant select on sys.V_$thread to <uid>; 
grant select on sys.V_$archive_dest to <uid>; 
grant select on SYS.AUD$ to <uid>;

To use archived logs to identify database changes for mirroring, you must execute the following command:

grant select on sys.v_$archived_log to <uid>;

  • For Oracle versions 10g and above, execute the following command:

    grant select any transaction to <uid>;

  • For Oracle versions 12c and above, execute the following command:

    grant select on cdb_pdbs to <uid>; grant logmining to <uid>;
grant select on sys.v_$database to <uid>;
grant select on sys.v_$containers to <uid>;
grant select on DBA_LOG_GROUPS to <uid>;
grant select on DBA_LOG_GROUP_COLUMNS to <uid>;
grant EXECUTE_CATALOG_ROLE to <uid>;

  • For Oracle versions 12c and above using Pluggable Database (PDB) all the GRANT statements should be run on the container:

    grant execute on sys.dbms_logmnr_d to <uid> container=ALL;
grant select on sys.v_$parameter to <uid> container=ALL;
grant select on sys.v_$log to <uid> container=ALL;
grant select on sys.v_$logfile to <uid> container=ALL;
grant select on sys.V_$logmnr_contents to <uid> container=ALL;
grant select on sys.V_$thread to <uid> container=ALL;
grant select on sys.V_$archive_dest to <uid> container=ALL;
grant select on sys.v_$archived_log to <uid> container=ALL;
grant select any transaction to <uid> container=ALL;
grant select on cdb_pdbs to <uid> container=ALL;
grant logmining to <uid> container=ALL;
grant select on sys.v_$database to <uid> container=ALL;
grant select on sys.v_$containers to <uid> container=ALL;
grant select on DBA_LOG_GROUPS to <uid> container=ALL;
grant select on DBA_LOG_GROUP_COLUMNS to <uid> container=ALL;
grant EXECUTE_CATALOG_ROLE to <uid> container=ALL;

Sample Command

grant select any transaction to sdruser; 
grant logmining to sdruser; 
grant select on sys.v_$archived_log to sdruser; 
grant execute on sys.dbms_logmnr to sdruser; 
grant select on sys.v_$parameter to sdruser; 
grant select on sys.v_$log to sdruser; 
grant select on sys.v_$logfile to sdruser; 
grant select on sys.V_$logmnr_contents to sdruser; 
grant select on sys.V_$thread to sdruser; 
grant select on sys.V_$archive_dest to sdruser; 
grant select on SYS.AUD$ to sdruser; 
grant select on sys.v_$database to sdruser; 
grant select on sys.v_$containers to sdruser; 
grant select on DBA_LOG_GROUPS to sdruser; 
grant select on DBA_LOG_GROUP_COLUMNS to sdruser; 
grant EXECUTE_CATALOG_ROLE to sdruser;

where sdruser is the User ID.

Oracle System Settings

Install Oracle Client with .NET Provider

Before connecting to an Oracle database from Syniti Replicate, make sure the Oracle .NET provider is installed and accessible from the system where Syniti Replicate is running.

If you install Syniti Replicate on the system where the Oracle server is installed, the Oracle .NET Data Provider should already be installed as part of the Oracle installation and Syniti Replicate should be able to find the provider automatically. If you install Syniti Replicate on a different system, to connect to the Oracle server you need to install the Oracle client on the same system as Syniti Replicate.

Oracle Log Retention Time Setting

In the Oracle database, log retention time determines how long the transaction information contained in the logs is retained, and not deleted or purged. If the need arises for Syniti Replicate to read earlier transactions from the database log, the retention time is a crucial parameter in determining the success or failure of this operation. The longer the retention time, the better the chances are of finding the required transactions in the database. If transactions are not found, a replication will fail and subsequently a full refresh is required to re-sync the data.

The following issues are the possible reasons why Syniti Replicate would need to read earlier transactions:

  • Prolonged stop of the Replication Agent.

  • Prolonged disabled time for a replication, or any other condition that puts the replication in recovery mode.

  • Prolonged maintenance activity of the servers involved (which may cause downtime of one or more services).

  • Long refresh time (during refresh, the backlog of transactions gets larger and the effort to read them will be higher).

  • Massive number of transactions executed on the source database for a continued period of time (a condition that impacts performance and, as a consequence, may require the log reader process to fall behind in order to read all transactions).

To manage conditions like the above, a log retention time of 72 hours is recommended. If 72 hours is not possible, a reasonable heuristic to use is: take the longest refresh time among all replications, and multiply that by two, with a minimum log retention time of 4 hours. However, note that if an extended period of downtime occurs on the system running Syniti Replicate or Oracle, these suggestions will not be sufficient and a full refresh will be needed when the system restarts.

Using Oracle Autonomous Database

If using Oracle Autonomous Database (either as a target or as a source with triggers), the following settings are required:

  • tnsnames.ora and sqlnet.ora should be placed in the ORACLE_HOME folder. Sample of ORACLE_HOME path: C:\app\client\supadhaya\product\12.2.0\client_1\network\admin

  • Sqlnet.ora should NOT have: “SSL_SERVER_DN_MATCH=yes”. Sample of sqlnet.ora text where DIRECTORY is the path where the cwallet.sso file is present.
    WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\app\client\supadhaya\product\12.2.0\client_1\network\admin")))

  • The client IP should be given access to the DB instance by adding it to the list in the Database’s Access Control List.

Setting Up for Synchronization

When replicating from Oracle using log-based synchronization mode, it is necessary to identify a user by retrieving session user information. This is achieved by enabling auditing: use the auditing SESSION statement option to generate a single audit record for each session created by connections to the instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time.

Set the audit trail to db, to direct audit records to the database audit trail (the SYS.AUD$ table) and complete the below steps. Allowed values are: db and db, extended.

  1. Connect to the database and run the following SQL statement:

ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;

  1. Restart the database instance after issuing the below command. To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, by SESSION (the default and only value for this option), connect to the database and run the following SQL statement: AUDIT SESSION; You can also set this option selectively for individual users: AUDIT SESSION BY dbmoto;
    The effect of this command is persistent after database reboot. In case you need to turn auditing off, you can do so by using the NOAUDIT statement, one for each AUDIT statement that you want to turn off. For example, if you have run both the commands shown above, to turn them off, then run the following commands:

NOAUDIT SESSION; and NOAUDIT SESSION BY dbmoto;

Once auditing is enabled, querying DBA_STMT_AUDIT_OPTS returns the following record:

Querying DBA_PRIV_AUDIT_OPTS returns:

You don't need to add any options to these views as the AUDIT SESSION command was used.

Add a Source Connection Wizard

Select Provider Screen

Assembly

The value for Oracle should be the path name to the .NET Assembly Oracle.DataAccess. For example, file name: Oracle.DataAccess.dll.

For later versions of Oracle, you can leave the Assembly field blank as the dll path should be available to Syniti Replicate (the dll is registered during installation of the Oracle .NET Data Provider) . If the value is not available, Syniti Replicate displays a message when you continue in the Source Connection wizard, allowing you to go back and type in the path. Search the location of the assembly in your environment by searching for the file name Oracle.DataAccess.dll, then enter the path and the assembly file name as in the example below.

Set Connection String Screen

Data Source

Enter the data source address for Oracle versions 11 and later. For example, 122.333.4.555:1521/ORADB1.

User ID

Enter a User ID that will be exclusively used by Syniti Replicate and has the authorization to read the database transaction log (redo log.) See a detailed list of authorizations needed from the above table.

For Synchronization Replications:

The Login or User ID that you provide must be unique to Syniti Replicate. It must not be used for any transactions occurring in either database involved in the synchronization. Syniti Replicate does not replicate transactions by the user you specify in this connection. Syniti Replicate uses this user ID during synchronization to read the database logs and perform the synchronization operations. Therefore, any transactions found in the logs with this user ID are not replicated as part of the synchronization data.

Enable Transactional Replication Wizard

For transactional replications (mirroring and synchronization), use the Enable Transactional Replication wizard after setting up a source connection. The following fields require specific information for Oracle:

Log Type Screen

Select whether you plan to perform replications using the Log Reader (default) or Log Server Agent.

Log Settings Screen

Refer to the Log Reader Settings section on Oracle Transactional Replication Settings for more information.

Change Log Settings Dialog Box

Refer to Oracle Change Log Settings Dialog box for more information.

Agent Settings Screen

Use Log Container

If your data source is an Oracle version 12 plugable non-root database, you also need to complete the root database connection information:

  • Server, User, Password: The connection string for the root database. This string is required because Oracle 12 does not keep redo log records for the entire database instance separately.

  • Use Remote LSA: This option is available for environments where it is preferable to read the Oracle log directly and involves the installation and configuration of an additional component on the Oracle server. Contact the technical support team via the Help Center for additional information.

Create Replication Wizard

After creating a connection, and setting up for transactional replications, create the replication. The following fields require specific information for Oracle.

Source Log Info

  • Service Name: This name is a unique identifier for your Oracle Server. The value is obtained automatically from your Oracle database server and cannot be modified.

  • Dictionary File: If using Oracle 9 or later, you can leave this field blank to use the online dictionary. If you prefer to supply a dictionary file, provide a path and dictionary file name on your Oracle database server. The online dictionary/dictionary file contains table information that is used in replication.

Configure the Oracle Client

Oracle 11 and above Client installation does not require extra steps to set up the Net Service Name for Oracle. For this reason, using an Oracle 11 or above client is recommended. However, for those who are required to use Oracle 10, this section provides information on configuring the Oracle 10 Client and the Net Service Name for the Oracle client.

Complete the following steps:

  1. Run the installation process for the Oracle client that contains the Oracle .NET Provider. The Oracle Net Configuration Assistant starts up automatically after you install the Oracle client.

    • You can also start it manually from the Windows Start menu and then select All Programs > Oracle – OraClient10g_homes1 > Configuration and Migration Tools > Net Configuration Assistant.

  2. Enter the service name and select Next.

  3. Select TCP as the ACCESS_LOG network protocol.

  4. Enter the IP address of your Oracle system and select Next.

  5. Select the Yes, perform a test option and select Next. The test may fail if you do not have the correct login and password information.

  6. Select Change Login and enter the user ID and password of your Oracle system, and then select OK.

  7. When the test is successful, select Next. Either accept the default for the NET service name or enter a new service name and select Next.

  8. Follow the instructions in the wizard to complete the setup.

Further Resources