"Reprint: Oracle Basics" Oracle Database Architecture

Let's take a look at an intuitive picture:

Oracle 11gArchitecture Diagram

Understand the Oracle architecture to fully understand three concepts : database, database instance (instance), session, Be sure not to confuse these three concepts.

The concept of a database

(A) The physical storage structure consists of data files, control files, redo log files :

1. data file (datafile)

SELECT * from Dba_data_files;

Data files include intermediate results for operations such as storing tables and index data, and sorting and hashing. A data file can belong to only one database, and one database may contain multiple data files.

2. Control Files (controlfile)

SELECT * from V$controlfile;

The control file records the physical storage structure and other control information of the database, such as the database name, the time stamp to create the database, the storage path and name of each data file that makes up the database and the redo log file, the checkpoint information of the system, and so on.

The role of the control file:

When the Oracle database is open (mount to open stage), you must first open the control file to read the data file and redo the log file information. If the control file is corrupted, the database cannot be opened, causing the user to have no access to the information stored in the database.

During instance recovery, the control file checkpoint information determines how the Oracle instance uses the Redo log file to recover the database.

3. Redo log file (Redo logfile)

SELECT * from V$logfile;

SELECT * from V$log;

The Redo log file records all the changes that are generated by the database. You can use redo logs to recover the database if the instance or media fails.

Redo Log Filegroups Store database redo log information, which is called an online redo log file. Each database must have at least two sets of redo log files. The Oracle instance uses the Redo log filegroup of the database in a circular write manner.

In archive mode, after the log file switchover occurs, the filled redo log files are copied to other places to save. These log file copies are referred to as archive log files.

(B) The logical structure consists of a table space (tablespace), a segment (segment), an interval (extent), a data block

A table space contains multiple segments, one segment contains multiple intervals, and one interval contains multiple blocks of data.

Table Space:

A tablespace is a logical container that is located at the top level of a logical storage structure. The data in the tablespace is physically stored in the data file. A table space can contain one or more data files. However, a data file can belong to only one table space.

The table spaces created by default for Oracle 11g systems are:

System: Systems table space. Data definition information that is used to store the entire database.

Sysaux: The secondary tablespace of the system table space, which stores data for some components and products to mitigate the load on system table space.

Temp: temporary table space. Used to store temporary data generated during SQL statement processing.

Undotbs1:undo table space, which is used by Oracle databases to store restore information for operations such as rollback.

User: The default table space for users. Used to store persistent user objects and data.

Paragraph

A segment is a database object that occupies storage space and is used to store and isolate data from different database objects.

Table segment: That is, the data segment.

Index segment

Rollback segment: For database restore information

Temporary segment

Interval:

The interval is the smallest allocation unit of Oracle storage space.

Data BLOCK:

The data block is the smallest I/O unit of Oracle.

Oracle Database physical storage structure and logical storage structure relationships such as:

DB Instance (instance )

An Oracle DB instance consists of a memory structure and a background process.

1. system Global Zone SGA(Systems Global Area)

(a) data buffer Cache

To reduce the physical I/O times of the database and improve performance, the data block is cached in the data buffer afterOracle retrieves the data from the disk data file or before writing the block to disk.

Oracle Database blocks can use 8KB ( standard block ),2KB,4KB,16KB,32KB. Data buffers are also divided into standard block buffers and non-standard block buffers.

Sql> Show Parameter Db_block_size

NAME TYPE VALUE

------------------------------------ -----------

Db_block_size integer 8192

Note: The Keep pool, loop pool, and default pool belong to Data_buffer_cache

(b) shared pool

The shared pool size is specified by the parameter shared_pool_size parameter, which is divided into the data dictionary cache, the library cache, and the server result cache.

Data dictionary cache (dictionary cache): first executed SQL code, the server process first parses the code and generates an execution plan. You need to retrieve the database objects and their definitions, users, and permissions for SQL statement Operations during the parsing process. This information is stored within the database dictionary of the database.

Library Cache : The execution plan used to cache parsed SQL statements. When the server executes the SQL code, it first finds its execution plan from the library cache and, if found, reuses the code, which is called soft parsing or library cache hit. Otherwise, hard parsing is performed.

Server result cache: a result set used to cache SQL statement query result sets and pl / SQL functions. (Cache data Block)

(c) redo log buffers (redo log buffer)

The server process writes the redo log generated during data modification (insert,update,delete) to the redo log buffer, and the log is written to the process LGWR Writes the redo log in the log buffer to the online redo log file on disk.

(d) Tai chi (Large pool)

A large pool is an optional memory area that is set by the large_pool_size parameter and is used to allocate large chunks of memory that are not allocated within the shared pool. Buffers that are required by the RMAN backup, and the buffer used by the statement in parallel execution.

(e) java pools (Java pool)

(f) flow pools ( stream pool)

About SGA You can query a view

SELECT * from V$sga_dynamic_components;

2. background Processes (background process)

(a) progress monitoring Process Pmon ( process Monitor)

L Monitor the operation of other background processes, server processes, and scheduling processes. When they are abnormally interrupted, restart these processes or terminate the instance run.

After the user process is interrupted abnormally, it is responsible for cleaning up the database buffer cache and releasing the resources locked by the user process.

L REGISTER the DB instance to a running listener.

(b) systems monitoring Process SPOM ( System Monitor)

Smon is responsible for a large number of system-level cleanup tasks:

When the instance is started,Smon performs an instance recovery if needed.

L Clean up temporary segments that are not in use

• Merging dictionaries to manage adjacent idle areas in a table space

(c) database writer process DBWR

Responsible for writing data blocks modified in the data buffer cache within the SGA to the data file.

(d) log write process LGWR (log writer) and archive process ARCH

Responsible for writing the redo log of the log buffer cache to the redo log file.

In archive mode, after the log file switchover occurs, the filled redo log files are copied to other places to save. These log file copies are referred to as archive log files.

(e) checkpoint progress CKPT (checkpoint process)

Within the Oracle data block, the checkpoint process starts periodically, writes checkpoint information to the control file and the data file header, and notifies the dbwn process to write dirty data to the data file. The dbwn process runs and starts the LGWR process writes the contents of the Redo log file buffer to the redo log file.