# Oracle Database@AWS Zero-ETL integration with Amazon Redshift
Zero-ETL integration is a fully managed solution that makes transactional and operational data available in Amazon Redshift from multiple sources. With this solution, you can replicate data to Amazon Redshift from your Oracle databases running on Oracle Exadata or Autonomous Database on Dedicated Exadata Infrastructure. The automatic synchronization avoids the traditional extract, transform, and load (ETL) process. It also enables real-time analytics and AI workloads. For more information, see [Zero-ETL integrations](https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl-using.html) in the *Amazon Redshift Management Guide*.
Zero-ETL integration provides the following benefits:
+ **Real-time data replication** – Continuous data synchronization from Oracle databases to Amazon Redshift with minimal latency
+ **Elimination of complex ETL pipelines** – No need to build and maintain custom data integration solutions
+ **Reduced operational overhead** – Automated setup and management through AWS APIs
+ **Simplified data integration architecture** – Seamless integration between Oracle Database@AWS and AWS analytics services
+ **Enhanced security** – Built-in encryption and AWS IAM access controls
Amazon Redshift doesn't charge an additional fee for the zero-ETL integration with Oracle Database@AWS. You pay for the existing Amazon Redshift resources used to create and process the change data created as part of a zero-ETL integration. For more information, see [Amazon Redshift pricing](https://aws.amazon.com/redshift/pricing/).
## Supported database versions for Zero-ETL integration in Oracle Database@AWS
Zero-ETL integration supports the following Oracle database versions:
+ **Oracle Exadata** – Oracle Database 19c
+ **Autonomous Database on Dedicated Infrastructure** – Oracle Database 19c and 23ai
## How Zero-ETL integration works in Oracle Database@AWS
Zero-ETL integration allows Oracle Database@AWS to replicate data to Amazon Redshift. The integration leverages Amazon VPC Lattice to create secure network connectivity. Change data capture (CDC) technology ensures real-time data synchronization. You manage the integration through AWS Glue APIs.
The Zero-ETL integration architecture includes the following:
+ **Secure connectivity** – Uses SSL/TLS encryption over TLS port 2484 for data transfer
+ **AWS Secrets Manager** – Stores database credentials and certificates securely using AWS Key Management Service
+ **AWS Glue integration** – Provides unified management interface for zero-ETL integrations
Replication proceeds through the following steps:
1. Establishing secure connection to the Oracle database using SSL on port 2484
1. Performing an initial full dump of selected databases, schemas, and tables
1. Setting up change data capture (CDC) for ongoing real-time replication
1. Writing the replicated data to the target Amazon Redshift cluster
**Important**
Zero-ETL integration isn't enabled by default. You must configure it using AWS Glue APIs. You can't set up zero-ETL integration directly using Oracle Database@AWS APIs.
# Prerequisites for zero-ETL integration in Oracle Database@AWS
Before setting up zero-ETL integration, ensure that you meet the following prerequisites.
## General prerequisites
+ **Oracle Database@AWS setup** – Make sure you have at least one VM cluster provisioned and running.
+ **Integration with zero-ETL enabled** – Make sure your VM cluster or Autonomous VM cluster is associated with an ODB network that has zero-ETL enabled.
+ **Supported Oracle Database versions** – You must use Oracle Database 19c (Oracle Exadata) or Oracle Database 19c/23ai (Autonomous Database on Dedicated Infrastructure).
+ **Same AWS Region** – The source Oracle database and target Amazon Redshift cluster must be in the same AWS Region.
## Oracle database prerequisites
You must configure your Oracle database with the following settings.
### Replication user setup
Create a dedicated replication user in each pluggable database (PDB) that you want to replicate:
+ **For Oracle Exadata** – Create user `ODBZEROETLADMIN` with a secure password.
+ **For Autonomous Database on Dedicated Infrastructure** – Use the existing `GGADMIN` user.
Grant the following permissions to the replication user.
```
-- For Autonomous Database on Dedicated Infrastructure only
ALTER USER GGADMIN ACCOUNT UNLOCK;
ALTER USER GGADMIN IDENTIFIED BY ggadmin-password;
-- For Oracle Exadata only
GRANT SELECT ON any-replicated-table TO "ODBZEROETLADMIN";
GRANT LOGMINING to "ODBZEROETLADMIN";
-- Grant the following permissions to all services.
-- For Oracle Exadata, use the ODBZEROETLADMIN user. For Autonomous Database on Dedicated Infrastructure,
-- use the GGADMIN user.
GRANT CREATE SESSION TO "ODBZEROETLADMIN";
GRANT SELECT ANY TRANSACTION TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$ARCHIVED_LOG TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$LOG TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$LOGFILE TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$LOGMNR_LOGS TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$LOGMNR_CONTENTS TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$DATABASE TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$THREAD TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$PARAMETER TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$NLS_PARAMETERS TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$TIMEZONE_NAMES TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$TRANSACTION TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$CONTAINERS TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_INDEXES TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_OBJECTS TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_TABLES TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_USERS TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_CATALOG TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_CONSTRAINTS TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_CONS_COLUMNS TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_TAB_COLS TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_IND_COLUMNS TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_ENCRYPTED_COLUMNS TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_LOG_GROUPS TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_TAB_PARTITIONS TO "ODBZEROETLADMIN";
GRANT SELECT ON SYS.DBA_REGISTRY TO "ODBZEROETLADMIN";
GRANT SELECT ON SYS.OBJ$ TO "ODBZEROETLADMIN";
GRANT SELECT ON DBA_TABLESPACES TO "ODBZEROETLADMIN";
GRANT SELECT ON DBA_OBJECTS TO "ODBZEROETLADMIN";
GRANT SELECT ON SYS.ENC$ TO "ODBZEROETLADMIN";
GRANT SELECT ON GV_$TRANSACTION TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$DATAGUARD_STATS TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$DATABASE_INCARNATION TO "ODBZEROETLADMIN";
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO "ODBZEROETLADMIN";
GRANT SELECT ON SYS.DBA_DIRECTORIES TO "ODBZEROETLADMIN";
GRANT SELECT ON ALL_VIEWS TO "ODBZEROETLADMIN";
GRANT SELECT ON DBA_SEGMENTS TO "ODBZEROETLADMIN";
GRANT SELECT ON V_$TRANSPORTABLE_PLATFORM TO "ODBZEROETLADMIN";
GRANT CREATE ANY DIRECTORY TO "ODBZEROETLADMIN";
GRANT EXECUTE ON DBMS_FILE_TRANSFER TO "ODBZEROETLADMIN";
GRANT EXECUTE ON DBMS_FILE_GROUP TO "ODBZEROETLADMIN";
GRANT EXECUTE on DBMSLOGMNR to "ODBZEROETLADMIN";
GRANT SELECT on V_$LOGMNRLOGS to "ODBZEROETLADMIN";
GRANT SELECT on V_$LOGMNRCONTENTS to "ODBZEROETLADMIN";
GRANT LOGMINING to "ODBZEROETLADMIN";
GRANT SELECT ON GV_$CELL_STATE TO "ODBZEROETLADMIN";
```
### Supplemental logging
Enable supplemental logging on your Oracle database to capture change data.
```
-- Check if supplemental logging is enabled
SELECT supplemental_log_data_min FROM v$database;
-- Enable supplemental logging if not already enabled.
-- For Oracle Exadata, enable supplemental logging on both the CDB and PDB.
-- For Autonomous Database on Dedicated Infrastructure, enable supplemental logging on the PDB only.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- For Autonomous Database on Dedicated Infrastructure only
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
-- Archive current online redo log
ALTER SYSTEM ARCHIVE LOG CURRENT;
```
### SSL configuration
To set up a zero-ETL integration between Oracle Database@AWS and Amazon Redshift, you must configure SSL.
**For Oracle Exadata databases**
You must manually configure SSL on port 2484. This task involves the following:
+ Configuring `(PROTOCOL=tcps)(PORT=2484)` in `listener.ora`
+ Setting up the wallet using `sqlnet.ora`
+ Generating and configuring SSL certificates (see [How To Configure SSL/TCPS For Exadata Cloud Database (ExaCC/ExaCS) (Doc ID 2947301.1)](https://support.oracle.com/knowledge/Oracle%20Database%20Products/2947301_1.html) in the My Oracle Support documentation)
**For Autonomous Databases**
SSL on port 2484 is enabled by default. No additional configuration is required.
The SSL port is fixed as 2484.
## AWS service prerequisites
Before setting up zero-ETL integration, set up AWS Secrets Manager and configure IAM permissions.
### Set up AWS Secrets Manager
Store your Oracle database credentials in AWS Secrets Manager as follows:
1. Create a Customer Managed Key (CMK) in AWS Key Management Service.
1. Store database credentials in AWS Secrets Manager using the CMK.
1. Configure resource policies to allow Oracle Database@AWS access.
To get your TDE key ID and password, use the technique described in [Supported encryption methods for using Oracle as a source for AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Encryption). The following command generates the base64 wallet.
```
base64 -i cwallet.sso > wallet.b64
```
The following example shows a secret for Oracle Exadata. For *asm\$1service\$1name*, the *111.11.11.11* represents the virtual IP for the VM node. You can also register the ASM listener with SCAN.
```
{
"database_info": [
{
"name": "ODBDB_ZETLPDB",
"service_name": "ODBDB_ZETLPDB.paas.oracle.com",
"username": "ODBZEROETLADMIN",
"password": "secure_password",
"tde_key_id": "ORACLE.SECURITY.DB.ENCRYPTION.key_id",
"tde_password": "tde_password",
"certificateWallet": "base64_encoded_wallet_content"
}
],
"asm_info": {
"asm_user": "odbzeroetlasm",
"asm_password": "secure_password",
"asm_service_name": "111.11.11.11:2484/+ASM"
}
}
```
The following example shows a secret for Autonomous Database on Dedicated Infrastructure.
```
{
"database_info": [
{
"database_name": "ZETLACD_ZETLADBMORECPU",
"service_name": "ZETLADBMORECPU_high.adw.oraclecloud.com",
"username": "ggadmin",
"password": "secure_password",
"certificateWallet": "base64_encoded_wallet_content"
}
]
}
```
### Configure IAM permissions
Create IAM policies that allow zero-ETL integration operations. The following example policy allows describe, create, update, and delete operations for an Exadata VM cluster. For an Autonomous VM cluster, use the value `cloud-autonomous-vm-cluster` instead of `cloud-vm-cluster` for the resource ARN.
# Considerations for zero-ETL integration in Oracle Database@AWS
When setting up Zero-ETL integration between Oracle Database@AWS and Amazon Redshift, consider the following guidelines:
**Initial data load time**
The initial full load time depends on the size of your database. Large databases might take several hours or days to complete the initial synchronization.
**Oracle database performance**
Change data capture might impact Oracle database performance, especially during high transaction volumes. After enabling Zero-ETL integration, monitor your database performance.
**Schema changes**
Data Definition Language (DDL) changes in the source Oracle database might require you to intervene manually to re-create the integration. Plan schema changes carefully.
For general considerations, see [Considerations when using zero-ETL integrations with Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/zero-etl.reqs-lims.html).
# Limitations for zero-ETL integration in Oracle Database@AWS
Note the following general limitations:
**Single PDB per integration**
Each zero-ETL integration can only replicate data from one pluggable database (PDB). Data filters like `include: pdb1.*.*, include: pdb2.*.*` aren't supported.
**Single integration per Autonomous Database or Exadata Infrastructure**
Each zero-ETL integration can only replicate data from one Autonomous Database on Dedicated Infrastructure.
**Fixed SSL port**
SSL connections must use port 2484.
**Same Region requirement**
The source Oracle Database@AWS VM cluster and target Amazon Amazon Redshift cluster must be in the same AWS Region. Cross-region replication isn't supported.
**No mTLS support**
Mutual TLS (mTLS) isn't supported. If your OCI database has mTLS enabled, you must disable it to use zero-ETL integration.
**Immutable integration settings**
After you create the secret ARN or KMS key associated with an integration, you can't modify it. You must delete and re-create the integration to change these settings.
**TDE column-level encryption**
Column-level Transparent Data Encryption (TDE) isn't supported for Oracle Exadata databases. Only tablespace-level TDE is supported.
**Data type support**
Some Oracle-specific data types might not be fully supported or might require transformation during replication. Test your specific data types thoroughly before you deploy your database to production.
# Setting up Oracle Database@AWS integrations with Amazon Redshift
To set up zero-ETL integration between your Oracle database and Amazon Redshift, complete the following steps:
1. Enable Zero-ETL on your ODB network.
1. Configure Oracle database prerequisites.
1. Set up AWS Secrets Manager and AWS Key Management Service.
1. Configure IAM permissions.
1. Set up Amazon Redshift resource policies.
1. Create the zero-ETL integration.
1. Create the target database in Amazon Redshift.
## Step 1: Enable Zero-ETL for your ODB network
You can enable the zero-ETL integration for the ODB network associated with your source VM cluster. By default, this integration is disabled.
### Console
**To enable zero-ETL integration**
1. Open the Oracle Database@AWS console at [https://console.aws.amazon.com/odb/](https://console.aws.amazon.com/odb/).
1. In the navigation pane, choose **ODB networks**.
1. Select the ODB network for which you want to enable the zero-ETL integration.
1. Choose **Modify**.
1. Select **Zero-ETL**.
1. Choose **Continue** and then **Modify**.
### AWS CLI
To enable the zero-ETL integration, use the `update-odb-network` command with the `--zero-etl-access` parameter:
```
aws odb update-odb-network \
--odb-network-id odb-network-id \
--zero-etl-access ENABLED
```
To enable zero-ETL integration for the ODB network associated with your source VM cluster, use the `update-odb-network` command. This command configures the network infrastructure required for zero-ETL integration.
```
aws odb update-odb-network \
--odb-network-id your-odb-network-id \
--zero-etl-access ENABLED
```
## Step 2: Configure your Oracle database
Complete the Oracle database configuration as described in the [Prerequisites](zero-etl-prerequisites.md):
+ Create replication users and grant necessary permissions.
+ Enable archived redo logs.
+ Configure SSL (Oracle Exadata only).
+ Set up ASM users if applicable (Oracle Exadata only).
## Step 3: Set up AWS Secrets Manager and AWS Key Management Service
Create a Customer Managed Key (CMK) and store your database credentials.
1. Create a CMK in AWS Key Management Service using the `create-key` command.
```
aws kms create-key \
--description "ODB Zero-ETL Integration Key" \
--key-usage ENCRYPT_DECRYPT \
--key-spec SYMMETRIC_DEFAULT
```
1. Store your database credentials in AWS Secrets Manager.
```
aws secretsmanager create-secret \
--name "ODBZeroETLCredentials" \
--description "Credentials for Oracle Database@AWS Zero-ETL integration" \
--kms-key-id your-cmk-key-arn \
--secret-string file://secret-content.json
```
1. Attach a resource policy to the secret to allow Oracle Database@AWS access.
```
aws secretsmanager put-resource-policy \
--secret-id "ODBZeroETLCredentials" \
--resource-policy file://secret-resource-policy.json
```
In the preceding command, `secret-resource-policy.json` contains the following JSON.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "zetl.odb.amazonaws.com"
},
"Action": [
"secretsmanager:GetSecretValue",
"secretsmanager:DescribeSecret"
],
"Resource": "*"
}
]
}
```
------
1. Attach a resource policy to the CMK. The CMK resource policy must include permissions for both the Oracle Database@AWS service principal and the Amazon Redshift service principal to support encrypted Zero-ETL integration.
```
aws kms put-key-policy \
--key-id your-cmk-key-arn \
--policy-name default \
--policy file://cmk-resource-policy.json
```
The `cmk-resource-policy.json` file should include the following policy statements. The first statement allows Oracle Database@AWS service access, and the second statement allows Amazon Redshift to create grants on the KMS key for encrypted data operations.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Sid": "Allow ODB service access",
"Effect": "Allow",
"Principal": {
"Service": "zetl.odb.amazonaws.com"
},
"Action": [
"kms:Decrypt",
"kms:GenerateDataKey",
"kms:CreateGrant"
],
"Resource": "*"
},
{
"Sid": "Allows the Redshift service principal to add a grant to a KMS key",
"Effect": "Allow",
"Principal": {
"Service": "redshift.amazonaws.com"
},
"Action": "kms:CreateGrant",
"Resource": "*",
"Condition": {
"StringEquals": {
"kms:EncryptionContext:{context-key}": "{context-value}"
},
"ForAllValues:StringEquals": {
"kms:GrantOperations": [
"Decrypt",
"GenerateDataKey",
"CreateGrant"
]
}
}
}
]
}
```
------
## Step 4: Configure IAM permissions
Create and attach IAM policies that allow zero-ETL integration operations.
```
aws iam create-policy \
--policy-name "ODBZeroETLIntegrationPolicy" \
--policy-document file://odb-zetl-iam-policy.json
aws iam attach-user-policy \
--user-name your-iam-username \
--policy-arn policy-arn
```
The following policy grants the necessary permissions.
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Sid": "ODBGlueIntegrationAccess",
"Effect": "Allow",
"Action": [
"glue:CreateIntegration",
"glue:ModifyIntegration",
"glue:DeleteIntegration",
"glue:DescribeIntegrations",
"glue:DescribeInboundIntegrations"
],
"Resource": "*"
},
{
"Sid": "ODBZetlOperations",
"Effect": "Allow",
"Action": "odb:CreateOutboundIntegration",
"Resource": "*"
},
{
"Sid": "ODBRedshiftFullAccess",
"Effect": "Allow",
"Action": [
"redshift:*",
"redshift-serverless:*",
"ec2:DescribeAccountAttributes",
"ec2:DescribeAddresses",
"ec2:DescribeAvailabilityZones",
"ec2:DescribeSecurityGroups",
"ec2:DescribeSubnets",
"ec2:DescribeVpcs",
"ec2:DescribeInternetGateways",
"sns:CreateTopic",
"sns:Get*",
"sns:List*",
"cloudwatch:Describe*",
"cloudwatch:Get*",
"cloudwatch:List*",
"cloudwatch:PutMetricAlarm",
"cloudwatch:EnableAlarmActions",
"cloudwatch:DisableAlarmActions",
"tag:GetResources",
"tag:UntagResources",
"tag:GetTagValues",
"tag:GetTagKeys",
"tag:TagResources"
],
"Resource": "*"
},
{
"Sid": "ODBRedshiftDataAPI",
"Effect": "Allow",
"Action": [
"redshift-data:ExecuteStatement",
"redshift-data:CancelStatement",
"redshift-data:ListStatements",
"redshift-data:GetStatementResult",
"redshift-data:DescribeStatement",
"redshift-data:ListDatabases",
"redshift-data:ListSchemas",
"redshift-data:ListTables",
"redshift-data:DescribeTable"
],
"Resource": "*"
},
{
"Sid": "ODBKMSAccess",
"Effect": "Allow",
"Action": [
"kms:CreateKey",
"kms:DescribeKey",
"kms:Encrypt",
"kms:Decrypt",
"kms:GenerateDataKey",
"kms:ListKeys",
"kms:CreateAlias",
"kms:ListAliases"
],
"Resource": "*"
},
{
"Sid": "ODBSecretsManagerAccess",
"Effect": "Allow",
"Action": [
"secretsmanager:GetSecretValue",
"secretsmanager:PutSecretValue",
"secretsmanager:CreateSecret",
"secretsmanager:UpdateSecret",
"secretsmanager:DeleteSecret",
"secretsmanager:DescribeSecret",
"secretsmanager:ListSecrets",
"secretsmanager:GetResourcePolicy",
"secretsmanager:PutResourcePolicy",
"secretsmanager:ValidateResourcePolicy"
],
"Resource": "*"
}
]
}
```
------
## Step 5: Configure Amazon Redshift resource policies
Set up resource policies on your Amazon Redshift cluster to authorize inbound integrations.
```
aws redshift put-resource-policy \
--no-verify-ssl \
--resource-arn "your-redshift-cluster-arn" \
--policy '{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "redshift.amazonaws.com"
},
"Action": [
"redshift:AuthorizeInboundIntegration"
],
"Condition": {
"StringEquals": {
"aws:SourceArn": "your-vm-cluster-arn"
}
}
},
{
"Effect": "Allow",
"Principal": {
"AWS": "your-account-id"
},
"Action": [
"redshift:CreateInboundIntegration"
]
}
]
}' \
--region us-west-2
```
**Tip**
Alternatively, you can use the **Fix it for me** option in the AWS console. This option automatically configures the required Amazon Redshift policies without your needing to do it manually.
## Step 6: Create the zero-ETL integration using AWS Glue
Create the zero-ETL integration using the AWS Glue `create-integration` command. In this command, you specify the source VM cluster and the target Amazon Redshift namespace.
The following example creates an integration with a PDB named `pdb1` running in an Exadata VM cluster. You can also create an Autonomous VM cluster by replacing `cloud-vm-cluster` with `cloud-autonomous-vm-cluster` in the source ARN. Specifying a KMS key is optional. If you specify a key, it can be different from the one that you created in [Step 3: Set up AWS Secrets Manager and AWS Key Management Service](#zero-etl-setup-secrets).
```
aws glue create-integration \
--integration-name "MyODBZeroETLIntegration" \
--source-arn "arn:aws:odb:region:account:cloud-vm-cluster/cluster-id" \
--target-arn "arn:aws:redshift:region:account:namespace/namespace-id" \
--data-filter "include: pdb1.*.*" \
--integration-config '{
"RefreshInterval": "10",
"IntegrationMode": "DEFAULT",
"SourcePropertiesMap": {
"secret-arn": "arn:aws:secretsmanager:region:account:secret:secret-name"
}
}' \
--description "Zero-ETL integration for Oracle to Amazon Redshift" \
--kms-key-id "arn:aws:kms:region:account:key/key-id"
```
The command returns an integration ARN and sets the status to `creating`. You can monitor the integration status using the `describe-integrations` command.
```
aws glue describe-integrations \
--integration-identifier integration-id
```
**Important**
Only one PDB per integration is supported. The data filter must specify a single PDB, for example, `include: pdb1.*.*`. The source must be in the same AWS Region and account in which the integration is being created.
## Step 7: Create a target database in Amazon Redshift
After the integration is active, create a target database in your Amazon Redshift cluster.
```
-- Connect to your Amazon Redshift cluster
psql -h your-redshift-endpoint -U username -d database
-- Create database from integration
CREATE DATABASE target_database_name
FROM INTEGRATION 'integration-id'
DATABASE "source_pdb_name";
```
After creating the target database, you can query the replicated data.
```
-- List databases to verify creation
\l
-- Connect to the new database
\c target_database_name
-- List tables to see replicated data
\dt
```
## Verify the zero-ETL integration
Verify that the integration works by querying the integration status in AWS Glue and making sure that your Oracle changes are being replicated to Amazon Redshift.
**To verify that your zero-ETL integration is working correctly**
1. Check the integration status.
```
aws glue describe-integrations \
--integration-identifier integration-id
```
The status should be `ACTIVE` or `REPLICATING`.
1. Verify data replication by making changes in your Oracle database and checking that they appear in Amazon Redshift.
1. Monitor replication metrics in Amazon CloudWatch (if available).
# Data filtering for zero-ETL integrations in Oracle Database@AWS
Oracle Database@AWS zero-ETL integrations support data filtering. You can use it to control which data your source Oracle Exadata database replicates to your target data warehouse. Instead of replicating the entire database, you can apply one or more filters to selectively include or exclude specific tables. This helps you optimize storage and query performance by ensuring that only relevant data is transferred. Filtering is limited to the database and table levels. Column- and row-level filtering aren't supported.
Oracle Database and Amazon Redshift handle object name casing differently, which affects both data filter configuration and target queries. Note the following:
+ Oracle Database stores database, schema, and object names in uppercase unless explicitly quoted in the `CREATE` statement. For example, if you create `mytable` (no quotes), the Oracle data dictionary stores the table name as `MYTABLE`. If you quote the object name in your creation statement, the Oracle data dictionary preserves the case.
+ Zero-ETL data filters are case sensitive and must match the exact case of object names as they appear in the Oracle data dictionary. For example, if the Oracle dictionary stores schema and table name `REINVENT.MYTABLE`, then create a filter using `include: ORCL.REINVENT.MYTABLE`.
+ Amazon Redshift queries default to lowercase object names unless explicitly quoted. For example, a query of `MYTABLE` (no quotes) searches for `mytable`.
Be mindful of the case differences when you create the Amazon Redshift filter and query the data. The filtering considerations for Oracle Database@AWS are the same as for Amazon RDS for Oracle. For examples of how case can affect data filters in an Oracle database, see [RDS for Oracle examples](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/zero-etl.filtering.html#zero-etl.filtering-examples-oracle) in the *Amazon Relational Database Service User Guide*.
# Monitoring zero-ETL integration
Regular monitoring of your zero-ETL integration ensures optimal performance and helps identify issues early.
## Integration status monitoring
Monitor the status of your zero-ETL integrations using AWS Glue APIs.
```
# Check status of a specific integration
aws glue describe-integrations \
--integration-identifier integration-id
# List all integrations in your account
aws glue describe-integrations
```
Integration statuses include:
+ **creating** – Integration is being set up
+ **active** – Integration is running and replicating data
+ **modifying** – Integration configuration is being updated
+ **needs\$1attention** – Integration requires manual intervention
+ **failed** – Integration has encountered an error
+ **deleting** – Integration is being removed
## Performance monitoring
Monitor the following aspects of your zero-ETL integration performance:
+ **Replication lag** – The time difference between when a change occurs in Oracle and when it appears in Amazon Redshift
+ **Data throughput** – The volume of data being replicated per unit of time
+ **Error rates** – The frequency of replication errors or failures
+ **Resource utilization** – CPU, memory, and network usage on both source and target systems
Use Amazon CloudWatch to monitor these metrics and set up alarms for critical thresholds.
# Managing zero-ETL integrations in Oracle Database@AWS
After creating a zero-ETL integration, you can perform various management operations including modifying and deleting integrations. This section covers the ongoing management of your zero-ETL integrations.
## Modifying zero-ETL integrations
You can modify only the name, description, and data filtering options for a zero-ETL integration in a supported data warehouse. You can't modify the AWS Key Management Service key used to encrypt the integration, or the source or target databases.
### Prerequisites for modifying integrations
Before you modify a zero-ETL integration, ensure that you have the following:
+ **Required permissions** – Your IAM user or role must have the `odb:UpdateOutboundIntegration` permission in addition to the standard AWS Glue permissions.
+ **Integration in active state** – The integration must be in an `ACTIVE` state, not in `CREATING`, `MODIFYING`, `DELETING`, or `FAILED`.
+ **Valid data filter syntax** – New data filters must follow the supported include/exclude pattern syntax.
### Modifying data filters
You can change which tables or schemas are replicated by modifying the data filter. In this way, you can add or remove database objects from replication without recreating the entire integration.
To modify the data filter for an integration, use the `modify-integration` command.
```
aws glue modify-integration \
--integration-identifier integration-id \
--data-filter "include: pdb1.new_schema.*"
```
You can also modify the integration name and description at the same time. In the following example, you modify the integration name, descriptions, and filters for two schemas in `pdb1`.
```
aws glue modify-integration \
--integration-identifier integration-id \
--data-filter "include: pdb1.schema1.*, pdb1.schema2.*" \
--integration-name "Updated Integration Name" \
--description "Updated integration description"
```
**Important**
When you modify the data filter, the integration enters a `modifying` state and performs a resynchronization of data. The integration stops replication, applies the new filter settings, and resumes replication with a reload-target operation. Monitor the integration status to ensure the modification completes successfully.
### Considerations for data filter modifications to zero-ETL integrations
Consider the following when modifying data filters:
+ **Single PDB limitation** – You can only specify one pluggable database (PDB) per integration. Data filters like `include: pdb1.*.*, include: pdb2.*.*` aren't supported
+ **Replication interruption** – Data replication stops during the modification process and resumes after the new filter is applied.
+ **Data reload** – The integration performs a full reload of data that matches the new filter criteria.
+ **Performance impact** – Large data filter changes might take significant time to complete and can affect the source database performance during the reload.
### Limitations for modifications to zero-ETL integration settings
You can't modify the following settings after you create a zero-ETL integration:
+ **Secret ARN** – The AWS Secrets Manager secret containing database credentials
+ **KMS key** – The customer managed key used for encryption
+ **Source ARN** – The Oracle Database@AWS VM cluster
+ **Target ARN** – The Amazon Redshift cluster or namespace
To change these settings, delete the existing zero-ETL integration and create a new one.
## Deleting zero-ETL integrations
When you no longer need a zero-ETL integration, you can delete it to stop replication and clean up associated resources.
### Deletion using AWS Glue
Delete a zero-ETL integration using the AWS Glue API.
```
aws glue delete-integration \
--integration-identifier integration-id
```
You can delete integrations in the following states:
+ **active**
+ **needs\$1attention**
+ **failed**
+ **syncing**
### Effects of deletion
When you delete a zero-ETL integration, consider the following effects:
**Replication stops.**
Oracle Database@AWS doesn't replicate new changes from Amazon Redshift.
**Existing data is preserved.**
Data already replicated to Amazon Redshift remains available.
**The target database remains.**
The Amazon Redshift database created from the integration isn't automatically deleted.
**Important**
Deletion is irreversible. If you need to resume replication after deletion, create a new integration, which performs a full initial load.
## Best practices for zero-ETL management
Follow these best practices to ensure optimal performance, security, and cost-effectiveness of your zero-ETL integrations.
### Operational best practices
These operational practices help maintain reliable and efficient zero-ETL integrations.
**Regular monitoring**
Set up CloudWatch alarms to monitor integration health and performance metrics.
**Credential rotation**
Regularly rotate database passwords and update them in AWS Secrets Manager.
**Backup verification**
Regularly verify that your Oracle database backups include the necessary components for disaster recovery.
**Performance testing**
Test the impact of zero-ETL integration on your Oracle database performance, especially during peak usage periods.
**Schema change planning**
Plan and test schema changes in a development environment before applying them to production.
### Security best practices
Implement these security measures to protect your zero-ETL integration and data.
**Least privilege access**
Grant only the minimum necessary permissions to replication users and AWS IAM roles.
**Network security**
Use security groups and NACLs to restrict network access to only required ports and sources.
**Encryption at rest**
Ensure that both Oracle databases and Amazon Redshift clusters use encryption at rest.
**Audit logging**
Enable audit logging on both Oracle and Amazon Redshift to track data access and changes.
**Secret management**
Use AWS Secrets Manager automatic rotation features where possible.
### Cost optimization
Apply these strategies to optimize costs while maintaining effective zero-ETL integration performance.
**Data filtering**
Use precise data filters to replicate only the data you need, reducing storage and compute costs.
**Amazon Redshift optimization**
Use appropriate Amazon Redshift node types and implement data compression to optimize costs.
**Monitoring usage**
Regularly review your zero-ETL integration usage and costs through AWS Cost Explorer.
**Cleanup unused integrations**
Delete integrations that are no longer needed to avoid ongoing charges.
# Troubleshooting Zero-ETL integration
This section provides guidance for resolving common issues with zero-ETL integration.
## Zero-ETL integration setup failures
**Authentication failures**
+ Verify that the replication user exists and has the correct password in AWS Secrets Manager.
+ Ensure that all required permissions have been granted to the replication user.
+ Check that the secret ARN is correct and accessible by Oracle Database@AWS.
+ Verify that the CMK resource policy allows access by Oracle Database@AWS service principal.
**Network connectivity issues**
+ Make sure that your ODB network has the zero-ETL integration enabled.
+ Verify that SSL is properly configured on port 2484 (Exadata only).
+ Check that the Oracle database listener is running and accepting connections.
+ Ensure that network security groups and NACLs allow traffic on port 2484.
+ Verify that the service name in your secret matches the actual Oracle service name.
**Permission errors**
+ Check that your IAM user or role has the necessary permissions for AWS Glue integration operations.
+ Verify that the Amazon Redshift resource policy allows inbound integrations from your VM cluster.
+ Ensure that Oracle Database@AWS has been granted access to your secrets and AWS Key Management Service key.
## Replication issues
**Initial load failures**
+ Verify that the Oracle database has sufficient resources to support the full load operation.
+ Ensure that supplemental logging is enabled on the source database.
+ Check for any table-level locks or constraints that might prevent data extraction.
**Change data capture issues**
+ Verify that the Oracle database has adequate redo log space and retention.
+ Check that the replication user has access to archived redo logs.
+ For ASM-enabled systems, ensure that the ASM user is properly configured.
+ Monitor Oracle database performance to ensure CDC is not causing resource contention.
**High replication lag**
+ Monitor the replication lag metrics in CloudWatch.
+ Check for high transaction volumes or large transactions in the source database.
+ Verify that the Amazon Redshift cluster has adequate capacity to handle incoming data.
## Data consistency issues
**Missing or incomplete data**
+ Verify that the data filter includes all required schemas and tables.
+ Check for unsupported data types that may be causing replication failures.
+ Ensure that the replication user has SELECT permissions on all required tables.
**Data type conversion errors**
+ Review the supported data type mappings between Oracle and Redshift.
+ Check for Oracle-specific data types that may require custom handling.
+ Consider modifying your Oracle schema to use more compatible data types.
## Monitoring and debugging
Use the following approaches to monitor and debug Zero-ETL integration issues:
+ **Integration status monitoring** – Regularly check the integration status using `aws glue describe-integrations`.
+ **CloudWatch metrics** – Monitor available CloudWatch metrics for replication performance and errors.
+ **Oracle database monitoring** – Monitor Oracle database performance and resource utilization.
+ **Redshift monitoring** – Monitor Amazon Redshift cluster performance and storage utilization.
For complex issues that cannot be resolved using this troubleshooting guide, contact AWS Support with the following information:
+ Integration ARN and current status.
+ Error messages from integration describe operations.
+ Oracle database and Amazon Redshift cluster configurations.
+ Timeline of when the issue started occurring.