Basic Terms of Oracle

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.

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. (< ;> Examples in Section 14 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 l tablespace and data file l Oracle instance)

§ 2. 2.1 An important part of the Oracle Data dictionary is the 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 in simple terms, 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 tablespaces store data and index tablespaces. After oracle7, it is promoted in application systems, especially systems with large amounts of data, data and indexes must be separated and stored in different partitions to improve 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 An Oracle instance (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 processes: Server Processes and background processes ).

Server process 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.

The background process 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 l redo log buffer l sharing pool l large sharing pool (optional)

Data Dictionary Buffer l other data buffer

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

§ 2. 3.1 initialization parameter file (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. in the ora file, but after oracle8, most parameters are no longer in the file. to modify these parameters, you can only run SQL>; and set session or set system. 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.

To initialize the parameter file initsid. ora, see the appendix.

§ 2. 3.2 data files are 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 by their data types, they can be divided into: l 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 The control file is an internal binary file accessed by the Oracle database instance at startup. The path they are stored is 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; L after the general database system is installed, more than two control files are automatically created;

To ensure security, it is generally recommended to create multiple control files. l The control files can be found using the following command:

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

§ 2. 3.4 redo log files is 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 In addition to the preceding three types of files, other supported files include: l sqlnet. ora, l tnsnames. ora, and l listener. ora.

2. 4. redo log and redo log work 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 All Oracle operations made by redo logs are recorded in log files. The Oracle system does not immediately Save the disk (write data files) for every modification to a record ), instead, it only makes changes, and 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.

§ 5.1 data blocks, intervals, and segments § 2. Data blocks)

Oracle data blocks are also called Oracle blocks; l ORACLE System formats data files into several Oracle blocks when creating tablespaces;

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 use 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 L interval is also called an extension, because when it has used up the allocated interval, when a new record is inserted, it is necessary to allocate a new interval (that is, to expand some blocks); the size of the L interval is determined by next; l once the interval is assigned to an object (table, index, and cluster), the interval cannot be distributed to other objects. l The number of intervals used by an object can be found using the following command:

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 the database object; Segment l can have table segment, index segment, rollback segment, temporary segment, and high-speed cache segment; segment L uses 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 allocation = initial interval = 500 k; 2) 256 k is allocated after the first 500 k is used up; this 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 processes generally have at least six background processes active after the database is started (the instance is started successfully). These processes are divided by database needs. 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. The initsid. ora parameter job_queue_process sets the number of snapshot processes. The parameter job_queue_interval determines the number of seconds before the snapshot process is awakened to process suspended jobs or transactions.

11) parallel query process (pnnn)

The Oracle server can start or stop the Query Process Based on the settings of the database activity parallel query option. 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.

§ 2. 7 Oracle memory structure as mentioned earlier, Oracle database instances are composed of a group of background processes and memory structures. The memory structure includes:

System Global Area)

Program Global Area)

2. 7.1 system global zone the Oracle system is used to store a storage area of system information. Both user processes and Oracle background processes can use SGA. SGA contains many components (different parts ).

Data buffer cache)

Data blocks recently used by the Oracle system are stored in the data high-speed buffer zone (that is, the user's high-speed buffer zone). When writing data to the database, it reads and writes data blocks, when the data high-speed buffer is filled up, the system automatically removes some infrequently accessed data. If the data you want to query is not in the Data High-speed buffer, Oracle automatically reads data from the disk. The data high-speed buffer zone includes three types:

1) Dirty partition (dirty buffers): contains data blocks that have been changed and need to be written back to the data file.

2) Free Zone (free buffers): it does not contain any data and can be written again. Oracle can read data blocks from data files.

3) Retention zone (pinned buffers): This zone contains a zone that is being processed or explicitly reserved for future use.

After Oracle8i, the buffer pool is divided into three zones (when multiple buffer pool features are used): 1) Keep Buffer Pool (keep buffer pool): Data blocks are retained in the memory, they will not be squeezed out from the memory; 2) the recycle buffer pool will remove data from the memory that is never needed; 3) the default buffer pool contains allocated blocks.

Redo log buffer (RaDO log buffer)

Any transaction must be put in the redo log buffer before it is recorded to the redo log (online redo log is required for recovery. The log is then written to the process (lgwr) to regularly write the content in the buffer to the redo log.

Shared Pool)

A shared pool is a reserved area of SGA and is used to store stored procedures and packages such as SQL and PL/SQL, data dictionaries, locks, character set information, and security attributes. The shared pool includes: 1) Library cache; 2) dictionary cache ).

Library Cache)

This area includes: 1) shared SQL area (shared pool area); 2) Private SQL area (Private SQL area); 3) PL/SQL stored procedures and packages (PL/SQL procedure and Package); 4) control structure ).

That is to say, this area contains SQL statements that have been analyzed by syntax and are ready to be executed at any time.

Dictionary high-speed buffer (data dictionary cache)

It is used to store all the information required by the Oracle System Management. This information is the username used to log on to Oracle. These users have database objects and their locations.

Svrmgr>; show SGA total system global area 35544188 bytes fixed size 22208 bytes variable size 3376332 bytes database buffer 122880 bytes redo buffer 32768 bytes

The last four items are added to the previous one.

You can also use select * from V $ SGA; to query the SGA of the current instance.

Large pool)

A large pool in SGA is an optional buffer. It can be configured with management right as needed. It provides a large zone for backup and recovery operations such as databases.

2) program global zone (PGA)

PGA is the memory area used by Oracle. It can only be stored and controlled by a single process at a time, used to store session variables and internal arrays.

SGA Oracle memory structure (SGA diagram)

§ 2. 7.2 The program global area PGA is the memory area used by Oracle. In this area, data can only be stored and controlled by a single process at a time, used to store session variables and internal arrays.

