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

On this put up, we present how you can migrate an Oracle knowledge warehouse to Amazon Redshift utilizing Oracle GoldenGate and DMS Schema Conversion, a function of AWS Database Migration Service (AWS DMS). This method facilitates minimal enterprise disruption by steady replication. Amazon Redshift is a quick, absolutely managed, petabyte-scale knowledge warehouse service that makes it easy and cost-effective to effectively analyze your knowledge utilizing your current enterprise intelligence instruments.

Answer overview

Our migration method combines DMS Schema Conversion for schema migration and Oracle GoldenGate for knowledge replication. The migration course of consists of 4 most important steps:

1. Schema conversion utilizing DMS Schema Conversion.
2. Preliminary knowledge load utilizing Oracle GoldenGate.
3. Change knowledge seize (CDC) for ongoing replication.
4. Ultimate cutover to Amazon Redshift.

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

The answer includes the next key migration elements:

Within the following sections, we stroll by how you can migrate an Oracle knowledge warehouse to Amazon Redshift. For demonstration functions, we use an Oracle knowledge 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, consult with Oracle GoldenGate Licensing Data.

Run schema conversion utilizing DMS Schema Conversion

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

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:

1. Specify a digital personal cloud (VPC) and subnet the place DMS Schema Conversion will run.
2. 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
3. For on-premises databases, arrange both:
   1. AWS Web site-to-Web site VPN
   2. AWS Direct Join

For complete details about community configurations, consult with 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, consult with Retailer database credentials in AWS Secrets and techniques Supervisor.

Create S3 bucket

DMS Schema Conversion saves gadgets akin to evaluation stories, transformed SQL code, and details about database schema objects in an S3 bucket. For directions to create an S3 bucket, consult with Create an S3 bucket.

Create IAM insurance policies and roles

To arrange DMS Schema Conversion, you could create acceptable IAM insurance policies and roles. This course of makes certain AWS DMS has the mandatory permissions to entry your supply and goal databases, in addition to different AWS companies 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:

1. On the AWS DMS console, select Occasion profiles within the navigation pane.
2. Select Create occasion profile.
3. For Identify, enter a reputation (for instance, sc-instance).
4. For Community kind, we use IPv4. DMS Schema Conversion additionally presents Twin-stack mode for each IPv4 and IPv6.
5. For Digital personal cloud (VPC) for IPv4, select Default VPC.
6. For Subnet group, select your subnet group (for this put up, default).
7. For VPC safety teams, select your safety teams. As beforehand acknowledged, the occasion profile’s VPC safety group should have entry to each the supply and goal databases.
8. For S3 bucket, specify a bucket to retailer schema conversion metadata.
9. Select Create occasion profile.

Add knowledge suppliers

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

1. On the AWS DMS console, select Knowledge suppliers within the navigation pane.
2. Select Create knowledge supplier.
3. To create your goal, for Identify, enter a reputation (for instance, redshift-target).
4. For Engine kind, select Amazon Redshift.
5. For Engine configuration, choose Select from Redshift.
6. For Redshift cluster, select the goal Redshift cluster.
7. For Port, enter the port quantity.
8. For Database identify, enter the identify of your database.
9. Select Create knowledge supplier.
10. Repeat comparable steps to create your supply knowledge supplier.

Create migration mission

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

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

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

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

7. Select Create migration mission.

Use DMS Schema Conversion to remodel Oracle database objects

Full the next steps to convert supply database objects:

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

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

4. Generate and view the evaluation report.
5. 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 would possibly 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 simply created earlier within the prerequisite steps.

6. To save lots of the SQL scripts, choose the item within the goal database tree and select Save as SQL on the Actions menu.
7. After you finalize the scripts, run them manually within the goal database.
8. Alternatively, you may 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 modifications.

It will apply the robotically 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 modifications and apply the transformed modifications on to the goal database.

Carry out knowledge 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 knowledge from the Oracle supply tables and sends this knowledge to the Replicat course of, which hundreds it into Redshift goal tables by the suitable database connectivity. Concurrently, Oracle GoldenGate’s CDC mechanism tracks the continuing modifications (inserts, updates, and deletes) within the supply Oracle database by studying the redo logs. These captured modifications are then synchronized to Amazon Redshift in close to actual time by the Extract-Pump-Replicat course of, facilitating knowledge 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, establishing the flashback question, and managing server assets.

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 may disable this verify 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

You could run Oracle GoldenGate on EC2 situations. The situations should have enough CPU, reminiscence, and storage to deal with the anticipated replication quantity. For extra particulars, consult with Working System Necessities. After you establish the CPU and reminiscence necessities, choose a present technology 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 Huge Knowledge 21c

For set up, consult with Set up, Patch, and Improve and Putting in and Upgrading Oracle GoldenGate for Huge Knowledge.

Configure Oracle GoldenGate for preliminary load

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

