Shulou(Shulou.com)05/31 Report--

This article focuses on "introduction to the architecture and physical and logical storage structure of Oracle". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the introduction of Oracle architecture and physical and logical storage structure".

1.Oracle architecture

Speaking of Oracle,Oracle, what is it? People who have come into contact with Mysql,SQL Server may subconsciously think that it is not just a relational database? In fact, the concept is too general for us to clearly understand the structure of Oracle. Strictly speaking, Oracle consists of two parts:

An instance is a set of processes and memory structures initialized when the database is started, and the database refers to some files in which the user stores data. The instance exists with the startup of the database, and it exists to efficiently access and process the files in the database, and to ensure zero data loss in the event of failure. That's why there are terms like starting and shutting down instances, loading and unloading databases.

If the above gives you a superficial understanding of Oracle, take a look at the following picture:

As you can see, with the blank in the middle as the line, the tender part above is the Oracle instance part, and the lower part is the database part, which we will analyze one by one.

2.Oracle physical storage structure

As the name implies, the physical storage structure is something that can be seen and actually exists, and the vernacular is a group of files that make up the physical storage structure of Oracle. As shown in the picture

The yellow part represents the core components of Oracle, which are data files, control files, and redo log files. In addition, the physical storage structure includes some other files, such as parameter files, password files, archive log files, and so on. These files are mainly introduced below.

Data file (Data files)

Data files refer to the files that store data in the database, these "xxxx.dbf" store system data, data dictionary data, index data and user stored data, so this part is also the core part of the database. The size of the data file is flexible, and it can be automatically expanded by setting it, avoiding the situation that the amount of data is too large but the data file space is limited; the data file belongs to a database and a table space, but a table space can have multiple data files. If the data read by the user is not in the buffer, the corresponding data is put into the buffer from the data file and then read. This part will be analyzed in detail later.

Control file (Control files)

The control file is a very small binary file. The "physical structure information" of the database stored in these "xxx.CTL" includes the name of the database, the name and location of the data file and online log file, and the timestamp when the database was created. When the database is started, it needs to access the control file and read the information of the data file and log file; with the operation of Oracle, the database will constantly update the control file; correspondingly, once the control file is damaged, the database will fail. Therefore, in order to better protect the database, we can mirror the control file, especially when the database structure changes, we should back it up to maintain the consistency of the control file.

Redo log file (Redo Log files)

Redo log files are used to record all modification information of the database, nicknamed log files. These ".log" files can not only ensure the security of the database, but also achieve database backup and recovery. In order to prevent accidental data loss, oracle also allows you to mirror log files. A log and its mirrored files form a log filegroup, but the number of mirrored files cannot exceed 5. As the saying goes, don't put all eggs in one basket, and the same group of files had better be saved on different disks to prevent physical damage from causing unnecessary trouble.

Parameter file (Parameter file)

The parameter file records the basic parameter information of the Oracle database, including the database name, the path where the control file is located, and so on. Parameter files include text parameter files (PFILE) and server parameter files (SPFILE), the former being init.ora and the latter being binary files of spfile.ora or spfile.ora. The parameter file is read when the database is started, and then SGA is allocated and a series of background processes are started according to the parameters in the parameter file.

Archive log files (Archived Log files)

Archive log files are used to save and copy full log files. The purpose is to keep the log for a long time to facilitate recovery.

3.Oracle logical storage structure

In the oracle database, the operation of the database will involve the logical storage structure, which analyzes the composition of the database from a logical point of view, describes the organization and management of the internal data of the database, and has nothing to do with the operating system.

As shown in the figure, it is obvious that the logical structure of the database mainly includes tablespaces, segments, extents and data blocks, all structures are one-to-many relationships, a database can have multiple tablespaces, a tablespace has multiple segments, and so on.

Tablespace

Tablespaces are the largest logical storage structure of Oracle, corresponding to physical data files, but a tablespace can have multiple data files, so I won't repeat them here.

Segment

A segment is a set of extents, which is an independent logical storage structure, which is used to store all data of objects with independent storage structure. Segment is generally the smallest storage unit handled by the end user of the database. When the data area of the segment is full, Oracle allocates another data area to it, and the data area of the segment may be discontiguous on disk. According to the characteristics stored in segments, they can be divided into 5 types:

Data segment: used to store all the data in the table; in Oracle, whenever a user creates a table, the system automatically allocates a data segment with the same name as the table in the default tablespace to facilitate the storage of all the data in the table.

Index segment: used to store all the data indexed in the table; in Oracle, whenever a user creates an index, the system automatically creates an index segment in the default tablespace with the same name as the index, making it easy to store all the data of the index.

