Modernize your information warehouse by migrating Oracle Database to Amazon Redshift with Oracle GoldenGate

On this publish, we present find out how to migrate an Oracle information warehouse to Amazon Redshift utilizing Oracle GoldenGate and DMS Schema Conversion, a function of AWS Database Migration Service (AWS DMS). This strategy facilitates minimal enterprise disruption via steady replication. Amazon Redshift is a quick, totally managed, petabyte-scale information warehouse service that makes it easy and cost-effective to effectively analyze your information utilizing your present enterprise intelligence instruments.

Answer overview

Our migration strategy combines DMS Schema Conversion for schema migration and Oracle GoldenGate for information replication. The migration course of consists of 4 primary steps:

Schema conversion utilizing DMS Schema Conversion.
Preliminary information load utilizing Oracle GoldenGate.
Change information seize (CDC) for ongoing replication.
Ultimate cutover to Amazon Redshift.

The next diagram exhibits the migration workflow structure from Oracle to Amazon Redshift, the place DMS Schema Conversion handles schema migration and Oracle GoldenGate manages each preliminary information load and steady replication via Extract and Replicat processes working on Amazon Elastic Compute Cloud (Amazon EC2) situations. The answer facilitates minimal downtime by sustaining real-time information synchronization till the ultimate cutover.

The answer contains the next key migration elements:

Within the following sections, we stroll via find out how to migrate an Oracle information warehouse to Amazon Redshift. For demonstration functions, we use an Oracle information warehouse consisting of 4 tables:

dim_customer
dim_product
dim_date
fact_sales

Stipulations

We advocate reviewing the licensing necessities for Oracle GoldenGate. For extra info, seek advice from Oracle GoldenGate Licensing Info.

Run schema conversion utilizing DMS Schema Conversion

DMS Schema Conversion mechanically converts your Oracle database schemas and code objects to Amazon Redshift-compatible codecs. This contains tables, views, saved procedures, capabilities, and information sorts.

Arrange community for DMS Schema Conversion

DMS Schema Conversion requires community connectivity to each your supply and goal databases. To arrange this connectivity, full the next steps:

Specify a digital non-public cloud (VPC) and subnet the place DMS Schema Conversion will run.
Configure safety group guidelines to permit visitors between the next:
1. DMS Schema Conversion and your supply Oracle database
2. DMS Schema Conversion and your goal Redshift cluster
For on-premises databases, arrange both:
1. AWS Web site-to-Web site VPN
2. AWS Direct Join

For complete details about community configurations, seek advice from Organising a community for DMS Schema Conversion.

Retailer database credentials in AWS Secrets and techniques Supervisor

DMS Schema Conversion makes use of secrets and techniques saved in AWS Secrets and techniques Supervisor to connect with your database. For directions so as to add supply and goal credentials to Secrets and techniques Supervisor, seek advice from Retailer database credentials in AWS Secrets and techniques Supervisor.

Create S3 bucket

DMS Schema Conversion saves gadgets resembling evaluation experiences, transformed SQL code, and details about database schema objects in an S3 bucket. For directions to create an S3 bucket, seek advice from Create an S3 bucket.

Create IAM insurance policies and roles

To arrange DMS Schema Conversion, you need to create applicable IAM insurance policies and roles. This course of makes positive AWS DMS has the required permissions to entry your supply and goal databases, in addition to different AWS providers required for the migration.

Put together DMS Schema Conversion

On this part, we undergo the steps to configure DMS Schema Conversion.

Arrange occasion profile

An occasion profile specifies the community, safety, and Amazon S3 settings for DMS Schema Conversion to make use of. Create an occasion profile with the next steps:

On the AWS DMS console, select Occasion profiles within the navigation pane.
Select Create occasion profile.
For Title, enter a reputation (for instance, sc-instance).
For Community kind, we use IPv4. DMS Schema Conversion additionally presents Twin-stack mode for each IPv4 and IPv6.
For Digital non-public cloud (VPC) for IPv4, select Default VPC.
For Subnet group, select your subnet group (for this publish, default).
For VPC safety teams, select your safety teams. As beforehand said, the occasion profile’s VPC safety group will need to have entry to each the supply and goal databases.
For S3 bucket, specify a bucket to retailer schema conversion metadata.
Select Create occasion profile.

