Six Must Know Oracle Commands

Currently, only Oracle Database 10G Express - otherwise known as Oracle 10G XE - can be used freely by developers. If you do not have an Oracle installation on your computer, then you should install Oracle database 10G Express so that you can follow along and practice these commands. This tutorial is using Ubuntu 10.04 LTS- the Lucid Lynx - as the operating system. The Oracle version used in this tutorial is “Oracle Database 10g Release 2 (10.2.0.1) Express Edition for Linux x86”.

First Command: Startup/Shutdown

Obviously the first command that you should know is how to start and shutdown Oracle in Linux. Bear in mind that Unix/Linux OS is the most common operating systems used in Oracle implementations (Source: http://bit.ly/dSzjsa ).

If you cannot start Oracle, then you will not be able to execute other commands into it.  Therefore this task is essential to any beginning database administrator. Below are some common errors found when Oracle is not started:

a.) In SQLplus, you connect as SYSTEM then you get this error:

Error:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory

b.) Or if you launch Database Homepage you get this error in your browser:

Could not connect to 127.0.0.1:8080

Solution:

1.) Go to System – Administration – Users and Groups.

2.) Click (null)Oracle - you should enable this account.

3.) When you enable it, it will ask what password you want to assign to that account. Make sure you write down the password. It will also ask for your Ubuntu root password for verification as well.

4.) Launch the terminal. Login as root:

codex-m@codex-m-desktop:~$ sudo -s -H

[sudo] password for codex-m:

5.) Go to Oracle home bin directory:

root@codex-m-desktop:/home/codex-m# cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/

6.) Edit nls_lang.sh using the pico command:

root@codex-m-desktop:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin# pico nls_lang.sh

Change the following from (found in the first line of nls.lang.sh):

#!/bin/sh

to:

#!/bin/bash

See the following screenshot for the correct changes (inside the yellow box): http://www.php-developer.org/screenshot/bashchange.jpg.To save the change, press Control- O then press the Enter key (the filename should still be the same). Then to exit, press Control – X. Close the terminal window.

7.) Launch another terminal window and login as “oracle” user.

codex-m@codex-m-desktop:~$ su - oracle

Password:

oracle@codex-m-desktop:~$

8.) Set the environment variables correctly. Enter this in the command prompt:

oracle@codex-m-desktop:~$ source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

9.) Initiate the SQL plus:

oracle@codex-m-desktop:~$ sqlplus / as sysdba

This is the result (with the SQL prompt in the end):

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 16 02:29:42 2011

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

Connected to an idle instance.

SQL>

10.) Finally, start Oracle in the SQL prompt using the “startup” command:

SQL> startup

This is the result:

ORACLE instance started.

Total System Global Area  603979776 bytes
Fixed Size 1260292 bytes
Variable Size 176162044 bytes
Database Buffers 423624704 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.

SQL>

Oracle is now started. If you would like to shutdown Oracle, issue this command in the SQL (to prevent permission denied errors):

SQL> exit

You will then see:

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Type the SQL*plus statement into the terminal again:

oracle@codex-m-desktop:~$ sqlplus / as sysdba

Safely Shutdown Oracle (using “shutdown immediate” command)

SQL> shutdown immediate

This will result in:

Database closed.
Database dismounted.
ORACLE instance shut down.