Migrate from Amazon Redshift to Oracle Autonomous Data Warehouse in 7 easy steps.


In this blog, I plan to give you a quick overview of how you can use SQL Developer Amazon Redshift Migration Assistant to help you migrate your existing Amazon Redshift to Oracle Autonomous Data Warehouse (ADW)

But first, why the need to migrate to Autonomous Data Warehouse?  

Data-driven organizations differentiate themselves through analytics to further their competitive advantage by extracting value from all their data sources. Today’s digital world is already creating data at an explosive rate, that organizations' physical data warehouses that were once great for collecting data from across the enterprise for analysis are not able to keep pace with storage and compute resources needed to support them. In addition, the manual cumbersome task of patching, upgrading and securing the environments and data poses significant risks to businesses.

There are few cloud vendors that serve this niche market, one of them is Amazon Redshift, a fully managed data warehouse cloud service that is built on top of technology licensed from ParAccel. Though it is an early entrant, its query processing architecture severely limits concurrency levels, making it unsuitable for large data warehouses or web-scale data analytics. Redshift is only available for fixed blocks of hardware configurations, as such computers cannot be scaled independently of storage. This leads to excess capacity making customers pay for more than what is used.  Additionally, resizing puts it in a read-only state and may require downtime, which could take hours while data is redistributed.

Oracle Autonomous Data Warehouse is a fully managed database tuned and optimized for data warehouse workloads that support both structured and unstructured data. It automatically and continuously patches, tunes, backups, and upgrades with virtually no downtime. Integrated machine-learning algorithms drive automatic caching, adaptive indexing, advanced compression, and optimized cloud data-loading delivers unrivaled performance, allowing you to quickly extract data insights and make critical decisions in real time. With little human intervention, the product virtually eliminates human error, with dramatic implications for not only minimizing security breaches and outages but also on cost. Autonomous Data Warehouse is built on latest Oracle Database software and technology that runs your existing on-premises marts, data warehouses, and applications, making it compatible with all your existing data warehouse, data integration, and BI tools.

Strategize your Data Warehouse Migration

Here is a proposed workflow for either on-demand migration of Amazon Redshift or the generation of scripts for a scheduled manual migration that can be run at a later time

Establish connections to both Amazon Redshift (Source) and Oracle Autonomous Data Warehouse (Target) using SQL Developer Migration Assistant.

Download SQL Developer 18.3 or later versions. It is a client application that can be installed on a workstation, laptop for both Windows / Mac OSX. For the purposes of this blog, we will run it on Microsoft Windows. Download Amazon Redshift JDBC driver to access Amazon Redshift Environment.

Open SQL Developer application and add Redshift JDBC driver as third-party driver (Tools > Preferences > Database > Third Party JDBC Drivers)

Add Connection to Amazon Redshift Database, in the connections panel, create new connection, select the Amazon Redshift tab and enter the connection information for Amazon Redshift.


If you are planning to migrate multiple schemas it is recommended to connect with the master username to your Amazon Redshift instance.
If you deployed your Amazon Redshift environment within a Virtual Private Cloud (VPC) you have to ensure that your cluster is accessible from the Internet, here are the details on how to enable public Internet access.
If your Amazon Redshift client connection to the database appears to hang or times out when running long queries, here are the details with possible solutions to address this issue.


Add Connection to Oracle Autonomous Data Warehouse, in the connections panel, create new connection, select the Oracle tab and enter the connection information along with wallet details. If you haven’t provisioned Autonomous Data Warehouse yet, please do so now. Here are quick easy steps to get you started. You can even start with a free trial account.

Test connections for both Redshift and Autonomous Data Warehouse before you save them.

2. Capture / Map Schema: From the tools menu of SQL Developer, start the Cloud Migration Wizard to capture metadata schemas and tables from the source database (Amazon Redshift).

First, connect to AWS Redshift from the connection profile and identify the schemas that need to be migrated. All objects, mainly tables, in the schema will be migrated. You have the option to migrate data as well. Migration to Autonomous Data Warehouse is a per-schema basis and schemas cannot be renamed as part of the migration.

Note: When you migrate data, you have to provide the AWS access key, AWS Secret Access Key, and an existing S3 bucket URI where the Redshift data will be uploaded to and staged. The security credentials require privileges to store data in S3. If possible, create new, separate access keys for the migration. The same access keys will be used later to load the data into the Autonomous Data Warehouse using secure REST requests.

For example, if you provide URI as https://s3-us-west-2.amazonaws.com/my_bucket

Migration assistant will create these folders: oracle_schema_name/oracle_table_name inside the bucket: my_bucket

"https://s3-us-west 2.amazonaws.com/my_bucket/oracle_schema_name/oracle_table_name/*.gz"

Redshift Datatypes are mapped to Oracle Datatypes. Similarly, Redshift Object names are converted to Oracle names based on Oracle Naming Convention. The column defaults that use Redshift functions are replaced to their Oracle equivalents.

3. Generate Schema: Connect to Autonomous Data Warehouse from the connection profile. Ensure the user has administrative privileges, as this connection is used throughout the migration to create schemas and objects. Provide a password for the migration repository that will be created in the Autonomous Data Warehouse. You can choose to remove this repository post-migration. Specify a directory on the local system to store generated scripts necessary for the migration. To start migration right away, choose ‘Migrate Now’

Use ‘Advanced Settings’ to control the formatting options, parallel threads to enable when loading data, reject limit (number of rows to reject before erroring out)during the migration

Review the summary and click ‘finish’. If you have chosen an immediate migration, then the wizard stays open until the migration is finished. If not, the migration process generates the necessary scripts in the specified local directory and does not run the scripts.

If you choose to just generate migration scripts in the local directory, then continue with the next steps.

Stage Data: Connect to Amazon Redshift environment to run redshift_s3unload.sql to unload data from Redshift tables and store them to Amazon Storage S3 (staging) using the access credentials and the S3 bucket that was specified in the migration wizard workflow. 
Deploy Target Schema: Connect to Autonomous Data Warehouse as a privileged user (example: ADMIN) to run adwc_ddl.sql to deploy the generated schemas and DDLs converted from Amazon Redshift.
Copy Data: While being connected to Autonomous Data Warehouse, run adwc_dataload.sql that contains all the load commands necessary to load data straight from S3 into your Autonomous Data Warehouse.
Review Migration Results: Migration task creates 3 files in local directory; MigrationResults.log, readme.txt and redshift_migration_reportxxx.txt. Each of them will have information on the status of migration

Test few queries to make sure all your data from Amazon Redshift has migrated. Oracle Autonomous Data Warehouse supports connections from various client applications. Connect and test them.


With greater flexibility, lower infrastructure cost, and lower operations overhead, there’s a lot to love about Oracle Autonomous Data Warehouse. The unique value of Oracle comes from its complete cloud portfolio with intelligence infused at every layer, spanning infrastructure services, platform services, and applications. For Oracle, the autonomous enterprise goes beyond just automation, in which machines respond to an action with an automated reaction, instead, it is based on applied machine learning, making it completely autonomous, eliminating human error and delivering unprecedented performance, high security and reliability in the cloud.




Read more: blogs.oracle.com

You may also like...

Popular Posts