Amazon provide an AWS Command Line Interface which gives the ability to gather information from the command prompt, and potentially create text files and load these into a database for better reporting.

I used this guide : https://docs.aws.amazon.com/cli/latest/userguide/awscli-install-windows.html

Download the installer and run it

Download and install Python V3.6 from https://www.python.org/downloads/release

Make sure you select ‘Add to path’

Once installed, run

pip install awscli

from the command prompt

Run this to upgrade to the latest version

pip install --user --upgrade awscli

You may get another prompt if you are not using the latest version of something – run the command that will be displayed

Close the command window and re-open

Log into the AWS console and select ‘My Security Credentials’

Click on ‘Access keys…’

Click on ‘Create New Access Key’

The key will be created – make a note and / or download the file

Go to the RDS instance in the console, and note the Availability Zone

Go to the command prompt, run

aws configure

And enter the information – note the default region name drops off the last character. If in doubt, go to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.RegionsAndAvailabilityZones.html

If all the information is correct, you will now be able to run commands against your instances (if you are using multi-factor authentication, see below)

aws rds describe-db-instances

You can basically do anything

aws rds stop-db-instance –db-instance-identifier MGAMON

If you are using Multi Factor Authentication (MFA), then this won’t work. You need to run a command to generate temporary credentials by entering the code from the Google Authenticator app.

You need to know your ARN – go to IAM from the AWS console and navigate to your username

You then run the command, and put the token code in

aws sts get-session-token --serial-number arn:aws:iam::83XXXXXXXX64:mfa/user.name --token-code 537662

This will return a set of credentials

CREDENTIALS ASIAXXXXXXXXXXXXXZ7JK5A 2018-09-06T17:22:59Z i5gVyiw+LXRsbUnv1GD/XXXXXXXXXXXXXU6nLk0BtA FQoGZXIvYXdzEHcaDJ1kZm7jTMh0OI95gCKwAVbTGneVDrFuTbJedKs/oir7D7nT+JQZasMGkhOWaO8X0XnopSFIB+XXXXXXXXXXXXXXXXXo9XOUa8nCodgjH4IWxR2YWdWPmNi3YD5z0FvI+TH0KYExCg0ScceGVxxxxxxxxxxxxxxxxxDgf9syGrVNwqnI9JP2GdgUVp+XXXXXXXJxsZAVjChAIRgEb82LCpsgtqtJWlXXXXXXXXXXXXXXPzwtwF

These are valid for 12 hours.

Set them as environment variables:

export AWS_ACCESS_KEY = ASIAXXXXXXXXXXXXXZ7JK5A

export AWS_SECRET_ACCESS_KEY= i5gVyiw+LXRsbUnv1GD/XXXXXXXXXXXXXU6nLk0BtA

export AWS_SESSION_TOKEN=FQoGZXIvYXdzEHcaDJ1kZm7jTMh0OI95gCKwAVbTGneVDrFuTbJedKs/oir7D7nT+JQZasMGkhOWaO8X0XnopSFIB+XXXXXXXXXXXXXXXXXo9XOUa8nCodgjH4IWxR2YWdWPmNi3YD5z0FvI+xxxxxxxxxxxxxxxxxGrVNwqnI9JP2GdgUVp+XXXXXXXJxsZAVjChAIRgEb82LCpsgtXXXXXXXXXXXXXXLPzwtwF

Create a profile in the .aws/config file with the target role

[default]

aws_access_key_id=AKIAXXXXXXXXXXXXXXXBJJQ

aws_secret_access_key=S+XXXXXXXXXXXXXXXXXXXXXDZk6deZjXXXWw

output = text

region = ap-southeast-2

[profile etrm]

role_arn=arn:aws:iam::32XXXXXXXXXX4:role/UsersMGA

source_profile=default

output=text

region=ap-southeast-2

mfa_serial=arn:aws:iam::83XXXXXXXXX4:mfa/user.name

Note the mfa_serial has “mfa” and not “user” – it won’t work otherwise

You can then run commands and it won’t prompt for the key

aws rds describe-db-instances --profile etrm

DBINSTANCES 5120 False ap-southeast-2c 7 rds-ca-2015 True arn:aws:rds:ap-southeast-2:32XXXXXXXX814:db:allegrordsprod db.m4.4xlarge allegrordsprod available 0 db-HDLEXXXXXXXXXRGIXXXY sqlserver-ee 12.00.5571.0.v1 arn:aws:logs:ap-southeast-2:3XXXXXX4:log-group:RDSOSMetrics:log-stream:db-HDLXXSP4XXXXXXXXX4Y False 2017-12-05T22:45:06.679Z 2018-09-09T22:54:01Z license-included administrator 1 arn:aws:iam::32XXXXXXXXX4:role/rds-monitoring-role True False 22:00-22:30 sat:02:00-sat:08:00 False ap-southeast-2a False gp2 E. Australia Standard Time