Temporary period: used to store temporary data generated when tables are sorted or summarized; whenever the user:

When temporary tablespaces are arranged or summarized by Order by, the system automatically creates a temporary period in the user's temporary tablespaces and automatically eliminates them at the end of the operation.

LOB segment: LOB is used to store large data objects in tables, such as CLOB and BLOB

Fallback segment: used to store the location and value before the user data was modified; when you want to fallback the user's data, use the fallback segment.

Zone

An extent is the smallest unit allocated to Oracle storage and is made up of one or more data blocks. One or more zones form a segment, that is to say, the size of the segment is determined by the number of zones. When a segment runs out of space, Oracle automatically assigns a new zone to the field.

Block

Block is the most basic unit used to manage storage space, and it is also the smallest logical storage unit. The size of the block is determined by the initialization parameter db_block_size, and different versions of Oracle vary in size, but once the database is created, its size cannot be changed. Although each data block can store different types of data, each data block has the same structure, as shown in the figure:

1. Block

Contains the general attribute information of the data block, such as the physical address of the data block, the type of segment to which it belongs, etc.

two。 Table catalogue

If the data stored in the data block is the data of a table, the information of the table is stored here.

3. Line directory

Used to store valid row information in a data block

4. Spare space

Storage space that has not yet been used by exponential data blocks

5. Row space

The data of the table or index is stored in the row space, so the row space is the storage space already used in the data block.

So it can be seen from the description that the capacity of a data block is actually the sum of the capacity of row space and free space.

4.Oracle instance memory structure and corresponding background process

As shown in the figure, the Oracle instance structure is divided into left and right parts, one is called SGA, called the system global zone, and the other is called PGA, called the program global zone. SGA is a set of shared memory structure allocated by the system, which is used to manage and operate database data. PGA is the exclusive memory area of user session, and each service process and background process has a PGA. In fact, as can be seen from the above picture, the instance and the database are originally two things that cannot be linked together, but there is a matchmaker in the middle: the background process of the instance, that is, the oval part of the example in the picture connects the two together. The following is the first analysis and description of SGA, in which the corresponding process will also be analyzed:

As shown in the figure:

Database buffer cache (database buffer cache)

-database buffer cache, also known as buffer cache, is used to store mirrors of blocks read from a data file. Oracle will perform sql work in this area, so when updating data, the user session will scan the buffer first instead of operating directly on the disk, which will reduce the disk IO and greatly improve the performance of the system.

For instance

Select user_id,date from customer where user--id > 10

When oracle executes this SQL statement, it is first sent to the server by the corresponding user process. After listening and receiving the request, a corresponding server process is created, and then the process will scan the buffer for the existence of data blocks with user_id > 10. If so, the relevant information will be sent to PGA for processing and finally displayed to the user. If it does not hit, the process copies the corresponding data block on the disk to the buffer and performs the rest of the operation.

But some people will think, if you keep looking for blocks that do not exist in the cache, will not all the blocks on the disk be copied to the buffer? isn't that faster? In fact, when you copy the data block into the buffer, it virtually increases the load of the buffer, and the purpose of the buffer is to improve the performance of the system, so you will use the LRU algorithm to manage the memory space of the buffer, so that it has the highest performance in a reasonable range.

In the case of executing some DML statements, such as update a XXX, the buffer is also operated as described above. If the buffer is scanned first and the cache is hit, it will be updated directly; if the cache fails, the corresponding data block of the disk will be copied to the buffer for update. At this time, the problem arises. After updating the buffer data, isn't the disk data unchanged?

At this point, it's the key person's turn: DBWn.

DBWn

DBWn, also known as the database write process, is responsible for writing updated buffer data to disk. In the data file in the database. However, this process is recognized as a lazy process, and it is not automatically written to disk whenever the buffer writes data. When the following occurs, the lazy process will perform the write task:

There is no cache space to write

The dirty cache reaches the limit (it will take too long to search).

Three seconds at the latest: a write will be performed in three seconds at the latest

Encounters a checkpoint: encounters this checkpoint and executes the writer.

When hot backup of tablespace

Tablespace offline, read-only statu

When performing a Drop operation

From the above timing, it is more clear that DBWn is not executed anytime, anywhere, and has nothing to do with commit operations. In fact, this lazy process can reflect the significance of the existence of the buffer and reduce the pressure of disk IO on the system, so "lazy" still has the advantage of "lazy".

What if when DBWn is not executed and a large amount of dirty data is still in the buffer and not written to disk, suddenly there is a disaster, thunder and power is cut off, and the data is lost? At this time, it is necessary to lead to the redo log and its corresponding background process to solve this problem.

