Oracle Database Administration I study plan

Map the Oracle instance memory structures (SGA components such as buffer cache, shared pool, large pool, redo log buffer) and name the role of each background process (DBWn, LGWR, CKPT, SMON, PMON, MMON, ARCn)

Trace the flow of a user query from client to server process to shared pool parse to buffer cache fetch, writing the sequence out step by step

Start a database instance with STARTUP, then query V$INSTANCE, V$SGA, and V$BGPROCESS to confirm each component is present and note its current value

Distinguish dedicated from shared server connections and identify which dynamic views reveal each mode

Explain the difference between logical storage (tablespace, segment, extent, block) and physical storage (data file, OS block) and map one object through all four levels

Create a smallfile tablespace with explicit data file size, AUTOEXTEND settings, and uniform extent allocation, then query DBA_TABLESPACES and DBA_DATA_FILES to verify the result

Use DBA_SEGMENTS and DBA_EXTENTS to locate where a specific table's blocks live and calculate its space consumption

Interpret segment space management (ASSM vs manual) and explain how it affects block-level free space tracking

Read a tablespace's used and free space from DBA_FREE_SPACE and DBA_TABLESPACE_USAGE_METRICS and describe when each view is appropriate

Create and alter tables with the main Oracle data types and constraints (NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY), including enabling and disabling constraints

Build indexes (B-tree and bitmap) and demonstrate with an execution plan when the optimizer chooses each

Manage schema objects end to end: rename, truncate, drop with and without PURGE, and recover a dropped table from the recycle bin

Load rows with INSERT, INSERT ... SELECT, and direct-path behavior, then compare the resulting segment sizes

Use DBA_OBJECTS and USER_OBJECTS to confirm object status after each change

Create users with explicit default and temporary tablespaces, quota limits, and a non-default profile, then verify with DBA_USERS and DBA_TS_QUOTAS

Grant system and object privileges, test the WITH ADMIN OPTION and WITH GRANT OPTION behaviors, and revoke each to observe the difference

Assign roles, set a default role, and demonstrate how a password-protected role changes access

Configure a profile with password lifetime, reuse limits, and failed-login lockout, then trigger a lockout and unlock the account as an administrator

Query DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS to audit exactly what a test user can do

Describe the stages of instance startup (NOMOUNT, MOUNT, OPEN) and name what is read at each stage

Perform a clean SHUTDOWN IMMEDIATE and a SHUTDOWN ABORT, then explain the recovery implications of each and what happens on the next startup

Use the DBCA in silent or interactive mode to create a database, or walk through the CREATE DATABASE statement components and explain each clause

Inspect and change key initialization parameters with ALTER SYSTEM, distinguishing SCOPE=MEMORY, SPFILE, and BOTH

Read the alert log and trace the startup sequence entries, matching each message to the stage it represents

Explain undo segment behavior across a transaction and query V$TRANSACTION and V$UNDOSTAT to observe undo consumption

Set UNDO_RETENTION and demonstrate the effect of a small undo tablespace on a long-running query, capturing the ORA-01555 error

Trace redo generation: perform DML, force a log switch with ALTER SYSTEM SWITCH LOGFILE, and inspect V$LOG and V$LOG_HISTORY

Describe commit and rollback mechanics, including what LGWR writes at commit and how checkpoints advance the checkpoint position

Identify the differences between the three redo log modes and explain how each affects durability

Configure the database in ARCHIVELOG mode and confirm with V$DATABASE and V$ARCHIVED_LOG

Take a full RMAN backup, list it with LIST BACKUP, and validate it with RESTORE ... VALIDATE

Perform a point-in-time recovery of a tablespace after simulated data loss and document each RMAN command used

Use Flashback Query and Flashback Table to recover from a mistaken DML statement, and explain what undo retention each requires

Compare RMAN backup types (full, incremental, image copy) and state when each is appropriate

Configure a local naming entry in tnsnames.ora and a listener in listener.ora, then connect with sqlplus using the alias and diagnose one deliberately broken entry with tnsping and lsnrctl status

Use the Automatic Workload Repository: generate an AWR snapshot, produce a report, and identify the top wait events in it

Query V$SESSION, V$SQL, and V$ACTIVE_SESSION_HISTORY to find the top SQL by elapsed time and explain what the ADDM advisor would flag

Locate and interpret the alert log, trace files, and the Automatic Diagnostic Repository structure, and run a health check with the appropriate ADR command

Review the automated maintenance tasks (optimizer statistics, segment advisor, automatic SQL tuning) and state their default maintenance windows