Database Standards and Environments:

01. Production Database Environment.
02. Pre-production Database Quality Test Environments
03. Development Database Environment.

General Database Standards are categorized into:

01. Pre-Database Creation Standards or Setting up of Environment.
02. Database Standards to be followed to create a database
03. Post-Database Creation Standards

Pre-Database Creation Standards or Setting up of Environments:

This includes:

a. Decisions based on the Hardware Architecture of the Hosting Servers per Enterprise decisions.
b. Decisions on the file-systems to be created basing on the underlying Storage Architectures.
c. Decisions derived from the Design and Architecture Team for the Databases, which also include the required initialization parameters.

a. If the database is home grown ER diagram generation
b. If the database is vendor supported application then securing the ER diagram
c. Capacity planning
d. Resource utilization
e. Process diagrams

d. Decisions based on the Enterprise Security Concerns at the OS Levels
e. Decision on the naming conventions of the file systems.
f. Decision on the naming convention of Oracle Databases

Production to be associated with P
Pre-production to be associated with S
Training to be associated with T
Quality to be associated with Q
Development to be associated with D

Database name cannot have more than 4 letters and the last letter should indicate the environment.

Example:

SIEBEL may be SIB
Production SIBP
Stage SIBS
Training SIBT
Quality SIBQ
Development SIBD

g. Decision on the ARCHIVELOG mode for the database in various environments and recoverability of the databases and data loss tolerance levels by Business
h. Decision on the backup strategy for the database and using RMAN as the standard Oracle provided tool for the backup and restore. Backup and Restore Policy is to decide on the retention of the backup catalogs within RMAN and MML catalogs, Exceptions if any, log file destination, details to be logged, database, archive log file backups.
i. The restoration policy is to include the SLA in case of data file, tablespace and database recovery and also on the log files to be applied.
j. Decision on the MML software where the backup destination is Tape.
k. Decision on the access levels to the Servers which host Oracle Databases and MML software to the Oracle DBA, System Administrators and Backup Administrators.
l. Altering the Kernel Parameters and rebooting the server before creation of Database, basing on the resources available on the server and the impact of the Hardware Architectural Design of the server.

Database Standards to be followed to create a database:

This includes:

a. Installation of Oracle Binaries based on the required components as decided by Database Standards to be followed to create a database. In case the application vendor drives the installation, the vendor has to provide a document with all the standards.
b. Naming Standards of the database.
c. Naming of the non-default schema object naming standards in case the application is home grown
d. Naming standards and Security Policies of LISTENER to the database
e. Naming standards and security policies for TNS Names file at the client level and centralized policy to update TNS Names file on the clients as and when a new database is created.
f. Decision of using SQLNet.ora file on the server and client.
g. Decisions of the purging or archiving alert log, listener log, RDBMS messages files, user dump and core dump files and audit files.
h. Decisions on the automated or user driven database startup and shutdown scripts as also listener.
Post-Database Creation Standards:

This includes:

a. Password security policy
b. Policy on grant of default roles and system privileges directly
c. Policy on creation of profile on better utilization of resources.
d. Policy on monitoring the databases
a. Monitoring RDBMS
b. Monitoring Backups
c. Monitoring Performance PERFSTAT and other tools (home grown or oracle or vendor supported)
d. Generation of statistics for the User Schemas
e. Scheduling Jobs through crontab or through dbms_job or any other schedulers or monitoring those jobs

Pre-Database Creation Standards-file system naming standards

RAID TYPE OF RAID Control files Database file Redo log file Archive log file
0 Striping Avoid* Ok* Avoid* Avoid*
1 Shadowing Ok Ok Recommended Recommended
0+1 Striping + Shadowing Ok Recommended (1) Avoid Avoid
3 Striping with Static Parity Ok Avoid(2) Avoid Avoid
5 Striping with Rotating Parity Ok Avoid(2) Avoid Avoid

* RAID 0 does not provide any protection against failures. It requires a strong backup strategy.
(1) RAID 0+1 is recommended for database files because this avoids hot spots and gives the best possible performance during a disk failure. The disadvantage of RAID 0+1 is that it is a costly configuration.
(2) When heavy write operation involves this data file

Installable Oracle Software Bundles (Base Version and Patch Sets) are to be kept on NFS mounted file systems accessible from all the database servers

Oracle Home is to be created on a separate file system. If the server is to host multiple database and if the applications supported by those databases are not based on the same Oracle Versions then with a capability to associate with the choice home the file systems are to be created and each database in its profile got set to the required home.

Example:

/opt/local/oracle/product/9.2.0.4
/opt/local/oracle/ product/9.2.0.5
/opt/local/oracle/ product/9.2.0.6
/opt/local/oracle/ product/9.2.0.7

Data files are to be located on the file systems created

The minimum size of a file system for the redo log should be 1 Gig. This gives unfettered ability to tune redo log issues.

System

/opt/local/oracle/<SID_NAME>/oradata/SYSTEM/sid_SYS_NNN.dbf

Data files

/opt/local/oracle/<SID_NAME>/oradata/dataNNN/sid_TBSP_NNN.dbf

Index files

/opt/local/oracle/<SID_NAME>/oradata/indxNNN/sid_TBSP_NNN.dbf

Redo log

/opt/local/oracle/<SID_NAME>/oradata/redoNNN/sid_REDO_G_N_M_NN.log
(No two members of the same group live on the same file system)
Control Files

/opt/local/oracle/<SID_NAME>/oradata/control_N/sid_control_N.ctl

Dump Scripts and Logs Files

/opt/local/oracle/<SID_NAME>/admin/bdump/
/opt/local/oracle/<SID_NAME>/admin/udump/
/opt/local/oracle/<SID_NAME>/admin/cdump/
/opt/local/oracle/<SID_NAME>/admin/rman/
/opt/local/oracle/<SID_NAME>/admin/exports/
/opt/local/oracle/<SID_NAME>/admin/scripts/

Archive Log Destinations

/opt/local/oracle/<SID_NAME>/admin/archive/dest_01/
/opt/local/oracle/<SID_NAME>/admin/archive/dest_02/