Download Amazon Redshift JDBC Driver and Add the Third Party Driver

1. Download an Amazon Redshift JDBC driver to access Amazon Redshift. Consult the Amazon Redshift documentation for the location of the most recent JDBC driver. For more information, see Configure a JDBC Connection.

   Note
   
   Use the Amazon Redshift JDBC Driver JDBC 4.2–compatible driver.

2. Store the Amazon Redshift JDBC driver in a local directory where SQL Developer can access the Amazon Redshift JDBC driver.

3. Add the Amazon Redshift JDBC driver as third party to SQL Developer before making a connection. Within SQL Developer, go to Tools > Preferences > Database > Third Party JDBC Drivers (for Mac, this is Oracle SQL Developer > Preferences Database > Third Party JDBC Drivers).

4. Click Add Entry and select the path to the Amazon Redshift JDBC Driver that you download.

   
   
   
   

5. Click OK to add the Amazon Redshift JDBC driver that you download.

Add Connection to Amazon Redshift Database

Connect to the Amazon Redshift database.

1. In the Connections panel, right-click Connections and select New Connection....

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

   
   
   
   

• For more details for configuring a JDBC Connection and obtaining the Amazon Redshift JDBC URL, see AWS: Configure a JDBC Connection.

• For more details for configuring security options for the connection ( in case of "Amazon [500150] connection error"), see AWS: Configure Security options for Connection ( in case of "Amazon [500150] connection error").

• 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. See http://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-authorize-cluster-access.html for details of 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, see http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html for possible solutions to address this issue.

Test the connection before you save it.

Additional Information for Amazon Authentication and Access Control

• AWS: Security

• AWS: Managing Cluster Security Groups Using the Console

The Cloud Migration wizard starts when you click Cloud Migrations from Migration in the Tools menu. The Cloud Migrations wizard enables you to migrate schemas, objects (tables), and data from an Amazon Redshift database to Autonomous Database.

The Cloud Migration Wizard is an easy set of steps. The Cloud Migration Wizard guides you to:

• Identify the schemas in your Amazon Redshift database that you want to migrate.

• Identify the target Autonomous Database.

• Define whether you want to migrate the metadata (DDL), the data, or both.

• Choose to migrate your system online, to generate scripts, or both.

Identify the Amazon Redshift Database

Identify the schemas in the Amazon Redshift database to migrate. All objects, mainly tables, in the schema will be migrated. Migration to Autonomous Database is on a per-schema basis. Schemas cannot be renamed as part of the migration.

1. In the AWS Redshift Migration, specify the Connection.

   
   
   
   

• Connection: Name of the Redshift database connection.

• Available Schemas: Schemas available for the specific connection.

• Selected Schemas: Click the Add icon to select the schemas you want to migrate from Available Schemas.

• Include Data: DDL and DATA migrates the selected schemas and data.

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 unloaded and staged. The security credentials require certain privileges to store data in S3. It is recommended to create new, separate access keys for the migration. The same access key is used later on to load the data into the Autonomous Database using secure REST requests.

• AWS Access Key: For more information on access keys, see AWS Identity and Access Management.

• AWS Secret Access: For more information on access keys, see AWS Identity and Access Management.

• S3 Bucket URI: For information on common S3 ServiceException errors, see S3ServiceException Errors.

  For more information an S3 buckets, see Creating and Configuring an S3 Bucket.

Amazon S3 Bucket URI Format

For the source files that reside in Amazon S3, see the following for a description of the URI format for accessing your files: Accessing a Bucket For example the following refers to the file folder 'folder_name' in the adwc bucket in the us-west-2 region.

https://s3-us-west-2.amazonaws.com/adwc/folder_name

S3 Bucket Configuration Example 1

If you provide the following S3 Bucket URI :

https://s3-us-west-2.amazonaws.com/my_bucket

The wizard verifies the entire path including my_bucket. An attempt is made to write a test file, if it is not accessible there is a prompt. In case, my_bucket does not exist, there is an error reported:

Validation Failed

