Update 23-Feb-2012: I have removed this image because (1) it’s outdated and based on 11g XE beta while released version is there and (2) licensing agreement doesn’t permit redistribution of software.
That’s right folks! Playing with latest beta of free Oracle Database 11g Express Edition couldn’t be any easier than that. If you are using Amazon EC2, you can have a fully working image with 64 bit Oracle Linux and Oracle 11g XE database running in a matter of few clicks and a minute to get the instance to boot.
Image — ami-ae37c8c7
Name — pythian-oel-5.6-64bit-Oracle11gXE-beta-v4
Source — 040959880140/pythian-oel-5.6-64bit-Oracle11gXE-beta-v4
You can find it in public images and at this point it’s only in US East region.
If you never used Amazon EC2 before, see detailed step-by-step guide on how to get started with EC2 on the example of this 11g XE image.
This image works great with Amazon EC2 Micro instance and I configured it specifically for Micro instance. Micro instance costs you only 2 cents per hour to run or even less than 1 cent if you are using spot instance requests (and there is free offer for new AWS users as Niall mentioned in the comments).
So what’s there?
- Oracle Enterprise Linux 5.6 64 bit (I started with 5.5 and updated to the latest)
- Oracle Database 11g XE Beta (oracle-xe-11.2.0-0.5.x86_64)
- Database created and configured to start on boot
- APEX coming with 11g XE configured on port 8080 and remote access enabled
- 10GB root volume on EBS with 5+GB free for user data. You could store up to 11GB of data in 11g XE and there is a way to grow volumes if you need but for more critical use then playground, I’d allocate separate EBS volumes anyway.
Few things worth to mention:
- I enabled public key authentication (“PubkeyAuthentication yes” in /etc/ssh/sshd_config) so you can setup shared key to login directly as oracle OS user – just copy your public key to /home/oracle/.ssh/authorized_keys.
- SYS and SYSTEM password is “pythian”. Change it!
- ADMIN password in APEX is “pythian” — change it on the first login.
- Micro instance has 613 MB of RAM and no swap — no instance (ephemeral) storage.
- Oracle database and listener autostarts on boot. You can use /etc/init.d/oracle-xe stop/start as root too.
- listener.ora has been modified to include (HOST=) so that it starts on any hosname/IP.
- APEX remote access is enabled! DBMS_XDB.SETLISTENERLOCALACCESS(FALSE)
- Ports 1521 and 8080 are open to the world on local iptables firewall. You still need to configure proper Security Group to be able to access those ports.
- Access APEX on https://{public-ec2-ip}:8080/apex and admin on https://{public-ec2-ip}:8080/apex/apex_admin.
There is currently an issue that APEX stops working after few minutes of run-time returning 404 code. Might be a bug in beta or installation issue (for example, I run it with no swap on Micro instance).
I will be keeping the AMI up to date as things develop so AMI id could change — check back here of just search public AMIs for the latest image.
If you don’t know how to use Amazon EC2 – I recommend to read the second chapter of Expert Oracle Practices: Oracle Database Administration from the Oak Table. This chapter was written by Jeremiah Wilton who’s been long time playing with Amazon EC2 for Oracle before any of us even thought of it.
When few folks confirm that it works, I’ll submit an image vi https://aws.amazon.com/amis/submit.
Update 4-Apr-2011: Create v3 image – fixed typo in database passwords, fixed retrieval of public key for ssh login as root, changed startup sequence so that ssh keys are initialized earlier as well public key retrieval.
Update 4-May-2011: Created v4 image – Increased SGA size to 212M. Set large_pool to 32M (Automatic SGA management doesn’t do it’s job properly – this is why APEX was not working – not enough large pool memory allocated). Enabled DIRECT IO and ASYNC IO for filesystem – buffered IO slowed down things a lot. Now APEX is actually pretty usable on Micro instance. Remember that you can run it on large instance to run in comfort but you are overpaying since there is 2 CPUs in large instance and 7.5GB of RAM while you can’t use more than 1GB. Of course, you could disable Direct IO and use OS buffering to take advantage of more RAM but can’t leverage both cores with APEX (it limits capacity to a single core).
Update 23-Jul-2011: If you need to use networking services from APEX (like web-service, sending emails and etc) then you need to configure network ACLs for APEX_040000 user.
30 Comments. Leave new
Nice! Note that you can also use EC2 micro instance for free for a year https://aws.amazon.com/free/
That’s true! Thanks for mentioning it Tanel.
Sadly though the free for a year offer is only available to new subscribers.
[…] just published Oracle Database 11g Express Edition Amazon EC2 image (AMI) but most of you have never used Amazon EC2… Not until now! This is a guide to walk you […]
But Niall, it only costs you 2 cents per hour (so it’s like $14 per month if you want to run it 24×7). If you use spot instances, it’s less than 1 cent per hour actually.
Very nice, thanks!
I actually tested 11g XE on one of the Amazon 64bits AMI.
After configuring APEX however, I had intermittent network glitches and some 404’s on apex requests.
However, I never saw %st over 20% during these moments, the only weirdness was an increase in CLOSE_WAIT tcp connections that didn’t free up for several minutes.
I have not investigated further, but I’m not yet convinced that 11g XE (and apex) on a micro instance is stable enough.
Alexis
You might be right Alexis (I did mention those 404 as well). I’d love to get it troubleshooted. I will try to reproduce on a bigger instance.
Live and learn… The original image didn’t work with public key distribution so you couldn’t really login via SSH. Also SYS/SYSTEM user password had typos. And few small things as good principles for sharing AMIs. So I posted v3 and the main post content has been updated to reflect it.
Hi,
excellent idea Alex.
Concerning the HTTP 404 errors, this is an old acquaintance, old XE running on a micro instance is very prone to this kind of problems and although i have got no definitive evidence of it, my gut feeling is that it has to do with the number of dispatchers and the number of connections they can handle.
On EC2 small instances the old XE does slightly better than on micro instances, glitches are less frequent, but they are still happening from time to time. I almost got rid of them when i installed Apache as proxy for caching images, CSS and javascript files, so that Oracle EPG doesn’t have to worry about a large number of concurrent connection requests.
Since then i experienced almost no HTTP 404, so i bet that the new XE is still affected by the same problem.
Another issue with EPG is the dispatcher process that goes wild (again i think it has to do with the number of concurrent connections it can realistically handle), but in that case i found a sort of workaround by running three dispatchers and getting notified by AWS when the CPU is running above 99% for 15 straight minutes, thereafter i log on and kill the dispatcher process (this problem occurs typically every 2-3 days and less frequently a couple of times per day).
Unfortunately I like best to develop apps than to administer databases, so when i am confronted with problems involving the inner workings of Oracle beyond the v$ views level, i have to give up, but it would be great if you managed to find out what’s the problem with EPG *exactly* :-)
In any case i’d like to hear what you think on these issues.
Cheers
Flavio
[…] tre anni sono molti. Solo pochi giorni fa Alex Gorbachev ha pubblicato su pythian questo post in cui spiega come accendere in pochi minuti una macchina virtuale sui servizi di amazon (per i […]
Hi Alex,
Per your comment of:
“There is currently an issue that APEX stops working after few minutes of run-time returning 404 code. Might be a bug in beta or installation issue (for example, I run it with no swap on Micro instance).”
can you please see if this generated any entries in your database alert log further describing this error? I’m not aware of any issue in APEX which would cause this, nor am I aware of any bug/issue in the XE Beta database that would result in this. I have not seen this reported on the OTN forum by other users of the XE Beta.
Thanks.
Joel
After follow up with Joel, I have created new AMI (v4) – changed memory setting and IO options. Post is updated.
APEX now works flawlessly with Micro edition. Please note that performance might still drop to a crawl because Micro instance doesn’t guarantee any CPU capacity for you – it’s anywhere between 0 and 2 CUs (Compute Units) but you get what you pay for.
You can run it with large instance (and you should change memory settings then) but you are overpaying for it because of XE limitation on what it can use (CPU and memory). Since 11gXE Beta runs only on 64 bit Linux (unless you want to do Windows), you can’t use EC2 small and medium instances – they are 32 bit only.
Hi I tried your ami and it works fine. Could you tell me what is the default workspace for apex?
Michael, there are no workspaces created by default. There is only INTERNAL workspace which is basically the administration interface. You should login in there first as “admin” user and create workspaces, users, schemas and etc.
Thanks for the tip Alex. I also need to grant some access to sys tables for example:
grant select on dba_2pc_pending but I can’t do it with ‘system’ user. I need to run it as ‘sys’. I tried login as sys with ‘pythian’ but it doesn’t work. Is the password for sys different?
also what is the password for sys account?
Michael, the passwords are documented in the post — they are also displayed when you login as oracle Linux user (edit .bash_profile to remove that).
When logging in as SYS user, you have to use additional qualifier AS SYSDBA.
RTFM these items to start with:
https://download.oracle.com/docs/cd/E17781_01/server.112/e18804/users_secure.htm#ADMQS215
https://download.oracle.com/docs/cd/E17781_01/server.112/e18804/users_secure.htm#CHDJIEBA
Thank you Alex, that was indeed my problem. Everything work well now. Thanks again for your work on this AMI. Cheers!
Hi Alex,
Wonderful job. Working great so far. Only thing: Having added port 22 to security ground, I am now trying to gain access to ec2 instance using ssh (and my private key). Can’t get thru. Do you have ssh setup using port 22? I can’t find mention of ssh port in the article. Makes me think I am missing something?
Absolutely – ssh is configured and your selected public key should be uploaded in the appropriate location during the first start up of the instance. Have you seen instructions on how to get started? https://www.pythian.com/news/22045/how-to-get-started-with-amazon-ec2-oracle-11g-xe-example/
I am in! Alex, thank you so much for how-to-get-started – helped me narrow down what I’d missed. Wonderful work indeed!
I was using this image on Micro EC2 instance very successfully and it works quite well so it seems there is enough memory allocated now. It might get slow on Micro at times but you get what you pay for (no guaranteed CPU cycles basically).
One problem I hit recently was trying to use web-services from APEX (while writing an S3 client – very cool) – “ORA-20001: The webservice was unreachable because either the URL you supplied was invalid, your environment requires a valid proxy server address for HTTP requests, or a wallet needs to be configured for HTTPS requests”.
After checking all the network connectivity (outgoing messages are permitted and are working), I mistakenly started digging into missing wallet and that was a big mistake — still not sure what’s needed there. However, the moment I stumbled upon mentioning of network ACLs, I immediately recalled that in Oracle database 11g, all TCP network communication are prohibited by default and must be explicitly enabled. Usually, it’s done after APEX install for the APEX_040000 user but I thought to keep image more secure and didn’t do it back then. So…
If you need to use UTL_HTTP or web-services or whatever out network connections from APEX — follow these instructions. Basically you need to run this as SYS:
[sql]DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
— Look for the ACL currently assigned to ‘*’ and give APEX_040000
— the "connect" privilege if APEX_040000 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = ‘*’ AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
— Before checking the privilege, ensure that the ACL is valid
— (for example, does not contain stale references to dropped users).
— If it does, the following exception will be raised:
—
— ORA-44416: Invalid ACL: Unresolved principal ‘APEX_040000’
— ORA-06512: at "XDB.DBMS_XDBZ", line …
—
SELECT SYS_OP_R2O(extractValue(P.RES, ‘/Resource/XMLRef’)) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, ‘/Resource/XMLRef’) = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;
DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, ‘APEX_040000’,
‘connect’) IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
‘APEX_040000’, TRUE, ‘connect’);
END IF;
EXCEPTION
— When no ACL has been assigned to ‘*’.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(‘power_users.xml’,
‘ACL that lets power users to connect to everywhere’,
‘APEX_040000’, TRUE, ‘connect’);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘power_users.xml’,’*’);
END;
/
COMMIT;
[/sql]
I am unable to locate this AMI when attempting to launch a new instance. Is it still available?
Hi Alex,
This is excellent! Is it possible for you to make it available in the EU-region as well?
I’m sure there are more than me interested in the ami on this side of the pond :)
Regards,
Håvard
Hi, Alex.
Extensive search through “Community AMIs” in the US East (Virginia) region does not reveal your instance.
Should I be looking somewhere else?
Regards,
Gus
Hm… received recently a warning that the AMI contained an SSH public key and the image was made non-public. This is odd as I scanned it all and couldn’t see any of that.
I’ve made it public for now – let’s see if I find something else soon but in the meantime it should work.
On the other hand, I should actually now rebuild this image using non-beta XE.
How much does an instance of Oracle cost to run on Amazon EC2?…
It depends how you want to run the eve instance and how your licensing agreements with oracle are if youbare an enthusiast and want to run oracle for testing there are AMIs available to use including AMis with Linux and oracle 11.2 xe Details are this …
Wonderful. Thanks. Let us all try to share like this.
Hi Alex …
It’s my first time using APEX in AWS .. I found that the APEX version in the AMI is 4.0.2.00.08 .. do you have any instructions to install APEX 4.1 in the AMI?
11g XE version itself is outdated (beta) and shouldn’t be used anymore. Unfortunately, I have to recall it to not get into legal issues.