Oracle DBA Admin- Production Readiness Assignment (L0/L1)

By /

Production-Grade Scenario Assignments for L0 (Associate DBA) & L1 (DBA Consultant)

The Setup: Assume you’ve just joined the DBA team at ABCMerchant Retail Systems, an e-commerce company running its order-processing platform on a single-instance Oracle 19c non-CDB database on RHEL 8.

  • DB Name / SID: PRODDB
  • OS: Linux (RHEL 8), user oracle, group oinstall/dba
  • ORACLE_HOME: /u01/app/oracle/product/19.0.0/dbhome_1
  • Data/FRA: /u02/oradata/PRODDB, /u03/fra/PRODDB
  • Storage: Standard filesystem (no ASM) — keeps focus on core file management skills

You’ll work through 10 modules, each framed as a real ticket from the Service Desk. L0 tasks build your operational muscle memory (safe, guided, checklist-driven). L1 tasks simulate what happens when things break, scale, or need architectural judgment — you’ll need to investigate, decide, and justify.

Each module ends with Deliverables (what you must submit/demonstrate) so this feels like real consulting work, not just practice queries.

Module 1 — Control File Management

🎫 Ticket #INC-1001: “Storage team accidentally deleted one control file copy during a migration. DB is currently down. CAB wants a permanent fix so this never happens again.”

L0 Tasks

  1. Identify current control file locations from SPFILE/PFILE and v$controlfile.
  2. Take a manual backup of the control file to trace (ALTER DATABASE BACKUP CONTROLFILE TO TRACE) and a binary backup.
  3. Restore the missing control file copy from a surviving multiplexed copy (cold copy method) and bring the DB back to OPEN.
  4. Verify multiplexing is active across at least 3 different mount points/disks.
  5. Document the control file record sections and their purpose (v$controlfile_record_section).

L1 Tasks

  1. Diagnose why the DB wouldn’t mount after the deletion — read and interpret the alert log error stack.
  2. Design a control file multiplexing strategy resilient to a single disk/failure, and implement it live (add/relocate copies without downtime, using ALTER SYSTEM SET CONTROL_FILES + restart).
  3. Rebuild a control file from scratch using CREATE CONTROLFILE with the trace file, handling a scenario where one redo log member is also missing.
  4. Write a runbook: “Control File Loss — RTO under 15 minutes” including RMAN-based recovery as an alternative path.
  5. Explain the impact of control file record section overflow (e.g., MAXLOGHISTORY, RMAN backup retention causing record wraparound) and how to preempt it in a high-transaction OLTP system.

Deliverables: Recovery runbook, before/after v$controlfile output, RCA document.

Module 2 — Redo Log File Management

🎫 Ticket #INC-1014: “Black Friday load test is generating log switches every 20 seconds. Alert log shows ‘Checkpoint not complete’ warnings. Performance team is blaming the DBA layer.”

L0 Tasks

  1. Query current redo log group/member layout and sizes (v$log, v$logfile).
  2. Calculate log switch frequency from the alert log over the last 24 hours.
  3. Add 3 new redo log groups with 2 multiplexed members each, sized appropriately.
  4. Drop the old undersized groups safely (handle INACTIVE vs CURRENT status correctly).
  5. Enable/verify archive log mode is not blocking switches unnecessarily.

L1 Tasks

  1. Perform full root-cause analysis of “checkpoint not complete” — correlate v$log_history, DB_WRITER throughput, and buffer cache checkpoint stats.
  2. Right-size redo logs and group count based on peak redo generation rate (compute from v$logfile/AWR redo size metric) to target log switches every 15–20 minutes.
  3. Simulate and recover from a loss of a redo log group with an inactive log vs. loss of the current online redo log group (two separate DR drills) — document differing recovery paths (one is a simple drop/recreate, the other may need incomplete recovery).
  4. Evaluate whether FAST_MZ_COMMIT/LOG_BUFFER sizing tuning is warranted, and justify with evidence.
  5. Present a capacity plan to leadership: redo growth trend for the next 2 sales seasons.

Deliverables: Sizing justification doc, DR drill logs, AWR-backed root cause report.

Module 3 — Tablespace Management (SYSTEM, SYSAUX, UNDO, TEMP, USERS)

