Top 10 Advanced Oracle DBA Interview Questions and Expert Answers:
1. What is the purpose of SCAN in Oracle RAC, and how does it improve cluster accessibility?
Answer:
SCAN (Single Client Access Name) is a feature introduced in Oracle RAC (from 11g Release 2) to simplify client connection to the cluster. It provides a single hostname for clients to connect to, irrespective of the number of nodes in the cluster.
- SCAN eliminates the need to reconfigure client-side TNS files when nodes are added or removed.
- It works through SCAN listeners and DNS or GNS, which dynamically resolve connections to the appropriate RAC node.
---
2. Explain how to troubleshoot ORA-04031 (shared pool memory fragmentation) in a production environment.
Answer:
To troubleshoot ORA-04031:
1. Gather details using alert.log and trace files.
2. Query V$SGASTAT to identify sub-pool usage:
SELECT pool, name, bytes FROM V$SGASTAT WHERE pool='shared pool';
3. Increase the shared_pool_size if necessary.
4. Use DBMS_SHARED_POOL to pin frequently used objects.
5. Enable ASMM or MEMORY_TARGET for automatic tuning.
6. If the issue persists, consider upgrading the database version to leverage improvements in shared pool management.
---
3. What are the differences between Data Guard and GoldenGate? When would you use one over the other?
Answer:
|
Use Case:
- Use Data Guard for disaster recovery in homogeneous Oracle environments.
- Use GoldenGate for real-time replication across heterogeneous systems or when transformations are required.
---
4. What is Hybrid Partitioning in Oracle 19c, and how does it enhance data management?
Answer:
Hybrid Partitioning allows partitions of a table to reside both on disk and in external storage (e.g., HDFS). This improves data lifecycle management by:
- Offloading less frequently accessed data to cheaper storage.
- Supporting seamless access using SQL without needing to reload the data into the database.
- Enhancing scalability for massive datasets.
---
5. How do you identify and resolve contention issues with undo tablespaces?
Answer:
1. Identify the issue:
- Check for ORA-01555: Snapshot Too Old.
- Monitor V$UNDOSTAT for undo tablespace usage and contention.
2. Resolve:
- Increase UNDO_TABLESPACE size or retention period (UNDO_RETENTION).
- Tune long-running queries by committing more frequently.
- If contention arises due to heavy DML, consider enabling auto undo tuning.
---
6. What is the role of Adaptive Query Optimization in Oracle 12c and later versions?
Answer:
Adaptive Query Optimization dynamically adjusts execution plans based on runtime conditions. Key features include:
- Adaptive Plans: Execution plans can adapt based on actual cardinality.
- Adaptive Statistics: The optimizer collects statistics during query execution (e.g., dynamic sampling).
- Usage: It helps in handling unpredictable workloads, especially when statistics are outdated or skewed.
---
7. How do you monitor and optimize ASM disk groups in Oracle?
Answer:
- Use ASM Views:
- V$ASM_DISKGROUP: Overall status and usage.
- V$ASM_DISK: Individual disk details.
- Optimize:
- Rebalance disks (ALTER DISKGROUP... REBALANCE).
- Ensure ASM disks have equal performance and size.
- Regularly monitor alert logs for errors.
- Use asmcmd for direct disk group management.
---
8. What is the purpose of TDE (Transparent Data Encryption), and how do you implement it?
Answer:
TDE secures data at rest by encrypting database files and backups.
Implementation Steps:
1. Create Wallet:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/path/to/wallet' IDENTIFIED BY <password>;
2. Set Wallet Parameters in SQLNet.ora:
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/path/to/wallet)))
3. Open Wallet and Configure Keys:
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <password>;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY <password> WITH BACKUP;
---
9. How do you analyze and resolve node eviction issues in Oracle RAC?
Answer:
Steps to analyze and resolve:
1. Check the alert logs and cssd logs for eviction reasons:
$GRID_HOME/log/<node>/cssd/ocssd.log
2. Look for network latency using ping or traceroute.
3. Validate interconnects with oifcfg getif.
4. Fix issues such as:
- Adjust misscount or disk timeout values if storage latencies are the cause.
- Resolve underlying network or hardware problems.
5. Test and monitor after applying fixes.
---
10. What are common pitfalls during an Oracle database upgrade, and how do you avoid them?
Answer:
Common pitfalls:
1. Pre-upgrade Checklist Missing: Failure to run preupgrade.jar.
2. Unsupported Features: Legacy features not supported in the new version.
3. Performance Issues: Suboptimal query plans due to new optimizer behavior.
How to avoid:
1. Perform a test upgrade in a non-production environment.
2. Backup the database.
3. Run preupgrd.sql and address recommendations.
4. Use DBUA or manual methods with careful monitoring.
5. Post-upgrade, gather stats and enable adaptive features to optimize performance.
6. Use GR points, to rollback any failed upgrades
Comments