ORACLE - Configuration Standards

PURPOSE

The purpose of this document is to list the configuration standard guidelines for any company

 

1 - File name standards

 

File type

Format

Example for <sid>=LIVE

Configuration parameter file

IFILE_<sid>.ORA

IFILE_LIVE.ORA

Initialization Parameter File

INIT<sid>.ORA

SPFILE<sid>.ORA

INITLIVE.ORA

SPFILELIVE.ORA

Control File

CONTROL’n’_<sid>.CTL

CONTROL1_LIVE.CTL

Data File (Holds data tablespaces)

DATA_<var>_<sid>.DBF

DATA_EMP_LIVE.DBF

Index File (Holds Index tablespaces)

INDX_<var>_<sid>.IDX

INDX_ACCT_LIVE.IDX

Redo Log file

REDO’g’’m’_<sid>.RDO

REDO1A_LIVE.RDO

Rollback Segments Data File

RBS<var>_<sid>.RBS

RBSBIG_LIVE.RBS

SYSTEM Data File

SYSTEM’n’_<sid>.DBF

SYSTEM1_LIVE.DBF

Temporary Data

TEMP<var>_<sid>.DBF

TEMP1_LIVE.DBF

Tools Data File

TOOL<var>_<sid>.DBF

TOOL1_LIVE.DBF

User Data File

USER<var>_<sid>.DBF

USER1_LIVE.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

 

 
 
<sid>    = Instance SID {see Note 1.3}

n        = Number of File starting with 1 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

RBS

Standard-operation rollback segments

DATA, INDEXES and TEMP

RBSBIG

Specialty rollback segments used for data loads

DATA, INDEXES and TEMP

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

 

 Below is a suggested arrangement for a compromise of 7 disks (reduced from ideal of 22-disk solution and dosed not allow for use of RAID).

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

LOG1A.RDO     LOG1B.RDO {Group 1, 2 members A and B}

LOG2B.RDO     LOG2A.RDO {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.

 

 

WEB Sites

http://www.ipass.net/~davesisk/oont.htm Dave Sisks Oracle on NT page

http://www.oramag.com Free Subscription to Oracle Magazine

http://www.auerbach-publications.com/io.htm Web page for “Oracle Internals”

http://technet.oracle.com/ Oracles Technet Web Page (Free subscription)

http://metalink.oracle.com/ MetaLinks login page

http://www.orafaq.com/faq2.htm Oracle FAQ site

http://www.orapub.com Good source of papers for Oracle and Win NT

http://www.oracle-users.com/ Good source of papers for Oracle and Win NT

http://web.singnet.com.sg/~petermag/oracle.html Loads of links to Oracle related sites