Oracle Database Interview Questions

 Oracle Database Interview Questions


1) Oracle Architecture & Internals (Core) — 25

  1. Explain Oracle instance vs database and the lifecycle of each.
  2. What are the major background processes (PMON, SMON, DBWn, LGWR, CKPT, MMON) and their roles?
  3. Describe SGA components (Shared Pool, Buffer Cache, Large Pool, Redo Log Buffer, Java Pool, Streams Pool).
  4. How does PGA differ from UGA? Where is UGA stored in dedicated vs shared server mode?
  5. Explain redo generation, LGWR behavior, and commit processing.
  6. What is a checkpoint? How does CKPT coordinate file headers and controlfiles?
  7. Describe buffer cache management (LRU, touch count, dirty buffers, checkpoints).
  8. What is SCN? How is it advanced and used during recovery/consistency?
  9. How does Oracle ensure read consistency using UNDO?
  10. What are ITL slots? How do they affect concurrency and TX enqueues?
  11. Explain row chaining vs row migration and their performance implications.
  12. What are latch vs lock vs enqueue? Give examples and troubleshooting steps.
  13. Draw the parse → optimize → execute pipeline; where do hard/soft parses happen?
  14. How are library cache and data dictionary cache maintained?
  15. What is result cache? When does it help or harm?
  16. Explain multiplexing of controlfiles and redo logs; why is it critical?
  17. What are Wait Events and Time Model Statistics? How do you use them together?
  18. How does the Database Writer choose how many DBWn processes to spawn?
  19. What is Adaptive Shared Memory Management (ASMM) vs AMM? When to choose which?
  20. What are System/Session statistics (v$sesstat/v$sysstat) you monitor during incidents?
  21. Explain how Oracle handles direct path reads/writes.
  22. What is a segment header? Describe freelists and ASSM bitmap blocks.
  23. Explain Smart Scans on Exadata and why they are sometimes disabled.
  24. What changed significantly between 11g, 12c, 19c in optimizer/adaptive features?
  25. Describe how ORA-600/ORA-7445 are triaged (incident packaging, ADRCI).

2) Multitenant: CDB/PDB — 22

  1. Architecture differences between non-CDB and CDB with PDBs.
  2. What lives in CDB$ROOT vs PDB$SEED vs each PDB?
  3. What are local vs common users/roles? Practical constraints?
  4. How do you plug/unplug a PDB and validate compatibility?
  5. PDB cloning (local vs remote): steps and pitfalls.
  6. How do you enable automatic PDB open on instance startup (SAVE STATE vs triggers)?
  7. Explain PDB lockdown profiles and typical restrictions.
  8. PDB hot cloning — what gets copied and what doesn’t?
  9. How is UNDO managed (shared vs local UNDO in a CDB)?
  10. How do you perform PDB PITR using RMAN?
  11. What is a proxy PDB? When would you use it?
  12. How do you manage AWR per PDB?
  13. How does resource management differ per PDB (CDB Resource Manager)?
  14. How are services configured per PDB for routing?
  15. Patching CDB with multiple PDBs — what is the safe sequence?
  16. What happens to common objects and metadata during plug/unplug?
  17. How do you clone a PDB across different character sets/endian issues?
  18. Troubleshoot a PDB stuck in MOUNTED with undo issues.
  19. How to restrict a DBA in a PDB from accessing CDB$ROOT.
  20. Strategy for cross-version migrations using PDB unplug/plug.
  21. Explain dictionaries and catalog views that are PDB-aware (CDB_, DBA_).
  22. How do you backup/restore a single PDB (hot) with minimal impact?

3) ASM & Storage — 20

  1. ASM architecture: instance, diskgroups, failure groups, templates, rebalancing.
  2. How does ASM allocate extents (AU/extent stripes) and manage striping/mirroring?
  3. Normal vs high vs external redundancy — tradeoffs and use cases.
  4. ASM rebalancing triggers and tuning (POWER LIMIT).
  5. Migrating from filesystem to ASM (datafiles, online logs, controlfiles).
  6. Fixing disks with poor performance in ASM — identifying candidates.
  7. Converting a diskgroup from external to normal redundancy (options).
  8. ASM metadata corruption — detection and recovery strategies.
  9. Best practices for FRA placement and sizing in ASM.
  10. What is ASMLIB vs UDEV for device persistence?
  11. How to move TEMP files into ASM and validate throughput.
  12. Co-locating redo logs vs datafiles — considerations.
  13. Bigfile tablespaces pros/cons and use cases.
  14. How to size redo logs for a write-heavy workload.
  15. Monitoring I/O service times via AWR/ASH and ASM views.
  16. Consequences of disk drop timing and diskgroup repair timer.
  17. ASM instance startup dependencies in RAC/GI.
  18. ACFS — when to use and how it differs from ASM file access.
  19. Detecting and resolving allocation/preferred read issues in extended clusters.
  20. Strategies for ASM performance testing and baselining.

