Sunday, 10 February 2013

Amazon Relational Database Service


I finally found some time to check out Amazon Relational Database Service (Amazon RDS) for Oracle. Over all it was straight forwards, but I did run into a few issues. This post is a note on what problems I had and how I solved them, along with a brief "howto" for setting up Oracle on RDS.

Create your account
First of all you need to sign up for Amazon Web Services (Amazon AWS). That is a straight forward process that I won't comment any further.

Create your database instance
Having your account you should access the RDS panel and start setting up your own database instance.

With the wizard it's quite simple.

Pick the RDBMS of your choice. For my test case I went with Oracle Standard Edition One (se1).

Summary of preferences chosen. Only pick BYOL if you have an Oracle licence already.

Enable connection
For you to be able to connect to the database you have to enable connection from your IP or your IP range. This is done under "DB Security Group" in the RDS control panel.

Add the IP range of your choice.

Note: If you forget this step you will get a time-out trying to connect to your database.

SQL Developer: IO Error: The Network Adapter could not establish the connection.

SQL Developer
A great tool to manage an Oracle database is Oracle's own SQL Developer, which should be quite simple to set up. However, I did run into some issues here due to being on a 64-bit Windows 7 system (due to not reading the documentation well enough). I did get error messages like "unable to launch the java virtual machine" and "msvcr100.dll is missing from your computer". This was due to me having installed a 32-bit JDK (first error message) and using version 7 in stead of 6 of Java (latter error message). At the time of writing SQL Developer only supports Java versions below 7. When I removed the problem versions of Java and installed it all over again, it worked just fine.

With the x64 / 64bit JDK (SDK) version 6 update 39 it worked just fine.

Note: If you get the error message "cannot find j2se skd installed" when starting SQL Developer it means you have to download the JDK and install it.

Starting SQL Developer also gave the error code "_execv() failed, err=2". This I simply ignored, as it didn't seem to matter. After a restart of the machine the error message also disappeared.

Connect
After 10-20 minutes your database instance should be ready, and you can connect to it.

Status: available.

Click the database instance in the control panel to view more details.

DB instance details.

Here you'll find the details needed for setting up a connection in SQL Developer. Note that endpoint is the same as hostname and that DB Name equals SID. If you're using a wrong value for SID you'll get the error message "ORA-12505, TNS:listener does not currently know of SID given in connect descriptor".

SQL Developer connection settings.

Test the connection. If you've done everything as described above it should read "success" in the down left corner of the connection settings window.

Create a new database user
You should of course not use your master user for developing. Hence you have to set up a new database user. If you know databases this should be straight forward.

Note: RDS is using Oracle Managed Files, hence no need for creating and administrating tablespaces, log files and control files.

Example of SQL needed for setting up a new user (done as master user), with some standard access rights:

CREATE USER SLEEPTRACKER
    IDENTIFIED BY "thisispassword";

GRANT CONNECT TO SLEEPTRACKER;

GRANT RESOURCE TO SLEEPTRACKER;

GRANT CREATE DATABASE LINK TO SLEEPTRACKER;

GRANT CREATE VIEW TO SLEEPTRACKER;

Note: If you have granted "resource" to your newly created user, but still get "ORA-01031: insufficient privileges" (when for example trying to create a table) it might be because you logged in with that user before the grants were executed. Try logging in and out again.

Now you can create a new connection in SQL Developer for your new user, log in and start developing.


select * from hello_world;

2 comments:

  1. Hi. I wanna know if you can enable/disable archive logs?, can you move the location where you write them?, and infrastructure...can i modify the datafiles structure? can i divide index and data datafiles, or just put them in a unique location?

    Thanks for your answers.

    ReplyDelete
    Replies
    1. Hi, Germán. I have not looked into that, but found a partial answer here:
      https://forums.aws.amazon.com/message.jspa?messageID=390178

      "Since archived redo logs are needed for any instances with backups enabled, you must disable backups by setting backup retention to zero in order to disable archiving in an Oracle instance."

      Delete

Allowed HTML tags:
<b>bold</b>
<strong>strong</strong>
<i>italics</i>
<em>emphasis</em>
<a href="">hyperlink</a>


Please, show the courtesy of identifying yourself when adding a comment. Anonymous comments will, most likely, be removed.