Add information suppliers

Knowledge suppliers retailer database sorts and details about supply and goal databases for DMS Schema Conversion to connect with. Configure information suppliers for the supply and goal databases with the next steps:

On the AWS DMS console, select Knowledge suppliers within the navigation pane.
Select Create information supplier.
To create your goal, for Title, enter a reputation (for instance, redshift-target).
For Engine kind, select Amazon Redshift.
For Engine configuration, choose Select from Redshift.
For Redshift cluster, select the goal Redshift cluster.
For Port, enter the port quantity.
For Database title, enter the title of your database.
Select Create information supplier.
Repeat comparable steps to create your supply information supplier.

Create migration mission

The DMS Schema Conversion migration mission defines migration entities, together with occasion profiles, supply and goal information suppliers, and migration guidelines. Create a migration mission with the next steps:

On the AWS DMS console, select Migration initiatives within the navigation pane.
Select Create migration mission.
For Title, enter a reputation to determine your migration mission (for instance, oracle-redshift-commercewh).
For Occasion profile, select the occasion profile you created.

Within the Knowledge suppliers part, enter the supply and goal information suppliers, Secrets and techniques Supervisor secret, and IAM roles.

Within the Schema conversion settings part, enter the S3 URL and select the relevant IAM function.

Select Create migration mission.

Use DMS Schema Conversion to rework Oracle database objects

Full the next steps to convert supply database objects:

On the AWS DMS console, select Migration initiatives within the navigation pane.
Select the migration mission you created.
On the Schema conversion tab, select Launch schema conversion.

The schema conversion mission can be prepared when the launch is full. The left navigation tree represents the supply database, and the proper navigation tree represents the goal database.

Generate and view the evaluation report.
Choose the objects you wish to convert after which select Convert on the Actions menu to transform the supply objects to the goal database.

The conversion course of may take a while relying on the quantity and complexity of the chosen objects.

It can save you the transformed code to the S3 bucket that you just created earlier within the prerequisite steps.

To save lots of the SQL scripts, choose the article within the goal database tree and select Save as SQL on the Actions menu.
After you finalize the scripts, run them manually within the goal database.
Alternatively, you’ll be able to apply the scripts on to the database utilizing DMS Schema Conversion. Choose the particular schema within the goal database, and on the Actions menu, select Apply adjustments.

This can apply the mechanically transformed code to the goal database.

If some objects require motion gadgets, DMS Schema conversion flags them and gives particulars of motion gadgets. For the gadgets that require decision, carry out guide adjustments and apply the transformed adjustments on to the goal database.

Carry out information migration

The migration from Oracle Database to Amazon Redshift utilizing Oracle GoldenGate begins with an preliminary load course of, the place Oracle GoldenGate’s Extract course of captures the prevailing information from the Oracle supply tables and sends this information to the Replicat course of, which hundreds it into Redshift goal tables via the suitable database connectivity. Concurrently, Oracle GoldenGate’s CDC mechanism tracks the continued adjustments (inserts, updates, and deletes) within the supply Oracle database by studying the redo logs. These captured adjustments are then synchronized to Amazon Redshift in close to actual time via the Extract-Pump-Replicat course of, facilitating information consistency between the supply and goal methods all through the migration course of.

Put together supply Oracle database for GoldenGate

Put together your database for Oracle GoldenGate, together with configuring connections and logging, enabling Oracle GoldenGate in your database, organising the flashback question, and managing server sources.

Oracle GoldenGate for BigData solely helps uncompressed UPDATE information when replicating to Amazon Redshift. When UPDATE information include lacking columns, these columns are set to null within the goal.

To deal with this example, configure Extract to generate path information with the column values (allow trandata for the columns). Alternatively, you’ll be able to disable this test by setting gg.abend.on.lacking.columns=false, which can lead to unintended NULLs on the goal database.When gg.abend.on.lacking.columns=true, Replicat course of on Oracle GoldenGate for BigData fails and returns the next error for compressed replace information:

ERROR OGG-15051 Java or JNI exception: java.lang.IllegalStateException: The UPDATE operation report within the path at pos[0/XXXXXXX] for desk [SCHEMA.TABLENAME] has lacking columns.

Set up Oracle GoldenGate software program on Amazon EC2

