Oracle DBA flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Instance vs database

An Oracle instance is the SGA plus background processes; the database is the set of physical files, including data files, control files, and redo logs, that the instance mounts and opens.

DBWn role

The database writer writes dirty buffers from the buffer cache to data files, making room for new blocks and helping enforce checkpoint progress.

Logical storage hierarchy

From largest to smallest, a database contains tablespaces, which contain segments, which contain extents, which contain Oracle data blocks.

Checkpoint CKPT process

CKPT updates control files and data file headers with checkpoint information and signals DBWn, but it does not write data blocks itself.

STARTUP NOMOUNT stage

NOMOUNT starts the instance by reading the parameter file and allocating the SGA and background processes; it does not open the control file.

SPFILE vs PFILE

An SPFILE is a binary server-side parameter file that persists ALTER SYSTEM changes; a PFILE is a text file edited manually and read at startup.

SHUTDOWN IMMEDIATE

Immediate shutdown disconnects users, rolls back uncommitted transactions, and performs a checkpoint before closing the database cleanly.

Listener default port

The Oracle Net listener typically listens on TCP port 1521 and accepts incoming client connection requests for registered database services.

Easy Connect naming

Easy Connect uses host:port/service_name syntax, such as hr.example.com:1521/orcl, and needs no tnsnames.ora entry on the client.

Dynamic service registration

The PMON background process registers database services with the listener, allowing clients to connect without a static SID_LIST entry.

Locally managed tablespace

A locally managed tablespace tracks extent allocation in a bitmap in its data file header rather than in the data dictionary, reducing contention.

Bigfile tablespace

A bigfile tablespace contains exactly one data file and can be very large; a smallfile tablespace can contain many data files.

ASSM

Automatic Segment Space Management uses bitmaps in segment headers to manage free space, replacing PCTFREE and PCTUSED manual freelist management.

CREATE USER essentials

CREATE USER specifies a password, default tablespace, temporary tablespace, and quota; the user needs CREATE SESSION to log in.

WITH ADMIN OPTION

Granting a system privilege WITH ADMIN OPTION lets the grantee grant that privilege to other users; revoking it does not cascade.

Profile password limits

A profile can set password lifetime, failed login attempts, lock time, and reuse limits, enforcing password policy for assigned users.

READ COMMITTED

Oracle's default isolation level gives statement-level read consistency; each query sees data committed before the statement began, not uncommitted changes.

Row-level locks

DML acquires exclusive row locks (TX) only for modified rows, allowing concurrent transactions to change different rows in the same table.

Deadlock ORA-00060

When sessions wait on each other in a cycle, Oracle detects the deadlock and rolls back one statement with ORA-00060, freeing the other session.

Unified auditing

Unified auditing stores audit records in the AUDSYS schema and exposes them through the UNIFIED_AUDIT_TRAIL view, replacing separate legacy audit trails.

CREATE AUDIT POLICY

A unified audit policy defines which privileges, actions, or objects to audit; it must be enabled with AUDIT POLICY to take effect.

Fine-grained auditing

FGA uses DBMS_FGA to audit row-level access that meets a specified condition, recording only relevant queries rather than all table access.

ARCHIVELOG mode

In ARCHIVELOG mode Oracle archives filled redo log files, enabling media recovery, online backups, and point-in-time recovery while the database is open.

Level 0 incremental

A level 0 incremental RMAN backup copies all blocks that have ever been used and serves as the base for later level 1 incremental backups.

Restore versus recover

Restore copies backup files back into place; recover applies redo and undo to restored files to make them consistent to a chosen point.

Flashback Database

Flashback Database uses flashback logs in the fast recovery area to return the whole database to an earlier point in time without restoring backup files.

Undo retention

UNDO_RETENTION specifies how long committed undo is kept for read consistency and flashback; RETENTION GUARANTEE prevents reuse even if space is needed.

ORA-01555

Snapshot too old occurs when a long query needs undo that has been overwritten, typically from insufficient undo retention or an undersized undo tablespace.

Undo tablespace contents

An undo tablespace stores only undo segments and cannot hold application tables or indexes; it supports rollback, read consistency, and recovery.

Data Pump directory object

Data Pump requires a DIRECTORY object pointing to a server filesystem path; the user needs READ and WRITE privileges on that directory.

SQL*Loader direct path

Direct-path SQL*Loader writes formatted blocks directly above the high-water mark, bypassing the buffer cache and normal insert processing for faster loads.

Transportable tablespaces

Transportable tablespaces move data files plus exported metadata between databases; source and target must have compatible platforms or use RMAN conversion.