Migrating an Oracle instance to AWS Aurora – 1 – Setting up the base infrastructure

Migrating database to the cloud is a hot topic since a few years. As more and more of our customers are in the cloud, and some of them in AWS, migrating some of their Oracle databases to AWS Aurora becomes a topic from time to time. In this little blog series we’ll have a look at how you can use the AWS Database Migration Service (DMS) and the AWS Schema Conversion Tool (SCT) for simplifying such a task. In this very first post we’ll setup the basic infrastructure we need for the demo.

I’ll be using Terraform once again for bringing up all the basic stuff. For the purpose of VPCs, Subnets, route tables and Security Groups please check the AWS documentation, this is not in the scope of this post.

The first thing to do in the Terraform script is to specify the AWS profile and region we want to use:

// set the provider to AWS and the AWS region to eu-central-1
provider "aws" {
  profile    = "test"
  region     = "eu-central-1"
}

After that there are two variables: the first one defines my local IP-address which is used in the security group definitions below, so connections via SSH and RDP will be possible from my current location. The second one defines the User data that will be passed to the EC2 instance that will host the Oracle source database. Basically it installs the Oracle Database Express Edition (XE) Release 18.4.0.0.0 (18c) and the Oracle sample schemas:

locals {
  my_ip        = ["XXX.XXX.XXX.XXX/32"]
  instance-userdata = <<EOF
#!/bin/bash
sudo yum update -y
sudo yum install -y wget perl
wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-18c-1.0-1.x86_64.rpm -O /home/ec2-user/oracle-database-xe-18c-1.0-1.x86_64.rpm
wget https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm -O /home/ec2-user/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
wget wget http://mirror.centos.org/centos/7/os/x86_64/Packages/compat-libstdc++-33-3.2.3-72.el7.i686.rpm -O /home/ec2-user/compat-libstdc++-33-3.2.3-72.el7.i686.rpm
sudo yum localinstall -y /home/ec2-user/compat-libstdc++-33-3.2.3-72.el7.i686.rpm
sudo yum localinstall -y /home/ec2-user/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
sudo yum localinstall -y /home/ec2-user/oracle-database-xe-18c-1.0-1.x86_64.rpm
(echo "manager"; echo "manager";) | /etc/init.d/oracle-xe-18c configure
sudo echo ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE/ >> /home/oracle/.bash_profile
sudo echo PATH=$PATH:$ORACLE_HOME/bin >> /home/oracle/.bash_profile
sudo echo ORACLE_SID=xe >> /home/oracle/.bash_profile
sudo echo export ORACLE_HOME PATH ORACLE_SID >> /home/oracle/.bash_profile
wget https://github.com/oracle/db-sample-schemas/archive/v19.2.tar.gz -O /home/oracle/v19.2.tar.gz
sudo su - oracle -c "tar -axf v19.2.tar.gz"
sudo su - oracle -c "cd db-sample-schemas-19.2; perl -p -i.bak -e 's#__SUB__CWD__#/home/oracle/db-sample-schemas-19.2#g' *.sql */*.sql */*.dat"
sudo su - oracle -c "cd db-sample-schemas-19.2; sqlplus system/manager@localhost/XEPDB1 @mksample manager manager manager manager manager manager manager manager users temp /tmp/ localhost/XEPDB1"
chkconfig --add oracle-xe-18c
EOF
}

The next lines of the Terraform script will setup all the network related stuff which I am not going to explain here:

// create the virtual private network
resource "aws_vpc" "dwe-vpc" {
  cidr_block = "10.0.0.0/16"
  enable_dns_hostnames = true
  enable_dns_support = true
  
  tags = {
    Name = "dwe-vpc"
  }
}

// create the internet gateway
resource "aws_internet_gateway" "dwe-igw" {
  vpc_id = "${aws_vpc.dwe-vpc.id}"

  tags = {
    Name = "dwe-igw"
  }
}

// create a dedicated subnet
resource "aws_subnet" "dwe-subnet" {
  vpc_id            = "${aws_vpc.dwe-vpc.id}"
  cidr_block        = "10.0.1.0/24"
  availability_zone = "eu-central-1a"

  tags = {
    Name = "dwe-subnet"
  }
}

// create a second dedicated subnet, this is required for RDS
resource "aws_subnet" "dwe-subnet-2" {
  vpc_id            = "${aws_vpc.dwe-vpc.id}"
  cidr_block        = "10.0.2.0/24"
  availability_zone = "eu-central-1b"

  tags = {
    Name = "dwe-subnet-2"
  }
}


// create routing table which points to the internet gateway
resource "aws_route_table" "dwe-route" {
  vpc_id = "${aws_vpc.dwe-vpc.id}"

  route {
    cidr_block = "0.0.0.0/0"
    gateway_id = "${aws_internet_gateway.dwe-igw.id}"
  }

  tags = {
    Name = "dwe-igw"
  }
}

