Oracle Study Notes (basic concepts)

Oracle learning notes (basic concepts) physical structure data files, log files, control files, parameter file system global zone (SystemGlobalArea) the user process, server process, and background process data file of the Shared Pool, data buffer, and log buffer process are only associated with one database. Once a data file is created

Oracle learning notes (basic concepts) physical structure data files, log files, control files, parameter file System Global Area) the Shared Pool, data buffer, log buffer process, server process, and background process data file are associated with only one database. Once a data file is created

Oracle Study Notes (basic concepts)

Physical Structure

Data files, log files, control files, parameter files

System Global Area)

Shared Pool, data buffer, log Buffer

Progress

User process, server process, background process

Data Files

• Each data file is associated with only one database.

• Once a data file is created, its size cannot be modified.

• A tablespace can contain one or more data files.

Log Files

• Each database must contain at least two Log File groups.

• Log File groups are written cyclically.

• Each log file member corresponds to a physical file.

The Log Switch is set to realize the recycling of Log File groups. The log switch may occur as follows:

• When a log file group is filled

• When the database is closed

• DBA manual transfer log Switch

Image Log Files are copies of two or more online log files on different disks to prevent log files from being lost.

Its features are as follows:

• Each log file group must contain at least two log file members.

• The number of members in each group is the same.

• All members in the same group are modified at the same time.

• The size of members in the same group is the same, and the size of members in different groups can be different.

A Control File is a small binary File used to describe the database structure.

The description is as follows:

• Database creation date.

• Database Name.

• Names and paths of all data files and log files in the database.

• The synchronization information required to restore the database.

The Parameter File is a text File that can be directly modified using the text editor in the operating system. The file is accessed only when a database is created or the instance is started. You must disable the instance before modifying the file.

Initial Parameter file: init. ora

Generate the parameter file: initSID. ora config. ora

Role of the parameter file:

• Determine the size of the storage structure.

• Set all default values for the database.

• Set the database range.

• Set various physical properties of the database.

• Optimize database performance.

INSTANCE)

It is a software mechanism for accessing and controlling databases. It consists of System Global Area (SGA) and background processes.

SGA is a group of shared buffer storage areas allocated to instances by the ORACLE system. It stores database data and control information to manage and operate database data.

Shared SQL zone includes

• SQL or PL/SQL statement text

• Syntax analysis of SQL or PL/SQL statements

• SQL or PL/SQL statement execution plan

The data dictionary area is used to store data dictionary information rows.

A Shared Pool consists of a Shared SQL area and a data dictionary area. The SHARED_POOL_SIZE parameter determines the size of the Shared Pool.

Data Buffer Storage is divided

• Dirty list

Includes the buffer blocks that have been modified but not written to the data file.

• LRU (Least Recently Used) List

This includes the idle buffer block, the buffer block being accessed, and the buffer block that has been modified but not moved to the dirty list.

A dedicated server opens a separate server process for each user. Applicable to real-time systems.

Do not set parameters:

MTS_SERVICE

MTS_DISPATCHERS

MTS_SERVERS

MTS_LISTERNET_ADDRESS

The multi-clue server allocates server processes to each user process through the scheduler. Set parameters:

MTS_SERVICE

MTS_DISPATCHERS

MTS_SERVERS

MTS_LISTERNET_ADDRESS

• MTS_SERVICE: the server name. The default value is DB_NAME.

• MTS_DISPATCHERS: Number of schedulers

• MTS_SERVERS: Number of server processes that can be started

• MTS_LISTERNET_ADDRESS: SQL * NET Listener address

• MTS_MAX_SERVERS: Maximum number of Server Processes

Main background processes include

• DBWR database write process

• LGWR log Writing Process

• CKPT checkpoint write process

• SMON system monitoring process

• PMON process monitoring process

• ARCH archiving process

• RECO process recovery

LCKn: Process Blocking

DBWR (Data Base Writer)

Write all the modified buffer block data in the data buffer to the data file, and use the LRU algorithm to keep the data block in the buffer as recently used to reduce the number of I/O times. This process is automatically started when the instance is started.

DBWR write operations:

• The Dirty List has reached the minimum limit.

It is equivalent to half of the value of DB_BLOCK_WRITE_BATCH.

• A process scans a specified number of buffer blocks in the LRU list and does not find any idle buffer blocks.

The DB_BLOCK_MAX_SCAN_CNT parameter determines the number of scans.

• Timeout

If the process is not active within 3 seconds, the process searches for unsearched buffer blocks in the LRU list. The number of these blocks is twice the value of DB_BLOCK_WRITE_BATCH.

• Check points appear.

LGWR write operations:

• A user process commits a transaction (Commit)

• The log buffer reaches the range of 1/3

• DBWR needs to clear the buffer block for a checkpoint

• Timeout occurs (if no activity is performed within 3 seconds, a write operation is performed .)

Checkpoint)

During a checkpoint, the DBWR process writes all the dirty buffer blocks in the data buffer to the data file. The LGWR process writes all the record items in the log buffer to the log file, to ensure that all data blocks modified since the previous checkpoint are written to the disk.

• A predetermined number of record items are filled.

The LOG_CHECKPOINT_INTERVAL parameter determines the predetermined number.

• Set the specified number of seconds.

The LOG_CHECKPOINT_TIMEOUT parameter determines the interval seconds.

• Each log Switch

• When the instance is closed

• DBA manual operation.

• The tablespace is offline.

ARCH (Archiver)

When the log file group is switched, copy the content of the old log file to the offline storage media. If the media fails, it is used to restore data.

• LOG_ARCHIVE_START

Determines whether the process is started or not.

• LOG_ARCHIVE_DEST

When the database is in archive mode, this parameter determines the log file archiving target.

• LOG_ARCHIVE_FORMAT

When the database operates in archive mode, this parameter determines the default file name format of the archive log file.

SMON (System Monitor)

Responsible for automatic instance recovery. This process is automatically started when the instance is started.

PMON (Process Monitor)

Undo the user process with an abnormal interruption and release the system resources or locks that the process has obtained.

RECO (Recover)

In the case of distributed operations, a transaction fails to be restored.

LCKn (Lock)

Apply locks to parallel server systems. A maximum of 10 locks can be added, including LCK0, LCK1, latency, and LCK9.

Three phases of SQL statement Processing

• Syntax analysis

• Execution

• Return command

The logical structure of ORACLE is composed of one or more tablespaces.

• A tablespace consists of a group of classification segments

• A segment consists of a set of ranges

• A range consists of a batch of database Blocks

• One database block corresponds to one or more physical Blocks

A Database Block is the smallest I/O unit used by a Database, also known as a logical Block or an ORACLE Block.

A database block corresponds to one or more physical blocks. The block size is determined by the DB_BLOCK_SIZE parameter.