Oracle Architecture Overview

Oracle Architecture Overview

I. Components of Oracle Architecture
Www.2cto.com
The Oracle architecture includes many basic components. The following describes these components in detail.
1. Oracle Server: the Oracle server contains multiple file structures, process structures, and memory structures. However, not all these structures are used to process SQL statements. Some structures are used to improve the performance of a database, ensure that the database can be recovered in the case of software or hardware errors, or perform other tasks required to maintain the database. The Oracle Server includes an Oracle instance and an Oracle database.
 
2. Oracle instance: An Oracle instance is a combination of background processes and memory structures. Data in the database can be accessed only after the instance is started. Each time an instance is started, the system global zone (SGA) is allocated and the Oracle background process is started. Background processes call processes to execute various functions. They unify the functions processed by multiple Oracle programs running for each user. Background processes execute input/output (I/O) and monitor other Oracle processes to improve concurrency, thus improving performance and reliability.
Www.2cto.com
3. Oracle Database: the Oracle database contains operating system files (also known as database files), which provide actual physical storage for database information. Database files are used to ensure data consistency and recovery when the instance fails.
 
4. Other key files: Non-database files are used to configure instances, verify privileged users, and when disks fail
Restore the database.
 
5. User processes and server processes: When executing SQL statements, user processes and server processes are the main processes involved. However, other processes can also help the server to process SQL statements.
 
6. other processes: there are many other processes available for other options, such as Advanced Queuing, Real Application Clusters, Shared Server, and
Advanced Replication. These processes will be discussed separately in the corresponding courses.
 



2. The client layer consists of two components: user and user process.
 
3. The server layer consists of three components: the server processes, instances, and databases that execute SQL.
 
1. instance components refer to a group of operating system processes and memory structures initialized at startup.
 
2. Database components refer to physical files used for data storage and database operations.
 
4. To sum up, the architecture of a single instance database is composed of four interactive components (as shown in ):
 
1. Interaction between users and user processes
 
2. Interaction between user processes and Server Processes
 
3. Interaction between server processes and Instances
 
4. Interaction between instances and databases
 
 
 
The client process is unlikely to have any connection with the database: All access requests must be performed through the intermediate server process. Client-server separation is implemented between user processes (SQL generation) and server processes (SQL Execution.
 
V. instance Components
An instance is a database access channel, including shared memory and background processes. It is composed of a shared memory block and a large number of background processes called the system global zone (SGA.
 
1. system global area (SGA)
The memory structure implemented by the shared memory segment provided by the operating system is called SGA. SGA contains at least three data structures:
(1) database buffer cache (buffer cache)
Www.2cto.com
(2) SHARED POOL)
A. database cache
B. Data Dictionary Cache
C. PL/SQL Zone
D. SQL query and PL/SQL function result Cache
 
(3) LOG BUFFER)
 
It may also include:
(1) Large Pool (Large Pool)
 
(2) JAVA Pool)
 
(3) streaming Pool)
 
Displays the current, maximum, and minimum values of the SGA component that can be dynamically reset.
Gyj @ OCM> select COMPONENT, CURRENT_SIZE, MIN_SIZE, MAX_SIZE from V $ SGA_DYNAMIC_COMPONENTS;
 
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
------------------------------------------------------------------------------------------------
Shard pool 264241152 264241152 264241152
Largepool 4194304 4194304 4194304
Javapool 25165824 25165824 25165824
Streamspool 0 0 0
DEFAULTbuffer cache 511705088 511705088 511705088
KEEPbuffer cache 16777216 16777216 16777216
RECYCLEbuffer cache 0 0 0
DEFAULT2K buffer cache 0 0 0
DEFAULT4K buffer cache 0 0 0
DEFAULT8K buffer cache 0 0 0
DEFAULT16K buffer cache 8388608 8388608 8388608
DEFAULT32K buffer cache 0 0 0
Invalid Dio Pool 0 0 0
ASMBuffer Cache 0 0 0
 
14 rows selected.
 
2. background processes (dbwn, lgwr, ckpt, smon, pmon, and reco)
(1) Introduction to various background processes
DBWn: Database Write Program
LGWR: log writing program
CKPT: indicates a checkpoint. It sends a signal to DBWR at the checkpoint and changes the file headers of controlfile and datafile.
SMON: system monitoring, instance recovery, cleanup of unused temporary segments
PMON: Process Monitoring
RECO: Restores processes and automatically resolves faults in distributed transactions.
MMAN: The Memory Manager process enables Automatic Management of Shared Memory
PSPO: The main function is to start other Oracle processes.
MMON: Manageability Monitor manages monitoring processes
CJQ0: scheduling process of the task queue. It finds the task to be executed from the job $ table and assigns the job process for execution. If the job process is insufficient, a new job process is automatically generated.
ARCn: archiving
QMNC: queue monitoring synchronization process
MMNL: A New Process added by AWR. It is mainly used to refresh AWR data from the memory to the table.
Dbrm: database resource management process, responsible for setting resource plans and other resource management work
Smco: This process is responsible for space management, coordination and management, and space allocation and recovery.
W000: the above-mentioned tasks are dynamically generated by smcO.
Diag: a database diagnostic process that maintains and manages various dump files for diagnosis and executes the oradebug command.
Dia0: Another database diagnostic process that detects hang and deadlock handling in the Oracle database.
Dnnn: scheduling PROCESS, allowing user processes to share limited SERVER processes)
Snnn: A server process that communicates with user processes and interacts with ORACLE to execute tasks on behalf of relevant user processes.
Vktm: this virtual keeper of time is responsible for tracking the time, which is particularly important in the cluster environment.
 
