AWS Schema conversion tool - Connection error

Question

I am trying to migrate my Oracle database to an AWS Redshift cluster, and I am following the steps as documented in the AWS website.

When I try to create a project in AWS Schema Conversion Tool, configured for Oracle (running in my laptop), I am unable to get a successful connection.

Here's the error I get:

Connection to 'jdbc:oracle:thin:@localhost:1521:orcl' wasn't established. ERROR: code: 28009; message: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Please note that in Schema Conversion Tool project page, I don't see a section "Connect as SYSDBA" option? I tried with some other sample users such as SCOTT, and I get Insufficient privileges.

With the same configurations am able to connect from an SQL client, SQLWorkbench.

Answer 1

To connect to Schema conversion tool you need a user having permissions as connect ,select_catalog_role and select any dictionary. Make sure these permissions are set correctly. Either create a new user with these permissions or modify rights of exiting user. SYS\SYSDBA will not be helpful.

As you are in localhost, with given permissions, you should be able to connect.

Answer 2

You can also connect using sys, in user name field provide the user name like this

sys as sysdba

As per the AWS documentation if Oracle is a source DB then user should be created like this.

CREATE USER oracle_sct_user IDENTIFIED BY password;

GRANT CONNECT TO oracle_sct_user;
GRANT SELECT_CATALOG_ROLE TO oracle_sct_user;
GRANT SELECT ANY DICTIONARY TO oracle_sct_user;

and AWS DMS requires oracle user to have some privileges, detail can be found here.

https://docs.aws.amazon.com/dms/latest/sbs/CHAP_Oracle2PostgreSQL.Steps.ConfigureOracle.html