Adding more on 8-Sept-2016:
Adding more on 2-Sept-2016:
Time to throw a few notes about Oracle database on Amazon RDS.
- It works
- Issues getting access to data files: file system access is generally forbidden on RDS for reasons understandable. However, imagine you ran into RDS limits. For example, you need a one-off patch (which RDS would not allow you to install). You need to escape RDS quickly into a normal Oracle database. You can exp/edpdp/imp/impdp/GoldenGate/Amazon Migration Service/SQL interface. But SQL interface is sloooow. Here you are – caught by RDS for good.
NOTE: colleague of mine claims Amazon already has native MS SQL Server backup into S3. So far it seems to be a full db backup with no control to have incremental or transaction log backups. However, if one day Amazon implements Oracle native RMAN backups into S3 then a rolling restore becomes an option – that would open an RDS esacape window.
NOTE2: Amazon seems to be supporting MySQL and Maria RDS binary log based replication into external database: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html
I can confirm that Aurora RDS (Aurora is about MySQL) successfully replicates into another Aurora RDS in another region. This Pythian link was instrumental to get it working, however – Amazon document has a nasty bug:
- Once RDS starts doing something (backuping/creating/patching/restoring/etc) you have very little idea how long it is going to take. Example, restoring Aurora database from a snapshot – you get status “creating”, you get button logs, but there are no logs yet.
- Access to Oracle redo log files. Take a look here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.Oracle.html
Amazon document writers do not even get that Oracle redo logs are light yeas away from Oracle trace and audit logs.
Log miner: can do it. Log retention can be set too: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html#Appendix.Oracle.CommonDBATasks.RetainRedoLogs
- Character sets. Amazon does support a number of them but a few older ones are not included.
- Issues setting hidden parameters (I failed) – note, some hidden parameters have been exposed by Amazon (i.e. can be changed).
- Can’t change some parameters like service name. I failed to add service name. Want to rename database – it’s copy&paste (i.e. back database and restore as a different thing – do this on a few terabytes to get taste of it). Want to add service name for application purposes (resource management, end-to-end user tracing) – no no.
- Tablespace growth control. So far I recommend using bigfile tablespaces. Then you can execute “alter tablespace … autoextend on/off maxsize …” If you use small file tablespace then “alter database” raises privileges error immediately. Btw, Amazon default Oracle RDS is keen to let you grow (and pay) – it has the maximum limit on bigfile tablespace, SYSTEM tablespace including. Let it be with payments but letting database grow out of control will end badly for your database (and you personally).
- One-off patches are no no (as was mentioned already). I do not know a good RDS escape strategy. RDS is a trap. I personally would like to hear if Amazon allowed a single RDS customer to get hands on RDS datafiles and migrate for good whererver he or she wished – that would be nice of Amazon.
- Database Flashback – I failed to get it running. I assume it can’t.
- Some performance questions linger regarding High Availability and Disaster Recovery implementation – the Multi Availability Zones option. It’s an open secret what technology Amazon is using (not a Data Guard, which is stated openly). Then it is File/Storage block replication vs redo log replication. I do assume that redo log [vectors] replication does require way less bandwidth and has a better latency.
- MultiAZ RDS failover happens within minutes (less than 5 minutes).
- RMAN backups. What RMAN backups ? RMAN restore of a particular failed block, data file, tablespace, ctonrol file, log file – no no. Amazon RDS restore granularity is the whole database or nothing. How long will the restore take: very little idea. However, you can read logs.
- Amazon RDS point in time restore is of 5 minutes granularity, mine and other people tests seem to confirm this number
- Can you freeze RDS database backup so that it does not expire at the end of backup retention period ? Seems like not: you can freeze database snapshot, true. But I did not find how to freeze backups of database archive logs. Here is what Amazon says: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html
“You can restore to any point in time during your backup retention period”
If this is truly so then it is a serious limitation for some testing scenarious.
- Take a look into v$datafile. I get it that Amazon attaches a single EBS volume for your datafiles. A single EBS volume has it’s limits of IOPS. On Amazon you combine many EBS volumes to get IOPS you need. Throughput (bandwidth in Amazon terms as EBS is attached to network interfaces) is another concern – it’s limited to Amazon instance network card, naturally. Forget about gigabytes per second. About 700MB/sec EBS throughput is maximum what I was told regarding normal Oracle installation on one of high end Amazon instances. RDS maximum is even less.
- Upgrades. I have upgraded 220.127.116.11 SE1 into 18.104.22.168 SE2. It took about an hour on 2 core (not damned vCPU but CORE!) server.
Amazon did a few backups underway – even incremental backups do take time. TBD: perhaps those backups involve only archive log backups?
The fact you have no control over backups kicking in is unnerving.
One note: When doing upgrade on a rather small instance db.m3.large the upgrade took a staggering 4 and a half hours. Note, that was a skeleton test database. “one off pacthes and performance improvements” phase kept improving the performance for exactly 3.5 hours. Perhaps it was caused by instance having just one core.
- Now, the paranoid part. If anything happens with the database then you have no access to the RDS host operating system. You are at Amazon mercy.
Ok, enough for the Bad and Ugly. What about the Good. Provisioning is reasonably fast. Not light fast but still fast. Backups and restores do work. Failover does work. Upgrade – the one I have done (see above) – was successful. Some basic monitoring is included. Overall, Oracle RDS is a very reasonable offer for application that can get away with what was said above. 24×7 aspect is the most troublesome thing for me: as I has already said, if anything non-trivial happens with your database then RDS is a black box.