Oracle DBA common mistakes
Oracle DBA · Understand the traps and how to avoid them.
Attributing sort/hash-join work areas and dedicated-server session memory to the SGA, and naming the wrong background process for a given write (for example saying DBWn writes redo log buffer entries to the online redo logs).
Automatic Memory Management is described as MEMORY_TARGET being split into SGA_TARGET and PGA_AGGREGATE_TARGET, which makes memory look like one undifferentiated pool; process roles are memorised as a list of acronyms rather than as a flow of data blocks and change vectors.
What to do instead
Draw the instance boundary explicitly: SGA = buffer cache, shared pool, redo log buffer, large pool, Java pool, fixed SGA and is shared by all server processes; PGA = private per-session memory containing work areas (sort, hash join, bitmap) for dedicated servers. Tie each process to its target: DBWn writes dirty buffers to datafiles, LGWR writes the redo log buffer to online redo logs, CKPT updates file headers and the control file, MMON/MMNL service AWR and metrics. Verify with V$SGAINFO, V$PGASTAT, V$PROCESS and V$BGPROCESS rather than from memory.
Assuming ALTER SYSTEM ... SCOPE=SPFILE changes take effect immediately, and assuming SCOPE=MEMORY changes survive an instance restart; also expecting a client-side init.ora to override the server-side SPFILE.
The parameter is easy to set in a lab and never retested after a shutdown, so the persistence semantics are never observed; the file search order is rarely seen because DBAs always use one file.
What to do instead
Memorise the semantics as a two-axis table: SCOPE=MEMORY applies now and is lost at restart; SCOPE=SPFILE is written to the server parameter file and applies only at the next startup; SCOPE=BOTH (the default when using an SPFILE) does both. For static parameters only SCOPE=SPFILE is legal and requires a restart. Memorise the startup search order in $ORACLE_HOME/dbs: spfile$ORACLE_SID.ora, then spfile.ora, then init$ORACLE_SID.ora, then init.ora - the first one found wins, so a stray initSID.ora can silently mask the SPFILE. Confirm with SHOW PARAMETER and SELECT name, value, isspecified FROM v$spparameter.
Believing the listener must stay running for already-established sessions, and believing remote clients need a copy of the server's tnsnames.ora to connect.
The listener is mentally modelled as a proxy that relays every packet, and tnsnames.ora is the only naming file most people have configured, so it seems mandatory on both ends.
What to do instead
Treat the listener as a connect-time broker only: it hands the client the server process's address, after which the client talks directly to the database. Stopping the listener does not drop existing sessions (verify in V$SESSION), it only blocks new ones. Name resolution happens on the client side, so the server needs tnsnames.ora only if it initiates outbound connections. Dynamic service registration by PMON/LREG means the database announces its services to the LOCAL_LISTENER address; use V$SERVICES and lsnrctl services to confirm registration, EZConnect (host:port/service) to bypass tnsnames.ora entirely.
Answering that DROP TABLESPACE removes the underlying datafiles from the operating system by default, or that a tablespace created without AUTOEXTEND will grow as segments are added.
The DROP succeeds, the segment disappears from the dictionary, and the test ends there without an OS-level check of the file; default storage attributes are assumed rather than read from the syntax.
What to do instead
Remember DROP TABLESPACE is a dictionary operation: the datafiles remain on disk and must be removed with INCLUDING DATAFILES (automatic for Oracle Managed Files) or manually afterwards. A tablespace cannot be dropped while it contains segments unless INCLUDING CONTENTS is specified. On creation, size the DATAFILE explicitly and add AUTOEXTEND ON NEXT n MAXSIZE m when growth is expected; confirm with SELECT tablespace_name, bytes, autoextensible, maxbytes FROM dba_data_files and DBA_TABLESPACES, and remember a file can be resized no larger than MAXSIZE.
Creating a user, granting CREATE SESSION and CREATE TABLE, then reporting a bug when the user still cannot create a table (ORA-01950: no privileges on tablespace).
System privileges are confused with space privileges; the default permanent tablespace and the quota grant in the CREATE USER statement are ignored because the test environment user happened to have UNLIMITED TABLESPACE.
What to do instead
Remember object creation needs three things: the system privilege, a default permanent tablespace, and a quota on that tablespace (CREATE USER ... DEFAULT TABLESPACE users QUOTA 100M ON users, or ALTER USER ... QUOTA UNLIMITED ON users, or the system privilege UNLIMITED TABLESPACE). Check DBA_TS_QUOTAS and DBA_USERS for the default and temporary tablespaces, and DBA_SYS_PRIVS for UNLIMITED TABLESPACE. Also note a user with no quota can still connect and query; only space-consuming DDL fails.
Claiming that a SELECT blocks an UPDATE of the same rows, that DML takes a table-level lock, or that a row lock persists after COMMIT or ROLLBACK.
Habits from other database engines, where shared read locks and lock escalation are normal, are carried over; Oracle's multiversion read consistency never has to be observed because it is invisible when it works.
What to do instead
State the rule plainly: Oracle never blocks readers - a query reads a consistent snapshot from undo and returns immediately while another session updates the same block. Writers block only writers, at row level; there is no lock escalation to table level, and TX locks appear in V$LOCK with the transaction's ID. Locks are held until COMMIT or ROLLBACK (statement-level rollback releases only that statement's). For isolation levels, READ COMMITTED gives statement-level read consistency, SERIALIZABLE gives transaction-level consistency and can fail with ORA-08177 rather than block. Practise reading V$TRANSACTION and V$LOCK to identify blockers.
Assuming that setting AUDIT_TRAIL=DB will populate the traditional AUD$ trail in a database where unified auditing is enabled, or assuming unified auditing can be turned off with an ALTER SYSTEM.
Almost all documentation examples and older scripts use AUDIT_TRAIL and DBA_AUDIT_TRAIL; the unified auditing change is a structural one, so the usual parameter toggle appears to work but silently records nothing.
What to do instead
Check first: SELECT value FROM v$option WHERE parameter = 'Unified Auditing'. When it is TRUE, all new audit records - including those created with the classic AUDIT statement - go to the unified trail, queried through UNIFIED_AUDIT_TRAIL (AUD$UNIFIED), not DBA_AUDIT_TRAIL. Create named policies with CREATE AUDIT POLICY ... then AUDIT POLICY ... and verify with AUDIT_UNIFIED_ENABLED_POLICIES. Enabling or disabling unified auditing requires relinking the oracle binary (make -f ins_rdbms.mk uniaud_on/uniaud_off), not a parameter change. Use DBMS_AUDIT_MGMT for purge and archival of the unified trail.
Planning point-in-time recovery beyond the last full backup on a database running in NOARCHIVELOG mode, or treating an RMAN backup taken with the database open in NOARCHIVELOG mode as a valid restore source.
The backup command succeeds either way, so mode is never connected to what can be recovered; archived redo log management feels like an optional extra rather than the thing that makes recovery possible.
What to do instead
Write the rule as a decision table: NOARCHIVELOG means online redo logs are reused and no redo is preserved, so the only recoverable state is the point of the last consistent (cleanly shut down) backup - restore that backup and you are done, with no roll-forward. Any recovery past that, or any backup taken while open, requires ARCHIVELOG mode: enable with SHUTDOWN IMMEDIATE, STARTUP MOUNT, ALTER DATABASE ARCHIVELOG, ALTER DATABASE OPEN, then confirm with ARCHIVE LOG LIST and V$DATABASE.LOG_MODE. In ARCHIVELOG mode you must also back up the archived logs to support complete and incomplete (until time/SCN/cancel) recovery.
Treating UNDO_RETENTION as a hard guarantee that undo is kept for that many seconds, then being surprised by ORA-01555 when a long query runs.
The parameter name sounds absolute and the behaviour is non-deterministic; space pressure in the undo tablespace is not visible in a small test database, so the overwrite of unexpired undo is never triggered.
What to do instead
Distinguish best-effort from guaranteed: UNDO_RETENTION is a target that Oracle may violate when the undo tablespace runs short; only a tablespace with RETENTION GUARANTEE refuses to overwrite unexpired undo (at the risk of failing DML with ORA-30036). Set it with ALTER TABLESPACE undotbs1 RETENTION GUARANTEE, verify with SELECT tablespace_name, retention FROM dba_tablespaces, and size the tablespace with AUTOEXTEND or a computed maximum. Diagnose with V$UNDOSTAT (tuned_undoretention, undo space) and V$ROLLSTAT, and remember UNDO_MANAGEMENT=AUTO with an undo tablespace is the only supported mode.
Running Data Pump export with an operating-system path such as /u01/dump/exp.dmp instead of a directory object name, or running a full export as a non-privileged user and being puzzled by the resulting errors.
Muscle memory from the original exp/imp utilities, which took filesystem paths directly; the DIRECTORY object is a separate dictionary object that must exist and be granted before Data Pump can be used at all.
What to do instead
Recall the three-step pattern: CREATE OR REPLACE DIRECTORY dpdir AS '/u01/dump'; GRANT READ, WRITE ON DIRECTORY dpdir TO operator; then expdp operator/... DIRECTORY=dpdir DUMPFILE=exp.dmp. Data Pump resolves the path server-side, so the directory must exist on the database server host. Full-database exports additionally require the DATAPUMP_EXP_FULL_DATABASE role (and DATAPUMP_IMP_FULL_DATABASE for imports). Confirm setup with SELECT directory_name, directory_path FROM dba_directories and DBA_DIR_GRANTS; for network-mode imports use NETWORK_LINK, which needs no dump file at all.