DBPARAMETERGROUPS prodetrmstacks-rds-paramgroups-allegroparams-1jbXXXXX5m in-sync

DBSUBNETGROUP Internal RDS Subnet prodetrmstacks-rds-subnetgroups-rdsinternal-1XXXXXXXXX05 Complete vpc-6XXXX05

SUBNETS subnet-dXXd Active

SUBNETAVAILABILITYZONE ap-southeast-2a

SUBNETS subnet-0XXXXXX55 Active

SUBNETAVAILABILITYZONE ap-southeast-2c

SUBNETS subnet-dXXXXXX9 Active

SUBNETAVAILABILITYZONE ap-southeast-2b

ENDPOINT allegrordsprod.cqmXXXXXX0f.ap-southeast-2.rds.amazonaws.com Z32TXXXXXXXS0V 1433

OPTIONGROUPMEMBERSHIPS sqlserverwithbackup in-sync

VPCSECURITYGROUPS active sg-1XXX3

aws rds describe-db-snapshots --max-items 5 --profile etrm

DBSNAPSHOTS 4000 ap-southeast-2c allegrordsprod arn:aws:rds:ap-southeast-2:32xxxxxxxxx4:snapshot:allegropreprodexport allegropreprodexport True sqlserver-ee 12.00.5546.0.v1 False2017-12-05T22:45:06.679Z arn:aws:kms:ap-southeast-2:325xxxxxx4:key/9bb411XXXXXXXXXXXXXXX15-95a1-9xxxxxac license-included administrator sqlserverwithbackup 100 1433 2018-03-06T09:31:08.027Z manual arn:aws:rds:ap-southeast-2:32xxxxxx4:snapshot:rds:allegrordsprod-2018-03-05-22-13 ap-southeast-2 available gp2 E. Australia Standard Time vpc-6XXXX

DBSNAPSHOTS 5120 ap-southeast-2c allegrordsprod arn:aws:rds:ap-southeast-2:32xxxxxx4:snapshot:allegroprodexport allegroprodexport False sqlserver-ee 12.00.5571.0.v1 False2017-12-05T22:45:06.679Z license-included administrator sqlserverwithbackup 100 1433 2018-09-09T22:33:04.270Z manual arn:aws:rds:ap-southeast-2:32xxxxxxx4:snapshot:rds:allegrordsprod-2018-09-08-22-13 ap-southeast-2 available gp2 E. Australia Standard Time vpc-6XXX

DBSNAPSHOTS 5120 ap-southeast-2b allegrordspreprod arn:aws:rds:ap-southeast-2:32xxxxxx4:snapshot:allegrordspreprod-2018-08-13 allegrordspreprod-2018-08-13 False sqlserver-ee 12.00.5571.0.v1 False 2018-08-07T01:22:50.677Z license-included administrator sqlserverwithbackup 100 1433 2018-08-13T07:35:26.984Z manual available gp2 E. Australia Standard Time vpc-6XXXXXXXX5

DBSNAPSHOTS 4500 ap-southeast-2a allegrordspreprod arn:aws:rds:ap-southeast-2:32xxxxxxx4:snapshot:allegrordspreprod-final-snapshot allegrordspreprod-final-snapshot Falsesqlserver-ee 12.00.5546.0.v1 False 2018-03-07T04:39:20.230Z 20000 license-included administrator sqlserverwithbackup 100 1433 2018-03-15T05:01:18.538Z manual available io1 E. Australia Standard Time vpc-61XXX5

DBSNAPSHOTS 5120 ap-southeast-2c allegrordsprod arn:aws:rds:ap-southeast-2:32xxxxxxx4:snapshot:allegrordsprod-manual allegrordsprod-manual False sqlserver-ee 12.00.5546.0.v1 False2017-12-05T22:45:06.679Z license-included administrator sqlserverwithbackup 100 1433 2018-06-13T05:31:03.012Z manual arn:aws:rds:ap-southeast-2:32xxxxxxxxx4:snapshot:rds:allegrordsprod-2018-06-12-22-13 ap-southeast-2 available gp2 E. Australia Standard Time vpc-6XXXXXX5

NEXTTOKEN eyJNYXJrxxxxxxxxxxxxxxxxx1bnQiOiA1fQ==

Welcome to the new world...

We are now becoming responsible for managing Oracle databases in the cloud – at this stage specifically RDS and EC2 in Amazon, but that may change to include MS Azure and Oracle.

