control-m-rds.yaml
AWSTemplateFormatVersion: 2010-09-09 Description: Control-M Oracle RDS instance template

Metadata

Metadata is mostly for organizing and presenting Parameters in a better way when using CloudFormation in the AWS Web UI.

Metadata: AWS::CloudFormation::Interface: ParameterGroups: - Label: default: Instance specifications Parameters: - DBEngineVersion - DBUsername - DBPassword

- Label: default: Network & Security Parameters:

 - VPCID
 - SubnetGroupMembers
- Label: default: Backups & Maintenance Parameters: - RestoreDBSnapshotARN - DBBackupWindow - DBBackupRetention - DBMaintenanceWindow - OverrideDBMinorMaintenance - OverrideDBMaintenanceSetting - Label: default: Tagging Parameters: - TagService - TagEnvironment - TagContactNetId - TagAccountNumber - TagTicketNumber

Parameters

These are the input parameters for this template. All of these parameters must be supplied for this template to be deployed.

Parameters:

The version number of the database engine that the DB instance uses. Find latest versions here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.PatchComposition.html

DBEngineVersion: Description: DB engine version Type: String Default: "19" AllowedValues: - "19" - "18" #- "12.2" #- "11.2"

The database instance type/size

DBInstanceType: Type: String Description: >- RDS DB Instance Type and Size db.t3.medium (2vCPU x 4GB) db.t3.large (2vCPU x 8GB) db.t3.xlarge (4vCPU x 16GB) db.m5.large (2vCPU x 8GB) db.m5.xlarge (4vCPU x 16GB) Default: db.m5.xlarge AllowedValues: [db.t3.medium, db.t3.large, db.t3.xlarge, db.m5.large, db.m5.xlarge]

The master user name for the DB instance.

DBUsername: Description: Master username Type: String MinLength: 1 MaxLength: 30 AllowedPattern: "[a-z][a-z0-9#_$]*" ConstraintDescription: Lowercase letters only, first character must be a letter, may also include numbers, and/or the symbols "#" or "_" or "$"; length is 1-30 Default: dbamstr

The master password for the DB instance.

DBPassword: Description: Master password (must be at least 8 characters) Type: String NoEcho: true MinLength: 8 MaxLength: 30 AllowedPattern: "[a-zA-Z][a-zA-Z0-9#_$]*" ConstraintDescription: First character must be a letter, may include any alphanumeric character, and/or the symbols "#" or "_" or "$"; length is 8-30

VPCID is the ID of the VPC where this template will be deployed. VPCID: Description: Choose a Virtual Private Cloud (VPC) Type: AWS::EC2::VPC::Id

The RDS Subnet IDs for the DB Subnet Group. SubnetGroupMembers: Description: Choose at least two private subnet group(s) Type: List

The character set to associate with the DB instance. DBCharacterSet: Description: Default Character Set Type: String Default: AL32UTF8

The name or ARN of the DB snapshot that's used to restore the DB instance.

RestoreDBSnapshotARN: Description: If this is a restore, enter the snapshot ARN; otherwise leave blank Type: String Default: ""

The daily time range during which automated backups are created.

DBBackupWindow: Description: What time would you like to run a daily backup? Type: String Default: 02:00-03:00 AllowedPattern: "([0-1][0-9]|2[0-3]):[0-5][0-9]-([0-1][0-9]|2[0-3]):[0-5][0-9]" ConstraintDescription: Format hh24:mi-hh24:mi in UTC, in at least a 30 minute interval, do not overlap with maintenance window

The number of days during which automatic DB snapshots are retained.

DBBackupRetention: Description: How many days should backups be retained (0-35)? Type: Number Default: 7

The weekly time range during which system maintenance can occur, in UTC.

DBMaintenanceWindow: Description: What time would you like to reserve for weekly maintenance? Type: String Default: sun:13:00-mon:01:00 AllowedPattern: "(sun|mon|tue|wed|thu|fri|sat):([0-1][0-9]|2[0-3]):[0-5][0-9]-(sun|mon|tue|wed|thu|fri|sat):([0-1][0-9]|2[0-3]):[0-5][0-9]" ConstraintDescription: Format is ddd:hh24:mi-ddd:hh24:mi in UTC, in at least a 30 minute interval

Allow overriding the AutoMinorVersionUpgrade setting, normally determined by Prod vs NonProd

OverrideDBMinorMaintenance: Description: Override the default determination of AutoMinorVersionUpgrade Type: String Default: false AllowedValues: [false, true]

Override setting, if override is requested

OverrideDBMaintenanceSetting: Description: If override is selected, what should AutoMinorVersionUpgrade be set to Type: String Default: enabled AllowedValues: [enabled, disabled]

