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
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>
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.
⋮
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 $
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 $
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
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