Maintenance often requires stopping the database instance. The shutdown mode determines how Oracle handles connected users and unfinished transactions. For a single-instance CDB, an instance-wide shutdown stops access to the root and every PDB.
Oracle DBA Lesson 26A — How Oracle Shuts Down
Sessions and transactions
A session is a user's connection to Oracle. A transaction groups changes into work that the application finishes with COMMIT or ROLLBACK. COMMIT makes the changes permanent. ROLLBACK reverses the uncommitted changes.
A session can stay connected after its transaction ends. An application connection pool keeps connections ready for later requests. That is why waiting for sessions and waiting for transactions are different shutdown behaviors. Oracle transaction concepts.
Choose the required behavior
| Mode | How current work is handled | Practical choice |
|---|---|---|
NORMAL |
Waits for users to disconnect, then completes a clean stop | The application can close its connections in an orderly way |
TRANSACTIONAL |
Lets current transactions finish, disconnects remaining sessions, and completes a clean stop | Current work should finish before maintenance |
IMMEDIATE |
Terminates statements, disconnects users, and rolls back uncommitted changes during shutdown | Active work can be cancelled for the maintenance stop |
ABORT |
Terminates the instance. Automatic instance recovery follows at startup | Emergency termination after an unsuccessful clean shutdown |
Oracle blocks new connections during shutdown. TRANSACTIONAL also blocks new transactions while existing transactions finish. A current transaction ends when its application commits or rolls back. Oracle shutdown behavior.
Specify the mode. These are alternative commands. Choose one. In SQL*Plus, SHUTDOWN by itself selects NORMAL. SQL*Plus command reference.
SHUTDOWN NORMAL
SHUTDOWN TRANSACTIONAL
SHUTDOWN IMMEDIATE
SHUTDOWN ABORTThree maintenance situations
- The application has finished its work and can close all pooled connections: use
NORMAL. - A payment update is still running and should finish: use
TRANSACTIONAL. - Maintenance requires cancelling an unfinished batch update: use
IMMEDIATE. Oracle rolls its uncommitted changes back as part of the stop.
A large rollback takes time. Plan the maintenance window around the actual workload. If the stop runs longer than expected, check sessions, transactions, and the alert log.
Clean shutdown and recovery
NORMAL, TRANSACTIONAL, and IMMEDIATE finish the clean close, dismount, and instance-stop sequence. Oracle saves the required state and closes the files.
After ABORT, Oracle performs automatic instance recovery the next time the database is opened. Redo reapplies recorded changes. Undo reverses uncommitted work. Recovery makes the database consistent. Transaction rollback can continue after the database opens, so allow time for that work during restart and later access. Oracle instance shutdown and recovery.
A shutdown and restart example
Practice only on a disposable, unmanaged single-instance CDB during its agreed outage. Confirm the database identity, a dedicated administrative connection, and CDB$ROOT. Use the designated SYSDBA account. Record which PDBs and services were open beforehand, and keep the reset procedure ready. An Oracle Restart-managed database uses its configured management procedure.
For an authorized local Linux administrative connection:
sqlplus / as sysdbaThe operating-system account needs the administrative membership for this instance, and the Oracle environment must point at that instance. Oracle administration workflow.
Capture the starting state:
SHOW CON_NAME
SELECT instance_name, status FROM v$instance;
SELECT name, open_mode FROM v$pdbs ORDER BY con_id;Then issue:
SHUTDOWN IMMEDIATEThe clean-stop messages are:
Database closed.
Database dismounted.
Oracle instance shut down.They report close, dismount, and instance termination in that order. Issued from CDB$ROOT as SYSDBA, SHUTDOWN IMMEDIATE stops the instance and all of its PDBs. SQL*Plus shutdown messages.
Restart and check availability:
STARTUP
SELECT status FROM v$instance;
SELECT name, open_mode FROM v$pdbs ORDER BY con_id;V$INSTANCE.STATUS is the instance state. OPEN is the state for normal database access. V$PDBS lists each PDB open mode. Compare those modes with the starting state, restore the required PDBs through the approved procedure, and test the application connection. V$INSTANCE. V$PDBS.
Practice
Name the mode for each maintenance situation before you run a command. On the disposable CDB, complete the IMMEDIATE stop and restart, keep the messages and the two checks, and restore the original availability.
No comments:
Post a Comment