Oracle DBA cheat sheet

Instance = SGA + background processes; database = data files + control files + online redo log files.

SGA includes buffer cache, shared pool (library cache and data dictionary cache), large pool, Java pool, redo log buffer, and optional result cache.

PGA is private memory for a server process and holds sort/hash work areas and session cursor state.

DBWn writes dirty buffers to data files; LGWR writes redo log buffer entries to online redo logs; CKPT records checkpoint positions.

SMON performs instance recovery after a crash; PMON cleans up failed processes and registers services; ARCn archives redo logs in ARCHIVELOG mode.

Logical hierarchy: database > tablespace > segment > extent > Oracle block; physical hierarchy: data files and OS blocks.

A non-RAC instance opens exactly one database at a time; RAC allows multiple instances to open the same database.

MEMORY_TARGET sizes SGA plus PGA dynamically; SGA_TARGET sizes SGA components; SGA_MAX_SIZE limits total SGA growth.

CREATE DATABASE requires the instance to be started in NOMOUNT and specifies control files, data files, and redo log files.

STARTUP stages: NOMOUNT reads the parameter file and starts the instance; MOUNT opens the control file; OPEN opens data files and redo logs.

SPFILE is binary and server-side; PFILE is text; CREATE SPFILE FROM PFILE converts a text file to an SPFILE.

SHUTDOWN NORMAL waits for sessions; TRANSACTIONAL waits for transactions; IMMEDIATE rolls back and disconnects; ABORT skips cleanup.

STARTUP FORCE performs SHUTDOWN ABORT followed by STARTUP.

Key parameters include DB_NAME, DB_BLOCK_SIZE, CONTROL_FILES, DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST, UNDO_TABLESPACE, and COMPATIBLE.

DBCA can create a database and optionally generate scripts for later execution.

Remote SYSDBA/SYSOPER administration uses a password file; OS authentication can use the OSDBA/OSOPER groups.

The listener receives client connections; default TCP port is 1521; lsnrctl STATUS, SERVICES, START, and STOP manage it.

tnsnames.ora maps net service names to connect descriptors on clients; listener.ora defines listener addresses and static services.

sqlnet.ora controls naming methods, logging, and security parameters on client and server.

Easy Connect syntax is host:port/service_name, for example dbhost:1521/orcl; it requires no local naming file.

PMON dynamically registers database services with the listener; static registration uses SID_LIST_LISTENER.

A connect descriptor contains ADDRESS and CONNECT_DATA sections, including SERVICE_NAME or SID.

TNSPING tests name resolution and listener reachability, not database authentication or SQL access.

Shared server uses dispatchers and shared server processes; dedicated server assigns one server process per client.

A data file belongs to exactly one tablespace; a tablespace can contain multiple data files, except a bigfile tablespace which has one.

Locally managed tablespaces use bitmaps in data file headers; AUTOALLOCATE lets Oracle size extents, UNIFORM uses fixed extent size.

ASSM (SEGMENT SPACE MANAGEMENT AUTO) uses bitmaps for free space; manual segment space management uses PCTFREE and PCTUSED.

SYSTEM and SYSAUX are mandatory tablespaces; undo and temporary tablespaces are required for normal operation.

ALTER TABLESPACE ... ADD DATAFILE, ALTER DATABASE DATAFILE ... RESIZE, and AUTOEXTEND ON NEXT ... MAXSIZE manage space.

DB_CREATE_FILE_DEST enables Oracle Managed Files, so data files and redo logs are named and placed automatically.

ASM disk groups provide storage for data files; ASM redundancy can be EXTERNAL, NORMAL, or HIGH.

The database block size is set at creation; tablespaces with nonstandard block sizes require a matching DB_nK_CACHE_SIZE.

CREATE USER sets a password, default tablespace, temporary tablespace, and quota; CREATE SESSION is required to connect.

System privileges apply to the database (CREATE TABLE); object privileges apply to specific objects (SELECT on hr.employees).

WITH ADMIN OPTION allows a system privilege to be granted onward; WITH GRANT OPTION does this for object privileges.

Roles bundle privileges; default roles are enabled at login; SET ROLE enables others; roles can require a password.

Profiles enforce password policy and resource limits such as FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, and IDLE_TIME.

ALTER USER ... ACCOUNT LOCK/UNLOCK and PASSWORD EXPIRE control account status.

PUBLIC is a role granted to every user; granting privileges to PUBLIC should be limited to widely needed privileges.

