Valery's Mlog

Mindlog of a Freak
July 9th, 2006 by Valery Dachev

Oracle Database 10g Express Edition in Linux

I’m going to skip the introduction into who Oracle are and what are they’re in my blog. It happens so that I had a seriously struggle with their products during the week. One of these products is Oracle Database 10g Express Edition. The label “Express Edition” stands for a group of products aimed at development and educational work. This package includes a fully functional database. Because of the goals of this edition the software has the following limitations (there’s a good article about that on this address):

  • the server cannot address more than 1Gb of memory;
  • the server can work on a single CPU only;
  • the server runs at most one database per computer (not schema !);
  • the server is limited to 4Gb disk usage;

In a matter of fact, these limitations are not that fatal in the most of the cases.. especially when talking about low-load web applications. These are some of the reasons I had to work with that thing despite my antipathy towards Oracle in general. So, that’s it with the usual bla-bla.

I’m going on briefly and step-by-step just like in my notes on the installation:

Downloading the Packages

Note for Debian on amd64: Oracle release their Express Edition database for 32-bit architecture only. That’s why apt-get can’t handle the cases when amd64 is the primary architecture of the operating system. Because of this the download and the installation of the packages should be done manually. Furthermore, the 32-bit glibc6 libraries (the libc-i386 package) should be downloaded, as 32-bit application apparently cannot link to (these?) 64-bit libraries and vice versa.

Note for RPM-based distributions: I suppose libaio might need to be installed but I don’t have such a system on test that on. I would be glad if someone who has “suffered” that shares their experience.

  • for DEB-based distibutions w/ apt-get:
    • the following line should be added to /etc/apt/sources.list:

      deb http://oss.oracle.com/debian/ unstable main non-free

    • the following command should be executed:

      apt-get -d libaio oracle-xe

  • for DEB-based distributions w/o apt-get:
    • the libaio package should be download from here;
    • the oracle-xe package should be downloaded from there;
  • for RPM-based distributions:
    • the oracle-xe rpm-package should be downloaded from this addres;

Server Installation

  • for DEB-based distributions w/ apt-get:

    apt-get install oracle-xe

  • for DEB-based distributions w/o apt-get:

    dpkg -i libaio_0.3.104-1_i386.deb
    dpkg -i oracle-xe_10.2.0.1-1.1_i386.deb

  • for DEB-based distributions on AMD64:

    apt-get install libc6-i386
    dpkg -i –force-architecture libaio_0.3.104-1_i386.deb
    dpkg -i –force-architecture oracle-xe_10.2.0.1-1.1_i386.deb

  • for RPM-based distributions:

    rpm -ivh oracle-xe-10.2.0.1-1.0.i386.rpm

NOTE: Ilias brought to my attention in the comments below that a problem exists with the listener unable to run on some distributions (RHEL 4, Ubuntu AMD64 and probably more) due to wrong permissions of the /var/tmp/.oracle (and/or /tmp/oracle) directory. You can take a look at this address for more information. There’re two ways to workaround this problem:

  1. change the user and group of the directory to “oracle” and “dba” respectively:

    chown oracle:dba /var/tmp/.oracle /tmp/.oracle

  2. leave the directory owned by root but change its permissions to 1777 (as it is in Debian):

    chmod 1777 /var/tmp/.oracle /tmp/.oracle

You’ll also probably have to restart the listener:

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/lsnrctl stop
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/lsnrctl start

Initial Server Configuration

Configuration is now independent of the distribution. Before finishing this operation the server will not be functional . When it starts, it will open two TCP ports – one for the extremely pleasant web administration (Application Express) and another one for communication with the server itself (Database Listener). It’s worth mentioning that, although Database Listener listens on all IP addresses, Application Express binds to 127.0.0.1 only, which makes it a bit unhandy if the database is installed on a remote machine. I found out this can be changed on a later stage using the web administration itself but, until then, I installed a small proggy called “redir” (the Debian package has the same name) which I used to redirect all requests to port 8080 of my public IP to 127.0.0.1. After doing its job it can be easily stopped in order to keep the system secure.

  1. the following command is executed:

    /etc/init.d/oracle-xe configure

  2. a port for Application Express should be selected (it’s 8080 usually);
  3. a port for Database Listener should be selected (by default 1521);
  4. a password for the system users should be picked;
  5. follows an answer to the question wheter or not the database server should be started on boot;
  6. at this point the server is up-and-ready;
  7. it’s a good idea to have the needed environment variables available to Oracle clients. A good choice is to write them down in /etc/profile:

    export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

    or you can simply add the following line:

    . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

  8. it’s also a good idea to put the path to the Oracle libraries in /etc/ld.so.conf in case additional software links to them:

    echo ‘/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib’ >> /etc/ld.so.conf
    /sbin/ldconfig

Administration

Once installed the server can be managed with a number of tools:

  • with the cute Application Express which is by default available on the following address:

    http://127.0.0.1/apex/

    Remote access can be enabled with entering the SQL*Plus console as sysdba and typing:

    EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

  • with the standard console tool SQL*Plus, which is usually located at:

    /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/sqlplus

  • with the graphical tool Oracle SQL Developer;

NOTE: The SID of the installed database is “XE” by default.

And there was happiness!.. unless you’re about to install the database client afterwards….

P.S.: Everybody should feel free to correct any of the mistakes I definitely have… :)

Comments