🎫 Ticket #CHG-2005: “New ‘Loyalty Rewards’ module is going live next sprint. It needs its own storage tier with quotas, and SYSAUX has been silently growing 2GB/week for 3 months — nobody knows why.”

L0 Tasks

  1. Report current size, used%, and autoextend status of every tablespace (SYSTEM, SYSAUX, UNDO, TEMP, USERS).
  2. Create a new locally-managed, ASSM tablespace LOYALTY_DATA with defined initial size, autoextend, and max size caps.
  3. Set default tablespace and quota for a new application schema.
  4. Resize USERS tablespace datafile up and confirm no fragmentation issues.
  5. Take a tablespace offline/online correctly for a maintenance window (non-SYSTEM).

L1 Tasks

  1. Investigate SYSAUX growth using v$sysaux_occupants — identify the top consumer (commonly AWR/SM/OPTSTAT) and implement a retention/purge policy.
  2. Design the UNDO tablespace sizing/UNDO_RETENTION strategy to survive a known 45-minute nightly batch job without ORA-01555 Snapshot too old.
  3. Convert a dictionary-managed legacy tablespace (simulate one) to locally managed with minimal downtime.
  4. Build a tablespace capacity forecasting model (growth rate → days-to-full) for all business-critical tablespaces and set proactive alert thresholds.
  5. Justify (with trade-offs) whether LOYALTY_DATA should use BIGFILE vs SMALLFILE, and whether compression should be enabled.

Deliverables: Tablespace inventory report, SYSAUX RCA + purge script, capacity forecast spreadsheet/summary.

Module 4 — Data File Management

🎫 Ticket #INC-1030: “A datafile on /u02 shows ‘file not found’ in the alert log after a botched OS-level restore. App is throwing ORA-01157 on order inserts.”

L0 Tasks

  1. List all datafiles with size, tablespace, and autoextend settings (v$datafile, dba_data_files).
  2. Add a new datafile to an existing tablespace approaching capacity.
  3. Move/rename a datafile to a new mount point using ALTER DATABASE MOVE DATAFILE (online, 19c feature).
  4. Enable autoextend with a sensible MAXSIZE cap (never UNLIMITED blindly) and explain why.
  5. Verify datafile header checkpoint SCNs are in sync (v$datafile_header).

L1 Tasks

  1. Recover the missing/corrupted datafile scenario end-to-end: take DB tablespace offline, restore from RMAN backup, apply archived redo, bring online — with minimal application downtime.
  2. Diagnose and fix a case of datafile fragmentation impacting a full table scan-heavy reporting job.
  3. Design a datafile layout strategy separating high-I/O tablespaces (UNDO, TEMP) from data tablespaces across mount points for I/O contention avoidance.
  4. Handle an ORA-27072 (OS I/O error) scenario — differentiate between permissions, disk full, and corruption causes using OS + DB evidence.
  5. Build a preventive control: script that alerts when any datafile’s autoextend is disabled or nearing MAXSIZE.

Deliverables: Recovery runbook with timestamps, fragmentation analysis, preventive monitoring script.

Module 5 — Temp File Management

🎫 Ticket #INC-1042: “Nightly ETL job failed with ORA-1652: unable to extend temp segment. Finance is escalating — month-end close is blocked.”

L0 Tasks

  1. Report current TEMP tablespace size, temp file count, and usage (v$tempfile, v$temp_space_header).
  2. Add a new tempfile to relieve immediate pressure.
  3. Resize an existing tempfile and enable autoextend with a max cap.
  4. Identify currently running sessions consuming TEMP space (v$sort_usage/v$tempseg_usage).
  5. Shrink an oversized tempfile safely post-incident.

L1 Tasks

  1. Perform full RCA on the ETL failure — correlate the offending SQL’s execution plan (excessive sort/hash joins) with TEMP consumption via v$sql_workarea.
  2. Recommend and implement a fix: SQL tuning (e.g., PGA_AGGREGATE_TARGET/WORK_AREA_SIZE_POLICY review) vs. structural TEMP sizing — justify the choice with cost/benefit.
  3. Design a multi-tempfile TEMP tablespace group strategy for parallel ETL workloads to avoid single-file contention.
  4. Simulate and recover from a scenario where the default temporary tablespace itself is corrupted — create a new one, reassign as default, and safely drop the old one.
  5. Build a proactive TEMP usage dashboard query (top sessions, top SQL, tempfile fill %) for the NOC team.

