Enhance Skills Daily

October 02, 2026

Oracle DBA Lesson 26 Part 1 — Predict What a Shutdown Will Wait For

A shutdown mode determines which existing activity can finish, how unfinished transactions are resolved, and whether reopening needs instance recovery. During maintenance, choose the mode from the required transaction outcome and the connections still present.

Oracle DBA Lesson 26 Part 1 — Predict What a Shutdown Will Wait For

A connection and a transaction are separate conditions

An application connection pool may keep a database session connected while it has no unfinished transaction. Another session can have an update that still needs a commit or a rollback. Finishing that update's transaction can leave both sessions connected.

For example, a transaction changes a value from 40 to 55. Before commit, it is unfinished. A commit makes 55 the committed value. A full rollback reverses this transaction's update and restores 40. See Transactions.

Compare the four modes

ModeExisting activity and unfinished workCompleted stop and next opening
NORMAL Blocks new connections and waits for currently connected users to disconnect. This is the SQL*Plus SHUTDOWN default. Completes a consistent shutdown. Instance recovery is unnecessary at the next opening.
TRANSACTIONAL Blocks new connections and transactions, lets existing transactions finish, then disconnects remaining clients. An attempt to start a new transaction can disconnect that client. Completes a consistent shutdown. Instance recovery is unnecessary at the next opening.
IMMEDIATE Terminates executing SQL, disconnects clients, and rolls back uncommitted transactions. Rollback must finish as part of the shutdown. Completes a consistent shutdown. Instance recovery is unnecessary at the next opening.
ABORT Terminates the instance, disconnecting clients without performing shutdown rollback or a closing checkpoint. Reopening after this inconsistent stop requires automatic instance recovery.

NORMAL: a connection can keep the shutdown waiting

If the update commits but the pool connection stays connected, NORMAL still waits. Coordinate the application's disconnection when you choose this mode. In SQL*Plus, SHUTDOWN without a mode means SHUTDOWN NORMAL. Oracle Restart's configured stop option is a separate setting.

TRANSACTIONAL: allow active work to finish

TRANSACTIONAL lets this transaction reach its own commit or rollback. After all transactions finish, Oracle disconnects clients that remain and continues with a clean shutdown. A long unfinished transaction can extend the wait.

Choose this behavior when existing transactions should complete under application control and new work must stop. The LOCAL option, and multi-instance or distributed behavior, need their own environment-specific review.

IMMEDIATE: Oracle resolves uncommitted work before stopping

IMMEDIATE terminates executing statements and disconnects clients. Oracle uses undo information to reverse unfinished work: the uncommitted 40-to-55 update returns to 40. A large transaction can require substantial rollback. Cleanup and file writes also take time, so measure the actual work rather than assuming an instant completion.

For NORMAL, TRANSACTIONAL, and IMMEDIATE, consistent shutdown includes a closing checkpoint. Changed blocks in the database buffer cache in the SGA are written to physical data files on disk. Oracle closes the online files, dismounts the database, and terminates the instance. The consistent stopping point supports the next opening without crash instance recovery.

ABORT: recovery follows at the next opening

ABORT stops the instance without shutdown rollback or a closing checkpoint. With the required database files intact, Oracle automatically recovers on the next opening. It reapplies necessary records from online redo log files on disk to data files on disk, including the redo needed to reconstruct undo, then uses undo to remove uncommitted changes. Committed work is preserved.

The recovery sequence explains why a faster stop can add work to reopening. Transaction rollback can continue after opening or occur on demand. The recovery sequence does not promise that all rollback finishes before every new connection. Instance recovery assumes the required files are intact. Missing or damaged files may require other recovery procedures. Reserve ABORT for an abrupt stop that the situation actually requires, such as persistent failure of a clean shutdown. See Oracle Database Instance and Shutting Down a Database.

Interpret the command in the correct scope

An actual shutdown interrupts database availability. This requires the intended database and Oracle home and SID, a SYSDBA connection in CDB$ROOT, and an unmanaged single-instance database. A root shutdown affects the CDB and its PDBs. A SHUTDOWN issued inside a PDB closes that PDB while the shared instance continues. Oracle Restart-managed databases use the documented SRVCTL procedure. See SHUTDOWN.

In the root administration session, capture the starting state:

SHOW USER
SHOW CON_NAME
SELECT instance_name, status FROM v$instance;
SELECT name, open_mode FROM v$database;
SELECT name, open_mode FROM v$pdbs ORDER BY con_id;
SHUTDOWN IMMEDIATE

This is a SQL*Plus command. A successful complete stop typically reports:

Database closed.
Database dismounted.
ORACLE instance shut down.

These are expected message stages. Close ends ordinary database access and closes the online data and redo files. Dismount ends the instance's association with the control file. Instance termination ends its background processes and releases the SGA. The initiating session gets control back once shutdown completes. Investigate a wait from the mode's documented behavior and the actual diagnostics.

After the maintenance, in the administration session:

STARTUP
SELECT status FROM v$instance;
SELECT name, open_mode FROM v$pdbs ORDER BY con_id;

STARTUP starts the instance, mounts, and opens the CDB in this normal scenario. V$INSTANCE.STATUS describes the current instance. OPEN indicates the open state. V$PDBS.NAME and OPEN_MODE identify each PDB and its actual mode, such as MOUNTED, READ ONLY, or READ WRITE. Compare the results with the starting record and the required availability. See V$INSTANCE and V$PDBS.

Restore originally available user PDBs to their required modes using the documented PDB procedure, and verify the application service connection. PDB open and saved-state commands are covered in the next chapter. Keep the seed's intended state.

Handling a wait

For user-disconnect or transaction-completion waits, Oracle 19c documents a one-hour event timeout and ORA-01013 cancellation. This is a limit on the wait conditions, not a two-minute completion promise. Oracle recommends allowing shutdown to complete rather than interrupting it. After ORA-01013, the instance is considered unpredictable. Continue the shutdown using the documented procedure. Persistent failures can require ABORT followed by restart. Preserve diagnostics and follow the operational procedure instead of repeated forced restarts or host power-off. See Shutting Down a Database.

Recap

NORMAL waits for currently connected users to disconnect. An idle pooled connection still counts after its transaction commits. TRANSACTIONAL blocks new connections and transactions, lets existing transactions finish, and then disconnects remaining clients. IMMEDIATE terminates executing SQL, disconnects clients, and rolls back uncommitted work before the stop completes. A large rollback can take time. Those three modes complete a consistent shutdown, including a closing checkpoint that writes changed blocks from the database buffer cache in the SGA to the physical data files on disk, so the next opening does not need crash instance recovery. ABORT terminates the instance without shutdown rollback or a closing checkpoint. With the required files intact, the next opening performs automatic instance recovery: it reapplies needed online redo, including the redo used to reconstruct undo, and then removes uncommitted changes. Committed work is preserved.

After STARTUP, compare V$INSTANCE.STATUS and the PDB open modes with that record, restore the required PDB availability, and verify the application service before handing access back. A disconnect or transaction wait has a documented one-hour timeout. After ORA-01013, the instance is unpredictable. Continue with the documented procedure. Persistent failure of a clean shutdown can require ABORT followed by restart. Do not use repeated forced restarts or host power-off.

Quiz

1. An update has committed, but an idle pooled connection remains connected. What does NORMAL wait for?

2. Which mode allows existing transactions to finish and then disconnects remaining clients?

3. Will IMMEDIATE finish instantly when a large transaction needs rollback?

4. With required files intact, what follows a root SHUTDOWN ABORT at the next database opening?

5. What should follow successful CDB STARTUP before access is handed back?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...