Authentication can be password-based, external OS, or global; OS_AUTHENT_PREFIX controls OS-authenticated usernames.

A transaction ends with COMMIT or ROLLBACK; SAVEPOINT and ROLLBACK TO SAVEPOINT provide partial rollback.

Default isolation is READ COMMITTED with statement-level read consistency; SERIALIZABLE provides transaction-level consistency.

DML takes row-level TX locks and table-level TM locks; DDL takes exclusive locks that block conflicting DML.

SELECT ... FOR UPDATE locks selected rows; NOWAIT returns immediately, WAIT n waits n seconds, SKIP LOCKED skips locked rows.

Deadlocks are detected automatically; one session receives ORA-00060 and its statement is rolled back.

V$LOCK, V$SESSION, DBA_BLOCKERS, and DBA_WAITERS help diagnose lock waits; ALTER SYSTEM KILL SESSION terminates a session.

Undo supports read consistency and rollback; ORA-01555 occurs when required undo is overwritten.

Autonomous transactions commit independently of the calling transaction and are declared with PRAGMA AUTONOMOUS_TRANSACTION.

Unified auditing is the modern framework; audit records are stored in AUDSYS and queried through UNIFIED_AUDIT_TRAIL.

CREATE AUDIT POLICY defines audit actions; AUDIT POLICY enables it; AUDIT_UNIFIED_POLICIES and AUDIT_UNIFIED_ENABLED_POLICIES show definitions and status.

Unified audit policies can audit privileges, actions, and objects, with success and failure conditions.

Traditional auditing uses AUDIT_TRAIL settings and the AUD$ table, with DBA_AUDIT_TRAIL and related views.

Fine-grained auditing uses DBMS_FGA to audit rows that satisfy a condition, such as access to sensitive columns.

The AUDIT_ADMIN role manages unified audit policies; AUDIT SYSTEM and AUDIT OBJECT are separate privileges.

Audit records can be written to the database audit trail, OS files, XML files, or syslog depending on configuration.

DBMS_AUDIT_MGMT manages audit trail tablespace and purge settings.

Physical backups copy database files; logical backups export data with Data Pump or export utilities.

ARCHIVELOG mode archives filled redo logs and enables online backups, media recovery, and point-in-time recovery.

NOARCHIVELOG mode permits only consistent closed backups and cannot recover media failures while the database is open.

RMAN level 0 backs up all used blocks as a base; level 1 differential backs up blocks changed since the last level 0 or 1; cumulative since last level 0.

RESTORE places backup files back; RECOVER applies redo and undo to make files consistent.

A consistent backup is taken with the database closed; an inconsistent open backup requires archived redo logs for recovery.

Flashback Database uses flashback logs in the fast recovery area to rewind the database without restoring files.

FAST_START_MTTR_TARGET influences checkpoint frequency to target a mean time to recover.

Automatic undo management uses an undo tablespace; UNDO_MANAGEMENT=AUTO and UNDO_TABLESPACE select it.

Undo supports rollback, read consistency, flashback queries, and instance recovery.

UNDO_RETENTION sets desired retention for committed undo; RETENTION GUARANTEE prevents reuse even when space is needed.

An undo tablespace contains only undo segments and cannot hold tables or indexes.

ALTER SYSTEM SET UNDO_TABLESPACE switches undo tablespaces; the old one becomes pending and can be dropped after it is unused.

ORA-01555 snapshot too old results from insufficient undo retention or a long-running query.

V$UNDOSTAT and DBA_UNDO_EXTENTS show undo usage; the undo advisor recommends tablespace size.

Temporary undo, enabled by TEMP_UNDO_ENABLED, stores undo for temporary tables in the temporary tablespace.

Data Pump uses expdp and impdp with DIRECTORY objects, dump files, log files, and optional network links.

Data Pump modes include full, schema, table, tablespace, and transportable tablespaces.

REMAP_SCHEMA, REMAP_TABLESPACE, and REMAP_TABLE transform names during import.

SQL*Loader loads external files using a control file; conventional path uses normal inserts, direct path writes blocks above the high-water mark.

External tables read external data through ORACLE_LOADER or ORACLE_DATAPUMP without loading it into the database.

Transportable tablespaces move data files and metadata; platforms must be compatible or converted with RMAN.

DBMS_METADATA extracts DDL; DBMS_DATAPUMP provides the Data Pump API.

CREATE TABLE ... AS SELECT and INSERT ... SELECT over a database link copy data between databases.