{ "AWSTemplateFormatVersion" : "2010-09-09", "Description" : "This CloudFormation sample template Oracle_DynamoDB_Aurora_PostgreSQL_DMS creates an Oracle and Aurora PostgreSQL RDS instances in a VPC which can be used to test the database migration using AWS DMS service. It also creates a DynamoDB table and a Cloud9 environment. You will be billed for the AWS resources used if you create a stack from this template", "Parameters" : { "OracleDBName": { "Default": "ORCL", "Description" : "Enter Oracle Database name", "Type": "String", "MinLength": "4", "MaxLength": "63", "AllowedPattern" : "[a-zA-Z][a-zA-Z0-9]*", "ConstraintDescription" : "must begin with a letter and contain a minimum of 4 alphanumeric characters." }, "OracleDBPassword": { "Default": "oraadmin123", "NoEcho": "true", "Description" : "Enter password for the oracle admin user: dbadmin", "Type": "String", "MinLength": "8", "MaxLength": "41", "AllowedPattern" : "[a-zA-Z0-9]*", "ConstraintDescription" : "must contain only alphanumeric characters with minimum of 8 characters." }, "OracleDBStorage": { "Default": "100", "NoEcho": "false", "Description" : "Enter storage for Oracle DB in GB", "Type": "Number", "MaxValue": "6000", "MinValue": "100", "ConstraintDescription" : "must contain only numberic and min 100gb less than 6000 GB." }, "OracleInstanceType":{ "Description":"Oracle DB instance type", "Type":"String", "Default":"db.t3.medium", "AllowedValues":[ "db.t3.medium", "db.t3.large", "db.r5.large", "db.m5.large"], "ConstraintDescription":"must be a valid Oracle instance type." }, "AuroraDBName": { "Default": "taxidb", "Description" : "Enter Aurora Database name", "Type": "String", "MinLength": "4", "MaxLength": "63", "AllowedPattern" : "[a-zA-Z][a-zA-Z0-9]*", "ConstraintDescription" : "must begin with a letter and contain a minimum of 4 alphanumeric characters." }, "AuroraDBUsername": { "Default": "auradmin", "NoEcho": "false", "Description" : "Enter Master admin username for Aurora RDS", "Type": "String", "MinLength": "4", "MaxLength": "16", "AllowedPattern" : "[a-zA-Z][a-zA-Z0-9]*", "ConstraintDescription" : "must begin with a letter and contain a minimum of 4 alphanumeric characters." }, "AuroraDBPassword": { "Default": "auradmin123", "NoEcho": "true", "Description" : "Enter password for Aurora RDS Admin user", "Type": "String", "MinLength": "8", "MaxLength": "41", "AllowedPattern" : "[a-zA-Z0-9]*", "ConstraintDescription" : "must contain only alphanumeric characters with minimum of 8 characters." }, "AuroraInstanceType":{ "Description":"Aurora PostgreSQL DB instance type", "Type":"String", "Default":"db.r5.large", "AllowedValues":[ "db.t3.medium", "db.r5.large", "db.r5.xlarge" ], "ConstraintDescription":"must be a valid Aurora PostgreSQL instance types." }, "AuroraEngineType": { "Description": "Aurora DB Engine type", "Type": "String", "Default": "aurora-postgresql", "AllowedValues": [ "aurora-postgresql" ], "ConstraintDescription": "must be a valid Aurora DB engine types." }, "DMSInstanceType":{ "Description":"DMS Replication instance type", "Type":"String", "Default":"dms.t2.medium", "AllowedValues":[ "dms.t2.medium", "dms.t2.large", "dms.c4.large", "dms.c4.xlarge" ], "ConstraintDescription":"must be a valid DMS Replication instance types." }, "ExistsDMSRole": { "Default": "N", "Description": "check If the dms-vpc-role exists in your account, please enter Y, else enter N", "Type": "String", "MinLength": "1", "MaxLength": "1", "AllowedPattern" : "[YN]", "ConstraintDescription" : "Permitted value is Y or N." }, "ClientIP" : { "Description" : "The IP address range that can be used to connect to the RDS instances from your local machine.It must be a valid IP CIDR range of the form x.x.x.x/x.Pls get your address using checkip.amazonaws.com or whatsmyip.org", "Type": "String", "MinLength": "9", "MaxLength": "18", "Default": "0.0.0.0/0", "AllowedPattern": "(\\d{1,3})\\.(\\d{1,3})\\.(\\d{1,3})\\.(\\d{1,3})/(\\d{1,2})", "ConstraintDescription": "It must be a valid IP CIDR range of the form x.x.x.x/x. Suggest to enable access to your IP address only. Pls get your address using checkip.amazonaws.com or whatsmyip.org." } }, "Metadata" : { "AWS::CloudFormation::Interface" : { "ParameterGroups" : [ { "Label" : { "default" : "Source Oracle Database Configuration" }, "Parameters" : [ "OracleDBName", "OracleDBPassword","OracleDBStorage","OracleInstanceType" ] }, { "Label" : { "default":"Target Aurora PostgreSQL Database Configuration" }, "Parameters" : ["AuroraDBName","AuroraDBUsername", "AuroraDBPassword","AuroraInstanceType", "AuroraEngineType"] }, { "Label" : { "default":"DMS Configuration" }, "Parameters" : ["DMSInstanceType" ,"ExistsDMSRole"] }, { "Label" : { "default" : "Enter IP address for the DB Security group Configuration" }, "Parameters" : [ "ClientIP" ] } ] } }, "Mappings": { "OracleEngineVersion" : { "us-east-1" : {"ver" : "19.0.0.0.ru-2022-10.rur-2022-10.r1"}, "us-west-2" : {"ver" : "19.0.0.0.ru-2022-10.rur-2022-10.r1"} }, "OracleSnapshotId" : { "us-east-1" : {"snapid" : "arn:aws:rds:us-east-1:152097041981:snapshot:aws-db-workshop-oracle-19c-taxidata-sample"}, "us-west-2" : {"snapid" : "arn:aws:rds:us-west-2:152097041981:snapshot:aws-db-workshop-oracle-19c-taxidata-sample"} }, "AuroraMap": { "aurora-postgresql": { "AuroraEngineVersion": "12.9", "AuroraPort": "5432", "AuroraParameterGroupName": "default.aurora-postgresql12", "AuroraPGFamily": "aurora-postgresql12" } } }, "Conditions": { "NotExistsDMSVPCRole": {"Fn::Equals": [{"Ref": "ExistsDMSRole"}, "N"]} }, "Resources" : { "VPC" : { "Type" : "AWS::EC2::VPC", "Properties" : { "CidrBlock" : "10.0.0.0/20", "EnableDnsSupport" : "true", "EnableDnsHostnames" : "true", "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"}}, {"Key" : "Name", "Value" : { "Ref" : "AWS::StackName"}}] } }, "DBSubnet1" : { "Type" : "AWS::EC2::Subnet", "Properties" : { "VpcId" : { "Ref" : "VPC" }, "CidrBlock" : "10.0.3.0/25", "AvailabilityZone" : { "Fn::Select" : [ "0", { "Fn::GetAZs" : "" } ] }, "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ] } }, "DBSubnet2" : { "Type" : "AWS::EC2::Subnet", "Properties" : { "VpcId" : { "Ref" : "VPC" }, "CidrBlock" : "10.0.4.0/25", "AvailabilityZone" : { "Fn::Select" : [ "1", { "Fn::GetAZs" : "" } ] }, "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ] } }, "AppSubnet1" : { "Type" : "AWS::EC2::Subnet", "Properties" : { "VpcId" : { "Ref" : "VPC" }, "CidrBlock" : "10.0.1.0/25", "AvailabilityZone" : { "Fn::Select" : [ "0", { "Fn::GetAZs" : "" } ] }, "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ] } }, "AppSubnet2" : { "Type" : "AWS::EC2::Subnet", "Properties" : { "VpcId" : { "Ref" : "VPC" }, "CidrBlock" : "10.0.2.0/25", "AvailabilityZone" : { "Fn::Select" : [ "1", { "Fn::GetAZs" : "" } ] }, "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ] } }, "InternetGateway" : { "Type" : "AWS::EC2::InternetGateway", "Properties" : { "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ] } }, "AttachGateway" : { "Type" : "AWS::EC2::VPCGatewayAttachment", "Properties" : { "VpcId" : { "Ref" : "VPC" }, "InternetGatewayId" : { "Ref" : "InternetGateway" } } }, "RouteTable" : { "Type" : "AWS::EC2::RouteTable", "Properties" : { "VpcId" : {"Ref" : "VPC"}, "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ] } }, "Route" : { "Type" : "AWS::EC2::Route", "DependsOn" : "AttachGateway", "Properties" : { "RouteTableId" : { "Ref" : "RouteTable" }, "DestinationCidrBlock" : "0.0.0.0/0", "GatewayId" : { "Ref" : "InternetGateway" } } }, "SubnetRouteTableAssociation" : { "Type" : "AWS::EC2::SubnetRouteTableAssociation", "Properties" : { "SubnetId" : { "Ref" : "DBSubnet1" }, "RouteTableId" : { "Ref" : "RouteTable" } } }, "SubnetRouteTableAssociation1" : { "Type" : "AWS::EC2::SubnetRouteTableAssociation", "Properties" : { "SubnetId" : { "Ref" : "DBSubnet2" }, "RouteTableId" : { "Ref" : "RouteTable" } } }, "MyNATGateway": { "Type": "AWS::EC2::NatGateway", "DependsOn": "MyNATPublicIP", "Properties": { "AllocationId": { "Fn::GetAtt": [ "MyNATPublicIP", "AllocationId" ] }, "SubnetId": { "Ref": "DBSubnet1" } } }, "MyNATPublicIP": { "Type": "AWS::EC2::EIP", "DependsOn": "VPC", "Properties": { "Domain": "vpc" } }, "MyprivateRouteTable": { "Type": "AWS::EC2::RouteTable", "Properties": { "VpcId": { "Ref" : "VPC" } } }, "MyprivateRoute": { "Type": "AWS::EC2::Route", "Properties": { "RouteTableId": { "Ref": "MyprivateRouteTable" }, "DestinationCidrBlock": "0.0.0.0/0", "NatGatewayId": { "Ref": "MyNATGateway" } } }, "privateSubnet1RouteTableAssociation": { "Type": "AWS::EC2::SubnetRouteTableAssociation", "Properties": { "SubnetId": { "Ref": "AppSubnet1" }, "RouteTableId": { "Ref": "MyprivateRouteTable" } } }, "privateSubnet2RouteTableAssociation": { "Type": "AWS::EC2::SubnetRouteTableAssociation", "Properties": { "SubnetId": { "Ref": "AppSubnet2" }, "RouteTableId": { "Ref": "MyprivateRouteTable" } } }, "MyDBSubnetGroup" : { "Type" : "AWS::RDS::DBSubnetGroup", "Properties" : { "DBSubnetGroupDescription" : "Subnet available for the DMS Demo RDS DB Instance", "SubnetIds" : [{ "Ref" : "DBSubnet1" },{ "Ref" : "DBSubnet2" }] } }, "MyDMSReplicationSubnetGroup" : { "Type" : "AWS::DMS::ReplicationSubnetGroup", "Properties" : { "ReplicationSubnetGroupDescription" : "Subnet group for DMS replication instances", "SubnetIds" : [{ "Ref" : "AppSubnet1" },{ "Ref" : "AppSubnet2" }] } }, "OraVPCSecurityGroup" : { "Type" : "AWS::EC2::SecurityGroup", "Properties" : { "GroupDescription" : "Security group for Oracle Instance.", "VpcId" : { "Ref" : "VPC" }, "SecurityGroupIngress" : [ { "IpProtocol" : "tcp", "FromPort" : "1521", "ToPort" : "1521", "CidrIp" : { "Ref" : "ClientIP"} }, { "IpProtocol" : "tcp", "FromPort" : "1521", "ToPort" : "1521", "CidrIp" : "10.0.0.0/20" } ] } }, "AuroraVPCSecurityGroup" : { "Type" : "AWS::EC2::SecurityGroup", "Properties" : { "GroupDescription" : "Security group for Aurora PostgreSQL Instances.", "VpcId" : { "Ref" : "VPC" }, "SecurityGroupIngress" : [ { "IpProtocol" : "tcp", "FromPort" : "5432", "ToPort" : "5432", "CidrIp" : { "Ref" : "ClientIP"} }, { "IpProtocol" : "tcp", "FromPort" : "5432", "ToPort" : "5432", "CidrIp" : "10.0.0.0/20" }] } }, "LambdaVPCSecurityGroup" : { "Type" : "AWS::EC2::SecurityGroup", "Properties" : { "GroupDescription" : "Security group for Lambda and VPC endpoints connectivity.", "VpcId" : { "Ref" : "VPC" }, "SecurityGroupIngress" : [ { "IpProtocol" : "tcp", "FromPort" : "443", "ToPort" : "443", "CidrIp" : "10.0.0.0/20" }] } }, "OracleDB" : { "Type" : "AWS::RDS::DBInstance", "DependsOn" : "AttachGateway", "DeletionPolicy" : "Delete", "Properties" : { "DBName" : { "Ref" : "OracleDBName" }, "AllocatedStorage" : { "Ref" : "OracleDBStorage" }, "MasterUserPassword" : { "Ref" : "OracleDBPassword" }, "DBInstanceClass" : { "Ref" : "OracleInstanceType" }, "Engine" : "oracle-se2", "EngineVersion" : {"Fn::FindInMap" : [ "OracleEngineVersion", { "Ref" : "AWS::Region" }, "ver" ]}, "LicenseModel" : "license-included", "PubliclyAccessible" : "true", "AvailabilityZone" : { "Fn::GetAtt" : [ "DBSubnet1", "AvailabilityZone" ] }, "MultiAZ" : "false", "DBSubnetGroupName" : { "Ref" : "MyDBSubnetGroup" }, "VPCSecurityGroups" : [ { "Ref" : "OraVPCSecurityGroup" } ], "DBSnapshotIdentifier": {"Fn::FindInMap" : [ "OracleSnapshotId", { "Ref" : "AWS::Region" }, "snapid" ]}, "StorageType" : "gp2", "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ] } }, "AuroraCluster" : { "Type" : "AWS::RDS::DBCluster", "DependsOn" : "AttachGateway", "Properties" : { "MasterUsername" : { "Ref" : "AuroraDBUsername" }, "MasterUserPassword" : { "Ref" : "AuroraDBPassword" }, "Engine" : {"Ref": "AuroraEngineType"}, "EngineVersion": { "Fn::FindInMap": [ "AuroraMap", { "Ref": "AuroraEngineType" }, "AuroraEngineVersion" ] }, "DBClusterParameterGroupName": { "Fn::FindInMap": [ "AuroraMap", { "Ref": "AuroraEngineType" }, "AuroraParameterGroupName" ] }, "DatabaseName" : {"Ref": "AuroraDBName"}, "Port": "5432", "DBSubnetGroupName" : { "Ref" : "MyDBSubnetGroup" }, "VpcSecurityGroupIds" : [{ "Ref" : "AuroraVPCSecurityGroup" }] } }, "AuroraDBParameterGroup": { "Type": "AWS::RDS::DBParameterGroup", "Properties" : { "Description" : "PostgreSQL Parameter Group for DMS Lab", "Family" : { "Fn::FindInMap": [ "AuroraMap", { "Ref": "AuroraEngineType" }, "AuroraPGFamily" ] }, "Parameters" : { "session_replication_role": "replica" } } }, "AuroraDB" : { "Type" : "AWS::RDS::DBInstance", "DependsOn" : "AttachGateway", "Properties" : { "DBSubnetGroupName" : { "Ref" : "MyDBSubnetGroup" }, "Engine" : {"Ref": "AuroraEngineType"}, "MultiAZ" : "false", "DBClusterIdentifier" : {"Ref" : "AuroraCluster" }, "DBParameterGroupName" : {"Ref":"AuroraDBParameterGroup"}, "PubliclyAccessible" : "true", "AvailabilityZone" : { "Fn::GetAtt" : [ "DBSubnet1", "AvailabilityZone" ] }, "DBInstanceClass" : { "Ref" : "AuroraInstanceType" }, "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ] } }, "Cloud9Environment": { "Type": "AWS::Cloud9::EnvironmentEC2", "Properties": { "AutomaticStopTimeMinutes": 60, "InstanceType": "t3.medium", "Name": { "Fn::Sub": "Project-${AWS::StackName}" }, "SubnetId": { "Ref": "DBSubnet1" } } }, "myDynamoDBTable" : { "Type" : "AWS::DynamoDB::Table", "Properties" : { "AttributeDefinitions": [ { "AttributeName": "riderid", "AttributeType": "S" }, { "AttributeName": "tripinfo", "AttributeType": "S" }, { "AttributeName": "DROPOFF_DATETIME", "AttributeType": "S" }, { "AttributeName": "DRIVER_EMAIL", "AttributeType": "S" } ], "KeySchema" : [ { "AttributeName" : "riderid", "KeyType" : "HASH" }, { "AttributeName" : "tripinfo", "KeyType" : "RANGE" } ], "BillingMode" : "PAY_PER_REQUEST", "TableName" : "aws-db-workshop-trips", "GlobalSecondaryIndexes" : [{ "IndexName" : "trips-driver-GSI1", "KeySchema" : [ { "AttributeName" : "DRIVER_EMAIL", "KeyType" : "HASH" }, { "AttributeName" : "DROPOFF_DATETIME", "KeyType" : "RANGE" } ], "Projection": { "ProjectionType": "KEYS_ONLY" } }] } }, "Cloud9DevIAMRole" : { "Type" : "AWS::IAM::Role", "Properties" : { "ManagedPolicyArns" : ["arn:aws:iam::aws:policy/AmazonDynamoDBFullAccess","arn:aws:iam::aws:policy/AmazonS3FullAccess","arn:aws:iam::aws:policy/AWSLambda_FullAccess","arn:aws:iam::aws:policy/SecretsManagerReadWrite","arn:aws:iam::aws:policy/AWSCloudFormationFullAccess"], "AssumeRolePolicyDocument" : { "Version" : "2012-10-17", "Statement" : [{ "Effect" : "Allow", "Principal" : { "Service" : "ec2.amazonaws.com" }, "Action" : "sts:AssumeRole" }] }, "Path": "/" }}, "DBWorkshopS3Bucket": { "Type": "AWS::S3::Bucket" }, "DMSIAMRole1": { "Type": "AWS::IAM::Role", "Condition" : "NotExistsDMSVPCRole", "Properties": { "ManagedPolicyArns": [ "arn:aws:iam::aws:policy/service-role/AmazonDMSVPCManagementRole" ], "Path": "/", "RoleName": "dms-vpc-role", "AssumeRolePolicyDocument" : { "Version" : "2012-10-17", "Statement" : [{ "Effect" : "Allow", "Principal" : { "Service" : "dms.amazonaws.com" }, "Action" : "sts:AssumeRole" }] } } }, "DMSIAMRole2": { "Type": "AWS::IAM::Role", "Condition" : "NotExistsDMSVPCRole", "Properties": { "ManagedPolicyArns": ["arn:aws:iam::aws:policy/service-role/AmazonDMSCloudWatchLogsRole"], "Path": "/", "RoleName": "dms-cloudwatch-logs-role", "AssumeRolePolicyDocument" : { "Version" : "2012-10-17", "Statement" : [{ "Effect" : "Allow", "Principal" : { "Service" : "dms.amazonaws.com" }, "Action" : "sts:AssumeRole" }] } } }, "DMSMigratetoDDBRole" : { "Type" : "AWS::IAM::Role", "Properties" : { "ManagedPolicyArns" : ["arn:aws:iam::aws:policy/AmazonDynamoDBFullAccess"], "AssumeRolePolicyDocument" : { "Version" : "2012-10-17", "Statement" : [{ "Effect" : "Allow", "Principal" : { "Service" : "dms.amazonaws.com" }, "Action" : "sts:AssumeRole" }] } } }, "DMSWorkshopInstance": { "Type": "AWS::DMS::ReplicationInstance", "Properties": { "AllocatedStorage": 100, "PubliclyAccessible": "false", "EngineVersion": "3.4.6", "ReplicationInstanceClass": { "Ref" : "DMSInstanceType" }, "ReplicationSubnetGroupIdentifier": { "Ref" : "MyDMSReplicationSubnetGroup" }, "Tags" : [ {"Key" : "Application", "Value" : { "Ref" : "AWS::StackId"} } ] } } }, "Outputs" : { "StackName" : { "Value" : { "Ref" : "AWS::StackName" } }, "Regionname" : { "Value" : { "Ref" : "AWS::Region" } }, "VPCid" : { "Value" : { "Fn::GetAtt" : [ "AppSubnet1" , "VpcId" ] }}, "LambdaSecurityGroupId" : { "Value" : { "Fn::GetAtt" : [ "LambdaVPCSecurityGroup" , "GroupId" ]}}, "LambdaSubnet1" : { "Value" : { "Ref" : "AppSubnet1" } }, "LambdaSubnet2" : { "Value" : { "Ref" : "AppSubnet2" } }, "S3bucketName": { "Description": "S3 bucket used for this workshop", "Value": { "Ref": "DBWorkshopS3Bucket" } }, "Cloud9Env": { "Value": { "Fn::Join": [ "", [ "https://", { "Ref": "AWS::Region" }, ".console.aws.amazon.com/cloud9/home/", "?region=", { "Ref": "AWS::Region" } ] ] } }, "DDBTableName" : { "Value" : { "Ref" : "myDynamoDBTable" }}, "DMSInstanceName" : { "Value" : { "Ref" : "DMSWorkshopInstance" }}, "DMSDDBRoleARN" : { "Value" : { "Fn::GetAtt": [ "DMSMigratetoDDBRole", "Arn"] }}, "OracleDBMasterUser":{ "Value" :{ "Fn::Select" : [ "0", [ "dbadmin", "auradmin"] ] }}, "OracleRDSDetails": { "Description" : "Oracle RDS DNS:port:DBName", "Value" : { "Fn::Join": [ "", [ { "Fn::GetAtt": [ "OracleDB", "Endpoint.Address" ] }, ":", { "Fn::GetAtt": [ "OracleDB", "Endpoint.Port" ] }, ":", { "Ref": "OracleDBName" }]]} }, "OracleJDBCConnectionString": { "Description" : "JDBC string for Oracle database", "Value" : { "Fn::Join": [ "", [ "jdbc:oracle:thin:@", { "Fn::GetAtt": [ "OracleDB", "Endpoint.Address" ] }, ":", { "Fn::GetAtt": [ "OracleDB", "Endpoint.Port" ] }, ":", { "Ref": "OracleDBName" }]]} }, "AuroraClusterEndpointName": { "Description" : "Aurora cluster Name", "Value" : { "Fn::Join": [ "", [ { "Fn::GetAtt": [ "AuroraCluster", "Endpoint.Address" ]} ]] } }, "AuroraDBName": { "Description" : "Aurora DB Name", "Value" : { "Ref": "AuroraDBName" } }, "AuroraDBMasterUser": { "Value" : { "Ref" : "AuroraDBUsername" } }, "AuroraJDBCConnectionString": { "Description" : "JDBC string for Aurora database", "Value" : { "Fn::Join": [ "", [ "jdbc:postgresql://", { "Fn::GetAtt": [ "AuroraCluster", "Endpoint.Address" ] }, ":", { "Fn::GetAtt": [ "AuroraCluster", "Endpoint.Port" ] }, "/", { "Ref": "AuroraDBName" } ]]} } } }