18 Responses to “Oracle Database 10g Express Edition in Linux”
  1. Thanks, I was able to install oracle xe on an Ubuntu AMD64 system.

  2. I had some trouble with the listener (it would not start) but:

    chown oracle /var/tmp/.oracle

    fixed it. See:

    http://forums.oracle.com/forums/message.jspa?messageID=1308427

  3. Ilias: I’ve made the appropriate changes to the post. Thanks for paying me attention on that. :)

  4. ** the server is limited to 4Gb disk usage

    does this include the metadata files and others files like web pages or just schemas files? does it counts all objects from all schemas created till fill up 4 GB or it is 4 GB per schema?

  5. Oracle all new for me, learning now and coming from MS SQL

  6. @Celio: I’m far from an Oracle expert and couldn’t answer your question. So I had to dig into the documentation. It says this is total user(/non-system) data limit of the database. Unfortunately, I couldn’t find any information about what “system” or “non-system” means. I made some experiments:

    • I listed all database users with their respective default tablespaces:
      SELECT “USERNAME”,”DEFAULT_TABLESPACE” FROM dba_users;
      The default HR user uses the “USERS” tablespace.
    • I listed all datafiles with their respecive tablespaces:
      SELECT “FILE_NAME”,”TABLESPACE_NAME”,”BYTES”,”MAXBYTES” FROM dba_data_files;
      The USERS tablespace was created with 1Gb size and maximum size ot 5Gb.
    • I tried resizing the datafile to something larger:
      ALTER DATABASE DATAFILE ‘/usr/lib/oracle/xe/oradata/XE/users.dbf’ RESIZE 5000M;
      and I got an error message saying: “ORA-12952: The request exceeds the maximum allowed database size of 4 GB“. However, I was allowed to resize it to 4000M.
    • Ok. Let’s say my users.dbf is 1Gb. I tried creating new datafile for this tablespace:
      ALTER TABLESPACE USERS ADD DATAFILE ‘/usr/lib/oracle/xe/oradata/XE/users01.dbf’ SIZE 4000M AUTOEXTEND OFF;
      Same error (4+1 = 5). I was able to create a smaller than 3000M datafile. Dropped it.
    • I tried creating a new tablespace:
      CREATE TABLESPACE USERS2 DATAFILE ‘/usr/lib/oracle/xe/oradata/XE/users2.dbf’ SIZE 1000M AUTOEXTEND OFF;
      Successfully. I tried creating another one with 3000M size. Error (1+1+3 = 5).

    I came to a conclusion it calculates the total size of user datafiles. However, I still don’t have an idea what is the mechanism of distinguishing system and non-system tablespaces as I can easily change any users’s default tablespace to SYSTEM, for example (something you shouldn’t do generally)… I suppose you have to stick to the assumption above or do some further experiments if you want to cheat the database server. In the latter case, I would appreciate if you share your experience here.

    Thank you !

  7. Thank you very much! I will do some tests later at work!

  8. hi
    i have succesfully installed oracle 10g on FC5.
    i want to know that how can i set database path to a remote disk.

  9. @Vikas: Generally to access a disk on a remote machine, there should be some kind of protocol for communication. Unfortunately I’m not aware if Oracle internally supports any of the available protocols. If you don’t know that (just like me) and need a quick solution, create NFS or SMB read/write shares (depending on your remote system), mount them and set the local path to the data files accordingly.

  10. Great article, but what about installing sql developer under amd64? Any clues?

  11. @Yannis T: Honestly I haven’t tried that. As far as I can see in the Oracle SQL Developer Downloads page, you can download “Oracle SQL Developer RPM for Linux”. There’re two things to note here:

    • The package is marked as “noarch” so it means it runs on any platform. And this is not unusual – it’s only the Java virual machine it depends on. The binary itself is platform independant.
    • It’s an RPM package so the installation is kinda trivial on Red Hat based distributions. In order to install it on a Debian based box you would probably like to use the “alien” package (available for both Debian and Ubuntu). You can find more information about alien’s usage in its manpage.

    Have fun! :)

  12. N. Johnson says

    Hello,
    I am using oracle 10g linux version in red hat linux for a project. It has a default SID which i named “trans1” during the installation of oracle. It is working fine. Now, I want to create new SID for another big project. How can I create,run and access in enterprise manager? Pliz help!

  13. J.Batista says

    Running apt-get update, I got into the following error message:
    [snippet]
    Reading package list… Done
    W: GPG error: http://oss.oracle.com unstable Release: The following signatures could not be verified because the public key is not available: NO_PUBKEY 2E2BCDBCB38A8516
    W: You will have to run apt-get update to correct these problems
    [/snippet]

    Besides a workaround such as

    gpg --keyserver wwwkeys.eu.pgp.net --recv-keys 2E2BCDBCB38A8516
    apt-key add /root/.gnupg/pubring.gpg
    

    is there any “official” solution to this?

  14. Murali N says

    Hi

    INSERT INTO gtc_xm_heading_translations VALUES(‘anx1_title’,’ПРИЛОЖЕНИЕ I’,’Bulgarian’,’T’)

    When i am trying to insert these values into table it’s showing language value like ‘??????????’ instead of ПРИЛОЖЕНИЕ..For all Bulgarian languages its showing values like this.Plz provide solution for this…

  15. […] Oracle 10G Express Edition on Linux […]

  16. It helps a lot for me to install oracle 10G express on ubuntu.

  17. […] found how to run Oracle 10g Express Edition on a 64-bit platform. It’s, er, remarkably straightforward. I expected to jump through more […]

  18. […] found how to run Oracle 10g Express Edition on a 64-bit platform. It’s, er, remarkably straightforward. I expected to jump through more […]

Leave a Reply

%d bloggers like this: