Oracle DBA LAB Questions

 Oracle DBA LAB Questions


1) Installation, Network & Instance Startup (15)

  1. Install Oracle 19c Grid Infrastructure + RDBMS (silent mode). Validate inventory and oraInst.loc.
  2. Configure listener.ora and tnsnames.ora for multiple services; prove service‑based failover.
  3. Database fails to start with ORA-01078 / LRM-00109 — fix spfile/pfile mismatch and boot.
  4. Change memory policy from AMM to ASMM and validate PGA/SGA behavior.
  5. Start a CDB in MOUNT, open only one PDB; keep others MOUNTED.
  6. Fix login storms from app: enable server‑side tracing for a specific service with oradebug setospid.
  7. Listener accepts connections but DB rejects with ORA-12514 — map the service correctly.
  8. Database won’t register with listener automatically — fix LOCAL_LISTENER/REMOTE_LISTENER.
  9. Set up passwordless OS authentication for sysdba; audit who used it.
  10. Diagnose slow logon due to DNS — implement EZCONNECT and compare.
  11. Configure Connection Manager (CMAN) and route traffic via CMAN for a PDB service.
  12. Force dedicated vs shared server; demonstrate impact on session count.
  13. Implement server parameter change via ALTER SYSTEM … SCOPE variants and verify persistence.
  14. Configure ADRCI purging rules; prove incident packaging.
  15. Simulate FRA‑full on startup; recover by moving archive logs.

🟦 2) ASM, Storage, Filesystem & FRA (15)

  1. Create ASM disk groups (DATA, FRA) with normal redundancy; move datafiles from filesystem to ASM.
  2. Add disks to DATA; rebalance; monitor v$asm_operation.
  3. Drop an ASM disk and recover redundancy while DB stays online.
  4. Move online redo logs to ASM; fix ORA-00313/00312 on wrong path.
  5. Configure and test Block Change Tracking; validate RMAN’s incremental speed.
  6. Diagnose FRA pressure; implement policy to auto‑delete backups safely.
  7. Create bigfile tablespace and compare extent map vs smallfile.
  8. Convert a heap object to ASSM locally managed; validate segment space usage.
  9. Detect I/O latency at ASM vs OS; correlate with v$eventmetric.
  10. Enable Hybrid Columnar Compression (HCC) on ACFS (where supported); measure compression.
  11. Toggle filesystemio_options and test async/direct I/O behavior.
  12. Diagnose ORA-15032/15063 while dropping diskgroup; fix resource usage.
  13. Move TEMP to faster disk and right size TEMP files for parallel sorts.
  14. Simulate write errors on FRA; reconfigure archive destinations with LOG_ARCHIVE_DEST_n.
  15. Validate DB_RECOVERY_FILE_DEST_SIZE usage; clear obsolete files.

🟦 3) RMAN Backup/Restore, PITR & Block Media Recovery (20)

  1. Implement full + incr level 1 RMAN strategy with retention policy; verify REPORT OBSOLETE.
  2. Perform PITR of one PDB to timestamp without affecting other PDBs.
  3. Test block media recovery for a single corrupt block; prove v$database_block_corruption is cleared.
  4. Restore SPFILE and CONTROLFILE to new host; mount and recover.
  5. Clone database to a new server via RMAN duplicate FROM ACTIVE DATABASE.
  6. Validate TDE backups — restore onto a clean server with wallet/certs.
  7. Recover from missing archived logs using incremental backup strategy.
  8. Implement backup compression and compare throughput/CPU.
  9. Execute crosscheck and fix catalog inconsistencies.
  10. Do a tablespace point-in-time recovery (TSPITR) for a single app tablespace.
  11. Protect controlfile autobackup; simulate total loss of datafiles.
  12. Create image copies + RECOVER COPY roll forward nightly; switch to copy.
  13. Restore to a lower patch home; fix compatibility mistakes.
  14. Validate backup performance: SBT vs disk, multiple channels, section size.
  15. Use catalog recovery area to register orphaned backups.
  16. Prove Flashback Database can recover from user error faster than PITR.
  17. Test guaranteed restore points; rollback after DDL batch.
  18. RMAN DUPLICATE FOR STANDBY to seed Data Guard quickly.
  19. Drill a disaster recovery full restore on an empty host; measure RTO/RPO.
  20. Verify FOREIGN ARCHIVE redo availability across sites.

