Oracle DBA Revision 1 — Lessons 1 to 5

Table of contents

1. Instance and database
2. Connections and sessions
3. Single instance and RAC
4. Shared infrastructure
5. Container structure
6. Session context in SQL*Plus

01

Instance and database

A running instance provides access to the database. The database files hold persistent information on disk.

Instance

The system global area (SGA) and background processes. The SGA is shared memory used by Oracle processes.

Data files

Store database data, including the blocks that hold tables and indexes.

Control files

Record essential physical database structure, including file information.

Online redo logs

Record changes needed for recovery.

InstanceSGA + background processes

Buffer cacheRedo log bufferShared pool

These three memory areas are in the SGA.

↓ accesses

Database files on disk

Data files

Control files

Online redo logs

The instance uses memory and processes to work with the database files.

Example

A query needs a block from a table. When Oracle reads that block into the database buffer cache, it places a copy in the SGA. The block also remains in the data file on disk.

Note

The database buffer cache, redo log buffer and shared pool belong to the SGA. Private process work areas belong to the program global area (PGA). Physical RAM provides the memory for these areas.

02

Connections and sessions

A connection carries requests between a client and the database server. A session holds the user’s identity and transaction state, and a server process handles the requests.

Client applicationSends a SQL request

→

Server processPerforms database work

Connection: communication path established through a database service.
Session: the user's current database context, including identity and transaction state.

The service identifies the database workload to connect to. A server process handles the request.

SQL request

1. The application connects using a configured database service.
2. After successful authentication, a session provides the user context.
3. The client submits a SQL request through its connection.
4. A server process performs the requested database work and returns a result.

Note

A session can handle many SQL statements. Finishing one query does not end the session; its user context remains available for later requests.

Does one session always mean one server process?

A dedicated server configuration uses a server process for a client connection. Shared server configurations allow server processes to serve multiple sessions. Each session retains its own context even when server processes are shared.

03

Single instance and RAC

A single-instance database uses one instance. Oracle Real Application Clusters (RAC) uses multiple coordinated instances to access the same database. Each RAC instance has its own SGA and background processes.

Single-instance configuration

One instanceSGA + background processes

↓ accesses

One databaseDatabase files

Oracle Real Application Clusters

Instance 1Own SGA and processes

Instance 2Own SGA and processes

↓ coordinated access

One shared databaseThe same database files

Each RAC instance has its own memory and processes; the database files are shared.

Example

A server hosts two independent databases, each with its own instance and database files. These remain two separate databases even though they use the same server.

Note

A surviving RAC instance can provide database access when the required services and shared infrastructure remain available. An existing connection to the failed instance may be interrupted; seamless application continuity requires appropriate configuration.

Watch Oracle DBA Revision 1 — Lessons 1 to 5 on YouTube

04

Shared infrastructure

Pluggable databases can use common instance resources while keeping application data logically separate.

Shared instance resourcesSGA and background processes

↓ serve both pluggable databases

ORDERS_PDBOrder application's data

BILLING_PDBBilling application's data

These standard PDBs belong to one CDB. Each maintains its own application-data boundary.

Example

A large billing job runs in BILLING_PDB while customers place orders in ORDERS_PDB. Both workloads use shared computing resources, so the billing job can affect order response times. Their application tables remain separate. Resource controls can allocate capacity between PDBs.

05

Container structure

A container database (CDB) contains the root, the seed and user-created pluggable databases (PDBs). Each session works in a current container.

Container database (CDB)

RootCDB$ROOTSystem metadata

SeedPDB$SEEDTemplate for new PDBs

User PDBORDERS_PDBApplication data

The seed remains available when used to create another PDB. A CDB can contain multiple user PDBs.

The root supports the CDB's administration. The supplied seed is a template, and a user-created PDB can hold application schemas and data. The current container is the container in which a particular session is working.

Example

A new TEST_PDB is created using PDB$SEED as its template. TEST_PDB becomes a separate PDB with its own application data. PDB$SEED remains available as the template for later PDBs.

↳

Session context in SQL*Plus

Use SQL*Plus to identify your user and current container.

Use an available training CDB and an account permitted to connect to its service. Run these commands in an authenticated SQL*Plus session:

SHOW USER
SHOW CON_NAME
SHOW CON_ID

SHOW USER

Displays the connected username.

SHOW CON_NAME

Displays the current container name.

SHOW CON_ID

Displays the current container's numeric identifier.

These are SQL*Plus client commands. Each reports session information without changing the current container.

Example

If a session returns the following values, the connected user is APP_READER and its current container is ORDERS_PDB:

USER is "APP_READER"

CON_NAME
------------------------------
ORDERS_PDB

Object access also depends on the user’s privileges.

Exercises

Where does each item belong?

Choose the most precise category for each item.

Order the steps in a client request

Assign positions 1–4. Start with a client that has not connected yet.

Questions

Choose one answer for each question.

Answers are saved in this browser.

References

Oracle Database 19c documentation for the relationships and commands used in this revision.

1. Oracle Database Instance — instance components, configurations and shutdown.
2. Memory Architecture — SGA, PGA and session memory.
3. Process Architecture — clients, server processes and connections.
4. Physical Storage Structures — data files, control files and online redo logs.
5. Overview of the Multitenant Architecture — PDB boundaries, services, shared resources, root and seed.
6. SQL*Plus SHOW — USER, CON_NAME and CON_ID.

https://www.apps-dba.com/https://www.youtube.com/@EnhanceSkillsDaily