1Z0-908 cheat sheet

An Oracle instance = memory structures (SGA: buffer cache, shared pool, large pool, redo log buffer, fixed SGA) + background processes; the database = data files, control files, redo log files, and optionally a server parameter file and password file.

Core background processes: DBWn writes dirty buffers to data files; LGWR writes redo from the log buffer to online redo logs; CKPT updates file headers and the control file; SMON does instance recovery and coalesces free space; PMON cleans up failed processes; ARCn copies filled redo logs when ARCHIVELOG is enabled.

The server parameter file (SPFILE) is binary and changed with ALTER SYSTEM ... SCOPE={MEMORY|SPFILE|BOTH}; a text PFILE (init.ora) is edited manually and read at startup or with STARTUP PFILE=.

ALTER SYSTEM SCOPE=MEMORY changes only the running instance and is lost on restart; SCOPE=SPFILE persists but needs a restart for static parameters; SCOPE=BOTH applies immediately and persists only for dynamic parameters.

Startup sequence: NOMOUNT (reads parameter file, starts instance) -> MOUNT (reads control files) -> OPEN (opens data files and redo logs). SHUTDOWN modes: NORMAL, TRANSACTIONAL, IMMEDIATE, ABORT; only ABORT and IMMEDIATE terminate sessions without waiting.

Diagnostic files: alert log (text alert_.log or XML), trace files, and Automatic Diagnostic Repository (ADR) under DIAGNOSTIC_DEST; view with ADRCI or V$DIAG_INFO.

Automatic Shared Memory Management sizes SGA components via SGA_TARGET; MEMORY_TARGET adds PGA management. If MEMORY_TARGET is set, SGA_TARGET and PGA_AGGREGATE_TARGET act as minimums.

V$INSTANCE shows status and startup time; V$PARAMETER shows current values; V$SPPARAMETER shows SPFILE contents; V$OPTION confirms whether options such as Partitioning or Multitenant are installed.

ARCHIVELOG mode is required for online backups and point-in-time recovery; LOG_MODE in V$DATABASE reports ARCHIVELOG or NOARCHIVELOG, and switching requires MOUNT state.

Oracle Universal Installer (OUI) installs the Oracle Home; Database Configuration Assistant (DBCA) creates databases; Net Configuration Assistant (NETCA) configures listeners and naming methods.

Oracle Home and Grid Infrastructure Home are separate; OPatch and the newer Oracle Database Autoupgrade (autoupgrade.jar) or dbca-based patching apply release updates and one-off patches to a home, not to a database.

Datapatch (datapatch -verbose) runs SQL patches against each database after OPatch applies binaries; a database is not patched until datapatch completes and the CDB/PDB is opened in UPGRADE mode as required.

Query patch state with opatch lspatches, opatch lsinventory, and the data dictionary views DBA_REGISTRY, DBA_REGISTRY_SQLPATCH, and DBA_REGISTRY_HISTORY.

Release Update (RU) and Monthly Recommended Patch (MRP) supersede the old PSU/CPU model; patch conflicts are checked with opatch prereq CheckConflictAgainstOHWithDetail.

Read-only Oracle Home (introduced in 18c) allows multiple patched homes sharing a read-only base; ORACLE_HOME and ORACLE_BASE must not be nested inside each other's directory trees.

Pre-install checks: OS packages, kernel parameters (shmmax, semmni), ulimits, /etc/hosts resolution, and disk space; runcluvfy.sh validates cluster prerequisites for RAC.

Upgrades use DBUA or the catupgrd.sql / utluppkg scripts manually; the compatible parameter limits which features can be used and cannot be lowered below the current data dictionary level without consequences.

Logical hierarchy: database -> tablespaces -> segments -> extents -> Oracle blocks; a segment belongs to one tablespace, and blocks are the smallest I/O unit (DB_BLOCK_SIZE, set at creation).

Locally managed tablespaces (LMT) track extents in a bitmap in the tablespace itself; AUTOALLOCATE lets Oracle choose extent sizes, UNIFORM SIZE forces fixed extents. Dictionary-managed tablespaces are legacy.

Segment space management AUTO (ASSM) uses bitmaps in segment headers and enables automatic free space management; MANUAL relies on PCTFREE/PCTUSED and freelists.

ASSM requires PCTFREE but ignores PCTUSED; PCTFREE reserves space in a block for row growth (updates), and PCTUSED controls return to the freelist only in MSSM.

