Menu
AWS Database Migration Service
Step-by-Step Migration Guide (API Version 2016-01-01)

Step 3: Test Connectivity to the Oracle DB Instance and Create the Sample Schema

After the CloudFormation stack has been created, test the connection to the Oracle DB instance by using SQL Workbench/J and then create the HR sample schema.

To test the connection to your Oracle DB instance using SQL Workbench/J and create the sample schema

  1. In SQL Workbench/J, choose File, then choose Connect window. Create a new connection profile using the following information as shown following

    For This Parameter Do This

    New profile name

    Type RDSOracleConnection.

    Driver

    Choose Oracle (oracle.jdbc.OracleDriver).

    URL

    Use the OracleJDBCConnectionString value you recorded when you examined the output details of the DMSdemo stack in a previous step.

    Username

    Type oraadmin.

    Password

    Provide the password for the admin user that you assigned when creating the Oracle DB instance using the AWS CloudFormation template.

  2. Test the connection by choosing Test. Choose OK to close the dialog box, then choose OK to create the connection profile.

    
                                Connecting to the Oracle DB instance for AWS Database Migration Service

    Note

    If your connection is unsuccessful, ensure that the IP address you assigned when creating the CloudFormation template is the one you are attempting to connect from. This is the most common issue when trying to connect to an instance.

  3. Create the HR schema you will use for migration using a custom script. The SQL script provided by AWS is located at this site.

    1. Open the provided SQL script in a text editor. Copy the entire script.

    2. In SQL Workbench/J, paste the SQL script in the Default.wksp window showing Statement 1.

    3. Choose SQL, then choose Execute All.

      When you run the script, you will get an error message indicating that user HR does not exists. You can ignore this error and run the script. The script drops the user before creating it,which generates the error.

    
                                 AWS Database Migration Service SQL script to install the demo schema
  4. Verify the object types and count in HR Schema were created successfully by running the following SQL query. You can also compare the results from the following queries with the results listed in the spreadsheet provided by AWS at this site.

    Copy
    Select OBJECT_TYPE, COUNT(*) from dba_OBJECTS where owner='HR' GROUP BY OBJECT_TYPE;

    The results of this query should be similar to the following:

    Copy
    OBJECT_TYPE COUNT(*) INDEX 7 PROCEDURE 2 SEQUENCE 3 TABLE 7 VIEW 1
    
                                 AWS Database Migration Service SQL script to install the demo schema
  5. Verify the number of constraints in HR schema by running the following SQL query:

    Copy
    Select CONSTRAINT_TYPE,COUNT(*) from dba_constraints where owner='HR' AND (CONSTRAINT_TYPE IN ('P','R')OR SEARCH_CONDITION_VC NOT LIKE '%NOT NULL%') GROUP BY CONSTRAINT_TYPE;

    The results of this query should be similar to the following:

    Copy
    CONSTRAINT_TYPE COUNT(*) R 10 P 7 C 2
  6. Verify the total number of tables and number of rows for each table by running the following SQL query:

    Copy
    Select table_name, num_rows from dba_tables where owner='HR' order by 1;

    The results of this query should be similar to the following:

    Copy
    TABLE_NAME NUM_ROWS COUNTRIES 25 DEPARTMENTS 27 EMPLOYEES 107 JOBS 19 JOB_HISTORY 10 LOCATIONS 23 REGIONS 4
  7. Verify the relationship in tables. Check the departments with employees greater than 10 by running the following SQL query:

    Copy
    Select b.department_name,count(*) from HR.Employees a,HR.departments b where a.department_id=b.department_id group by b.department_name having count(*) > 10 order by 1;

    The results of this query should be similar to the following:

    Copy
    DEPARTMENT_NAME COUNT(*) Sales 34 Shipping 45