Global Database Technical Services

Standards

Database Configuration Standards

For

Oracle on UNIX Platforms

On Date:4/8/2003 8:26 AM

Version:1.1

Status:Final

Document Scope

Introduction

Terminology

Section 1.Platform

1.1.Hewlett Packard HP-UX

1.2.IBM AIX

Section 2.Requirements

2.1.Directory Configuration

2.1.1.Database File System

2.1.2.Administrative File System

2.2.Database Parameters

2.3.Database Creation

2.3.1.Character Set

2.3.2.Control Files

2.3.3.Required Tablespaces

2.3.3.1.System

2.3.3.2.Tools

2.3.3.3.Users

2.3.3.4.Temp

2.3.3.5.Rollback

2.3.4.Redo Logs

2.3.5.Rollback Segments

2.3.5.1.Rollback Segment Size

2.3.5.2.Rollback Segment Configuration

2.3.5.3.Rollback Segment Name

2.3.6.Archiving

2.3.7.Configuration Files

2.4.Application Tablespaces

2.4.1.Application Tablespace Name

2.4.2.Application Tablespace Configuration

2.5.Network Configuration

2.6.User Setup

2.7.Scheduled Administration Scripts

2.7.1.Database Dependent Maintenance Scripts

2.7.2.Database Independent Maintenance Scripts

2.7.3.Statistics Scripts

2.8.Backup Configuration

2.8.1.Logical Backup

2.8.2.Physical Backup

2.8.3.Archive Backup

2.9.Monitoring Configuration

2.9.1.Standard Database Events

2.9.2.Optional Database Events

2.10.GDTS Notification

Section 3.Flexibility

Section 4.Compliance

Section 5.Variance Procedure

Appendix A.Revision History

Page 1

10/6/2018

Global Database Technical Services

Standards

Document Scope

This document contains the GDTS standards for creating and configuring a database for Oracle on Unix platforms.

Introduction

These standards will help ensure consistency in databases created for Oracle installations on Unix.

Terminology

See the GDTS Standards Terminology for the definition of guidelines, recommendations and standards.

Section 1.Platform

1.1.Hewlett Packard HP-UX

1.2.IBM AIX

Section 2.Requirements

2.1.Directory Configuration

The storage architecture for a database, at a minimum, shall include 2 file systems; one for the database files and one for the administrative files. The number of files systems defined for the database environment will be dependent on the storage array network configuration.

2.1.1.Database File System

(S) Name all mount points required for the database data files to match the pattern /mnt/um/oradata/sid, where m is a unique fixed-length 2 digit integer identifier, beginning with 01, that distinguishes one mount point from another. The number of files systems defined for the database environment will be dependent on the storage array network configuration.

[Example: /mnt/u01/oradata/sid, /mnt/u02/oradata/sid, etc.]

2.1.2.Administrative File System

(S) $ORACLE_BASE is the admin branch of the OFA directory tree. Because of its dynamic nature, $ORACLE_BASE is not created under /opt/oracle/product. It will be defined under /var/opt/oracle. For each database, database administration files shall be stored in the following subdirectories of/var/opt/oracle/admin/sid, where sid is the Oracle SID. This administration file directory structure should be located on a separate physical disk from the database data file system. This will enhance performance and database recoveries.

adhocad hoc SQL scripts for a given database

archarchived redo log files

auditdatabase audit files

bdumpbackground process trace files

cntrlmirrored control file for the database

createscripts used to create the database

expdatabase export files

pfileinstance parameter file

redomirrored online redo logs for the database

statspackstatspack reports

udumpuser SQL trace files

utlfiledirectory for PL/SQL file I/O.

[Example: /var/opt/oracle/admin/dsid/exp, /var/opt/oracle/admin/tsid/pfile, ...]

2.2.Database Parameters

(S) A default init.ora is provided for you during your database create which contains minimum settings for memory parameters, GDTS standard user_dump locations…etc. The file format will be sectioned as follows:
control file locations
db_name, service_name, global_name, compatibility
memory parameters

optimizer parameters
user_dump destinations
archive parameters
mts parameters
utlfile destinations
job_queue parameters
miscellaneous parameters
unsupported or hidden parameters

2.3.Database Creation

2.3.1.Character Set

(R)The standard character set for all databases will be WEISO8859P1, Western European 8 bit character set. This character set allows us a wider set of characters and improves our capability of exporting and importing data between geographies.

2.3.2.Control Files

(S)Every Oracle database should have at least 2 control files, each stored on a different file system. Each file system that stores members of on-line redo log groups should also include a control file copy. This minimizes the risk that all control files and all groups of on-line redo logs will be lost in a single disk failure.

Each control file shall be named controlnn.ctl where nn is a sequential number, starting with 01, to guarantee uniqueness.