Log buffer (Redo log buffer)

When we perform some DML operations, the resulting change vector will be written to the redo log file. Once the above-mentioned situation occurs, such as thunder or power outage, the system will suddenly shut down, and the dirty data in database buffer cache will not wait until DBWn writes it to disk. When the system restarts, there will be an instance recovery process, and the change vectors in the redo log will play a role in keeping the database consistent with the data in the moment before downtime; some common backup and recovery operations are the same: extract backup sets-> apply the change records in the redo log files.

Log files are used to record changes to the database, so in order to reduce disk IO and user waiting time, the modification operation information of the database should be written to the log buffer first. When the log buffer reaches a certain limit, it will be written to disk by the log writing process LGWR. This log buffer has less impact on database performance than database buffer cache.

LGWR

LGWR is also called log writer. As the name implies, the log writer writes dirty data from the log buffer to disk, and LGWR is much more "diligent" than the "lazy" process of DBWn.

Commit write

Log buffer occupancy rate 1 beat 3

Before DBWn is written to a dirty buffer

Both database buffer caches and log buffers exist to improve performance and avoid frequent IO. The log buffer is much smaller than the database buffer cache and cannot be managed automatically. the modification of the log buffer needs to restart the instance, and the database buffer cache can be managed automatically. The DBWn process acting on the database buffer cache performs as few writes as possible in order to avoid frequent disk IO causing system performance degradation, and DBWn writes have nothing to do with commit operations.

The LGWR process, which acts on the log buffer, writes very aggressively, typically dumping redo log records to disk almost in real time. LGWR is one of the biggest bottlenecks in Oracle architecture. The speed of DML cannot exceed the speed that LGWR writes the change vector to disk.

Shared pool

Shared pools are the most complex SGA structure, with many substructures. Let's take a look at some common shared pool components:

Library cache (library cache): the purpose of Oracle importing library cache is to share SQL and PL/SQL code. When the server process executes SQL and PL/SQL, it first goes to the library cache to find out whether there is the same SQL. If so, it no longer carries out subsequent compilation processing, but directly uses the compiled SQL and execution plan. Oracle determines whether the two SQL statements are the same by comparing the body of the two SQL statements, so if you want to share the SQL statement, you must use binding variables. Such as:

Select * from emp where sal > 100 and select * from emp where sal > 101are different, and when binding variables are used, Oracle thinks select * from emp where sal > & v_sal is the same even if the value of select is different. Oracle uses LRU queues and algorithms to manage the library cache. The recently used SQL will be placed at the head of the queue, and the SQL that has not been used for a long time will be placed at the end of the queue. When the library cache needs memory space and there is no free memory space, the SQL in the memory at the end of the queue will be cleared and put into the latest SQL, and the head of the queue will point to the secondary memory. Oracle does not provide a way to directly modify the size of the library cache, but can only modify the size of the library cache indirectly by changing the size of the shared pool.

Data dictionary cache (dictionary cache): when Oracle executes SQL, it stores the definition and permission information of related data files, tables, indexes, columns, users, and other data objects in the data dictionary cache. After that, if the same related data is needed, Oracle will extract it from the data dictionary cache. Oracle does not provide a way to directly modify the size of the data dictionary cache, but can only modify the size of the data dictionary cache indirectly by changing the size of the shared pool.

What is the way to modify the shared pool? Alter system set shared_pool_size= xxx m; the size of the shared pool is limited by the size of the SGA_MAX_SIZE parameter.

Daichi

A large pool is an optional memory area that provides a large buffer for database backup and recovery operations. The actual needs of large pools are generally as follows:

Backup and recovery of database

SQL statements with a large number of sort operations

Parallelized database operation

JAVA Pool

The JAVA pool supports the operation of JAVA in the database and stores the parsing table of JAVA code and JAVA statements; the size of the JAVA pool is generally not less than 20m, so it is easy to install the JAVA virtual machine.

Flow pool

The process of extracting the change record from the redo log and the process of applying the change record will use the flow pool (for example, if the instance is shut down abnormally, for example, when the instance is shut down due to a power outage, Oracle will automatically perform the instance recovery process during restart. In this process, you need to extract the redo log and apply the redo log)

The common memory structures of Oracle are listed above. It should be noted that except for the memory areas listed above, except that the log buffer is fixed and cannot be dynamically adjusted or automatically managed, other memory areas can be dynamically adjusted or managed automatically.

5.Oracle program global area

PGA, which contains individual user or server data and control information, is a private memory area allocated by oracle to a process. Whenever a user process connects to the database and creates a session, Oracle automatically allocates the corresponding space.

