How to Design Large Oracle databases

I. Introduction
Super Large systems have the following features:
1. Generally, the number of users processed exceeds 1 million, and the number of users processed exceeds 10 million. The data volume of the database generally exceeds 1 TB;
2. The system must provide the real-time response function. The system must run without stopping services, requiring high availability and scalability of the system.
In order to meet the above requirements, in addition to computers with superior performance and massive storage devices, advanced database structure design and optimization application systems are also required.
Generally, super-large systems use dual-host or multi-host Cluster Systems. The following uses the Oracle 8.0.6 Parallel Server as an example to describe how to design a super large database:
Determining the system's ORACLE Parallel Server application Division Policy
Database Physical Structure Design
Division and allocation of system hard disks
Considerations for backup and recovery policies
　　Ii. Division Policies for Oracle Parallel Server Applications
The Oracle Parallel Server allows multiple INSTANCE instances on different nodes to access a database at the same time to improve system availability, scalability, and performance. Each INSTANCE in the Oracle Parallel Server can read the table or index data blocks in the shared database into the local buffer, this means that a data block can exist in the SGA zone of multiple INSTANCE instances. Therefore, it is important to maintain data consistency in These buffers. Oracle uses the Parallel Cache Management lock to maintain the consistency of the buffer. Oracle also implements the PCM lock through the I DLM (integrated distributed lock manager, the dedicated LCK process is used to achieve data consistency between INSTANCE instances.
Consider this situation: When INSTANCE1 modifies block x, INSTANCE2 also needs to modify block x. The Oracle Parallel Server uses the PCM lock mechanism to write block x from the SGA zone of INSTANCE 1 to the database data file, and read block x from the data file into the SGA zone of instance2. This is a PING. PING turns the work that can be completed by one memory io into two disk io and one memory io. If the system has excessive PING, the system performance will be greatly reduced.
Each PCM lock in the Oracle Parallel Server can manage multiple data blocks. The number of data blocks managed by PCM locks is related to the number of PCM locks allocated to a data file and the size of the data file. When INSTANCE 1 and INSTANCE 2 need to operate different blocks, if these blocks are managed by the same PCM lock, PING will still occur. These PING operations are called false ping. When multiple instances access the same BLOCK, the PING generated is true ping.
Reasonable Application Division allows different applications to access different data, which can avoid or reduce the value of true ping; by assigning more PCM locks to data files with more 'false' pinging, you can reduce the number of 'false' pinging attempts, and increasing the PCM locks cannot reduce 'true' pinging.
Therefore, the purpose of the Oracle Parallel Server design is to make the system transaction processing reasonably distributed among INSTANCE instances, to minimize the PING, and rationally allocate PCM locks to reduce the false ping. The key to design is to identify possible conflicts and decide the application Division strategy. There are four ways to divide an application:
1. Based on functional modules, different nodes run different applications
2. Users of different types run on different nodes.
3. Based on Data Division, different nodes access different data or indexes.
4. By time, different applications run in different time periods
The two important principles of application Division are to minimize PING and roughly balance the load of each node.
　　Iii. Database Physical Structure Design
The physical database structure design includes determining the physical storage parameters of tables and indexes, determining and allocating database tablespaces, determining the initial rollback segments, temporary tablespaces, and redo log files, determine the main initialization parameters. The physical design aims to improve the system performance. The parameters of the entire physical design can be adjusted according to the actual running conditions.
Table and index data size estimation and physical storage parameter settings
The Storage Capacity Estimation of tables and indexes is determined based on the record length and the estimated maximum number of records. In capacity computing, the head overhead of data blocks and the overhead of records and fields are considered. The initial and next storage parameters of tables and indexes are generally set to equal, and pctincrease is set to 0.