 Cameo
Appearance as DBA -- Part III
Roger Feldman
This article is Part 3 of a three-part series designed to help
Unix admins become familiar with Oracle and Sybase. The first article
gave some background to issues to consider before diving into the
database. It also gave information about how to get connected and
start/stop the database. The second article explained how to navigate
around the database, collecting valuable information along the way.
The final installment will look at sql queries, backup of the database,
and a few common tasks to be performed.
At this point, it's time to get involved with the users' view
of SQL. You don't need to be an expert at SQL. You will, however,
need to understand the basics of the language so that you can make
simple queries, install special applications that come with scripts,
and solve problems that your customers will have inside the database.
The SQL language is not complicated. If you are a skilled Unix
technician, you should not have problems making queries. You might
have problems with the SQL proprietary extensions (such as PL/SQL
from Oracle) that have been added by the vendors, but you can get
by with basic administration using some simple SQL. You'll do just
fine if you examine and use the following SQL statements:
SELECT, INSERT, DELETE, UPDATE, CREATE, DROP, ALTER
Several other statements are used in conjunction with the previous
statements. You can start with:
FROM, WHERE, AND, GROUP BY, ORDER BY, HAVING
The Web contains many tutorials for SQL. Here is a link to one of
the scores of SQL tutorial sites:
http://www.w3schools.com/sql/sql_intro.asp
I found the Oracle SQL fundamentals study book (see "Resources") to
be a good way to try most of the features of SQL. The book's examples
are taken from the user schemas from the Oracle demo database schema
that comes with the sample database. Oracle has been moving closer
to the ANSI standard so that most of this basic SQL can be useful
for other database vendors. If you connect as the "HR" oracle demo
user, you can use the following command to see HR's tables and views:
sql> select * from tab;
You can then run a "describe" on one of the tables to see which columns
it contains:
sql> describe countries
I recommend spending some time with the SELECT statements,
because your first interest will likely be to look into the database.
We'll leave the coding and development to the application development
team. I don't recommend altering the rows of a database if the changes
are to be made by a program or application. Be sure you know what
you are doing if you are going to run UPDATE, DELETE,
and INSERT outside the application that normally makes changes
in the database. Concentrate on using the FROM, WHERE,
ORDER, and GROUP BY statements to trim your SELECT
statements so that you get exactly what you want. Here is an example
of the syntax:
SELECT column_name1, column_name2
FROM table_name
WHERE column_name1 = xxxx
AND column_name2 > 999
I also created my own tables and practiced inserting data into them
(example from Oracle):
SQL> CREATE TABLE artists (
SQL> name varchar2(30),
SQL>instrument varchar2(20),
SQL>id number(10),
SQL>birthdate date);
Here is an example of an INSERT:
SQL>INSERT INTO artists
SQL>(name, id)
SQL>VALUES ('jones', 17);
This is all very logical, and you should be able to mix and match
your own SQL queries after a few hours of study and testing. Don't
worry about advanced topics like JOINS until you can really
manipulate the basic SQL.
Backups
I am basically known as a paranoid and cautioned administrator,
and I was quite worried about having the responsibilities of a DBA.
To address this, I made it a priority to be able to perform a recovery
so that I wouldn't have to worry about anything that went wrong.
It was a way to compensate for my lack of experience. I stand by
that today. Your backup policy is the basis for everything that
you have. If you inherit a database that already has a backup configured,
read the scripts or check which programs are running the backup.
Confirm the backup and test recovery. When something goes wrong,
the pressure is enormous to get the database back. There are lots
of different ways to perform backups, so you'll have to investigate
the possibilities.
I like the Sybase backup because it seems to be straightforward.
There are four basic commands that are usually incorporated into
some kind of shell script. Try creating a test database and test
the "dump database" to make a full dump. Try using "dump transaction"
to make a transactional dump. The backups are loaded with the "load
database" and "load transaction". You'll need to read further on
your own to fully understand the concept of Sybase transaction backups,
which you may or may not need depending on your site. If you want
to be able to recover to the hours after your last dump, then you'll
need to use transactional dumps. Here is a basic dump to a disk
file:
isql> dump database myTest to "/u05/dumps/myTest.dmp"
isql> go
Here is the basic load:
isql> load database myTest from "/u05/dumps/myTest.dump"
isql> go
The database must be placed online after the load:
isql> online database myTest
isql> go
You'll probably be able to find a script on the Internet that uses
this command. I use a script created by a Sybase consultant that I
inherited at my site. It is quite similar to scripts that I have examined
on the Internet.
Oracle backup will offer many more options and alternatives. I
have used different combinations of the following:
1. Export and import utilities
2. Hot backup
3. RMAN (Oracle recovery manager)
Deciding how to back up Oracle could be confusing. The most logical
answer is to use the RMAN Oracle backup utility. This utility has
developed over several years and has reached a point where it is
mature and easy to use.
Before we moved over to RMAN we used what is known as "hot backup",
which involves making a script that places the database files into
backup mode allowing for backup while the database is running. This
worked quite well but required administrator knowledge of manual
recovery. The RMAN utility made that obsolete because it takes care
of all those ugly details. You can run RMAN from your own scripts
(to be run from cron) or from the OEM GUI manager.
I recommend RMAN, however, setting it up can be a bit of a task
itself and is beyond the scope of this article. (See the "Oracle9i
Recovery Manager User's Guide" for details of what is required.)
If you really want to run hot backup scripts, then I recommend taking
a look at the many hot backup scripts found on the Internet. They
all seem to have one thing in common -- they place the tablespace
in the backup mode with a command like this:
sql> alter tablespace myTest begin backup;
At this point, you copy the files to your backup directory and then
issue the end backup command:
sql> alter tablespace myTest end backup;
You will also need to know how to take care of your archive log files.
There is an option to use archive log versus noarchive log. Your redo
logs have records of all changes in the database and will eventually
overwrite the redos after switching through the redo log groups. The
archive log mode will write these to disk so that they can be available
in a recovery situation. This requires space on your disk and some
kind of interaction with a backup and cleanup program. The RMAN utility
handles all that quite easily. But, even if you have automated these
functions, you'll need to have a good understanding of the redo logs
and archive logs.
You will also need to consider the import and export utilities.
I have been criticized by many people at the online DBA forums for
using import and export for backup. They can, however, be effective
in certain situations for isolated user schemas. This method should
not be your only backup solution but can complement RMAN
or hot backups. These utilities basically dump out all or parts
of the database to an export file, which can later be imported.
You can dump the whole database, a user, tables, and more. The point
is that this should not be your primary backup method. If the database
is quite large, you may have issues with the time it takes and with
the performance load during the operation. Here is a quick example
of an export that dumps the entire database. You can also do this
on the "user" level:
# exp system/password file=fulldump log=fulldump.log full=y
The import command has an equally friendly syntax and can be used
in many ways to import all or parts of your export. You can list a
user or a table. The import and export commands even have an interactive
mode. Running a "one-liner" from a script usually does the trick.
This is just a quick tour of backup methods. The main point that
I would like make is that the backup isn't complete until you have
thoroughly confirmed and tested recovery. Never be happy to configure
a database backup and "hope" that it will be okay. Always document
the backup and recovery methods so that another member of your team
can recover if you are absent.
Other Tasks
Finally, I will look at some common DBA tasks that you will be
performing. This guide is an introduction only and cannot cover
everything. I hope to provide information to get you installed,
logged in, and involved enough that you can begin to solve some
basic problems.
After installing Sybase, we always create the users and the databases
that our customer has ordered. In Sybase, you have to create a disk
device and log device that will be used by the database that you
create. You obviously must size the devices to fit your database
needs. You can create separate disk and log devices for each database,
or you can create a large disk device and create all the databases
devices in that large area. For our test database, we have a partition
/u01/devices that contains the devices and we create separate devices
for each database. Here we are creating first the data device and
then the log device using the Sybase disk init command from
isql:
1> disk init
2> name ="test_data01",
3> physname="/u01/devices/test_data01",
4> vdevno=5,
5> size=8192
6> go
1> disk init
2> name ="test_log01",
3> physname="/u01/devices/test_log01",
4> vdevno=6,
5> size=8192
6> go
The disk init command is used to create a device, "name" is
the unique name, "physname" is the real disk path, "vdevno" are a
series of available disk device numbers. The vdevno is automatically
set to next available with later Sybase versions. Finally, we have
the "size". Once you have created your devices, you can create a database
using those devices, which in this case will be 4 MB:
1> create database mytest on test_data01=4
2> log on test1_log01=4
3> go
Now we put it online:
1>online database
2>go
Someone must also own the database and use the database. Sybase has
a two-layer approach to access of the database:
- Login: This level is the level at which you can log in to the
server. It does not mean you automatically have access to the
databases. You must have a "login" before you can be granted the
status of a "user" of one or more of the databases running on
that server.
- User: Once you are a user, the dbo (database owner) can create
a user login for a particular database.
Syntax for new login:
isql>sp_addlogin name, password, default_db
Syntax for new user:
isql>sp_adduser login_name, name_in_db
Display the results:
isql>sp_helpuser
Most of the time, a trick is used to create the "dbo" (database owner)
after creating a new database:
1> use mytest
2> go
1> sp_addalias testadmin, dbo
2> go
Aliases are an alternative to user logins. If user "testadmin" has
a login, she can be aliased to the dbo of the database, thus having
the same privileges as the dbo without even having a local db user
login.
If you have received a script that creates user tables and objects,
you will need to run it from the command line:
% isql -S MyServer -U Myuser -i script_name.sql
This method is almost always used if you receive a product or application
that requires a predefined object in the database.
You may have problems or receive error messages that ask you to
change some parameters in the database. Study your DATABASE_NAME.cfg
file, which is found under the ASE-12_x catalogue, to see your tunable
parameters. Sybase is a helpful system and often tells you what
it wants you to change in that file. With the later versions of
Sybase, many of the changes can be made "live" from the running
database, at which point the old ".cfg" file is saved in sequence
as a backup. You can edit the file yourself and bounce the database
if the parameters are not dynamic.
The Sybase online manual describes all the parameters in the file
and gives you examples of how to change them. This is just a taste
of some basic Sybase administration tasks. I hope that running a
few of the commands that I have shown will inspire you to read the
online pages and continue learning.
You can begin Oracle administration by setting up an application
in your instance. We usually receive a set of special scripts to
be run after we have created the Oracle instance. These scripts
come from the application product that we have purchased or the
application developers that have created the application that needs
a database. Sometimes the scripts will create the tablespaces, tables,
and users. Sometimes those will have to be created manually. Either
way, you'll be checking to see that everything is set correctly
so that the application can run properly. You might first need to
create a tablespace, which will be used by a user.
Earlier versions of Oracle had a complex syntax for tablespace
creation. You had to know a how to size the default storage parameters
required for the tablespace. These settings are INITIAL, NEXT, MINEXTENTS,
MAXEXTENTS, and PCTINCREASE. Sometimes we received these values
from the application vendor and sometimes we tried to calculate
them ourselves (after much study). With current versions of Oracle,
you can configure these to be set automatically by using "extent
management local". I'll show you the most simple form of a tablespace
creation:
SQL> CREATE TABLESPACE project
2 DATAFILE '/u03/oradata/test/project01.dbf'
3 SIZE 50M
4 EXTENT MANAGEMENT LOCAL;
Once you have a tablespace, you can create the user for that tablespace.
Often you will also create a separate tablespace to be used as its
own temporary tablespace:
SQL> CREATE USER proj_adm
2 IDENTIFIED BY Mypasswd
3 DEFAULT TABLESPACE project
4 TEMPORARY TABLESPACE temp;
Next you will need to grant the privileges to connect and create objects
in that database:
SQL> GRANT CREATE SESSION TO proj_adm;
Then you will have to give them privileges to create their objects:
SQL> GRANT CREATE TABLE TO proj_adm;
SQL> GRANT CREATE VIEW TO proj_adm;
SQL> GRANT CREATE SEQUENCE TO proj_adm;
You can now make life easier for yourself and learn how to configure
ROLES and PROFILES.
PROFILES can be used to limit the resources that a user's session
can consume and include connection time, idle time, logical reads,
and failed logins to name a few. The default is unlimited resource
usage. To use resource limits, set the init.ora parameter resource_limit
to TRUE. Then you can create or alter a user's profile. You can
define the profile while creating the user. In the following example,
we will change user barney's profile:
SQL> alter user barney profile test;
ROLES are used for administering privileges that are granted to a
role, and a role can be granted to a user or another role. This makes
for easy administration and inheritance:
create role helper;
You can now grant privileges to the role "helper". You'll have to
look up the dirty details of GRANT on your own. Be sure to read about
how users inherit the granted privileges.
Final Notes
If you are running Oracle or Sybase, then you should have a service
contract. Take advantage of that. If you have problems or questions
that can't be solved with documentation, newsgroups, or Internet
forums, make a help desk case. If you are paying a lot of money
for a license, then you might as well take advantage of the service.
They may be able to help you if you have exhausted all other avenues.
There is a lot of information that I have not covered at all;
for example, I haven't even mentioned indexes or constraints. I
hope to have given you an idea about how you can get involved as
a DBA, but it would take a whole book to cover all the material.
Start looking at your own databases and ask yourself questions.
Take it upon yourself to investigate things that you see while looking
at your database. Solving problems will give you the best chance
to learn, and every time you perform a new task, you'll improve
your skills.
Resources
Dawes, Chip, and Biju Thomas. 2002. OCA/OCP Introduction to
Oracle 9i SQL Study Guide. Sybex Books. ISBN: 0-7821-4062-9.
Oracle9i Recovery Manager User's Guide -- http://www.metalink.oracle.com
SQL tutorials -- http://www.w3schools.com/sql/sql_intro.asp
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 at his
home in Stockholm Sweden. He can be contacted at: roger.feldman@bostream.nu. | 
