Oracle 1Z0-078 flashcards

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

SGA vs PGA

The SGA is shared memory containing the buffer cache, shared pool, redo log buffer, and large pool. The PGA is private per-server-process memory for sorts, hash joins, and session state.

LGWR write triggers

LGWR writes redo to disk on COMMIT, every three seconds, when the redo buffer is one-third full, or when DBWn needs to write dirty buffers. This enables fast commit and instance recovery.

SPFILE vs PFILE

The SPFILE is a binary server-managed parameter file that persists ALTER SYSTEM changes. The PFILE is a text file edited manually. Create an SPFILE with CREATE SPFILE FROM PFILE.

Startup stages

NOMOUNT starts the instance by reading the SPFILE. MOUNT reads the control file to locate data files. OPEN opens data files and redo logs, making the database available to users.

SCOPE parameter values

SCOPE=MEMORY changes only the running instance. SCOPE=SPFILE changes only the file and needs a restart. SCOPE=BOTH changes both and is the default for dynamic parameters.

Incremental backup levels

A level 0 backup includes all blocks and is the base. A level 1 differential backs up blocks changed since the last level 0 or 1. A cumulative level 1 backs up changes since the last level 0.

ARCHIVELOG mode requirement

ARCHIVELOG mode preserves archived redo logs, enabling online backups and point-in-time recovery. NOARCHIVELOG mode allows only consistent offline backups and loses recent transactions on media failure.

Restore versus recover

RESTORE copies data files from backups to disk. RECOVER applies archived and online redo to roll the restored files forward. Both steps are needed for media recovery.

Block change tracking

Block change tracking records changed blocks in a tracking file. It lets RMAN skip unchanged blocks during incremental backups, greatly reducing backup time for large databases.

Flashback Database

Flashback Database rewinds the whole database to a prior time or SCN using flashback logs. It requires ARCHIVELOG mode and a configured fast recovery area.

AWR and ADDM

AWR stores performance snapshots in SYSAUX hourly by default. ADDM runs after each snapshot and reports findings with estimated impact and concrete recommendations.

DB time and AAS

DB time is total time spent by foreground sessions in the database. Average active sessions equals DB time divided by elapsed time. AAS above CPU count signals contention.

Active Session History

ASH samples active sessions every second into V$ACTIVE_SESSION_HISTORY. Grouping samples by wait event reveals the top bottleneck over a time range.

SQL plan baseline

A SQL plan baseline is an accepted set of execution plans for a SQL statement. The optimizer uses only accepted plans, preventing performance regressions from plan changes.

PCTFREE and PCTUSED

PCTFREE reserves space in each block for future row growth. PCTUSED sets the minimum free space before a block is reused for inserts. Both affect block utilization and row migration.

Locally managed tablespace

A locally managed tablespace tracks extent allocation in a bitmap in the data file header, not in the data dictionary. It avoids recursive space management and reduces contention.

Undo retention

UNDO_RETENTION sets how long committed undo is kept. It must cover the longest query and flashback needs. Insufficient undo causes ORA-01555 snapshot too old.

Bigfile tablespace

A bigfile tablespace contains exactly one data file, which can reach 4 million blocks. A smallfile tablespace allows up to 1,022 data files. Block size is fixed at database creation.

Automatic Storage Management

ASM manages disk groups and stripes files across disks. Redundancy levels are NORMAL (two-way), HIGH (three-way), and EXTERNAL (no mirroring). It replaces file system management for database files.

CDB and PDB structure

A multitenant container database has one root (CDB$ROOT), one seed (PDB$SEED), and zero or more pluggable databases. Each PDB has its own data dictionary and application schema.

Common versus local users

Common users exist in the root and every PDB, typically with a C## prefix. Local users exist only in one PDB. SYSDBA is a common privilege usable across containers.

Container switching

ALTER SESSION SET CONTAINER = pdbname changes the session container. The CON_ID column in data dictionary views identifies which container a row belongs to.

Unplugging a PDB

Unplugging requires closing the PDB, then ALTER PLUGGABLE DATABASE ... UNPLUG INTO manifest.xml. The manifest and data files can be plugged into another CDB.

PDB save state

ALTER PLUGGABLE DATABASE ... SAVE STATE stores the open mode so the PDB reopens automatically when the CDB restarts. Without it, PDBs stay mounted after restart.

Data Pump parameters

expdp and impdp use directory objects. Key parameters include SCHEMAS, TABLES, CONTENT, REMAP_SCHEMA, REMAP_TABLESPACE, and NETWORK_LINK. Jobs are monitored in DBA_DATAPUMP_JOBS.

Direct path load

SQL*Loader direct path builds blocks directly and bypasses the buffer cache, which is faster than conventional path. Conventional path uses INSERTs and generates redo for each row.

Transportable tablespaces

Transportable tablespaces move data files plus metadata between databases. The tablespaces must be read-only and the platforms compatible, as listed in V$TRANSPORTABLE_PLATFORM.

GoldenGate components

GoldenGate uses an extract process to read redo, trail files to stage changes, and a replicat process to apply them to the target. Supplemental logging must be enabled.

Materialized view fast refresh

Fast refresh applies only changed rows and requires materialized view logs on the base tables. Refresh can occur ON COMMIT or ON DEMAND. Query rewrite lets the optimizer use the view.

Password file authentication

The password file (created with orapwd) stores passwords for privileged users. REMOTE_LOGIN_PASSWORDFILE controls its use. It enables SYSDBA logins over the network.

Profile password limits

Profiles set FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_REUSE_MAX, and PASSWORD_LOCK_TIME. The DEFAULT profile applies to users without an explicit profile.

Unified auditing

Unified auditing uses CREATE AUDIT POLICY and AUDIT POLICY statements. Audit records are stored in the AUDSYS schema and queried through the UNIFIED_AUDIT_TRAIL view.