When once migrating databases to the cloud was the exception, it is now more widely accepted and clients are moving forward with it.

Managing these databases is a change to the way that you are familiar with, so this post should assist with at least getting started. It does not go into details around how to implement the various options, but provides information on what is available and things to be aware of.

The 2 different types of database

With Amazon (AWS), there are 2 kinds of approach – RDS and EC2.

RDS is a database as a service, and is the main change to the way you know and operate.

EC2 will be more familiar. It’s basically a host in the cloud, very much like a VM, so think of it like that.

For this post, I’ll concentrate on RDS, since that is where the differences are. EC2 still has challenges, but they are mainly around security and connectivity, both of which are covered in the RDS information.

What is RDS?

RDS is a database that is provisioned in the AWS cloud. It can be SQL Server, Oracle, MySQL, PostgreSQL or others. Naturally this document addresses Oracle.

Once it’s provisioned, it can be connected to via an application, or SQLDeveloper. There is no host access. So this means you cannot run scripts, on-host export / import, move files etc.

Amazon have implemented some methods to enable some of the lost functionality.

How do I create an RDS instance?

Most of the time, you won’t need to. For the migrations we have performed, the client has already created them.

If you do need to, you need an Amazon account provided by the client, and given the permissions to create the instance. This is usually created / set up by the team that manages the AWS service for the client, so they should have all the required information.

Part of the account will be a role; there are usually different roles depending on the business unit, to allow for internal billing. So obviously you will need to be granted that role and create the instance in the correct one.

Other things to be aware of if creating an instance is the type – there are several options, including Enterprise Edition, Standard Edition, Bring Your Own Licence, as well as the size (CPU, Memory, Disk) depending on the usage etc.

This information will need to be provided by the client, as will information around standards such as naming conventions, security etc.

Navigating around AWS

There are 2 ways to manage the RDS databases – through the AWS console, and using SQLDeveloper.

Using the AWS console

Once the instance has been created and set up, there is usually no requirement to use the AWS console for database management apart from major maintenance (ie resizing, patching).

Assuming you have a login, you will need to switch to the correct role. This is at the top of the page towards the right.

If this is the first time you’ve logged in, the client will need to provide you with the role name and account, and grant you the correct access.

Once you have selected the correct role, you can click on the ‘DB Instances’ link to see the instances.

It will show the instances, name, types and status.

You can select one by clicking on the radio button, or on the link, which will open a new page.

If you click on the radio button, you can then use the ‘Instance Actions’ item to perform things like stop / restart etc.

When you click on the link, it will go to a new page with details of the instance.

Scrolling down the page you can see the summary, some charts on performance, security groups, a details section etc.

Most of the sections are self-explanatory; the ones I’ve mentioned below are note-worthy.

Cloudwatch

Performance information – to be honest, not much use, but be aware they are available.

Security Group Rules

You need to assign the correct Security Group to the instance – they determine the allowed connectivity.

For example, you will need a Security Group to have port 1521 defined to allow SQLNet connections. The client should provide the information.

Details

The important information here is probably the endpoint, which is the hostname for connections strings, and the backup and maintenance window.

CloudWatch Alarms

This shows what monitoring and alerting has been set up (more on this later).

Snapshots

These are effectively the backups. Note there are some differences around point in time recovery which will be covered later.

Monitoring and Alerting

AWS RDS instances come with a limited amount of monitoring and alerting – called CloudWatch. There is a chargeable option which provides ‘Enhanced Monitoring’, which just gives more specific information around the underlying OS metrics.

Since RDS is on the Amazon underlying OS platform, there is a good chance that the instance shares resources with other databases / applications. This obviously makes diagnosing issues that are OS related difficult.

Alerting is split into 2 categories – CloudWatch and Events.

At the time of writing, CloudWatch only provides 2 performance alarms for Oracle – high CPU and low disk space (even though it will let you configure others, they won’t work).

The Events alerting is for things like database restarts / outages, backups etc.

So if you want to be alerted for something, you need to set up both CloudWatch alarms and Event Subscriptions.

You can also use Oracle Enterprise Manager Cloud Control (V12 onwards) through the use of an Option Group – See below.

Alert and Listener Logs

In the main RDS instance screen there is a section called ‘Logs’

They are listed in alphabetical order, so the audit logs will always be shown – just type ‘Alert’ into the filter box and only these will be shown

You can click on these to see the contents.

However, you won’t be alerted if there are ORA- errors found. For this to occur, you need to publish the logs to the CloudWatch console, which is a separate step.

You need to click on ‘Modify’, then scroll down to the ‘Log exports’ section and check ‘Alert Log’ and ‘Listener Log’

