Thanks for all the responses. As I figured, everyone has a different
method, but the common thread seems to be automation. I'm not sure exactly
what approach I'm going to take yet, but I'll definitely try to automate
it as much as possible...
I'll add one of my own documentation tricks that might be useful for large
sites. I have a graphical diagram (created in Visio) that I keep up to
date with our database servers. Under each server is a list of databases
running on it and other minor details (like Oracle release, versions of
applications it supports, etc).. Finally, I have lines drawn between the
servers/databases documenting triggers that update remote databases,
advanced replication/streams, automated export/imports, or other important
interactions. Especially if you have 50-100+ databases, it can be tough to
remember where every database lives and what relationships exist between
them. It's also great to give to managers who love diagrams... :-)
Thanks again,
Alan
Alan Aschenbrenner
Oracle DBA
IHS Group
[EMAIL PROTECTED]
"Stephane
Faroult" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
orp.com> Subject: RE: Re: Documenting databases
Sent by:
[EMAIL PROTECTED]
.com
12/10/2003 06:29
AM
Please respond to
ORACLE-L
Alan,
The IT departments of several sites, hitherto fairly independent, have
all been brought under a single roof at one of my customers and as a result
a lot of databases have fallen into the herd of databases we had to manage
there.
IMHO the key point to inventory is automation; if you don't automate, it
will never stay up-to-date.
First of all, get hold of some platform for scripting.
<VERY VERY SMALL>I don't know perl,</VERY VERY SMALL><MICROSCOPIC> I don't
even plan to learn it any soon</MICROSCOPIC> and as I feel comfortable with
ksh, sed, awk and the like I jumped on a Unix platform, but your choice may
be different.
The first challenge in our case was to build an inventory of databases
(asking people is totally unreliable); I have used scripts from Tim Gorman
which you will find on his site (http://www.evdbt.com) - from a security
paper, which I have reworked to suit my case. The idea was to probe the
network (fortunately all servers are supposed to follow a special address
pattern) and check for listeners, and send the lsnrctl stat command. This
helps you identify servers, listeners, and instances. A suitable schema was
built into a database (Oracle, but see below) to store this; note that
relationships are sometimes not very simple, since a same instance can be
served by several listeners.
Next step was to secure a foothold into each database to execute inventory
queries (it has been a good opportunity to check security too).
DBSNMP/DBSNMP is a good bet. Actually, we created a special MONITOR account
on each database, with only the minimum rights required.
Everyday a script runs, which checks V$DATABASE, V$INSTANCE, V$LICENCE,
V$VERSION (the only place BTW when you find some indication about which OS
you are running on), getting information and updating it if required.
Storage is of course checked as well. Database links are collected too. We
have a PHP application displaying all the information (with the refresh
date), conveniently crossed (for instance, we list for each database the
dblinks to the database as well as the dblinks from the database). We have
some summary PDF reports (storage, databases per OS, per version, etc.)
which are printed every week. We are also linking to a (static) inventory
of applications.
It's still work in progress. We have recently added a connection test
every 15mn to check database availability (trying a non-existent user. If
we don't get ORA-1917 we try to ping the server and tnsping the listener to
pinpoint the reason for the problem - of course we skip the other databases
on the server if we can't ping it) and compute some availability percentage
figure. We also intend to collect some metrics at regular intervals to have
an idea about the load.
I have nothing against using Access to store the data; in fact, some of
the ideas were borrowed from another customer where the repository is a
Sybase database (TCL scripts do a full inventory of both the Sybase and
Oracle databases - several hundreds of them). But, once again, do it
AUTOMATICALLY.
HTH
Stephane Faroult
>----- ------- Original Message ------- -----
>From: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>Sent: Tue, 09 Dec 2003 15:34:32
>
>
>
>
>
>Dan,
>
> That's a good idea for documenting structures
>inside the database.
>However, my database manager wants more high level
>info: database name /
>host, oracle version, listeners, applications that
>use it, cron job
>descriptions and times, main schemas and what they
>are used for, lists of
>developers names that access the databse, etc...
>
>Alan
>
>
>
>
>
>
> Daniel Hanks
>
>
> <[EMAIL PROTECTED] To:
> Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
> c.com>
cc:
>
>
> Sent by:
>Subject: Re: Documenting databases
>
> [EMAIL PROTECTED]
>
>
> .com
>
>
>
>
>
>
>
>
> 12/09/2003 04:09
>
>
> PM
>
>
> Please respond to
>
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
>On Tue, 9 Dec 2003 [EMAIL PROTECTED]
>wrote:
>
>> Recently our database manager has asked us to
>do the unthinkable....
>> document our databases! To make matters worse,
>and without our input, he
>> went ahead and created a schema and put it in an
>Access database (using
>> tables to make it look like a speadsheet).
>Either we use his idea or
>come
>> up with something else.
>> So, I thought I'd ask everyone on the list
>how you do it. Text
>files?
>> In a database (oracle, or other)? Spreadsheets?
> What are the pros and
>> cons? Etc....
>>
>
>How about in each database itself.
>
>COMMENT ON TABLE|COLUMN tab|tab.col IS '...'
>
>comes to mind. It's simplistic, yes, but at least
>you don't have to
>remember where you put your documentation...
>
>HTH,
>
>-- Dan
> Daniel Hanks - Systems/Database Administrator
> About Inc., Web Services Division
>--
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.net
>--
>Author: Daniel Hanks
> INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
