Connecting to Oracle Databases with the AWS Schema Conversion Tool - AWS Schema Conversion Tool
Privileges for OracleConnecting to Oracle as a source

Connecting to Oracle Databases with the AWS Schema Conversion Tool

You can use AWS SCT to convert schemas, database code objects, and application code from Oracle Database to the following targets:

  • Amazon RDS for MySQL

  • Amazon Aurora MySQL-Compatible Edition

  • Amazon RDS for PostgreSQL

  • Amazon Aurora PostgreSQL-Compatible Edition

  • Amazon RDS for Oracle

  • Amazon RDS for MariaDB

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.

Topics

Privileges for Oracle as a source

The privileges required for Oracle as a source are as follows:

  • CONNECT

  • SELECT_CATALOG_ROLE

  • SELECT ANY DICTIONARY

  • SELECT ON SYS.ARGUMENT$

Connecting to Oracle as a source

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 Add source.

  2. Choose Oracle, then choose Next.

    The Add source dialog box appears.

  3. For Connection name, enter a name for your database. AWS SCT displays this name in the tree in the left panel.

  4. Use database credentials from AWS Secrets Manager or enter them manually:

    • To use database credentials from Secrets Manager, use the following instructions:

      1. For AWS Secret, choose the name of the secret.

      2. Choose Populate to automatically fill in all values in the database connection dialog box from Secrets Manager.

      For information about using database credentials from Secrets Manager, see Configuring AWS Secrets Manager in the AWS Schema Conversion Tool.

    • To enter the Oracle source database connection information manually, use the following instructions:

      Parameter Action
      Type

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

      • SID

        • Server name: The Domain Name System (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.

          You can connect to your source Oracle database using an IPv6 address protocol. To do so, make sure that you use square brackets to enter the IP address, as shown in the following example.

          [2001:db8:ffff:ffff:ffff:ffff:ffff:fffe]

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

          After you choose the TNS file, AWS SCT adds all Oracle database connections from the file to the TNS alias list.

          Choose this option to connect to Oracle Real Application Clusters (RAC).

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

      • TNS connect identifier

        • TNS connect identifier: The identifier for the registered TNS connection information.
      User name and Password

      Enter the database credentials 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. Make sure to 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.

      AWS SCT uses the password to connect to your source database only when you choose to connect to your database in a project. To guard against exposing the password for your source database, AWS SCT doesn't store the password by default. 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

      Choose this option to use Secure Sockets Layer (SSL) to connect to your database. Provide the following additional information, as applicable, on the SSL tab:

      • SSL authentication: Select this option to use SSL authentication by certificate Set up your trust store and key store in Settings, Global settings, Security.

      • Trust store: The trust store to use.

      • Key store: The key store to use.
      Store password

      AWS SCT creates a secure vault to store SSL certificates and database passwords. Choose this option to 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 JDBC drivers for AWS Schema Conversion Tool.

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

  5. Choose Test Connection to verify that AWS SCT can connect to your source database.

  6. Choose Connect to connect to your source database.