1. Patching & Maintenance
Q1. What is Database Patching?
Database patching is the process of applying Oracle-provided fixes to resolve bugs, security vulnerabilities, performance issues, and stability problems without reinstalling the database software.
Real-time Example:
We regularly apply Release Updates (RU) on Oracle 19c every quarter to stay compliant with Oracle Critical Patch Updates (CPU).
Q2. What is an Oracle Patch?
An Oracle patch is a software update released by Oracle to fix:
Security vulnerabilities
Database bugs
RAC issues
ASM issues
Performance problems
Q3. Why is Patching Important?
Benefits:
Security compliance
Bug fixes
Improved stability
Better performance
Oracle support certification
Failure to patch may expose systems to known security exploits.
Q4. Types of Oracle Patches
Interim Patch
Fixes specific bug
Release Update (RU)
Quarterly patch bundle
Release Update Revision (RUR)
Additional fixes on RU
OJVM Patch
Java VM security fixes
One-Off Patch
Single bug fix
Grid Infrastructure Patch
RAC/ASM/Clusterware fixes
Q5. What is PSU Patching?
PSU (Patch Set Update) was used in Oracle 11g and 12c.
It contained:
Security fixes
Selected bug fixes
PSU has now been replaced by RU (Release Updates).
Q6. How to Verify PSU/RU Installed?
SQL
SELECT * FROM dba_registry_sqlpatch;
OPatch
opatch lsinventory
Q7. How to Check OPatch Conflicts?
opatch prereq CheckConflictAgainstOHWithDetail \
-phBaseDir /tmp/patch
Interviewers like this answer because it shows practical experience.
Q8. Have You Applied OEM Patches?
Yes.
OEM patching involves:
OMS patch
Agent patch
Plug-in patch
Commands:
emctl stop oms -all
opatch apply
emctl start oms
Always validate OMS and repository after patching.
Q9. How to Patch if OraInventory is Missing?
Recreate inventory
runInstaller -silent \
-attachHome \
ORACLE_HOME=/u01/app/oracle/product/19c
Verify:
opatch lsinventory
Q10. Why RAC Shows Rolling Patch Status?
Check:
crsctl query crs activeversion
Occurs when:
One node patched
Remaining nodes not patched
Complete patching on all nodes:
rootcrs.sh -postpatch
Then rolling patch mode disappears.
2. ASM, RAC & Clusterware
Q11. Have You Worked on ASM?
Yes.
Typical architecture:
Disk
↓
Failure Group
↓
Disk Group
↓
ASM Instance
↓
Database
Common disk groups:
DATA
FRA
OCR
VOTE
Q12. What is Cache Fusion?
Cache Fusion is RAC's mechanism to transfer data blocks directly between instances through the private interconnect.
Instead of:
Disk → Instance
Oracle performs:
Instance A → Interconnect → Instance B
Benefits:
Reduced disk IO
Faster block access
Q13. What is SCAN Listener?
SCAN = Single Client Access Name
Benefits:
Single connection string
Load balancing
Failover
Example:
myrac-scan.company.com
Usually resolves to 3 IP addresses.
Q14. How to Locate SCAN Listener?
srvctl config scan
srvctl config scan_listener
srvctl status scan_listener
Q15. What is TFA?
Trace File Analyzer
Used for:
RAC troubleshooting
Log collection
Incident diagnostics
Commands:
tfactl status
tfactl diagcollect
Q16. What is AWR?
Automatic Workload Repository
Captures:
SQL statistics
Wait events
IO activity
Memory usage
Snapshots taken every hour by default.
3. Backup & Recovery
Q17. What is Recovery Catalog?
Separate schema/database used by RMAN to store:
Backup metadata
Scripts
Recovery history
Benefits:
Centralized backup management
Longer retention
Q18. Media Recovery vs Crash Recovery
Crash Recovery
Occurs automatically after instance failure.
Uses:
Online Redo Logs
No DBA intervention required.
Media Recovery
Required when datafiles are lost.
Uses:
Backup + Archive Logs
Requires DBA action.
Q19. Restore vs Recover
Restore
Copies backup files back.
Backup → Disk
Recover
Applies redo logs.
Datafiles + Redo
Restore alone is not sufficient.
Q20. Thousands of Archive Log Gaps in Standby
Check gap
SELECT * FROM v$archive_gap;
Solution
Incremental backup from SCN
BACKUP INCREMENTAL FROM SCN;
Transfer backup
Recover standby
This is faster than transferring thousands of logs.
Q21. Prevent Block Corruption in Standby
Enable:
db_block_checking=FULL
db_block_checksum=FULL
RMAN:
BACKUP VALIDATE
Data Guard automatically detects physical corruption during redo apply.
4. Data Guard & Disaster Recovery
Q22. Have You Worked on DR?
Yes.
Experience includes:
Physical Standby
Switchover
Failover
DR Drills
Active Data Guard
Q23. Logs Not Reaching Standby
Check:
Network
tnsping standby
Archive Destination
SELECT dest_name,status,error
FROM v$archive_dest;
Listener
lsnrctl status
Data Guard Processes
SELECT process,status
FROM v$managed_standby;
Q24. Physical vs Snapshot Standby
Physical Standby
Read-only
Redo Apply ON
DR ready
Snapshot Standby
Read/Write
Redo Apply OFF
Used for testing
Can be converted back later.
5. Performance Tuning
Q25. Important Sections in AWR
Focus on:
Database Time
Top waits
Top SQL
High resource SQL
Load Profile
Transactions/sec
Wait Events
IO bottlenecks
RAC Statistics
Global cache waits
Advisory Section
Memory recommendations
Q26. Oracle Performance Tools
AWR
ASH
ADDM
OEM
SQL Monitor
SQL Tuning Advisor
Explain Plan
TKPROF
Q27. Why Rebuild Index?
Reasons:
Excessive fragmentation
High deleted leaf blocks
Space reclamation
ALTER INDEX idx_name REBUILD ONLINE;
Do not rebuild routinely without evidence.
Q28. Production Slow During Peak Hours
Step 1
Check database load
SELECT * FROM v$sysmetric;
Step 2
Generate AWR
Step 3
Check Top Wait Events
Examples:
db file sequential read
log file sync
gc buffer busy
Step 4
Check blocking sessions
v$session
Step 5
Check storage latency
iostat -x
Step 6
Tune SQL
Step 7
Increase resources if required
Real Production Example:
A batch job introduced a Cartesian join causing CPU to hit 95%. After creating a composite index and gathering statistics, execution time reduced from 3 hours to 12 minutes.
6. Archive Log Full Scenario
Q29. Archive Destination Full
Immediate Actions
Identify usage:
df -h
Delete obsolete backups:
DELETE OBSOLETE;
Backup archive logs:
BACKUP ARCHIVELOG ALL DELETE INPUT;
Add temporary storage.
Resume database:
ALTER SYSTEM ARCHIVE LOG CURRENT;
Long-Term Prevention
FRA monitoring
OEM alerts
Archive deletion policy
CONFIGURE ARCHIVELOG DELETION POLICY;
Increase FRA size
Fix Data Guard lag issues
7. Database Administration
Q30. Minimum Parameters for Table Export
expdp system/password \
tables=EMP \
directory=DATA_PUMP_DIR \
dumpfile=emp.dmp \
logfile=emp.log
Required:
TABLES
DIRECTORY
DUMPFILE
Q31. High-Level Cloning Steps
Source
Take RMAN backup
Target
Restore controlfile
Restore datafiles
Recover database
Rename
nid
Update
spfile
listener
tnsnames
Open database
Q32. What is Table Partitioning?
Partitioning divides a large table into smaller logical segments.
Types
Range
List
Hash
Composite
Benefits
Faster queries
Faster maintenance
Partition pruning
Easier archival
Example:
Instead of one 5 TB SALES table:
SALES_2023
SALES_2024
SALES_2025
SALES_2026
Oracle scans only required partitions, significantly improving performance.
Senior DBA Interview Closing Statement
When answering scenario-based questions, use the format:
Problem → Analysis → Action → Result
Example:
Problem: Archive destination reached 100%.
Analysis: FRA full due to standby transport issue.
Action: Cleared obsolete archives, increased FRA temporarily, fixed Data Guard transport.
Result: Database resumed without downtime and preventive monitoring was implemented.
This structure demonstrates production support experience, which is exactly what interviewers look for in Oracle DBA candidates with 8–15+ years of experience.
No comments:
Post a Comment