Managing the Oracle Instance
Scope
This domain tests startup and shutdown stages, parameter scope, alert-log and ADR use, and instance recovery. Questions often describe a failed startup or a parameter that disappears after restart.
Core rules
- Startup stages: NOMOUNT starts the instance and reads the parameter file; MOUNT opens the control file; OPEN opens datafiles and online redo logs.
- Shutdown NORMAL waits for sessions; TRANSACTIONAL waits for active transactions; IMMEDIATE rolls back transactions and disconnects sessions; ABORT terminates immediately and requires instance recovery at next startup.
- ALTER SYSTEM SET ... SCOPE=MEMORY changes only the running instance; SCOPE=SPFILE persists for restart; SCOPE=BOTH does both when the parameter is dynamic. Static parameters require SCOPE=SPFILE and a restart.
- V$PARAMETER shows current values; V$SPPARAMETER shows spfile contents; V$INSTANCE shows status and startup time.
- The alert log and trace files live under the ADR. ADRCI views and packages incidents.
Worked example
A DBA runs ALTER SYSTEM SET processes=300 SCOPE=SPFILE; then SHUTDOWN IMMEDIATE and STARTUP. PROCESSES is static, so SCOPE=MEMORY would fail or have no effect on the running instance. After startup, V$PARAMETER confirms 300, and V$SPPARAMETER confirms the stored value.
Traps
SCOPE=MEMORY is lost at restart. SHUTDOWN ABORT is not a clean shutdown. The alert log is not the same as a user trace file. A spfile is binary and must not be edited by hand.