Oracle Database Administration I flashcards

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

Instance vs. database

An instance is the SGA plus background processes running in memory; a database is the set of physical files (datafiles, control files, online redo logs) on disk.

SGA components

The system global area contains the shared pool, database buffer cache, redo log buffer, large pool, Java pool, and streams pool; SGA_TARGET enables automatic sizing of several of these.

DBWn and LGWR roles

DBWn writes dirty buffers from the buffer cache to datafiles, while LGWR writes redo entries from the log buffer to online redo log files, typically on commit.

Database block, extent, segment

A block is the smallest I/O unit; an extent is a set of contiguous blocks; a segment is a set of extents allocated for one schema object such as a table or index.

Locally managed tablespace

A tablespace whose free and used extents are tracked by a bitmap stored in each datafile rather than in the data dictionary, with AUTOALLOCATE or UNIFORM extent sizing.

ORA-01653 and ORA-01652

ORA-01653 means a table cannot extend because its tablespace or datafile is full; ORA-01652 means a temporary segment cannot extend, usually from insufficient TEMP space.

System vs. object privilege

A system privilege authorizes an action anywhere, such as CREATE SESSION or SELECT ANY TABLE; an object privilege authorizes an action on a specific object, such as SELECT on a table.

WITH ADMIN OPTION behavior

It lets a grantee grant a system privilege or role onward. Revoking the original system privilege does not cascade, so dependent grants remain in effect.

Profile password parameters

A profile sets PASSWORD_LIFE_TIME, PASSWORD_GRACE_TIME, PASSWORD_REUSE_MAX, PASSWORD_REUSE_TIME, PASSWORD_LOCK_TIME, and FAILED_LOGIN_ATTEMPTS, applied to all users assigned to it.

Startup NOMOUNT, MOUNT, OPEN

NOMOUNT starts the instance using the parameter file; MOUNT reads the control files; OPEN opens datafiles and redo logs so the database becomes available for normal use.

SHUTDOWN IMMEDIATE vs. ABORT

IMMEDIATE disconnects users and rolls back uncommitted transactions cleanly; ABORT terminates immediately without cleanup and requires instance recovery at the next startup.

SPFILE vs. PFILE

An SPFILE is a binary server-side parameter file changed with ALTER SYSTEM SCOPE; a PFILE is a text file edited manually. CREATE SPFILE FROM PFILE converts between them.

TRUNCATE vs. DELETE

TRUNCATE is DDL that removes all rows, releases space to the tablespace, and cannot be rolled back; DELETE is DML that can be rolled back and can include a WHERE clause.

Index-organized table

An IOT stores table rows in the leaf blocks of a B-tree index keyed on the primary key, so the table and its primary key index are the same segment.

Sequence NEXTVAL and CURRVAL

NEXTVAL returns the next sequence value and advances it; CURRVAL returns the last value generated in the current session. Sequences are not transactional and gaps are normal.

Undo vs. redo purpose

Undo stores before-images used for rollback and read consistency; redo stores after-images used to replay changes during recovery. Undo changes are themselves protected by redo.

ORA-01555 snapshot too old

The error occurs when a long-running query needs undo data that has already been overwritten because the undo tablespace is too small or UNDO_RETENTION is too low.

Log switch and archiving

A log switch closes the current redo log group and opens the next. In ARCHIVELOG mode the archiver process copies the closed group to the archive destination before reuse.

RMAN incremental levels

A level 0 backup copies all blocks as a base; a level 1 differential copies blocks changed since the last level 1; a cumulative level 1 copies blocks changed since the last level 0.

Flashback Database prerequisites

Flashback Database requires ARCHIVELOG mode, a fast recovery area, and flashback logs enabled; the database must be mounted to run FLASHBACK DATABASE to a target time or SCN.

Fast recovery area

A disk location defined by DB_RECOVERY_FILE_DEST with a size limit in DB_RECOVERY_FILE_DEST_SIZE that stores archived redo logs, RMAN backups, and flashback logs.

Connect descriptor structure

A connect descriptor lists ADDRESS entries with PROTOCOL, HOST, and PORT plus a CONNECT_DATA section naming the SERVICE_NAME or SID to which the client connects.

Dynamic service registration

The PMON process registers the instance and its service names with the listener, so a listener started after the database still learns about the service automatically.

ORA-12514 listener error

The listener is reachable but does not know the requested service, usually because the service name is misspelled or the instance has not registered with that listener.

DB time definition

DB time is the total time spent by database sessions in calls, equal to DB CPU plus non-idle wait time; it measures workload demand rather than elapsed wall-clock time.

Buffer cache hit ratio

The ratio is 1 minus physical reads divided by logical reads (db block gets plus consistent gets). A high value alone does not prove good performance.

db file sequential read

This wait event represents single-block reads, typically index or table access by rowid. High time here often points to inefficient indexed access or slow storage.

AWR and ASH

AWR stores periodic performance snapshots, by default every 60 minutes, for report comparison; ASH samples active session states every second for short-interval analysis.

Automatic Diagnostic Repository

ADR is the file-based repository rooted at DIAGNOSTIC_DEST that holds alert logs, trace files, incidents, and health check results, managed with the ADRCI utility.

Incident vs. problem

An incident is one occurrence of a critical error with its diagnostic data; a problem groups related incidents, and the Incident Packaging Service bundles them for support.

Health Monitor checks

DBMS_HM runs integrity checks such as DB Structure Integrity, Data Block Integrity, and Redo Integrity; findings are visible in V$HM_RUN and V$HM_FINDING.

SQL Tuning Advisor

It analyzes one or more SQL statements and recommends actions such as new indexes, refreshed statistics, or SQL profiles; it can be invoked with DBMS_SQLTUNE or Enterprise Manager.