Oracle Basics

Oracle DB Diagram

    ---
	config:
	  theme: forest
	  layout: elk
	---
	flowchart TD
	  subgraph s1["Instance DB"]
	    style s1 fill:#E8F5E9,stroke:#388E3C,stroke-width:2px
	
	    subgraph s1a["Background Processes"]
	      style s1a fill:#FFF9C4,stroke:#FBC02D,stroke-width:1px
	      n5["PMON (Process Monitor)"]
	      n6["SMON (System Monitor)"]
	      n10["RECO (Recoverer Process)"]
	    end
	
	    subgraph s1b["PGA (Process Global Area)"]
	      style s1b fill:#E3F2FD,stroke:#1976D2,stroke-width:1px
	      n1["Processes"]
	    end
	
	    subgraph s1c["SGA (System Global Area)"]
	      style s1c fill:#FFEBEE,stroke:#D32F2F,stroke-width:1px
	      subgraph n7["Shared Pool (SP)"]
	        style n7 fill:#F3E5F5,stroke:#7B1FA2,stroke-width:1px
	        n7a["DC (Dictionary Cache)"]
	        n7b["LC (Library Cache)"]
	        n7c["RC (Result Cache)"]
	      end
	      n8["DB Cache (DBC)"]
	      n9["Redo Buffer"]
	      n3["DBWR (DB Writer)"]
	      n4["LGWR (Log Writer)"]
	      n5["PMON (Process Monitor)"]
	      n6["SMON (System Monitor)"]
	      n10["RECO (Recoverer Process)"]
	    end
	  end
	
	  subgraph s2["Database: Physical Files"]
	    style s2 fill:#FFF3E0,stroke:#F57C00,stroke-width:2px
	    n11["TBS (Tablespaces, files in .DBF)"]
	    n12["Redo Log Files"]
	    n13["Control Files"]
	    n14["SPFILE (Binary Authentication File)"]
	    n15["ArchiveLog files"]
	  end
	
	  subgraph s3["Operating System"]
	    style s3 fill:#E0F7FA,stroke:#00796B,stroke-width:2px
	    n16["Listener (Port 1521)"]
	  end
	
	  n3 --> n11
	  n3 --> n7c
	  n4 --> n12
	  n6 --> n7a
	  s3 --> s1
	  s1c <--> n12
	  s1c <--> n13
	  s1c <--> n14
	  n7b <--> n7c
	
	  classDef Aqua stroke-width:1px, stroke-dasharray:none, stroke:#0288D1, fill:#B3E5FC, color:#01579B
	  classDef Yellow stroke-width:1px, stroke-dasharray:none, stroke:#FBC02D, fill:#FFF9C4, color:#F57F17
	  classDef Green stroke-width:1px, stroke-dasharray:none, stroke:#388E3C, fill:#C8E6C9, color:#1B5E20
	  classDef Red stroke-width:1px, stroke-dasharray:none, stroke:#D32F2F, fill:#FFCDD2, color:#B71C1C
	
	  class n11,n12,n13,n14,n15 Aqua
	  class n5,n6,n10 Yellow
	  class n1 Green
	  class n7,n8,n9,n3,n4 Red

Explanation

An Oracle server includes an Oracle Instance and an Oracle Database.

Oracle Instance

PGA (Process Global Area): Handles calculations.

SGA (System Global Area) contains:

• DBC (DB Cache): When modification queries are made, it keeps the original row in a temporary undo file.
  • If another user requests the same information, the DB Cache will provide the result from the undo tablespace (i.e., before the modification). The modification must be “committed” for other users to see the latest update.
  • Two users cannot modify the same row simultaneously (thanks to the DB Cache).
• DBWR (DB Writer): Writes to the appropriate tablespace and updates the cache.
• LGWR (Log Writer): Writes to the REDO log.
• SP (Shared Pool) contains three components:
  • DC (Dictionary Cache): Stores metadata about the database.
  • LC (Library Cache): If someone has already requested something and requests it again, the library cache indicates where the information is located.
  • RC (Result Cache): Stores query results for reuse.

The SGA interacts with:

• SPFILE: A binary file containing authentication information. (Alternatively, there is init.ora, which is a text file.)

• CTRL_File (Control File): The most important file, containing version information, the location of backups on disks, and the locations of database files.

• REDO: (50 MB) A log of the most recent operations performed on the database.

• Background Processes :

  • PMON (Process Monitor): Monitors processes.
  • SMON (System Monitor): Updates the dictionary cache (DC) and tablespace information.
  • RECO