Tablespace states: ONLINE/OFFLINE (data files unavailable), READ ONLY, READ WRITE, and TEMPORARY; a tablespace can be renamed, made read-only, or put in backup mode.

Bigfile tablespaces contain exactly one data file (max ~32 PB with 8K blocks) and use 4G extent bitmaps; smallfile tablespaces allow up to 1022 data files (OS-dependent).

Undo tablespace holds undo segments for read consistency, rollback, and flashback; TEMP tablespace holds sort/hash work areas and temporary segments; both are mandatory.

Space reporting: DBA_TABLESPACE_USAGE_METRICS, DBA_DATA_FILES, DBA_FREE_SPACE, DBA_SEGMENTS, and DBA_EXTENTS; V$DATAFILE and V$TEMPSEG_USAGE show runtime usage.

Data file autoextend is configured with AUTOEXTEND ON NEXT n MAXSIZE m; resizing uses ALTER DATABASE DATAFILE ... RESIZE, and adding files uses ALTER TABLESPACE ... ADD DATAFILE.

CREATE USER ... IDENTIFIED BY password; a schema is created with the user and has the same name. Default tablespace and quota are set with DEFAULT TABLESPACE and QUOTA n ON tablespace.

System privileges (e.g., CREATE TABLE, SELECT ANY TABLE) are granted with GRANT priv TO user; object privileges (SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES) are granted on specific objects.

Roles bundle privileges; a role can be granted to users or other roles, and can be made non-default (ALTER USER ... DEFAULT ROLE) so it must be enabled with SET ROLE.

WITH ADMIN OPTION lets the grantee grant the system privilege or role onward; WITH GRANT OPTION does the same for object privileges. Revoking a system privilege does not cascade, but revoking an object privilege does cascade.

Profiles enforce password policy: FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_REUSE_MAX, PASSWORD_VERIFY_FUNCTION, and resource limits such as SESSIONS_PER_USER and IDLE_TIME.

Privilege analysis and least privilege: query DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_ROLE_PRIVS, DBA_COL_PRIVS, and SESSION_PRIVS (currently enabled privileges in the session).

Common predefined roles: DBA (all system privileges WITH ADMIN OPTION), RESOURCE, CONNECT, SELECT_CATALOG_ROLE, and PUBLIC (granted to every user - avoid granting sensitive privileges to PUBLIC).

A user with the SYSDBA privilege authenticates via password file or OS authentication (OSOPER/OSDBA groups); SYSDBA connects to an idle instance for startup, SYSOPER cannot create or drop databases.

Account lock/unlock uses ALTER USER ... ACCOUNT LOCK|UNLOCK; expired passwords are reset with ALTER USER ... IDENTIFIED BY; DBA_USERS shows ACCOUNT_STATUS and EXPIRY_DATE.

Backup types: full vs incremental (level 0 base, level 1 differential = changes since last level 0, cumulative = changes since last level 0 or 1), and image copies vs backup sets.

RMAN incremental levels: LEVEL 0 is the base; LEVEL 1 DIFFERENTIAL backs up blocks changed since the most recent level 0 or 1; LEVEL 1 CUMULATIVE backs up blocks changed since the most recent level 0.

Consistent (cold) backups require the database closed; inconsistent (hot) backups require ARCHIVELOG mode and put tablespaces in backup mode or use RMAN, which handles block consistency via redo.

RMAN commands: BACKUP DATABASE PLUS ARCHIVELOG, BACKUP INCREMENTAL LEVEL 1, RESTORE, RECOVER, VALIDATE, and REPORT NEED BACKUP / OBSOLETE; configure retention with CONFIGURE RETENTION POLICY.

Fast Recovery Area (FRA) is sized by DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST; it stores backups, archived logs, and flashback logs, and space pressure triggers deletion of obsolete files.

Complete recovery applies redo to a restored data file up to the current SCN; point-in-time recovery uses SET UNTIL TIME|SCN|SEQUENCE and requires backups plus archived redo covering the target time.

Incomplete recovery requires OPEN RESETLOGS afterwards, which creates a new incarnation; restore a control file with RESTORE CONTROLFILE FROM AUTOBACKUP when the control file is lost.

Flashback technologies: Flashback Query (AS OF), Flashback Table, Flashback Drop (recycle bin), Flashback Database (needs flashback logs and MOUNT state), and Flashback Data Archive for long-term history.

