Oracle DBA Interview Notes
1. Introduction to Oracle Database
Versions: Oracle 11g, 12c, 19c, 21c. Long-Term Support (LTS) is 19c.
Architecture Overview: Instance (SGA + background processes) and Database (datafiles, control
files, redo logs).
Multitenant architecture introduced in 12c (CDB & PDB).
Interview Q: Difference between 12c and 19c?
Interview Q: What is multitenant architecture?
2. Oracle Database Architecture
Key components: SGA (Buffer Cache, Shared Pool, Redo Log Buffer), PGA, Background processes
(PMON, SMON, DBWn, LGWR, CKPT).
Files: Control files, Redo log files, Datafiles.
Interview Q: Explain SGA and its components.
Interview Q: What is the role of SMON and PMON?
3. Oracle Installation & Configuration
Install Oracle software using OUI (GUI) or silent mode.
Configure OS pre-requisites before install.
Use DBCA to create database.
Interview Q: Steps to install Oracle 19c on Linux.
Interview Q: How do you perform silent installation?
4. Database Creation & Management
Create DB manually using CREATE DATABASE SQL or with DBCA.
Set environment variables: ORACLE_HOME, ORACLE_SID.
Interview Q: Manual vs DBCA creation?
Interview Q: What are ORACLE_HOME and ORACLE_SID?
5. Tablespace & Datafile Management
SYSTEM, SYSAUX, TEMP, UNDO, USERS tablespaces.
ALTER TABLESPACE ADD DATAFILE, RESIZE, AUTOEXTEND.
Interview Q: How to add space to a tablespace?
Interview Q: Purpose of TEMP and UNDO?
6. User and Privilege Management
CREATE USER, GRANT, REVOKE, ROLES, PROFILES.
Security: Password policies, locking users.
Interview Q: Difference between role and privilege?
Interview Q: How do you lock/unlock a user?
7. Oracle Networking
Configuration files: listener.ora, tnsnames.ora, sqlnet.ora.
Commands: lsnrctl start/status, tnsping.
Interview Q: ORA-12162, ORA-12541 causes and solutions?
Interview Q: What is TNS and why is it needed?
8. Backup and Recovery (RMAN & Manual)
RMAN: Full, Incremental, Archivelog backups.
Manual: Cold and Hot backups.
Interview Q: RMAN backup script?
Interview Q: Difference between complete and incomplete recovery?
9. Data Pump (expdp/impdp)
Export/Import: Schema, Table, Full DB level.
expdp and impdp with directory object.
Interview Q: Difference between exp/imp and expdp/impdp?
Interview Q: How do you export a single schema?
10. Performance Tuning Basics
AWR, ADDM, SQL Trace, TKPROF.
Gather statistics, monitor wait events, use EXPLAIN PLAN.
Interview Q: How to identify a slow query?
Interview Q: What are wait events?
11. Patching & Upgrades
Patch tools: OPatch.
Types: PSU, RU, One-off patches.
Interview Q: How do you apply patch using OPatch?
Interview Q: How to check Oracle version and patch level?
12. Database Cloning
Methods: RMAN Duplicate, Cold copy.
Interview Q: Steps to clone a database using RMAN?
13. Monitoring & Health Checks
Use OEM, check alert logs, monitor tablespace/datafile usage.
Interview Q: How do you monitor tablespace usage?
Interview Q: Where do you check Oracle alert logs?
14. Pluggable Databases (12c+)
CDB (Container) and PDB (Pluggable).
SQL: CREATE PLUGGABLE DATABASE, ALTER PLUGGABLE DATABASE OPEN.
Interview Q: How to create a new PDB?
Interview Q: How to unplug and plug a PDB?
15. Basic SQL & PL/SQL (DBA-Level)
Basics: SELECT, INSERT, UPDATE, DELETE, JOINs.
PL/SQL for automation and scripts.
Interview Q: How to write a PL/SQL procedure for daily backup?
