AWSTemplateFormatVersion: 2010-09-09 Description: Oracle RDS instance template for Budget Office (budget_2_rds.yaml)

Metadata: AWS::CloudFormation::Interface: ParameterGroups: - Label: default: Instance specifications Parameters: - DBEngineVersion - DBInstanceClass - DBStorageSize - Label: default: Database configuration Parameters: - DBPrefix - DBUsername - DBPassword - SNSSubscriptionEmail - DBPerformanceInsights - Label: default: Backups & Maintenance Parameters: - RestoreDBSnapshotARN - DBBackupWindow - DBBackupRetention - DBMaintenanceWindow - Label: default: Tagging Parameters: - TagService - TagEnvironment - TagContactNetID - TagAccountNumber - TagTicketNumber - TagSubAccount

DBEngineVersion: Description: DB engine version Type: String Default: 19c AllowedValues: - 19c

DBInstanceClass: Description: The compute and memory capacity of the DB instance Type: String Default: db.m5.large AllowedValues: - db.m5.large - db.m5.xlarge - db.m5.2xlarge - db.m5.4xlarge

DBStorageSize: Description: Storage Size in GB Type: String Default: '35'

DBPrefix: Description: A 3 - 5 character prefix for the DB name (aka Oracle SID) Type: String MinLength: 3 MaxLength: 5 AllowedPattern: "[a-z][a-z0-9]*" ConstraintDescription: Lowercase letters only, first character must be a letter, may also include numbers; length is 3-5

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

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

SNSSubscriptionEmail: Description: The endpoint (email) that receives notifications from the Amazon SNS topic Type: String

DBPerformanceInsights: Description: Enable Performance Insights (true/false) Type: String AllowedValues: - true - false Default: false

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

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 is hh24:mi-hh24:mi in UTC, in at least a 30 minute interval, do not overlap with maintenance window

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

DBMaintenanceWindow: Description: What time would you like to reserve for weekly maintenance? Type: String Default: sun:13:00-sun:19: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

TagService: Description: Name of the service associated with this resource (as listed in the service catalog) Type: String Default: BudgetOffice 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: '1182200' TagTicketNumber: Description: Ticket number associated with this database Type: String Default: CLOUD-893 TagSubAccount: Description: Sub account associated with the service Type: String Default: COMM

VersionMap: 19c: Option: 19 Version: 19.0.0.0.ru-2021-01.rur-2021-01.r1

Conditions: RestoreFromSnapshot: !Not [!Equals [!Ref RestoreDBSnapshotARN, ""]]

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

OracleDBInstance: Type: AWS::RDS::DBInstance Properties: AllocatedStorage: !Ref DBStorageSize AllowMajorVersionUpgrade: false AutoMinorVersionUpgrade: true BackupRetentionPeriod: !Ref DBBackupRetention CharacterSetName: AL32UTF8 CopyTagsToSnapshot: true DBInstanceClass: !Ref DBInstanceClass DBInstanceIdentifier: !Sub ${TagService}-${TagEnvironment}-rds-oracle DBName: !Sub ${DBPrefix}${TagEnvironment}

DBSnapshotIdentifier: !If [ RestoreFromSnapshot, !Ref RestoreDBSnapshotARN, !Ref "AWS::NoValue" ] DBSubnetGroupName: !Ref DBSubnetGroup EnablePerformanceInsights: !Ref DBPerformanceInsights Engine: oracle-ee EngineVersion: !FindInMap [ VersionMap, !Ref DBEngineVersion, Version] LicenseModel: bring-your-own-license MasterUsername: !Ref DBUsername MasterUserPassword: !Ref DBPassword MonitoringInterval: 60 MonitoringRoleArn: !Sub arn:aws:iam::${AWS::AccountId}:role/rds-monitoring-role MultiAZ: false

OptionGroupName: !Ref DBOptionGroup Port: '1521' PreferredBackupWindow: !Ref DBBackupWindow PreferredMaintenanceWindow: !Ref DBMaintenanceWindow PubliclyAccessible: false StorageEncrypted: true 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 - Key: subaccount Value: !Ref TagSubAccount

DBSubnetGroup: Type: AWS::RDS::DBSubnetGroup Properties: DBSubnetGroupDescription: !Sub "${DBPrefix}${TagEnvironment} Subnet Group" 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 - Key: subaccount Value: !Ref TagSubAccount

VPCSecurityGroup: Type: AWS::EC2::SecurityGroup Properties: GroupDescription: Budget Office Security Group for RDS Oracle VpcId: !GetAtt AccountInfo.vpcid SecurityGroupIngress:

- IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 150.135.168.98/32 Description: budget4.budgetoffice.arizona.edu - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 150.135.168.112/32 Description: budget18.budgetoffice.arizona.edu

- IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 10.221.227.0/25 Description: Budget Office WorkSpace 1 - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 10.221.227.128/25 Description: Budget Office WorkSpace 2

- IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 128.196.135.64/26 Description: Computer Center, Room 309 - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 150.135.112.96/27 Description: EntApp VPN

- IpProtocol: tcp FromPort: 2484 ToPort: 2484 CidrIp: 0.0.0.0/0 Description: Anywhere private

- IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 10.220.183.128/26 Description: UAIR Prd Account Private Subnet1 - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 10.220.183.192/26 Description: UAIR Prd Account Private Subnet2

- IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 10.220.161.64/26 Description: UAIR Dev Account Private Subnet1 - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 10.220.161.128/26 Description: UAIR Dev Account Private Subnet2 - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 10.220.161.192/27 Description: UAIR Dev Account Private Subnet3 - IpProtocol: tcp FromPort: 1521 ToPort: 1521 CidrIp: 10.220.161.224/27 Description: UAIR Dev Account Private Subnet4 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 - Key: subaccount Value: !Ref TagSubAccount

DBOptionGroup: Type: AWS::RDS::OptionGroup Properties: EngineName: oracle-ee MajorEngineVersion: !FindInMap [ VersionMap, !Ref DBEngineVersion, Option] OptionGroupDescription: !Sub "${DBPrefix}${TagEnvironment} Option Group" OptionConfigurations: - OptionName: Timezone OptionSettings: - Name: TIME_ZONE Value: America/Phoenix - OptionName: SSL Port: 2484 VpcSecurityGroupMemberships: - Fn::GetAtt: - VPCSecurityGroup - GroupId OptionSettings: - Name: SQLNET.SSL_VERSION Value: '1.2' 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 - Key: subaccount Value: !Ref TagSubAccount DBFreeSpaceAlarm: Type: AWS::CloudWatch::Alarm Properties: AlarmName: !Sub ${TagService}-RDS-${TagEnvironment}-Low-Free-Storage-Space AlarmDescription: Notify when free space drops below 8GB Namespace: AWS/RDS MetricName: FreeStorageSpace Dimensions: - Name: DBInstanceIndentifier Value: !Ref OracleDBInstance ActionsEnabled: true Statistic: Average Period: 3600 EvaluationPeriods: 1 Threshold: 8000000000 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: !Ref SNSSubscriptionEmail Protocol: email DBPendingMaintenance: Type: AWS::RDS::EventSubscription Properties: Enabled: true EventCategories: - availability - configuration change - failure - low storage - maintenance - notification SnsTopicArn: !Ref DBSNSTopic SourceIds: - Ref: OracleDBInstance SourceType: db-instance

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 ${DBPrefix}${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