Manually start a Oracle 11G instance

I just installed a Oracle 11G database on a Ubuntu Linux server. Oracle 11G is not certified to run on Ubuntu however it can be done without any problem. Because it is not certified by Oracle you will never get support from Oracle and I have to advice you to never run a production environment with it. To have it running as a development environment on your laptop is fine, this is the reason I am running it at the moment, I now have the possibility to develop PL/SQL code wherever I go on my laptop.

Installing a Oracle 11G database on Ubuntu is no rocked science, however, a guide can come in handy. So I have to praise Augusto Bott from the Pythian Group who wrote a great paper on installing Oracle 11G databases on Ubuntu. When you start installing please do refer to this paper and follow the steps and you will have a running database very quickly.

Almost at the end of the Paper Augusto states the following "Just remember: this script depends on the existence of a database on the machine, so don’t forget to run dbca as the oracle user, and configure it to start at boot time (edit /etc/oratab after creating the database, and set the last column to Y)." This is very true, however it might be fun to NOT do this and to learn some about how you can mount and start your database manually.

I installed my Oracle 11G database and created a database named JLO11G so if we look at the file /etc/oratab we can see the last line is stating my database name and the home of the JLO11G database. Also you can see that it states N which means that the database should not be started at boot time.

JLO11G:/u01/app/oracle/product/11.1.0/db_1:N

Now we have to start, mount and make the database available by hand which is normally done by the system when you set the last column to Y. First of all we have to set the ORACLE_SID as the user running the database, in my case this is the user oracle at my linux box named orcl11gdb. To do so we enter the following command:

oracle@orcl11gdb:~$
oracle@orcl11gdb:~$ export ORACLE_SID=JLO11G

Now we have set the ORACLE_SID, a ORACLE_SID is the Oracle System ID which is used to identify a particular database in the system. Under UNIX/Linux and windows it is called a ORACLE_SID and at VMS systems it is called a ORA_SID. As we are talking about a Linux system we talk about a ORACLE_SID. In case you installed your database a long time ago and like to start your project again but you are failing to remember you ORACLE_SID you can look in your /etc/oratab file where you can find it. As we have set the ORACLE_SID and we are logged in as the user who runs the database software we can now begin starting the database with sqlplus. We will login as a sysdba because this is providing us the correct rights to mount and start a database.

oracle@orcl11gdb:~$
oracle@orcl11gdb:~$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 18 12:23:32 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL>

We are now connected to a idle instance so we need to start the instance, to do so we will first start it without mounting the database.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 849530880 bytes
Fixed Size 1303216 bytes
Variable Size 377490768 bytes
Database Buffers 465567744 bytes
Redo Buffers 5169152 bytes
SQL>


Now we have a running instance and we can even query some information from the instance, we can for example query the status of the instance. In case you do not know what the status of a instance is this can be very handy.

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL>

In case you have a system running a lot of databases you might find a statement like the one below very handy to get some information about what and who is running in which state, in this case it is not very impressive however it can come in handy:

SQL> select
2 instance_name,
3 version,
4 status
5 from
6 v$instance;

INSTANCE_NAME VERSION STATUS
---------------- ----------------- ------------
JLO11G 11.1.0.6.0 STARTED

SQL>

The next step is to mount the database so we can later make it available for users. To do so we enter the following command:

SQL> alter database mount;

Database altered.

SQL>

If we now query again the status we will see that the instance is now in the status mounted and not in the status started anymore. Again a query that can come in handy if you are unsure of the current status of your instance.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL>

At this stage the database is mounted however it is not in the status open. If the database is in the status open users can connect to it and can use it, in the status mounted it is closed and you (as a sysdba) can work on it. A mounted database gives you access to configuration tables and such, this can be very handy when you are recovering or working on solving a priority one problem with the database. You will have access to fixed tables and views. The fixed tables and views are those in the data dictionary (Oracle's internal configuration tables). We do however want the database to be open for all users so we have to alter the database so it will be open for normal use:

SQL> alter database open;

Database altered.

SQL>

Now the database is open for normal use and all users can connect to it and work on it normaly. We can also 'again' check the status and we will see that the database is open, this might be a good thing to do before informing your users that the database is available again. (login as a normal database user is also a very good check ;-) ).

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>

So in short, what do you need to do to make instance come back to live:

- startup nomount;
- alter database mount;
- alter database open;

And to shutdown the instance you can do so by enetering a "shutdown immediate;" command which will result in the following:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.