As a DBA who has worked with Oracle for over a decade, I’ve consistently observed a phenomenon: many people, from beginners to developers with several years of experience, have a fuzzy understanding of Oracle’s most fundamental and core concepts—the “Instance” and the “Database”—often equating them. This conceptual confusion prevents a true understanding of Oracle’s startup, operational, and shutdown mechanisms, let alone deep performance diagnostics.

In this article, I don’t want to just list definitions from a textbook. I want to take you on a journey to peel back the layers of the instance and the database, like an onion, to see their distinct entities and how they collaborate in a sophisticated dance. After reading this, you will have a fundamentally new perspective on the Oracle “worldview.”

I. Clarifying the Concepts: What is the Relationship Between an Instance and a Database?

Let’s start by thoroughly clarifying these two concepts. In the world of Oracle:

  • Database: Refers to a collection of physical files stored on disk. It is “quiet” and static. It primarily includes three types of files:
    • Data Files: Where user data, such as tables and indexes, is actually stored.
    • Control Files: The database’s “household register,” recording the database’s physical structure, the locations of data files, and redo log files.
    • Redo Log Files: The “ledger,” recording all changes made to the database, used for recovery.
  • Instance: Refers to a set of data structures in memory and a group of processes running in the background. It is “active” and dynamic, serving as the sole channel to access and manipulate the database files. It mainly consists of two parts:
    • Memory Structures: Primarily the System Global Area (SGA) and the Program Global Area (PGA).
    • Background Processes: Such as DBWn, LGWR, etc., which are the actual “workers.”

To make this relationship clearer, I like to use an analogy: a “restaurant kitchen” versus a “cookbook and ingredient warehouse.”

The Database is like the restaurant’s cookbook (control files) and a huge ingredient warehouse (data files). They sit there quietly and cannot produce dishes on their own.

The Instance is the fully-equipped, brightly lit kitchen, complete with various workstations (SGA memory), the chefs’ personal tools (PGA memory), and a group of busy chefs (background processes).

  • Only when the kitchen (instance) is up and running can the chefs (processes), following the cookbook (control files), fetch ingredients from the warehouse (data files) to cook (data operations), and record every step of the process (redo logs). A single database can be accessed by multiple instances (in a RAC cluster), just as a large ingredient warehouse can supply multiple kitchens simultaneously.

Now, we can visually see the difference with simple SQL queries.

-- Check the currently connected instance information
SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

-- Check the database information associated with the instance
SELECT NAME, OPEN_MODE FROM V$DATABASE;

Typically, you will see the instance status as OPEN and the database open mode as READ WRITE, which means your “kitchen” is open for business and can perform read/write operations on the “ingredient warehouse.”

II. Inside the Instance’s Heart: Memory and Processes

Since the instance is the only entry point to operate the database, it’s necessary to go deeper inside to see how this “kitchen” works.

Memory Structures: The Efficient Collaboration Zone of SGA and PGA

The SGA (System Global Area) is the largest and most central memory area in an instance, shared by all server processes and background processes. You can think of it as the kitchen’s common area.

-- View the main components of the SGA and their sizes (SHOW SGA is more common in SQL*Plus)
SELECT NAME, BYTES/1024/1024 AS "SIZE(MB)" FROM V$SGAINFO;

The most important components include:

  • Database Buffer Cache: This is the largest part of the SGA. When you need to read or modify data, Oracle first reads the relevant data blocks from the data files on disk into this cache. Subsequent operations are performed directly in this memory area, significantly improving performance. This is like a chef bringing the most commonly used ingredients for the day from the walk-in freezer (disk) to the prep station (Buffer Cache) to avoid frequent trips.
  • Shared Pool: Mainly stores information that can be shared among multiple users, such as SQL execution plans and PL/SQL program code. When a user executes an SQL statement, Oracle first checks the shared pool to see if an identical SQL has been executed before. If so, it reuses the existing execution plan, saving the time-consuming parsing process. This is the core of “parse once, execute many.”
  • Redo Log Buffer: A circular buffer in memory used to cache all data change records (Redo Entries). Any changes generated by DML operations (INSERT, UPDATE, DELETE) are quickly written here first. This is like a chef jotting down the steps on a small blackboard before starting to chop vegetables—it’s very fast.

The PGA (Program Global Area), on the other hand, is a private memory area for each server process, used for storing sorting data, session information, etc. It’s like each chef’s own small workstation and toolbox, which other chefs cannot touch.

Background Processes: The “Five Hardest Workers” of the Instance

