Concepts and terminology of Oracle (1)

The racle database system is a complex software system. Without understanding its internal structure principles and relationships, it is impossible to design and Compile High-Quality application software systems or manage a complex application system. To lay a solid foundation for subsequent chapters, this chapter briefly describes the system structure of Oracle 8/Oracle8i databases.

§ 2. 1 terminology

Database Block)

The minimum storage and processing unit in the Oracle database, including the header information data of the block or PL/SQL code.

The size of the Oracle block can be specified by selecting "custom installation" during installation, or when creating a database instance in create database. The minimum value is 2 K and the maximum value is 64 K.

Bottleneck)

A part that limits system performance.

Object-oriented Relational Database Service

It has all the functions of relational databases and supports object-oriented databases, called object-oriented relational database systems. Oracle7 is a fully functional relational database system; oracle8 is an object-oriented relational database system.

Client/Server structure)

It has three elements: client, server, and network. The client (PC) connects and works with the server through the network.

Fat Client)

Generally, clients in the Client/Server structure are fat clients. Because these clients need to configure high devices, such as memory, hard disk, clock speed, and cd_rom.

Thin client)

Also known as NC (Network Computer), it is a kind of memory configuration that is small (usually only 4 MB in the past, which cannot be defined now), no hard disk, only a processor of the processing chip.

Online Data Analysis and Processing (OLAP)

It is a technology that provides quick, interactive, and convenient insight into data conditions. Such as Oracle Express, Cognos (Canada) power play, Business Objects business object, etc.

Multithreading (MTS)

A user can run multiple programs to access the database at the same time. For example, Visual C/C ++ can start multiple processes at the same time, but Visual Basic cannot.

Data Warehouse)

Data warehousing or data warehousing is supported to store a large amount of data information. When several small databases are integrated into a large database and serve a wide range of organizations, if the database stores historical data, it provides decision-making support, data aggregation, and read-only data, in essence, it acts as a data receiver for all the finished databases that provide data to it. Therefore, it is usually called a data warehouse.

Instance)

It is a part of the computer memory required to access the Oracle database and assists in processing background processes.

Object (objects)

Is a software representation that implements world entities, such as tables, indexes, views, synonyms, and processes.

Database File)

Files used to store data (Application System data) in the Oracle system. (<Oracle8 beginner's guide> the examples in p14 are inaccurate and easy to mislead readers ).

Control File)

Oracle stores some data files used by the system. For example, the path and file name of the data file, the initial file path and file name are all stored in the control file.

Log File)

It is also called a transaction record file, which records all modification operations or transactions performed by transactions on the database.

DBA (Database Administrator-Database Administrator)

It is a technical tool or personnel for managing database systems. It provides DBA functions in a complete database system.

Dirty data block)

The raw data stored in the memory has been modified, and the modified data has not been written into the database.

Hot data block)

Stores frequently-modified data called Hot data blocks.

LRU (least recently used-least recently used)

An algorithm used by the Oracle system. For data blocks (memory blocks) that are in the memory but are not used recently, they are called LRU, oracle will remove the data from the memory based on the LRU and free up space to load other data.

Table)

Space created and allocated for storing specialized data.

Table space)

Is a collection of one or more data files. All data objects are stored in the specified tablespace, but they are mainly stored in tables, so they are called tablespaces.

Rollback)

Recover the changed user (or transaction) to the last state.

Undo Information)

The information used for recovery is called recovery information. Rollback is performed based on the recovery information.

 

2. 2 Oracle Database Structure

The Oracle database structure is the main part of the Oracle database system. It is a visible software, including:

Oracle Data Dictionary
Table space and data files
Oracle instance)

§ 2. 2.1 Oracle Data Dictionary

An important part of Oracle databases is data dictionary. It stores the information used by the Oracle database. It is a set of read-only tables for users. The data dictionary includes:

Information about all schema objects in the database, such as tables, views, clusters, and indexes.

How much space is allocated, and how much space is currently used.

Column default value.

Integrity of constraints.

The name of the Oracle user.

Permissions granted to users and roles.

Audit information accessed or used by the user.

Other generated database information.

A database data dictionary is a group of tables and view structures. They are stored in the system tablespace.

The database data dictionary is not only the center of each database. It is also very important to every user. You can use SQL statements to access the database data dictionary.

§ 2. 2.2 tablespace and data files

Simply put, a tablespace is a set of one or more data files (physical files) (logical files ). The following table space is generally used:

1) system tablespace)

It is an essential part of different versions of Oracle. It stores some information about the Oracle system and generally only has one system tablespace.

2) Temporary tablespace (temporary tablespace)

Since Oracle often requires some temporary disk space when working, these space is mainly used for queries with sorting (group by, order by, etc.) algorithms, and will be released immediately after use, the information recorded in the disk area is no longer used, so it is called a temporary tablespace. Generally, there is only one temp temporary tablespace after installation.

3) Tools tablespace)

Used to save the database objects required by the Oracle database tools. These tools store tablespaces in dedicated tables used during work. The tablespace is not available in the old Oracle version. The tool tablespace after installation is tools.