Then the code generation creates the following path for the DBMS_CLOUD.COPY_DATA function:

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

The migration assistant creates these folders: oracle_schema_name/oracle_table_name inside the bucket: my_bucket.

S3 Bucket Example 2

If you provide the following S3 Bucket URI :

https://s3-us-west-2.amazonaws.com/my_bucket/another_folder

The wizard verifies the entire path including my_bucket. An attempt is made to write a test file, if it is not accessible there is a prompt. In case, my_bucket does not exist, there is an error reported:

Validation Failed

In this case the another_folder does not have to exist. The migration creates the another_folder bucket inside my_bucket.

Then the code generation creates the following path for the DBMS_CLOUD.COPY_DATA function:

file_uri_list => ‘https://s3-us-west-2.amazonaws.com/my_bucket/another_folder/oracle_schema_name/oracle_table_name/*.gz

Step 2 of 3: Autonomous Data Warehouse Cloud

First create a connection for your target Autonomous Database See Connect to Autonomous Database. The user for this connection must have the administrative privileges; the connection is used throughout the migration to create schemas and objects.

The Amazon Redshift Migration Assistant allows you to do an online migration right away, to generate all scripts necessary for a migration, or both. If you chose to store the scripts in a local directory you have to specify the local directory (the directory must be writable by the user).

• Connection: Name of the Autonomous Data Warehouse Cloud connection. Create a connection for the Autonomous Database if required. The user must have administrative privileges since this connection is used throughout the migration to create schemas and objects. 

• Migration Repository Password: Password for the migration repository that is installed in the Autonomous Database as part of the schema migration. Either use the pre-filled password or enter a new password.

• Remove repository on successful migration: Select this option to remove the repository after the migration is completed. The repository is not required after migration.

• Migrate Now: Select this option to perform an online migration immediately.

  Note
  
  

  • If Include Data from Step 1 and Migrate Now are both unselected, you are opting for just generation of all required SQL Scripts for manual migration.

  • If Include Data from Step 1 is unchecked and Migrate Now is selected, then all selected schemas and their tables will be deployed in Autonomous Database but data will not be loaded into tables.

  • If Include Data from Step 1 and Migrate Now are both selected, then all selected schemas and their tables will be deployed in Autonomous Database and data will be loaded into tables.

• Directory: Specify the director to store the generated scripts necessary for the migration; this saves the scripts in a local directory.

Advanced Settings (Optional)

The default settings should work unless you want to control the format options when Unloading to S3 storage or Copying from S3 storage to Autonomous Database. For more information on Format Options, see DBMS_CLOUD Package Format Options. To use advanced options, click Advanced Settings.

Output Directory: Enter the path or click Select Directory to select the directory or folder for the migration.

Maximum Number of Threads: Enter the number of parallel threads to enable when loading data to tables in Autonomous Database.

Use Scheduler: Select this option to enable the scheduler for migration. You can schedule jobs for data load migration operations from the AWS S3 bucket to Autonomous Database. You have the option to run the scheduled jobs immediately or at a future date and time. To monitor the data load scheduled jobs, use the Scheduler node in the Connections navigator.

Migration Execution Choice:

• Immediate runs the scheduler as soon as the Redshift migration is triggered.

• Once runs the scheduler on a future date. You specify the Start Date and Time Zone. By default, the Start Date displays the current date and time of the local system. To change the start date, use the calendar icon to double-click and select the date or use the spinner to highlight the date and then click the field to set it.

Redshift Unload Options: Allow Overwrite: If this option is enabled, the unload process will overwrite existing files, including the manifest file (lists the data files that are created by the unload process). By default, unload fails if there are files that can be overwritten.

ADWC format options: Reject Limit: Enter the number of rows to reject when loading data to tables in Autonomous Database. The migration operation will error out after the specified number of rows are rejected. The default is 0.

If you have chosen an immediate migration, then the dialog of the migration wizard stays open until the migration is finished. If you select generate scripts, the migration process generates the necessary scripts in the specified local directory, and does not run the scripts.