4) RMAN & Recovery — 25

  1. RMAN architectural components (channels, SBT, controlfile/catalog).
  2. Backups: image copies vs backup sets — benefits and when to use.
  3. Full vs incremental vs cumulative incremental — design strategies.
  4. Incremental merge — how it works and when to implement.
  5. Using block change tracking — benefits and caveats.
  6. Configuring retention policy with FRA pressure and archival policies.
  7. Crosscheck vs catalog; what problems they solve.
  8. Controlfile autobackup — how and when to rely on it.
  9. Restore/recover to new host with limited metadata (disaster recovery drill).
  10. DUPLICATE FROM ACTIVE DATABASE — requirements and failure points.
  11. TSPITR end-to-end — restore, aux instance, metadata cleanup.
  12. Block media recovery — detection (v$database_block_corruption) and fix.
  13. PITR to SCN/time/sequence — validation steps.
  14. Encrypted backups — key rotation and cross-host restore testing.
  15. Parallelization and section size for large files.
  16. RMAN/Flashback Database — choosing between them for user error rollback.
  17. Channel configuration, throughput tuning, and backup window planning.
  18. Handling catalog corruption/out-of-sync scenarios.
  19. Restoring single PDB from CDB backups.
  20. Transporting tablespaces with RMAN — checks and metadata.
  21. Handling missing archived logs (incremental to roll forward).
  22. Validating backup integrity (RESTORE VALIDATE, cross-platform tests).
  23. Backup lifecycle automation and reporting (daily checks/aging).
  24. Performance bottlenecks in backup (media vs network vs DB read).

5) Data Guard & DGMGRL — 25

  1. Physical vs Logical standby — internal mechanics and limitations.
  2. Redo transport (ARCn, LNSn) and RFS — how they interact.
  3. Real-time apply and log shipping modes — how to enable/verify.
  4. Standby redo logs — sizing, number, and placement.
  5. FSFO architecture — observer requirements and behaviors.
  6. Switchover vs failover — prechecks, postchecks, and validation.
  7. Flashback for reinstating a failed primary — steps and gotchas.
  8. Diagnosing apply lag — SQL apply vs redo apply causes.
  9. Handling archive gaps and strategies for gap resolution.
  10. Protect mode (Max Performance/Availability/Protection) — latency and loss tradeoffs.
  11. Far Sync instances — use cases and configuration guidelines.
  12. DGMGRL broker configuration — key commands and diagnostics.
  13. Multi-standby topologies — cascading and hub/spoke.
  14. Read-only workload on standby — service design and routing.
  15. Detecting lost writes and preventing silent divergence.
  16. Upgrades with Data Guard (transient logical, DBMS_ROLLING).
  17. SSL/TLS for redo transport — configuration and testing.
  18. Troubleshooting LNS timeouts and NET_TIMEOUT settings.
  19. Broker status codes (ORA-168xx) — common root causes.
  20. Handling standby role transitions for PDB services.
  21. Using Flashback Database to undo logical errors at primary.
  22. Tuning redo send/receive and apply for WAN links.
  23. Monitoring key views for health (v$dataguard_stats, v$managed_standby).
  24. Testing DR with minimal app interruption.

6) Oracle RAC / Grid Infrastructure — 25

  1. Clusterware architecture — CRS, OHASD, agents, OCR, voting disks.
  2. SCAN listeners — purpose, DNS dependencies, and client-side effects.
  3. Role of GES/GCS and Cache Fusion during consistent reads.
  4. Diagnosing node eviction — CSSD logs, OSWatcher, TFA usage.
  5. Services — preferred/available instances, failover policies (TAF/FCF/AC).
  6. Load balancing advisory and connection pooling best practices.
  7. Adding/removing RAC nodes — steps and checks.
  8. Rolling patching — GI vs DB homes, and zero downtime strategies.
  9. Understanding gc cr request and gc buffer busy waits and remediation.
  10. Interconnect configuration — bonding, jumbo frames, HAIP, and tests.
  11. VIP and public IP behavior during failover; client-side retry.
  12. OCR backup/restore and disaster handling.
  13. srvctl/crsctl basics — start/stop/status scripting and dependencies.
  14. RAC One Node — online relocation and use cases.
  15. NUMA and instance placement — CPU pinning for RAC performance.
  16. Diagnosing library cache or enqueue contention in RAC.
  17. Service-level isolation for hot schemas (cardinality skew).
  18. ASM on RAC — role separation and instance startup orchestration.
  19. Diagnosing CRS resource flapping and dependencies.
  20. TAF vs Application Continuity — differences and selection.
  21. Cluster startup order and custom resource registration.
  22. PDB awareness in RAC and service mapping.
  23. RAC failover testing runbooks (planned/unplanned).
  24. Inter-node latency measurement and its impact on Cache Fusion.
  25. Using AWR Compare Periods Across Instances for hotspots.

