ORACLE
- Configuration Standards
PURPOSE
The purpose
of this document is to list the configuration standard
guidelines for any company
Oracle database instance names are limited to eight characters. The
last two characters of the name should reflect the nature of the
database.
e.g.
Live instance
HLTpr
Test instance
HLTte
Data Warehouse HLTdw
Developing Instance
HLTde
1 - File
name standards
|
File type |
Format |
Example for
<sid>=LIVE |
|
Initialization
Parameter File |
INIT<sid>.ORA SPFILE<sid>.ORA |
INITLIVE.ORA SPFILELIVE.ORA |
|
Control File |
CONTROL_<sid>_’nn’.CTL |
CONTROL_LIVE_01.CTL |
|
Data File
(Holds data tablespaces) |
DATA_nn_<var>_<sid>_’nn’.DBF |
DATA_EMP_LIVE_01.DBF |
|
Index File
(Holds Index tablespaces) |
INDX_<var>_<sid>_’nn’.DBF |
INDX_ACCT_LIVE_01.DBF |
|
Redo Log
file |
REDO’_<sid>_’g’’m.LOG |
REDO_LIVE_1A.LOG |
|
SYSTEM Data
File |
SYSTEM_<sid>_’nn’.DBF |
SYSTEM_LIVE_01.DBF |
|
Temporary
Data |
TEMP_<sid>_’nn’.DBF |
TEMP_LIVE_01.DBF |
|
Tools Data
File |
TOOL_<sid>_’nn’.DBF |
TOOL_LIVE_01.DBF |
|
User Data
File |
USER_<sid>_’nn’.DBF |
USER_LIVE_01.DBF |
|
SQL PL/SQL
script |
Filename.SQL |
Free_space.SQL |
|
Archived
Redo Log file {see Note 1.1} |
ARC_’seq num’’thread
num’.ARC |
ARC00000025630001.ARC |
‘nn’
=
Number of File starting with 01 and incrementing by one.
<var> = Variable data e.g. brief descriptive
information
‘g’
=
Redo group number
‘m’
=
Redo member reference a,b,c etc…
Note 1.1
Determined
by INIT<sid>.ORA
parameter LOG_ARCHIVE_FORMAT which should be defined as follows to meet
name
standard:
Log_archive_format =
ARC%S%T.ARC
Where:
%S = Log
sequence number, left-zero-padded
%T = Thread
number, left-zero-padded
Note 1.2
Consideration
to using <sid>
as part of file name will require file renames if cloning your DB.
2 - Control
Files
The
Database will be configured to use 3 control files. Each
control file must be on separate spindle.
3 - RAID
Hardware
RAID is preferred over software RAID. RAID adapter write
cache set to “write through” (physically writing the information on
disk before
acknowledging the write to the operating system).
|
File Type |
Acceptable
RAID (best first) |
Comments |
|
Redo Logs |
None RAID 0
(single disk array) RAID 1 RAID 5 |
Always
Mirror at Oracle-level |
|
Data and
Index |
RAID 10 RAID 1 RAID 5 RAID 0 |
For high
write capacity. Moderate
write capacity. Moderate
write, heavy read. Best write
performance but no fault tolerance! |
|
SYSTEM TS |
RAID 10 RAID 1 RAID 5 RAID 0 |
|
|
RBS |
RAID 10 RAID 1 RAID 5 RAID 0 |
|
|
TEMP |
RAID 10 RAID 1 RAID 5 RAID 0 |
If little
loss of functionality due to loss of TEMP and can be easily relocated
then consider RAID 0 |
|
Archive Logs |
RAID 5 RAID 10 |
For high
change activity |
|
Control
Files |
Non-specific |
Always
mirror at Oracle-level |
|
Oracle
Software and Configuration
Files |
RAID 1 |
|
Each type
of RAID is
defined by:
|
RAID Level |
Description |
Minimum No.
Of physical disks required |
|
0 |
Disk
striping |
2 |
|
1 |
Disk
mirroring |
2 |
|
5 |
Disk
striping with parity |
3 |
|
10 (0 + 1) |
Mirrored
stripe sets |
4 |
4 – Optimal
Flexible Architecture (OFA)
Uses of OFA
are related to RAID levels in use and also file
separation. Generally speaking, try to perform an OFA Compatible
Installation.
5 - File
separation
Separation
of files to balance disk I/O and reduce contention is
primarily influenced by whether database is read intensive (DSS) or
transaction
oriented (OLTP). Files may also be separated in support of recovery
strategy.
The primary aim is to separate objects by object type and activity type
by
placing tablespaces associated with
specific objects
in separate physical data files and separating these physical files
subject to
quantities of disks available, desired RAID levels and recovery
considerations.
A review of the expected usage levels of the various tablespaces
should also be taken into account when deciding which files to separate
across
disks.
Below is a
list of some typical tablespaces
found in databases and their recommended divisions.
|
Tablespace |
Description
of objects for use in tablespace |
Keep
separated from |
|
SYSTEM |
Data
dictionary |
All other tablesapces |
|
DATA |
Standard-operation
tables |
SYSTEM,
DATA_2 |
|
DATA_2 |
Static
tables used during standard operations |
SYSTEM, DATA |
|
INDEXES |
Indexes for
the standard-operation tables |
From tables
used by indexes |
|
INDEXES_2 |
Indexes for
the static tables |
From tables
used by indexes |
|
TOOLS |
RDBMS tools
tables |
TOOLS_I |
|
TOOLS_I |
Indexes for
heavily used RDBMS tools tables |
TOOLS |
|
TEMP |
Standard-operation
temporary segments |
SYSTEM |
|
TEMP_USER |
Temporary
segments created by a specific user |
SYSTEM |
|
USERS |
User
objects, in development databases |
All other tablespaces |
|
USERS_I |
User indexes, in testing
databases |
All other tablespaces |
In addition
to above where applicable each distinct object type
should be stored in its own tablespace to
minimize
the impact objects have on each other. Typically these can include:
- SNAPS For snapshot tables and indexes.
- PARTITIONS
- TEMP_WORK
For use during
large data loads.
Other files
for separation consideration are:
|
File type |
Keep
separated from |
|
Online Redo
Logs |
Ideally all
other DB files. Less optimally from SYSTEM, RBS or very active DATA or
INDEX tablespaces. If running DB in
ARCHIVELOG mode then also from each other across multiple disks to
avoid LGWRARCH contention. |
|
Archived
Redo Log Files |
SYSTEM, RBS
or very active DATA or INDEX tablespaces,
Online Redo Log files |
|
Control
files (have 3 copies) |
Other
copies of control file |
|
Oracle
software |
Database
files |
|
Disk |
Contents |
|
1 |
Oracle
software |
|
2 |
SYSTEM,
TOOLS, INDEXES_2 tablespaces, Control file
1 |
|
3 |
RBS, RBSBIG
tablespaces, Control file 2 |
|
4 |
DATA tablespace, Control file 3. Oracle Mirrored
Online Redo logs 1, 2, and 3 |
|
5 |
INDEXES,
TEMP, TEMP_USER, DATA_2 tablespaces |
|
6 |
Online Redo
logs 1, 2, and 3 |
|
7 |
Application
software, Archived redo logs |
6 - Redo
Logs
The
quantity and size of Redo Logs will be determined initially by
application requirements and tuned as needed. Redo logs must always be
mirrored
at Oracle level with a minimum of 2 members per group with members of
each
group on different disks. Example:
Disk 1
Disk 2
REDO_LIVE_1A.LOG REDO_LIVE_1B.LOG
{Group 1, 2 members A and B}
REDO_LIVE_2A.LOG REDO_LIVE_2B.LOG
{Group 2, 2 members A and B}
7 – Redo
Log Archiving
Unless
there is a particular reason for not doing so, each
database is to be set-up with Archive Logging enabled. Use of
LOG_ARCHIVE_DUPLEX_DEST parameter may also be considered for duplexing archived redo logs.
8 - File
system
NTFS is the
preferred operating file system for use because of its
file level security, fault tolerance features and also it’s capability
to
handle larger file sizes.
9 - Block
size
Generally
8K for OLTP systems and 16K for DW.