§ 2. 8 an oracle instance is composed of SGA, background processes, and data files. Each database has its own SGA and an independent Oracle process set. :

Oracle instance and background process (instance diagram)

In the case of distribution, Oracle uses a SID (system identifer) to identify the names of each Oracle server so that the names of different database systems are not confused, in UNIX environments, the oracle_sid variable is used to distinguish between them.

§ 2. 9 multi-threaded server (MTS)

Multi-threaded ORACLE Server (multithreader server) allows multiple connections to the database to fully share memory and resources, which allows a small amount of memory to support more users.

All processes connected to the Oracle database occupy a certain amount of memory. Therefore, if too many processes connect to Oracle, a performance bottleneck occurs.

Oracle8 allows more than 10 thousand users to connect to Oracle at the same time, but not all users use MTS. currently, some 4gl tools do not support MTS, such as VB and Pb, and VC/C ++ does not support MTS. the Oracle multi-thread server has its own connection pool (that is, the Shared Server process ). Because users share open connections, this is much faster than the original dedicated method (eliminating bottlenecks ).

Multithreading is very suitable for some specialized application systems, such as the order registration system, where the customer submits the order, and the data of the recorded employee's order; another recorder is negotiating with the customer, not all data is being input (dedicated server processes are idle ). These terminals are forced to connect to the system and occupy the resources of other users.

Multi-threaded servers eliminate these shortcomings. A multi-threaded server only maintains one connection pool. When a terminal needs to talk to the system, it can be allocated a connection. You can remove it if you do not need it. In this way, the system resources are shared by multiple users.

Change the relevant parameters in the parameter file to make the system a multi-threaded server configuration (effective after restart ). In addition, the database instance must provide the same number of users as the number of users placed.

§ 2. 10 Oracle transaction processing process:

1. Issue an SQL statement to query the remaining amount, such as: Select account_balance from banktable where account_number = '000000' and account_type = 'savings ';

The SQL statement uses SGA to obtain the server process;

The server process checks whether the statement exists in the Shared Pool. If no statement exists, it is placed in the Shared Pool and ready to run;

Execute the SQL statement to read the high-speed data buffer of SGA from the data file of the remaining data blocks;

Display results. For example, the remaining amount is $325.

2. Withdrawal $25: The SQL statement is: Update bank_table set account_balanct = 300 where account_number = '000000' and account_type = 'savings ';

The customer process sends the SQL statement to the server process through SGA. The server process finds whether the statement exists and executes it (; l analyzes the SQL statement and saves it to the sharing pool; l executes the SQL statement;

Is the data to be processed in the Data High-speed buffer? Yes to 7;

Reads data blocks from data files to high-speed data buffer;

Record the original value ($325) in the rollback segment );

Generate a copy of the transaction in the redo log;

Change the balance in the Data High-speed buffer to $300;

Bank teller machines send work completion signals through SGA (submit ):

Record completed transactions in the redo log;

Clear the Undo information in the rollback segment );

The customer has obtained the money.

§ 2. 11 The sys and system modes of the Oracle System sys and system are the two accounts installed by default for each Oracle database system. Sys is the owner of all internal database tables, structures, process packages, and so on. It also has V $ and data dictionary views, and creates all encapsulated database roles (dBA, connect, resource ). Sys is the only user who can access a specific internal data dictionary. System is also created when oracle is installed for DBA task management.

The default password after sys is installed is change_on_install, and the default password for system is manager. To ensure security, you can install the system after it is installed. Use the alter user SYS identified by PASSWORD command to modify the passwords of these two privileged accounts.

§ 2. 12 all Oracle databases have at least one file used to record system information, errors, and major events. This file is called alertsid. Log (the Sid here is the System ID of Oracle). The storage location is given by the background_dump_dest parameter of the initsid. ora file.

Both the background process and the user process can create their own tracking files. The background process tracking file location is given by the background_dump_dest parameter, and the user tracking file location is given by the user_dump_dest parameter. As shown in the parameter file initora8.ora:

# Define directories to store trace and alert files background_dump_dest = D:/Oracle/admin/ora8/bdump user_dump_dest = D: the/Oracle/admin/ora8/udump background trace file is named sidproc. TRC

2. 13 The Data Dictionary of the Oracle System is the knowledge base for all objects stored in the database. The Oracle database system uses the data dictionary to obtain object information and security information, users and DBAs use it to view database information. The data dictionary stores information about data objects and segments. Such as tables, views, indexes, packages, processes, and information about users, permissions, roles, and audits. A data dictionary is a read-only object and cannot be modified by anyone.

§ 2. 14 other data objects apart from the preceding data objects, Oracle also includes views, sequences, synonyms, triggers, database chains and packages, processes and functions. The following is a description.

2. 14.1 view is the SQL statement stored in the database query. It is mainly for two reasons: security reasons. view can hide some data, such as the Social Insurance Fund table, you can use the view to display only the name and address, but not the social insurance number and wage number. Another reason is that complex queries are easy to understand and use.

§ 2. 14.2 sequence is a database object used to generate unique digital data. The sequence is created with an initial value, increment value, and maximum value, which can be up to 38 integers.

2. 14.3 trigger is a special stored procedure. Its execution is not called by a program or started manually, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the dba_triggers and user_triggers data dictionary.

§ 2. 14.4 synonym is a database pointer to other database tables. There are two types of synonyms: private and public ). Private synonyms are created in the specified mode and accessed only in the mode used by the creator. Public synonyms are accessed in the mode specified by public, and can be accessed by all database modes (users.

§ 2. 14.5 database link is the storage definition for connecting to a remote database. It is used to query remote users in the distributed database environment. Because they are stored in the dba_db_links data dictionary, they can be considered as a database object type.