Teaching notes-Oracle architecture 02-physical and logical structures of Oracle databases

· Physical Structure of the Oracle database:
The storage structure of the Oracle database includes the physical storage structure and logical storage structure of data. The physical storage structure is the actual data storage unit, and the logical storage structure is the conceptual structure of data.
The physical storage structure of Oracle mainly includes:
(1) One or more data files (. DBF Files in the oracle/product/10.2.0/oradata/orcl folder)
(2) Two or more redo log files (REDO01.LOG files in the oracle/product/10.2.0/oradata/orcl folder)
(3) one or more control files (. CTL files in the oracle/product/10.2.0/oradata/orcl folder)
(4) other OS files
Data Files: You can store database data, including system data (data dictionary), user data (tables, indexes, etc.), undo data, and temporary data. The Oracle Database contains at least one data file. Logically, the database is composed of tablespaces, while the tablespace is physically composed of one or more data files, A data file contains physical disk blocks on multiple operating systems.
Redo log file: used to record database changes, in order to restore the database when the database instance fails or the media fails. Oracle requires at least two redo log file groups. When the Oracle database is running in archive mode, a copy is created for all the redo log files before they are overwritten, that is, all the transactions in the database are backed up, it is the safest way to operate a database and allows expansion of the Restoration function (including restoration at a specified time). However, this method increases the overhead of the system. In non-archive mode, when a redo log file is overwritten, all previous transaction records are cleared. In this mode, only limited recovery capabilities are available, as long as the data is designed to be protected during routine failures.
Control File: used to record and maintain the physical structure of the database. The database must contain at least one control file, which is critical to the control file. Therefore, you should store more pass backups online. These files are stored on multiple disks, to reduce potential risks caused by disk failure. The main control file records: ① the size of the data file, location ② the size of the redo log file, location ③ Database Name, creation time ④ log serial number.
Other files:
① Archive log files (backup files for redo log files)
② Parameter file (used to define the parameters to be initialized when the database instance starts. The parameter files are stored in ORACLE_HOME/db_1/database by default. ora file. The name format of the text parameter file is inti <SID>. ora, the name format of the server parameter file is SPFILE <SID>. ora; SID is a database instance)
③ Password file (stores privileged user information, such as SYSDBA, SYSOPER; db_1/database/pwdorcl. ora)
④ Warning files (consisting of continuous messages and error messages, which are stored in chronological order. You can view Oracle internal errors through warning files or monitor privileged user operations, the location of the warning file is determined by the initialization parameter background_dump_dest. The name format is oracle/product/10.2.0/admin/orcl/bdump/alert_orcl.log)
⑤ Background process trace file (which records the warnings and errors of background processes. Each background process has a corresponding trace file. The storage location is determined by the initialization parameter background_dump_dest. The name format is: <database instance name >_< background process name >_< OS process ID corresponding to the process>. trc)
⑥ Server process tracking file (used to track SQL statements, diagnose SQL statement performance, and make response adjustments. The storage location is determined by the initialization parameter user_dump_dest. The name format is: <database instance Name> _ ora _ <OS process ID corresponding to the process>. trc)
 
· Logical structure of the Oracle database:
The logical structure of a database is user-oriented. It describes how a database organizes and stores data logically. The logical structure of a database determines how a database uses the physical space of the system. The data in the database is physically stored in the data file, while the logic is stored in the tablespace. Oracle databases Use logical structures such as tablespaces, segments, intervals, and data blocks to manage spatial objects.

1. Table space is a logical component of the database. It groups related logical structures. An Oracle10g Database must contain at least one SYSTEM tablespace and one SYSAUX tablespace. It should also contain data table space, index tablespace, temporary tablespace, and UNDO tablespace. A tablespace can belong to only one database instance. Most of the tablespaces in Oracle databases are permanent tablespaces, and temporary tablespaces can also be created. The control information of the tablespace can be saved directly in the data file or in the data dictionary.
(1) SYSTEM tablespace: each database instance must contain at least the SYSTEM tablespace, which is the SYSTEM tablespace, the data dictionary used to store the database, and other unexpected objects in the data table (for example: views, sequences, etc.
Because the SYSTEM tablespace is reserved for storing SYSTEM information, user data objects should not be stored in this tablespace; otherwise, the SYSTEM operation performance and security will be compromised.
(2) SYSAUX tablespace: the auxiliary system tablespace added to Oracle10g is used to reduce the load on the system tablespace and improve the system operation efficiency. The tablespace is created by the system and serves as the default location for storing data.
(3) Non-system tablespaces: Oracle databases usually have several tablespaces for logical and physical storage of data. The following tablespaces are essential and common for most databases.
A> UNDOTBS1 is used to save the transaction rollback information (rollback)
B> User tablespace USERS, which stores users' private information
C> temporary tablespace TEMP, used to store temporary tables and temporary data
D> EXAMPLE table space EXAMPLE, which includes some instance schemes such as human resources and order input.
In fact, as long as the SYSTEM has SYSTEM, SYSAUX, and TEMP tablespaces, the SYSTEM can work normally. You can query the data dictionary DBA_TABLESPACES to see the status of tablespaces in the SYSTEM.
(4) applications of tablespaces in actual projects: For an Oracle database application solution, the solution users should establish their own dedicated data table spaces and index tablespaces for storing user data, A dedicated temporary tablespace should also be created. The goal is to improve data access performance and facilitate data management, backup, and recovery.
(5) Table space status and working mode
① Online tablespace and offline tablespace: the data in the online tablespace is accessible to the database, and the data in the offline tablespace is inaccessible. If necessary, the database administrator can) offline to prevent users from accessing application data. Note: The SYSTEM must always be online because the data dictionary must always be accessible.
② Read-only and read-write tablespaces: newly created tablespaces are always readable and writable. When the data in a tablespace cannot be changed (for example, the historical data used for the data warehouse application), you can set it as a read-only tablespace.
2. A Segment is a physical representation of an object. It consists of one or more segments used to store all data of a specific object, including a specified type of logical storage structure in a tablespace, segments cannot span tablespaces. A segment can only belong to one tablespace. Segment type: table, table partition, cluster, index, table organized by index, index partition, rollback segment, temporary segment, LOB segment, nested table, etc. It can be divided into data segments, index segments, temporary segments, and rollback segments.
Cluster: stores data from multiple tables according to keywords. A cluster Table can contain data from multiple tables.
Index: stores the table keywords and the corresponding record rowid. The index is essentially a table.
Lob segment: for Big Data
3. Range (Extent) is the logical unit for Oracle space allocation, and the smallest storage allocation unit in Oracle databases.
4. Data blocks, also known as Oracle blocks, are the units in which Oracle manages the storage space in data files. It is the minimum unit for Oracle to perform I/O operations on data files. You can modify the initialization parameter DB_BLOCK_SIZE to define the standard block size. A Data block is divided into three regions (Header: the address that stores the Data block, the table directory, the row directory, and the space reserved for the transaction. FreeSpace: reserved for future Data updates, Data :), although this design wastes some space, it reduces the overhead of updating the system significantly, thus greatly improving the system performance.
This article is from the "IT thin" blog