Design of an ultra-large Oracle database application system

First, Introduction Ultra-large systems are characterized by: 1. The number of users to deal with is 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 function, the system needs to operate without downtime, requiring a high availability and scalability of the system. 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 • Design of database physical structure • The Division and distribution of the 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 allows the original 1 MEMORY io to 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 Function module division, the different node operation different application 2. According to the user division, different types of users run on different nodes 3. Depending on the data partition, different nodes access different data or indexes 4. According to the time division, the different application runs in the different time period 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. Design of table Space The tables and indexes of an Oracle database are stored in the database through a tablespace tablespace. The tablespace design is generally considered as follows: 1, the General large table or index to assign a single tablespace. 2, read only object or read mostly object into a group, there is a corresponding tablespace. 3, if the object in the Tablespace is read only object, you can set the tablespace into read only mode, in the backup, read only tablespace just back up once. 4, the high frequency inserts the object to divide into a group, exists the corresponding tablespace. 5, add, delete and change the object into a group, there is a corresponding tablespace. 6, tables and indexes are stored in different tablespace. 7. The extent size of a table (or index) stored in the same tablespace is best multiplied by multiple relationships, which is beneficial to the reuse of space and the reduction of fragmentation. DB Block SIZE Large database db block size is typically 4KB to 64KB, while the most commonly used is 8KB, 16KB, or 32KB. Choosing a larger DB block size can reduce the height of the index and also improve IO efficiency. Redo Log Files ORACLE writes logs to the log file using a dedicated process redo log writer (LGWR). The general log file is best built on a dedicated mirrored disk. The number of log file groups and the size of the file is related to the size of the system transaction volume. Each instance in an Oracle parallel server uses its own set of rego log files. The average number of log files per group is 3-7, each size is 200MB-500MB. Data File Size Recommended standard file size, such as 200M, 1GB, 2GB, 4GB, 8GB, can simplify the maintenance of space. Rollback segment The rollback segment is typically built into a dedicated tablespace. Each instance instance has its own rollback segment. The general principle for setting up a rollback segment is that the values of the initial and next storage parameters are equal, as well as multiples of the DB block size. The minextents of each rollback segment is set to the value of the 20,optimal parameter to ensure that the rollback segment is reduced to no less than 20 extents. Temporary table space Temporary table spaces are typically built into a dedicated tablespace. Each instance instance has its own temporary tablespace. There will be no ping when using temporary tablespace space. Sets the initial=next of the temporary table space. Division and distribution of the system hard disk In a multiple-machine cluster environment, Oracle parallel servers share the same database through the DRD services provided by the operating system. Each instance access to the data files of the database is performed through the DRD service where the data file resides. Consider the following: Host 1 on the DRD Service 1, the service corresponding to the data file 1, 2, 13, 35, 67, etc., if the host 2 on the INSTANCE2 need to read data file 13, through the DRD service scheduling, host 1 through the DRD service access to the disk array data file 13, Read the INSTANCE2 needed data into memory and upload the data via memory IO to the INSTANCE2 of host 2. Write operations are the inverse process of read operations. Through the above analysis, the system hard disk division and distribution principle is to minimize memory IO. V. Considerations for backup and recovery strategies Database backup and recovery plays a very important role in the system design. A good backup and recovery strategy can reduce the risk of running the system and reduce the loss caused by hardware failure. Oracle Backup methods: 1. Physical Backup The physical files of the database are backed up to the backup media through the operating system's commands or tools. Physical backups are often used to recover data from a database system when a storage media failure occurs. Depending on how the database is run, different physical backups can be performed: A physical cold backup (offline Backup) Physical cold backup requires that the database be closed (all instances stopped). This backup must be a full backup, that is, to back up all data files, control files, log files (redo log file), initial parameter files, and so on. Physical cold backup steps are simple, but require the system to be able to stop. b Physical Hot backup (online backup) Physical hot Backup is a database backup that is performed while the database system is running normally. This backup can be a partial backup of the database, either by backing up a table space (tablespace) or a data file (datafile) of the database, or by backing up the control file. Physical hot backup requires the database to run in Archivelog mode. This type of backup is typically used in situations where the application system cannot be shut down. C Archive Log files backup (archived log file backup) In order for the database system to revert to the state of a moment before the point of failure, or revert to a specified time state, the database must be in Archivelog mode. In Archivelog mode, the database system produces an archive log file (archive log files). Archive log files also need to be backed up to backup media. These files can restore the database to its most recent state when it is restored. The archive log files are generated in the specified directory, and these files are a lifetime achievement can be backed up to backup media, and DBAs can periodically back them up based on disk space conditions. 2. Logical Backup A logical backup is an export tool provided by Oracle that unloads the structure definition and data of a database into a file in a particular format and backs up the file. In practical applications, logical backups are used with physical backups. In general, physical backups are used for disk media corruption or for data file corruption, and logical backups are used for some objects in the database to be corrupted or for users to operate incorrectly. Backup strategy considerations are mainly in the following three areas: Storage space Impact on the performance of the current operating system Impact of recovery time If you need to save space and restore time, you need to increase the frequency of backups, but the backup operation will significantly increase the load on the currently running system. 、 Oracle's recovery approach Different methods of recovery are used according to different backup methods. Using physical backup to restore Oracle offers three ways to recover: 1, database-level recovery 2. Table space (tablespace) recovery 3, the recovery of data files Database-level recovery requires that the database be in a shutdown but Mount state. The recovery of tablespace and data files can be performed in a state where the database is running. Using logical Backup Recovery A logical backup can be restored when an object in the database is corrupted, or if the user's misoperation causes the data to be destroyed (such as by deleting the table). A logical backup can only be restored to the state of the backup time. 　　In a word, the design of database system is a profound knowledge. This paper is based on the experience and lessons learned from the management of the Super large billing system for several years, and is based on the reference ORACLE8.0.6 documentation. Because of my talents, inevitably have improper and wrong place, please the people of insight critical correction.