Oracle Database 11g Architecture

I. Oracle database structure

1. Logical Structure

1.1. Data Block (Orale block): A certain number of bytes of disk space in the operating system's storage system. The data block is the smallest logical part of the Oracle database. can be defined as 2K, 4K, 8K, 16K, 32K, or larger, often referred to as oralce blocks.

Size of the Orale block: the db_block_size parameter setting in the initial file Init.ora. is the smallest unit that handles Oracle updates, select, and Insert database transactions (pages that can be considered equivalent to SQL Server). The operating system also has a disk block size, so the size of the block should ideally be a multiple of the disk block size.

Show parameter db_block_size;--view block size

Scheme of block size selection:

(1) If the row is small and the access is random, select a smaller block size. For example, handling small rows requires a large number of index lookups.

(2) If the row is small and the access is mainly continuous (or random and continuous), or a larger row, select a larger block size. such as reports.

(3) If you are unsure, the Oracle recommended size is 8K.

Multiple block sizes can be configured in the same database (the corresponding sub-caches in the buffer cache of the SGA need to be configured) and are used primarily for routing table space between databases with different database block sizes.

The inner structure of the data block (the row data section, the Free space section, and some other small parts: the overhead and header space used by the data block)

The row data section includes data that is stored in a table or index. The free space portion is the space left by the Oracle block, in order to give new data to be entered or to extend the space left by existing rows

1.2, District (extent): Adjacent data blocks form a zone. When creating database objects such as tables or indexes, allocate an initial amount of space (initial extent) and specify the amount of space for the next area. The extents retain them until the object is deleted. Once the object is deleted from the database, the zone space is also returned to the pool of available space that the database can allocate.

1.3, paragraph (segment): A group of District composition section. Oracle says that all of the space allocated to any database object is a single segment. If a segment is filled, Oracle allocates additional extents as needed, which may be nonadjacent.

1.4. Tablespace (tablespace): A logical entity that contains physical data files. System, Sysaux, undo (undo), Temp (temporary), default parameters, these 5 are the table spaces that the database must have.

There can be different Oracle block size table spaces in the same database, we can put large object (LOB) data in other table spaces, and set large-sized Oracle blocks to improve query speed.

A large file table space is simply a tablespace with a large data file.

A small file table space can contain multiple data files.

A temporary tablespace contains only data for the duration of a user's session, usually only for data classification or similar activities.

The permanent table space is all table spaces except for the temporary table space.

Undo Table Space contains undo records, Oracle rolls back or revokes changed database

Read-only tablespace does not allow write operations on data files for tablespace.

--Create large table space create bigfile tablespace bigtablespace datafile  '/mnt/db1/ BIGTABLESPACE01.DBF '  size 500m autoextend on;--create small table space, add data file (default Oracle creates small Tablespace press) create  TABLESPACE TABLESPACE_NAME LOGGING DATAFILE  '/mnt/db1/tablespace_name.dbf '  size  2048M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; ALTER  tablespace tablespace_name add datafile  '/mnt/db1/tablespace_name02.dbf '  SIZE  50m autoextend on next 100m maxsize unlimited;--Create a temporary tablespace and modify the default temporary table space create  temporary tablespace tablespacee_temp tempfile  '/mnt/db1/tablespacee_temp.dbf '  SIZE  50m AUTOEXTEND ON NEXT 50m MAXSIZE 20480m; ALTER DATABASE  default temporary tablespace tablespacee_temp;--Set the default tablespace for the user alter user user_name  Default tablespace tablespace_name temporary tablespace tablespacee_temp; 

2. Physical Structure

2.1. Data file stores table and index data

The capacity and db_block_size of the data file can be set to 4K, 8K, 16K, 32K, 64K when the database is created,

The maximum number of data files in an Oracle small tablespace is 4,194,304 blocks. So the maximum value is 4194304*db_block_size/1024m:

4K Max data file: 16384m=16g

8K Max data file: 32768m=32g

16K Max data file: 65536m=64g

32K Max data file: 121072m=128g

64K Max data file: 262144m=256g

Oracle Large table space has a maximum data speed of 4G, so the data file size 8t-128t

2.2. Control file records all changes to the database structure, including the name and location of the data file and redo log file, the current log sequence number, the backup set details, and the SCN (System change number).

--View Control file status, path and name, whether it is placed in the Flashback recovery Area Select status, name, Is_recovery_dest_file from V$controlfile;

Oracle recommends multiplexing control files.

--View Control file information Show PARAMETER Control_filesselect * from v$controlfile;--adjust multiplexing based on SPFile-initiated alter system set control_files= '/ Mnt/db1/controlo1.ctl ', '/mnt/db2/controlo2.ctl ', '/mnt/db3/controlo3.ctl ' scope=spfile;--close the database shutdown immediate--copying physical control files to a new directory--Start the database

2.3. Redo log files (redo log file) change information to the table data. The current log file is called an online redo log, which distinguishes the old saved archive log.

Redo logs are made up of redo records, which are changed vector groups, each pointing to a specific change in the data block.

The contents of the start log are retained in the Redo log buffer (memory area), then transferred to disk, and if the database is suddenly down, the redo log will help determine whether the transaction before the crash is committed.

Turn on archive logging mode

Shutdown immediate;startup mount;alter Database archivelog;alter database open;archive log list;

Oracle recommends multiplexing redo log files.