4) User tablespace (users tablespace)

It is used to store the space used in some experiment examples, and of course it can also be used to store the data of the application system. The old version does not have users tablespace. The current user tablespace is users.

5) data and index tablespace

Table spaces that store data and indexes are promoted in application systems after oracle7, especially systems with large amounts of data. Data and indexes should be stored separately in different partitions, this improves the query speed. After installation, the index tablespace may be indx. The data table space is users.

6) rollback segment table space (rollback segment)

The Oracle database system requires a disk space to store the Undo information when executing insert, update, and delete operations, so that the original information can be read during recovery. The tablespace In the rollback segment after installation is the RBS tablespace. The database administrator can also create another tablespace for the rollback segment based on the needs of the application.

Oracle stores logical data in table space and connects physical data.

Tablespace 1 tablespace 2

A database is divided into one or more logical components, which are called tablespaces. The tablespace is also divided into logical parts called segments. These segments are further subdivided into extents ).

§ 2. 2.3 Oracle instances

An Oracle instance is composed of a group of background processes and memory structures. Each running database system is related to an instance. Therefore, Oracle instances are sometimes called database operating systems. The following is an explanation of the Oracle instance.

Oracle Process

Oracle has two types of processes: Server process and background process ).

Server process

An Oracle server process is a transaction that processes user-to-instance connections. The task is:

Analyze and execute the SQL statements issued by the application.

Read the necessary data from the data file to the shared data zone in the SGA area.

Return necessary information to the application.

Background Process

The Oracle system uses some additional processes to handle the necessary work of the system. These processes are called background processes:

Database write (dbw0 or dbwn)
Log write (lgwr)
Detection point (ckpt)
System Monitoring (SMON)
Process Monitoring (pmon)
Archive (arcn)
Restore (reco)
Lock (lck0)
Work Queue (snpn)
Queue monitoring (qmnn)
DISPATCHER (dnnn)
Server (snnn)

Oracle memory structure (SGA)

The SGA structure is:

Data High-Speed Buffer
Redo log Buffer
Sharing pool
Large Shared Pool (optional)
Data Dictionary Buffer
Other data buffers

§ 2. 3 Oracle Database Files

Oracle Database is a complex database operating system consisting of a series of components, including: 1) initialization parameter files, 2) data files, 3) control files, and 4) log files.

§ 2. 3.1 initialize the parameter file

The initialization parameter file (initsid. ora) is the main configuration point of Orale RDBMS. It is a set of configuration parameters and values. Each parameter value controls or modifies a certain aspect of the database and instance. In earlier versions, all parameters were written in initsid. ora file, but after oracle8, most parameters are no longer in this file. to modify these parameters, you can only use set session or set System in SQL>. View the availability of system parameters. For example:

SQL> select * from V $ parameter;

.

The Oracle initialization parameter file is a editable text file. It is accessed when the database instance is started to obtain the path of data files, log files, control files, and so on. In addition, the initialization parameter file also provides the parameters used by the instance.

For the initsid. ora file, see the appendix.

§ 2. 3.2 data files

It is used to store data files of all databases. Multiple necessary data files are automatically created during Oracle installation. These data files are used to store basic data of the Oracle system. During application system development, we can create some data files as needed.

If data files are classified based on their data types, they can be divided:

User Data

The data stored in the application system is user data.

System Data

The management of user data and the data of the database system, such as the data dictionary, the name and type of the table created by the user, are all recorded in the system data.

§ 2. 3.3 Control File

The control files are internal binary files accessed by the Oracle database instance at startup. Their storage paths are determined by the control_files = parameter of the parameter file.

Oracle generally has two or more control files, and each control file record has the same information. If an error occurs in a control file during database operation, Oracle will automatically use another control file. The system cannot work when all control files are corrupted.

The Oracle database must have at least one control file;

Generally, more than two control files are automatically created after the database system is installed;

We recommend that you create multiple control files for security purposes;

The control file can be found using the following command:

Select name, value from V $ parameter where name like 'control _ FILES ';

§ 2. 3.4 redo log files

Redo log files are an important file in the Oracle system. Features:

Redo logs are Oracle journals that record all user objects or system changes;

After the installation is complete, there are multiple redo log files, which are divided into several groups, and the size of the redo log files in the group must be the same;

To improve system performance, you can create multiple Log File groups;

You can run the following command to find the redo log file:

Select * from V $ logfile;

§ 2. 3.5 Other supported files

In addition to the above three types of files, there are:

Sqlnet. ora file;

Tnsnames. ora file;

Listener. ora file.

§ 2.4 redo log and redo log Modes

Redo logs, log files, and redo daily work modes are important concepts in the Oracle system. administrators must fully understand their principles and usage methods. The following is a simple concept. The related management is provided in another chapter.

§ 2. 4.1 redo log

All operations performed by Oracle are recorded in the log file. The Oracle system does not save the disk immediately (write data files) For Every modification of a record, but only makes modification records, online redo logs save all the changes. When all the modifications are finally written to the data file, all the modifications are still recorded in the online redo log, which will facilitate the recovery of these transaction records. However, if the log is not redone online, only some recent operations can be restored.

