Lesson 1

An Oracle database deployment has two halves that work together:

• Instance - memory structures and background processes that run the database.
• Database - the physical files (datafiles, control files, redo logs, tempfiles, and archived logs) that store data and metadata.

Modern releases also support multitenant architecture:

• Container Database (CDB) - the root database that owns system metadata and common users.
• Pluggable Databases (PDBs) - user databases that plug into a CDB. One CDB can host many PDBs; a single instance can service the CDB and all open PDBs.

The instance is primarily the SGA (buffer cache, shared pool, redo log buffer, large pool, etc.) plus background processes such as DBWn (writes dirty buffers), LGWR (writes redo), CKPT (signals checkpoints), SMON/PMON (system/process monitoring), and optionally ARCn (archives redo).

At startup the instance proceeds through NOMOUNT → MOUNT → OPEN states to read parameters, mount control files, and open datafiles (and PDBs in a CDB).

Tablespaces are logical storage containers. They map to one or more datafiles that live on disk. Objects (tables, indexes, etc.) are created in tablespaces and their segments may span multiple datafiles within that tablespace.

Other essential files

• Control files - small binary files that record database structure (names/paths of datafiles and redo logs, checkpoints, SCNs). Keep multiplexed copies.
• Online redo logs - circular log groups that record every change for recovery. When full, they switch to the next group; with archiving enabled, filled logs are copied to archived redo logs.
• Tempfiles - back temporary tablespaces for sorts/hash joins; recreated as needed.
• UNDO tablespace - stores undo records for read consistency and transaction rollback.

-- Database and instance basics
SELECT name FROM v$database;
SELECT instance_name, status FROM v$instance;

-- Multitenant status
SELECT cdb, open_mode FROM v$database;
SELECT con_id, name, open_mode FROM v$pdbs ORDER BY con_id;

-- Tablespaces and datafiles (sample)
SELECT tablespace_name, file_name, bytes/1024/1024 AS mb
FROM   dba_data_files
ORDER  BY tablespace_name, file_name;

-- Control files
SHOW PARAMETER control_files

-- Redo log groups and members
SELECT l.group#, l.bytes/1024/1024 AS mb, l.status, f.member
FROM   v$log l
JOIN   v$logfile f ON f.group# = l.group#
ORDER  BY l.group#, f.member;

On Linux/UNIX, an Oracle home contains a dbs/ directory for initialization files. After a software-only install, it may be empty until you create an instance and database.

ls -l

Create parameter files (SPFILE/PFILE), start the instance, create the database, then add tablespaces and users.

• Instance vs database: the instance is compute+memory; the database is the files. You can restart the instance without recreating files.
• CDB/PDB confusion: root owns system metadata; PDBs hold user schemas. Opening/closing PDBs doesn’t require restarting the instance.
• Redo vs archive: online redo logs are reused; archived logs are copies saved for recovery and backups.

1. Identify the major SGA components and background processes.
2. Explain the relationship among tablespaces, datafiles, and objects.
3. Describe the roles of control files, online redo logs, archived logs, tempfiles, and undo.
4. Run basic queries to verify multitenant status and list storage files.