// associate the routing table with the subnet
resource "aws_route_table_association" "subnet-association" {
  subnet_id      = "${aws_subnet.dwe-subnet.id}"
  route_table_id = "${aws_route_table.dwe-route.id}"
}

// create a security group for ssh access to the linux systems
resource "aws_security_group" "dwe-sg-ssh" {
  name        = "dwe-sg-ssh"
  description = "Allow SSH inbound traffic"
  vpc_id      = "${aws_vpc.dwe-vpc.id}"

  ingress {
    from_port   = 22
    to_port     = 22
    protocol    = "tcp"
    cidr_blocks = local.my_ip
  }

  // allow access to the internet
  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = {
    Name = "dwe-sg-ssh"
  }
}

// create a security group for rdp access to the windows systems
resource "aws_security_group" "dwe-sg-rdp" {
  name        = "dwe-sg-rdp"
  description = "Allow RDP inbound traffic"
  vpc_id      = "${aws_vpc.dwe-vpc.id}"

  ingress {
    from_port   = 3389
    to_port     = 3389
    protocol    = "tcp"
    cidr_blocks = local.my_ip
  }

  // allow access to the internet
  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = {
    Name = "dwe-sg-rdp"
  }
}

Once the network is ready we’ll deploy the EC2 instance that will run the Oracle database (Red Hat 7.7 in this case):

// setup a red hat 7 system for the oracle source
resource "aws_instance" "dwe-oracle-source" {
  ami                         = "ami-05798e9b15f285b27"
  instance_type               = "t2.medium"
  key_name                    = "dwe-key-pair"
  vpc_security_group_ids      = ["${aws_security_group.dwe-sg-ssh.id}"]
  subnet_id                   = "${aws_subnet.dwe-subnet.id}"
  associate_public_ip_address = "true"
  user_data                   = "${base64encode(local.instance-userdata)}"

  root_block_device {
    volume_size           = "30"
    volume_type           = "standard"
    delete_on_termination = "true"
  }

  tags = {
    Name = "dwe-oracle-source"
  }
}

As the target for the migration will be an Aurora cluster we will need that as well. This are basically three steps:

• Create a DB Subnet Group
• Creating the RDS cluster
• Creating and attaching the RDS instance to the RDS cluster

// create the subnet group for RDS instance
resource "aws_db_subnet_group" "dwe-rds-subnet-group" {
    name = "dwe-rds-subnet-group"
    subnet_ids = [
        "${aws_subnet.dwe-subnet.id}",
        "${aws_subnet.dwe-subnet-2.id}"
    ]
}

// create the RDS cluster
resource "aws_rds_cluster" "aws_rds_cluster_dwe" {
    backup_retention_period = "7"
    cluster_identifier = "aurora-dwe"
    db_cluster_parameter_group_name = "default.aurora-postgresql10"
    db_subnet_group_name = "${aws_db_subnet_group.dwe-rds-subnet-group.id}"
    deletion_protection = "false"
    engine = "aurora-postgresql"
    engine_mode = "provisioned"
    engine_version = "10.11"
    master_password = "manager123"
    master_username = "postgres"
    port = "5432"
    skip_final_snapshot = true
}

// create the RDS instance
resource "aws_rds_cluster_instance" "aws_db_instance_dwe" {
    auto_minor_version_upgrade = "true"
    publicly_accessible = "false"
    monitoring_interval = "0"
    instance_class = "db.r5.large"
    cluster_identifier = "${aws_rds_cluster.aws_rds_cluster_dwe.id}"    
    identifier = "aurora-1-instance-1"
    db_subnet_group_name = "${aws_db_subnet_group.dwe-rds-subnet-group.id}"
    engine = "aurora-postgresql"
    engine_version = "10.11"
}

For running the AWS Schema Conversion Tool we’ll finally setup a Windows instance so we are able to connect via RDP and install the AWS Schema Conversion Tool in the next post:

// create a windows instance for the AWS SCT
resource "aws_instance" "dwe-oracle-sct" {
  ami           = "ami-0cc2a6842e0da929f"
  instance_type = "t2.micro"
  key_name                    = "dwe-key-pair"
  vpc_security_group_ids      = ["${aws_security_group.dwe-sg-rdp.id}"]
  subnet_id                   = "${aws_subnet.dwe-subnet.id}"
  associate_public_ip_address = "true"

  root_block_device { 
    volume_size           = "30"
    volume_type           = "standard"
    delete_on_termination = "true"
  }

  tags = {
    Name = "dwe-oracle-sct"
  }
}

Put all these steps together in a file and let Terraform do the work for you:

$ terraform init
$ terrafrom plan
$ terraform apply

The whole Oracle stuff will take some time to complete as downloading the Oracle XE rpm and the installation is nothing you can do in seconds.
In the next post we’ll look at the AWS Schema Conversion Utility and how that can be used to convert an Oracle schema to an AWS Aurora with PostgreSQL compatibility schema.