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).