Data Pump (expdp/impdp) is the logical alternative to RMAN for schema/table movement; it uses DIRECTORY objects and supports REMAP_SCHEMA, REMAP_TABLESPACE, and network mode.

Automatic Workload Repository (AWR) snapshots are taken every 60 minutes by default (configurable via DBMS_WORKLOAD_REPOSITORY); reports compare two snapshots and show top wait events and SQL.

Active Session History (ASH) samples active sessions every second; V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY show where time is spent, including wait class breakdown.

Wait classes: Application, Configuration, Administrative, Concurrency, Commit, Idle, Network, User I/O, System I/O, Scheduler, Cluster, Queueing. DB time = CPU time + non-idle wait time.

The buffer cache hit ratio is a diagnostic, not a target; a low hit ratio may be fine for a well-tuned I/O subsystem. Prefer examining top wait events and SQL by elapsed time.

ADDM runs automatically after each AWR snapshot and reports findings and recommendations; query with DBMS_ADDM or view DBA_ADVISOR_FINDINGS.

SQL tuning tools: SQL Tuning Advisor, SQL Access Advisor (index/materialized view advice), SQL Plan Management (baselines), and Automatic SQL Tuning in the maintenance window.

V$SQL and V$SQLSTATS rank statements by ELAPSED_TIME, CPU_TIME, BUFFER_GETS, and DISK_READS; V$SQL_PLAN shows the execution plan with DBMS_XPLAN.DISPLAY_CURSOR for actual rows.

Memory advisors: V$DB_CACHE_ADVICE, V$PGA_TARGET_ADVICE, V$SGA_TARGET_ADVICE, and V$MEMORY_TARGET_ADVICE estimate the effect of resizing before you change parameters.

Statspack is legacy; AWR requires the Diagnostics and Tuning Pack license. Statistic levels (STATISTICS_LEVEL = BASIC, TYPICAL, ALL) control whether AWR, ASH, and advisors collect data.

Oracle uses statement-level read consistency and transaction-level read consistency via undo; a query sees data as of the SCN when the statement or transaction began, never uncommitted changes.

Isolation levels: READ COMMITTED (default), SERIALIZABLE, and READ ONLY. Oracle does not implement dirty reads; writers do not block readers and readers do not block writers.

Row-level locking: an UPDATE/DELETE locks rows until COMMIT or ROLLBACK; a second session blocks with the 'enq: TX - row lock contention' wait. SELECT ... FOR UPDATE locks rows explicitly.

Deadlocks are detected automatically after ~3 seconds; Oracle rolls back one statement with ORA-00060 and writes a deadlock trace to the alert log.

Undo data supports rollback, read consistency, and flashback; UNDO_RETENTION sets the desired retention, and UNDO_TABLESPACE names the undo tablespace (one active per instance).

ORA-01555 snapshot too old occurs when undo is overwritten before a long-running query finishes; remedies include larger undo tablespace, longer UNDO_RETENTION, and committing less often in loops.

Transaction control: COMMIT (makes changes permanent, releases locks, writes redo), ROLLBACK (undoes), SAVEPOINT (partial rollback), and SET TRANSACTION (isolation, read-only, name).

V$TRANSACTION shows active transactions with USED_UBLK and USED_UREC; V$UNDOSTAT and DBA_UNDO_EXTENTS show undo usage and expired/active/unexpired extents.

Autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) commit independently of the caller and are useful for logging that must survive a rollback.

A multitenant container database (CDB) contains one root container (CDB$ROOT), one seed (PDB$SEED, read-only), and zero or more pluggable databases (PDBs); each PDB has its own data dictionary and can be opened or closed independently.

A PDB is created with CREATE PLUGGABLE DATABASE ... ADMIN USER ... or cloned from PDB$SEED; it is opened with ALTER PLUGGABLE DATABASE ... OPEN and saved state with SAVE STATE.

Connect to a PDB with a service name in the connect string, or switch containers with ALTER SESSION SET CONTAINER = pdbname; V$CONTAINERS lists all containers and their open mode.

