Oracle architecture notes

I have always wanted to take a good look at Oracle, but now the item is about oracle Security. So, take a moment to sort it out. Although it is impossible to fully understand oracle, but it is better to do it than not.

I. ORACLE instances
　　
1. ORACLE instances
　　
System Global Area (SGA) and Background Process are called database instances.
　　
2. ORACLE Database
　　
A collection of physical files (data files, control files, online logs, parameter files, etc)
　　
3. System Global Area (SGA)
　　
System Global Area is a huge Area of shared memory. It is regarded as a large buffer pool for Oracle databases. The data here can be shared by various ORACLE processes. The size can be viewed using 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 information, see V $ sgastat and V $ buffer_pool.
　　
It mainly includes the following parts:
　　
A. Shared pool)
　　
The shared pool is the most critical memory segment in SGA, especially in terms of performance and scalability. A small shared pool will stop the system, and a large shared pool will have the same effect, which will consume a lot of CPU to manage the Shared Pool. Improper use of the Shared Pool will only cause disasters. The sharing pool can be divided into the following two parts:
　　
Library Cache)
　　
When a user submits an SQL statement, Oracle analyzes the SQL statement (parse). This process is similar to compiling and takes a relatively large amount of time. After the SQL statement is analyzed, Oracle saves the analysis result to the Library Cache of the Shared pool. When the database executes the SQL statement for the second time, Oracle automatically skips the analysis process, this reduces the system running time. This is why the first SQL statement is slower than the second one.
　　
The following example shows the time of parse.
SQL> select count (*) fromscpass;
COUNT (*)
----------
243
Elapsed: 00:00:00. 08
　　
This is the time used when there is no Data buffer in the performance_pool and Data buffer.
SQL> alter system flush SHARED_POOL;
System altered.
　　
Clear performance_pool and 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 time difference between the two SQL statements shows that the Parse time of the SQL statement is about 00:00:00. 02.
　　
SQL statements stored in the shared pool can be queried from V $ Sqltext and v $ Sqlarea. programmers should try to increase the statement reuse rate and reduce the statement analysis time. A poorly designed application can destroy the Share pool of the entire database. To improve the Reuse Rate of SQL statements, you must first develop good habits and try to use Bind variables as much as possible.
　　
Data Dictionary Cache)
　　
Obviously, the data dictionary buffer is a buffer pool specially prepared by ORACLE for data dictionary for internal use in ORACLE. Nothing can be said.
　　
B. Database Buffer Cache)
　　
These buffers correspond to some used data blocks in all data files. Enable them to operate in memory. At this level, there are no system files, household data files, temporary data files, and rollback segment files. That is, data blocks of any file may be buffered. Any modification to the database is completed in the buffer, and the DBWR process writes the modified data to the disk.
　　
The blocks in this buffer zone are basically managed in two different lists. A "Dirty" table (Dirty List) of blocks.
　　
Writer (DBWR) to write data. The other is a Free List of non-dirty blocks. Generally, it is Least Recently Used (Least Recently Used, LRU) algorithm. Block buffer high-speed cache can be subdivided into the following three parts (Default pool, Keep pool, Recycle pool ). If the initialization parameter (Init. ora) is not set manually, ORACLE defaults to the Default pool. Due to the limitation of the addressing capability of the operating system, without special settings, on a 32-bit system, the maximum cache capacity of the block buffer can reach 1.7 GB. On a 64-bit system, the Maximum Cache size of a block buffer is 10 Gb.
　　
C. Redo log buffer)
　　
Redo the buffer of the log file. Any changes to the database are recorded in the buffer in order, and then written to the 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 the memory-to-memory operations are much faster than the memory-to-hard disk operations. Therefore, replaying the log buffer can speed up database operations, however, considering the consistency and recoverability of the database, the retention time of data in the redo log buffer is not long. Therefore, the duplicate log buffer is usually very small. The duplicate log buffer greater than 3 m does not have much practical significance.
　　
D. Java program buffer (Java Pool)
　　
Java program zone. After Oracle 8I, Oracle added support for Java in the kernel. The program buffer is reserved for the Java program. If you do not need a Java program, you do not need to change the default size of the buffer.
　　
E. Large Pool)
　　
The name of a large pool is not because it is large, but because it is used to allocate large blocks of memory and process larger memory than the shared pool, which is introduced at 8.0.
　　
The following objects use a large pool:
　　
MTS -- allocate UGA in the Large Pool of SGA
　　
Parallel Executeion of Statements: allows the allocation of message buffers between processes to coordinate Parallel query servers.
　　
Backup-used for RMAN disk I/O cache
　　
4. Background process)
　　
The background process is an Oracle program used to manage database read/write, recovery, and monitoring. Server Process communicates with user process and exchanges data with user process. On Unix machines, the Oracle background process is relative to the operating system process. That is to say, an Oracle background process starts an operating system process. On Windows machines, the Oracle background process is relative to the operating system thread, open the task manager and we can only see one ORACLE..
　　
You can view background processes in Unix as follows:
　　
Ps-ef | grep ora _
# Ps-ef | grep ora _ | grep XCLUAT
Oracle 29431 1 0 Sep 02 ora_dbwr_SID
Oracle 29444 1 0 Sep 02 ora_ckpt_SID
Oracle 29448 1 0 Sep 02 ora_smon_SID
Oracle 29442 1 0 Sep 02 ora_lgwr_SID
Oracle 29427 1 0 Sep 02 ora_pmon_SID
　　
A. The Oracle system has five basic processes:
DBWR (data file writing process)
LGWR (Log File writing process)
SMON (system monitoring process)
PMON (user process monitoring process)
CKPT (Checkpoint Process, synchronous data file, log file, control file)
　　
B. DBWR
Write the data in the modified data buffer to the corresponding data file.
Maintain an empty BUFFER IN THE SYSTEM
Here are several concepts that are prone to errors:
After an update is submitted, DBWR writes the data to the disk and returns it to the user for submission.
DBWR triggers the CKPT background process
DBWR does not trigger the LGWR Process
The above concepts are all incorrect.
DBWR is a very underlying working process that writes data in the buffer zone to the disk in batches. It has almost nothing to do with the processes of any front-end users and is not controlled by them. We will discuss whether DBWR will trigger the LGWR and CKPT processes in the following sections.
The main conditions for DBWR operation are as follows:
DBWR timeout
There are not many empty buffers in the system to store data.
CKPT process triggers DBWR, etc.
　　
C. LGWR
Write the data in 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, the system generates a redo log and records it in the redo log buffer. This redo log can be considered as a structure similar to the following:
SCN = 000000001000
Data Block ID
The object ID = 0801.
Data ROW = 02
Data after modification = 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, and then notify the foreground process of successful submission, and the foreground process will notify the user. From this point, we can see that LGWR undertakes the task of maintaining system data integrity.
The main conditions for LGWR work are as follows:
User submission
1/3 redo log buffer not written to disk
A redo log buffer larger than 1 MB is not written to the disk
Timeout
The SCN Number of the data to be written by DBWR is greater than the scn number of the lgwr record. DBWR triggers LGWR writing.
　　
D. SMON
Work mainly includes
Clear temporary space
The system instance is restored when the system starts.
Gathering free space
Recovering transaction activities from files that are not available
Instance recovery for failed nodes in OPS
Clear OBJ $ table
Reduce rollback segments
Offline rollback segments
　　
E. PMON
It is mainly used to clear invalid user processes and release resources used by user processes. For example, PMON rolls back unsubmitted jobs, releases locks, and releases the SGA resources allocated to failed processes.
　　
F, CKPT
Synchronization of data files, log files, and control files requires the CKPT process to synchronize data files, log files, and control files due to the working principle of DBWR/LGWR. CKPT updates the header information of the data file/control file.
The main conditions for CKPT operations are as follows:
When switching logs
When the database is shut down with the immediate, transaction, and normal options
The values are determined based on the values set in the initial file LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT, and FAST_START_IO_TARGET.
User-triggered
Manual configuration is required for starting the following processes
　　
G, ARCH
When the database runs in archive mode, Oracle starts the ARCH process. When the redo log file is full, switch the log file, the old redo log file is copied to one/more specific directories/remote machines by the ARCH process. These replicated redo log files are called archive log files.
　　
H. RECO
Resolves faults in distributed transactions. Oracle can connect to multiple remote databases. Due to network problems, some things are pending. The RECO process tries to establish communication with the remote server. When the fault is eliminated, the RECO process automatically resolves all pending sessions.
I. service Process Server Process
Service Process Classification
Dedicated Server Process)
A service process corresponds to a user process
MultiTreaded Server Process)
A service process corresponds to multiple user processes and serves user processes in turn.
PGA & UGA
PGA = Process Global Area
UGA = User Global Area
It stores user information such as user variables, permissions, stacks, and sorting spaces. For dedicated server processes, UGA is allocated in PGA. For multi-threaded 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 throughout the database-System Change Number (SCN)
System Change number, a serial number maintained by the system. It is automatically added when the system needs to be updated. It is an important indicator of maintaining data consistency and sequential recovery in the system.
　　
A. the query statement does not increase the SCN. Even if an update occurs at the same time, the corresponding SCN in the database is different. This ensures the order of data recovery.
　　
B. Maintain data consistency.

1 2 Next Page