Tags

The following tags are applied to all resources created by this template.

TagService: Description: Name of the service associated with this resource (as listed in the service catalog) Type: String Default: ControlM TagEnvironment: Description: Environment type of this resource (dev, tst, rpt, trn, prd) Type: String Default: prd AllowedValues: - dev - tst - rpt - trn - prd TagContactNetId: Description: NetID of the primary technical resource Type: String Default: donnellc TagAccountNumber: Description: Account number associated with the service Type: String Default: 1192660 TagTicketNumber: Description: Ticket number associated with this database Type: String Default: CLOUD-104

Mappings

Mappings: OracleDBMap: "19": Engine: 19.0.0.0.ru-2020-10.rur-2020-10.r1 "18": Engine: 18.0.0.0.ru-2020-10.rur-2020-10.r1 #"12.2":

Engine: 12.2.0.1.ru-2020-07.rur-2020-07.r1

#"11.2":

Engine: 11.2.0.4.v25

Conditions

Establishes conditions based on input parameters.

Conditions: IsPRD: !Equals [ !Ref TagEnvironment, prd ] IsRestore: !Not [!Equals [!Ref RestoreDBSnapshotARN, ""]] AddOEMAgent: !Not [!Equals [!Ref DBEngineVersion, "19"]] OverrideMinorUpdate: !Equals [!Ref OverrideDBMinorMaintenance, true] OverrideMinorSetting: !Equals [!Ref OverrideDBMaintenanceSetting, enabled]

Resources

These are all of the resources deployed by this template.

Resources:

AWS Account Information

Lambda function to introspect VPCs, subnets, and select most available

AccountInfo: Type: Custom::AccountInfo Properties: ServiceToken: !Sub "arn:aws:lambda:${AWS::Region}:${AWS::AccountId}:function:fdn-cf-account-info" VPCInfo: - vpcid - private-subnet-a - private-subnet-b

Database Instance

This deploys the database RDS instance with some tags.

OracleDBInstance: Type: AWS::RDS::DBInstance Properties: AllocatedStorage: !If [ IsPRD, "20", "20" ] AllowMajorVersionUpgrade: false AutoMinorVersionUpgrade: !If [ OverrideMinorUpdate, !If [OverrideMinorSetting, true, false], !If [ IsPRD, false, true ] ] BackupRetentionPeriod: !Ref DBBackupRetention CharacterSetName: WE8MSWIN1252 CopyTagsToSnapshot: true DBInstanceClass: !Ref DBInstanceType DBInstanceIdentifier: !Sub ${TagService}-${TagEnvironment}-rds-oracle DBName: !Sub ctm19${TagEnvironment}
 DBParameterGroupName:       !Ref DBParameterGroup
DBSnapshotIdentifier: !If [ IsRestore, !Ref RestoreDBSnapshotARN, !Ref "AWS::NoValue" ] DBSubnetGroupName: !Ref DBSubnetGroup Engine: oracle-ee EngineVersion: !FindInMap [OracleDBMap, !Ref DBEngineVersion, Engine] LicenseModel: bring-your-own-license MasterUsername: !Ref DBUsername MasterUserPassword: !Ref DBPassword
 MultiAZ:                    !If [ IsPRD, true, false ]
MultiAZ: true OptionGroupName: !Ref DBOptionGroup Port: "1521" PreferredBackupWindow: !Ref DBBackupWindow PreferredMaintenanceWindow: !Ref DBMaintenanceWindow PubliclyAccessible: false StorageEncrypted: false StorageType: gp2 VPCSecurityGroups: - Ref: VPCSecurityGroup Tags: - Key: service Value: !Ref TagService - Key: Name Value: !Sub ${TagService}-${TagEnvironment}-rds-oracle - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetId - Key: accountnumber Value: !Ref TagAccountNumber - Key: ticketnumber Value: !Ref TagTicketNumber

Database Subnet Group

Subnet group for the database instance, that has at least two availiablity zones

DBSubnetGroup: Type: AWS::RDS::DBSubnetGroup Properties: DBSubnetGroupDescription: !Sub "ctm19${TagEnvironment} Subnet Group"
 SubnetIds: !Ref SubnetGroupMembers
SubnetIds: [!GetAtt AccountInfo.private-subnet-a, !GetAtt AccountInfo.private-subnet-b] Tags: - Key: service Value: !Ref TagService - Key: Name Value: !Sub ${TagService}-${TagEnvironment}-rds-oracle - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetId - Key: accountnumber Value: !Ref TagAccountNumber - Key: ticketnumber Value: !Ref TagTicketNumber