Common users (created in CDB$ROOT with C## prefix) can connect to any container; local users exist only in one PDB. Common privileges are granted with CONTAINER=ALL.

Plug/unplug: ALTER PLUGGABLE DATABASE ... UNPLUG INTO file.xml, then CREATE PLUGGABLE DATABASE ... USING file.xml; the PDB must be in MOUNT state and compatibility must match.

Application containers (application root + application PDBs) share an application model and metadata; application common objects are created with SHARING = METADATA or DATA.

Resource management across PDBs uses CDB resource plans (CREATE PLAN in CDB$ROOT) with shares and parallel server limits per PDB; PDB lockdown profiles restrict OS access and common user privileges.

Backup in a CDB: RMAN backs up the whole CDB or individual PDBs (BACKUP PLUGGABLE DATABASE); a PDB can be restored without affecting other PDBs when using PDB-level backups and the root is intact.

Local undo (LOCAL_UNDO_ENABLED = TRUE) gives each PDB its own undo tablespace, enabling PDB cloning and point-in-time recovery without affecting the CDB root; shared undo is the default in 12.1.

Client-side files: sqlnet.ora (naming methods, encryption, tracing), tnsnames.ora (net service name -> host:port/service), and listener.ora (server-side protocol addresses and services).

Naming methods in NAMES.DIRECTORY_PATH: tnsnames, ezconnect (host:port/service), ldap, and local naming; EZConnect syntax is host:port/service_name and needs no client configuration.

The listener (lsnrctl start|status|services|reload) listens on a port (default 1521) and hands connections to a server process; dynamic service registration is done by PMON with SERVICE_NAMES and LOCAL_LISTENER.

Static registration uses SID_LIST_LISTENER in listener.ora for databases that are not open (needed for startup via listener); dynamic registration requires the instance to be in NOMOUNT or later.

Connection failures: ORA-12541 (no listener), ORA-12514 (service not registered), ORA-12170 (connect timeout), ORA-12560 (protocol adapter error, often ORACLE_SID or environment).

Dedicated server vs shared server: dedicated gives one server process per session; shared server uses dispatchers (DISPATCHERS parameter) and a shared pool request queue for many sessions.

Connection Manager (CMAN) and Oracle Connection Manager proxy connections for firewall traversal; database links (CREATE DATABASE LINK) connect schemas across databases using a net service name.

TNSPING tests name resolution and listener reachability but not authentication; trcroute and sqlnet tracing (TRACE_LEVEL_CLIENT) diagnose path issues.

Security: Oracle Net encryption/integrity (SQLNET.ENCRYPTION_CLIENT/SERVER), TCPS with SSL/TLS wallets, and valid_node_checking restrict which hosts may connect.

DBMS_SCHEDULER creates jobs with CREATE_JOB (PL/SQL, stored procedure, or executable), schedules (CREATE_SCHEDULE), and programs (CREATE_PROGRAM); jobs run as the creating user or a designated credential.

Job attributes: JOB_TYPE, REPEAT_INTERVAL (calendar syntax such as FREQ=DAILY; BYHOUR=2), ENABLED, AUTO_DROP, and JOB_CLASS for resource consumer group assignment.

Monitor jobs via DBA_SCHEDULER_JOBS, DBA_SCHEDULER_JOB_LOG, DBA_SCHEDULER_JOB_RUN_DETAILS, and DBA_SCHEDULER_RUNNING_JOBS; DBMS_SCHEDULER.RUN_JOB forces a run, STOP_JOB halts it.

Chains (CREATE_CHAIN) define dependency graphs of steps with conditions; use them when steps must run in order or conditionally.

Maintenance windows: the AUTOTASK background jobs (statistics gathering, segment advisor, SQL tuning advisor) run in predefined windows managed by DBMS_AUTO_TASK_ADMIN.

Data Pump: expdp/impdp with DIRECTORY objects, PARALLEL, CONTENT=DATA_ONLY|METADATA_ONLY, and FLASHBACK_SCN for consistency; use REMAP_SCHEMA and REMAP_TABLESPACE to relocate objects.

SQL*Loader (sqlldr) loads external files with a control file; direct path bypasses the buffer cache and is faster but does not fire triggers. External tables (ORGANIZATION EXTERNAL) query flat files through SQL.

Transportable tablespaces move data files between databases with DBMS_TTS.TRANSPORT_SET_CHECK and ALTER TABLESPACE ... READ ONLY; Data Pump can perform transportable tablespace export/import.

Oracle GoldenGate and Data Guard provide replication/standby; Data Guard redo transport (SYNC/ASYNC) and apply (MRP) are configured with broker commands on the primary and standby.