It’s essential to run Oracle GoldenGate on EC2 situations. The situations will need to have enough CPU, reminiscence, and storage to deal with the anticipated replication quantity. For extra particulars, seek advice from Working System Necessities. After you identify the CPU and reminiscence necessities, choose a present era EC2 occasion kind for Oracle GoldenGate.

When the EC2 occasion is up and working, obtain the next Oracle GoldenGate software program from the Oracle GoldenGate Downloads web page:

Oracle GoldenGate for Oracle 21.3.0.0
Oracle GoldenGate for Massive Knowledge 21c

For set up, seek advice from Set up, Patch, and Improve and Putting in and Upgrading Oracle GoldenGate for Massive Knowledge.

Configure Oracle GoldenGate for preliminary load

The preliminary load configuration transfers present information from Oracle Database to Amazon Redshift. Full the next configuration steps:

Create an preliminary load extract parameter file for the supply Oracle database utilizing GoldenGate for Oracle. The next code is the pattern file content material:

# Extract preliminary load configuration (INITLE11)

EXTRACT INITLE11
SETENV ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
USERID ******************:1521/ORCL, PASSWORD ogg_password
RMTHOST ec2-xx-xx-xx-xx.compute-1.amazonaws.com, MGRPORT 9809, COMPRESS
RMTTASK REPLICAT, GROUP INITLR11
TABLE commerce_wh.dim_customer;
TABLE commerce_wh.dim_product;
TABLE commerce_wh.dim_date;
TABLE commerce_wh.fact_sales;

Add the EXTRACT on the GoldenGate for Oracle immediate by working the next command:

ADD EXTRACT INITLE11, SOURCEISTABLE

GGSCI (ip-**-**-**-**.us-west-2.compute.inner) 1> information INITLE11

Extract    INITLE11  Initialized  2025-07-08 03:44   Standing STOPPED
Checkpoint Lag       Not Out there
Log Learn Checkpoint  Not Out there
                     First File         File 0
Activity                 SOURCEISTABLE

Create a Replicat parameter file for the goal Redshift database for the preliminary load utilizing GoldenGate for Massive Knowledge. The next code is the pattern file content material:

# Replicate preliminary load configuration (INITLR11)

