AWS Schema Conversion Tool and Database Migration Service

1. Prepare Environment

1. Prepare IDE, source and target database

For Cloud IDE refer to AWS Cloud9
For source database Refer to Launch Amazon EC2 using Custom AMI
For target database Refer to Create Amazon RDS PostgreSQL instance

2. Create Windows firewall inbound rule for Oracle TNS port(1521) using PowerShell script

Open the PowerShell console and run script

New-NetFirewallRule -DisplayName “Oracle TNS (TCP-in 1521)” -Profile @(‘Domain’, ‘Private’, 'Public') -Direction Inbound -Protocol TCP –LocalPort 1521 -Action Allow

PS C:\Users\Administrator> New-NetFirewallRule -DisplayName “Oracle TNS (TCP-in 1521)” -Profile @(‘Domain’, ‘Private’, 'Public') -Direction Inbound -Protocol TCP –LocalPort 1521 -Action Allow
>>
Name                  : {8897f95a-d712-4847-a70c-45177fe444a1}
DisplayName           : Oracle TNS (TCP 1521)
Description           :
DisplayGroup          :
Group                 :
Enabled               : True
Profile               : Domain, Private, Public
Platform              : {}
Direction             : Inbound
Action                : Allow
EdgeTraversalPolicy   : Block
LooseSourceMapping    : False
LocalOnlyMapping      : False
Owner                 :
PrimaryStatus         : OK
Status                : The rule was parsed successfully from the store. (65536)
EnforcementStatus     : NotApplicable
PolicyStoreSource     : PersistentStore
PolicyStoreSourceType : Local
PS C:\Users\Administrator>

3. Grant Oracle database user privileges

Grant privileges for SCT and DMS on SQL Client using sysdba(System Database Administrator) role

-- PRIVILEGES FOR SCT
GRANT SELECT ANY DICTIONARY TO HR;
-- PRIVILEGES FOR DMS
GRANT CREATE SESSION TO HR;
GRANT SELECT ANY TRANSACTION TO HR;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO HR;
GRANT SELECT ON SYS.V_$LOG TO HR;
GRANT SELECT ON SYS.V_$LOGFILE TO HR;
GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO HR;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO HR;
GRANT SELECT ON SYS.V_$DATABASE TO HR;
GRANT SELECT ON SYS.V_$THREAD TO HR;
GRANT SELECT ON SYS.V_$PARAMETER TO HR;
GRANT SELECT ON SYS.V_$NLS_PARAMETERS TO HR;
GRANT SELECT ON SYS.V_$TIMEZONE_NAMES TO HR;
GRANT SELECT ON SYS.V_$TRANSACTION TO HR;
GRANT SELECT ON SYS.V_$CONTAINERS TO HR;
GRANT SELECT ON ALL_INDEXES TO HR;
GRANT SELECT ON ALL_OBJECTS TO HR;
GRANT SELECT ON ALL_TABLES TO HR;
GRANT SELECT ON ALL_USERS TO HR;
GRANT SELECT ON ALL_CATALOG TO HR;
GRANT SELECT ON ALL_CONSTRAINTS TO HR;
GRANT SELECT ON ALL_CONS_COLUMNS TO HR;
GRANT SELECT ON ALL_TAB_COLS TO HR;
GRANT SELECT ON ALL_IND_COLUMNS TO HR;
GRANT SELECT ON ALL_ENCRYPTED_COLUMNS TO HR;
GRANT SELECT ON ALL_LOG_GROUPS TO HR;
GRANT SELECT ON ALL_TAB_PARTITIONS TO HR;
GRANT SELECT ON SYS.DBA_REGISTRY TO HR;
GRANT SELECT ON SYS.OBJ$ TO HR;
GRANT SELECT ON DBA_TABLESPACES TO HR;

  ⋮
Grant succeeded.
  ⋮

4. Install xdg-utils for SCT

Update apt database

sudo apt update

mspuser:~/environment $ sudo apt update
Hit:1 https://download.docker.com/linux/ubuntu bionic InRelease
  ⋮
3 packages can be upgraded. Run 'apt list --upgradable' to see them.
mspuser:~/environment $

Install xdg-utils using apt

sudo apt -y install xdg-utils

mspuser:~/environment $ sudo apt -y install xdg-utils
Reading package lists... Done
  ⋮
