Hello, My name is StÈphane Faroult, I am a French
database consultant and I wish to guide your first steps in Oracle
administration in a few videos. I shall base my videos on Oracle Express, an edition of Oracle that
is free, but stores up to 4 GB of user data, uses up to 1 Gb of memory and is supposed
to use a single processor ñ which is enough
for many applications. It can be downloaded from this URL. Otherwise, it is the same Oracle
as all the other editions and it is presented by Oracle as a ìstarter databaseî for Database administrators.
Most of the things I will show you are valid with any
Oracle edition. If you havenít watched yet my video about
the architecture of Oracle which is elsewhere on this site,
you may find useful to do it before you watch this video. If you are interested by Oracle Express, itís
very likely that either you simply want to learn Oracle, or you are already familiar with Oracle and
wish to capitalize on your knowledge for a new project,
in which case you would probably not watch this video, or you have a new project that you want to
start on the cheap but which you plan to upgrade to the paying Standard Edition or Enterprise
Edition one day. The problem is that if the tools that are
provided with Oracle Express make it easy to use for a
developer, they donít really guide you into administering
Oracle the way the other editions are administered. I shall
therefore try to focus, past the installation stage, on concepts that
are common to all editions of Oracle, and, even if you are using Oracle Express,
Iíll try to take you closer to the real world of Oracle administration. When you install Oracle Express on Windows,
itís basically a question of clicking ënextí all along.
You are asked three things, first to accept the license, then where on your disk you wish
to install the Oracle software and your database files,
and finally the password you want to give to the SYS and
SYSTEM accounts, two key accounts in an Oracle database. The Oracle software and the initial database
that is created use about 1.2 Gb of storage, and you will find installed
on your machine about 2,400 new files in 140 directories. Out of this only 100 Mb are
initially allocated for user data. Beware that even if you are limited by the
license to 4Gb of user data, a database doesnít contain only
user data ñ the data dictionary uses much storage and
if you want to use Apex to develop your applications they will be stored in the database too. Most importantly, you will need to backup
your database, and the copy will be probably stored at least temporarily on disk. You must
therefore make sure that you have at least 10 or 15 Gb available if you want to be in
the comfort zone. If you install Oracle Express on Linux, and
I have done it on Ubuntu and Open Suse (there may be some
minor differences on other Linuxes) then you first install a package, and the location
that is asked during the Windows installation is forced
as /usr/lib/oracle. For having tried it, creating a link to
elsewhere prior to installing the package is a bad idea because it makes, in the next
stage, the restoration of the template database fail. If they donít already exist, an oracle account
and a dba group will also be created, and the home
directory of the oracle account will be set to /usr/lib/oracle/xe. A shell script called oracle-xe will be also
created under /etc/init.d. You will then need to connect as root, and
run this shell script with the ëconfigureí parameter.
This script will prompt you for four things, the HTTP port for the web-based administration
toy, the TCP port for connecting to the database
from other machines, the common password for users SYS and SYSTEM, and whether you want the database to be started
when your server boots, which you usually want. Interestingly, the only common question with
the Windows install is the password. For Windows, the default values of 8080, 1521 and Y are
used. Disabling automatic service start is easy,
and Iím going to show you shortly where the port
values are used. This operation creates a configuration file
called oracle-xe that you will find, depending on your Linux,
either under /etc/sysconfig or /etc/default. You arenít supposed to modify it by hand
but if you want to change it you must delete it before running
the script that creates it. Another file that will be created is called
/etc/oratab. The installation script will create it with a
line that starts by XE, followed by a colon, a longish path, another colon, and N.
If you want to re-run the configuration script, you should remove this line from the oratab
file. This brings me to a first discrepancy between Oracle Express and the
other editions. This file is the standard Unix
way to tell Oracle that you want one particular database instance to be started when the
machine boots. There is one line per database with three fields separated by colons. The first one is the name of the instance, the second one is what is called the ìoracle
homeî and tells where the Oracle binaries are located.
It allows having several different versions of Oracle running simultaneously on the same
machine. The third field must normally set to Y if
you want your database to be started at boot time.
Oracle Express is the only edition that doesnít use this file for this purpose. You can safely
let N. On Windows you will notice after the installation
two services running. One is called OracleXETNSListener and it has two functions ñ it listens on one port, 1521 by default, for
incoming connections to the databases, and it also doubles as a lightweight HTTP
server that listens on port 8080 for the administration interface and Apex applications. The other service is OracleServiceXE that
accesses the data in memory (in the SGA) and the data files. On Linux, the picture is a bit different.
The listener is a process called tnslsnr. But instead of a single service for the database,
you have a large number of specialized processes (in Windows, their roles is played by threads
in the service) of which I am only showing a few,
and each connection will usually create a new server process. You can easily check a few things in a console.
In Linux you should have a few variables set in your environment. The most important one is called ORACLE_HOME,
and this should be the second field in the oratab file.
You should also include $ORACLE_HOME/bin in your path, and set ORACLE_SID to XE. This part of ORACLE_HOME is sometimes known
in Oracle as ORACLE_BASE, and it is the equivalent of the
installation directory that you provide in the Windows installation. Be careful, though, because the definition
of ORACLE_BASE isnít consistent across Oracle versions.
Letís say that this will be my definition of ORACLE_BASE. You can check the listener status by typing
lsnrctl stat. It will first list you information about
the version, current settings, and the names of the file that stores the parameters for
the listener (you should take a look at it), as
well as the log file. But then comes the most interesting. You have
the list of communication types that the listener can handle. ìipcî refers to inter-process communications,
thatís usually what is used when you access the database
from the server itself. The next line is for tcp connections, and
the information that is displayed is read from
the parameter file. The last line is for the HTTP connection,
and this is NOT read from the parameter file. In fact,
the port is stored inside the database itself, where you can change it if it conflicts with
something else. The way it works is that you start the listener
first. When instance XE is started (any instance,
in fact) it looks for a listener and registers itself
with this listener. That way, the listener knows that the database
instance is ready to accept connections. The listener gets the HTTP port in the process. And if you check the listener status, you
can verify that the database instance has registered (you find other services that are
registered, but this is the important one). Iíd like know to guide you in the jungle
of the 140 directories and point to you the most important places. First, below what I have called ORACLE_BASE
youíll find app and oradata. I keep oradata for later; under app you find oracle admin and XE that
is in fact the name or ORACLE_SID, of your database instance.
If you were running several instances on the same machine, you would have one directory
per instance. Below this you have a few directories, most
of which have a name that ends in ëdumpí, and usually fill
up with .trc files ñ trace files that may or may not relate to an interesting problem. What is really important here is in bdump
the file called alert_XE.log. Of course, once again XE is the ORACLE_SID
and the name will be different for another instance.
This file records every change to the structure of the database, as well as errors,
which sometimes point to a .trc file nearby. This is the file to watch when you want to
check that everything is fine, or after a problem. Many DBAs write scripts that scan this file
for lines that contain ORA-, which indicates an error.
You may also want to rotate this logfile from time to time, as well as remove old .trc files
ñ as an Oracle DBA, itís usually very useful to have
scripting skills. Note that this hierarchy changes after Oracle
10. If you have to deal with Oracle 11, you will
find the alert file under diag, rdbms, the database name
and the instance name ñ several different instances, that is sets of processes serving
a memory area, can share a single database that is a set
of files. This kind of configuration can be met with very
large databases. In most cases, you have one instance per database and the name of the
database is equal to the name of the instance. Another
set of directories below and the alert file isnít under
ìalertî, that would be too easy, but under ìtraceî. But letís return to Oracle Express. At the
same level as admin you have a doc directory that contains HTML
documentation. If Iíd thought that itís amazingly good
for learning Oracle administration you wouldnít be watching this video. You also have flash_recovery_area, and this
one is really important. You have one XE directory, once again itís one directory per instance,
and below you have ONLINELOG that contains two redo log files,
that is the journal of transactions that is used to restore the database to a consistent
state if the machine crashes. Oracle writes them one after
the other in a circular fashion, and you donít have to
worry about their growing out of control. Flash_recovery_area is also very important
because thatís the place where the backup of your files
will go when you use the backup utility that is provided with the database ñ Iíll tell
more about the topic in another video. Finally, still at the same level, you find
the product directory and thatís where the Oracle
distribution really goes, with one subdirectory per version of the software, and a ìserverî
subdirectory because today the database server proper is
just one offer in the Oracle product range. This entire path, ORACLE_BASE, app, oracle,
product, version number, server is what is known as
ORACLE_HOME ñ you find it in oratab on Unix, if you remember, and itís defined in the
registry on Windows. Many directories there, I just want right
now to point at two of them, ìbinî contains all the Oracle programs,
the database program itself but also the sqlplus client for
instance and all utilities. You must make sure that it is in your execution path. And ìdbsî contains a file named SPFILE.ora that contains the configuration parameters of your database instance. Itís
a binary file, Iíll tell you more about it later. After app, itís much simpler on the oradata
side. One directory per instance, and there you
have first the control file, and then the various data files that make
up your database ñ the data dictionary and other internal
data in SYSTEM and SYSAUX, a work area in TEMP when you have for instance to perform
massive sorts that cannot fit in memory, UNDO that stores
what is required to rollback transactions, and finally
USERS that contains user data. Filenames are in lowercase on Linux. Here is the layout, and weíll see more about
administration proper later. Stay tuned!