"Go" Oracle Architecture

The interviewer asked me about Oracle's architecture a few days ago, and let me draw it on a blank sheet of paper. Looking back at that time answer is also good, most of the content is described, hehe, just saw an article on the internet to explain the Oracle architecture, feel good, turn around and save, may be used in the future.

=================================================================================

In this article you can learn about the following:

　　

1. ORACLE instance-includes memory structure and background process

2. ORACLE Database--a collection of physical operating system files

3. Understand the composition of the memory structure

4, understand the role of background process

5. Understand the physical files of the database

6. Explain various logical structures

One, Oracle instance

　　

1. ORACLE instance

　　

The System Global area (SGA) and Background Process are referred to as instances of the database.

　　

2. ORACLE Database

　　

A collection of physical files (data files, control files, online logs, parameter files, etc.)

　　

3. System global sharing area (SGA)

　　

The System Global area is a huge shared memory region that is seen as a large buffer pool for Oracle databases, where data can be shared among Oracle's processes. The size can be viewed by the following statement:

Sql> select * from V$SGA;

NAME VALUE

-------------------- ---------

Fixed Size 39816

Variable Size 259812784

Database buffers 1.049E+09

Redo buffers 327680

　　

For more detailed information, refer to V$sgastat, V$buffer_pool

　　

The main parts include the following:

　　

A, shared pool

　　

A shared pool is the most critical memory fragment in the SGA, especially with regard to performance and scalability. A shared pool that is too small can stifle performance, cause the system to stop, too large a shared pool will have the same effect, and will consume a lot of CPU to manage this shared pool. Using a shared pool incorrectly can only bring disaster. A shared pool can be divided into the following two parts:

　　

SQL statement buffering (Library Cache)

　　

When a user submits an SQL statement, Oracle parses the SQL (parse), which is similar to compiling and consumes relatively much time. After analyzing this SQL, Oracle will save his analysis to the library cache in the shared pool, and when the database executes the SQL for the second time, Oracle automatically skips the analysis process, reducing the time it takes to run the system. This is why the first SQL run is a bit slower than the SQL that runs for the second time.

　　

The following examples illustrate the time of parse

Sql> Select COUNT (*) Fromscpass;

COUNT (*)

----------

243

elapsed:00:00:00.08

　　

This is the time that is spent without data buffers in both Share_pool and

Sql> alter system flush Shared_pool;

System altered.

　　

Empty Share_pool, keep data buffer

Sql> Select COUNT (*) from Scpass;

COUNT (*)

----------

243

elapsed:00:00:00.02

Sql> Select COUNT (*) from Scpass;

COUNT (*)

----------

243

elapsed:00:00:00.00

　　

The parse time of this SQL can be seen from two sentences of SQL in 00:00:00.02

　　

For the SQL statements that are saved in the shared pool, you can query from V$sqltext, V$sqlarea, and for programmers, try to increase the reuse rate of the statements and reduce the parsing time of the statements. A poorly designed application can destroy the entire database of share pool, improve the reuse rate of SQL statements must first develop a good habit, try to use the bind variable.

　　

Data dictionary buffers (Dictionary Cache)

　　

Clearly, the data dictionary buffer is a buffer pool that Oracle has specially prepared for the data dictionary for internal use by Oracle, with nothing to say.

　　

B, block buffer cache (Database buffer cache)

　　

These buffers are data blocks that correspond to all data files that are used. Allow them to operate in memory. At this level there are no system files, user data files, temporary data files, and rollback section files. That is, the data blocks of any file may be buffered. Any modifications to the database are done in the buffer, and the modified data is written to disk by the DBWR process.

　　

The blocks of this buffer are basically managed in two different lists. One is the "dirty" Table of blocks (Dirty list), which needs to use the database block's

　　

Writer (DBWR) to write, and the other is a list of non-dirty blocks (free list), in general, is managed using the least recently used (Least recently USED,LRU) algorithm. The block buffer cache can also be subdivided into the following three sections (Default pool,keep pool,recycle pool). If the initialization parameters (Init.ora) are not set manually, Oracle defaults to default pool. Due to the limitations of the operating system addressing capability, the block buffer cache can reach up to 1.7G on a 32-bit system, and the block buffer cache can reach up to 10G on a 64-bit system.

　　