§ 2. 4.2 redo log Working Mode

Oracle can work in one of two modes:

1) archivelog mode (recoverable)

Oracle generally has at least two log files. They are written in turn with all the changes made. When the system is set to the recovery mode, Oracle automatically backs up the log information to be overwritten (washed out) each time, and then records the modified information on it. In this way, you can use backup and recovery to recover data in a certain period of time.

2) noarchivelog mode (limited recovery)

The default value is noarchivelog. Oracle does not retain the old redo log information (you can overwrite the original basic information ). Therefore, it is generally irrecoverable. § 2. 5 data blocks, intervals, and segments

§ 2. 5.1 data blocks)
Oracle data blocks are also called Oracle blocks;

When creating a tablespace, the Oracle System formats the data file into several Oracle blocks;

Each Oracle block is the minimum Unit processed by the Oracle system;

The block size is determined during system installation. You can select "custom installation" to set the block size;

Once the block size is determined, it cannot be changed;

The block size ranges from 2 K to 64 K;

You can run the following command to check the block size:

Select name, value from V $ parameter where name like 'db _ block_size ';

§ 2. Range 5.2 (extent)

Any continuous block allocated to an object (such as a table) is called an interval;

An interval is also called an extension, because when it runs out of an allocated interval, a new record must be inserted into a new interval (that is, to expand some blocks );

The interval size is determined by next;

Once an interval is assigned to an object (table, index, and cluster), the interval cannot be assigned to other objects;

The following command can be used to check the interval used by an object:

Select segment_name, tablespace_name, count (*) from dba_extents

Having count (*)> 1 group by segment_name, tablespace_name;

Section 2 (segment)

Segment refers to the generic name that occupies the data file space or the set of space used by database objects;

Segments can include table segments, index segments, rollback segments, temporary segments, and high-speed cache segments;

Segments use several Oracle blocks in the same tablespace (which can be located in different data files ).

Example:

Create Table ABC (empno number (4), ename varchar2 (20), Sal number (9, 2 ))

Tablespace user_data storage (initial 500 K next 256 k pctincrease 0 );

1) segment allocated = initial interval = 500 K;

2) 256 K will be allocated after the first 500 k is used up; this time period = 256 K + K;

3) if the allocated range is used up, 256 K will be allocated ,...

Segment (segment), range (extent), and block relationship (seg_ext_block)

§ 2. 6 Oracle Database Process

Generally, when the database is started successfully (the instance is started successfully), at least six background processes are active. These processes are divided according to the needs of the database. They are:

1) database writer (dbwr)

The database writer task writes the modified (in memory) data blocks back to the database file. In some operating systems, Oracle can have two bdwr processes.

2) Check Point (ckpt)

Is an optional process. When a data query request occurs during system operation, the system finds the data from the database and stores the data in the memory zone. In this way, you can modify the data in the memory zone. When you need to write the modified data back to the data file, a switch is generated for the redo log, and the verification point appears. The system writes the information in the gray data (modified) block in the memory back to the data file on the disk. In addition, the system will redo the log notification control file. DBA can change the checkpoint_process true parameter file to enable (make valid or invalid) the process.

3) log writer (lgwr)

The process used to write log information in the SGA area to the log file. Generally, the user's modification value is first recorded in the log file. The modification results will be written back to the data file after a certain period of time.

4) System Monitor (SMON)

System monitor is a mandatory process for recovery when the database system is started. For example, in Parallel Server mode (two servers share one disk group), SMON can restore another database that is in failure. Switch the system to another normal server.

5) Process Monitor (pmon)

Process Monitor is used to terminate failed users and release resources occupied by the user.

6) archive (ARCH)

Optional process. used when the database system is in archivelog mode.

7) Lock (lckn)

Optional process. Multiple locked processes can appear in Parallel Server mode to facilitate database communication.

Recoverer (rdco)

Optional processes used in distributed databases (with different machines and different Oracle Systems in different locations) mode for data inconsistency recovery. Before the reco resolves the restoration, the mark of the modified data is marked as "suspicious ".

9) Scheduling (dnnn)

Optional process, used in multiple threads, that is, for each in use (d000 ,..., dnnn) Communication Protocol creates at least one scheduling process. Each scheduling process is responsible for routing requests from the connected user process to the available server process. Return the response to the appropriate user process.

10) snapshot process (snpn)

The Snapshot process processes the automatic refresh of database snapshots and runs the predefined database process through the dbms_job package. initsid. the ora parameter job_queue_process sets the number of snapshot processes. The parameter job_queue_interval determines the number of seconds for the snapshot process to sleep before being awakened to process suspended jobs or transactions.

11) parallel query process (pnnn)

The Oracle server can start or stop query processes based on the database activity parallel query options. These processes involve the creation of parallel indexes, table creation, and query. The number of started instances is the same as the number specified by parallel_min_servers. The number cannot exceed the value specified by this parameter.

This article from: http://oracle.chinaitlab.com/serial/754900_3.html