0 upgraded, 1 newly installed, 0 to remove and 3 not upgraded.
mspuser:~/environment $

5. Install AWS Schema Conversion Tool

Refer to Installing AWS SCT

Download the compressed AWS SCT installer for Ubuntu

wget https://s3.amazonaws.com/publicsctdownload/Ubuntu/aws-schema-conversion-tool-1.0.latest.zip

mspuser:~/environment $ wget https://s3.amazonaws.com/publicsctdownload/Ubuntu/aws-schema-conversion-tool-1.0.latest.zip
--2022-09-07 01:48:57--  https://s3.amazonaws.com/publicsctdownload/Ubuntu/aws-schema-conversion-tool-1.0.latest.zip
Resolving s3.amazonaws.com (s3.amazonaws.com)... 52.217.195.128
Connecting to s3.amazonaws.com (s3.amazonaws.com)|52.217.195.128|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1023114636 (976M) [application/zip]
Saving to: ‘aws-schema-conversion-tool-1.0.latest.zip’

aws-schema-conversion-tool-1.0.late 100%[================================================================>] 975.72M  8.73MB/s    in 2m 28s  

2022-09-07 01:51:26 (6.58 MB/s) - ‘aws-schema-conversion-tool-1.0.latest.zip’ saved [1023114636/1023114636]

mspuser:~/environment $

Unzip downloaded file

unzip aws-schema-conversion-tool-1.0.latest.zip

mspuser:~/environment $ unzip aws-schema-conversion-tool-1.0.latest.zip 
Archive:  aws-schema-conversion-tool-1.0.latest.zip
inflating: aws-schema-conversion-tool-1.0.665.deb  
inflating: agents/aws-cassandra-extractor-1.0.665-1.x86_64.rpm  
inflating: agents/aws-cassandra-extractor-1.0.665.deb  
inflating: agents/aws-schema-conversion-tool-extractor-2.0.1.665-1.x86_64.rpm  
inflating: agents/aws-schema-conversion-tool-extractor-2.0.1.665.deb  
inflating: agents/aws-schema-conversion-tool-extractor-2.0.1.665.msi  
inflating: dmsagent/aws-schema-conversion-tool-dms-agent-3.4.5-R2.x86_64.rpm  
mspuser:~/environment $ ls -la
total 1489664
drwxr-xr-x  5 ubuntu ubuntu       4096 Sep  7 02:02 .
drwxr-xr-x 13 ubuntu ubuntu       4096 Sep  7 01:48 ..
drwxrwxr-x  5 ubuntu ubuntu       4096 Aug 29 09:00 .c9
drwxrwxr-x  2 ubuntu ubuntu       4096 Sep  7 02:02 agents
-rw-r--r--  1 ubuntu ubuntu  502254846 Aug 19 16:23 aws-schema-conversion-tool-1.0.665.deb
-rw-rw-r--  1 ubuntu ubuntu 1023114636 Aug 29 15:50 aws-schema-conversion-tool-1.0.latest.zip
drwxrwxr-x  2 ubuntu ubuntu       4096 Sep  7 02:02 dmsagent
mspuser:~/environment $

Run AWS SCT installer file extracted

Change <build-number> according to your current build number

sudo dpkg -i aws-schema-conversion-tool-1.0.<build-number>.deb

mspuser:~/environment $ sudo dpkg -i aws-schema-conversion-tool-1.0.665.deb
(Reading database ... 106543 files and directories currently installed.)
  ⋮
Adding shortcut to the menu
mspuser:~/environment $

5. Download and Install JDBC drivers for AWS SCT

Refer to Downloading the required database drivers

Downloading the required database drivers

wget https://download.oracle.com/otn-pub/otn_software/jdbc/217/ojdbc8.jar
wget https://jdbc.postgresql.org/download/postgresql-42.2.19.jar
wget https://downloads.mariadb.com/Connectors/java/connector-java-2.4.1/mariadb-java-client-2.4.1.jar

mspuser:~/environment $ wget https://download.oracle.com/otn-pub/otn_software/jdbc/217/ojdbc8.jar
--2022-09-07 08:38:18--  https://download.oracle.com/otn-pub/otn_software/jdbc/217/ojdbc8.jar
  ⋮