(2) Relationship between the five core background processes ::
Pmon <-- ckpt <-- lgwr <-- dbwn <-- smon
------>
(3) which background processes can be killed?
Kill-9
Sqlplus/as sysdba
Select sid, program from v $ session where type = 'user' andstatus = 'active ';
 
6. What is an ORACLE database?
1. Physical Structure Analysis:
Consists of a series of Files: data files, control files, online log files, archived log files, parameter files, password files, etc.
 
(1) Data File: The most core file in the database
Select * from v $ dbfile;
 
(2) control file: it is a very important file, including the physical structure of the entire database, all data files, log files, and other information and backup information.
Select * from v $ controlfile
Selecttype, record_size, records_total, records_used from v $ controlfile_record_section;
 
(3) Online Log File: used to record Database Change Information
Select * from v $ logfile;
 
(4) archive log files: Mainly used for database recovery
 
(5) parameter file: spfile/pfile
 
(6) password file: stores the superuser password and the username/password of other privileged users
Strings orapwocp
Grantsysdba to system;
 
2. Database Logic Structure
The physical structure of the database is presented to the system administrator as an operating system file. What you see is the logical structure of a table. Oracle uses the term "segment" to describe any structure containing data. A typical segment is a table that contains data rows, but an Oracle database contains more than 10 segment types.
Oracle abstracts logical storage from physical storage in the tablespace mode. A tablespace is a logical set of one or more segments. It is physically a set of one or more data files. If the term "link score" is used, there are many-to-many relationships between segments and data files: A table can be distributed across multiple data files, A data file may also contain a portion of multiple tables. Oracle inserts tablespace entities between segments and files to solve this multi-to-Multi-relationship problem.
Segments are composed of multiple blocks. The format of the data file is set to multiple blocks. As the number of blocks increases, these blocks are allocated to segments. Because the space for managing a block is too time-consuming, the staff are grouped in the partition (extent ). A partition is a series of consecutive blocks in the data file. By adding a new partition for the segment, the segment will be expanded. These areas must be adjacent, even in a data file. They can be from any data file that is part of the tablespace where the segment is located.

 
Logically, a tablespace can contain multiple segments, and each segment can contain multiple partitions. A zone is a set of continuous Oracle blocks. Physically, data files are composed of multiple operating system blocks (allocated by the file system being used by the operating system. The relationship between the two ends of the connection model shows that a tablespace can contain multiple data files. At the lowest level, an Oracle block will contain multiple operating system blocks.
 
 
7. Relationship between databases and instances: 1: 1 or 1: N
 
Determine whether the instance is part of the RAC database:
Gyj @ OCM> select parallel from v $ instance;
 
PAR
-------
NO
 
8. knowledge points involved in the whole process of executing an SQL statement:
User process, connection, session, foreground process, PGA, instance, database
 
 
9. Test Site:
1. All work completed through OEM can be completed through SQL statements. The OCP examination is widely used to examine the use of SQL for management. It is vital to be familiar with the command line technology.
 
2. the SGA memory is shared by all the backend and foreground processes. The PGA memory is only accessible to the foreground processes of the sessions allocated. In the 11g, both the SGA and PGA memory can be automatically managed.
 
3. What SGA structures are required and what are optional? Data buffer cache, log buffer, and shared pool are required, while large pools, JAVA pools, and stream pools are optional.
 
4. You can adjust the cache size of the database buffer or manage it automatically.
 
5. The log buffer size remains fixed and is set to a fixed value when the instance is started. It cannot be automatically managed.
 
6. The size of the Shared Pool is dynamic and can be automatically managed.
 
7. The size of the large pool is dynamic and can be automatically managed.
 
8. The JAVA pool size is dynamic and can be automatically managed.
 
9. The stream pool size is dynamic and can be automatically managed.
 
10. If the session ends abnormally, what will happen to the active transaction? The PMON background process rolls back the active transaction.
 
11. Under what circumstances will DBWR perform write operations? There are no available buffers, too many dirty buffers, three seconds of timeout, or checkpoints.
 
12. What actions does DBWn do when a transaction is committed? The answer is: It does nothing.
 
13. Under what circumstances will LGWR dump the log buffer to the disk? When the session sends a commit request and the buffer usage reaches 1/3, when the buffer reaches 1 MB, DBWn needs to be executed before writing.
 
14. When will a full checkpoint appear? This can only be requested, or appears when the database is closed in an orderly manner.
 
15. By default, MMON collects snapshots every hour and starts ADDM.
 
16. LGWR performs write operations on the log files of the On-premise machine. ARCn reads online log files without any other processes accessing such files.
 
17. Which three types of files must the database contain? These three types of files are control files, online redo log files, and data files.
 
18. To ensure normal operation of each database, at least two online redo log file groups must be created. To ensure security, each group must have at least two members.
 
19. The server process reads data files, while DBWn writes data files.
 
20. In Oracle10g and later versions, the SYSAUX tablespace must be created when the database is created.