Oracle Database basics: instance vs database, CDB and PDB

Oracle uses two words that other databases blur together. Getting them straight makes the rest of Oracle administration much easier to follow.

Instance vs database

• The database instance is a set of memory structures and processes. When it starts, Oracle allocates the System Global Area (SGA), a shared memory area, and starts background processes. The instance does the work.
• The database is the set of files on disk: data files, control files and online redo log files. It holds the data.

In a single-instance setup there is one instance per database. With Oracle Real Application Clusters (RAC) several instances on different servers open the same database. An instance mounts only one database at a time. On a host, each instance is identified by its SID, usually set with the ORACLE_SID environment variable, alongside ORACLE_HOME (where the software is installed).

Startup stages

Starting a database goes through three states, which matter for maintenance and recovery:

1. NOMOUNT: the instance is started (parameter file read, SGA allocated, background processes running) but not attached to a database. Used when creating a database or restoring control files.
2. MOUNT: the instance has read the control file and knows the database’s files, but users cannot connect. Used for full restores and for switching on ARCHIVELOG mode.
3. OPEN: data files are open and authorized users can work.

export ORACLE_SID=ORCLCDB
sqlplus / as sysdba

SQL> STARTUP            -- goes through NOMOUNT, MOUNT and OPEN
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT      -- stop at MOUNT for maintenance
SQL> ALTER DATABASE OPEN;

Multitenant: CDB and PDB

Since Oracle Database 12c, a database can be a multitenant container database (CDB). Starting with Oracle Database 21c it is the only supported architecture; the classic non-CDB is desupported. So in 21c and newer releases (and in most new 19c installations) you work with these containers:

• CDB$ROOT, the root: Oracle-supplied metadata and common users. It does not store user data.
• PDB$SEED, the seed: a read-only template used to create new PDBs.
• PDBs, pluggable databases: your application databases. To a client, each PDB looks like a separate database with its own service name, users, tablespaces and data.

All PDBs in a CDB share the CDB’s instance (memory and background processes) and its redo logs. That is the point of the design: many application databases, one set of processes to run and patch. You can unplug a PDB and plug it into another CDB.

Finding out where you are

SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual;
SELECT name, open_mode FROM v$pdbs;

ALTER SESSION SET CONTAINER = orclpdb1;   -- switch, as a common user with SET CONTAINER

When you run sqlplus / as sysdba on the server, you land in CDB$ROOT. Applications should connect straight to their PDB through its service name instead (see the article on connecting to Oracle).

Common users and local users

• A common user exists in the root and in every PDB. Its name must start with the common user prefix, by default C## (for example C##DBA). SYS and SYSTEM are Oracle-supplied common users.
• A local user exists in one PDB only and must not start with C##. Application schemas should be local users created while connected to the PDB.

A frequent beginner error is trying CREATE USER appuser ... while connected to the root; Oracle refuses because the name lacks the common prefix. Switch to the PDB first.

Version names

Oracle Database 19c is the long-term support release found in most data centers. 21c was an innovation release. 23ai followed, and the current documentation set is published for the release now named 26ai. Statements on this page about CDB/PDB apply from 12c on, and the non-CDB desupport applies from 21c on.

Official documentation

• Oracle Database 19c Concepts: Oracle Database instance
• Oracle AI Database Concepts: CDBs and PDBs
• Oracle Database 19c SQL Reference: CREATE USER