Oracle DBA interviews, especially for senior roles, usually go beyond definitions. The discussion often moves quickly into troubleshooting, recovery, RAC, Data Guard, patching, GoldenGate, and real production scenarios.
Below are some recent Oracle DBA interview questions reportedly asked in a product-based organization, along with practical answers and the checks I would expect a DBA to discuss during an interview.
1. Explain the Oracle RAC startup sequence.
RAC startup is dependency-driven. Oracle does not simply start the database instances first.
A simplified startup sequence is:
OHASD → CSSD / CRSD / EVMD → ASM → VIP / Listener / SCAN → Database Instances → Services
Clusterware first establishes cluster membership and access to voting resources. ASM must be available before the database can access its files. Database instances and application services are then started according to Clusterware resource dependencies.
crsctl check crs
crsctl stat res -t
srvctl status database -d <db_unique_name>2. What are the ways to resolve lag in a standby database?
The first step is to determine whether the problem is transport lag or apply lag.
For transport lag, I would check redo shipping, archive destination errors, network latency, archive gaps and standby redo log configuration. For apply lag, I would check managed recovery, CPU and I/O pressure on the standby, recovery waits and whether real-time apply is being used.
Useful views include:
V$DATAGUARD_STATS
V$ARCHIVE_DEST_STATUS
V$ARCHIVED_LOG
V$DATAGUARD_PROCESSRestarting MRP may recover a stopped recovery process, but it should not be treated as the solution until the actual reason for the lag is understood.
3. Explain the export steps for an audit trail table.
The approach depends on whether the database uses traditional auditing or Unified Auditing.
For Unified Auditing, the required records can be selected from UNIFIED_AUDIT_TRAIL into an archive table and that table can then be exported using Data Pump.
For traditional auditing, SYS.AUD$ may be involved, but audit management should preferably be performed using DBMS_AUDIT_MGMT rather than directly manipulating SYS-owned audit tables.
Before exporting, I would define the required date range and scope so that unnecessary historical audit data is not included.
4. Explain important RAC background processes.
Some important RAC-specific processes are:
- LMON – Monitors Global Enqueue Service activity and participates in cluster reconfiguration.
- LMD – Handles global enqueue requests and global deadlock detection.
- LMS – Handles Cache Fusion block transfers between RAC instances.
- LMHB – Monitors RAC lock-management processes and detects hangs.
These processes work with the Global Cache Service and Global Enqueue Service to coordinate resources across RAC instances.
5. If STANDBY_FILE_MANAGEMENT is MANUAL, what happens when a datafile is added on primary?
The standby does not automatically create and manage the corresponding datafile.
During managed recovery, Oracle may register the new file using a name such as UNNAMEDxxxxx, and recovery can stop because the expected standby datafile does not exist.
After identifying the file number and correct destination, the file can be created at the required standby location and managed recovery restarted.
ALTER DATABASE CREATE DATAFILE
'<UNNAMED_file>'
AS '<standby_datafile_path>';
With STANDBY_FILE_MANAGEMENT=AUTO, Oracle can normally handle new files automatically when OMF or the relevant filename conversion configuration is correct.
6. A dump contains 10 schemas. How would you import only one?
Use the SCHEMAS parameter with Data Pump Import.
impdp system/***** \
DIRECTORY=DP_DIR \
DUMPFILE=exp.dmp \
SCHEMAS=HRIf the schema has to be imported under a different name:
REMAP_SCHEMA=HR:HR_TEST
If the requirement is actually to import only one table, then TABLES should be used instead. A table can also be renamed during import using REMAP_TABLE.
7. What causes a split-brain scenario?
Split brain can occur when RAC nodes lose communication with each other while individual nodes remain alive and believe they can continue servicing the database.
Possible causes include private interconnect failure, network partitioning, severe latency, OS hangs, heartbeat failures or voting-disk accessibility problems.
Oracle Clusterware protects the cluster using network and disk heartbeats. A node that cannot maintain valid cluster membership can be evicted to protect database integrity.
8. If the whole RAC cluster goes down, how would you bring it back?
I would first determine why the cluster went down rather than immediately attempting database startup.
The basic validation order would be:
Servers → Storage → Interconnect → Voting/OCR → Clusterware → ASM → Listeners → Database → Services
crsctl check crs
crsctl start crs
crsctl stat res -tAfter recovery, I would verify instance status, ASM disk groups, services and both Clusterware and database alert logs to understand the original failure.
9. Explain Cache Fusion.
Cache Fusion allows RAC instances to transfer database blocks directly between their buffer caches through the private interconnect.
If Instance 1 has the required current block and Instance 2 requests it, Oracle can transfer the block through the interconnect instead of forcing Instance 1 to write it to disk and Instance 2 to read it back.
The Global Cache Service manages block ownership and LMS processes participate in the block transfer.
10. Explain Oracle patching steps.
A production patching activity normally follows this sequence:
Read README → Compatibility check → OPatch validation → Inventory check → Prerequisite checks → Backup → Apply patch → Start services → Run datapatch → Validate
opatch lsinventory
datapatch -verbose
After patching, I would verify DBA_REGISTRY_SQLPATCH, database and Clusterware logs, component status and application connectivity.
For RAC and Grid Infrastructure, whether the patch can be applied in rolling mode depends on the specific patch and Oracle's documented instructions.
11. I have a full database backup in two different locations. How would you restore the database?
First I would make sure RMAN can see the required backup pieces. If the backup files have been moved or copied, they may need to be cataloged.
CATALOG START WITH '<backup_location>';
RESTORE DATABASE;
RECOVER DATABASE;The complete sequence may include restoring the SPFILE and controlfile, mounting the database, cataloging backup pieces, restoring datafiles, recovering the database and finally opening it.
If incomplete recovery is performed or a backup controlfile is involved, opening with RESETLOGS may be required.
12. Normally 100 processes run, but during peak hours it suddenly becomes 300. What would you check?
I would first identify where those additional connections came from instead of immediately increasing the PROCESSES parameter.
I would group sessions by username, machine, program, module, service and session status.
V$SESSION
GV$SESSION
V$RESOURCE_LIMITThen I would investigate connection-pool behaviour, application connection leaks, batch jobs, deployments, idle session accumulation, blocking sessions, listener connection storms and CPU or memory pressure.
ASH and AWR can also help establish what changed during the spike.
13. What are the different switchover statuses and what do they mean?
SELECT switchover_status
FROM v$database;Some important values are:
- TO STANDBY – Primary is ready to transition to standby.
- SESSIONS ACTIVE – Switchover is possible, but active sessions are present.
- TO PRIMARY – Standby is ready to become primary.
- RECOVERY NEEDED – Additional redo must be applied before the transition.
- NOT ALLOWED – The database is not currently ready for the requested transition.
Other statuses can appear depending on redo gaps or the current stage of the switchover. The important point is to understand why the role transition is or is not currently possible.
14. What happens when active sessions are disconnected during switchover?
When the switchover is performed using the option that allows active sessions to be disconnected, Oracle can proceed with the role transition even when user sessions still exist.
Those sessions are disconnected and any uncommitted transactions may require rollback. Oracle then completes the required role-transition processing and updates the database role.
In production, application connections should still be drained gracefully wherever possible before the switchover.
15. Explain Oracle GoldenGate processes.
In a traditional GoldenGate architecture, the logical flow is:
Source Database → Extract → Local Trail → Data Pump → Remote Trail → Replicat → Target Database
- Manager – Manages GoldenGate processes.
- Extract – Captures database changes.
- Data Pump – Optionally sends trail data to the target environment.
- Trail Files – Store captured change records.
- Replicat – Applies changes to the target database.
Integrated Extract also uses Oracle database mining infrastructure to capture changes from redo.
16. What are the steps to drop an Oracle database?
Before dropping a database, I would confirm the database identity, verify approvals and make sure any required backups have been retained.
STARTUP FORCE MOUNT RESTRICT;
DROP DATABASE;
For RAC, the database configuration may also need to be removed from Clusterware using the appropriate srvctl command.
Archived logs, RMAN backups, diagnostic files and other external configuration may require separate cleanup depending on the decommissioning procedure.
17. What redo and Data Guard parameters are normally configured?
Common Data Guard parameters include:
DB_UNIQUE_NAME
LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
FAL_SERVER
STANDBY_FILE_MANAGEMENT
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERTThe filename-conversion parameters are particularly relevant when primary and standby file locations differ and Oracle Managed Files are not being used.
I would also verify FORCE LOGGING, standby redo logs, password-file synchronization and redo transport configuration because Data Guard readiness is not only about initialization parameters.
18. Explain manual Grid Infrastructure patching steps.
The exact sequence should always come from the README supplied with the specific Grid Infrastructure Release Update.
For a rolling manual patch, the general approach is:
Prerequisite checks → Backup configuration → Prepatch → Patch Grid Home → Postpatch → Validate node → Continue with next node
rootcrs.sh -prepatch
# Apply the required patch using the procedure
# documented for the specific RU.
rootcrs.sh -postpatch
crsctl stat res -t
opatch lsinventory
Once a node is healthy, the same procedure can be performed on the remaining nodes. If database homes are also patched, the required SQL patching with datapatch must also be completed.
19. Even after killing a session, why might it still appear?
Executing ALTER SYSTEM KILL SESSION does not necessarily mean that the corresponding operating-system process disappears immediately.
A session can remain in KILLED state while Oracle completes cleanup.
Possible reasons include a large transaction rollback, PMON cleanup, an OS-level process stuck in an I/O call, network waits, distributed transaction cleanup or another uninterruptible operation.
V$SESSION
V$PROCESS
V$TRANSACTIONIn RAC, I would also confirm which instance owns the session before attempting further action. Killing the operating-system process should only be considered after identifying exactly what that process is doing.
One Point Worth Remembering During DBA Interviews
For experienced DBA roles, knowing the command is usually only the beginning of the discussion.
The next questions are often:
How would you verify it?
Which view or log would you check?
What if the first solution does not work?
How would you validate the database after the change?
A practical way to structure most troubleshooting answers is:
Symptom → Evidence → Component → Action → Validation
That usually makes the difference between giving a memorized answer and showing how you would actually handle the situation as a DBA.
Have you encountered any of these questions in an Oracle DBA interview? And more importantly, how many of these scenarios have you actually tested in your own lab or environment? 🙂
No comments:
Post a Comment