Using Oracle as a source for AWS SCT - AWS Schema Conversion Tool

Using Oracle as a source for AWS SCT

You can use AWS SCT to convert data from Oracle to the following targets:

  • Amazon RDS for MySQL

  • Amazon Aurora (MySQL)

  • Amazon RDS for PostgreSQL

  • Amazon Aurora (PostgreSQL)

  • Amazon RDS for Oracle

When the source is an Oracle database, comments can be converted to the appropriate format in, for example, a PostgreSQL database. AWS SCT can convert comments on tables, views, and columns. Comments can include apostrophes; AWS SCT doubles the apostrophes when converting SQL statements, just as it does for string literals.

For more information, see the following sections:

Permissions required when using Oracle as a source database

The privileges required for Oracle as a source are listed following:

  • CONNECT

  • SELECT_CATALOG_ROLE

  • SELECT ANY DICTIONARY

  • SELECT on SYS.USER$ TO <sct_user>

Connecting to Oracle as a source database

Use the following procedure to connect to your Oracle source database with the AWS Schema Conversion Tool.

To connect to an Oracle source database

  1. In the AWS Schema Conversion Tool, choose Connect to Oracle.

    
                            Connect to source database

    The Connect to Oracle dialog box appears.

    
                            Oracle connection information
  2. Provide the Oracle source database connection information. Use the instructions in the following table.

    For this parameter Do this
    Type

    Choose the connection type to your database. Depending on your type, provide the following additional information:

    • SID

      • Server name: The DNS name or IP address of your source database server.

      • Server port: The port used to connect to your source database server.

      • Oracle SID: The Oracle System ID (SID). To find the Oracle SID, submit the following query to your Oracle database:

        SELECT sys_context('userenv','instance_name') AS SID FROM dual;

    • Service Name

      • Server name: The DNS name or IP address of your source database server.

      • Server port: The port used to connect to your source database server.

      • Service Name: The name of the Oracle service to connect to.

    • TNS Alias

      • TNS file path: The path to the file that contains the Transparent Network Substrate (TNS) name connection information.

      • TNS file path: The TNS alias from this file to use to connect to the source database.

    • TNS Connect Identifier

      • TNS identifier: The identifier for the registered TNS connection information.

    User name and Password

    Enter the user name and password to connect to your source database server.

    The first time you connect to the Oracle database, you enter the path to the Oracle Driver file (ojdbc8.jar). You can download the file at http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html. You must register on the free Oracle Technical Network website to complete the download. AWS SCT uses the selected driver for any future Oracle database connections. The driver path can be modified using the Drivers tab in Global Settings.

    Note

    AWS SCT uses the password to connect to your source database only when you create your project or choose the Connect to source option in a project, where source is your source database. To guard against exposing the password for your source database, AWS SCT doesn't store the password. If you close your AWS SCT project and reopen it, you are prompted for the password to connect to your source database as needed.

    Use SSL

    Select this option if you want to use SSL to connect to your database. Provide the following additional information, as appropriate, on the SSL tab:

    • SSL Authentication: Select this option to use SSL authentication by certificate is used for the connection instead of user name and password.

    • Trust Store: The location of a trust store containing certificates.

    • Trust Store Password: The password for the trust store.

    • Key Store: The location of a key store containing a private key and certificates. This value is required if SSL Authentication is selected and is otherwise optional.

    • Trust Store Password: The password for the key store. This value is required if SSL Authentication is selected and is otherwise optional.

    Store Password

    AWS SCT creates a secure vault to store SSL certificates and database passwords. Enabling this option lets you store the database password and to connect quickly to the database without having to enter the password.

    Oracle Driver Path

    Enter the path to the driver to use to connect to the source database. For more information, see Installing the required database drivers.

    If you store the driver path in the global project settings, the driver path doesn't appear on the connection dialog box. For more information, see Storing driver paths in the global settings.

  3. Choose Test Connection to verify that you can successfully connect to your source database.

  4. Choose OK to connect to your source database.