Database Creation Standards

Copyright Oriole Corporation, 1999

1. Account and installation

Rule DC1

The oracle account is created as any user account, for instance under /usr/users/oracle

Rule DC2

The oracle distribution MUST NOT be installed under the home directory of the oracle account but under a separate hierarchy owned by the oracle account which follows the rules of the Oracle Flexible Architecture (OFA), a recognized standard, in which various versions are installed each in its own directory under a common $ORACLE_BASE directory. Example : ORACLE_BASE=/u01/app/oracle/product ORACLE_HOME=$ORACLE_BASE/8.0.5

2. Naming
1. Database

Rule DC3	Production database names must not in any case include a version number, Oracle-related or otherwise
Rule DC4	Database names are reminiscent of the site where they are physically located (machine or otherwise) Exception to rule DC4 Database names can be the name of an application if the database is dedicated to a single application
Rule DC5	Only n - 2 out of the maximum n characters allowed to name a database (n depending on the operating system) will be used. The n - 1 th character will be used to indicate the status of the database (D for Development, T for Test, P for Production), the pth one will be used to number databases of the same status at the same location (which can be the same database at various stages of evolution).

2. Instance

Rule DC6

In the most common case of a single instance database, the instance name will be identical to the database name. In the case of a multi-instance database, the instance name will be equal to the database name plus a unique instance identifier.

3. Database files
1. File structure

Recommendation RC1	As far as possible, one shall try to follow more or less strictly the OFA. Following this architecture (Unix example) : Under the $HOME directory of the oracle unix account one subdirectory bearing the database name for each database. Under this directory one will create the following subdirectories : admin audit (audit_file_dest) bdump (background_dump_dest) cdump (core_dump_dest) pfile temp (temporary_sort_file_dest) udump (user_dump_dest) Database files (including redo log files) will be located under file systems dedicated to data as follows : /<top of hierarchy>/ORACLE/<database name>
Recommendation RC2	Depending on the capabilities of the underlaying operating system and the hardware configuration, one will try to : Have data files on disks accessible through at least two independent controllers Have redo log file on a disk (several if you have several redo log files per group) where they represent the only activity and which is also accessible through an independent controller. Most obviously, trying to put datafiles on separate disks becomes irrelevant if they are located on the striped filesets allowed by some operating systems and hardware. If ARCHIVE LOG mode is used, one shall also try to have some dedicated (and large!) disk space to temporarily store archived redo log files.

2. File names

Rule DC7

Database files associated with a tablespace will be named <tablespace name>_<V>##.dbf, where <V> indicates the volatility of data (S for Static, B for Dynamic, etc.) and ## represents a sequence number (01 to 99). Exception to rule DC7 : file names will include also the database name if files from several databases are likely to be stored (during backup, for instance) under a common directory.

3. Parameter files

Rule DC8

At least two parameter files will be used per database : A database specific file named config<database name>.ora, which will include database-specific parameters such as db_name, db_block_size, etc. At least one instance-specific file named init<instance name>.ora which will include instance-specific parameters such as shared_pool_size, rollback_segments, db_writers, db_block_buffers, sort_area_size, etc. Note that there may be different instance-specific files per instance if, say, daily and nightly processes have very different requirements. The init<instance name>.ora file will refer to the config<database name>.ora file through the include parameter. A soft link bearing the same name will be created under $ORACLE_HOME/dbs to the actual init<instance name>.ora file.


Tablespaces

Recommendation RC3

- Besides the default SYSTEM tablespace (suggested size 30M), the following tablespaces will be systematically created (followed by a suggested size): TEMP for temporary segments (50 Mb) RBS for rollback segments (50 Mb) TOOLS for tool-related tables (such as the tables required by Oracle Enterprise Manager or Designer 2000) (20Mb) USERS for accounts which really need creating tables (10Mb) Moreover, two tablespaces will be created at least to store tables and indexes used by each application hosted in the database : <application name>_TAB for tables, and <application name>_IDX for indexes, including implicit indexes used by PRIMARY KEY and UNIQUE constraints (by default, this will be sized as 60% of <application name>_TAB)

Rule DC9

- The auto-extent feature will, if used, only be used for the TEMP and RBS tablespaces. PCTINCREASE will be set to 0 in the default storage clause of all tablespaces.

Oracle roles

Rule DC10

- One role named APPLICATION_OWNER will be systematically created, with the following system privileges : create session alter session create cluster create table create view create procedure create sequence create synonym create trigger create public synonym create role create database link create snapshot


Oracle accounts


Rule DC11

- Default accounts : SYS - Password of SYS will be set to a non-enterable value by using : alter user sys identified by values '*** SYS ***' (or similar). No other change. SYSTEM - Password of SYSTEM will be set to the same value as the password of the host account under which the Oracle software is installed (the 'oracle' account). Temporary tablespace will be set to TEMP and default tablespace to TOOLS.

Rule DC12

- Additional accounts : An externally identified, DBA account, will be created for the 'oracle' account. This will allow to have password-free dba scripts. Temporary and default tablespaces will be set as for SYSTEM. As a rule, DBA actions executed from the server will be run from the externally identified account. SYSTEM will be used for SQL*Net DBA connections. For each application the tables of which are hosted in the database, one account will be created. No privilege will be granted to this account but the APPLICATION_OWNER role. Unlimited quota will be given on <application name>_TAB and <application name>_IDX exclusively. Temporary table space will be set to TEMP. Default tablespace will be set to a tablespace such as USERS on which the account has no quota. The purpose of this rule is to ensure that TABLESPACE is correctly specified everywhere, including in the ADD CONSTRAINTS statements, in the scripts provided by developers, and that no import will ever hide, by creating tables in the default tablespace, a missing tablespace.


Rollback segments


Rule DC13	- Rollback segments will be created with a value for INITIAL identical to the value for NEXT. Initially 5 to 20 rollback segments will be created (depending on the expected number of simultaneously updating users) with INITIAL=NEXT=100K. MAXEXTENTS will be set to the maximum allowed for the block size (Oracle 7) or unlimited (0racle 8) and MINEXTENTS will be initially set to 5.
Recommendation RC4	- After about a week or two of real activity, rollback segments will be resized and the value for OPTIMAL will be set (a script is available in the X-rated section of the Oriole Corp site to do this).