C, redo log buffers (Redo log buffer)

　　

Redo the buffer of the log file, any modifications to the database are recorded sequentially in that buffer, which is then written to disk by the LGWR process. These modifications may be DML statements, such as (Insert,update, Delete), or DDL statements, such as (Create,alter,drop, etc.). The redo log buffer exists because memory-to-memory operations compare memory to hard disk much faster, so redo the log buffer can speed up the operation of the database, but the consistency and recoverability of the database to consider, the data in the redo log buffer in the retention time is not very long. So the redo log buffers are generally small, and a redo log buffer greater than 3M has no significant practical significance.

　　

D, java program buffers (Java Pool)

　　

Java program area, Oracle 8I Later, Oracle added support for Java in the kernel. The program buffers are reserved for Java programs. If you do not use a Java program it is not necessary to change the default size of the buffer.

　　

E, candidates (Large Pool)

　　

The big pool is not named because it is large, but because it allocates large chunks of memory and handles larger memory than the shared pool, which is introduced in 8.0.

　　

The following objects use a large pool:

　　

mts--assigned UGA in the large pool of the SGA

　　

Parallel query for statements (Parallel executeion of statements)--Allows the allocation of inter-process message buffers to coordinate parallel query servers

　　

Backup--For Rman disk I/O caching

　　

4. Background processes (Background process)

　　

A background process is an Oracle program that manages the reading, writing, recovery, and monitoring of a database. The Server process is primarily connected and communicated with user process, and is exchanged for data by him and user process. On UNIX machines, the Oracle background process is relative to the operating system process, that is, an Oracle background process initiates an operating system process, and on Windows machines, the Oracle background process opens Task Manager, relative to the operating system thread, We can only see a ORACLE.EXE process, but with another tool, we can see the threads that are contained in the process.

　　

On UNIX, you can view background processes in the following ways:

　　

Ps–ef | grep Ora_

# Ps-ef | grep Ora_ | grep xcluat

Oracle 29431 1 0 Sep 2:02 ora_dbwr_sid

Oracle 29444 1 0 Sep 0:03 ora_ckpt_sid

Oracle 29448 1 0 Sep 2:42 ora_smon_sid

Oracle 29442 1 0 Sep 3:25 ora_lgwr_sid

Oracle 29427 1 0 Sep 0:01 ora_pmon_sid

　　

A, Oracle system has 5 basic processes they are

DBWR (data file write process)

LGWR (log file write process)

Smon (System monitoring process)

Pmon (User process monitoring process)

CKPT (checkpoint process, synchronizing data files, log files, control files)

　　

B, DBWR

Writes data from the modified data buffer to the corresponding data file

Maintaining an empty buffer within the system

Here are some easy-to-mistake concepts:

When an update is submitted, DBWR writes the data to the disk and returns it to the user to complete the submission.

DBWR will trigger CKPT background process

DBWR does not trigger the LGWR process

The above concepts are all wrong.

DBWR is a very low-level worker process that writes buffer data to disk in batches. It has little to do with the process of any foreground user and is not under their control. As to whether DBWR will trigger the LGWR and ckpt processes, we'll discuss them in the following sections.

The main conditions of DBWR work are as follows

DBWR Timeout

There are not many empty buffers in the system to hold the data

CKPT process trigger DBWR, etc.

　　

C, LGWR

Writes data from the redo log buffer to the redo log file, LGWR is a process that must communicate with the foreground user process. When the data is modified, a redo log is generated and recorded in the redo log buffer. This redo log can be similar to one of the following structures:

scn=000000001000

Data Block ID

Object id=0801

Data row =02

Modified Data =0011

At the time of submission, LGWR must write the data in the redo log buffer of the modified data to the log data file before notifying the foreground process that the submission was successful and notifying the user by the foreground process. From this we can see that LGWR undertook the task of maintaining the integrity of the system data.

