 Cameo
Appearance as DBA -- Part II
Roger Feldman
Part I of "Cameo Appearance as DBA" concentrated on background
information that needs to be considered to create test databases
for Sybase and Oracle. I covered issues that affect how you will
analyze your current configurations and how you will approach your
installations. I then made the initial connections to the database
and considered how you can start and stop the database. In Part
II, I will dive into exploration of the database using the command-line
utilities.
Once you've installed a test database and can log in, start, and
stop Sybase and Oracle databases, you may wonder what exactly to
do with them. I recommend a two-pronged approach to getting involved
with being a DBA. You will need to study both the DBA view of the
database and the users' practical view of the database. The DBA
will be looking under the hood of the database. Your users, however,
will be interested in getting help with the actual database tables
and data. At first, I made the mistake of looking mostly at the
DBA side of things and was weak at helping with the users' actual
data. Many of your users may not know about or care about your crazy
database commands because they just want to store their data. They
have a practical use for the database.
GUI Managers
Both Sybase and Oracle have GUI managers that can handle most
of your administrative needs. So, why bother to learn all those
ugly commands when you don't need them anymore? The world of Oracle
and Sybase is complicated enough that it demands that you understand
what happens under the hood. It would be a disaster to learn Oracle
only by the "touch and feel" techniques of clicking your way around
the OEM oracle manager. Just because these tools are available doesn't
mean that the job of being a DBA has become mindless. The job has
become easier, and you may find it easier to do some of your tasks
in the GUI after you've tested and understand the command-line operations.
When you learn the commands, however, you will really know what
is happening and, more importantly, what the consequences are for
running these commands.
I recommend getting an account on one of your site's developmental
databases that has full DBA access under the conditions that you
are going to only run commands that "explore" the database. Both
Oracle and Sybase have lots of commands and functions that simply
give information about the database. You can make a list of commands
that are 100% harmless. I went a step further and downloaded lots
of scripts (you will have no problem finding them on the Internet)
that make harmless queries about tablespaces, datafiles, users,
and database configuration. I then mapped the physical files and
tried to match them to the logical datafiles in the database along
with the users who access them.
Once you begin to see what a real database looks like, you can
go into your Linux private test database and run all of those horrifying
commands that you wouldn't dare run on your company's database.
While running the test database, feel free to move datafiles, expand
them, delete them, and change configuration parameters. Everything
you try will lead you toward finding the correct syntax for a command
or understanding the consequences of running a command.
When I must run a new command or function that I have never used
on a production database, I always first experiment with the command
in the safety of the test environment. I'm often surprised that
there is some hitch to getting it to work correctly. Then, when
I work in the real production environment, I don't get caught off
guard by these quirks. Working in a production environment is no
game, and your reputation is on the line when you make important
changes. The customers also may be expecting you to perform the
task quickly.
Sybase Commands
Here are some Sybase commands that you can use to make your first
exploration of the database once you're logged in with full privileges.
You will find a lot of use for the System Procedures that have the
prefix "sp_".
To begin, you can see which databases exist. You will notice that
Sybase itself has created master, tempdb, sybsystempdb, and sybsystemprocs
(read about these on the online manual mentioned later on). Later
you will be creating and using "user" databases. If you are looking
at an existing database, you'll see at least one user database.
Here is the command to display your current databases:
1>sp_helpdb
2> go
Next, you can see all of the devices that are created. The devices
that are shown are used by the database for data and log files. You
may want to save the output of this command and study it. You will
now see where the Sybase disk devices live on your disks. This will
reveal a lot about how your database was designed:
1> sp_helpdevice
2> go
If you logged in as "sa", you'll be in the master database and you
can specify one of the databases that you found with sp_helpdb and
examine it. You move into the database with the use command.
Assume that we want to look into the "test101" database:
1> use test101
2> go
You can get more info about test101 with helpdb:
1> sp_helpdb test101
2> go
A command that shows the tables in a specific database is:
1> select name from sysobjects where type='u'
2> go
You can then find out more about one of the tables you found with
sp_help. Supposing that test101 has a table called artists:
1> sp_help artists
2> go
Sybase has an excellent online administration manual. You will be
able to solve many of your problems by simply searching in this book.
Your initial task will be to look up and test all of the available
"sp_" commands that do not change the database. Some of them create
logins and users, and give them privileges. You can experiment with
those commands on your test database. You can now start to gather
information and gain understanding of the database. Here is the link
to the manual:
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/ \
asesag/@Generic__BookView
Rob Verschoor's site is great. I recommend buying his book if you're
going to be doing a lot of Sybase work:
http://www.sypron.nl/download.html
Another helpful Sybase site containing basic quick tips is:
http://www.rocket99.com/sybase/sybase1.html
There are also a bunch of Sybase functions that are helpful when starting.
I've included some here, and you'll find a lot more in the manual.
Be careful to distinguish between the commands that simply make a
query and those that make changes in the database. Remember that at
this stage we are simply getting a feel for logging in and navigating
around the database. Try the following:
1> select db_name()
2> go
1> select db_id()
2> go
1> select user_id()
2> go
Working with Oracle
Learning about Oracle can be a lifelong journey. Oracle is heavy
duty and can be just as deep and complicated as learning, for example,
the Solaris operating environment. Your first step is to start looking
at your database. This should stimulate your interests and inspire
you to investigate further.
Oracle has two sets of queries for DBA usage that can show information
about your instance. One set has the dba_ prefix, and the other
has the v$ prefix. Between the two, you should be able to find all
you need to know about your database. The DBA views look into the
database dictionary, which describes the database. You need DBA
admin privileges to view these as they are not available to all
users (you also find a set of similar commands that can be used
as "user" to get information about database objects). I quickly
found lots of scripts that use these commands on the Internet. After
collecting about 30-40 scripts, I edited them a bit and now use
them to view the database. Before you do a select * from
one of the views, you can do a desc and see what you can
pull out of the DBA view:
sql> desc dba_tablespaces;
Then you can choose what information you want to get from that view:
sql> select tablespace_name from dba_tablespaces;
A select * will result in a lot of information, and it won't
be formatted for easy reading. You need to experiment with the formatting
commands to make your script output readable. Check out the COLUMN,
HEADING, and FORMAT commands. You can find examples of formatting
in the scores of scripts on the Internet.
In the following example, we use one of the V$ views to see datafiles.
With a bit of formatting, you're on your way to creating a script
so you don't have to type the command each time you want the information:
SQL> clear col
SQL> col name format a30
SQL> col status format a10
SQL> select name, status from v$datafile;
NAME STATUS
---------------------------------------- ----------
/u00/data1/fred/system01.dbf SYSTEM
/u00/data1/fred/undotbs01.dbf ONLINE
/u00/data1/fred/cwmlite01.dbf ONLINE
/u00/data1/fred/drsys01.dbf ONLINE
/u00/data1/fred/example01.dbf ONLINE
/u00/data1/fred/indx01.dbf ONLINE
/u00/data1/fred/odm01.dbf ONLINE
/u00/data1/fred/tools01.dbf ONLINE
/u00/data1/fred/users01.dbf ONLINE
/u00/data1/fred/xdb01.dbf ONLINE
Once you have a query that you like, place it in a file and run it
from sqlplus. If we save the previous commands to a file datafile_check.sql,
we can run it as follows:
SQL> @datafile_check.sql
The second alternative is to run it from the Unix command line. You
may want to add "exit" if you want to leave the sql shell after running
the script:
% sqlplus user_name/password @datafile_check.sql
I recommend creating a library of about 10-20 primitive scripts to
help you build up your knowledge of the database. Don't be intimidated
by the complexity of some of the scripts you'll find on the Internet
because you can get the information you need with some pretty basic
queries. Here are some basic views to begin investigating:
DBA_USERS: shows user info including tablespaces, profile,
password and more.
DBA_DATA_FILES: Shows your datafiles, status, tablespaces and more.
DBA_PROFILES: Shows your profiles (used for user accounts).
DBA_ROLES: Shows your roles (used for user accounts)
DBA_ROLE_PRIVS: Shows the grantor and grantee of a role
DBA_ROLLBACK_SEGS: Show info about rollback segments
DBA_TABLES: Gives lots of info about tables. Use qualifier
statements to get correct info.
DBA_TABLESPACES: Info and status for your tablespaces.
DBA_VIEWS: Info and status for views. Use qualifier statements
to get correct info.
DBA_INDEXES: Info and status for indexes. Use qualifier statements
to get correct info.
V$ARCHIVE: Show group status for your archive logs.
V$CONTROLFILE: Shows the name of status of your control file which
should be multiplexed.
V$LOG: Shows status of your archive logs.
V$INSTANCE: show the basics of your instance.
V$PARAMETER: show the parameters for the instance
You can also study the famous init.ora file, which contains the tunable
parameters for the database. Newer versions of Oracle allow for a
dynamically updated init.ora file, which is known as the spfile. You
should start by studying the file so that you can see what kind of
tunable options are available. If you are running the dynamic spfile,
you can dump the contents to a file. You should see things like the
three locations for your multiplexed control file, setting for memory
pools, and lots of other strange parameters. From the SQL prompt you
can also run:
SQL> show parameters
This will be the beginning of a lot of research if you want to understand
the options and parameters. Here you can begin to see what's in store.
The following forum has been quite helpful to me. I always confirm
any tips and tricks that I receive on my private test database before
running the solution on a production database:
http://www.dbasupport.com
Oracle's own help site is another important resource. Here, you'll
have to sign up and create an account:
http://metalink.oracle.com/
You will also find tons of Oracle manuals online containing information
for almost any problem that comes up. If you encounter a problem,
take the time to research it with the wealth of online materials:
http://www.oracle.com/technology/documentation/index.html
Conclusion
I hope that the information that I have provided will inspire
you to explore your Sybase and Oracle databases. I've introduced
a few commands to get you started, but it's up to you to create
your own scripts and queries to the database.
My final article will concentrate on common tasks, queries, and
backup of the database. I will look at some of the command tasks
used to create users and databases. You should consider backup as
it will be the only insurance against user or administrator errors.
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. | 
