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, [EMAIL PROTECTED] at [EMAIL PROTECTED] 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 > [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: Tim Gorman 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).