REPLICAT INITLR11
TARGETDB LIBFILE libggjava.so SET property=/house/ec2-user/ogg_bd/dirprm/rs.props
MAP commerce_wh.dim_customer, TARGET commerce_wh.dim_customer;
MAP commerce_wh.dim_product, TARGET commerce_wh.dim_product;
MAP commerce_wh.dim_date, TARGET commerce_wh.dim_date;
MAP commerce_wh.fact_sales, TARGET commerce_wh.fact_sales;
```

Add the REPLICAT on the GoldenGate for Massive Knowledge immediate by working the next command:

ADD REPLICAT INITLR11, SPECIALRUN

GGSCI (ip-**-**-**-**.us-west-2.compute.inner) 2> information INITLR11

Replicat   INITLR11  Initialized  2025-07-08 03:47   Standing STOPPED
Checkpoint Lag       00:00:00 (up to date 00:00:05 in the past)
Log Learn Checkpoint  Not Out there
Activity                 SPECIALRUN

Configure Oracle GoldenGate for CDC and Amazon Redshift handler

On this part, we stroll via the steps to configure Oracle GoldenGate for CDC and the Amazon Redshift handler.

Configure Oracle GoldenGate for extracting from supply

For steady replication, arrange the Extract, Pump, and Replicat processes:

Create an Extract parameter file for the supply Oracle database for CDC utilizing GoldenGate for Oracle. The next code is the pattern file content material:

# Extract configuration (EXTPRD)

EXTRACT EXTPRD
SETENV ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
USERID ********@oracledb:1521/ORCL, PASSWORD ogg_password
*************************************************/dirdat/ep
CHECKPOINTSECS 1
TABLE commerce_wh.dim_customer;
TABLE commerce_wh.dim_product;
TABLE commerce_wh.dim_date;
TABLE commerce_wh.fact_sales;
TRANLOGOPTIONS ALTARCHIVELOGDEST /u01/app/oracle/fast_recovery_area/ORCL/archivelog

Add the Extract course of and register it:

# Add Extract and Register (EXTPRD)

ADD EXTRACT EXTPRD, INTEGRATED TRANLOG, BEGIN NOW

REGISTER EXTRACT EXTPRD DATABASE

ADD EXTTRAIL /u01/app/oracle/product/21.3.0/oggcore_1/dirdat/ep, EXTRACT 
EXTPRD

GGSCI (ip-**-**-**-**.us-west-2.compute.inner) 3>  information EXTPRD

Extract    EXTPRD    Initialized  2025-07-08 03:50   Standing STOPPED
Checkpoint Lag       00:00:00 (up to date 00:00:36 in the past)
Log Learn Checkpoint  Oracle Built-in Redo Logs
                     2025-07-08 03:50:33

Create an Extract Pump parameter file for the supply Oracle database to ship the path information to the goal Redshift database. The next code is the pattern file content material:

# Pump course of configuration (PMPPRD)

EXTRACT PMPPRD
PASSTHRU
RMTHOST ec2-xx-xx-xx-xx.compute-1.amazonaws.com, MGRPORT 9809, COMPRESS
RMTTRAIL /house/********/ogg_bd/dirdat/pt
TABLE commerce_wh.dim_customer;
TABLE commerce_wh.dim_product;
TABLE commerce_wh.dim_date;
TABLE commerce_wh.fact_sales;

Add the Pump course of:

# Pump course of addition

ADD EXTRACT PMPPRD, EXTTRAILSOURCE /u01/app/oracle/product/21.3.0/oggcore_1/dirdat/ep

ADD RMTTRAIL /house/ec2-user/ogg_bd/dirdat/pt, EXTRACT PMPPRD

GGSCI (ip-**-**-**-**.us-west-2.compute.inner) 4> information PMPPRD

Extract    PMPPRD    Initialized  2025-07-08 03:51   Standing STOPPED
Checkpoint Lag       00:00:00 (up to date 00:00:09 in the past)
Log Learn Checkpoint  File /u01/app/oracle/product/21.3.0/oggcore_1/dirdat/ep000000000
                     First File  RBA 0

Configure Oracle GoldenGate Redshift handler to use adjustments to focus on

To configure an Oracle GoldenGate Replicat to ship information to a Redshift cluster, you need to arrange a Redshift properties file and a Replicat parameter file that defines how information is migrated to Amazon Redshift. Full the next steps:

Configure the Replicat properties file (rs.props), which consists of an S3 occasion handler and Redshift occasion handler. The next is an instance Replicat properties file configured to connect with Amazon Redshift:
```
gg.goal=redshift

# S3 Occasion Handler
gg.eventhandler.s3.area=us-west-2
gg.eventhandler.s3.bucketMappingTemplate=your-s3-bucket-name

# Redshift Occasion Handler
gg.eventhandler.redshift.connectionURL=jdbc:redshift://your-cluster.area.redshift.amazonaws.com:5439/dev
gg.eventhandler.redshift.userName=your_redshift_username
gg.eventhandler.redshift.Password=your_redshift_password
gg.classpath=/path/to/aws-sdk-java/*:/path/to/redshift-jdbc-driver.jar
jvm.bootoptions=-Xmx8g -Xms8g

gg.eventhandler.redshift.AwsIamRole=arn:aws:iam::your-account-id:function/your-redshift-role

gg.abend.on.lacking.columns=false
```
To authenticate Oracle GoldenGate’s entry to the Redshift cluster for information load operations, you could have two choices. The really useful and safer technique is to make use of IAM function authentication by configuring the gg.eventhandler.redshift.AwsIamRole property within the properties file. This strategy gives safer, role-based entry. Alternatively, you need to use entry key authentication by setting the surroundings variables AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY. For extra info, seek advice from the Oracle GoldenGate for BigData documentation.

Create a Replicat parameter file for the goal Redshift database utilizing Oracle GoldenGate for BigData. The next code is the pattern file content material:

# Replicat course of configuration (RSPRD)

REPLICAT RSPRD
TARGETDB LIBFILE libggjava.so SET property=/house/********/ogg_bd/dirprm/rs.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 1000
MAP commerce_wh.dim_customer, TARGET commerce_wh.dim_customer;
MAP commerce_wh.dim_product, TARGET commerce_wh.dim_product;
MAP commerce_wh.dim_date, TARGET commerce_wh.dim_date;
MAP commerce_wh.fact_sales, TARGET commerce_wh.fact_sales;