Database Security Group

Security group for the database instance.

VPCSecurityGroup: Type: AWS::EC2::SecurityGroup Properties: GroupDescription: Control-M Security Group for RDS Oracle
 VpcId: !Ref VPCID
VpcId: !GetAtt AccountInfo.vpcid SecurityGroupIngress: - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 128.196.135.64/26 Description: Colleen IP - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 150.135.112.96/27 Description: EntApp VPN - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 10.138.2.0/17 Description: Mosaic VPN - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 128.196.130.85/32 Description: democracy.uits.arizona.edu - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 128.196.130.7/32 Description: UITS OEM12c database access - IpProtocol: tcp FromPort: 3872 ToPort: 3872 CidrIp: 128.196.130.7/32 Description: UITS OEM12c agent access Tags: - Key: service Value: !Ref TagService - Key: Name Value: !Sub ${TagService}-${TagEnvironment}-rds-oracle - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetId - Key: accountnumber Value: !Ref TagAccountNumber - Key: ticketnumber Value: !Ref TagTicketNumber

Database Option Group

Option group for the database instance.

DBOptionGroup: Type: AWS::RDS::OptionGroup Properties: EngineName: oracle-ee MajorEngineVersion: !Ref DBEngineVersion OptionGroupDescription: !Sub "ctm19${TagEnvironment} Option Group" OptionConfigurations: - OptionName: Timezone OptionSettings: - Name: TIME_ZONE Value: America/Phoenix - !If - AddOEMAgent - OptionName: OEM_AGENT OptionVersion: 12.1.0.5.v1 Port: 3872 VpcSecurityGroupMemberships: - !Ref VPCSecurityGroup OptionSettings: - Name: OMS_HOST Value: gracehopper.uits.arizona.edu - Name: OMS_PORT Value: "4903" - Name: AGENT_REGISTRATION_PASSWORD Value: "{{resolve:ssm:/oracle/oem/agent-registration-pw:1}}" - !Ref "AWS::NoValue" Tags: - Key: service Value: !Ref TagService - Key: Name Value: !Sub ${TagService}-${TagEnvironment}-rds-oracle - Key: environment Value: !Ref TagEnvironment - Key: contactnetid Value: !Ref TagContactNetId - Key: accountnumber Value: !Ref TagAccountNumber - Key: ticketnumber Value: !Ref TagTicketNumber DBFreeSpaceAlarm: Type: AWS::CloudWatch::Alarm Properties: AlarmName: !Sub ${TagService}-RDS-${TagEnvironment}-Low-Free-Storage-Space AlarmDescription: Notify when free space drops below 80% of 20GB Namespace: AWS/RDS MetricName: FreeStorageSpace Dimensions: - Name: DBInstanceIndentifier Value: !Ref OracleDBInstance ActionsEnabled: true Statistic: Average Period: 3600 EvaluationPeriods: 1 Threshold: 4000000000 ComparisonOperator: LessThanThreshold AlarmActions: - !Ref DBSNSTopic InsufficientDataActions: - !Ref DBSNSTopic DBSNSTopic: Type: AWS::SNS::Topic Properties: TopicName: !Sub ${TagService}-RDS-${TagEnvironment} DisplayName: !Sub AWS ${TagService} RDS ${TagEnvironment} Subscription: - Endpoint: "donnellc@email.arizona.edu" Protocol: email DBPendingMaintenance: Type: AWS::RDS::EventSubscription Properties: Enabled: true EventCategories: - configuration change - low storage - maintenance SnsTopicArn: !Ref DBSNSTopic SourceIds: - Ref: OracleDBInstance SourceType: db-instance

Outputs

Output values that can be viewed from the AWS CloudFormation console.

Outputs: DBInstance: Description: The database instance identifier Value: !Sub ${TagService}-${TagEnvironment}-rds-oracle Export: Name: !Sub ${AWS::StackName}-DBInstanceIndentifier DBName: Description: The database instance name (ie Oracle SID) Value: !Sub ctm19${TagEnvironment} Export: Name: !Sub ${AWS::StackName}-DBName DBEndpoint: Description: The connection endpoint (ie, listener host) of the database Value: !GetAtt OracleDBInstance.Endpoint.Address Export: Name: !Sub ${AWS::StackName}-Endpoint DBPort: Description: The port number on which the database accepts connections Value: !GetAtt OracleDBInstance.Endpoint.Port Export: Name: !Sub ${AWS::StackName}-Port DBSecurityGroup: Description: The database security group Value: !GetAtt VPCSecurityGroup.GroupId Export: Name: !Sub ${AWS::StackName}-RDSSecurityGroupId