To perform the migration, click Finish

If the selected schema name in AWS Redshift already exists in Autonomous Database, the migration process excludes deploying these selected schemas and displays a dialog:

Summary: What The Migration Assistant Creates

• Creates a new Autonomous Database user using the schema_name from Redshift.

• Creates a new bucket on S3 based on the schema name.

• Creates sub-folders on S3 for each table.

Unload Your Amazon Redshift Data into S3

The first step of a successful migration is to unload your Amazon Redshift data into Amazon S3, which acts as a staging area. Script redshift_s3unload.sql has all the Amazon Redshift unload commands to unload the data using the access credentials and the S3 bucket that were specified in the Migration Wizard workflow.

Connect to your Amazon Redshift environment to run this script.

Create Your Data Warehouse Objects

To prepare your Autonomous Database create your empty data warehouse schema prior to loading data. The Amazon Redshift Migration Assistant converted all Amazon Redshift schema structures into Oracle structures in script adwc_ddl.sql.

The script must be executed while you are connected to your Autonomous Database as privileged user; for example, ADMIN.

By default, the schema created for the migration has the same name as the schema in Amazon Redshift. You must change the password to the valid password for the specified user either in the script or after the script runs. If you want to change the schema name then change the schema name and all references to the name.

Load Your Amazon Redshift Data into Your Oracle Autonomous Database

The script adwc_dataload.sql contains all the load commands necessary to load your unloaded Amazon Redshift data straight from S3 into your Autonomous Database.

Execute the script while connected to your Autonomous Database as a privileged user; for example ADMIN.

If you want to change the target schema name when you create your data warehouse objects then you must adjust the target schema names in this script accordingly.

Use of JOB SCHEDULER

SQL Developer provides a graphical interface for using the DBMS_SCHEDULER PL/SQL package to work with Oracle Scheduler objects. To use the SQL Developer scheduling features, please refer 'Scheduling Jobs Using SQL Developer' topic of SQL Developer User Guide and Oracle Database Administrator's Guide to understand the concepts and essential tasks for job scheduling.

The Scheduler node for a connection appears in the Connections navigator and in the DBA navigator. Use ADWC 'admin' user to navigate which displays Scheduler objects owned by the 'admin' monitoring status of data load jobs.

Under ADWC 'admin' Connection → Scheduler → Jobs, you will see AWS Redshift to ADWC data load jobs are created with name <schema_name>_<table_name>. 

To see the status of completion of each data load, please expand each scheduled job and check the status.

Also for more detailed information about data load operation see table MD_REPORT in SQLDEV_MIGREPOS schema that stores information about table columns: and

 OPERATION_ID, LOGFILE_TABLE, BADFILE_TABLE, SOURCE_SCHEMA_NAME, TARGET_SCHEMA_NAME, SOURCE_TABLE_NAME, 

and

 TARGET_TABLE_NAME, SOURCE_TABLE_ROWS, TARGET_TABLE_ROWS_LOADED, ERROR MESSAGE, 

and

STATUS (COMPLETED or FAILED)

Redshift Migration Log and Report Files

After Redshift Migration, you will find three files:

• MigrationResults.log : Log file of Redshift migration

• readme.txt : file explains how to use the Generated Amazon Redshift Migration Scripts.

• redshift_migration_reportxxx.txt : Contains information about Migration, here is sample:

  ______________________________________________________________
  OPERATION ID : 8566
  LOGFILE TABLE : COPY$8566_LOG
  BADFILE TABLE : COPY$8566_BAD
  SOURCE SCHEMA : sample
  TARGET SCHEMA : SAMPLE
  SOURCE TABLE : listing
  TARGET TABLE : LISTING
  SOURCE TABLE ROWS : 192497
  TABLE ROWS LOADED : 192497
  ERROR MESSAGE : null
  STATUS : COMPLETED
  START TIME : 2018-09-27 17:25:18.662075
  END TIME : 2018-09-27 17:25:25.012695
  ______________________________________________________________