Add a Replicat course of:

# Add Replicat
ADD REPLICAT RSPRD, EXTTRAIL /house/ec2-user/ogg_bd/dirdat/pt, BEGIN NOW

GGSCI (ip-**-**-**-**.us-west-2.compute.inner) 3> information RSPRD

Replicat   RSPRD     Initialized  2025-07-08 03:52   Standing STOPPED
Checkpoint Lag       00:00:00 (up to date 00:00:07 in the past)
Log Learn Checkpoint  File /house/ec2-user/ogg_bd/dirdat/pt000000000
                     2025-07-08 03:52:48.471461

Begin preliminary load and alter sync

First begin the change sync extract and information pump on the supply Oracle database. This can begin capturing adjustments when you carry out the preliminary load.

Within the GoldenGate for Oracle GGSCI utility, begin EXTPRD and PMPPRD:

GGSCI (ip-**-**-**-**.us-west-2.compute.inner as ggsuser@ORCL) 13> begin EXTPRD

Sending START request to Supervisor ...
Extract group EXTPRD beginning.


GGSCI (ip-**-**-**-**.us-west-2.compute.inner as ggsuser@ORCL) 15> begin PMPPRD

Sending START request to Supervisor ...
Extract group PMPPRD beginning.

Don’t begin Replicat at this level.

File the Supply System Change Quantity (SCN) from the Oracle database, which serves as the start line for replication on the goal system:
```
choose current_scn from v$database;

CURRENT_SCN
13940177
```

Begin the preliminary load Extract course of, which is able to mechanically set off the corresponding preliminary load Replicat on the goal system:

GGSCI (ip-**-**-**-**.us-west-2.compute.inner as ggsuser@ORCL) 21> begin INITLE11

Sending START request to Supervisor ...
Extract group INITLE11 beginning.

Monitor the preliminary load completion standing by executing the next command on the GoldenGate for BigData GGSCI utility. Make certain the preliminary load course of has accomplished efficiently earlier than continuing to the following step. The report will point out the load standing and potential errors that want consideration.

Begin the change synchronization Replicat RSPRD utilizing the beforehand captured SCN to facilitate steady information replication:

GGSCI (ip-**-**-**-**.us-west-2.compute.inner) 17> begin RSPRD , aftercsn 13940177

Sending START request to Supervisor ...
Replicat group RSPRD beginning.

Discuss with the Oracle GoldenGate documentation for Amazon Redshift handlers to be taught extra about its detailed performance, unsupported operations, and system limitations.

When transitioning from preliminary load to steady replication in an Oracle database to Amazon Redshift migration utilizing Oracle GoldenGate, it’s essential to correctly handle information collisions to keep up information integrity. The secret’s to seize and use an applicable SCN that marks the precise level the place preliminary load ends and CDC begins. With out correct collision dealing with, you may encounter duplicate information or lacking information throughout the transition interval. Implementing applicable collision dealing with mechanisms makes positive duplicate information are correctly managed with out inflicting information inconsistencies within the goal system. For extra info on HANDLECOLLISIONS, seek advice from the Oracle GoldenGate documentation.

Clear up

When the migration is full, full the next steps:

Cease and take away Oracle GoldenGate processes (EXTRACT, PUMP, REPLICAT).
Delete EC2 situations used for Oracle GoldenGate.
Take away IAM roles created for migration.
Delete S3 buckets used for DMS Schema Conversion (if now not wanted).
Replace utility connection strings to level to the brand new Redshift cluster.

Conclusion

On this publish, we confirmed find out how to modernize your information warehouse by migrating to Amazon Redshift utilizing Oracle GoldenGate. This strategy facilitates minimal downtime and gives a versatile, dependable technique for transitioning your vital information workloads to the cloud. With the complexity concerned in database migrations, we extremely advocate testing the migration steps in non-production environments prior to creating adjustments in manufacturing. By following one of the best practices outlined on this publish, you’ll be able to obtain a clean migration course of and set the muse for a scalable, cost-effective information warehousing answer on AWS. Keep in mind to constantly monitor your new Amazon Redshift surroundings, optimize question efficiency, and benefit from the AWS suite of analytics instruments to derive most worth out of your modernized information warehouse.