Oracle Database Components

Instance:

The Oracle database system is comprised of several essential components within an instance, each playing a crucial role in managing and processing data.

SGA (System Global Area):

• Shared Pool:

1. Library Cache: The Library Cache stores all executable code called by any session, whether SQL or PL/SQL. PL/SQL reads stored procedures from the Dictionary Cache.
2. Dictionary Cache: This cache maintains system metadata brought from data files and parses it into the Library Cache. All queries are first validated in the Dictionary Cache to check for the availability of tables and columns.

• DB Buffer Cache: This cache reads blocks from data files, brings values into memory, processes queries, and returns the results.
• Large Pool: Used in shared server processes, backups (RMAN), and parallel processing.
• Java Pool: Utilized for stored Java procedures.
• Stream Pool: Employed in DB replication.
• Log Buffer: All changes, both old and new, are kept and later written to redo log files.

Background Process:

• SMON (System Monitor): Monitors instance health and aids in recovery.
• PMON (Process Monitor): Monitors process health and informs the listener for authentication.
• DBWn (Database Writer): Writes data from the DB buffer into data files.
• CHKP (Checkpoint): Synchronizes buffer cache with data files and log buffer with redo log file.
• LGWn (Log Writer): Writes all new changes from the buffer cache to redo log files.

Database:

• Block Size: The block size is set at provision time.
• Data Files: Data files are formatted based on the block size and include.

1. System Data Files: Contain system-related metadata and user credential information.
2. User Data Files: Part of user-defined Tablespaces.
3. UNDO Files: Used to roll back data.
4. Temp Files: Work area for performing sort or join operations.

• Control Files: Instance reads this file on startup, and it contains the DB names, DB structure, and file locations.
• Redo Log Files: These files contain a chronological list of changes made to your data and are stored sequentially, aiding in recovery.
• Parameter Files: Block size is defined in the block_size parameter, and these files contain a list of Instance and DB parameters.pfile: A text file.spfile: A binary file manipulated by Oracle DB.
• Password Files: Mandatory for starting up the DB and used to authenticate superusers who can initiate the DB.