orawin.info

Niall's Oracle Pages – Oracle Opinion since 2004

When is a Health Check not a Health Check

with 3 comments

Thanks to The Human Fly via Twitter @sjaffarhussain I see that Oracle Corporation have a published note on How to Perform a Database Health Check. (Note 122669.1). I read this with some interest as this is something that I do quite frequently as part of my day job. (If you’d like to get me to take a look at one of your databases drop us a line). Here is the contents list of that note.
1. Parameter file
2. Controlfiles
3. Redolog files
4. Archiving
5. Datafiles
5.1 Autoextend
5.2 Location
6. Tablespaces
6.1 SYSTEM Tablespace
6.2 SYSAUX Tablespace (10g Release and above)
6.3 Locally vs Dictionary Managed Tablespaces
6.4 Temporary Tablespace
6.5 Tablespace Fragmentation
7. Objects
7.1 Number of Extents
7.2 Next extent
7.3 Indexes
8. AUTO vs MANUAL undo
8.1 AUTO UNDO
8.2 MANUAL UNDO
9. Memory Management
9.1 Pre Oracle 9i
9.2 Oracle 9i
9.3 Oracle 10g
9.4 Oracle 11g
10. Logging & Tracing
10.1 Alert File
10.2 Max_dump_file_size
10.3 User and core dump size parameters
10.4 Audit files
The paper introduces each of these areas – so for example the section on control files reads (in it’s entirety)
It is highly recommended to have at least two copies of the controlfile. This can be done by mirroring the controlfile, strongly recommended on different physical disks. If a controlfile is lost, due to a disk crash for example, then you can use the mirrored file to startup the database. In this way fast and easy recovery from controlfile loss is obtained.
connect as sysdba
SQL> select status, name from v$controlfile;

STATUS NAME

------- ---------------------------------

/u01/oradata/L102/control01.ctl

/u02/oradata/L102/control02.ctl

The location and the number of controlfiles can be controlled by the
‘control_files’ initialization parameter.

I’m rather a pedant so would rather the document be described as a “Best Practices” or “Guidelines “document than a how to. A How to document would reword the above as

Ensure that the database has at least 2 controlfiles located on different disks by reviewing the ‘control_files’ initialisation parameter.

I.E it told you How to do something, hence the name. But my beef isn’t really that this is yet another Best Practices document, but rather with what it leaves out. This is perhaps best illustrated by referring you to this forum thread. A health check that doesn’t ensure recoverability is no health check.

Possibly Related Posts:

Written by Niall Litchfield

May 16th, 2011 at 8:34 am

Posted in Uncategorized

Tagged with

«
»

3 Responses to 'When is a Health Check not a Health Check'

Subscribe to comments with RSS or TrackBack to 'When is a Health Check not a Health Check'.

  1. I read stuff like that and I think “when was the last time someone actually used manual archiving?” I for one have never seen it aside from a blown rfs process and can hardly imagine it as a normal op.

    Remember this?

    SET ECHO off 
REM NAME:  TFSMSTAT.SQL 
REM USAGE:"@path/tfsmstat.sql" 
REM -------------------------------------------------------------------------- 
REM REQUIREMENTS: 
REM    Should be run as a DBA 
REM  
REM -------------------------------------------------------------------------- 
REM AUTHOR:  
REM    Marlene L. Theriault - Oracle Corporation - 02 NOV 1993 - orig 
REM    Martin D. Rosman     - Oracle Corporation - 14 MAR 1996 - update 
REM -------------------------------------------------------------------------- 
REM PURPOSE: 
REM    This script examines various V$ parameters.  The script makes  
REM    suggestions on mods that can be made to your system if specific 
REM    conditions exist. The report should be run after the system has been  
REM    up for at least 10 hours and should be run over a period of time to  
REM    get a real feel for what the real condition of the database is.  A  
REM    one-time sample run on an intactive system will not give an accurate 
REM    picture of what is really occuring within the database.  
REM  
REM    If the database is shut down on a nightly basis for backups, the script 
REM     can be run just prior to shutdown each night to enable trending 
REM    analysis.  
REM  
REM    This script can be run on any platform but is tailored to evaluate an  
REM    Oracle7.x database.  The script assumes that you are running it from a  
REM    DBA account where CATDBSYN.SQL has been run. 

...

    joel garry

    20 May 11 at 12:01 am

  2. Thanks for dropping by Joel, part of the trouble for sure is that old material stays around for much longer than it should, most of the trouble though seems to be a “best practices” attitude – if my database is using features x, y and z all must be well – without any attention to appropriateness and if they are being correctly used.

    by the way I added [ code ] tags to your post to make the sql script more readable – hope that’s ok.

    Niall Litchfield

    20 May 11 at 6:03 am

  3. when was the last time someone actually used manual archiving?

    Funny enough, the LOG_ARCHIVE_START is deprecated, but we could still try to stop the archiver

    SQL> archive log stop
    Statement processed.

    But if does not stop anything actually…

    SQL> archive log list
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination /u01/app/oracle/admin/DB001/arch
    Oldest online log sequence 8080
    Next log sequence to archive 8083
    Current log sequence 8083

    Laurent Schneider

    23 May 11 at 12:15 pm

Leave a Reply