If the SGA is the workstation, the background processes are the tireless chefs. Although Oracle has dozens of background processes, the following five are the “hardest workers” you must know:

  1. DBWn (Database Writer): Its core duty is to write modified (“dirty”) data blocks from the Buffer Cache to the data files on disk at appropriate times. It’s a “lazy” process, not writing immediately after every modification but in batches to improve I/O efficiency.
  2. LGWR (Log Writer): It’s very “diligent,” responsible for quickly writing change records from the Redo Log Buffer to the redo log files on disk. Data durability primarily relies on it. Once LGWR’s write is successful, the transaction is considered committed, and data will not be lost even if the instance crashes.
  3. CKPT (Checkpoint): A signaler responsible for updating the headers of all data files and control files when a specific event is triggered, recording a “checkpoint” position. This position tells Oracle that all “dirty” blocks before this point have been written to disk by DBWn. This greatly shortens the time required for recovery after a crash.
  4. SMON (System Monitor): Responsible for performing instance recovery at startup (if necessary) and cleaning up unused temporary segments, among other system maintenance tasks.
  5. PMON (Process Monitor): Responsible for cleaning up resources used by a user process when it terminates abnormally, such as rolling back uncommitted transactions and releasing locks.

You can see these hard-working processes with the following query:

-- View core background processes
SELECT NAME, DESCRIPTION 
FROM V$BGPROCESS 
WHERE PADDR IS NOT NULL 
AND NAME IN ('PMON', 'SMON', 'DBW0', 'LGWR', 'CKPT');

III. The Art of Interaction: The Journey of an UPDATE Statement

With the theory covered, let’s trace an actual UPDATE statement to connect the interaction between the instance and the database:

UPDATE employees SET salary = 6000 WHERE employee_id = 100;

  1. Connection and Parsing: Your SQL client connects to the database via the listener, and Oracle assigns a server process to you. The server process first checks the Shared Pool for the execution plan of this UPDATE statement. If not found, it performs a hard parse and caches it.
  2. Reading Data: Based on the execution plan, the server process first looks for the data block containing employee_id = 100 in the Buffer Cache. If it’s not there, it reads the block from the data files on disk (visible via V$DATAFILE) into the Buffer Cache.
  3. Generating Change Records: The modification operation is performed in the PGA, changing the salary to 6000. Simultaneously, a detailed change record (Redo Entry) is generated in the Redo Log Buffer, describing the “before and after” of this change.
  4. Modifying the Data Block: The server process modifies the data block in the Buffer Cache and marks it as a “dirty block.” Note that the data file on disk is not yet changed.
  5. Committing the Transaction (COMMIT): When you execute COMMIT, the LGWR process is awakened and immediately writes all redo records related to the transaction from the Redo Log Buffer to the redo log files on disk (visible via V$LOGFILE). Once this write is successful, the transaction is considered permanent.
  6. Background Writing: At some later point (e.g., a checkpoint occurs or the Buffer Cache runs out of space), the DBWn process will find the “dirty block” in the Buffer Cache and write it back to the corresponding data file on disk, completing the final data persistence.

IV. Seeing the Essence Through the Startup Process: The Secrets of NOMOUNT, MOUNT, and OPEN

Understanding the three stages of the Oracle startup process is the best practice for fully grasping the relationship between the instance and the database.

  1. STARTUP NOMOUNT:
    • What Happens: Oracle creates the instance. This means allocating SGA memory and starting the background processes based on the parameter file (pfile/spfile).
    • State: At this point, only the instance exists—a pure “kitchen” has been built and powered on. However, it doesn’t yet know which “cookbook and ingredient warehouse” (database) it is supposed to serve.
    • Verification: Querying V$INSTANCE will return results, but querying V$DATABASE will result in an error because the control file has not yet been read.
  2. ALTER DATABASE MOUNT:
    • What Happens: The instance finds and opens the control files based on the control_files parameter in the parameter file. By reading the control files, the instance learns the database name, the locations of the data files, and log files.
    • State: The “kitchen” has obtained the “cookbook” (control files) and knows where the ingredient warehouse is, but it has not yet opened the warehouse doors.
    • Verification: Both V$INSTANCE and V$DATABASE will now return results, but the OPEN_MODE of the database is MOUNTED. You can now query V$DATAFILE and V$LOGFILE to see the file lists.
  3. ALTER DATABASE OPEN:
    • What Happens: The instance opens all data files and redo log files and performs consistency checks. If necessary, SMON will perform instance recovery.
    • State: The “kitchen” is officially open for business! It has opened the doors to all ingredient warehouses and can now provide services.
    • Verification: The OPEN_MODE in V$DATABASE changes to READ WRITE. The database is now fully available.

Conclusion

I hope that after this “kitchen tour,” you have a clear understanding of Oracle’s architecture. Please remember these key points:

  • The database is a static collection of physical files; it is the “body.”
  • The instance is a dynamic combination of memory and processes; it is the “function.”
  • We never operate directly on the database; all operations must be performed through the instance, the sole agent.
  • The SGA is the core workspace, and the background processes are the core workforce; together, they form the instance, an efficient “data processing factory.”

With this understanding, you will have a clear operational blueprint in your mind when you troubleshoot database performance issues, analyze wait events, or design backup and recovery strategies in the future.