| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: Documenting databases
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
alan.aschenbrenner_at_ihs.com
"Stephane
Faroult" To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
<sfaroult_at_oriolec cc:
orp.com> Subject: RE: Re: Documenting databases
Sent by:
ml-errors_at_fatcity
.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: alan.aschenbrenner_at_ihs.com
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>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
>
>
> <hanksdc_at_about-in To:
> Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
> c.com>
cc:
>
>
> Sent by:
>Subject: Re: Documenting databases
>
> ml-errors_at_fatcity
>
>
> .com
>
>
>
>
>
>
>
>
> 12/09/2003 04:09
>
>
> PM
>
>
> Please respond to
>
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
>On Tue, 9 Dec 2003 alan.aschenbrenner_at_ihs.com
>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: hanksdc_at_about-inc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: sfaroult_at_oriolecorp.com 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: ListGuru_at_fatcity.com (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: alan.aschenbrenner_at_ihs.com 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: ListGuru_at_fatcity.com (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).Received on Wed Dec 10 2003 - 17:39:26 CST
 |
 |