Oracle Database Architecture

Understanding Oracle Database Architecture is essential for every DBA. This post covers the most important interview questions about Oracle’s memory, processes, storage, and new features like Multitenant. These questions will help you build a strong foundation and prepare confidently for your Oracle DBA interviews.

1. What is an Oracle Database and how is it different from other types of databases?

An Oracle Database is a software system that stores and manages large amounts of data securely and efficiently. Unlike simple databases, Oracle supports advanced features like scalability, high availability, and complex transaction management, making it suitable for enterprise-level applications.

2. What is a Database Instance? How does it relate to the physical database?

A Database Instance is the set of Oracle background processes and memory structures that manage the database. The physical database consists of files on disk, while the instance is what accesses and manipulates those files in memory.

3. What are the main memory components of an Oracle Instance? (Explain SGA and PGA)

The main memory areas are:

• SGA (System Global Area): Shared memory used by all database users for caching data and SQL commands.
• PGA (Program Global Area): Memory private to each server process, used for sorting, session information, etc.

4. What is the System Global Area (SGA) and what are its components?

SGA is a shared memory region containing data and control information for one Oracle instance. Its main parts include:

• Database Buffer Cache: Stores data blocks read from datafiles.
• Shared Pool: Caches SQL statements and execution plans.
• Redo Log Buffer: Stores redo entries (transaction logs).
• Large Pool: Optional area for large memory allocations.
• Java Pool: For Java execution in the database.

5. What is the Program Global Area (PGA)?

PGA is memory allocated to a single server process. It holds data like session variables, sort areas, and other private information needed during query execution.

6. What are the key background processes in Oracle? Name and explain their roles (PMON, SMON, DBWR, LGWR, CKPT, ARCn, RECO, MMON, MMNL, CJQ0, SMCO, FBDA, LREG, Dispatcher & Shared Server processes).

• PMON: Cleans up failed user processes and releases resources.
• SMON: Performs instance recovery and cleans temporary segments.
• DBWR: Writes modified data from buffer cache to datafiles.
• LGWR: Writes redo log entries to disk for transaction durability.
• CKPT: Updates datafile headers and control files at checkpoints.
• ARCn: Archives filled redo log files for recovery.
• RECO: Resolves distributed transactions’ failures.
• MMON: Performs manageability tasks like memory advisory.
• MMNL: Coordinates manageability tasks.
• CJQ0: Manages scheduled jobs and tasks.
• SMCO: Manages space usage and segment shrink.
• FBDA: Manages flashback data archive operations.
• LREG: Registers the database instance with the listener.
• Dispatcher (Dnnn): Handles incoming client requests in shared server mode.
• Shared Server (Snnn): Processes client requests when using shared servers.

7. What is the Shared Pool and why is it important?

The Shared Pool caches parsed SQL, execution plans, and dictionary info to speed up query processing and reduce CPU load. It helps reuse SQL statements instead of parsing them repeatedly.

8. What is the Database Buffer Cache?

It stores copies of data blocks read from disk so users can quickly access data without repeated disk reads, improving performance.

9. What is the Large Pool and when is it used?

Large Pool is an optional memory area used for large memory operations like backup and restore, parallel query execution, and Shared Server processes, reducing contention in the Shared Pool.

10. What are Tablespaces? How do they fit into Oracle’s logical database storage?

Tablespaces are logical storage units in Oracle that group related database objects. They map to one or more physical datafiles and help organize data logically.

11. What is the difference between physical and logical database structures?

• Physical structures: Actual files on disk (datafiles, control files, redo logs).
• Logical structures: Database objects like tablespaces, segments, extents, blocks that the database manages.

12. What are datafiles, control files, and redo log files?

• Datafiles: Store the actual user and system data.
• Control files: Keep metadata about database structure and state.
• Redo log files: Record all changes made to the database for recovery.

13. What is the purpose of the Checkpoint process (CKPT)?

CKPT signals data writes to datafiles and updates control files to mark a consistent point, helping reduce recovery time after crashes.

14. What is the Archiver process (ARCn) and why is it important?

ARCn copies filled redo log files to archive storage to protect against data loss and enable point-in-time recovery.

15. What is the Recover process (RECO) and when does it come into play?

RECO resolves failed distributed transactions automatically to ensure consistency across multiple databases.

16. What are the MMON and MMNL processes? What is their function?

• MMON: Performs monitoring and manageability tasks like gathering statistics and memory advisory.
• MMNL: Coordinates and supports MMON activities.

17. What is the role of the Job Queue Coordinator (CJQ0)?

CJQ0 manages scheduled jobs such as backups, statistics gathering, and maintenance tasks.

18. What is the Space Management Coordinator (SMCO)?

SMCO manages space reclamation, like shrinking segments and reclaiming fragmented space.

19. What is Oracle Multitenant Architecture? Explain Container Database (CDB) and Pluggable Database (PDB).

Oracle Multitenant lets you manage many databases (PDBs) within one container database (CDB), simplifying consolidation and management while isolating data.

20. What are Application Containers in Oracle Multitenant?

Application Containers are special containers that hold common application metadata and users shared across multiple PDBs for easier app management.

21. What is the Dispatcher (Dnnn) and Shared Server (Snnn) process?

Dispatchers route client requests to shared servers which process the requests in shared server mode, saving resources for many simultaneous users.

22. What is the Listener Registration Process (LREG)?

LREG registers the database instance with the Oracle Listener so it can accept client connection requests.

23. What is Automatic Diagnostic Repository (ADR) and how does it help DBAs?

ADR is a directory structure that stores diagnostic data like logs, traces, and dumps, making it easier for DBAs to troubleshoot issues.

24. What is Flashback Data Archiver Process (FBDA)?

FBDA manages the flashback archive feature that tracks historical changes to data, allowing queries on past data versions.

25. What are the main Oracle Editions and how do they differ?

Oracle Editions include Standard, Enterprise, and Express. They differ in features, scalability, performance options, and licensing costs.

26. What is the role of Oracle DBA related to architecture and management of these components?

DBAs manage and tune memory structures, monitor background processes, organize storage, ensure availability, backup and recovery, and troubleshoot performance based on a deep understanding of the architecture.