We have introduced DBWn and LGWR above, and we will introduce other process information below:

SMON

SMON (System Monitor), also known as the system monitoring process, performs recovery operations when the database instance fails or the system crashes. It also periodically merges free space in the tablespace managed by the dictionary, and he can clean up all temporary periods of the tablespace during a system restart. Installing and opening the database is also done by this process. SOMN is awakened regularly and can be called when other processes need it.

PMON

PMON (process monitor) is also called process monitor. The recovery operation performed when the user process fails is responsible for cleaning up the memory area and releasing the resources used by the process; in addition, PMON will periodically check the status of the scheduling process and the server process and restart the dead process. POMN is awakened regularly and can be called when other processes need it.

CKPT

CKPT (checkpoint process) checkpoint process. The process is responsible for initiating checkpoint signals to get DBWn to work, and updating control files and data file headers.

ARCn

The ARCn (Archive) archiving process is mainly responsible for copying log files into archived log files to prevent recycling of log filegroups from overwriting existing log files. Only when the database is in ARCHIVELOG mode and automatic archiving is enabled, the system will start the ARCn process; ARCn includes archiving and non-archiving. The ARCn process exists only when it is archived, and when it is archived, no process can access the log files being manipulated.

Except for ARCn, all of the above processes are necessary for the system. It plays an important role in maintaining the connection between physics and memory.

6. Data dictionary

Data dictionary is an important part of Oracle database, which is automatically created and updated by Oracle to store a set of tables that store database instance information and organize its information in the form of tables and views. The tables in the data dictionary cannot be accessed directly, but the views in the data dictionary can be accessed. The data dictionary is owned by the SYS user and is stored in the default table space SYSTEM table space. The data dictionary mainly stores the following information:

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

Storage space allocation information, how much space is allocated, how much space is currently used, and so on.

Security information

Performance and statistics of instance runtime

Basic information of other databases themselves

There are static and dynamic data dictionaries in Oracle; static data dictionaries do not change when users access data dictionaries. Zhelei dynamic data dictionaries depend on the performance of database operation and reflect some inherent information of database operation, so it is often not immutable when accessing this kind of data dictionary.

Views in the static data dictionary are divided into three categories, each of which is made up of three prefixes: user_*, all_*, and dba_*.

User view

The name of the user view is prefixed with user_ and is used to record information about the user object. The user view can be thought of as the all view itself, and each user can query the user view. You can use DESC to understand the view structure.

All view

The name of the all view is prefixed with all_ and is used to record the information of the user object and all the object information that can be accessed, including the user's own scheme object, as well as the scheme object that is authorized to access other users. The all view is an extension of the user view.

Dba view

The dba view is prefixed with dba_ and is used to record information about all objects in the database instance. In general, only the dba role can access the dba view, and if ordinary users are granted Select any dictionary system permissions, they can also access this.

Dynamic performance views are views that start with V$, which is prefixed with V$ and are used to record performance statistics dynamic information related to database activity. For example, the V$session view can view the details of the current session.

7. Database startup and shutdown process

Oracle startup is divided into three processes: nomount, mount and open. The specific work of these three processes is as follows:

-nomount status: start the instance.

First, the parameter files are read from the dbs directory under the environment variables in the following order, but considering the importance of the parameter files, they are read through the following process:

First, read the spfile+ instance name .ora file; if the file is not found, read spfile.ora;; if the file is not found, read the init+ instance name .ora file; if none of the above files are found, you can also specify the pfile parameter file to start instead of the default startup method.

After reading the parameter file, you start to allocate SGA, start the background process, start the early warning log file (record the events in the life cycle of the instance, such as system internal errors, data block corruption, system parameter modification, etc.) and trace files (record SQL operation and time consumption, etc.). Note that the database must be named after the initial parameter file or the DB_NAME parameter in the startup command.

-mount status: associate the instance with the database, read the control file and get the data file and redo log file name status.

After starting the instance, you need to associate the instance with the database. When reading the parameter file, find and open the control file location, read the data file and redo the log file name and status. However, the existence of the data file and the redo log file is not checked here. In the state of mount, the system finishes loading the database.

Open status: open database

The process of opening the database inherits the mount state, reads from the control file to the location of the data file and redo log file, and opens it here; if any of these files are missing, Oracle will return an error. In the final stage, the Oracle database verifies whether the data file and redo log file can be opened and verifies the consistency of the database. If it is not consistent, the SMON background process will start the instance recovery.

Shutdown has four parameters: normal, transactional, immediate, and abort. Without parameters, the default is normal.

