Oracle architecture and some tools to understand

Oracle's architecture is roughly divided into 3 parts,

Client: User-side

Oracle instance: is called an instance, consisting of a memory structure (a memory pool or an SGA) and a background process. Oracle instance is one of the core of Oracle RDBMS and is responsible for the management capabilities of the RDBMS. The memory structure has the SGA (Share global Area) system global zone, and the five major processes in the background include pnom (Process Monitor), Smon (System Monitor), DBWN,LGWR,CKPT, etc.

Database data files: Various files, data files, control files, redo log files and so on.

Description: Multiple instances can correspond to an Oracle database. The SGA can be shared by multiple processes, and the PGA is typically used by a process, which is a memory that is allocated for no single user. So when creating a database with DBCA, 80% gives sga,20% to the PGA.

User connection process = User Process + Service process + PGA

Dateabase (Database)

Tablespace (table space)

Segment (segment)

Area Area

The Block (data block) is finally mapped to a disk block.

Tools have Sqlplus, EM web-managed way. The DBCA tool creates a database.

Toad for Oracle, the NAVICAT for Oracle tool can also use the Oracle database for third parties.

What happens in an RDBMS when you execute a written SQL statement

1. Loading SQL statements into the database buffer
2. Load the copy of the data file to be manipulated by the SQL statement into the database buffer
3. Execute the SQL statement and modify the copy of the data file to form a "dirty buffer"
4. Ckpt detected a "dirty buffer" and called DBWN
5. Before dbwn runs, LGWR is run, recording the original state of the data file and the changes to the database to redo log files
6. Run dbwn to write "dirty buffer contents to data file"
7. Simultaneously ckpt modify control file and data file header
8. Smon Reclaim unnecessary idle resources

At last

Finally, let's take a look at how Oracle RDBMS works

1. User submits a request before accessing Oracle server (including information such as db_name, instance_name, username, password, etc.), and Oracle server receives the request and passes the validation of the password file , allocate the SGA memory pool, and start the background process to create and launch the instance at the same time.
2. After launching the instance, user process establishes connect with the server process.
3. Build sesscion through server process and Oracle instance.
4. The user executes the SQL statement that is received by the server process and interacts directly with Oracle.
5. The SQL statement arrives at Oracle Instance through the server process, and then loads SQL into the database buffer.
6. The Server process notifies Oracle database to load a copy of the data block associated with the SQL statement into the buffer.
7. Executes the SQL statement in the database buffer and produces a "dirty buffer".
8. The CKPT checkpoint process checks to the "dirty buffer" and calls the DBWN database write process, but before Dbwn executes, the original state of the data file, the change of the database, and so on should be recorded to redo log files prior to execution by LGWR.
9. Writes the updated content to a data file on disk.
10. Returns the result to the user

Oracle architecture and some tools to understand