As a systems administrator and part-time developer I find myself touching and playing around with many technologies including databases. However, one particular flavour has always seemed to elude my understanding until lately and that is the Oracle Database. This is very strange especially given the fact that most admins I know are familiar with MySQL, PostgreSQL and a myriad of other key/value data stores and NoSQL databases but yet there is that hurdle to developing the familiarity or expertise with Oracle. Maybe its Oracle's legacy, its weird file conventions and hierarchies, the fact that it wasn't always open or easy to download and is still very expensive, or simply the fact that there is just so much configuration still necessary to get it going. Whatever the cause we are going to help you to jump the hurdle today.
Oracle tends to play in an enterprise league that causes it to have the aura of a mysterious black box. Often times it rests as the backbone of core mission critical business applications making it even more scary to deal with and further protecting it from prying eyes and silly juniors that like to tweak and god forbid meddle. As a result of its place at the heart of vital business infrastructure in those rare circumstances when it finally does creak and give a problem or two you are more prone to utterances like
Let me call the DBA
than to tinker around and try to fix it on your own. Why does this database require more expertise? The truth is it is not that tricky after all. For the admins out there that feel lost at sea when there is a problem with that non open source mother of a database called Oracle I am here to help. This series of articles is for you and will give you the foundation needed to resolve that issue when the DBA is on vacation.
The Basics
So, first things first, we need to define a few terms and their meanings. You are going to find out quickly that not everything has the same meaning in Oracle land. Simply because the heritage goes back to as far as the 70s it is more Unix than Windows and its perspective on the world is very different even in relation to Unix/Linux today. Unsurprisingly, for an architecture that goes back this far it does have a fair amount of cruft but nevertheless its design choices still stand up pretty well today. The software is very flexible and strikes a fair balance between convention and configuration. Hence, its performance and continued popularity but notwithstanding those good roots and design decisions it can be off putting to the newcomer as mentioned previously.
One key thing to always remember is that the Oracle database is not a monolithic application. Instead it is a bunch memory structures, file structures and processes. Oracle follows the unix philosophy of having many small specialized tools that are chained together to perform a function. Additionally, flexibility is a major feature for Oracle databases which also means that it takes a route of mostly configuration but sticks to a few specific conventions when it comes filesystems and naming. Ironically those few conventions it does keep deviate from the conventions in vogue today.
What is the instance, database and listener?
Unlike other database systems Oracle maintains a separation between its networking components, the storage structures inclusive of datafiles and the actual processes that control and write information to those datafiles and structures.
The Database
As far as Oracle software is concerned the database refers only to the binary storage structures. That is the database is the collection of data files that store all the objects inclusive of Schemas no matter how many. Each Oracle instance will only ever manage a single database. However, there can be many Schemas, Tablespaces and Datafiles.
Database Instance
The instance is the set of processes and memory structures known as SGA and PGA that manage and control the database. Essentially, for all practical purposes the instance is what we know as the database server.
Note carefully, the instance does not include those processes that listen and respond to incoming and outgoing connections. Networking is treated separately from the instance.
Listener
Because the networking functions of the database server has been moved from the instance the processes that control inter process communication as well as manage external connections over a network whether IP or Unix sockets based are known as Listeners. Listeners also have there own name resolution known as TNS. Strictly speaking TNS is likely used for other things but to the end user it is really just the naming system used to identify different components (typically connections to instances).
Installer
All Oracle software is installed via a grand installer program referred to as the OUI (Oracle Universal Installer. This is a Java application that installs the required components. Since, it uses graphics the installation will therefore need to take place using the shell GUI or over a remote desktop session on Windows or an X Terminal Server Emulator on UNIX/Linux environments.
Client Access
Users can run queries on the database from the CLI using the built in client access program sql*plus. This is similar to the mysql or psql clients. Applications and programs will connect to the database using JDBC, ODBC APIs or language API bindings.
Users and Groups
One non-system user and two groups should created to complete a standard install of the software. There should be a user called "oracle" which owns the database on the system as well as two groups "dba" and "oinstall". Strictly speaking the users and groups can be named anything but this is an area where is pays to follow the convention.
Post installation you will also assign roles that take care of the actual administration of the database but these are separate from the OS users and groups. Various roles include sysdba (super user), sysoper, sysman, asmoper etc. The total number of roles in use will depend on the components and features being utilized. Do remember that there are users and roles in the database and these are separate from the users and groups mentioned in the first paragraph above.
Environment Variables
The database software uses many scripts to automate tasks during administration. These scripts are context sensitive. Tracking which paths and context are relevant is done by using the operating systems environment variables. The most important such variable will always be the $ORACLE_HOME and $ORACLE_BASE. Later on you will also be introduced to the $ORACLE_SID variable that stores the instance name.
When encountering errors on the command line sometimes it pays to check what values are being referenced by the variables in the profile. You can do this on Linux by running the env command from the shell.
env
File System Hierarchy
Oracle has a standard for its directory structures across systems. It is known as Flexible GRID architecture or something like that ...whatever the name is just remember that is it strange and that it does not follow the FHS.
This means it will not put its files under /opt or place any binaries in /, /bin, /usr, /usr/bin. Being unique Oracle invented its own convention and will by default install files under directories start with /u
The convention goes like the following:
File system base:
/u01/app/
This is usually saved as the $ORACLE_BASE variable.
Applications
$ORACLE_BASE/product/
or
$ORACLE_BASE/version
Database
$ORACLE_BASE/product/
This is usually saved as the $ORACLE_HOME variable.
Database Files
$ORACLE_HOME/dfs
Alerts, Diagnostics, Traces Files, logs
$ORACLE_HOME/diag
Configuration Files
/etc/oratab
/etc/inittab
$ORACLE_HOME/admin
Key Processes
DBWn Database Writer processes. These daemons flush buffers from the database buffer cache in memory to disk.
LGWR The log writer process. Manages the redo log buffer by writing the redo log entries to the log file.
CKPT Checkpoint Process. Records the database checkpoint stamp to the control file. The control file is a file that keeps track of where and which datafiles are being modified. This file is needed during startup and recovery.
SMON System Monitor Process. Performs recovery of an instance and cleans up memory segments.
PMON Process Monitor. Registers services with listeners, monitors idle session timeouts and performs user process recovery and failure detection. Releases unused resources.
RECO Recoverer Process. Used in distributed database configurations and resolves transactions in doubt between systems.
ARCn Archiver Process. Performs log switches and copies redo log files. Transmits redo data to standby destinations in active-standby configurations.
Introspection through Views
A powerful feature of Oracle is its ability to do introspection. It exposes many memory structures for viewing and manipulation via SQL queries to special tables called views. By modifying and setting the fields in these view tables the behaviour of the database can be altered often without requiring a restart or editing configuration files.
Most relevant database views have v$ prepended to their name or gv$ for grid views.
Next Steps
Now that you have a grasp of the basics it is time to start actually doing something. The next step is to go and download the Oracle Database. The current version is 12c. It will also be useful to download the Grid infrastructure as well as the core database files. You will need to download both zips for each component.
We are taking an approach similar to the Red Hat Cluster Series and have split this tutorial over several articles to prevent it from becoming one long monolithic piece. The follow up post in the series will go through actually setting up the OS pre-prequisites and walking through the installation wizard.
Keep checking the Oracle Tag or the Tutorial section to catch all the articles in the series or simply sign up below to be reminded via email the instance the next post is published.
Written by
Nneko Branche.