In our Remote DBA practice, we frequently perform comprehensive system reviews for our customers on their database services. Among the things we always check for are non-default settings for the database software. We want to validate that any non-default setting is set that way for a good reason, and that any setting that is default really should be that way.
In Oracle, this is easy. The
gv$parameter view has a column,
ISDEFAULT, that allows a simple SQL query to show which settings are set to non-default values.
It’s not so easy in SQL Server. There is a view, master.sys.configurations, but it doesn’t have a way to tell if the setting is default or modified or anything.
Interesting question on human mistakes was posted on the DBA Managers Forum discussions today.
As human beings, we are sometimes make mistakes. How do you make sure that your employees won’t make mistakes and cause downtime/data loss/etc on your critical production systems?
I don’t think we can avoid this technically, probably working procedures is the solution.
I’d like to hear your thoughts.
I typed my thoughts and as I was finishing, I thought that it makes sense to post it on the blog too so here we go…
The keys to prevent mistakes are low stress levels, clear communications and established processes. Not a complete list but I think these are the top things to reduce the number of mistakes we make managing data infrastructure or for that matter working in any critical environment be it IT administration, aviation engineering or medical surgery field. It’s also a matter of personality fit – depending on your balance between mistakes tolerance and agility required, you will favor hiring one individual or another.
Regardless of how much you try, there are still going to be human errors and you have to account for them in the infrastructure design and processes. The real disasters happen when many things align like several failure combined with few human mistakes. The challenge is to find the right balance between efforts invested in making no mistakes and efforts invested into making your environment errors-proof to the point when risk or human mistake is acceptable to the business.
Those are the general ideas.
Just a few examples of the practical solutions to prevent mistakes when it comes to Oracle DBA:
Some of the items to limit impact of the mistakes:
Both lists can go on very long. Old article authored by Paul Vallee is very relevant top this topic — The Seven Deadly Habits of a DBA…and how to cure them.
Feel free to post your thoughts and example. How do you approach human mistakes in managing production data infrastructure?
Before launching into this, I must give due deference to Mogens Nørgaard’s landmark article, You Probably Don’t Need RAC (YPDNR), available here, but originally published Q3 2003 in IOUG Select Journal. Mogens showed that you can be a friend of Oracle without always agreeing with everything they do.
SimpleTalk have just published another of my SQL Server articles – which looks at the way that SQL Server stores data in “clustered indexes”, and the variation that appears depending on the pattern of data.
This afternoon I’ve been cleaning up some data in an SQL Server database. I decided to use SQL*Developer to connect to SQL Server by following this post.
I made liberal use of the following tip when dealing with TEXT and NTEXT types.
The joys of dealing with multiple engines…
In Microsoft SQL Server, session IDs of 50 or less are dedicated background threads. These are analogous to Oracle’s background processes. Suppose you needed to obtain the Windows thread ID of a SQL server background thread. Just try finding out how to do that in the Microsoft documentation or by Googling. I tried, and came up empty-handed.
Why might you need to know this kind of thing? Well suppose one of those threads was misbehaving or hanging. You would need to debug it and obtain a stack trace. For that you would need the OS thread ID.
A little sleuthing shows that you need to query three dynamic system views: sys.dm_os_threads, sys.dm_os_workers and sys.dm_exec_requests. The following SQL should do the trick in MSSS 2005 and 2008:
SimpleTalk have just published another of my SQL Server articles – which talks about storage methods and block dumping in SQL Server, and ends with the suggestion that the reason that SQL Server DBAs seem to be very keen on “clustered indexes” may be related to the fact that SQL Server doesn’t seem to handle [...]
Continuing my little list of things in SQL Server that I’d like to see in Oracle: 1 Index statistics: when SQL Server collects index statistics, it also collects stats not only on the full index definition, but also on every “prefix” of the index, i.e. if you have a 4-column index, you will get stats [...]
I pointed out some time ago a few of the things in SQL Server that I would like to see in Oracle. Here’s a couple more: Executions Plans Insert, update, and delete execution plans show the indexed access paths used to check the side effects of referential integrity constraints. (I haven’t checked to see what [...]