🟦 4) Data Guard & DGMGRL (Physical/Logical/FSFO) (25)

  1. Build Physical Standby (no Data Guard Broker); manage via SQL only.
  2. Convert to Data Guard Broker (DGMGRL); enable Fast-Start Failover with an observer.
  3. Perform switchover; validate services failover; fail back.
  4. Simulate log transport gap; resolve with incremental SCN rollforward.
  5. Diagnose ORA-16810 / ORA-16826 status errors; fix.
  6. Create Far Sync instance; measure latency improvements.
  7. Configure real-time apply and validate APPLY LAG near zero.
  8. Standby open read‑only with active sessions; test ADG‑aware services.
  9. Stop redo apply; perform reports; resume and catch up.
  10. Reinstate a previously failed primary using flashback.
  11. Resolve ARCH corruption at standby; fetch missing logs manually.
  12. Promote standby without broker; re‑create new standby from promoted primary.
  13. Logical standby setup; replicate a subset of tables; test DDL capture.
  14. Fix LNS/RFS handshake issues; verify net timeout parameters.
  15. Force‑redo transport compression; monitor bandwidth usage.
  16. Change protection mode (Max Perf/Avail/Prot) and observe commit latency.
  17. Use DBMS_ROLLING for rolling upgrade with near‑zero downtime.
  18. Test DGMGRL observer auto‑reinstate; capture logs.
  19. Optimize redoTransportSettings for WAN; measure redo rates.
  20. Detect and repair lost write symptoms between primary/standby.
  21. Automate read‑preferred routing to standby via services.
  22. Fix ORA-10458/10459 errors during apply.
  23. Archive gap resolution with FAL_CLIENT/FAL_SERVER.
  24. Test Snapshot Standby for UAT; convert back cleanly.
  25. Validate FSFO thresholds and observer failover history.

🟦 5) RAC / Grid Infrastructure (Clusterware, CRS, Services) (25)

  1. Install 2‑node RAC; validate SCAN listeners and VIPs.
  2. Start/stop cluster resources using crsctl and srvctl; document dependencies.
  3. Relocate a RAC service to another instance; validate TAF/FAN behavior.
  4. Diagnose gc cr request and gc buffer busy waits; tune interconnect.
  5. Configure services with -failovertype TRANSACTION and connection load balancing.
  6. Simulate node eviction; analyze OSWatcher/TFA and diag/ traces.
  7. Fix GNS/DNS misconfig causing SCAN resolution issues.
  8. Test Transparent Application Failover (TAF) and runtime load balancing.
  9. Recreate OCR backups; test OCR restore.
  10. Patch GI home using opatchauto; rollback scenario.
  11. Add a new node to a RAC cluster; then remove it gracefully.
  12. Validate HAIP for private interconnect redundancy.
  13. Convert single‑instance to RAC One Node; online relocate.
  14. Fix ASM instance not starting on one node; diagnose with alert_+ASM.
  15. Simulate voting disk loss and recovery steps.
  16. Configure srvctl start dependencies for services and PDBs.
  17. Patch rolling (GI + DB) with zero database downtime; document.
  18. Implement application continuity for RAC; verify replay success rate.
  19. RAC instance crash during heavy DDL — review KDUMP and CRSD logs.
  20. Capture and tune GC wait classes with AWR compare reports.
  21. Validate CPU/memory NUMA placement for RAC instances.
  22. Test leaf node resource profiles (where applicable).
  23. Repair HAS stack failing to start on one node.
  24. Simulate interconnect bandwidth saturation; remediate.
  25. Move online redo threads between ASM diskgroups.

