1. General & Project Understanding
Q1. Self Introduction
Sample Answer:
"My name is Shashi Ranjan Singh and I have over 10 years of experience as an Oracle Database Administrator. Currently, I am working as a Lead Cloud Database Engineer managing Oracle RAC, Data Guard, GoldenGate, OEM, RMAN, and ZDLRA environments.
My responsibilities include database administration, performance tuning, disaster recovery management, database upgrades, patching, automation through shell scripting, and cloud migration activities. I also actively participate in database architecture reviews and production issue resolution."
Q2. Explain Your Current Project Architecture
Sample Answer:
"Our production environment consists of Oracle 19c RAC databases hosted on Linux servers. We have a 2-node RAC setup for high availability.
For disaster recovery, we maintain Oracle Data Guard in Maximum Availability mode. Oracle GoldenGate is used for real-time replication to downstream reporting systems.
We use OEM for monitoring, RMAN with ZDLRA for backups, and application servers connect through SCAN listeners."
Q3. What database versions are you working on?
Answer:
Oracle 19c (Primary)
Oracle 21c (Testing)
Experience with 11g, 12c, 18c, and upgrade projects
Q4. Individual Contributor or Team?
Answer:
"I work both as an individual contributor and as a team lead. I handle critical production incidents, mentor junior DBAs, review changes, and coordinate with application, infrastructure, and storage teams."
Q5. New Technologies Learning?
Answer:
Oracle Database 23ai/26ai
OCI Services
Kubernetes
Terraform
Ansible Automation
AI-integrated database features
2. Tablespace Monitoring & Storage Management
Q6. How do you monitor tablespaces?
Answer:
Using:
OEM Alerts
Custom SQL Scripts
Shell scripts
Email notifications
Example:
SELECT tablespace_name,
ROUND((used_space/tablespace_size)*100,2) USED_PERCENT
FROM dba_tablespace_usage_metrics;
Q7. Why is tablespace monitoring important?
Answer:
To avoid:
Application failures
ORA-01653
ORA-01654
Transaction failures
Data loading failures
Q8. What happens if tablespace becomes full?
Answer:
Users cannot insert data.
Common errors:
ORA-01653 unable to extend table
ORA-01654 unable to extend index
Application may become unavailable.
Q9. Are tablespaces critical?
Answer:
Yes.
If SYSTEM, SYSAUX, UNDO, TEMP or application tablespaces become full, production applications can stop functioning.
3. Performance Tuning & Troubleshooting
Q10. Application is slow. How do you troubleshoot?
Step 1
Check database health.
top
vmstat
iostat
Step 2
Check sessions.
SELECT sid,serial#,event
FROM v$session;
Step 3
Check blocking sessions.
Step 4
Analyze AWR report.
Step 5
Review SQL consuming high resources.
v$sql
Step 6
Check storage latency.
Step 7
Coordinate with application team.
Q11. What is Query Tuning?
Answer:
Optimizing SQL statements to reduce CPU, memory, and IO consumption while improving response time.
Q12. How do you know query tuning is needed?
Indicators:
High elapsed time
High buffer gets
Full table scans
Excessive IO
SQL appears in Top SQL section of AWR
Q13. SQL Tuning Steps
Identify SQL
Generate execution plan
EXPLAIN PLAN
Analyze access path
Create/rebuild indexes
Update statistics
EXEC DBMS_STATS.GATHER_SCHEMA_STATS;
Test changes
Q14. Batch Jobs Slow. Which report?
Answer:
AWR Report
If issue is real-time:
ASH Report
If root cause analysis:
ADDM Report
Q15. What is ADDM?
Answer:
Automatic Database Diagnostic Monitor.
It analyzes AWR snapshots and identifies:
SQL bottlenecks
CPU issues
Memory issues
I/O problems
It also provides recommendations.
4. Indexing Concepts
Q16. What is Indexing?
Answer:
Index is a database object that improves data retrieval speed by avoiding full table scans.
Think of it like a book index.
Q17. Types of Indexes Used?
Answer:
B-tree Index
Bitmap Index
Composite Index
Unique Index
Function-Based Index
Reverse Key Index
Partitioned Index
Q18. Why Rebuild Index?
Answer:
To:
Reduce fragmentation
Improve index efficiency
Reclaim unused space
Command:
ALTER INDEX index_name REBUILD ONLINE;
5. User & Security Management
Q19. How do you manage security?
Answer:
Create users
Assign roles
Principle of least privilege
Password policies
Auditing
Encryption
Q20. Grant Privileges?
GRANT CREATE SESSION TO user1;
GRANT SELECT,INSERT,UPDATE
ON emp TO user1;
Q21. Secure Data Transfer?
Answer:
SCP
SFTP
Rsync over SSH
Oracle Data Pump with encryption
Example:
scp file.dmp server:/backup
Q22. What is Data Masking?
Answer:
Replacing sensitive data with fictitious values.
Example:
Original:
9876543210
Masked:
XXXXXX3210
Used in non-production environments.
6. Upgrade, Cloning & Recovery
Q23. What is Database Upgrade?
Answer:
Moving database from lower version to higher version.
Example:
11g → 19c
Benefits:
Security
Performance
New features
Q24. Upgrade Validation (11g/12c/19c)
Answer:
Pre-upgrade:
preupgrade.jar
Check:
Invalid objects
Backup availability
Tablespace size
Compatibility
Post Upgrade:
utlrp.sql
Validate:
SELECT * FROM dba_registry;
Q25. Cloning Requirements
Answer:
Source backup
Sufficient storage
Oracle binaries
Network connectivity
Password files
TNS entries
Q26. Post Restore Steps
Recover database
Open database
ALTER DATABASE OPEN RESETLOGS;
Validate objects
Gather statistics
Enable jobs
Verify applications
7. Shell Scripting & Automation
Q27. Monitor CPU Every 30 Minutes
#!/bin/bash
DATE=$(date)
CPU=$(top -bn1 | grep "Cpu(s)" | awk '{print $2}')
echo "$DATE CPU Usage : $CPU" >> /tmp/cpu.log
Cron Entry:
*/30 * * * * /home/oracle/cpu_monitor.sh
8. Oracle Concepts
Q28. What is Seeded SQL?
Answer:
Seeded SQL refers to Oracle-provided SQL statements embedded within Oracle applications such as Oracle EBS.
These SQLs are delivered by Oracle and generally should not be modified directly.
Examples:
Concurrent Program SQL
Standard Reports SQL
Workflow SQL
9. Behavioral & Scenario-Based Questions
Q29. Decision-Making Scenario
Answer:
"During a critical production issue, archive logs were accumulating rapidly due to a Data Guard transport failure, causing the FRA to reach 95% utilization. I quickly analyzed the issue, coordinated with the network team, temporarily increased FRA space, and restored log transport. This prevented database downtime and avoided a production outage."
Q30. Conflict Resolution
Answer:
"I focus on facts and data rather than opinions. When conflicts arise, I gather technical evidence, discuss the issue with stakeholders, evaluate options, and work toward a solution that best supports business objectives while maintaining system stability."
Q31. Why Do You Want to Join Our Company?
Answer:
"Your organization has a strong reputation for innovation, technology leadership, and investment in modern cloud and database technologies. The role aligns closely with my experience in Oracle RAC, Data Guard, GoldenGate, performance tuning, and cloud platforms. I believe I can contribute immediately while also expanding my skills through challenging projects and collaboration with experienced professionals."
Interview Tip for 10+ Years Oracle DBA
For senior-level interviews, always answer in this format:
Concept → Real-Time Example → Action Taken → Result
This demonstrates hands-on production experience and differentiates you from candidates who provide only theoretical answers.
No comments:
Post a Comment