Every DBA eventually learns this lesson: when something breaks at 2 AM, commands are useful, but architecture is what saves you. If you understand how the instance, database files, containers, memory, listener, and services fit together, troubleshooting becomes much less scary.
This is chapter 2 of the Oracle 26ai DBA series. The first post gave us the feature map. Now we slow down and build the mental model. No fancy words unless they help. Just the pieces you need to understand before patching, upgrading, tuning, or fixing a production issue.
The big picture
An Oracle database environment has several moving parts. At a high level, think about it like this:
Users / Applications
|
v
Oracle Listener
|
v
Database Service
|
v
Oracle Instance
+-----------------------------+
| SGA memory |
| Background processes |
+-----------------------------+
|
v
Oracle Database Files
+-----------------------------+
| Data files |
| Control files |
| Online redo logs |
| Temp files |
| Archived redo logs |
+-----------------------------+
|
v
CDB and PDB containers
The most important split is this:
- The instance is memory plus background processes.
- The database is the physical files on disk or storage.
That sounds basic, but it explains a lot. A database can sit on disk while everything is shut down. An instance only exists while Oracle is running. When startup fails, this distinction helps you ask the right question: did the instance fail to start, did the database fail to mount, or did the files fail to open?
Instance vs database
A common beginner mistake is to use "instance" and "database" as the same word. They are related, but not the same.
Oracle instance
The instance contains:
- SGA memory
- Background processes
- Server processes
- Session state
- Locks and runtime structures
Oracle database
The database contains:
- Data files
- Control files
- Redo log files
- Temp files
- Undo data
- Archived redo logs
SQL*Plus check:
SELECT instance_name,
host_name,
status,
database_status
FROM v$instance;
SQL*Plus style output:
SQL> SELECT instance_name, host_name, status, database_status
2 FROM v$instance;
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS
------------- ------------- ------- ---------------
orcl dbserver01 OPEN ACTIVE
CDB and PDB architecture
Modern Oracle databases use the multitenant architecture.
A CDB is a container database. A PDB is a pluggable database. Applications usually connect to a PDB, not directly to the root container.
Important containers:
- CDB$ROOT: root container, stores common Oracle metadata.
- PDB$SEED: template used to create new PDBs.
- Application PDBs: where application schemas and data live.
Architecture view:
CDB: ORCLCDB
|
+-- CDB$ROOT
|
+-- PDB$SEED
|
+-- SALES_PDB
|
+-- HR_PDB
|
+-- APP_PDB
SQL*Plus checks:
SHOW CON_NAME
SELECT con_id, name, open_mode
FROM v$pdbs
ORDER BY con_id;
SQL*Plus style output:
SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
SQL> SELECT con_id, name, open_mode
2 FROM v$pdbs
3 ORDER BY con_id;
CON_ID NAME OPEN_MODE
------ --------- ----------
2 PDB$SEED READ ONLY
3 APP_PDB READ WRITE
DBA rule: always know which container you are connected to before running administration commands.
Switching containers
If you connect as a common administrative user, you can switch containers.
ALTER SESSION SET CONTAINER = APP_PDB;
SHOW CON_NAME
SQL*Plus style output:
SQL> ALTER SESSION SET CONTAINER = APP_PDB;
Session altered.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
APP_PDB
This matters because a command run in the wrong container can create users, objects, or grants in the wrong place.
Memory architecture: SGA and PGA
Oracle memory has two major areas:
- SGA: shared memory used by the instance.
- PGA: private memory used by server processes.
SGA components
The SGA commonly includes:
- Database buffer cache
- Shared pool
- Large pool
- Java pool
- Streams pool
- Redo log buffer
- In-Memory area, if enabled
PGA usage
The PGA is used for:
- Sorts
- Hash joins
- Session memory
- Private SQL areas
- Work areas
SQL*Plus checks:
SHOW PARAMETER sga
SHOW PARAMETER pga
SELECT name, value
FROM v$sgainfo
ORDER BY name;
SQL*Plus style output:
SQL> SHOW PARAMETER sga
NAME TYPE VALUE
------------------------------------ ----------- ----------
sga_target big integer 4G
sga_max_size big integer 4G
SQL> SHOW PARAMETER pga
NAME TYPE VALUE
------------------------------------ ----------- ----------
pga_aggregate_target big integer 1G
DBA takeaway: when performance is slow, memory is one of the first areas to inspect, but it should not be tuned blindly. Always connect memory symptoms to wait events, SQL plans, and workload behavior.
Background processes
Oracle background processes keep the database running. Some important ones are:
- DBWn: writes dirty buffers from memory to data files.
- LGWR: writes redo entries to online redo logs.
- CKPT: signals checkpoint activity.
- SMON: system monitor, handles instance recovery tasks.
- PMON: process monitor, cleans up failed sessions.
- MMON: manageability monitor, collects AWR-related statistics.
- ARCn: archives redo logs when the database is in ARCHIVELOG mode.
SQL*Plus check:
SELECT pname, description
FROM v$process
WHERE pname IS NOT NULL
ORDER BY pname;
SQL*Plus style output:
PNAME DESCRIPTION
----- -----------------------------------------
ARC0 Archival Process 0
CKPT checkpoint
DBW0 db writer process 0
LGWR Redo etc.
MMON Manageability Monitor Process
PMON process cleanup
SMON System Monitor Process
Database files
Oracle stores data and recovery information in several file types.
Data files
Data files store table and index segments.
SELECT file_id,
tablespace_name,
file_name,
bytes/1024/1024 AS size_mb
FROM dba_data_files
ORDER BY file_id;
Control files
Control files store database structure metadata, checkpoint information, and backup metadata pointers.
SELECT name
FROM v$controlfile;
Online redo logs
Redo logs record changes. They are essential for recovery.
SELECT group#, thread#, bytes/1024/1024 AS size_mb, status
FROM v$log
ORDER BY group#;
SQL*Plus style output:
GROUP# THREAD# SIZE_MB STATUS
------ ------- ------- --------
1 1 200 INACTIVE
2 1 200 CURRENT
3 1 200 INACTIVE
DBA rule: never treat redo logs and control files casually. They are central to recovery.
Listener and services
The listener receives client connection requests. A service tells clients which database workload they are connecting to.
A single database can expose multiple services:
APP_RW_SERVICE -> read/write application traffic
APP_RO_SERVICE -> read-only reporting traffic
BATCH_SERVICE -> batch jobs
MAINT_SERVICE -> DBA maintenance
Useful operating system checks:
$ lsnrctl status
$ lsnrctl services
$ tnsping APP_RW_SERVICE
Useful SQL checks:
SELECT name, network_name, pdb
FROM cdb_services
ORDER BY name;
SQL*Plus style output:
NAME NETWORK_NAME PDB
--------------- ----------------- --------
app_rw_service app_rw_service APP_PDB
app_ro_service app_ro_service APP_PDB
DBA takeaway: services are not just connection labels. They are operational tools for workload routing, high availability, draining, and troubleshooting.
Sessions and server processes
When a user connects, Oracle creates or assigns a server process. That process works on behalf of the session.
Useful session check:
SELECT sid,
serial#,
username,
status,
service_name,
machine,
program
FROM v$session
WHERE username IS NOT NULL
ORDER BY logon_time DESC;
SQL*Plus style output:
SID SERIAL# USERNAME STATUS SERVICE_NAME MACHINE PROGRAM
--- ------- -------- ------ -------------- ----------- ------------
77 39201 APPUSER ACTIVE APP_RW_SERVICE appserver01 JDBC Thin
81 11720 SYS ACTIVE SYS$USERS dbserver01 sqlplus
This query is one of the first places DBAs look during troubleshooting.
Startup and shutdown flow
Oracle startup usually moves through these stages:
SHUTDOWN
|
v
NOMOUNT -> instance started, parameter file read
|
v
MOUNT -> control files opened
|
v
OPEN -> data files and redo logs opened
SQL*Plus example:
SQL> STARTUP NOMOUNT;
ORACLE instance started.
SQL> ALTER DATABASE MOUNT;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
Shutdown example:
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
DBA takeaway: understanding startup stages helps diagnose parameter file issues, control file issues, media recovery needs, and data file problems.
Common architecture mistakes
Mistake 1: Running commands in the wrong container
Always check:
SHOW CON_NAME
Mistake 2: Confusing listener status with database status
The listener can be running while the database is down. The database can be open while a service is not registered correctly.
Check both:
$ lsnrctl status
SELECT status FROM v$instance;
SELECT name, open_mode FROM v$pdbs;
Mistake 3: Ignoring services
Do not connect everything through one generic service. Use services to separate application, reporting, batch, and maintenance workloads.
Mistake 4: Tuning memory without evidence
Do not increase SGA or PGA just because someone says the database is slow. First check wait events, SQL plans, memory advisors, and workload changes.
Daily DBA architecture checklist
Use this checklist when you log in to a database for the first time:
SELECT banner_full FROM v$version;
SELECT instance_name, host_name, status
FROM v$instance;
SHOW CON_NAME
SELECT name, open_mode
FROM v$pdbs
ORDER BY name;
SELECT name, network_name, pdb
FROM cdb_services
ORDER BY name;
SELECT group#, status, bytes/1024/1024 AS size_mb
FROM v$log
ORDER BY group#;
SELECT tablespace_name, status, contents
FROM dba_tablespaces
ORDER BY tablespace_name;
Final thought
Oracle 26ai brings new AI and developer features, but the DBA foundation has not disappeared. It is still instance, memory, processes, files, containers, listener, and services. The newer the database gets, the more valuable the fundamentals become.
If you understand these pieces, you can troubleshoot faster and make better decisions during patching, upgrades, migrations, and production incidents.
In the next chapter, we will cover tablespace and space management: permanent tablespaces, temporary tablespaces, undo, data files, autoextend, segment growth, and practical space monitoring queries.