🟦 6) Multitenant (CDB/PDB), Upgrade & Patching (20)

  1. Create CDB with multiple PDBs; set services per PDB; test connect strings.
  2. Clone a PDB locally and via database link (remote clone).
  3. Plug a non‑CDB as PDB; fix non‑CDB to PDB compatibility issues.
  4. Use PDB lockdown profiles for least privilege.
  5. Implement per‑PDB resource manager plans; prove isolation.
  6. Move PDB between CDBs using TDE; manage wallets.
  7. Upgrade 12.2 → 19c using AutoUpgrade; analyze config and scripts.
  8. Patch with RU/RUR; rollback test; read opatch lsinventory.
  9. Convert non‑CDB backup catalog to CDB; validate RMAN metadata.
  10. Automate opening PDBs on restart; test triggers vs PDB SAVE STATE.
  11. PDB PITR using RMAN recover pluggable database to SCN.
  12. Diagnose PDB ORA-65040 open errors; fix stale undo segments.
  13. Transport PDB across endian change using XTTS v5 style flow.
  14. Validate PDB hot clone under load; fix blocking sessions.
  15. Set default temp for a PDB and verify local TEMP usage.
  16. Migrate users/roles/privs into a PDB securely.
  17. Implement AWR per‑PDB snapshots; view in AWR Warehouse.
  18. Fix invalid objects after patch/upgrade with utlrp.
  19. Automate quarterly patch runbook with pre/post checks.
  20. Validate application grants do not require CDB$ROOT privileges.

🟦 7) Performance (AWR/ASH/ADDM), SQL Tuning & Optimizer (25)

  1. Capture AWR during a performance event; produce a delta report and isolate top wait classes.
  2. Use ASH to find “on‑CPU” vs “waiting” sessions; correlate to SQL_IDs.
  3. Diagnose log file sync spikes; tune commit batching/log buffer.
  4. Identify db file sequential/scattered read root causes; adjust indexing and I/O.
  5. Drill cursor sharing and bind peeking; apply SQL Plan Baselines.
  6. Use SQLTXPLAIN (SQLT) or SPA (SQL Performance Analyzer) to fix a regression.
  7. Create SQL Profile / SPM Baseline for a regressed plan; verify capture/evolution.
  8. Detect cardinality estimate issues; experiment with extended stats/histograms.
  9. Pin point latch vs enqueue contention; propose changes.
  10. Prove benefit of result_cache on repetitive query.
  11. Track hard parse storms; fix session cursor cache.
  12. Use dbms_xplan.display_cursor with ALLSTATS LAST to verify row counts and time.
  13. Optimize a star transformation with bitmap indexes; compare plans.
  14. Analyze PX (parallel execution) skew; fix DOP, PQ_DISTRIBUTE.
  15. Enable adaptive features properly (19c) and measure.
  16. Compare optimizer_features_enable impacts; lock a stable plan.
  17. Analyze SQL Monitor active reports for long‑running SQL.
  18. Reduce buffer busy waits hot block contention via partitioning or ITL.
  19. Tune library cache lock/pin waits; test cursor sharing settings.
  20. Find top segments by logical/physical reads; re‑layout storage.
  21. Reduce gc (Global Cache) waits in RAC by service‑based routing.
  22. Stabilize a batch using dbms_stats.set_table_prefs; manage feedback.
  23. Validate plan change cause after stats gather; test NO_INVALIDATE.
  24. Diagnose frequent parse time cpu spikes; fix misuse of literals.
  25. Build a performance baseline and daily diff reports.

🟦 8) Undo/Redo, Space Management & Segment Ops (15)

  1. Diagnose “snapshot too old” with ORA-01555; fix undo sizing and retention.
  2. Recreate undo tablespace online; fix corrupted undo segment.
  3. Analyze ITL wait and enq: TX - allocate ITL entry; increase INITRANS/space.
  4. Shrink a high‑water mark; compare SHRINK SPACE vs MOVE.
  5. Rebuild a massive partitioned index online; maintain constraints.
  6. Implement SECUREFILE LOB and measure dedup/compress.
  7. Identify top segments with row chaining; adjust PCTFREE and block size.
  8. Diagnose freelist vs ASSM behavior on legacy tablespaces.
  9. Transport a tablespace between databases (same endian).
  10. Convert dictionary‑managed tablespace to locally managed.
  11. Fix ORA-01652 (unable to extend temp segment) without restart.
  12. Reclaim space after large delete; evaluate DBMS_SPACE findings.
  13. Deal with ORA-30036 (undo tablespace) during large operations.
  14. Validate auto segment space management behavior under high DML.
  15. Create Heat Map/ILM policies for tiering cold segments.

