| 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: Documenting databases
I apologize if someone already mentioned this (I've been busy so mostly
deleting traffic in order to keep from being overwhelmed!), but did anyone
mention the DBMS_METADATA package for this?
Looks pretty cool, mostly intended for generated XML output but also generates DDL commands...
on 12/10/03 4:39 PM, alan.aschenbrenner_at_ihs.com at alan.aschenbrenner_at_ihs.com wrote:
>
>
>
>
> 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>
>>
>> >> 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: Tim Gorman INET: tim_at_sagelogix.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 - 22:14:25 CST
 |
 |