The main conditions of LGWR work are as follows

User Submissions

There are 1/3 redo log buffers that were not written to disk

There are more than 1M redo log buffers that were not written to disk

Timeout

DBWR the SCN number of the data to be written is greater than the SCN number of the LGWR record, DBWR triggers LGWR write

　　

D, Smon

Work mainly includes

Clear temporary space

Complete System Instance recovery at system startup

Poly-Knot free space

Activities to recover transactions from files that are never available

Instance recovery for failed nodes in OPS

Clear the obj$ table

Reduce rollback segments

Take the rollback segment offline

　　

E, Pmon

Primarily used to purge failed user processes and to release resources used by user processes. If Pmon will roll back uncommitted work, release the lock and release the SGA resource allocated to the failed process.

　　

F, CKPT

Synchronization of data files, log files and control files, due to the working principle of DBWR/LGWR, resulting in data files, log files, control files, which requires the CKPT process to synchronize. CKPT updates the header information of the data file/control file.

The main conditions of CKPT work are as follows

At the time of log switching

Database with immediate, transaction, normal option when shutdown the database

Based on the values set by the initial session file Log_checkpoint_interval, Log_checkpoint_timeout, and Fast_start_io_target, determine

User triggered

The following processes need to be manually configured to start

　　

G, ARCH

Oracle initiates the arch process when the database is archived, and when the redo log file is full, the log files are switched, and the old redo log files are copied by the arch process to one or more specific directories/remote machines. These replicated redo log files are called archive log files.

　　

H, RECO

Responsible for solving problems in distributed things. Oracle can connect to multiple databases remotely, and some things are in an outstanding state due to network problems. The RECO process attempts to establish communication with the remote server, and when the failure is eliminated, the reco process automatically resolves all pending sessions.

I, service process server processes

Classification of service processes

Private service processes (dedicated Server process)

A service process corresponds to a user process

Shared Services processes (multitreaded Server process)

A service process that corresponds to multiple user processes, in turn, serves the user process.

PGA & UGA

PGA = Process Global Area

UGA = User Global Area

He saves user information such as variables, permissions, stacks, sort spaces, and so on for dedicated server processes, UGA is allocated in the PGA. For multithreaded processes, UGA is allocated in the large pool.

　　

J. User Process

On the client, pass the user's SQL statement to the service process

　　

5, a global concept through the database----system change number SCN

System change number, a serial number maintained by the system internally. When the system needs to be updated automatically increases, he is the system to maintain data consistency and sequential recovery of important signs.

　　

A. The query statement does not increase the SCN, even if it is a simultaneous update, the corresponding SCN within the database is different. This guarantees the order in which the data is restored.

　　

B. Maintaining consistency of data

　　

Ii. ORACLE Database

　　

The composition of the Oracle database-a collection of physical operating system files. Mainly include the following several.

　　

1, control files (parameter file Init.ora records the location of the control files)

The control file includes the following key information

Database name, checkpoint information, time stamp for database creation

All data files, online log files, archived log file information

Backup information, etc.

With this information, Oracle knows that those files are data files, which are now the redo log files, which are the basic conditions for the system to start and run, so he is the root of Oracle operation. It is not possible to start without controlling the file system. Control files are very important, generally using a plurality of mirror phase

The loss of control files will make the recovery of the database very complex.

Control file information can be obtained from the V$controlfile query

　　

2. Data files (details of data files are recorded in the control file)

You can view the data file in the following ways

Sql> select name from V$datafile;

NAME

---------------------------------------------

/u05/dbf/prod/system_01.dbf

/u06/dbf/prod/temp_01.dbf

/u04/dbf/prod/users_01.dbf

/u09/dbf/prod/rbs_01.dbf

/u06/dbf/prod/applsys_indx_01.dbf

/u05/dbf/prod/applsys_data_01.dbf

As can be seen from the above, data files can be broadly divided into the following categories:

　　

I. System data files (SYSTEM_01.DBF)

Storing system tables and data dictionaries, typically without user data, but user scripts, such as procedures, functions, packages, etc., are stored in the data dictionary.