2022-09-07 08:38:19 (85.0 MB/s) - ‘ojdbc8.jar’ saved [5089412/5089412]
mspuser:~/environment $ wget https://jdbc.postgresql.org/download/postgresql-42.2.19.jar
--2022-09-07 08:40:18--  https://jdbc.postgresql.org/download/postgresql-42.2.19.jar
  ⋮
2022-09-07 08:40:20 (1.09 MB/s) - ‘postgresql-42.2.19.jar’ saved [1005078/1005078]
mspuser:~/environment $ wget https://downloads.mariadb.com/Connectors/java/connector-java-2.4.1/mariadb-java-client-2.4.1.jar
--2022-09-07 08:40:49--  https://downloads.mariadb.com/Connectors/java/connector-java-2.4.1/mariadb-java-client-2.4.1.jar
  ⋮
2022-09-07 08:40:50 (698 KB/s) - ‘mariadb-java-client-2.4.1.jar’ saved
mspuser:~/environment $

Installing JDBC drivers

Create a directory to store the JDBC drivers in
```
sudo mkdir –p /usr/local/jdbc-drivers
```
```
mspuser:~/environment $ sudo mkdir –p /usr/local/jdbc-drivers
mspuser:~/environment $
```
Install the JDBC driver for Oracle database engine
Install the JDBC driver for PostgreSQL database engine
Install the JDBC driver for MariaDB database engine

2. Perform Schema Conversion and Database Migration

1. Create schema conversion project

2. Create AWS DMS replication instance, endpoint and task

Get an AWS CloudFormation stack template body

wget https://github.com/t2yijaeho/AWS-SCT-and-DMS/raw/matia/Template/DMS-Oracle2Postgre.yaml

Get your source database server EC2 private IP address and target RDS database endpoint

EC2_PRIVATE_IP=$(aws ec2 describe-addresses \
  --filters Name=InstanceId,Values=$EC2_INSTANCE_ID \
  --query "PrivateIpAddress" \
  --output text)
echo $EC2_PRIVATE_IP

DB_INSTANCE_ENDPOINT=$( \
  aws rds describe-db-instances \
    --db-instance-identifier targetdb \
    --query "DBInstances[0].Endpoint.Address" \
    --output text)
echo $DB_INSTANCE_ENDPOINT

Create an AWS CloudFormation stack

aws cloudformation create-stack \
  --stack-name Ora2PgDMS \
  --template-body file://./DMS-Oracle2Postgre.yaml \
  --parameters ParameterKey=EC2PrivateIP,ParameterValue=$EC2_PRIVATE_IP \
    ParameterKey=RDSInstanceEndpoint,ParameterValue=$DB_INSTANCE_ENDPOINT

AWS CloudFormation returns following output

{
"StackId": "arn:aws:cloudformation:us-abcd-x:123456789012:stack/Ora2PgDMS/b4d0f5e0-d4c2-11ec-9529-06edcc65f112"
}

Monitor the progress by the stack's events in AWS management console

Name		Name	Last commit message	Last commit date
Latest commit History 14 Commits
Scripts		Scripts
Template		Template
LICENSE		LICENSE
README.md		README.md

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

AWS Schema Conversion Tool and Database Migration Service

1. Prepare Environment

1. Prepare IDE, source and target database

2. Create Windows firewall inbound rule for Oracle TNS port(1521) using PowerShell script

3. Grant Oracle database user privileges

4. Install xdg-utils for SCT

5. Install AWS Schema Conversion Tool

5. Download and Install JDBC drivers for AWS SCT

2. Perform Schema Conversion and Database Migration

1. Create schema conversion project

2. Create AWS DMS replication instance, endpoint and task

About

Releases

Packages

License

t2yijaeho/AWS-SCT-and-DMS

Folders and files

Latest commit

History

Repository files navigation

AWS Schema Conversion Tool and Database Migration Service

1. Prepare Environment

1. Prepare IDE, source and target database

2. Create Windows firewall inbound rule for Oracle TNS port(1521) using PowerShell script

3. Grant Oracle database user privileges

4. Install xdg-utils for SCT

5. Install AWS Schema Conversion Tool

5. Download and Install JDBC drivers for AWS SCT

2. Perform Schema Conversion and Database Migration

1. Create schema conversion project

2. Create AWS DMS replication instance, endpoint and task

About

Topics

Resources

License

Stars

Watchers

Forks

Releases

Packages 0

Packages