7) SQL Optimizer & Execution Plans — 25

  1. Cost-based Optimizer (CBO) — inputs and selectivity calculation.
  2. Bind peeking and adaptive cursor sharing — when it helps/hurts.
  3. SQL Plan Baselines — capture, evolve, and fix regressions.
  4. SQL Profiles vs Baselines vs Outlines — differences.
  5. Histograms (frequency/top-frequency/height-balanced) and their pitfalls.
  6. Extended statistics (column groups) — when to use.
  7. Adaptive plans and dynamic sampling — version differences.
  8. Cardinality feedback — how and when it kicks in.
  9. Join methods (NL, Hash, Sort Merge) — selection and tuning.
  10. Star transformations and bitmap indexes in DW workloads.
  11. Scalar subquery unnesting and correlated subqueries.
  12. SARGability — function-based indexes vs rewriting predicates.
  13. Query block hints and their safe usage.
  14. Detecting and fixing plan instability across instances.
  15. Auto DOP and parallel execution policies — skew and distribution.
  16. Using DBMS_XPLAN and SQL Monitor for runtime stats.
  17. Why EXPLAIN PLAN can be misleading vs actual.
  18. Impact of stale or missing stats on joins and filters.
  19. Inlining PL/SQL and SQL macros (19c/21c) — performance impacts.
  20. Managing optimizer_features_enable across schemas/apps.
  21. Result cache hints and session parameters.
  22. GTT vs temp vs result cache for intermediates.
  23. JSON/XML indexing and path-based access plans.
  24. Partition-wise joins and pruning — necessary preconditions.
  25. Diagnosing hard-parse storms and cursor cache misses.

8) Performance (AWR/ASH/ADDM/Stats) — 25

  1. AWR report anatomy — where you begin and why.
  2. Top wait classes and mapping to subsystems (CPU, I/O, Concurrency).
  3. ASH sampling — reading sessions on CPU vs waiting.
  4. ADDM recommendations — when to trust and when to ignore.
  5. Segment-level stats — top logical/physical I/O segments.
  6. SQL-level stats — finding top SQL by DB time.
  7. Session history — reconstructing an incident timeline.
  8. LIO vs PIO ratios — what “good” means for OLTP vs DW.
  9. log file sync spikes — diagnosing commit frequency/app patterns.
  10. latch free/enqueue contention — real cases and fixes.
  11. Buffer cache hit ratio myths — what to look at instead.
  12. Measuring concurrency via enq: TX and row-level locks.
  13. Temp usage spikes — operations causing them and mitigation.
  14. Parallel execution issues — PX allocation, QC/Slave waits.
  15. Library cache lock/pin hot spots — detection and relief.
  16. Cursor sharing forced — side effects and alternatives.
  17. Baseline creation (AWR baselines) and comparison.
  18. SGA/PGA sizing methodology — avoiding over/under allocation.
  19. Tracking memory usage with V$MEMORY_TARGET_ADVICE and friends.
  20. Capturing and comparing execution statistics across deployments.
  21. Using SQLT/SPA for regression testing.
  22. Diagnosing I/O issues via AWR I/O profiles and ASM metrics.
  23. Using OEM/Cloud Control performance pages effectively.
  24. Designing SLIs/SLOs for Oracle performance.

9) Space, Segments, Undo, Temp — 20

  1. Undo tablespace sizing — retention tuning for long queries.
  2. ORA-01555 (snapshot too old) — common root causes and mitigations.
  3. ITL waits and INITRANS/PCTFREE tuning.
  4. Shrink space vs move/online move — index rebuild implications.
  5. Segment advisor — how to read and act on recommendations.
  6. Detecting row chaining/migration and table redesign strategies.
  7. LOB storage: BASICFILE vs SECUREFILE — dedup/compress and read/write impact.
  8. Tempfile sizing and placement for heavy sorts/hashes.
  9. ORA-01652 unable to extend temp segment — tactical fixes.
  10. Partitioning strategy for large segments and maintenance.
  11. Online table redefinition vs edition-based redefinition — choices.
  12. Monitoring freelists vs ASSM bitmaps for hot objects.
  13. Compress for OLTP vs HCC — tradeoffs and licensing.
  14. Segment-level encryption with TDE — overhead considerations.
  15. Detecting high water mark issues and reclaiming space.
  16. Space usage tracking in CDB/PDB across services.
  17. Managing growth forecasts — capacity planning methods.
  18. Minimizing redo/undo during bulk loads.
  19. Reclaiming space after mass delete — approach without outages.
  20. Managing extremely wide tables (100+ columns) efficiently.

