Capture from Amazon RDS for Oracle

Fivetran HVR supports log-based capture and integrate into Amazon RDS for Oracle database. This section provides the information required for replicating changes in Amazon RDS for Oracle.

Capture from Oracle ASM is supported only when the capture method is LogMiner.

Capture from Oracle using the LOGMINER method will be deprecated in one of the future HVR 6 releases. We encourage you to consider migrating to BFILE or DIRECT capture methods.

In HVR UI, the connection parameters for Amazon RDS for Oracle must be supplied while creating an Oracle location with Amazon RDS for Oracle in Step 3. Configure Location Connection.

Connecting to Amazon RDS for Oracle

To enable the HVR capture or integrate process to connect to Amazon RDS for Oracle, you must allow inbound traffic on the database listener port to the system running the HVR process. If an HVR Agent is in place, then communication must be enabled for the system where the HVR Agent is running. When directly connected from an HVR Hub Server, the connection must be allowed for the HVR Hub Server. If the HVR system connecting to Amazon RDS for Oracle runs in the same VPC as Amazon RDS for Oracle, you can use the internal rather than public IP address for the service to allow access. It is recommended to restrict access to only the HVR system that requires access, rather than allowing broader or public access.

The default database listener port that must be opened for TCP/IP connection is 1521.

NOTE: The port may have been changed from the default by an administrator.

Logging Modes

The following logging modes must be enabled for the Amazon RDS DB instances. You can use the Amazon RDS procedure mentioned below to enable/disable the logging modes.

Force Logging - Oracle logs all changes to the database except changes in temporary tablespaces and temporary segments (NOLOGGING clauses are ignored).
```
exec rdsadmin.rdsadmin_util.force_logging(p_enable => true);
```
Supplemental Logging - To ensure that LogMiner and products that use LogMiner have sufficient information to support chained rows and storage arrangements such as cluster tables.
```
exec rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action=>'ADD');
```
Switch Online Log Files - To prevent the following error in HVR: Log scanning error F_JZ1533. The scanner could not locate the RBA file sequence number in the thread.
```
exec rdsadmin.rdsadmin_util.switch_logfile;
```
Retaining Archive Redo Logs - To retain archived redo logs on your Amazon RDS DB instance, database backups must be enabled by setting the archivelog retention hours to greater than 0 (zero) hours. Enabling database backup can be done while creating the instance or after by going to Instances > Modify > Backup and set the number of days.
The following example retains 24 hours of the redo log.
```
begin
rdsadmin.rdsadmin_util.set_configuration (name => 'archivelog retention hours', value => '24'); 
end;
/
```
Set the backup retention period for your Amazon RDS DB instance to one day or longer. Setting the backup retention period ensures that the database is running in ARCHIVELOG mode. For example, enable automated backups by setting the backup retention period to three days:
```
aws rds modify-db-instance \

--db-instance-identifier mydbinstance \

--backup-retention-period 3 \

--apply-immediately
```
In Amazon RDS for Oracle, disabling automated backups may lead to replication issues in HVR.

For better performance, it is recommended to install HVR on Amazon Elastic Cloud 2 (EC2) instance in the same region as the RDS instance.

Grants for Amazon RDS for Oracle

The database User must be granted the privileges mentioned in sections Grants for Log-Based Capture, Grants for LogMiner, and additionally the following grants for using Amazon RDS for Oracle:

For Amazon RDS for Oracle 12 and later, the HVR database User must be granted the logmining system privilege.
```
grant logmining to username;
```
The HVR database User must be granted the LogMiner specific grants by using the Amazon RDS procedure:
```
begin
rdsadmin.rdsadmin_util.grant_sys_object(
 p_obj_name => 'DBMS_LOGMNR',
 p_grantee => 'username',
 p_privilege => 'EXECUTE',
 p_grant_option => true);
end;
/
```
For more information about Amazon RDS for Oracle-specific DBA tasks, refer to Common DBA Tasks for Oracle DB Instances in AWS Documentation.