Deliverables: RCA report with execution plan evidence, TEMP monitoring query pack.

Module 6 — Storage Utilization Management

🎫 Ticket #CHG-2010: “CFO wants a storage cost-optimization report. Mount /u02 is 92% full and finance won’t approve new disk purchase without proof it’s needed.”

L0 Tasks

  1. Produce a full storage utilization report: OS-level (df -h) vs. DB-level (allocated vs. used) for every mount point.
  2. Identify tablespaces with high allocated-but-unused space (candidates for RESIZE/shrink).
  3. Run dbms_space to check for reclaimable High Water Mark (HWM) space in top 5 large tables.
  4. Perform a segment shrink (SHRINK SPACE) on a table with row movement enabled.
  5. Identify and report on orphaned/unused datafiles, old trace files, and stale archive logs consuming OS space.

L1 Tasks

  1. Build a full storage governance report: growth trend per tablespace over last 90 days, projected exhaustion date, and reclaimable space via HWM analysis (dbms_space.space_usage).
  2. Identify and safely reorganize a table with severe row chaining/migration impacting both space and performance.
  3. Propose and implement table/index compression (OLTP or basic, per license) for cold historical data (e.g., ORDERS_ARCHIVE) — quantify space savings.
  4. Design a data lifecycle/archival policy (partition + move to cheaper tier or purge) for a rapidly growing audit table.
  5. Present the CFO report: current state, cost of doing nothing, savings from reclamation vs. cost of new disk — a genuine business case, not just a query dump.

Deliverables: Storage governance report (business-readable), space reclamation before/after metrics, archival policy proposal.

Module 7 — Users, Profiles & Privileges Management

🎫 Ticket #REQ-3001 / SEC-AUDIT-07: “New DevOps engineer needs read-only prod access. Separately, a security audit flagged 3 accounts with unlimited password validity and DBA role granted ‘temporarily’ 8 months ago.”

L0 Tasks

  1. Create a new user with a strong default profile (password complexity, expiry, lockout).
  2. Grant precise, least-privilege access (SELECT-only on specific schema objects, not SELECT ANY TABLE).
  3. Create and apply a custom profile with PASSWORD_LIFE_TIME, FAILED_LOGIN_ATTEMPTS, IDLE_TIME, resource limits.
  4. Lock/expire an account, and separately, safely drop a decommissioned user’s objects.
  5. List all users with DBA/SYSDBA-equivalent privileges for the audit.

L1 Tasks

  1. Conduct a full privilege audit: find all grants of powerful system privileges (SELECT ANY TABLE, DROP ANY TABLE, roles with ADMIN OPTION) and least-privilege-remediate them without breaking application functionality.
  2. Design a role-based access control (RBAC) model: functional roles (APP_READONLY, APP_READWRITE, APP_ADMIN) mapped to actual business need, replacing direct grants.
  3. Investigate and remediate the “temporary DBA grant” account — trace dba_audit_trail/unified audit to determine actual usage before revoking.
  4. Implement password verify function enforcement and account lockout monitoring; simulate a brute-force attempt and confirm lockout triggers.
  5. Write a security hardening checklist aligned to CIS Oracle 19c benchmark basics (default accounts, PUBLIC grants, UTL_* package exposure).

Deliverables: RBAC design doc, privilege audit findings + remediation script, hardening checklist.

Module 8 — Indexing Management

🎫 Ticket #INC-1055: “The order-search API, which used to respond in 200ms, now takes 9 seconds. It went live 6 months ago and nobody’s touched the schema since.”

L0 Tasks

  1. Identify existing indexes on the ORDERS and ORDER_ITEMS tables (dba_indexes, dba_ind_columns).
  2. Run EXPLAIN PLAN/DBMS_XPLAN on the slow query and identify full table scans.
  3. Create an appropriate B-tree index and confirm the plan change.
  4. Identify and report unused indexes (v$object_usage or index monitoring) that may be adding write overhead for no read benefit.
  5. Rebuild a fragmented index (ALTER INDEX ... REBUILD) and verify improvement.

