Database Design Specifications

1. Purpose
Define database design specifications as technical references for database design, planning, development, and maintenance personnel.
2. Overview
This document describes the design specifications for database environment configuration, database physical design, database logic design, views, stored procedures, and application design.
3. Database Physical Design Principles
3.1 database environment configuration principles
* Operating System Environment:
For small and medium database systems, the linux operating system is suitable. For systems with high database redundancy requirements and load balancing capacity requirements or large database systems, the Oracle RAC cluster database method can be used.
* Memory Requirements
For a database in a linux operating system, SGA is designed in a suitable range: 50%-70% of the physical memory.
* Swap Zone Design
If the physical memory is less than 2 GB, the swap partition swap is 3 times the physical memory. If the physical memory is greater than 2 GB, the swap size is 1-2 times the physical memory.
* Other environment variables

For more information, see Oracle Installation documentation and random documentation.

3.2 Database Design Principles
* Conventions
We agree that 26 English letters (case sensitive) and 10 natural numbers 0-9 are used, and underscores (_) are added, which are 63 characters in total. No other characters are allowed (except comments ).
* Database SID
The database SID is the unique identifier of the database. The name length is limited to 10 characters. For single-node databases. The project name can be abbreviated in English, for example, EduOA.
For cluster databases, after the SID is specified, the SID of each node is automatically named SIDnn, where nn is the node number: 1, 2 ,..., 64. For example, rac1, rac2, and rac24.
* Global Database Name
Global Database Name: <sid>. domain

* Database Type Selection
Data warehouse is used for massive database systems. For small databases or OLTP databases, the Transaction Processing type is used.

* Database Connection Type Selection
Oracle databases have dedicated server connection types and multi-thread server MTS connection types. For batch processing services, dedicated server connection is required, while for OLTP services, MTS connection is more appropriate. After MTS is used, you can configure network services to achieve certain batch processing services using dedicated server connections. Therefore, the MTS type is generally used in database design.

* Database SGA Configuration
The database SGA can be configured manually or in the proportion of physical memory. in the initial design stage of the database, it adopts the proportional configuration mode and modifies the SGA according to the system tuning method in actual applications.

* Database character set selection
To enable the database to correctly support multiple languages, you must configure the appropriate database character set and use the UTF8 character set.

* Other database parameter configurations
* DB_FILES
Db_files is the number of files that can be opened at the same time in the database. The default value is 200. When the number of FILES in the Database Planning is close to or greater than 200, configure the FILES according to the following estimates:
DB_FILES = FILES * 1.5
* Db_block_size
Db_block_size is the minimum physical unit of the database. Once the database is created, this parameter cannot be modified. db_block_size is adjusted according to the following rules:
Data Warehouse type: db_block_size is as large as possible, with 8192 or 16384
OLTP type: db_block_size. The value range is 2048 or 4096.
* Database Control file configuration
* Control file Image
Multiple control files are stored in different physical locations.
* Control file configuration
Parameter settings in the control file. The maximum number of data files cannot be smaller than the database parameter db_files.

* Database Log File Configuration
* Log file size
The size of the log file is determined by the transaction processing capacity of the database. during the design process, ensure that one log file is switched every 20 minutes. Therefore, for the batch processing system, the log file size is several hundred MB to several GB. For OLTP systems, the log file size is within several hundred MB.
* Number of Log File groups
For batch processing systems: Log File groups are 5-10 groups.
For OLTP systems: Log File groups are 3-5 groups.
The log size of each group is consistent.
Each node has an independent log group for the cluster database system.

* Number of log members

To ensure that logs can be mirrored, each log group has two members.
* Database rollback segment Configuration
In the Oracle9i database, the Undo tablespace is designed to replace the previous version of the rollback segment tablespace.
The Undo tablespace size design specification is calculated by the following companies:
Undospace = UR * UPS * db_block_size + Redundancy
UR: indicates the maximum number of seconds to be kept in the undo operation, which is determined by the UNDO_RETENTION value of the database parameter.
UPS: number of database blocks generated per second in undo.
For example, if two hours of rollback data is retained in the database, it is assumed that 200 database blocks are generated every second. Then Undospace = 2*3600*200*4 K = 5.8G
* Temporary database segment tablespace Configuration
The tablespace size of the temporary database segment is adjusted according to the actual production environment. The tablespace attribute is automatically expanded.
* Database System tablespace Configuration
The size of the system tablespace is about 1 GB. Except for the data stored in the database data dictionary, other data cannot be stored in the system tablespace.

1 2 Next Page