A complete Oracle database system usually consists of two parts: instance + database
Database : a collection of a series of physical files (data files, control files, online logs, parameter files, etc.)
Example : a set of Oracle background processes/threads and shared memory allocated by the server
The relationship between the database and the instance:
- The database can be loaded and opened by multiple instances
- The instance can load and open a database at any time
- An instance can only load and open at most one database during its lifetime
The logical storage units from small to large are: data block, extent, segment, table space
Data block : The smallest unit used by Oracle to manage storage space , and it is also the smallest unit for performing database input and output
- The composition of the data block : block header, table directory, row directory, free space, row space
Panel : The smallest unit of Oracle storage allocation , which is a panel composed of a series of data blocks that are physically and continuously stored, and one or more panels form a segment
Segment : an independent logical storage structure , different types of database objects have different types of segments, divided into: data segment, index segment, temporary segment, rollback segment and LOB segment
Table space : The largest logical storage structure that can be used in Oracle . A table space is composed of one or more data files. A data file can only belong to one table space. This is the unification of logic and physical; storage space is physically represented It is a data file, which is logically represented as a tablespace; when creating a database, Oracle will automatically create some default tablespaces. In addition to the ordinary tablespaces used to store user data, there are SYSTEM tablespaces, undo tablespaces, Temporary table space
- (1) SYSTEM table space : data dictionary, internal system table base table, source code and analysis code of PL/SQL program, definition of database objects
- (2) Revocation table space : specifically used to store revocation information in the automatic revocation management mode, that is, rollback information
- (3) Temporary table space : During the operation of the instance, Oracle uses some temporary space to save the temporary data generated during the execution of the SQL statement
|system||System table space, used to store system data dictionary, system management information and user data tables, etc.|
|sysaux||Auxiliary system table space. Used to reduce the load of the system table space and improve the efficiency of system operations. The table space is automatically maintained by the Oracle system and is generally not used to store user data|
|temp||Temporary table space. Used to store temporary data, such as temporary data generated during sorting. Under normal circumstances, all users in the database use temp as the default temporary table space|
|undotbsl||Undo the table space. It is used to store the revocation information in the automatic revocation management mode. In the undo table space, except for the rollback segment, no other types of segments can be created. Therefore, users cannot create any database objects in the undo tablespace|
|users||User table space. Used to store permanent user objects and private information|
Physical storage structure
- The physical storage structure does not exist independently, it has an inseparable connection with the logical storage structure of the database
The database physical storage structure mainly includes 3 types of physical files:
- data file
- Control file
- Redo file
- And some parameter files
View data file commands:
data files are roughly divided into:
- System data files (STSTEM01.DBF, STSTEMAUX01.DBF)
- Undo segment file (UNDOTBS01.DBF)
- User data files (USER01.DBF, EXAMPLE01.DBF)
- The database control file is a small binary file that contains important information about the physical structure of the database
- By reading the control file when loading the database, Oracle can find the operating system files it needs (data files, redo log files, etc.)
- During the operation of the database, Oracle will continuously update the content of the control file, so the control file must remain available during the entire database open period. If the control file is unavailable for some reason, the database will crash
- Each database must have at least one control file, a database can have multiple control files at the same time, but a control file can only belong to one database
- The main information in the control file includes: the name of the database, checkpoint information, the timestamp of database creation, all data files, redo log files, archive log file information and backup information, etc.
Redo log file
Redo log file is a file that records all modification information in the database
Redo log files record two types of data: 1. Data before modification 2. Data after modification
- When certain conditions are met, the redo records generated by the modification operation are written to the redo log file, and then the modification results in the memory are written into the data file in batches, and finally the transaction is submitted
- Redo log files are composed of redo records, and redo records are composed of modification vectors. When a user executes an update statement to modify a record in a table, a record is generated. This record uses multiple vectors to record the information in each data block modified by this statement. Once the database crashes, the Oracle server uses the records in the redo log file to restore the database.
- Oracle uses multiple groups (group) redo logs. The redo log writing process (LGWR) can only write to the group redo log group at any time. The redo log group being written by the LGWR background process is called the current redo log group. LGWR copies the exact same information from the redo log buffer to each redo log file in the group. It writes the redo log group in a circular manner. When LGWR writes a set of redo logs, it starts to write a set of redo logs. This is called log switching. When the last group is filled, LGWR starts to write the first group of redo logs.
- Archive log files
- Parameter file
- Password file
- Alert log file
- Background or user tracking files
The memory structure of the instance: system global area SGA, program global area PGA
System global area SGA
When the Oracle database is started, the system will first plan a fixed area in the memory to store the data required by the user and the system information necessary for Oracle runtime
Program global area PGA
- The Program Global Area (PGA) is to save the data and control of a specific service process
- The memory structure of the information, this memory structure is not shared, only the service process itself can access its own PGA area. Each service process has
its own PGA area containing the following structure:
- Private SQL workspace
- Session memory area
- A process is an independent schedulable activity in the operating system, used to complete a specified task.
- The difference between a process and a program is that the former is a dynamic concept, and the latter is a static entity. A program is just an ordered collection of instructions, while a process emphasizes the process of execution.
Processes can be created dynamically and die when the task is completed.
Oracle's process includes three categories:
- Service process
- Server process
- backstage process
When a service process
user executes an Oracle application or starts an Oracle tool (such as SQL Plus), a user process is created to perform corresponding user tasks. *
(1) Connection: A communication path between a user process and a database instance. This communication path is implemented through the inter-process communication mechanism or network connection in the operating system platform.
(2) Session: A designated connection from a user to the database. For example, when a user starts SQL*Plus and enters the correct user name and password to connect to a database library, a session is created for the user. The session always exists during the user's connection to the instance, until the user disconnects or terminates the application.
Sessions are established through connections; the same user can create multiple sessions to the Oracle database by establishing multiple connections.
- The server process is the process that completes the work on behalf of the client session.
- Responsible for scheduling requests and responses between the user process and the Oracle instance.
- The SQL statement sent by the application to the database is received and executed by these processes. After the user process submits the query, the server process is responsible for executing the query statement, that is, read the data from the disk into the cache, obtain the query results, and then return the results to the user process.
- Even if there are some errors in the response, the server process will send the error message back to the user process, so that the user process can perform appropriate processing. There can be two types of server processes in the Oracle database:
- Dedicated server process: Only one user process can provide services.
- Shared service process: can provide services for multiple user processes.
The background process
Oracle instance includes two parts: SGA and a set of background processes
At any one moment, the Oracle database can handle multiple concurrent user requests and perform complex data operations. At the same time, the database system must be maintained to always have good performance.
In order to complete these tasks, Oracle has a set of background processes necessary for the actual maintenance tasks required by the database to run the
- DBWR (data writing process)
- This process writes the data used by the buffer user into the data file. So that the user process can always get unused buffers
- LGWR (log writing process)
- To record the committed transaction processing of all databases in the online redo log file, it will perform the write operation in the following four situations
- Transaction commit
- Redo log cache has been filled 1/3
- The amount of data in the redo log cache reaches 1MB
- Every 3 seconds
- CKPT (checkpoint process)
- This process modifies the headers of all data files when the checkpoint occurs. Under normal circumstances, the checkpoint process is not started and its work is performed by the log writing process LGWR. However, if the checkpoint significantly reduces system performance, the CKPT process will run, separating the checkpointing work originally performed by the LGWR process and implemented by the CKPT process
- SMON (system monitoring process)
- In the event of an instance failure, SMON is responsible for restarting the system and performing crash recovery.
- SMON will clear the temporary segments that have been allocated but not yet released.
- SMON will also perform extent combination in table space management.
- PMON (Process Monitoring Process)
- Monitor server processes to ensure that damaged or failed processes can be destroyed and their resources released. Use the Oracle listener to register the database server on the host operating system.
- In addition, other commonly used processes:
- ARCn (Archive process)
- Write the transaction changes of the redo log to the archive log
- Save the redo log file before the transaction processing in the redo log file is overwritten
- RECO (recovery process)
- Dnnn (scheduling process)
The data dictionary is composed of a series of basic tables and views that are read-only for users. It stores basic information about the database itself and all objects stored in it.
The Oracle system is responsible for the management and maintenance of the data dictionary. No database user can modify the content in the data dictionary, but the user can view the content in the data dictionary.
The information in the data dictionary is organized in the form of tables and views, and the data dictionary and views are stored in the SYSTEM tablespace. The view is like a reference manual about the current Oracle database, which can be queried through the SELECT statement.
The data dictionary in Oracle is divided into static and dynamic:
- Static data dictionary: It will not change when the user accesses the data dictionary. It is mainly composed of tables and views. The tables in the data dictionary cannot be accessed directly, but the views in the data dictionary can be accessed
- Static data words: The views in the dictionary are divided into three categories, which are respectively composed of three prefixes: user_ , all_ , dba_ *
- Dynamic data dictionary: depends on the performance of the database operation, reflects some internal information of the database operation, and will be continuously updated
Static data dictionary 3 types of views :
- user_ *
- This view stores information about the objects owned by the current user. (I.e. all objects in the user mode)
- all_ *
- This view stores information about the objects that the current user can access. (Compared with user_ *, all_ * does not need to own the object, only needs to have the permission to access the object)
- dba_ *
- This view stores information about all objects in the database. (The premise is that the current user has access to these databases, generally speaking, must have administrator rights)
Data dictionary query example:
查看当前用户的用户名、 缺省表空间: SQL> select username,default_ tablespace from user_ _users; 查看用户下所有的表 SQL>select * from user_ tables; 查看当前用户的角色: SQL >select * from user_ role_ _privs; 查看当前用户的系统权限和表级权限: SQL>select * from user_ sys_ privs; sQL>select * from user_ _tab_ _privs; 查询当前用户有权访问的所有对象的信息: SQL> select object name, object type from all_ objects; 查询当前用户有权访问的所有表对象的信息: SQL> select owner, table_ name from all_ _tables; 查询数据库实例中有多少用户: SQL .>select username from dba_ _users; 显示指定用户所具有的系统权限: sQL>select * from dba_ sys_ privs where grantee='SCOTT;
Display privileged users
display SGA information of the entire storage area