Oracle quick and dirty

Recently, I had the opportunity to install a Oracle database on RHEL5.5. Not really an enlightenment when you come from a PostgreSQL/MySQL background.

Installation

I've followed the instruction from Oracle: http://download.oracle.com/docs/cd/B28359_01/install.111/b32286/toc.htm

A few things that are not mentioned in the prerequisites and may lead to problems:

1. make sure your hostname is correclty resolved, I got some errors during the installation when this was not the case
2. choose a memory usage that fits into your tmpfs and you have to have /dev/shm mounted
   
   sudo mount -t tmpfs shmfs -o size=300m /dev/shm
   
   otherwise you get an error when you want to start or connect to the database instance:
   
   SQL> ORA-00845: MEMORY_TARGET not supported on this system
   
   see http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/ for details.
3. Once your done an you've created a database, link the oratab file to /etc
   
   [oracle@columbus_rhel5 opt]$ ll /etc/oratab
   lrwxrwxrwx 1 root root 53 Feb 24 13:52 /etc/oratab -> /opt/oracle11g/product/11.2.0/dbhome_1/install/oratab
   
   This file will tell you your ORACLE_SID, by the way.

Starting a Database

First, you should consider reading through the quick administration guide: http://download.oracle.com/docs/cd/B28359_01/server.111/b28301/toc.htm

The easiest solution is to get the Oracle Enterprise Manager Database Control web-interface running and perform the administrative tasks from there. You can start the web-interface from the command line as the oracle user:

[oracle@columbus_rhel5 opt]$ ORACLE_SID=omero41 ORACLE_HOME=oracle11g/product/11.2.0/dbhome_1/ /opt/oracle11g/product/11.2.0/dbhome_1/bin/emctl start dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://localhost:5500/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ........ started.
------------------------------------------------------------------
Logs are generated in directory /opt/oracle11g/product/11.2.0/dbhome_1/localhost_omero41/sysman/log

As you see, you'll get the information under which port the web-interface is accessible. In case you loose this information and the web-interface is already running, you get it from the portlist.ini file:

[oracle@columbus_rhel5 dbhome_1]$ cat /opt/oracle11g/product/11.2.0/dbhome_1/install/portlist.ini
Enterprise Manager Console HTTP Port (omero41) = 5500
Enterprise Manager Agent Port (omero41) = 1830

Now point your browser to http://oracle_host:5500/em and login with sys as sysdba with the password you specified during the installation.

On the home page you can start the database and listener processes.

Creating and Dropping Databases

Databases are created with the dbca tool, which you launch from the command line, make sure you have an X-Server running on the local or remote machine. The rest is pretty much self-explanatory, just follow the steps:

[oracle@columbus_rhel5 dbhome_1]$  ORACLE_SID=omero41 ORACLE_HOME=oracle11g/product/11.2.0/dbhome_1/ /opt/oracle11g/product/11.2.0/dbhome_1/bin/dbca

As described below, in order to re-created the  database, the simplest solution is to drop all database objects, rather than deleting the database and create at new, as this is a time consuming process.

Creating and Dropping Users

In the database control web-interface you can use the Server link to create new user accounts. Roles and/or privileges need to be assigned to the new user in order to allow access to the database. The Resource role maintains the default privileges to create, update and select standard database objects. However, view and procedure privileges need to be assigned additionally.

Once you want to get rid of the database content, the easiest solution is to drop the users that owns the database objects.

Connecting to the Database using SQLPlus

Remember to put quotes arround the logon identifier, as the shell expansion might spoil the input. In addition the tralling slash on the ORACLE_HOME environment variable could lead to errors (http://forums.oracle.com/forums/thread.jspa?threadID=239281&tstart=0).

[hollaebn@columbus_rhel5]~% ORACLE_HOME=/opt/oracle11g/product/11.2.0/dbhome_1/ /opt/oracle11g/product/11.2.0/dbhome_1/bin/sqlplus "columbus/columbus@localhost/omero41"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 28 14:52:32 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL>