Database Health Check - Additional Checks?

Follow / 12 Jun 2018 at 1:50pm

I've been doing a few database health checks recently, and I've found Toads DB Health Check to be rather helpful. However, there are a couple of missing (in my opinion) useful checks that might be able to be added at some future point. These are:

  • Check that sequences have a decent sized cache_size to avoid contention on the dictionary. I normally look for about 20 in the cache, but perhaps this is too high for some, or too low for others, so perhaps 10-15 would be a good starting point?

  • Check that a file can be created (and hopefully deleted again) using UTL_FILE, for example, to access all the server paths defined in DBA_DIRECTORIES. This will ensure that they are all present. This might be difficult if the user executing said checks doesn't themselves have READ, WRITE access to the directories, but useful all the same. Perhaps a message could be displayed if the executing user is not the owner and/or doesn't have the required privileges.

  • If any rows in V$LOGFILE have a TYPE of 'STANDBY', then we should assume that this database is able to be run as a standby. To this end, check that the database is running in FORCED LOGGING and ARCHIVELOG mode - otherwise, we are not going to be of much use when we switch over.

  • Also, if any rows in V$LOGFILE have a TYPE of 'STANDBY', then if we have log file GROUPs defined, we should have as many STANDBY groups as we have ONLINE groups plus the count of rows from V$THREAD. In other words, because English doesn't come easy to a Scotsman, if there are 5 ONLINE groups and one row in V$THREAD, we should have 5+1=6 (No! Really?) STANDBY groups.

  • Alternatively, if there are STANDBY logs present, but no redo logfile groups present, then we should have as many STANDBY logs as we have ONLINE logs plus the number of rows in V$THREAD.

  • And finally, this one can be quite a nightmare, I've come across too any databases where a check constraint is defined as "CHECK column_name IN ('Y','N',NULL)" - which as we all know means that all bets are off, and any value at all can be stored in said column. What I normally do in SQL is:

create global temporary table check_constraints on commit preserve rows
as (
    -- SEARCH_CONITION is a LONG data type, hence the need for a table.
    select owner, table_name, constraint_name, to_lob(search_condition) as search_condition
    from dba_constraints
    where owner not in (select username from dba_user.daily_stats_exclusions)
    and table_name not like 'BIN$%'
    and constraint_type = 'C'
) ;

delete from check_constraints where upper(search_condition) not like '% IN %NULL%';
commit;

select count(*) from check_constraints;
select * from check_constraints;

truncate table check_constraints;
drop table check_constraints;

I obviously have a good look at what's left in the table before I truncate and drop  it - to see if any rows remaining are of the above listed and very broken construct.

--

Cheers,

Norm. [TeamT]

Replies