- 论坛徽章:
- 0
|
主题: How to Perform a Healthcheck on the Database
文档 ID: 122669.1 类型: BULLETIN
上次修订日期: 17-JUL-2008 状态: PUBLISHED
Table of Contents
-----------------
1. Introduction
2. Parameter file
3. Controlfiles
4. Redolog files
5. Archiving
6. Datafiles
6.1 Autoextend
6.2 Location
7. Tablespaces
7.1 SYSTEM Tablespace
7.2 SYSAUX Tablespace
7.3 Locally vs Dictionary Managed Tablespaces
7.4 Temporary Tablespace
7.5 Tablespace Fragmentation
8. Objects
8.1 Number of Extents
8.2 Next extent
8.3 Indexes
9. AUTO vs MANUAL undo
9.1 AUTO Undo
9.2 Manual undo
10. Memory Management
10.1 Pre-Oracle 9i
10.2 Oracle 9i
10.3 Oracle 10g
10.4 Oracle 11g
11. Logging & Tracing
11.1 Alert File
11.2 Max_dump_file_size
11.3 User and core dump size parameters
11.4 Audit files
11.5 Sqlnet
1. Introduction
---------------
This article explains how to perform a health check on the database. 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.
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 needs/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.
General scripts that help track information on the database:
=)> Note 250262.1 Health Check / Validation Engine Guide
=)> Note 136697.1 “hcheck8i.sql” script to check for known problems in Oracle8i, Oracle9i, and Oracle10g
2. Parameter file
-----------------
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 otherwise.
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.
Reference:
Note 249664.1: Pfile vs SPfile
3. Controlfiles
---------------
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.
4. Redolog files
----------------
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.
References :
Note 102995.1 Maintenance of Online Redo Log Groups and Members
5. Archiving
------------
Archiving provides 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
Archive destination <arch. dest.> --OR-- USE_DB_RECOVERY_FILE_DEST
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.
References:
Note 69739.1 How to Turn Archiving ON and OFF
Note 122555.1 Determine how many disk space is needed for the archive files
6. Datafiles
------------
6.1 Autoextend
~~~~~~~~~~~~~~~
The autoextend 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.
DB_BLOCK_SIZE Max 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:
SQL> select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name
from dba_data_files
where autoextensible = 'YES';
Reference:
Note 112011.1: ALERT: RESIZE or AUTOEXTEND can "Over-size" Datafiles and Corrupt the Dictionary
Note 262472.1: 10g BIGFILE Type Tablespaces Versus SMALLFILE Type
6.2 Location
~~~~~~~~~~~~~
Verify the 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 * from v$dbfile;
FILE# NAME
--------- --------------------------------------------------
1 D:\DATABASE\SYS1D806.DBF
2 D:\DATABASE\D806\RBS1D806.DBF
3 D:\DATABASE\D806\TMP1D806.DBF
5 D:\DATABASE\D806\USR1D806.DBF
6 D:\USR2D806.DBF
7 F:\ORACLE\USR3D806.DBF
7. Tablespaces
--------------
7.1 SYSTEM Tablespace
~~~~~~~~~~~~~~~~~~~~~~
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.
SQL> select owner, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');
7.2 SYSAUX Tablespace (10g Release and above)
~~~~~~~~~~~~~~~~~~~~~~
The SYSAUX 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 tablespace.
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:
select space_usage_kbytes, occupant_name, occupant_desc
from v$sysaux_occupants
order by 1 desc;
Reference:
Note 329984.1: Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER
7.3 Locally vs Dictionary Managed Tablespaces
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Locally Managed 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:
SQL> select tablespace_name, extent_management
from dba_tablespaces;
Reference:
Note 93771.1: Introduction to Locally-Managed Tablespaces
Note 105120.1: Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
7.4 Temporary Tablespace
~~~~~~~~~~~~~~~~~~~~~~~~~
o 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.
SQL> select tablespace_name, contents
from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
USER_DATA PERMANENT
ROLLBACK_DATA PERMANENT
TEMPORARY_DATA TEMPORARY
o 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.
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';
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;
o 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:
SQL> select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;
This will give 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;
This will give 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;
7.5 Tablespace Fragmentation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Heavly fragmented 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 drop and recreate the object. In most
cases doing an export and import will solve the problem. If you need to
defragment your system tablespace, you must rebuild the whole database since
it is NOT possible to drop the system tablespace.
References:
Note 1020182.6 SCRIPT to detect tablespace fragmentation
Note 1012431.6 Common causes of Fragmentation |
|