Once these have been published, you need to access the CloudWatch Console (see below) and set up Metric Filters and alerts.

With the alert log, up to now I’ve not found a way to capture the actual message. I set up an alarm that triggers when it encounters “ORA-“, but all this does is set a counter to 1. I then set up a notification so that an email is sent when this occurs, but it only sends an email with the subject “mga-mgapoc2-alert-log-errors” – which means you need to log into the AWS Cloudwatch console and locate the message:

Instructions on how to get to this page are in another post.

CloudWatch Console

The CloudWatch console allows the creation of dashboards and is where you configure the alarms

https://console.aws.amazon.com/cloudwatch/home

This will take you to the webpage.

Creating dashboards is very straight forward, generally drag and drop.

AWS CLI

You can also implement AWS CLI on a Windows PC which enables you to run commands against an instance and potentially capture the output into files – this would allow for automation, string searches and the possibility of uploading the information into a repository. There is a post on how to install it and get started.

General Management

Option Groups

Since RDS does not allow the running of scripts, AWS has created Options.

You first create an Option Group – call it something meaningful – and then add options to it. You can then assign this option group to an RDS instance.

The options available include the OEM agent, Oracle APEX, Spatial. Statspack etc.

Some of these options, like the OEM agent, require information (OMS host name, ports in the case of the OEM agent).

Note that adding an option group to an instance usually requires an outage.

Parameter Groups

Parameter groups are similar to Option Groups – create one, add the required parameters and apply to the instance(s) – usually an outage is required.

Security Groups

The security group defines what ports are available for connections. Be careful about ensuring that you follow site standards because setting them incorrectly can leave the database exposed to the outside world. The information will be usually be provided by the client. You need to know if the default port 1521 is used, or if the site uses a different one. You will need to know to be able to connect.

Patching

Obviously with no access to the host, patching is not possible in the traditional way. AWS has enabled patching through the use of the Engine Version. When you create a database you can select from a list of versions:

Each of these versions relates to an Oracle PSU.

You can check which version is which PSU here:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.PatchComposition.html

You can see the version of the RDS from the instance summary screen

When a new PSU is released by AWS, it will be available in the instance ‘Modify’ screen. You should take the same precautions and process with implementing this as you would normally – backup, test.

Backups / DR

RMAN backups are not possible, so AWS takes snapshots of the database periodically (automatically), and maintains the archived logs itself – however, restoring to the latest data has limitations. There is a lag between the archived log uploads of 5 minutes. This means that if a database crashes at 10:04, you may only be able to restore to 10:00 – losing 4 minutes of data.

You can see the time that you can restore to in the console.

You can, however, restore to any point within the retention period.

So you can restore a snapshot and use the logs to roll forward.

RDS does not have traditional DR, but when you create a database you can specify ‘Multi AZ’ (Availability Zone). This automatically creates a replica and synchronizes data to another instance in a separate geographical location. In the event of an infrastructure failure the failover is automatic and transparent. The downside is that this costs the same as the primary instance so it may be cost-prohibitive.

rdsadmin.rdsadmin_util

The main way to perform database tasks traditionally done by connecting “/ as sysdba” is through the rdsadmin.rdsadmin_util package that is installed with RDS.

This page has the information:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.System.html

For example, you can’t grant privileges on a v$ view, you have to use this utility:

exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SERVICES','MGA_USER','SELECT');

Connecting to the database

Using SQLPlus

You can connect to the database with SQLPlus by installing the client on a PC and creating a tnsnames.ora. The connection information is in the AWS console in the details section and from when you created the database – or the information will be provided by the client.

If the port is not the default 1521, you should be able to find it defined in the Security Group.

In this example, the tnsnames.ora will look like this

MGAPOC2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = mgapoc2.xxxxxxxx.ap-southeast-2.rds.amazonaws.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = MGAPOC2)

)

You would connect using sqlplus mgadba@MGAPOC2 and enter the password – either used when you created the instance or provided by the client.

Using SQLDeveloper

I think SQLDeveloper is preferable to SQLPlus, and I would encourage people to start using it.

To connect, enter the details in the connection pane

The nice thing about SQLDeveloper is that it has a lot of useful features out of the box, like a live status display

You can right-click on a SQL Statement and select ‘Details’, and if the performance pack is licenced, show the explain plan and other details:

You can also write reports so you don’t have to re-write SQL all the time, and they can include charts and colours:

So that's a basic look at how to get started managing RDS databases in the AWS Cloud.

Yet Another Oracle DBA Blog

Sunday, September 9, 2018

Using AWS CLI

Managing Oracle Databases in AWS