Shutdown normal: constantly connecting users, preventing any users from establishing new connections, including administrators. Users who are already connected can continue their current work, such as submitting new update transactions, until the user disconnects himself. This requires a long time to wait, you can find out the current connection users, and then inform them to disconnect themselves. All users are disconnected before the database is closed, that is, the database is closed, the database is uninstalled, and the routine is terminated. A database that is closed in this case will not have a problem after restarting. No instance recovery is required at startup.

Shutdown transactional: prevent any user from establishing a new connection, wait for all currently connected users' uncommitted active transactions to be completed, and then disconnect the user immediately. If all users are disconnected, they will immediately close the database, uninstall the database, terminate the process, and so on. In this way, users may be doing accounts and making complex reports! If a database operation cannot be completed, it will be disconnected after just doing a database operation, which will have a certain impact on the user, and there is no need for instance recovery when starting.

Shutdown immediate: prevents any user from making a new connection while restricting the current connection user from starting a new transaction. If connected users have outstanding transactions, the database system does not wait for them to complete, but directly rolls back the currently uncommitted transactions. The database system no longer waits for the user to disconnect initiatively. When the unsubmitted transaction is successful, the system will directly shut down and uninstall the database, and terminate the database process. No instance recovery is required when starting.

Shutdown abort: when the database fails, it is possible that none of the above three methods can shut down the database properly, so use this method. Force the end of the currently executing SQL statement, and any uncommitted transactions will not be rolled back! This method basically does not cause damage to the control file or parameter file, which is better than a forced shutdown (when the database cannot be shut down normally), and the instance recovery is performed automatically at startup.

Startup has seven parameters: nomount, mount, open, pfile, force, restrict and recover

Startup nomount: through the parameter file, assign sga, start the database background process, do not open control files and data files, can not access the database. Usually you can start here to do create database, create or recreate control file or mount standby database and other actions, and it is also possible to modify parameter.

Startup mount: only administrative operations are performed for dba, and database users are not allowed to access it. Only the control file of the current instance is opened, and the data file is not opened. In this mode, you can do the following: rename the data file, add cancel or rename the redo log file, set the archive mode, set flashback, perform a complete database recovery operation, and so on.

The default parameter of startup open:startup is open, which opens the database and allows access to the database. All the files described in the current instance control file have been opened.

Startup pfile=FILENAME: start the database with FILENAME as the initialization file, not the default initialization file.

Startup force: aborts the current database and starts to restart the database normally.

Startup restrict: only users with restricted session privileges are allowed to access the database. In this mode, login users can do the following operations: export or import database data, use SQL*Loader to perform data loading operations, temporarily prevent general users from using data, and use ALTER SYSTEM statements to disable RESTRICTED SESSION feature ALTER SYSTEM DISABLE RESTRICTED SESSION after logging in restricted session during a migration process and upgrade operation. If you open a database in unrestricted mode and later find that you need to restrict access, you can use an ALTER SYSTEM statement with an ENABLE RESTRICTED SESSION clause to do so.

Startup recover: the database starts and media recovery begins.

Appendix: commonly used data dictionaries

Static view

Data dictionary name description dba_tablespaces information about tablespaces dba_ts_quotas all user tablespace limits dba_free_space free partitions in all tablespaces dba_segments description of storage spaces for all segments in the database information for all partitions in the dba_extents database description of all data tables in the dba_tables database, Information for all views in the view and cluster columns dba_views database dba_synonyms about synonyms dba_sequences all user sequence information dba_constraints all user table constraint information dba_indexs data table description of all indexes dba_ind_columns all tables and columns of compressed indexes on the cluster dba_triggers all user trigger information dba_source all user stored procedure information dba_data_files query Information about database files dba_tab_grants/privs query information about object authorization dba_objects database all objects dba_users information about all users in the database

Dynamic view

Data dictionary name description v$database description related information about the database data file information used by the v$datafile database v$log extracts information about the redo log group from the control file v$logfile information about the instance reset log group filename and its location v$archived_log record basic information of the archived log file v$archived_dest record archived log file path information v$controlfile description control file related information v$instance record Basic information of the instance v$system_parameter displays the currently valid parameter information of the instance v$sga displays the SGA area size of the instance v$sgastat statistics SGA usage information v$parameter records the values of all items in the initialization parameter file v$lock by accessing the database session All the information that sets the object lock v$session has a session information v$sql records SQL statement details v$sqltext records SQL statement information v$bgprocess displays background process information v$process current process information so far, I believe you have a deeper understanding of the "Oracle architecture and physical and logical storage structure introduction", might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.