10) Security, Auditing, TDE, Vault — 20

  1. Unified Auditing — policy design and log retention.
  2. Database Vault — use cases to separate duties from DBAs.
  3. TDE: tablespace vs column encryption — performance differences.
  4. Wallet management — auto-open, key rotation, restore tests.
  5. Fine-Grained Auditing (FGA) on sensitive tables — design pitfalls.
  6. Virtual Private Database (VPD) — policy functions and performance.
  7. Data Redaction vs VPD — differences and when to use which.
  8. Password policies, profiles, and verification functions.
  9. Locking down powerful packages (UTL_FILE/HTTP) and external jobs.
  10. Auditing SYS operations and protecting audit trails.
  11. Managing and auditing database links.
  12. Least-privilege design for app schemas in PDBs.
  13. Common vs local users — privilege boundaries.
  14. Protecting SYS.USER$ and hashing algorithms history.
  15. Securing listener (TCPS), valid node checking, and ACLs.
  16. Handling security in mixed CDB/PDB with different tenants.
  17. Encryption of backups and keys off-box (OKV/OCI Vault).
  18. Detecting privilege creep and drift over time.
  19. GDPR/PII architectural controls at DB layer.
  20. Handling compromised credentials — emergency response.

11) Exadata & Oracle Cloud (OCI/Autonomous) — 18

  1. Exadata storage cells — Smart Scan, storage indexes, offload.
  2. When Smart Scan is disabled and how to re-enable.
  3. EHCC types and workload fit; migration considerations.
  4. Cell metrics and AWR cell-specific views.
  5. Network and I/O layout best practices on Exadata.
  6. Autonomous DB — architecture, auto-indexing, and limitations.
  7. Differences between Autonomous Shared vs Dedicated.
  8. OCI DBCS vs ExaCS vs Autonomous — choosing the right service.
  9. Data Guard in OCI — cross-AD and cross-region.
  10. Key management with OCI Vault and rotation plans.
  11. ZDM (Zero Downtime Migration) — flow and blockers.
  12. GoldenGate to OCI targets — design patterns.
  13. AWR Hub / AWR Warehouse in cloud — benefits for fleets.
  14. Cost control in cloud (IOPS, backups, standby, licensing).
  15. Private endpoints and network security groups for DB access.
  16. Patching automation in cloud services — guardrails.
  17. Using Fleet Patching & Provisioning (FPP) for estates.
  18. Observability stack in OCI (OCI Monitoring/Logging) for DB signals.

12) Real-World Troubleshooting & Scenarios — 25

  1. Database slow during peak — end-to-end triage steps you follow.
  2. High “log file sync” — how to isolate app vs storage causes.
  3. RAC node eviction at random — what logs and counters first?
  4. Data Guard apply lag increases at peak — isolate sender/receiver/apply.
  5. PDB can’t open due to UNDO issues — safe recovery plan.
  6. Library cache lock storms — what typically causes them?
  7. Row-level locking chain — how to find the blockers and design changes.
  8. ORA-4031 shared pool/PGA issues — step-by-step remediation.
  9. ORA-600/7445 crash — triage, reproduce, and engage support with artifacts.
  10. RMAN restore too slow — where is the bottleneck?
  11. gc waits spike after new release — what changed?
  12. Massive temp usage during ETL — structural fixes beyond adding temp.
  13. Plan regression after stats gather — stabilize without freezing stats.
  14. Archive log generation doubled overnight — isolate SQL/PLSQL changes.
  15. Undo space errors for long reports — alternative designs.
  16. RAC interconnect saturation — detect and remediate.
  17. High latch/ITL contention on a hot table — schema and initrans approach.
  18. PDB-level resource abuse — cap and shape workloads.
  19. DG switchover succeeded but apps fail to connect — what was missed?
  20. AWR shows CPU-bound but low TPS — interpretation and action.
  21. ASH shows high concurrency on a single segment — design alternatives.
  22. Heavy parse time CPU — why and how to reduce?
  23. “Snapshot too old” despite large undo — root cause patterns.

Comments

Post a Comment