Senior Oracle DBA Interview Prep Guide
Explain the Oracle architecture. How does the instance and database interact?
Oracle architecture consists of an Instance (memory + background processes) and a
Database (physical files). The instance includes the SGA (System Global Area) and
background processes like DBWn, LGWR, CKPT, etc. The instance manages access to the
database, which is made up of control files, datafiles, and redo logs. In Oracle 19c/21c,
Multitenant Architecture (CDB/PDB) is common, where the CDB contains shared
components, and each PDB behaves like a self-contained database.
How do you perform point-in-time recovery (PITR)?
Using RMAN:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RUN {
SET UNTIL TIME '2025-04-01 09:00:00';
RESTORE DATABASE;
RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;
In 19c, PITR is supported at the pluggable database (PDB) level.
How do you identify and resolve performance bottlenecks?
Use AWR, ASH reports, and views like v$active_session_history, v$sql_monitor. Analyze
execution plans using DBMS_XPLAN.DISPLAY_CURSOR. Tune by gathering fresh stats,
creating indexes, or using SQL Profiles/SPM. 19c/21c tools like Automatic Indexing and
Real-Time Stats can help.
What are your best practices for database security?
Apply least privilege using roles and profiles, enable Transparent Data Encryption (TDE),
configure Unified Auditing, use wallets for key management, apply regular patching, and
implement Oracle Data Redaction or Database Vault as needed.
How do you configure Oracle Data Guard?
Enable FORCE LOGGING, configure standby redo logs and listener entries, use RMAN
DUPLICATE FOR STANDBY, and set log_archive_dest parameters. Use DGMGRL for Data
Guard Broker configuration. 19c/21c offer real-time cascading and automatic gap
resolution.
How do you patch an Oracle database with minimal downtime?
Apply patches on test first. Use opatch or opatchauto for GI/RAC. Use datapatch post-
patching. For RAC, use rolling patch mode. 19c/21c support Fleet Patching and Zero
Downtime Patching.
How do you perform database cloning using RMAN?
Use: RMAN> DUPLICATE TARGET DATABASE TO clone FROM ACTIVE DATABASE
NOFILENAMECHECK;
In 19c+, you can also clone pluggable databases (PDBs) for faster provisioning.
A query is suddenly running slow. How do you troubleshoot?
Check SQL_ID, execution plan, compare with historical (AWR). Use bind peeking analysis,
statistics, indexes, or SQL plan management. Consider environmental/system resource
checks.
What are key improvements in Oracle 19c/21c that you've worked with?
Automatic Indexing, Read-only Oracle Home, PDB-level recovery, Real-Time Stats,
Blockchain/Immutable Tables (21c), and improved Data Guard management.
How do you handle a hung production database?
Connect via sqlplus / as sysdba, use v$session, v$process, and system event views. Generate
system state dump if needed. Kill blocking sessions or restart DB if necessary. Analyze alert
and trace logs post-incident.
Real-World Scenario Q&A (Advanced)
Redo logs are filling up quickly. What could be the cause and solution?
Possible causes include high DML activity, insufficient log size, or missing archiving
configuration. Check v$log, v$log_history, and ARCH process. Increase log size, enable
ARCH, and ensure LGWR isn’t bottlenecked. Consider switching to faster storage or
increasing redo log groups.
Archive log destination is full. How do you resolve it without downtime?
1. Move old archive logs to another location.
2. Use RMAN to backup and delete logs:
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
3. Add another archive destination temporarily using LOG_ARCHIVE_DEST_n.
4. Ensure FRA isn’t full if using it.
User reports ORA-01555: Snapshot Too Old. What’s your approach?
This is typically caused by insufficient undo retention. Increase UNDO_RETENTION
parameter, size the undo tablespace appropriately, and check for long-running queries.
Consider enabling RETENTION GUARANTEE if needed.
You need to migrate a large production database with near-zero downtime.
What’s your approach?
Use Oracle GoldenGate for real-time replication. Set up a new environment, initiate
GoldenGate to sync changes, perform switchover during a short cutover window. Validate
data consistency before finalizing.
You see contention on TEMP tablespace. What’s causing it and how do you fix
it?
Common causes: large sorts, hash joins, bad execution plans. Monitor v$tempseg_usage and
v$sql_workarea. Increase TEMP size, enable autoextend, or tune the queries. Consider using
PGA_AGGREGATE_LIMIT to avoid excessive spilling to disk.
Application is locking tables and causing blocking. How do you identify and
resolve it?
Use:
SELECT * FROM v$session WHERE blocking_session IS NOT NULL;
Check v$lock for lock types. Kill offending session if needed. Review application logic to
reduce row-level locking or apply proper indexes.
Sudden spike in CPU usage by Oracle. What’s your response?
Check v$session for high CPU consumers, analyze SQL_IDs. Use AWR/ASH to identify top
SQLs. If necessary, kill runaway sessions. Investigate plan changes, missing stats, or system
overload. Tune SQLs or increase system resources.
You need to enforce encryption for sensitive columns. How do you proceed?
Use Transparent Data Encryption (TDE) for tablespaces or column-level encryption. Set up
Oracle Wallet. Example:
ALTER TABLE employees MODIFY (ssn ENCRYPT USING 'AES256');
You suspect data corruption. What do you do?
Run DBVERIFY on datafiles, use RMAN VALIDATE. Check alert logs for ORA-01578 or ORA-
01110. If corruption found, restore from backup or use block media recovery. Verify storage
subsystem for root cause.
Developers request a copy of production for testing. How do you safely provide
it?
Mask sensitive data using Oracle Data Masking, then clone using RMAN or PDB cloning.
Alternatively, use Data Pump export/import with remap and masking procedures. Always
test clone to ensure no real data leaks.
