Prompted by Linda’s comment on a previous blog, I thought it might be worth writing a couple of postings on DBA documentation.<\/p>\n
The first thing you need is a Database Catalogue of some kind.<\/p>\n
Benefits<\/b>
1) Even an experienced DBA arriving on site won’t know what servers exist, how to login to them, what versions you are using and so on unless they have a crystal ball. Once they have those details, they can login to the various servers to familiarise themselves with your environment.<\/p>\n
2) When you start to support more than a couple of hundred databases, you’re not going to be able to memorise the details. At Sun, for example, our team supported over 1500 databases and I can’t be more specific about the number because it was changing all the time.<\/p>\n
3) Although I’m not a manager and don’t intend to address their requirements in detail here, they are usually keen on having a good overall impression of the team’s workload and the number of databases. What RDBMS they use and specific patch levels are also crucial. For example, when my manager finds that a version of Oracle is going to be desupported, he’ll want to know how many databases this will affect, and quickly. He pays the wages, remember 😉<\/p>\n
There are many benefits and you’ll come across new ones all the time once you’ve taken the time to create it.<\/p>\n
Challenges<\/b>
The difficulty with maintaining an accurate Database Catalogue is that things will change. New servers are deployed, old ones decommissioned and databases are being created all the time for new development and testing requirements.<\/p>\n
At the site I’ve just left, we originally used a spreadsheet to maintain our database catalogue. It was simple and easy to update and yet, despite that, some DBAs were better at keeping it up to date than others. Someone might perform an upgrade and forget to update the spreadsheet. Databases that were completely absent from the spreadsheet were even more frustrating. That seems shabby to me, but I suppose when people are busy, the documentation tasks are the first to be abandoned 🙁<\/p>\n
I think this is one area of documentation where automating the process is the best approach.<\/p>\n
However, if the process is automated, how can we decide whether a database is Production (or Live) and critical to the business? Perhaps there’s some way we can name our databases to indicate so, perhaps by using a P at the end? e.g.<\/p>\n
PSOFTP
PSOFTT
PSOFTD<\/p>\n
For Peoplesoft Production, Test and Development, in that order.<\/p>\n
This is a reasonable approach, but what you’ll find is that you support databases that are ‘kind of’ Production. That’s just one of the trials of DBA life, I’m afraid. In fact, you have to create a new database to populate with data in advance of the go live date, so you’re bound to have a database with a P at the end which isn’t Production yet<\/i>. You’ll also find that database naming standards have changed over time and you’ll probably have some old legacy databases that don’t follow the standard. That’s just a couple of the reasons that I don’t think a Database Catalogue can be generated entirely automatically. Well, it can, but you must be able to update it manually.<\/p>\n
Approach<\/b>
Here’s one possible approach, based on the site I’ve just left. It’s not all my own work and I’m not going to say exactly what we did in any case, but it’ll hopefully give you some ideas for your own.<\/p>\n
First, in every database, create a table called MYSITE_CATALOGUE (or CATALOG, if that’s how you spell it). It might look something like this. <\/p>\n
DATABASE_NAME
RDBMS
VERSION
DESCRIPTION
HOST<\/p>\n
Then insert a row into that table once, paying particular attention to the DESCRIPTION column, because that gives you the opportunity to express useful information that only a human would understand. e.g.<\/p>\n
insert into mysite_catalogue values (NULL, NULL, NULL, ‘Peoplesoft Production – not live yet – contact Doug Burns’, NULL);<\/p>\n
So what’s with all the NULLs? Well, most of this information is going to be populated automatically. You’ll need a server that can access all of your other servers using ssh and, more specifically, scp. (I’m used to predominantly Unix environments, but those with more Windows knowledge can fill in the blanks.) You write a script and place it on that server where it can be transferred to each of the remote servers and, for each pmon process it finds running there, connect to that instance and retrieve information to populate the MYSITE_CATALOGUE table. For example,<\/p>\n
Having retrieved whatever information you choose, you UPDATE the row in the MYSITE_CATALOGUE table but (and this is the important bit) don’t<\/b> update the description column. That way, you pick up any new information about the database (particularly version and patch level information) but retain the carefully crafted human-generated description. Having updated the row, you return the values to the central server, which moves on to the next server. At the end of the process, the central server will contain a flat file with information about all of the databases. That file can then be loaded into a table in a database, which can be queried using ODBC connection to populate a spreadsheet, or can just be opened in a spreadsheet program, if you prefer.<\/p>\n
What happens when the PSOFTP database finally moves to Production status? We issue one update statement to update the DESCRIPTION column to reflect that. If people forget to make the necessary updates, it will be very clear from any global view of the data and can be fixed. Ultimately, if you want some data that contains the human touch, then humans have to do some<\/i> work 😉<\/p>\n
I hope it’s obvious that, although this isn’t a complete solution, it offers a base for you to develop your own preferred solution. Believe me, once you get started, you’ll find the benefits encourage you to implement minor improvements and your manager won’t mind the time spent, because they benefit too.<\/p>\n
Oh, one final thing. Oracle’s OEM Grid Control makes this job much more straightforward, so use that if it’s an option for you.<\/p>\n","protected":false},"excerpt":{"rendered":"