Noun Explanation: Data dictionary data dictionary is some system table or view, he holds the system information, he includes the database version, data file information, table and index and other pieces of information, system running state and other system-related information and user script information. The database administrator can understand the operational status of Oracle by querying the data dictionary.

　　

Ii. rollback segment File (RBS_01.DBF)

If the database makes modifications to the data, the rollback segment must be used, and the rollback segment is used to temporarily store the pre-modified data (before Image). The rollback segments are usually placed on a separate table space (rolling back the table space) to avoid tablespace fragmentation, and the tablespace contains data files that are rolled back to the data file.

　　

Iii. Temporary data file (TEMP_01.DBF)

The primary storage of user's sort and other temporary data, similar to the rollback segment, the temporary section is also prone to fragmentation of the table space, and there is no way to open a temporary segment on a permanent table space, so there must be a temporary table space, it contains data files are temporary data files, mainly for the non-memory of the sorting operations. We must specify a temporary table space for the user.

　　

Iv. User data files (/applsys_data_01.dbf, applsys_indx_01.dbf)

Storing user data, here are two common types of user-type data, general data and index data, which, in general, can be considered on different disks if conditions permit.

　　

3. Redo log file (online redo log)

Any action that the user makes to the database is logged in the Redo log file. Before you know the redo log, you must understand the two concepts of redo logs, redo the log group and redo log Group members (MEMBER), a database that has at least two log group files, and one group to write and then write another group, that is, to write in turn. There is at least one log member in each log group, and more than one log member in a log group is a mirror relationship, which facilitates the protection of log files because of the corruption of log files, especially the current online log, the impact on the database is enormous.

The exchange process for online log groups is called switching, and it is important to note that log switching can cause temporary "hangs" in a poorly optimized database. There are generally two cases of hangs:

In the case of archiving, logs that need to be archived are too late to archive, and online logs need to be re-used

Checkpoint events are not completed (log switchover causes checkpoints), and online logs need to be re-used

The common means of solving this problem are:

　　

I. Adding log groups

　　

Ii. increasing the size of log file members

With V$log You can view the log groups and V$logfile can view specific member files.

　　

4. Archive log files

Oracle can run in two modes, archive mode and no archive mode. Without archiving, of course, you wouldn't have archived logs, but your system wouldn't be a practical system, especially for production systems, because you could lose data. However, in archive mode, in order to save all the user's changes, the system saves them to a contiguous set of file series after the redo log file is switched and overwritten, which is the archive log file.

Some people might say, archive log files occupy my large amount of hard disk space, in fact, you are willing to waste a bit of disk space to protect your data, or is willing to lose your data? We need to ensure the security of our data. In fact, the archive does not always occupy your disk space, you can back it up to tape, or delete all the log files before the last full backup.

　　

5. Initialize parameter file

Initsid.ora or Init.ora files, because the version is different, its location may be different. In 8i, usually located under $oracle_home/admin//pfile, the initialization file records many database startup parameters, such as memory, control files, number of processes, etc., when the database is launched (Nomount load), initialization files record a lot of important parameters, The performance impact on the database is very large, if not very understanding, do not easily rewrite, otherwise it will cause database performance degradation.

　　

6. Other documents

I. Password file

Authentication for Oracle with SYSDBA rights users.

Ii. log files

Alarm log file (Alert.log or Alrt.ora)

Record database startup, shutdown, and some important error messages. The database administrator should check this file frequently and respond to any problems that occur. You can find his path through the following SQL select value from v$parameter where name = "Background_dump_dest";

Background or user trace file

The information written before the system process or user process error is generally not readable and can be translated into a readable format through Oracle's Tkprof tool. For the system process to generate the trace file with the path of the alarm log file, the path of the user trace file, you can find his path through the following SQL select value from v$parameter where name = "User_dump_dest";

　　

III. Logical Structure of Oracle

　　

1. Table Space (tablespace)

A tablespace is a collection of basic logical structures in a database, a series of data files. A table space can contain more than one data file, but a single data file can belong to only one table space.

　　

2. Paragraph (Segment)

