Logical and physical storage structures of Oracle11g

The logical storage structure cannot find the file corresponding to the logical storage structure in the operating system. However, you can find the logical storage endpoint by querying the data dictionary of the Oracle database.

The logical storage structure cannot find the file corresponding to the logical storage structure in the operating system. However, you can find the logical storage endpoint by querying the data dictionary of the Oracle database.

Logical Storage Structure

The file corresponding to the logical storage structure cannot be found in the operating system, but the description of the logical storage structure can be found by querying the data dictionary of the Oracle database.

The logical storage structure includes tablespaces, segments, partitions, and blocks. To put it simply, the relationship between logical storage structures is: Multiple Block components, multiple partition components, multiple segments constitute the tablespace, and multiple tablespaces form the logical database.

One partition can only be in one data file. Each partition in one segment can be in multiple data files. Blocks in the component area are continuous. Because logical blocks correspond to a fixed size in the disk space (generally an integer multiple of the Operating System database), the logical storage structure is also large.

I. Table space)

The following table lists the names and descriptions of automatically created tablespaces in oracle11g.

Name

Category

Description

EXAMPLE

Non-system tablespace

The sample tablespace stores the solution object information and training materials of the sample database.

SYSAUX

System tablespace

Auxiliary system tablespace is used to reduce the load on the system tablespace and improve the system efficiency. Is the new tablespace of oracle 10 GB

SYSTEM

System tablespace

System tablespace stores management information such as tablespace names, control files, and data files, and defines solution objects (such as tables, indexes, synonyms, and sequences, the source code that stores all pl/SQL programs (such as processes, functions, packages, and triggers) is the most important tablespace in oracle databases. It belongs to the SYS and SYSTEM schemes and is only used by SYS and SYSTEM or other users with sufficient permissions. Even SYS and SYSTEM users cannot delete or rename the space. It is the user's default tablespace, that is, when the user creates an object, if no specific tablespace is specified, the data of this object will also be saved in the SYSTEM tablespace.

TEMP

System tablespace

Temporary tablespace stores temporary tables and temporary data for sorting. Each database should have a (or create a) Temporary tablespace so that users can be allocated when they are created. Otherwise, the TEMP tablespace will be used as the temporary tablespace.

UNDOTBS1

Non-system tablespace

Redo tablespace to store information and data related to redo in the database

USERS

Non-system tablespace

User tablespace stores permanent data and private information of user objects. Therefore, it is also called a data table space. Each database should have a (or create a) User tablespace so that users can be allocated when they are created. Otherwise, the SYSTEM tablespace will be used to save data, this approach is not good. Generally, system users use SYSTEM tablespaces instead of USERS.

Ii. segment (segment) (This part has no detailed records)

Segment is used to store data of a specific database object with an independent storage structure in a tablespace. It consists of one or more consecutive zones. When you create tables, indexes, clusters, and other objects, oracle allocates storage space (that is, segments) for these objects to store their data. The data stored in segments has different features and uses. They can be divided into the following types:

Segment type

Name

Segment type

Name

Index partition

Index Partition

Deferred rollback

Delay rollback segment

Table partition

Table Partition

Undo

Undo segment (Restore segment)

Table

Table segment (data segment)

Temporary

Temporary Section

Cluster

Cluster segment

Cache

High-speed cache segment

Index

Index segment

Lob

Binary large object segment

Rollback

Rollback segment

Lobindex

Binary large object index segment

Segments gradually increase with the increase of storage data. The increase of segments is achieved by increasing the number of partitions (each time a partition is added ). The major and minor tasks in each area are counted as integers.

Area 3 (extent)

A zone is composed of physically contiguous blocks. The partition is the minimum unit for oracle storage allocation. One or more blocks constitute one or more blocks. A zone can belong to only one data file.

When you create a solution object (such as a table or index) with the actual storage structure in the database, oracle allocates several partitions for the solution object (depending on the initial size of the solution ), to form a corresponding segment to provide the initial storage space for the solution object. When all allocated partitions in a segment are full, oracle will allocate a new blank area for the segment in the tablespace where the solution object is located to accommodate more data.

4. block)

Block is the smallest data management unit, that is, all the I/o operations stored in oracle are in blocks. Correspondingly, the minimum unit for the operating system to perform I/o operations is the operating system block. The block size is an integer multiple of the operating system block size.

The block size is the attribute of a tablespace. The SYSTEM and SYSAUX tablespaces have the same standard block size. This size is specified by the DB_BLOCK_SIZE initialization parameter when the database is created (the two tablespaces are created simultaneously, however, this parameter value cannot be changed after the database is created.

Physical storage structure

The physical storage structure is the actual data storage unit, which corresponds to operating system files and is easier to understand than the logical storage structure. An oracle database is composed of these operating system files that reside on the disk of the server. These files include control files, data files, and redo log files.

It is related to the oracle database, but technically it does not belong to any attached file of the oracle database, including the password file (PWD. ORA), the parameter file (SPFILE. ORA), and the archived and redone log file.

One operation difference between a physical storage structure and a logical storage structure is that a logical storage structure can only be created or deleted. In addition to creating or deleting a physical storage structure, there is also an important operation to move it to another location (including changing its name), so that it can achieve multiplexing (control file, log file) and arrange them to different disk locations and change names.

I. Control Files

The control file is a very small (usually the smallest in the database) file, the size is generally 1 ~ About 5 MB, which is a binary file. But it is a key file in the database. It is crucial for the successful start and normal operation of the database, because it stores key information that cannot be obtained elsewhere, including:

1. Database Name

2. name, location, online/offline status, and size of the data file and redo log file

3. When a disk fault or user error occurs, it is used to restore the database information (log serial number, checkpoint)

When a database is running, the content of the control file is modified every time a database checkpoint occurs or the database structure is modified. DBA can use OEM tools to modify part of the control file. However, neither DBA nor user should consider it as modifying the content in the control file. Otherwise, the control file will be damaged.

Ii. Data Files

A data file is an operating system file that stores the actual data inserted into the database. Data is written to a data file in an oracle-specific format. Other programs cannot read data in the data file.

The size of data files is directly related to the size of the data they store. After data is written, the new area is automatically allocated, but data deletion does not reduce it. Instead, more free zones are available.

In addition to the SYSTEM tablespace, any tablespace can be switched to offline in the connection status. When the tablespace enters the offline status, the data files that make up the tablespace enter the offline status. You can set a data file in a tablespace to offline for database backup or recovery. Otherwise, data cannot be backed up.

3. redo log files

When a user modifies a database, the data in the memory is actually modified. After a period of time, the modification results in the village are written to the above data files in batches. Oracle adopts this approach mainly for performance considerations, because for data operations, the memory speed is tens of thousands of times faster than the hard disk speed.

Oracle uses the "(online) redo log file" to save the modification results at any time, that is, oracle saves the modification results in memory to the "redo log file" at any time. "Anytime" indicates that the modification results may have been written to the "redo log file" several times before being written to the data file ". Therefore, even if a fault causes the database to crash, oracle can use the information in the redo log file to restore the lost data. As long as the redo information of an operation is not lost, the redo information can be used to reproduce the operation.

Oracle uses redo log files cyclically. Therefore, each database requires at least two redo log files. When the first redo log file is fully written, the background process LGWR (log write process) begins to write the second redo log file. When the second redo log file is fully written, write the second redo log file again, and so on.

4. other files (not carefully read this part)

Other files include parameter files, password files, archived redo log files, and warning and tracking files.