🟦 9) Data Pump, GoldenGate & Integration (15)

  1. Export one PDB’s schema with expdp; import into another PDB; map remap_schema/tablespace.
  2. Parallelize expdp/impdp; tune streams; resolve ORA‑390xx errors.
  3. Use transportable tablespaces via Data Pump; confirm metadata only.
  4. GoldenGate uni‑directional replicat; handle DDL replication.
  5. Resolve OGG data drift with @marker and logdump.
  6. Configure Integrated Extract; register with database.
  7. Recover OGG after replicate abends; avoid gaps.
  8. Compare Data Guard vs GoldenGate for zero‑downtime migrations.
  9. Migrate schema with LOBs; ensure LOB retention and chunking.
  10. Mask sensitive data during impdp; use REMAP_DATA or DBMS_REDACT.
  11. Split a huge table export using partition filters; verify constraints.
  12. Use network‑link import to avoid staging.
  13. Diagnose ORA-31693/39126 during impdp; fix invalid objects.
  14. Validate supplemental logging for OGG.
  15. Move OGG trails to ASM/ACFS and benchmark.

🟦 10) Security, Auditing, TDE & Vaults (15)

  1. Enable Unified Auditing; capture logon, DDL, and privileged operations; purge policy.
  2. Implement TDE in CDB/PDB with per‑PDB keystores; test wallet auto‑open.
  3. Lock down PUBLIC grants; audit risky system privileges.
  4. Create Database Vault realms; restrict DBA from sensitive schemas.
  5. Configure Password Verify Function and profiles; test expirations/lockouts.
  6. Implement Data Redaction; prove effectiveness in app queries.
  7. Use Virtual Private Database (VPD) policy for row‑level access.
  8. Detect and remediate UTL_HTTP/UTL_FILE misuse.
  9. Rotate TDE master keys; ensure backup/restore works post‑rotation.
  10. Capture Fine Grained Auditing (FGA) on a critical table.
  11. Validate TLS/TCPS for listener; configure wallets; test mutual auth.
  12. Block lateral movement by restricting external jobs/hosts.
  13. Secure SYS access paths; enforce strong auditing on SYSDBA.
  14. Inventory and remove unused database links.
  15. Verify Oracle roles/privileges drift with a baseline job.

🟦 11) OEM/Cloud Control, Observability & Automation (10)

  1. Deploy OEM agents to RAC and DG; fix blocked ports/firewall.
  2. Create blackouts and maintenance windows; stop false alerts.
  3. Build custom metrics (redo rate, log file sync) and alerts.
  4. Automate AWR extraction and store in AWR Warehouse.
  5. Create Compliance Standard and evaluate targets for drift.
  6. Build a runbook to capture SR logs (TFA/ADRCI/OSW).
  7. Integrate OEM notifications with Teams/Slack via script.
  8. Automate patch prechecks and post‑checks; archive results.
  9. Build a daily health report (invalids, space, FRA, DG lag, AWR deltas).
  10. Simulate Sev‑1 and run incident lifecycle end‑to‑end.

🟦 12) Cloud (OCI/Azure/AWS) & Exadata (10)

  1. Provision OCI DBCS and enable Data Guard across ADs.
  2. Configure Autonomous Database cloning and refreshable clones.
  3. Capture AWR on Exadata; analyze storage cell waits (cell smart …).
  4. Test Exadata Hybrid Columnar effects on scans.
  5. Validate OCPU scale up/down with minimal disruption.
  6. Encrypt at rest with OCI Vault; rotate keys; validate restore.
  7. Migrate on‑prem → OCI with ZDM (Zero Downtime Migration).
  8. Enable Data Guard Broker in DBCS and test FSFO.
  9. Validate smart scan eligibility for specific queries.
  10. Compare Exadata offload vs non‑offload behavior with plans.

Comments

Post a Comment