Health Check on Database 11G

Health Check on Database 

To check for the control files and to have at least two copies of the controlfile

connect as sysdba
SQL> select status, name from v$controlfile;

At least two redo log groups are required

SQL> select * from v$logfile;

To check archive configuration

SQL> archive log list

To determine if a datafile and thus, a tablespace, has AUTOEXTEND capabilities:

SQL> select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name from dba_data_files where autoextensible = 'YES';

To verify location of datafiles

SQL> select * from v$dbfile;

To check user objects that should not be created in the system tablespace

SQL> select owner, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');

To check which components are occupying space:

 SQL> select space_usage_kbytes, occupant_name, occupant_desc
from v$sysaux_occupants
order by 1 desc;

To verify which tablespace is Locally Managed or Dictionary Managed:

SQL> select tablespace_name, extent_management
from dba_tablespaces;

To view tablespaces

SQL> select tablespace_name, contents
from dba_tablespaces;

To query lists of all users that have a permanent tablespace specified as their default temporary tablespace.

SQL> select u.username, t.tablespace_name
from dba_users u, dba_tablespaces t
where u.temporary_tablespace = t.tablespace_name
and t.contents <> 'TEMPORARY';

To show the SYSTEM tablespace as default temporary tablespace. This value can be altered as well to prevent fragmentation in the SYSTEM tablespace.

SQL> alter user SYSTEM temporary tablespace TEMP

To get the size of the temporary tablespace:

SQL> select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;

To get the "high water mark" of that temporary tablespace (= max used at one time):

SQL> select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;

 To get  current usage:

SQL> select ss.tablespace_name, 
sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;

To query list of all objects that have allocated more extents than a specified minimum. Change the <--minext--> value by an actual number, in general objects allocating more then 100 a 200 extents can be recreated with larger extent sizes:

SQL> select owner, segment_type, segment_name, tablespace_name,
count(blocks), SUM(bytes/1024) "BYTES K", SUM(blocks)
from dba_extents
where owner NOT IN ('SYS','SYSTEM')
group by owner, segment_type, segment_name, tablespace_name
having count(*) > <--minext-->>
order by segment_type, segment_name;

To query all segments that are unable to allocate their next extent :

select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);

Query to show if there are not enough rollback segments online or if the rollback segments are too small.

SQL> select d.segment_name, d.tablespace_name, s.waits, s.shrinks,
s.wraps, s.status
from v$rollstat s, dba_rollback_segs d
where s.usn = d.segment_id
order by 1;

The WAITS indicates which rollback segment headers had waits for them. Typically you would want to reduce such contention by adding rollback segments. 

If SHRINKS is non zero then the OPTIMAL parameter is set for that particular rollback segment, or a DBA explicitly issued a shrink on the rollback segment.
The number of shrinks indicates the number of times a rollback segment shrinked because a transaction has extended it beyond the OPTIMAL size. If this value is too high then the value of the OPTIMAL size should be increased as well as the overall size of the rollback segment (the value of minextents can be increased or the extent size itself, this depends mostly on the indications of the WRAPS column).

The WRAPS column indicate the number of times the rollback segment wrapped to another extent to serve the transaction. If this number is significant then you need to increase the extent size of the rollback segment.

Oracle 11g

Automatic Memory Management (AMM) is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

Oracle 12c

You can monitor SGA memory usage on pluggable databases using queries against database views.

11g and above: Alert File

SQL> show parameter diagnostic_dest

NAME TYPE VALUE
------------------------------ ------- ----------------------------------
diagnostic_dest string /oracle/admin/L111

Pre-11g: 

show parameter background_dump_dest

NAME TYPE VALUE
------------------------------ ------- ----------------------------------
background_dump_dest string D:\Oradata\Admin\PROD\Trace\BDump

Make sure the disk space can handle the maximum size specified, if not then this value should be changed.

SQL> show parameter max_dump_file_size

NAME TYPE VALUE
---------------------------------- ------- ---------------------
max_dump_file_size integer 10240

Audit files

By default, every connection as SYS or SYSDBA is logged in an operating system file. 
The location is controlled through the parameter 'audit_file_dest'. If this parameter is not set then the location defaults to $ORACLE_HOME/rdbms/audit. Overtime this directory may contain a lot of auditing information and can take up a significant amount of space.

Advanced Health Checking

The previous sections have been outlining the basic items to check to prevent common database caveats. In this section you will find references to several MOS articles explaining how a more in depth analyses and monitoring can be achieved. These article mainly focus on Data Dictionary Integrity and DataBase structure verification.

Oracle Pre-11g:

Note 456468.1 - Identify Data Dictionary Inconsistency 
NOTE 136697.1 - "hcheck8i.sql" script to check for known problems in racle8i,Oracle9i, and Oracle10g


Oracle 11g

Note 466920.1: 11g New Feature Health monitor 

REFERENCES

NOTE:249664.1 - Pfile vs SPfile
NOTE:105120.1 - Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
NOTE:115424.1 - How to Rename or Move Datafiles and Logfiles
NOTE:122555.1 - Determine How Much Disk Space is Needed for the Archive Files
NOTE:136697.1 - "hcheck.sql" script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g
NOTE:147356.1 - How to Move Tables from One Tablespace to Another.
NOTE:112011.1 - ALERT: RESIZE or AUTOEXTEND can "Over-size" Datafiles and Corrupt the Dictionary
NOTE:262066.1 - How To Size UNDO Tablespace For Automatic Undo Management
NOTE:262472.1 - 10g: BIGFILE Type Tablespaces Versus SMALLFILE Type
NOTE:311615.1 - Oracle 10G new feature - Automatic Undo Retention Tuning
NOTE:329984.1 - Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER
NOTE:443746.1 - Automatic Memory Management (AMM) on 11g
NOTE:456468.1 - Identify Data Dictionary Inconsistency


NOTE:62005.1 - Creating, Optimizing, and Understanding Rollback Segments
NOTE:69739.1 - How to Turn Archiving ON and OFF in Oracle RDBMS
NOTE:564989.1 - How To Truncate a Background Trace File Without Bouncing the Database
NOTE:93771.1 - Introduction to Locally-Managed Tablespaces
NOTE:1012431.6 - Overview of Database Fragmentation in Oracle 7
NOTE:1020182.6 - Script to Detect Tablespace Fragmentation
NOTE:102995.1 - Maintenance of Online Redo Log Groups and Members

Additional References:
Note: 122669.1 How to Perform a Health Check on the Database 

Note: 1417774.1 FAQ: SQL Health Check (SQLHC) Frequently Asked Questions

Note: 1366133.1 SQL Tuning Health-Check Script (SQLHC)

Note: 868955.1 Get Proactive - Oracle Health Checks - Installation, troubleshooting, catalog and more.