Oracle 1Z0-078 cheat sheet

An Oracle instance = SGA (memory) + background processes; the database = data files, control file, redo log files, and optional server parameter file (SPFILE).

SGA components: shared pool (library cache + row cache), database buffer cache, redo log buffer, large pool, Java pool, streams pool, and fixed SGA; SGA_MAX_SIZE caps total SGA, MEMORY_TARGET sizes SGA+PGA together.

Background processes: DBWn, LGWR, CKPT, SMON, PMON, ARCn, RECO (or the single process in multitenant), MMON/MMNL, and DIA0; LGWR writes redo on commit, every 3 seconds, or when the redo buffer is one-third full.

Parameter scope rules: SCOPE=MEMORY changes only the running instance; SCOPE=SPFILE changes only the file (restart required); SCOPE=BOTH is default for dynamic parameters.

SPFILE vs PFILE: SPFILE is binary and server-managed; create it with CREATE SPFILE FROM PFILE; recover a lost SPFILE from the alert log or from RMAN autobackup.

Instance startup stages: NOMOUNT (reads SPFILE, starts SGA and processes), MOUNT (reads control file), OPEN (opens data files and redo logs); SHUTDOWN NORMAL/TRANSACTIONAL/IMMEDIATE/ABORT differ in whether sessions are disconnected and whether recovery is needed.

A database supports up to 8,732 data files and 255 redo log file groups; redo log group size is bounded by OS limits; a single data file can grow to 4M blocks by default (block size dependent).

Use ALTER SYSTEM SET ... DEFERRED for parameters that cannot change while sessions exist (e.g., sort_area_size); ALTER SYSTEM RESET reverts to the SPFILE default.

Diagnostic destinations: DIAGNOSTIC_DEST is the base for trace files; ADR homes hold alert logs, trace, and incident data; query V$DIAG_INFO for locations.

RMAN backup types: full (all blocks), incremental level 0 (base for later incrementals), incremental level 1 differential (since last level 0 or 1), and cumulative (since last level 0 only).

Block change tracking (ALTER DATABASE ENABLE BLOCK CHANGE TRACKING) records changed blocks in a change-tracking file, enabling fast incremental backups; query V$BLOCK_CHANGE_TRACKING.

ARCHIVELOG mode is required for online backups and point-in-time recovery; NOARCHIVELOG mode permits only offline/consistent backups and restores to the last full backup.

Redo log files are protected by multiplexing (multiple members per group) and archived redo by LOG_ARCHIVE_DEST_n; FRA (DB_RECOVERY_FILE_DEST) handles archive logs, backups, and flashback logs with a size quota.

RMAN retention policies: RETENTION POLICY TO RECOVERY WINDOW OF n DAYS vs REDUNDANCY n; REPORT OBSOLETE and DELETE OBSOLETE enforce the policy.

Restore vs recover: RESTORE copies files from backup; RECOVER applies redo (archived and online) to roll forward; then the database opens.

Point-in-time recovery: RMAN SET UNTIL TIME/SCN/SEQUENCE, or FLASHBACK DATABASE (requires flashback logs and ARCHIVELOG mode); FLASHBACK TABLE uses undo.

Data Recovery Advisor (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE) proposes fixes for detected failures; RMAN VALIDATE checks backup integrity.

A whole-database backup needs the control file autobackup and SPFILE; a restore of a control file can come from CONFIGURE CONTROLFILE AUTOBACKUP ON output.

AWR snapshots are stored in SYSAUX; MMON collects them hourly by default; DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS sets the interval and retention; AWR reports compare two snapshots.

ADDM runs automatically after each AWR snapshot and reports findings with impact and recommendations; run manually with @?/rdbms/admin/addmrpt.sql.

ASH samples active sessions every second into V$ACTIVE_SESSION_HISTORY; the top wait event by DB time identifies the bottleneck.

DB time = time spent by foreground sessions in the database; average active sessions (AAS) = DB time / elapsed time; AAS > number of CPUs indicates contention.

Wait classes: User I/O, System I/O, Concurrency, Application, Commit, Network, Configuration, Administrative, Scheduler, Cluster, Queueing.

Top SQL: V$SQL, V$SQLAREA, and DBA_HIST_SQLSTAT; look at buffer gets per execution, disk reads per execution, and elapsed time per execution; bind variables reduce hard parses.

SQL Tuning Advisor (DBMS_SQLTUNE) and SQL Access Advisor (DBMS_ADVISOR) recommend indexes, statistics, or SQL profiles; SQL Plan Management (SQL plan baselines) stabilizes plans.

Statistics: DBMS_STATS.GATHER_TABLE_STATS / GATHER_SCHEMA_STATS; stale statistics cause bad cardinality estimates; histograms help for skewed columns; DBMS_STATS.AUTO_SAMPLE_SIZE is default.

Real-Time SQL Monitoring (V$SQL_MONITOR) activates for long-running or parallel SQL; SQL Trace (10046 event) plus TKPROF yields detailed execution statistics.

Tablespace types: permanent (SYSTEM, SYSAUX, users), undo (UNDOTBS), and temporary (TEMP); each data file belongs to exactly one tablespace.

Segment types: table, index, undo, temp, LOB, and cluster; a segment consists of extents, an extent of blocks; PCTFREE reserves space for row growth, PCTUSED governs block reuse.

Locally managed tablespaces use bitmaps in the data file header; AUTOALLOCATE vs UNIFORM extent sizing; ASSM (automatic segment space management) uses bitmaps for free space in segments.