At a minimum, there should be one control file created under the database data file system (/mnt/u01/oradata/sid) and a second control file created under the administrative file system ($ORACLE_BASE/sid/cntrl).

2.3.3.Required Tablespaces

(S) There are 5 standard tablespaces; System, Tools, Users, Temp, and Rollback. This is the minimum set of tablespaces that GDTS requires for each database.

2.3.3.1.System

The system tablespace shall have the following characteristics:

  • Name - System
  • Minimum Size – 300 megabytes
  • Datafile Name – system01.dbf
  • Type – Permanent Dictionary Managed
  • Autoextend - NO
  • Tools

The tools tablespace shall have the following characteristics:

  • Name - Tools
  • Minimum Size – 10 megabytes
  • Datafile Name – tools01.dbf
  • Type – Permanent Locally Managed with uniform extent size of 128K
  • Autoextend – increment by 10 megabyte with no maxsize
  • Users

The users tablespace shall have the following characteristics:

  • Name - Users
  • Minimum Size – 10 megabytes
  • Datafile Name – users01.dbf
  • Type – Permanent Locally Managed with uniform extent size of 128K
  • Autoextend – increment by 10 megabyte with no maxsize
  • Temp

The temporary tablespace shall have the following characteristics:

  • Name - Temp
  • Minimum Size – 100 megabytes
  • Datafile Name – temp01.dbf
  • Type – Temporary Locally Managed with uniform extent size of 1024K
  • Autoextend - NO
  • Rollback

The rollback tablespace shall have the following characteristics:

  • Name - Rollback
  • Minimum Size – 250 megabytes
  • Datafile Name – rollback01.dbf
  • Type – Permanent Locally Managed with uniform extent size of 1024K
  • Autoextend - NO

2.3.4.Redo Logs

(S)Every Oracle database instance has an on-line redo log that consists of groups of on-line redo log files. Each database should have at least 2 redo log groups. Additionally, databases should have mirrored groups of on-line redo logs. Members of the same group should be placed on different file systems so that no single disk failure can cause the database instance to fail.

Each redo log in a group shall be named redonna.log where nn is a sequential number, and ais an alphabetic character (starting with the letter a and moving through the alphabet for each additional redo log group).

The redo logs should be sized to ensure a minimum of 1 redo log switch per hour during the normal business hours. The redo log size will be implementation specific and will be dependent on the application design and business use of the application.

At a minimum, there should be one redo log group created under the database file system (/mnt/u01/oradata/sid) and a second redo log group created under the administrative file system ($ORACLE_BASE/sid/redo).

2.3.5.Rollback Segments

2.3.5.1.Rollback Segment Size

(R) The rollback segments should be sized based on the type of transactions that are most common in your environment. If the types of database transactions are short, then use small rollback segments. Long running database transactions will perform better with larger rollback segments. For a system that does not have specific transaction requirements, then it is recommended that the initial size of the rollback segments be 10% of the largest table.

2.3.5.2.Rollback Segment Configuration

(R) Each segment should be divided into 10 equally sized extents. The optimal setting should then be set to the initial size. It is suggested that you have one rollback segment for every 4 concurrent transactions in a production environment. The number of rollback segments for a development environment can be less. At a minimum there should be 4 rollback segments.

2.3.5.3.Rollback Segment Name

(S) Each rollback segment shall be named rbsnn where nn is a sequential number.

2.3.6.Archiving

(S) All production databases will be configured to run in archive log mode. This allows the database to be backed up online and enhances the ability to recover the database up to the time of failure.

There should be one and only one archive log destination defined in the init.ora file. This destination should be set to $ORACLE_BASE/sid/arch.

