Explain the concepts of Oracle Database processes in a simple and in-depth manner (1)

Oracle Database Process Overview:

Database buffer cache is mainly used to store data blocks in data files.

Data blocks in the database cache are the basic unit for information exchange between the cache and data files. Before Oracle Database 8 I, there was only one type of data block size. After Oracle Database 9i, there were two types of data blocks: 2 K, 4 K, 8 KB, 16 KB, and 32 KB.

Run SQL> SHOW PARAMETER DB to display the BUFFER CACHE of the current database.

The database cache and features are as follows:

<1>. Least recently used LRU.

<2>. The size is specified by DB_BLOCK_SIZE.

<3>. ORACLE uses block size for data access.

It also contains the following independent sub-caches:

<1>. DB_CACHE_SIZE.

<2>. DB_KEEP_CACHE_SIZE.

<3>. DB_RECYCLE_CACHE_SIZE.

Note: You can use Alter system set db_cache_advice = on; to specify the size based on the suggestions of the system.

Redo log file is mainly used to REDO logs.

Run SQL> SHOW PARAMETER LOG_BUFFER.

SQL> ALTER SYSTEM SET LOG_BUFFER_SIZE = 60 M.

Used to display or modify information about the redo log buffer.

Command SQL> achive log list to view the current archiving mode of the database.

Large pool:

Like java pool, it is used as an optional system memory structure and configured by SGA.

Java pool:

It is used only when JAVA is installed or used.

The ORACLE memory process includes two parts: SGA and PGA.

(1). SGA is allocated when the ORACLE instance is started. It is a basic component of the ORACLE instance.

(2). PGA is allocated when the server process starts.

Process Structure:

<1>. user process. Used for the PROCESS in which the USER interacts with the oracle server.

User process must be connected to ORACLE before it can be used. It cannot interact with oracle server.

<2>. server process.

Server process is a PROCESS that executes USER commands after a connection is established between the user and the service.

<3>. background process.

Background process: database writer (DBWn) is used to write the dirty DATA in the data buffer cache back to the DATABASE.

(Note: dirty data is changed .)

It writes data to the database in the following cases:

<1>. In the event of CHECKPOINT synchronization.

<2>. Dirty data reaches the threshold.

<3>. The free space of the database buffer cache is too small.

<4>. TIMEOUT (3 seconds .)

<5>. rac ping request.

<6>. tablespace offline.

<7>. tablespace readonly.

<8>. table drop or table truncate (clear TABLE data OR delete TABLE structure ).

<9>. tablespace begin backup.
 

By SGA (including database buffer cache, background process) -----> [DBWn] ----> {data files,

Control files, redo log file} ----> This process changes the data in the database.

Log writer (LGWR) LOG Writing Process

In the Oracle environment

<1>. When COMMIT is enabled.

<2>. When 1/3 space is full.

<3>. When the log has 1 MB to be redone.

<4>. Every 3 seconds

<5>. BEFORE DBWn WRITES. (write logs BEFORE writing data ).

Redo log buffer -----> LGWR ---> DBWn

| --------------------------------------> REDO LOG FILES

System monitor (SMON) SYSTEM monitoring process