Segments are the space that objects occupy in the database, although segments and database objects are one by one corresponding, but segments are viewed from the database storage perspective. A segment can belong to only one tablespace, although a table space may have multiple segments.

Tablespaces and data files are a one-to-many relationship on physical storage, where table spaces and segments are one-to-many relationships on logical storage, and segments do not directly relate to data files. A segment can belong to more than one data file, and about segments can specify which data file to extend to.

section can be divided into the following four types

Data segment (Segment)

Index Segment (Index Segment)

Rollback segment (Rollback Segment)

Temporary segment (Temporary Segment)

　　

3. Interval (Extent)

There are many explanations about extent's translation, some of which are extended, some of which are translated into regions, which I usually translate as intervals. There can be more than one interval in a segment, the interval is a large storage space reserved for the data one time, until the interval is full, the database will continue to request a new reserved storage space, that is, the new interval, until the maximum interval number of segments (max Extent) or no available disk space can be applied. In oracle8i or above, theoretically a segment can be infinitely wide, but multiple intervals have a performance impact on Oracle, and Oracle recommends distributing the data in as few intervals as possible to reduce the management and head movement of Oracle.

　　

4, Oracle data Block (block)

Oracle's most basic storage unit, which is an integer multiple of the OS data block. Oracle operates on blocks as a base unit, and one interval can contain multiple blocks (if the interval size is not an integer multiple of the block size, Oracle actually expands to an integer multiple of the block).

　　

5, basic table space Introduction

　　

A. system table space

Main storage data dictionary and Internal system table base table

View SQL for Data data dictionary

SELECT * FROM Dict

View SQL for internal system tables

SELECT * FROM V$fixed_view_definition

The DBA must have a deep understanding of the data dictionaries in the system tables of systems, and they must prepare some basic SQL statements that enable them to immediately understand the state of the system and the status of the database, which include

The remaining space of the system

The SGA of the system

Wait for the state system

User's permissions

The current user lock

Buffer usage status, etc.

On the road to becoming a DBA, we do not recommend that you rely too much on good database management tools such as Oem/quest, because they are not conducive to your understanding of data data dictionaries, and SQL statements can do almost all of the database management work.

A large amount of reading and writing is a significant feature of the table space.

　　

B. temporary table space.

The temporary table space, as its name implies, is a temporary space for temporary data, such as a sort operation, and his space is freed at the next system startup.

　　

C. Rolling back a segment table space

　　

I. The role of the rollback segment in the system

When the database is updated to insert and delete operations, the new data is updated to the original data file, and the old data (before Image) is placed in the rollback segment, and if the data needs to be rolled back, the data can be copied back to the data file from the rollback segment. To complete the rollback of the data. When the system is restored, the rollback segment can be used to rollback data that is not being commit and to resolve the system's accessibility.

The rollback segment is a large number of writes, typically a small amount of reading, so it is recommended that the rollback segment be separately placed on a separate device (such as a separate disk or RAID) to reduce the IO contention on the disk.

　　

Ii. how the rollback segment works

A rollback table space can be divided into multiple rollback segments.

A rollback segment can hold data for multiple sessions.

The rollback segment is a circular data model

It is assumed that the rollback segment consists of 4 intervals, and their order of use is 2à interval 3à interval 4à interval of interval 1à interval 1. That is, the interval can be recycled, when the interval 4 to the interval 1, the interval 1 inside the session has not ended, the interval 4 can not be used after the interval 1, then the system must allocate interval 5, to continue to serve other services.

We analyze the completion of an UPDATE statement

　　

①. User submits an UPDATE statement

　　

②. Server Process checks the memory buffer.

If there is no buffering of the data block, the read-in from disk

　　

I. If there is no valid space for memory, DBWR is initiated to write dirty buffers that are not written to disk

　　

II. If there is a valid space, the read-in

　　

③. Updating data within a buffer

　　

I. Apply for a rollback segment entry to write old data as a rollback segment

　　

II. Lock and update data

　　

III. And at the same time record the changes in redo log buffer

"Go" Oracle Architecture