 Cameo
Appearance as DBA -- Part I
Roger Feldman
Back in 1998 when I first started working as a Unix Administrator,
I noticed the database structures on certain machines. I wasn't
sure what /u00 and /u01 meant, but I didn't want to make any
mistakes with something that I didn't understand. Two years
later, I was negotiating arguments between Unix admins who didn't
think that database administrators (DBAs) knew anything about Unix,
and DBAs who didn't want Unix admins touching the machines
that housed their databases. I had a strict policy. I didn't
do anything on a database machine without contacting the DBA. I
never administered the databases.
During the end of 2000, I got a new job as a consultant. I promised
to do whatever jobs came up for the firm, and was immediately placed
as an Oracle DBA at a large company that could not find a DBA during
the IT boom. They had predicted a large increase in Oracle and Sybase
during the boom, and I was forced to quickly learn how to handle
the basic DBA duties. Shortly thereafter, the IT crash ruined their
plans for expansion, spending cuts were made, and I was expected
to work with databases "on the side" while doing Unix
administration. It's been like that for the past four years,
and I consider myself lucky to be a part-time DBA. During cutbacks
in IT spending, companies tend to keep people who can handle the
databases. I enjoy being involved in the world of databases.
The following information is a guide to being a part-time DBA
while working as a Unix admin. I'm not aiming to be the senior
Oracle/Sybase DBA, but I've been consistently able to fill
in gaps, thereby helping the IT team and consolidating my role as
an all-around player. In this first article, I'll focus on
installation basics and startup and shutdown considerations.
No Modification without Verification
This first rule of database administration is extremely important.
Never make changes or modifications on a "production"
database without following the procedures for testing and verification
in the development environment. Most companies that have important
data in their databases use the production-test scheme for databases.
All new tests, changes, and modifications are first done in the
test environment. After the changes are thoroughly tested, they
are implemented during a planned change date for the production
database. Making a mistake in the production environment without
following procedures is a proper cause for punishment or termination
of employment.
Because we are new to the database world, we will go one step
further. We will install Oracle and Sybase on a Linux machine where
we can make all the mistakes we want (I also use a Sun for testing).
Your company's test environment may be used by serious developers
who are testing new versions of their software. It may not be professional
to do your testing in their environment. You will want to use your
own machine where you can install the database ten times if you
like and reboot at will.
Oracle downloads for Linux are available from Oracle's Web
site. You may want to run Oracle 10i, which is the latest and greatest.
I use Oracle 9i as our site is locked in on that version for the
time being. Large companies may have many applications and products
connected to a certain version of Oracle. Making the move to a new
version may take a long time and, even though the database version
may no longer be supported, the company may stay with the outdated
version because of problems migrating the application and issues
with downtime.
We currently have two databases that must be run on Oracle 8.1.7
due to the demands of the original application support. Our main
databases are Oracle 9i, and we have an OEM (Oracle Enterprise Manager
GUI) that is also Oracle 9i. Getting our customers and application
products over to Oracle 10i may take some time. If this sounds confusing,
don't worry -- most of the basic concepts and commands
can be used with both versions 9 and 10.
You can find Oracle 9i for Linux at:
http://www.oracle.com/technology/software/products/oracle9i/htdocs/linuxsoft.html
You'll find Sybase for Linux at:
http://www.sybase.com/downloads
Look for the ASE (Adaptive Server Enterprise) downloads. All of our
servers have a Sybase version 12_x.
Installing Oracle and Sybase on the same test machine doesn't
have to be a problem as long as you start them manually and understand
their need for memory and minor kernel configurations. I don't
run them both at the same time. Both Oracle and Sybase will require
a couple of minor adjustments for shared memory on both Linux and
Solaris. Making changes is easy for our test machine. Getting the
correct kernel settings for an Oracle server running with two instances
on Solaris could be more difficult and is beyond the scope of this
article. Sybase also requires special settings for Solaris and Linux
shared memory. Be sure to make the adjustments recommended in the
release notes.
Plant a Database and Watch It Grow
Installing a real database for an enterprise data environment
is like planting a tree. Once you've planted the tree and the
roots take to the soil, you may find it very difficult to move it.
Deciding how to install your database could be the task of a "database
architect". This really depends on the size, amount of users,
and demands on the database. Although the initial installation of
databases has been simplified in the past few years (you'll
find yourself clicking through a GUI-driven installation), you may
need more experience and knowledge before you can install a production
database.
Installing a real database for a real enterprise site will require
you to explore several issues. Our test database may not have RAID,
multiple disk controllers, or raw devices, but looking into these
issues may help you understand your company's database structure.
You must explore your current database installations and ask yourself
why things were set up that way. Taking care of the database will
require total knowledge of the physical installation, which may
have been designed and installed by someone else. Once you understand
where everything is, you can make decisions about dealing with objects
in the database.
One pre-installation issue has to do with RAID mirrors and raw
devices. All of the production databases that I have dealt with
use RAID mirrors. We have always mirrored all of the disks that
contain the Oracle application and the Oracle database files. We
do the same for the Sybase application and disk devices. Sybase
often uses "raw" disks for their disk device files. The
newer versions of Sybase allow you to use regular disk. It will
be well worth your time to do some research before deciding whether
you want raw devices in Sybase. Our test database may not need RAID
or mirrored disks, but you should not dive into a real installation
without making these critical decisions.
While deciding about your RAID disks and other design issues,
you'll need to contact the application owner to find out whether
the Oracle datafiles or Sybase disk devices need to be placed on
separate disks or on controllers that you create during your OS
installation. You will need to consider making space for your backup
dumps if you're going to store them locally. So, you will not
be able to correctly answer the questions in your GUI-based installation
if you don't know about the I/O and other demands on the database.
The Oracle installation will also ask which template you will
use for your database. I hope it is obvious that you can't
guess about these questions. My main point is that you must establish
contact with the application owner or the application developer.
They have special needs and have detailed knowledge that will be
necessary for you to complete the installation.
The naming conventions for the connections to the database will
also be important because your application clients may already have,
or need to connect to, a special name. It could be a nightmare to
change the connection configuration on hundreds of PCs or the hard-coded
connection parameters for an in-house application because you did
not make the correct choices. All of these things can be changed,
but my experience is that making changes in large databases requires
special planned testing and downtime.
At the end of the Oracle installation, you may choose to run a
GUI database configuration assistant (and network configuration
assistant), which will guide you through the creation of your Oracle
instance. You may choose (if you have the skills) to create your
instance with scripts. At this time, you will have to answer those
vital questions that I mentioned previously. You'll be asked
what kind of "template" you want to use. You'll need
to know your SID and Global database name. Then you'll be asked
whether or not you want a "dedicated" or "shared
database". You'll also be asked where to place the database
files. You'll need to match these file locations with your
disk/RAID/controller strategy that you made during the design of
the OS and database. Where I work, we follow the /u00 style directory
naming because it is an easy way to say, "these are database
directories". Basically, you can create catalogues named /u00,
/u01, etc., so that each one corresponds to a different mirrored
disk partition. Using this method can isolate your tablespaces,
creating better I/O and protecting them from disaster.
The Sybase installation will also guide you through a GUI set
of choices. Sybase's approach is different from Oracle's.
The Oracle databases that I've used have been matched to one
specific application. We run one Oracle instance (a set of Oracle
processes) for a certain application. Although you can have more
than one Oracle instance running on a machine, we have dedicated
the whole machine to one instance. When your Oracle installation
is complete, you have one instance running with a specific name
that will be most likely used for a specific application.
Sybase, however, uses another concept. You install Sybase and
then create databases under that main database. When your Sybase
installation is done, you'll have the main database running
but you will have no user databases. Your job then will be creating
the disk devices and creating the database(s). We usually have several
smaller Sybase databases running under the single Sybase main daemon.
This is not to say that you can't run a single database under
Sybase or that you can't have several unrelated Oracle users
that connect to the same Oracle instance. The point is that Oracle
has the "instance" concept and Sybase has databases created
under the main master database concept.
I will not cover all of the installation details as I hope that
I have made my point, which is: anybody can install a database,
but a real enterprise database must be designed and installed by
an experienced professional. These issues may not matter for your
test database, but you should still look at your production databases
and ask yourself "why" the partitions and structures were
set up as they were.
My observation is that if you have installed the application and
database correctly on a well-designed machine, then the database
will run well and need little administration. Several of our databases
seem to run themselves because they were designed and installed
correctly with an eye toward the future. Poorly designed databases
will require changes, updates, and lots of fire-fighting administration.
Ask for help if you lack the experience to make these choices.
I inherited most of my databases from former employees and have
imitated their design structures when I have needed to design and
install without help. When we had a custom in-house application
that needed a customized datafile scheme, the Senior DBA spent many
hours working with the application developer to design the database
so that it worked properly with the custom application. Studying
the installation and configuration of existing production database
servers will be key to learning about the database structures.
Startup and Shutdown
After you have installed your test databases, you'll need
to be able to do two things -- login, and start and stop the
database. Don't laugh, but as an assistant to the database
team, you may be the one who is asked to do a simple "bounce"
of the database if the actual DBA has something else to do on a
Sunday afternoon. He or she might say, "bounce the database
and call me if there are problems". This may be part of a planned
stop for your site or part of making changes to the configuration
that require a "bounce". Well, it's not too exciting,
but it's a start. You may have start and stop scripts at your
site that run out of your /etc init catalogues. While doing maintenance,
we like to start and stop the database manually to verify that it
comes up (or down) successfully. It's a good idea to learn
how to do this without the scripts that may or may not be included
in your installations.
On Sybase, you can use the "sa" account for starting
and stopping the database. You should give administration privileges
to certain users so that they are not logged in as the "sa".
Assuming that "freddy" is granted such privileges, you
would first login and use the isql command to connect to
a database named SYBLINUX_DS. Make sure that the database is running
by moving into the catalogue (under ASE-12_x/install) "install"
and running "showserver". This will confirm that that
the database is up and give you the correct names for the data server
and backup server (both should be running):
# ./showserver
If you encounter problems, take a look at the "interfaces"
file in the Sybase home catalogue. This defines the servers you can
connect to with their names and addresses. Do some research about
how to edit and update that file using "dsedit".
Sybase comes with isql as your interface shell to the database.
Connect to the database with the isql shell (-S is for server,
and -U is for user) as follows:
# isql -S SYBLINUX_DS -U freddy
Once you're logged in, you can shut down both the Sybase DS data
server and the BS backup server (both should be running). The shutdown
command uses the default "with wait" option that does a
checkpoint and waits for executing sql statements to finish. You can
bail out as an abort by using the "with nowait". Remember
that you must follow your isql commands with "go"
on the next isql shell line to execute them:
1> shutdown SYB_BACKUP
2> go
Now you can take down the data server (you don't need to name
the data server if it is local):
1> shutdown
2> go
To start the Sybase, go to the ASE-12_x/install directory and, at
the Unix command prompt, run two commands -- one for the data
server and one for the backup server:
# startserver -f RUN_SYBLINUX_DS
# startserver -f RUN_SYBLINUX_BS
To confirm the BS talking to the DS, run the following from the database
isql:
1> SYB_BACKUP...sp_who
2> go
If it returns, then they can run together.
Oracle has a more complicated startup, and there are different
stage levels that can be used for maintenance. You can start with
"no mount" for certain operations where the database is
not mounted and open for usage. Until you get into advanced operations,
which may be used for recovery, you can start with the simple startup/shutdowns.
Oracle uses the sqlplus shell to interact with the database.
Check the $ORACLE_SID variable to make sure that you're connecting
to the correct instance (if you are running more than one instance).
I've almost shut down the wrong instance on a couple of occasions.
To connect to the proper instance, you'll need to have a "listener"
running. You will need to become familiar with the lsnrctl
command, which starts up the listeners that you defined during installation.
It is also used for starting up the dbsnmp. If you have problems,
take a look at two interesting files found under $ORACLE_HOME/network/admin.
The files "tnsnames.ora" and "listener.ora"
define which servers you can connect to and which instances are
available for connection on the server. For the time being, try
the following:
# lsnrctl status # get the listener status
# lsnrctl stop
# lsnrctl start
Use sqlplus to connect to the running database (as the user
who has rights to run the shutdown):
# sqlplus username@your_instance
Once you're connected, you can run the shutdown command
from the sqlplus shell:
SQL> shutdown immediate
There are four options to shutdown. The default is "shutdown
normal", which waits for the users to voluntarily finish before
shutdown. We usually go with the "shutdown immediate", which
waits for current sql statements to finish before shutdown. The "shutdown
transactional" will wait for current transactions to finish.
Finally, if everything is hung and you're totally stuck, you
can run the "shutdown abort" option.
If your database is properly shut down, you can follow this method
for startup (bash shell is used with export):
# export ORACLE_SID="dbname"
This sets the instance ID so that you log in to the correct instance
of the database (if you have two on the same machine). Most sites
have scripts to set the variables. The following command will connect
you to the database if it is not running:
# sqlplus /nolog
You can now connect as "sysdba", who can start the database:
SQL> connect / as sysdba
SQL> startup
There are many options for Oracle startup for you to explore on your
own. They are necessary to perform special maintenance or to recover
the database. Check your manuals to learn the more advanced options.
Conclusion
Taking care of production databases for large companies is not
something to be taken lightly. Most companies have high demands
and expectations and you should not do any guessing. The next article
in this series will show you how to start navigating your way around
the database without a GUI interface, and we will start to gather
information about the database and its structures.
Roger Feldman is a jack-of-all-trades Unix administrator who
has been involved in just about everything at one time or another.
He is currently working as a consultant for a major company in Stockholm,
Sweden. He can be contacted at: roger.feldman@bostream.nu. | 
