Oracle Database Administration I common mistakes
Oracle Database Administration I · Understand the traps and how to avoid them.
Assuming that shutting down an instance with SHUTDOWN IMMEDIATE also removes the need to recover, and answering questions about instance crash recovery as if committed data could be lost.
Candidates conflate instance failure with media failure. Because committed transactions are protected by redo and the instance can be recovered automatically at startup, a crash does not lose committed data; only uncommitted work is rolled back. The confusion stems from treating the buffer cache as the source of truth.
What to do instead
Memorize the rule: instance failure is recovered automatically using online redo and undo; media failure requires restoring datafiles and applying redo and archived logs. Practice explaining the difference between instance recovery, crash recovery, and media recovery in one sentence each, and identify which structures (control file, redo log, undo) are needed for each.
Treating the SYSTEM and SYSAUX tablespaces as ordinary user tablespaces, or forgetting that a locally managed tablespace with AUTOALLOCATE versus UNIFORM extent allocation changes how space is assigned.
These are frequently tested storage facts but are easy to skim. Candidates also mix up dictionary-managed and locally managed tablespaces, and forget that the default permanent tablespace is controlled by DEFAULT TABLESPACE in CREATE/ALTER DATABASE rather than by the user's quota.
What to do instead
Build a one-page reference: SYSTEM holds the data dictionary; SYSAUX holds auxiliary components such as AWR and EM repository; UNDO and TEMP are special-purpose; permanent user data should live in a non-SYSTEM tablespace. For each tablespace type, note the extent management options and the relevant data dictionary views (DBA_TABLESPACES, DBA_DATA_FILES, DBA_FREE_SPACE).
Granting privileges to a role and expecting those privileges to work inside a definer's-rights stored procedure, or forgetting that system privileges granted through a role are not active in named PL/SQL blocks.
Role-based privileges are disabled inside definer's-rights PL/SQL units. Candidates who only test grants interactively see the privilege work and assume it applies everywhere. This is a classic exam trap that rewards understanding of privilege activation rather than rote memorization.
What to do instead
State the rule explicitly: roles are enabled for SQL and anonymous blocks but not for definer's-rights stored units; grant the privilege directly to the owner or use invoker's rights. When reviewing a scenario, ask whether the object is a stored unit and whether rights are definer or invoker before deciding whether a role grant is sufficient.
Using STARTUP FORCE or SHUTDOWN ABORT as the default answer for every startup or shutdown scenario, and ignoring the different states (NOMOUNT, MOUNT, OPEN) and what is accessible in each.
Candidates memorize command names but not the sequence of stages. They also forget that NOMOUNT reads the parameter file and starts the instance, MOUNT reads the control file, and OPEN makes datafiles available to users. Without that ladder, they cannot map a symptom to the correct stage.
What to do instead
Draw the startup ladder and label what each stage reads and what operations are possible: NOMOUNT for creating a database or recreating a control file; MOUNT for renaming datafiles, enabling ARCHIVELOG, or performing full database recovery; OPEN for normal use. For shutdown, match each mode (NORMAL, TRANSACTIONAL, IMMEDIATE, ABORT) to its behavior for sessions, transactions, and checkpointing.
Assuming that dropping a column or table immediately frees space to the tablespace, or that a TRUNCATE can be rolled back like a DELETE.
High-water-mark behavior and DDL semantics are subtle. A DELETE does not lower the high-water mark, TRUNCATE is DDL and commits, and DROP moves segments to the recycle bin unless PURGE is specified. Candidates who only know row-level behavior misjudge space reclamation and recoverability.
What to do instead
Memorize the comparison: DELETE is DML, generates undo, can be rolled back, and leaves the high-water mark; TRUNCATE is DDL, cannot be rolled back, resets the high-water mark, and is much faster; DROP with PURGE bypasses the recycle bin. For space questions, distinguish logical deletion from physical reclamation and mention segment shrink or moving the table as reclamation options.
Confusing undo retention with undo tablespace size, or answering that increasing UNDO_RETENTION alone guarantees a longer retention period without enough undo space.
Undo retention is a target, not a guarantee. With automatic undo management, Oracle tunes retention based on undo tablespace size and workload; if the tablespace is too small, unexpired undo can be overwritten. Candidates who only adjust the parameter miss the space dependency.
What to do instead
Explain the relationship: UNDO_RETENTION sets the desired retention, but the undo tablespace must be large enough to hold the required undo; with RETENTION GUARANTEE, the database protects unexpired undo at the risk of failing transactions. Practice reading DBA_UNDO_EXTENTS status values (ACTIVE, UNEXPIRED, EXPIRED) and V$UNDOSTAT to diagnose retention problems.
Treating Flashback Query, Flashback Table, Flashback Drop, and Flashback Database as interchangeable, and choosing the wrong one for a given recovery scenario.
All four share the word Flashback, so candidates group them by name rather than by mechanism. Flashback Database rewinds the whole database using flashback logs; Flashback Table and Query use undo; Flashback Drop uses the recycle bin. The required prerequisites and the scope of each differ sharply.
What to do instead
Create a comparison table with columns for scope, mechanism, prerequisites, and whether the database must be mounted. For each scenario, identify the smallest unit of recovery needed and select the matching feature. Note that Flashback Database requires ARCHIVELOG mode and flashback logs, while Flashback Query requires only undo and sufficient retention.
Assuming a listener must be running on the same host as the database, or that a client can connect by SID when the service is registered dynamically with a different service name.
Oracle Net concepts are abstract and easy to memorize superficially. Candidates forget that the listener brokers connections and can run on a different host, and that dynamic service registration publishes service names rather than SIDs. They also mix up local naming (tnsnames.ora) with the easy connect syntax.
What to do instead
Separate the roles: the listener receives connection requests and hands them to the database; the client resolves a net service name through tnsnames.ora, LDAP, or easy connect. For dynamic registration, the SERVICE_NAMES parameter determines what the listener advertises. Practice tracing a connection from client to listener to server process and identify which component would fail at each step.
Using V$ views and AWR reports interchangeably, or assuming AWR snapshots are always available on every edition and every database.
V$ views are real-time and instance-level; AWR is a historical repository requiring the Diagnostics and Tuning Pack and the SYSAUX tablespace. Candidates who memorize report names without knowing prerequisites pick the wrong tool for a historical analysis question.
What to do instead
Map each tool to its purpose and prerequisites: V$ views for current state, Statspack and AWR for historical snapshots, ADDM for automatic diagnosis, and the advisory framework (V$ADVISOR views and DBMS_ADVISOR) for targeted advice. State the licensing and configuration prerequisites before selecting a tool in a scenario.
Ignoring the automatic diagnostic repository and treating alert log, trace files, and incident dumps as the same thing, or forgetting that ADRCI manages the ADR home.
Diagnostic infrastructure is often studied last and in a hurry. Candidates do not know that the alert log is an XML file viewed through ADRCI or as text, that trace files are per-process, and that incidents are packaged for support. They also forget the DIAGNOSTIC_DEST location.
What to do instead
Learn the ADR layout: the base directory is DIAGNOSTIC_DEST, with homes per product and instance, containing the alert log, trace files, incident dumps, and the health monitor output. Use ADRCI commands such as SHOW ALERT, SHOW TRACEFILE, and IPS PACK to locate and package diagnostics. For automated maintenance, know the maintenance window and the AutoTask framework that schedules statistics gathering and space advisory jobs.