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.
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.
Tags
Tags are used to set standard notes – business unit, charge
codes etc.
Logs
Access to log files that are traditionally available on the
host. More on this later.
Recent Events
A log of major events like startup / shutdown, backups etc
Setting up alerts is straight-forward, but be aware that the
ARN is the subject of the alarm, so make sure it is created with that in mind –
it’s a good idea to have it something like ‘mga-instance-name-high-cpu-warning’.
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
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:
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:
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.