--View Log file information select * from v$log;select * from v$logfile;--Add Group Alter database  add logfile group 5  ('/mnt/db1/oradata/redo05.log ')  size 50m;--add Group member alter  database add logfile member  '/mnt/db1/oradata/redo01b.log '  to group 1 , '/mnt/db2/oradata/redo02b.log '  to group 2, '/mnt/db2/oradata/redo03b.log '  to group  3, '/mnt/db2/oradata/redo04b.log '  to group 4, '/mnt/db2/oradata/redo05b.log '  to  group 5;--Switch 5 times logfile, so that all 5 groups of logs are in effect, so far, the database redo log multiplexing is complete. alter system switch logfile;--Delete Log Group members--if only one member of the reorganization cannot be deleted--the currently used log group member cannot be deleted, first delete the other and then switch the log group, then delete the Alter  database drop logfile member  '/mnt/db1/oradata/redo01b.log ', '/mnt/db2/oradata/ Redo02B.log ', '/mnt/db2/oradata/redo03b.log ', '/mnt/db2/oradata/redo04b.log ';--delete the log group, the status of the log group must be inactive, Physical files to manually remove alter database drop logfile group 5;

2.4. Other documents

The 2.4.1, server parameter file (SPFILE), which describes the memory limit values of the instance, controls the location of the file, whether the archive log is saved and where, and some other server settings. The file is a binary file that can be converted to pfile for editing

--Create Spfilecreate pfile= '/home/oracle/pfile.ora ' from spfile;--Modify the value of pfile and restore create SPFile from pfile= '/home/oracle/ Pfile.ora ';

2.4.2, a password file, is an optional file that specifies the name of the database user who is granted SYSDBA, Sysoper administrative permissions, and the actions that these users can perform.

2.4.3, alert log files, Alterdb_name.log, capture the main changes and events during instance run, including log switching, errors, warnings, and other messages. Use the following command to view all error messages.

grep ora-alterdb_name.log

2.4.4, trace files, all diagnostic files are stored in the directory specified by the diagnostic_dest initialization parameter.

2.4.5, back up files, restore data files after a media failure or user error.

II. structure of the Oracle process

1. User Process

Connect user users to database instance applications: Sql*plus, SQL Developer, and more.

2. Oracle Process

2.1. Server process

A process that serves a separate user process.

2.2. Background process

Database writer (DBWN): All Oracle data modifications are made in memory, and then DBWN is responsible for writing dirty data in the database buffer, which is changed, to disk. Using the least recently used algorithm to execute the process, the trigger condition (1) publishes the Checkpoint (2) cannot find a reusable buffer (3) executes the process immediately every 3 seconds. There are up to 20 write processes (DBW0-DBW9,DBWA-DBWJ), which can be specified by db_writer_processes and not specified based on the number of CPUs and processors allocated.

Log writer (LGWR): The contents of the redo log buffer are transferred to disk, and when the data is changed, Oracle writes uncommitted changes to the redo log buffer, and the log writer writes the changes from the redo log buffer to the disk redo log file. Trigger condition (1) every 3 seconds (2) Redo log buffer is populated One-third (3) data written to disk (3) Transaction commit

Checkpoint (CKPT): Responsible for notifying the database writer process to write dirty data from the memory buffer to disk. The checkpoint process then updates the data file header and the control file. Step (1) Log writer (2) The checkpoint record writes to the Redo log file (3) The contents of the database buffer cache are written to disk (4) Checkpoint updates the header of the data file and the control file.

Process Monitor (Pmon): After a user process fails, Pmonc clears it immediately, releasing the resources that the dead process consumes. Restart the failed server process and scheduler process for a long period of inactivity and periodically start to see if it needs to be performed.

System Monitor (Smon): an instance of the system monitoring task that starts when needed. (1) After the crash instance restarts, check that the database is consistent (2) manage the tablespace locally, Smon merge the availability zone, and allocate the larger contiguous availability zone of the disk to the database object (3) to clear unnecessary temporary segments. Start when needed.

Archiver (ARCn): The database runs in archive mode, and each filled log file is saved in a specific place. Up to 30 archiver processes (ARC0-ARCN), initialization parameters log_archive_max_processes parameters determine how many archiver processes Oracle initiates initially (no need to set in advance). This is a dynamic parameter that can be changed in the database run:

ALTER SYSTEM SET log_archive_max_processes=10;

ASM-related processes: You must create an ASM instance (rebalance (Rbal), ASM rebalance (ARBN), ASM background) when using ASM

Manageability Monitor (Mmon): Collects centralized statistics, such as awr snapshot information, and warns you about the various dimensions of a database that violate thresholds.

Manageability Monitor Lights (MMNL): Flushes ash data to disk, other manageable tasks, captures session history data, and calculates database metrics.

Memory Manager (Mman): Resizing a memory part

Job Queue tuning Process (CJQO): schedules and runs user jobs, cjqo the dependent processes (J000 to J999) that generate the job queue dynamically.

Recovery Device (RECO): Used to adjust distributed databases and other special processes.

Flashback Data Archiver (FBDA): Responsible for writing changes to the table (the table that the flashback data is archived to the history table).

Result set cache backend (RCBG): Responsible for managing the result set cache.

Third, Oracle memory structure

This article from "Felixchen Blog" blog, reproduced please contact the author!

Oracle Database 11g Architecture