1. 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;

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

   ADD EXTRACT INITLE11, SOURCEISTABLE
   
   GGSCI (ip-**-**-**-**.us-west-2.compute.inside) 1> data INITLE11
   
   Extract    INITLE11  Initialized  2025-07-08 03:44   Standing STOPPED
   Checkpoint Lag       Not Obtainable
   Log Learn Checkpoint  Not Obtainable
                        First File         File 0
   Activity                 SOURCEISTABLE

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

   # Replicate preliminary load configuration (INITLR11)
   
   REPLICAT INITLR11
   TARGETDB LIBFILE libggjava.so SET property=/dwelling/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;
   ```

4. Add the REPLICAT on the GoldenGate for Huge Knowledge immediate by working the next command:

   ADD REPLICAT INITLR11, SPECIALRUN
   
   GGSCI (ip-**-**-**-**.us-west-2.compute.inside) 2> data 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 Obtainable
   Activity                 SPECIALRUN

Configure Oracle GoldenGate for CDC and Amazon Redshift handler

On this part, we stroll by 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:

1. 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

2. 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.inside) 3>  data 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

3. Create an Extract Pump parameter file for the supply Oracle database to ship the path recordsdata 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 /dwelling/********/ogg_bd/dirdat/pt
   TABLE commerce_wh.dim_customer;
   TABLE commerce_wh.dim_product;
   TABLE commerce_wh.dim_date;
   TABLE commerce_wh.fact_sales;

4. 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 /dwelling/ec2-user/ogg_bd/dirdat/pt, EXTRACT PMPPRD
   
   GGSCI (ip-**-**-**-**.us-west-2.compute.inside) 4> data 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 modifications to focus on

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

1. 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:position/your-redshift-role
   
   gg.abend.on.lacking.columns=false

   To authenticate Oracle GoldenGate’s entry to the Redshift cluster for knowledge load operations, you will have two choices. The really helpful and safer technique is to make use of IAM position authentication by configuring the gg.eventhandler.redshift.AwsIamRole property within the properties file. This method gives safer, role-based entry. Alternatively, you should use entry key authentication by setting the atmosphere variables AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY. For extra info, consult with the Oracle GoldenGate for BigData documentation.

2. 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=/dwelling/********/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;

3. Add a Replicat course of:

   # Add Replicat
   ADD REPLICAT RSPRD, EXTTRAIL /dwelling/ec2-user/ogg_bd/dirdat/pt, BEGIN NOW
   
   GGSCI (ip-**-**-**-**.us-west-2.compute.inside) 3> data 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 /dwelling/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 knowledge pump on the supply Oracle database. It will begin capturing modifications whilst you carry out the preliminary load.

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

   GGSCI (ip-**-**-**-**.us-west-2.compute.inside as ggsuser@ORCL) 13> begin EXTPRD
   
   Sending START request to Supervisor ...
   Extract group EXTPRD beginning.
   
   
   GGSCI (ip-**-**-**-**.us-west-2.compute.inside as ggsuser@ORCL) 15> begin PMPPRD
   
   Sending START request to Supervisor ...
   Extract group PMPPRD beginning.

   Don’t begin Replicat at this level.

2. 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

3. Begin the preliminary load Extract course of, which can robotically set off the corresponding preliminary load Replicat on the goal system:

   GGSCI (ip-**-**-**-**.us-west-2.compute.inside as ggsuser@ORCL) 21> begin INITLE11
   
   Sending START request to Supervisor ...
   Extract group INITLE11 beginning.

4. Monitor the preliminary load completion standing by executing the next command on the GoldenGate for BigData GGSCI utility. Be sure 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.
5. Begin the change synchronization Replicat RSPRD utilizing the beforehand captured SCN to facilitate steady knowledge replication:

   GGSCI (ip-**-**-**-**.us-west-2.compute.inside) 17> begin RSPRD , aftercsn 13940177
   
   Sending START request to Supervisor ...
   Replicat group RSPRD beginning.

Seek advice from the Oracle GoldenGate documentation for Amazon Redshift handlers to study 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 knowledge collisions to keep up knowledge integrity. The secret’s to seize and use an acceptable SCN that marks the precise level the place preliminary load ends and CDC begins. With out correct collision dealing with, you would possibly encounter duplicate information or lacking knowledge in the course of the transition interval. Implementing acceptable collision dealing with mechanisms makes certain duplicate information are correctly managed with out inflicting knowledge inconsistencies within the goal system. For extra info on HANDLECOLLISIONS, consult with the Oracle GoldenGate documentation.

Clear up

When the migration is full, full the next steps:

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

Conclusion

On this put up, we confirmed how you can modernize your knowledge warehouse by migrating to Amazon Redshift utilizing Oracle GoldenGate. This method facilitates minimal downtime and gives a versatile, dependable technique for transitioning your crucial knowledge 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 modifications in manufacturing. By following the very best practices outlined on this put up, you may obtain a easy migration course of and set the muse for a scalable, cost-effective knowledge warehousing resolution on AWS. Keep in mind to constantly monitor your new Amazon Redshift atmosphere, optimize question efficiency, and make the most of the AWS suite of analytics instruments to derive most worth out of your modernized knowledge warehouse.

Concerning the authors