Database - Enterprise Edition - Version 220.127.116.11 and later
Information in this document applies to any platform.
This article explains how to perform. a BASIC Health Check on the database verifying
several configuration issues. General guidelines are given on what areas to investigate
to get a better overview on how the database is working and evolving. These guidelines
will reveal common issues regarding configuration as well as problems that may occur in the future.
For a more in depth health check to check Database structure and data dictionary integrity,
please follow the appropriate links in chapter 11.
The areas investigated here are mostly based on scripts and are brought to you without
any warranty, these scripts may need to be adapted for next database releases and features.
This article will probably need to be extended to serve specific application need0s/checks.
Although some performance areas are discussed in this article, it is not the intention
of this article to give a full detailed explanation of optimizing the database performance.
1. Parameter file
3. Redolog files
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.1 Number of Extents
7.2 Next extent
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.3 User and core dump size parameters
10.4 Audit files
11. Advanced Health Checking
The parameter file can exists in 2 forms. First of all we have the text-based version, commonly referred to as init.ora or pfile, and a binary-based file, commonly referred to as spfile. The pfile can be adjusted using a standard Operating System editor, while the spfile needs to be managed through the instance itself.
It is important to
realize that the spfile takes presedence above the pfile, meaning whenever
there is an spfile available this will be automatically taken unless specified
NOTE: Getting an RDA report after making changes to the database configuration is also a recommendation. Keeping historical RDA reports will ensure you have an overview of the database configuration as the database evolves.
Note 249664.1 Pfile vs SPfile
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;
The location and the number of controlfiles can be controlled by the 'control_files' initialization parameter.
The Oracle server maintains online redo log files to minimize loss of data in the database. Redo log files are used in a situation such as instance failure to recover commited data that has not yet been written to the data files. Mirroring the redo log files, strongly recommended on different physical disks, makes recovery more easy in case one of the redo log files is lost due to a disk crash, user delete, etc.
connect as sysdba
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
--------- ------- ------ -----------------------------------
1 ONLINE /u01/oradata/L102/redo01_A.log
1 ONLINE /u02/oradata/L102/redo01_B.log
2 ONLINE /u01/oradata/L102/redo02_A.log
2 ONLINE /u02/oradata/L102/redo02_B.log
3 ONLINE /u01/oradata/L102/redo03_A.log
3 ONLINE /u02/oradata/L102/redo03_B.log
At least two redo log groups are required, although it is advisable to have at least three redo log groups when archiving is enabled (see the following chapter). It is common, in environments where there are intensive log switches, to see the ARCHiver background process fall behind of the LGWR background process. In this case the LGWR process needs to wait for the ARCH process to complete archiving the redo log file.
Note 102995.1 Maintenance of Online Redo Log Groups and Members
the mechanism needed to backup the changes of the database. The archive files
are essential in providing the necessary information to recover the database.
It is advisable to run the database in archive log mode, although you may have
reasons for not doing this, for example in case of a TEST environment where you
accept to loose the changes made between the current time and the last backup.
You may ignore this chapter when the database doesn't run in archive log mode.
There are several ways of checking the archive configuration, below is one of them:
connect as sysdba
SQL> archive log list
Database log mode No Archive Mode --OR-- Archive Mode
Automatic archival Disabled --OR-- Enabled
Oldest online log sequence seq. no
Current log sequence seq. no
Pre-10g, if the database is running in archive log mode but the automatic archiver process is disabled, then you were required to manually archive the redolog files.
If this is not done in time then the database is frozen and any activity is prevented.
Therefore you should enable automatic archiving when the database is running in archive log mode. This can be done by setting the 'log_archive_start' parameter to true in the parameter file.
Starting from 10g, this parameter became obsolete and is no longer required to be set explicitly. It is important that there is enough free space on the dedicated disk(s) for the archive files, otherwise the ARCHiver process can't write and a crash is inevitable.
Note 69739.1 How to Turn Archiving ON and OFF
Note 122555.1 Determine how many disk space is needed for the archive files
command option enables or disables the automatic extension of data files. If
the given datafile is unable to allocate the space needed, it can increase the
size of the datafile to make space for objects to grow.
A standard Oracle datafile can have, at most, 4194303 Oracle datablocks.
So this also implies that the maximum size is dependant on the Oracle Block size used.
Mb value to use in any command
2048 8191 M
4096 16383 M
8192 32767 M
16384 65535 M
Starting from Oracle 10g, we have a new functionality called BIGFILE, which allows for bigger files to be created. Please also consider that every Operating System has its limits, therefore you should make sure that the maximum size of a datafile cannot be extended past the Operating System allowed limit.
To determine if a datafile and thus, a tablespace, has AUTOEXTEND capabilities:
file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name
where autoextensible = 'YES';
location of your datafiles. Overtime a database will grow and datafiles may be
added to the database. Avoid placing datafiles on a 'wherever there is space'
basis as this will complicate backup strategies and maintenance.
Below is an example of bad usage:
SQL> select *
Note 115424.1 How to Rename or Move Datafiles and Logfiles
User objects should not be created in the system tablespace. Doing so can lead to unnecessary fragmentation and preventing system tables of growing. The following query returns a list of objects that are created in the system tablespace but not owned by SYS or SYSTEM.
owner, segment_name, segment_type
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');
tablespace was automatically installed as an auxiliary tablespace to the SYSTEM
tablespace when you created or upgraded the database. Some database components
that formerly created and used separate tablespaces now occupy the SYSAUX
If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.
The amount of data stored in this tablespace can be significant and may grow over time to unmanageble sizes if not configured properly. There are a few components that need special attention.
To check which components are occupying space:
space_usage_kbytes, occupant_name, occupant_desc
order by 1 desc;
Note 329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER
Tablespaces are available since Oracle 8i, however they became the default
starting from Oracle 9i. Locally Managed Tablespaces, also referred to as LMT,
have some advantage over Data Dictionary managed tablespaces.
To verify which tablespace is Locally Managed or Dictionary Managed, you can run the following query:
* Locally Managed Tablespaces use tempfiles to serve the temporary tablespace, whereas Dictionary Managed Tablespaces use a tablespace of the type temporary. When you are running an older version (pre Oracle 9i), then it is important to check the type of tablespace used to store the temporary segments. By default, all tablespaces are created as PERMANENT, therefore you should make sure that the tablespace dedicated for temporary segments is of the type TEMPORARY.
* Make sure that the users on the database are assigned a tablespace of the type temporary. The following query lists all the users that have a permanent tablespace specified as their default temporary tablespace.
from dba_users u, dba_tablespaces t
where u.temporary_tablespace = t.tablespace_name
and t.contents <> 'TEMPORARY';
Note: User SYS and SYSTEM will show the SYSTEM tablespace as there default temporary tablespace. This value can be altered as well to prevent fragmentation in the SYSTEM tablespace.
SQL> alter user SYSTEM temporary tablespace TEMP
*The space allocated in the temporary tablespace is reused. This is done for performance reasons to avoid the bottleneck of constant allocating and de-allocating of extents and segments. Therefore when looking at the free space in the temporary tablespace, this may appear as full all the time. The following are a few queries that can be used to list more meaningful information about the temporary segment usage:
This will give the size of the temporary tablespace:
tablespace_name, sum(bytes)/1024/1024 mb
group by tablespace_name;
This will give the "high water mark" of that temporary tablespace (= max used at one time):
tablespace_name, sum(bytes_cached)/1024/1024 mb
group by tablespace_name;
This will give current usage:
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;
tablespaces can have an impact on the performance, especially when a lot of
Full Table Scans are occurring on the system. Another disadvantage of
fragmentation is that you can get out-of-space errors while the total sum of
all free space is much more then you had requested.
The only way to resolve fragmentation is recreate the object. As of Oracle8i you can use the 'alter table .. move' command. Prior to Oracle8i you could use export/import.
If you need to defragment your system tablespace, you must rebuild the whole database since it is NOT possible to drop the system tablespace.
Note 1020182.6 - SCRIPT. to detect tablespace fragmentation
Note 1012431.6 - Common causes of Fragmentation
Note 147356.1 - How to Move Tables from One Tablespace to Another.
While the performance hit on over extended objects is not significant, the aggregate effect on many over extended objects does impact performance. The following query will list all the 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:
owner, segment_type, segment_name, tablespace_name,
count(blocks), SUM(bytes/1024) "BYTES K", SUM(blocks)
where owner NOT IN ('SYS','SYSTEM')
group by owner, segment_type, segment_name, tablespace_name
having count(*) > <--minext-->>
order by segment_type, segment_name;
It is important that segments can grow and therefore allocate their next extent when needed. If there is not enough free space in the tablespace then the next extent can not be allocated and the object will fail to grow. The following query returns all the segments that are unable to allocate their 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);
Note that if there is a lot of fragmentation in the tablespace, then this query may give you objects that still are able to grow. The above query is based on the largest free chunk in the tablespace available. If there are a lot of 'small' free chunks after each other, then Oracle will coalesce these to serve the extent allocation.
Therefore it can be interesting to adapt the script. in Note 1020182.6 'SCRIPT. to detect tablespace fragmentation' to compare the next extent for each object with the 'contiguous' bytes (table space_temp) in the tablespace.
The need to
rebuild an index is very rare and often the coalescing the index is a better option.
Please see the following article for a full explanation:
Note 989093.1: Index Rebuild, the Need vs the Implications
Note 989186.1: Script. to investigate a b-tree index structure
Starting from Oracle 9i we introduced a new way of managing the before-images. Previously this was achieved through the RollBack Segments or also referred to as manual undo. Automatic undo is used when the UNDO_MANAGEMENT parameter is set to AUTO. When not set or set to MANUAL then we use the 'old' rollback segment mechanism. Although both versions are still available in current release, automatic undo is preferred.
There is little to
no configuration involved to AUM (Automatic Undo Management). You basically
define the amount of time the before image needs to be kept available. This is
controlled through the parameter UNDO_RETENTION, defined in seconds. So a value
of 900 indicates 15 minutes.
It is important to realize that this value is not honored when we are under space pressure in the undo tablespace.
Therefore the following formula can be used to calculate the optimal undo tablespace size:
Note 262066.1: How To Size UNDO Tablespace For Automatic Undo Management
Starting from Oracle 10g, you may choose to use the GUARANTEE option, to make sure the undo information does not get overwritten before the defined undo_retention time.
Note 311615.1: Oracle 10G new feature - Automatic Undo Retention Tuning
* Damaged rollback
segments will prevent the instance to open the database. Only if names of
rollback segments are known, corrective action can be taken. Therefore specify
all the rollback segments in the 'rollback_segments' parameter in the init.ora
* Too small or not enough rollback segments can have serious impact on the behavior. of your database. Therefore several issues must be taken into account. The following query will show you if there are not enough rollback segments online or if the rollback segments are too small.
d.segment_name, d.tablespace_name, s.waits, s.shrinks,
from v$rollstat s, dba_rollback_segs d
where s.usn = d.segment_id
order by 1;
SEGMENT_NAME TABLESPACE_NAME WAITS SHRINKS WRAPS STATUS
--------------- ------------------ ----- --------- --------- --------
RB1 ROLLBACK_DATA 1 0 160 ONLINE
RB2 ROLLBACK_DATA 31 1 149 ONLINE
SYSTEM SYSTEM 0 0 0 ONLINE
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.
Note 62005.1 Creating, Optimizing, and Understanding Rollback Segments
This chapter is very version driven. Depending on which version you are running the option available will be different. Overtime Oracle has invested a great deal of time and effort in managing the memory more efficiently and transparently for the end-user. Therefore it is advisable to use the automation features as much as possible.
memory components (SGA & PGA) needed to be defined at the startup of the
database. These values were static. So if one of the memory components was too
low the database needed to be restarted to make the changes effective.
How to determine the optimal or best value for the different memory components is not covered in this note, since this would lead us too far. However a parameter that was often misused in these versions is the sort_area_size.
The 'sort_area_size' parameter in the init.ora defines the amount of memory that can be used for sorting. This value should be chosen carefully since this is part of the User Global Area (UGA) and therefore is allocated for each user individually.
If there are a lot of concurrent users performing large sort operation on the database then the system can run out of memory.
E.g.: You have a sort_area_size of 1Mb, with 200 concurrent users on the database. Although this memory is allocated dynamically, it can allocate up to 200Mb and therefore can cause extensive swapping on the system.
Oracle 9i we introduced the parameters:
workarea_size_policy = [AUTO | MANUAL]
This allows you define 1 pool for the PGA memory, which will be shared across sessions.
When you often receive ORA-4030 errors, then this can be an indication that this value is specified too low.
Memory Management (ASMM) was introduced in 10g. The automatic shared memory
management feature is enabled by setting the SGA_TARGET parameter to a non-zero value.
This feature has the advantage that you can share memory resources among the different components.
Resources will be allocated and deallocated as needed by Oracle automatically.
Automatic PGA Memory management is still available through the 'workarea_size_policy' and
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.
Note 443746.1 Automatic Memory Management(AMM) on 11g
The alert log file
of the database is written chronologically. Data is always appended and
therefore this file can grow to an enormous size. It should be cleared or
truncated on a regular basis, as a large alert file occupies unnecessary disk
space and can slow down OS write performance to the file.
NAME TYPE VALUE
------------------------------ ------- ----------------------------------
background_dump_dest string D:\Oradata\Admin\PROD\Trace\BDump
11g and above:
NAME TYPE VALUE
------------------------------ ------- ----------------------------------
diagnostic_dest string /oracle/admin/L111
processes generate trace files for certain errors or conflicts. These trace
files are of use for further analyzing the problem. The init.ora parameter 'max_dump_file_size'
limits the size of these trace files. The value of this parameter should be
specified in Operating System blocks.
Make sure the disk space can handle the maximum size specified, if not then this value should be changed.
SQL> show parameter
NAME TYPE VALUE
---------------------------------- ------- ---------------------
max_dump_file_size integer 10240
'user_dump_dest' and 'core_dump_dest' can contain a lot of trace information.
It is important to clear this directory at regular times as this can take up a significant amount of space.
Note: starting from Oracle 11g, this location is controlled by the 'diagnostic_dest' parameter
Note 564989.1 How To Truncate a Background Trace File Without Bouncing the Database
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.
chapters have been outlining the basic items to check to prevent common
database cavehats. In this section you will find references to several articles
explaining how a more in depth analyses and monitoring can be achieved. These
article mainly focus on Data Dictionary Integrity and DataBase structure
Note 456468.1 - Identify Data Dictionary Inconsistency
NOTE 136697.1 - "hcheck8i.sql" script. to check for known problems in Oracle8i,Oracle9i, and Oracle10g
Note 466920.1: 11g New Feature Health monitor
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15747463/viewspace-772575/，如需转载，请注明出处，否则将追究法律责任。