Space checks: DBA_FREE_SPACE, DBA_TABLESPACE_USAGE_METRICS, DBA_SEGMENTS, and DBA_DATA_FILES; ALTER DATABASE DATAFILE ... AUTOEXTEND ON MAXSIZE n controls growth.

Undo: UNDO_RETENTION controls how long undo is kept; undo tablespace must be sized for the longest query and flashback; ORA-01555 is a snapshot-too-old error.

Temporary tablespace: TEMP files, TEMP_UNDO_ENABLED for global temporary tables; sort and hash operations use TEMP; monitor V$TEMPSEG_USAGE.

Oracle Managed Files (OMF, DB_CREATE_FILE_DEST) auto-name and place files; ASM (Automatic Storage Management) uses disk groups and redundancy levels NORMAL/HIGH/EXTERNAL.

Shrink operations: ALTER TABLE ... SHRINK SPACE (needs row movement), ALTER INDEX ... SHRINK SPACE, and online data file resize; MOVE for tablespaces.

Bigfile tablespaces contain exactly one data file (up to 4M blocks); smallfile tablespaces allow up to 1,022 files; block size is set at CREATE DATABASE and cannot change.

A CDB contains exactly one root (CDB$ROOT), one seed (PDB$SEED), and zero or more PDBs; the root holds common users and common objects.

PDB creation: CREATE PLUGGABLE DATABASE ... FROM seed, or clone from an existing PDB, or plug in an unplugged PDB via a manifest XML file.

Local vs common users: common users (C## prefix by default) exist in root and all PDBs; local users exist only in one PDB; SYSDBA is a common privilege.

Containers clause: ALTER SESSION SET CONTAINER = pdbname switches session container; V$CONTAINERS lists containers; CON_ID column in data dictionary views identifies the container.

A PDB is opened with ALTER PLUGGABLE DATABASE pdbname OPEN; save state with ALTER PLUGGABLE DATABASE ... SAVE STATE to reopen on CDB restart.

Unplugging requires the PDB to be closed; UNPLUG INTO manifest.xml writes the manifest; DROP PLUGGABLE DATABASE ... KEEP DATAFILES preserves files.

PDB cloning: CREATE PLUGGABLE DATABASE newpdb FROM oldpdb; a snapshot copy or a clone from a remote CDB uses a database link.

Resource management in a CDB uses CDB resource plans (DBMS_RESOURCE_MANAGER) to allocate shares and CPU limits among PDBs.

Backup in multitenant: RMAN can back up the whole CDB or a single PDB; a PDB can be restored or flashed back independently; PDB point-in-time recovery uses SET UNTIL.

Data Pump: expdp/impdp use directory objects; parameters include SCHEMAS, TABLES, CONTENT (DATA_ONLY/METADATA_ONLY), REMAP_SCHEMA, REMAP_TABLESPACE; jobs run in the database and are monitored via DBA_DATAPUMP_JOBS.

Data Pump network mode uses NETWORK_LINK to move data between databases without dump files; FLASHBACK_TIME/SCN provides a consistent export.

SQL*Loader: control file defines the load; conventional path uses INSERTs and redo, direct path bypasses the buffer cache and can use PARALLEL; external tables (ORACLE_LOADER) read files via SQL.

Transportable tablespaces move data files with metadata: expdp TRANSPORT_TABLESPACES, copy files, impdp; requires the tablespaces to be read-only and platforms to be compatible (V$TRANSPORTABLE_PLATFORM).

GoldenGate replicates changes using extract (reads redo), trail files, and replicat (applies changes); uses supplemental logging (ADD SUPPLEMENTAL LOG DATA).

Materialized views replicate query results; REFRESH FAST requires materialized view logs; ON COMMIT vs ON DEMAND refresh; query rewrite can use MVs.

Streams (deprecated) and Logical Standby (SQL Apply) are alternative replication mechanisms; Logical Standby applies SQL from redo to a standby database.

Data movement between PDBs: CREATE TABLE ... AS SELECT across containers, or Data Pump with REMAP_SCHEMA; DBMS_CLOUD moves data to object storage.

Authentication methods: password file (orapwd), OS authentication (OS_AUTHENT_PREFIX), and external authentication (Kerberos, RADIUS); REMOTE_LOGIN_PASSWORDFILE controls SYSDBA logins.

Privileges: system privileges (CREATE SESSION, CREATE TABLE) vs object privileges (SELECT, INSERT on a table); roles group privileges and can be granted with or without ADMIN OPTION.

Profiles control password limits: FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_REUSE_MAX, PASSWORD_LOCK_TIME; set in CREATE PROFILE; DEFAULT profile applies to all users.

Auditing: unified auditing (AUDIT POLICY, CREATE AUDIT POLICY) writes to AUDSYS schema; query UNIFIED_AUDIT_TRAIL; traditional auditing uses AUD$ and AUDIT_TRAIL parameter.

Fine-grained auditing (DBMS_FGA) audits rows matching a condition and can capture SQL text and bind values; policy stored in DBA_AUDIT_POLICIES.

Transparent Data Encryption (TDE) encrypts columns or tablespaces with a keystore (wallet); ADMINISTER KEY MANAGEMENT manages the keystore; encrypted data is transparent to applications.

Data Redaction (DBMS_REDACT) masks column values at query time; Virtual Private Database (DBMS_RLS) enforces row-level security via policies.

Least privilege: REVOKE unnecessary privileges, use roles, and audit privileged users; DBA_SYS_PRIVS and DBA_TAB_PRIVS show grants; DBA_ROLE_PRIVS shows role membership.

Database Vault enforces separation of duties and realm protection; it restricts even SYSDBA from accessing protected application data.