Tiêu đề DBA-Complete.pdf ✅

Database Administration Compiled By: Dadhi R. Ghimire Page 1 Architecture of Oracle Database Management System Oracle is a Relational Database Management System (RDBMS), which uses Relational Data Model to store its database and SQL (commonly abbreviated as Structured Query Language) to process the stored data. The architecture of Oracle system can be best explained in terms of client/server paradigm. Thus, we will explain the architecture of Oracle server using the structure called instance. An oracle instance is a complex set of memory structures and operating system processes. It is the Oracle instance, which manages all database activities, such as transaction processing, database recovery, form generation, and so on. The instance structure is loosely styled after UNIX's implementation of multitasking operating system. Discrete processes perform specialized tasks within the RDBMS that work together to accomplish the goals of the instance. Each process has a separate memory block that it uses to store local and private variables, address stacks and other runtime information. The processes use a common shared memory area for processing data concurrently. This memory block is called the System Global Area (SGA). Figure 1 illustrates the architecture of an instance. Each component of the instance is described below. Figure 1: Oracle instance architecture System Global Area (SGA) The SGA is the primary memory component of the instance. It provides memory structure necessary for data manipulation, SQL statement parsing, and redo caching. The SGA is shared, which means that the multiple processes can access and modify the data contained in it in a synchronized manner. The SGA consists of the following components:  Shared Pool  Database buffer cache
Database Administration Compiled By: Dadhi R. Ghimire Page 2  Redo Log Buffer  Multithread server (MTS) structures. The Shared Pool Figure 2 illustrates the structure of a shared pool. It contains the library cache, the dictionary cache, and server control structures (such as the database character set). Figure 2: Oracle Shared pool architecture Library cache: Stores the text, parsed format, and execution plan of SQL statements that have been submitted to the RDBMS, as well as the headers of PL/SQL packages and procedures that have been executed. For each SQL statement the server first checks the library cache to see if an identical statement has already been submitted and cached. If it has, then the server uses the stored parse tree and execution path for the statement, rather than building these structures from scratch. The library cache has shared and private SQL areas. The shared area contains the parse tree and execution path for SQL statement. The private SQL area contains session specific information, such as bind variable, environment and session parameters, runtime stacks and buffers, etc. A private SQL area is created for each transaction initiated, and it is de-allocated after the cursor corresponding to that private area is closed. Using these two structures, the server can reuse the information common across all execution of an SQL statement, while session specific information to the execution can be retrieved from the private SQL area. The private SQL area is further divided into persistent and runtime areas. The persistent area contains information that is valid and applicable through multiple executions of the SQL statement. The runtime area contains data that is used only while the SQL statement is being executed. Dictionary cache: Stores data dictionary rows that have been used to parse SQL statements. Information such as segment information, security and access privileges, and available free storage space is held in this area. The Database Buffer Cache
Database Administration Compiled By: Dadhi R. Ghimire Page 3 The buffer cache is composed of memory blocks. All data manipulated by Oracle server is first loaded into the buffer cache before being used. All data updates are performed in the buffer blocks The data movement (swapping and loading) between buffer and disk or other parts of RAM is by least recently Used (LRU) algorithm. The LRU list keeps track of what data blocks are accessed and how often. Buffer blocks that have been modified are called dirty and are placed on the dirty list. The dirty list keeps track of all data modifications made to the cache data that have not been flushed to disk. When Oracle receives a request to change data, the data change is made to the blocks in the buffer cache and written to the redo log, and then the block is put on the dirty list. Subsequent access to this data reads the new value from the changed data in the buffer cache. Dirty data from the dirty list are written to the disk database under deferred update policy. The Redo Log Buffer The redo log buffer is used to store redo information in memory before it is flushed to the redo log files on the disk. It is circular buffer. The Oracle Background Process The Oracle server process transactions concurrently. Thus, at any time there may be hundreds of simultaneous users performing a number of different operations. To accomplish these tasks, the server divides the entire workload between a number of programs, each of which operates largely independently of one another and has a specific role to play. These programs are referred to as the Oracle background processes. The Oracle background processes are:  SMON (System Monitor) and PMON (Process Monitor): SMON is the process that performs automatic instance recovery. If the last database shutdown was not clean, SMON automatically rolls forward the operations that were complete but could not be installed in the database, and rolls back unfinished transactions. SMON process also manages certain database segments, reclaiming temporary segment space no longer in use, and automatically combining contiguous blocks of free space in the data files.  PMON: is responsible for cleaning up terminated or failed processes, rolling back uncommitted transactions, releasing the locks held by disconnected processes, and freeing SGA resources held by failed processes. It also monitors the server and dispatcher processes, automatically starting them if they fail.  DBWR: Database Writer process is responsible for writing the dirty blocks from the database buffer cache to the data files on the disk. The process waits until certain criteria are met, then reads the dirty list and writes a set of modified blocks in batch. In most installations, there is one DBWR process to handle all the write activity of the database. However, more than one DBWR process can be started if one is incapable of keeping up with the demands of the database.