Prompted by Linda’s comment on a previous blog, I thought it might be worth writing a couple of postings on DBA documentation.
The first thing you need is a Database Catalogue of some kind.
Benefits
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.
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.
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 😉
There are many benefits and you’ll come across new ones all the time once you’ve taken the time to create it.
Challenges
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.
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 🙁
I think this is one area of documentation where automating the process is the best approach.
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.
PSOFTP
PSOFTT
PSOFTD
For Peoplesoft Production, Test and Development, in that order.
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. 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.
Approach
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.
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.
DATABASE_NAME
RDBMS
VERSION
DESCRIPTION
HOST
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.
insert into mysite_catalogue values (NULL, NULL, NULL, ‘Peoplesoft Production – not live yet – contact Doug Burns’, NULL);
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,
- Query V$VERSION for version information or, even better, run opatch against each ORACLE_HOME to get more detailed information.
- Use uname to get the hostname.
- In the script, you will have seperate sections for different RDBMS, to populate that column with Oracle or MySQL or DB/2.
Having retrieved whatever information you choose, you UPDATE the row in the MYSITE_CATALOGUE table but (and this is the important bit) don’t 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.
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 work 😉
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.
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.
>> 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.
A good case for Application Express, perhaps.
Unsurprisingly the first solution that sprang into my mind was a factless multistar schema enabling SCD2-type tracking of o/s and DBMS versions and patch levels, instance types (P,R,D etc). For enterprise scale 1500 instance environments I’d be seriously considering it — not so for more modest organisations.
It would be interesting to build such a system and integrate it with statspack data pulled from all instances to a central repository — I wonder if that would enable tracking of performance of the same SQL across multiple P, T & D systems?
A good case for Application Express, perhaps.
Definitely, although I suspect most managers will still want to see it end up in Excel somehow, which I recall Apex can do quite easily.
It would be interesting to build such a system and integrate it with statspack data pulled from all instances to a central repository
Once you’ve got anything like this in place, it’s most useful aspect is that it’s easily extended. For example, most sites want some indication of space used by different environments, for planning future deployments.
Still getting to your wiki question, btw – a future posting, I reckon.
We… well mostly me with interesting tidbits from others, have built a system in our company that works pretty well.
We use LDAP for our TNS information, every oracle database is in that listing simply because nobody can access it outside of the server without it.
I have a schema in our middleware DB that is sole purpose in life is to connect to every database in the company and gather whatever we have coded it to gather. Everything from the DB version to the current SGA usage and about 30 points of data in between.
This does require some extra work. A schema with an incredibly long password is created in each DB and only has the necessary rights to run what we need it to run. If the SID exists in TNS and that schema does not exist, the checking job fails and emails flow.
Then there is an APEX application that sits in that schema and presents the data to the world. The SA’s have a report screen that has been developed for them so they can see what DB’s run on what server very quickly as well what DB versions and whatever other information they want. Us DBA’s can see basic information about the system and trend some things like user count and space usage pretty easily, not at great detail though.
The kicker comes from the prod/dev/test question. There is a table that somebody is supposed to fill out when a new DB is created. We had some instances of that not being filled out, and my manager (bless his soul) came up with a great idea. If no information is found in the DB_TYPE_INFO table, he has a dbms_job runnibg daily that emails the DBA group (and the manager) then submits a new job that checks in 12 hours. If still no information, then emails and submits for 6 hours, this loop continues down to a 5 minute submit loop.
Nice – another good example of what I’m talking about and you can make it as complex with as many features as you like, or very basic. For example, I haven’t mentioned passwords yet, but will in a future post.
I like this 😉
If still no information, then emails and submits for 6 hours, this loop continues down to a 5 minute submit loop.
Doug,
OEM is just for it. Don’t reinvent the bicycle.
And you are going to see – it does work. 😉 And you better believe it. LOL.
Cheers,
Alex
I agree, but then I have to agree with the boss 😉
Seriously, I know that something like OEM can work very well, but it has to be able to support everything and be implemented properly with some commitment, otherwise people will just by-pass it and end up using scripts. I’ve seen this too often.
However, I must say that at least one of our clients seems to have done a good job of it from what I’ve seen so far.
OEM is a such a huge time commitment. We have it running and do use it from time to time, for performance reporting. But it is so complicated and our system is so easy we simply rely on our stuff. Ours very rarely breaks and does not require that massive agent on every server.
But it does do a lot more than ours does, so it becomes a trade off.
The frustrating thing about OEM is we can’t figure out how to rename the OEM generated name for a target. Ours renames (via view) to what we want. For example. PSOFTP Becomes “People Soft Production DB” on the reports.
OEM is a such a huge time commitment. We have it running and do use it from time to time, for performance reporting. But it is so complicated and our system is so easy we simply rely on our stuff. Ours very rarely breaks and does not require that massive agent on every server.
A number of excellent points, which I think will reflect the thoughts of many DBAs at many Oracle sites.
As I mentioned, though, I’m working with one client who I would say is getting pretty good use out of it. I’ll probably change my mind about that later, though.
Apologies that I may be a few days late in commenting on this stuff, but going through this post, and your rant on documentation, Doug, has left me in ‘rant mode’ myself.
How can you all talk about documentation and cook books without EVER mentioning the word ‘metadata’?? As for cookbooks – the scourge of the industry, IMHO. They are a cheap shorthand way of documenting a task, and encourage a non-thinking approach. Now, before your own rant modes set in, I make a VERY CLEAR distinction between ‘documentation’ and ‘cookbooks’. The essence of the cookbook is, by definition, that you do NOT have to understand the theory of why you are doing something (the ‘what’) works. By contrast, good documentation explains the why and what.
Cookbooks can be summarised in the pithy sentence “don’t ask why you are being asked to do this – just bl**dy well do it!”
As for metadata, well, of course it needs to be in the database. Some of it is, of course (the Oracle stuff), but plenty of scope for your own systems metadata to go in there too. So why not the documentation too? Been there, done it, lectured on it years ago.
OK, rant over, back to sleep!
peter
Cookbooks can be summarised in the pithy sentence “don’t ask why you are being asked to do this – just bl**dy well do it!”
Ah, but sometimes that’s *exactly* what I want. Imagine a situation when you’re part of a team supporting several hundred databases. You know a couple of dozen intimately but sometimes you need to take emergency action on one of them because the guy who knows them intimately isn’t around. Isn’t it better to have a single document describing that action, all that I need to do, when I don’t actually care *why* because I’ll only do this once a year or so?
It might be ‘dumbing down’, but with the growth in the number of databases, that’s how it is. Of course, someone could write a document explaining precisely why I need to do everything, but I might not have time to read that, so will simply ignore it.
I suggest that there’s a scale of detail in documentation which ‘cookbook’ lies on and can serve a purpose.
Cookbooks are a horrible way to learn about databases, but a great way to achieve a single, focussed task quickly.
I reckon I just used one of your trigger words 😉
Oh, and I *knew* you’d mention metadata, but that isn’t going to be much help for a class of problems such as recovering a database on to a different server using a variety of tools. That’s a procedure and should therefore be a list of detailed instructions.
Yup, you got the trigger word…
Doug, I think you are confusing ‘cookbook’ with ‘prompt list’. Later are fine – used them myself lots of time. These two are NOT the same.
The real trigger combinations would be ‘dummies cookbook on RDBMS…’ sigh!
peter