(log_arch_dest = /var/opt/oracle/admin/sid/arch

The archive log format is also assigned in the init.ora file. The format should be set using the format: sid_%s.log; where %s represents the log sequence number.

(log_arch_format = sid_%s.log)

2.3.7.Configuration Files

(S)For every new database that you want to configure for automated startup and shutdown and maintenance scripts you must configure the oratab file. To set up the automated startup and shutdown scripts so that they are called at system startup, database entries must appear in the oratab file in the format:

ORACLE_SID:ORACLE_HOME:{Y|N}

Where Y or N specifies whether you want the automated startup and shutdown scripts to start up and shut down the database. Oratab is located in the /etc directory.

2.4. Application Tablespaces

2.4.1.Application Tablespace Name

(R) Application tablespace names sometimes cannot be controlled by GDTS. Every effort should be made so that the tablespace names are identifiable with the applications that they are used by. If the application/vendor will accept GDTS tablespace naming standards, table tablespaces should be named application_DAT and index tablespaces should be named application_IDX.

2.4.2.Application Tablespace Configuration

(S) The tablespaces should be locally managed with auto allocate or uniform extent sizes. Tables and indexes should have separate tablespaces. The datafile names for tablespaces should be lower case. Each datafile should be named tablespace_namenn.dbf where nn is a sequential number beginning with 01, to guarantee uniqueness.

2.5. Network Configuration

(S) Each database will need an entry in the listener.ora. Each database will require an entry in Oracle Names. Verify that the server is using Oracle Names by looking in the sqlnet.ora file located in the $ORACLE_HOME/ network/admin directory. If the server is not using Oracle Names, then you will need to add an entry in the local tnsnames.ora file located in either of the above locations.

2.6. User Setup

(S) Database users dbsnmp, listuser, oemmgr and cargillexp should be created and their passwords should match the GDTS standard password for each account. Any variation from the standard passwords should be logged in the Global Password database. The regional IP administrator for the database should be notified to create the individual DBA accounts and secure the passwords for the sys, system, and internal accounts.

2.7. Scheduled Administration Scripts

2.7.1.Database Dependent Maintenance Scripts

(R) All database dependent maintenance scripts should be scheduled using the cron scheduler. The following scripts are database dependent and should be scheduled to run on the server.
ora_arch_compress.sh---compresses archive logs.
ora_rm_archv.sh---deletes archive logs.
ora_ofa_exp.sh---exports the database.
ora_cr_controfile.sh---backup controlfile to trace.

2.7.2.Database Independent Maintenance Scripts

(R) All database independent maintenance scripts should be scheduled using the cron scheduler. The following scripts are database independent and should be scheduled to run on the server.

ora_rm_trc.sh---removes trace files from the udump directory.
ora_rm_aud.sh---removes audit files from the audit directory.
ora_log_maint.sh---listener log maintenance.

2.7.3.Statistics Scripts

(R) All scripts that generate statistics for the Oracle optimizer should be scheduled using the cron scheduler. Statistics should be generated for all application tables and indexes unless directed otherwise by application support or the vendor. Depending on the distribution of data, one of the following scripts should be scheduled to run on the server against the application schemas.

ora_compute_stats.sh---computes table and index statistics for application schemas ora_compute_Hstats.sh---computes table and index statistics with histograms for application schemas

NOTE: Once statistics have been generated, the cost-based optimizer will

be used by the instance if CHOOSE, FIRST_ROWS, or ALL_ROWS was set for the OPTIMIZER_MODE in the database parameter file.

2.8.Backup Configuration

The backup strategy for the database will integrate logical and physical backups based on the database's usage characteristics.

2.8.1.Logical Backup

(R)A logical backup of an Oracle database is accomplished by using the Oracle export utility. Exports provide supplemental protection for a backup strategy, and are used to recover data lost due to user error. A database export should be produced once every 24 hours.

2.8.2.Physical Backup

(S)All production databases will be backed up online. The online database backup is integrated with the operating system backup, and its' schedule will be based on project and infrastructure requirements. The backup process will be monitored and alerts will be sent to DBAs, via ITO, in the event of backup anomalies.

2.8.3.Archive Backup

(R)In addition, the database will have the archive log directory backed up every hour. This is done to ensure that we can recover a database in the event of a disk failure that includes the archive log directory. A journaling job will be scheduled to execute every 60 minutes during normal business hours on the backup server.

2.9. Monitoring Configuration

2.9.1.Standard Database Events

(S) OEM monitoring is the Standard monitoring tool. OEM monitoring allows for pro-active monitoring measures. The five standard database events monitored are:
Event NameFrequencyDescription
Alert Log31 minutesChecks alert log for Oracle ORA- errors.
Archive Full16 minutesMonitors archive destinations for space usage.
DB Up/Down2 minutesMonitors for database availability.
Max Extents23 minutesMonitors for objects reaching max extents.
Process Limit5 minutesMonitors database for number of processes.

2.9.2.Optional Database Events

(G) In addition to the standard events, the following are optional events:
Event NameFrequencyDescription
Broken Database Jobs30 minutesMonitors dba_jobs table for broken jobs.
Redo Log Allocation10 minutesMonitors for users waiting for redo log buffer.
Rollback Contention10 minutesMonitors for rollback contention.

2.10.GDTS Notification

(S) Notification of new databases should be emailed to –admin-it-cit-gia-gdts/mtkb with the following information:
database name
server name
os type
database version
application
business unit
business owner
technical owner
csi
db creator

Section 3.Flexibility

N/A

Section 4.Compliance

A post-creation review with another DBA is required if GDTS will support the Oracle database.

Section 5.Variance Procedure

Guidelines may be implemented with necessary and appropriate modifications without further documentation.

Variations from Recommendations must be documented and sent to the Technical Review Group for review.

Deviations from Standards require a variance. Document the change and the reasons for it and submit to the Technical Review Group for review, discussion, and approval or denial.

Appendix A.Revision History

1)04/08/2003Added the Audit Directory to the Oracle_Base file system for a database.

Arlynn Leapaldt

2)

Page 1

10/6/2018