Teach you to design a large Oracle database

Ultra-Large system features: 1, the number of users are generally more than million, and some more than tens of millions of data, the database generally more than 1TB;

First, Introduction
Ultra-large systems are characterized by:
1, the number of users are generally more than million, and some more than tens of millions of data, the database generally more than 1TB;
2, the system must provide real-time response functions, the system needs to operate without downtime, the system requires a high availability and scalability.
In order to meet the above requirements, in addition to the need for superior performance of the computer and mass storage equipment, but also the need for advanced database structure design and optimization of the application system.
In general, the super large system adopts dual or multi machine cluster system. The following is a database using Oracle 8.0.6 parallel server as an example to talk about the design method of the Super large database:
Identify the system's Oracle Parallel server application partitioning strategy
The design of the physical structure of database
Partition and distribution of system hard disk
Considerations for backup and recovery strategies
　　 Second, Oracle Parallel server application partitioning strategy
Oracle parallel servers allow multiple instance instances on different nodes to access a database simultaneously to improve system availability, scalability, and performance. Each instance instance in an Oracle parallel server can read the data blocks of a table or index in a shared database into a local buffer, which means that a block of data can exist in the SGA area of multiple instance instances. It is important to maintain the consistency of the data in these buffers. Oracle uses PCM (Parallel Cache Management) locks to maintain buffer consistency, and Oracle implements PCM locks through the I DLM (Integrated distributed lock Manager) and implements the data consistency between instance instances through a dedicated LCK process.
Consider this situation: INSTANCE1 blocks x block changes, then INSTANCE2 to block x blocks also need to be modified. The Oracle parallel server uses the PCM lock mechanism to write block X from the SGA area of instance 1 to the database data file, and to read blocks x in the INSTANCE2 in the SGA area from the data file. This occurs as a ping. Ping makes the original 1 MEMORY IO can complete the work into 2 disk IO and a MEMORY io can be completed, if the system has too much ping, will greatly reduce the performance of the system.
Each PCM lock in an Oracle parallel server manages multiple blocks of data. The number of data blocks managed by PCM locks is related to the number of PCM locks assigned to a data file and the size of the data file. When instance 1 and instance 2 operate on different blocks, ping will still occur if these blocks are managed by the same PCM lock. These pings are called false ping. A ping that is generated when multiple instance access the same block is a true ping.
Reasonable application partitioning enables different applications to access different data to avoid or reduce the true ping; by giving false ping more data files to allocate more PCM locks can reduce the number of false ping, increase the PCM lock can not reduce the true ping.
Therefore, the purpose of Oracle Parallel server design is to make system transaction processing reasonably distributed between instance instances to minimize ping, while reasonably allocating PCM locks and reducing false ping. The key to design is to identify possible conflicts and determine the strategy for applying partitioning. There are four ways to apply the partition:
1, according to the functional module division, different nodes run different applications
2, according to the user division, different types of users run on different nodes
3, according to the data division, different nodes to access different data or index
4, according to the time division, the different application in the different time period runs
The two important principles of application partitioning are to minimize the ping and to make the load of each node roughly balanced.
　　 third, the design of the physical structure of the database
The physical structure design of the database includes determining the physical storage parameters of the table and index, determining and allocating the database table space, determining the initial rollback segment, temporary table space, redo log files and so on, and determining the main initialization parameters. The purpose of physical design is to improve the performance of the system. The parameters of the whole physical design can be adjusted according to the actual operating conditions.
Table and index data quantity estimation and physical storage parameters setting
The storage capacity estimates for tables and indexes are determined based on their record length and the maximum number of records estimated. The header cost of the data block and the header overhead of the records and fields are considered in the capacity calculation. Initial and next storage parameters for tables and indexes are generally set to equal, Pctincrease set to 0.