L1 Tasks

  1. Full RCA: was it stale statistics, index fragmentation, a plan flip from bind variable peeking, or genuine data growth? Prove it with evidence (AWR SQL history, v$sql_plan).
  2. Design a composite/covering index strategy for the search query balancing read speed vs. insert/update overhead on a high-write OLTP table.
  3. Evaluate and justify (or reject) a function-based index or partitioned index for a specific reporting query pattern.
  4. Build an ongoing index health process: identify unused indexes, duplicate/redundant indexes, and invisible-index testing strategy before dropping anything in prod.
  5. Present a “before vs after” performance case study with quantified business impact (response time, resource savings).

Deliverables: Performance RCA with plan evidence, indexing strategy doc, before/after benchmark.

Module 9 — Networking (Listener, TNS, SQL*Net)

🎫 Ticket #INC-1067: “App servers in the new DR site can’t connect to PRODDB — ORA-12541 intermittently, and one app team says they get ORA-12154 only from their server.”

L0 Tasks

  1. Locate and review listener.ora, tnsnames.ora, sqlnet.ora — document current configuration.
  2. Start/stop/reload the listener and verify status (lsnrctl status).
  3. Add a new TNS entry for the DR app servers and test connectivity (tnsping, sqlplus).
  4. Fix a broken tnsnames.ora entry causing ORA-12154: TNS:could not resolve the connect identifier.
  5. Confirm the listener is registered with the correct service name (dynamic vs. static registration).

L1 Tasks

  1. Diagnose intermittent ORA-12541 — check listener log for crash/restart patterns, OS port/firewall issues, and SQLNET.INBOUND_CONNECT_TIMEOUT misconfigurations.
  2. Configure static service registration in listener.ora for DR failover scenarios (where the DB may be down and dynamic registration can’t help).
  3. Harden sqlnet.ora: implement TCP.VALIDNODE_CHECKING, restrict listener access, disable weak configurations, and set connection timeouts to mitigate a slowloris-style DoS risk found in a pen-test.
  4. Design and implement a listener failover/load balancing setup using multiple listener endpoints for the app tier.
  5. Set up SSL/TLS or at minimum verify and document current network encryption posture for data-in-transit, flag gaps to security team.

Deliverables: Network topology + config diagram, hardened sqlnet.ora/listener.ora with change justification, pen-test remediation summary.

Module 10 — Archive Log Management

🎫 Ticket #INC-1080 (P1): “FRA is 98% full at 2 AM. Database has hung — sessions can’t commit. RMAN backups haven’t run in 4 days due to a scheduler failure.”

L0 Tasks

  1. Check archive log mode, destination, and current usage (v$flash_recovery_area_usage, archive log list).
  2. Free up space by deleting archived logs already backed up (safe method, via RMAN — not rm).
  3. Increase DB_RECOVERY_FILE_DEST_SIZE as an immediate mitigation.
  4. Verify the DB resumes normal operation post-cleanup (no more “archiver stuck” hang).
  5. Confirm/restart the RMAN backup schedule (cron/dbms_scheduler).

L1 Tasks

  1. Full incident response: stabilize the hang first (safe, backed-up-log deletion), then RCA why the scheduler silently failed for 4 days (log review, alerting gap).
  2. Design an archive log retention policy balancing recovery window (e.g., 7-day PITR requirement) against FRA disk cost.
  3. Build a monitoring/alerting mechanism (threshold-based, e.g., 80%/90% FRA usage) that pages the on-call DBA before it becomes a P1.
  4. Simulate a full point-in-time recovery (PITR) using archived logs — restore to a point 2 hours before a bad batch job corrupted data, with full validation.
  5. Write the post-incident review (PIR): timeline, root cause, customer impact, and 3 concrete preventive actions — presentable to leadership.

Deliverables: Incident timeline, PITR recovery proof, FRA monitoring implementation, formal PIR document.

🏆 Capstone Challenge (Combine Everything)

🎫 Ticket #CRISIS-9999: “Simulated Disaster Day. At 3 AM, a junior sysadmin ran a cleanup script that deleted files across /u02 and /u03. You have until 8 AM (business open) to bring PRODDB back with zero data loss, then present a full ‘never again’ hardening plan covering all 10 areas above.”

  • Diagnose what’s actually broken (control files? datafiles? archive logs? all of it?).
  • Recover using the correct sequence (control file → datafile → redo → open).
  • Validate data integrity post-recovery.
  • Deliver a single consolidated Production Hardening & Runbook Package referencing every module
Scroll to Top