Consolidating 38 Databases onto Four Nodes
A retail company runs 38 departmental databases on 14 aging physical servers. Average CPU is 12%, but two servers are consistently at 75%. The proposal is to consolidate everything onto four two-socket nodes, each with 512GB RAM and shared all-flash storage, using one container database per node with up to ten pluggable databases, and a single 8KB block size everywhere. The largest tenant is a 1.9TB OLTP schema with heavy row churn and 4,000 peak IOPS; a reporting tenant scans 900GB daily with direct-path reads. Storage can be presented as NFS or iSCSI. The DBA must approve the design this week. In the pilot, two PDBs running the same vendor upgrade concurrently produced library-cache-lock waits, and one node reached 92% memory utilization after only three PDBs were started.
1. Is the proposed single 8KB block size appropriate for both tenants, and what must the DBA require before signing off on the consolidation?
Read the worked answer
Block size is a database-creation decision, not a per-PDB tuning knob. In a CDB, all PDBs inherit the container's block size; changing it later means rebuilding the database. So the design must be judged on the aggregate workload, not on one tenant.
8KB is defensible for this mix. The OLTP tenant peaks at 4,000 IOPS, roughly 32MB/s at 8KB, which is modest for all-flash. The reporting tenant's 900GB direct-path scans are sequential; larger blocks do not materially reduce scan cost. The real levers are multiblock reads, partitioning, compression, and I/O limits through Resource Manager.
The exception to test: if any table has rows larger than 8KB with chained rows, or a LOB workload that benefits from 16KB/32KB blocks, that tenant should go into a separate CDB rather than force the whole container to change.
Sign-off conditions: validate peak IOPS and latency at full ten-PDB density; prove backup throughput for 38 databases on four nodes; test failure of one node; and benchmark both NFS and iSCSI with the real workload before choosing the protocol.
2. The pilot shows library-cache-lock waits and a node at 92% memory after three PDBs start. How do you diagnose and remediate this, and what does it imply for the design?
Read the worked answer
Diagnose with ASH, not guesses. Query v$active_session_history and dba_hist_active_sess_history for the wait library cache lock, identify the blocking session and the object, then correlate with DDL or upgrade timestamps. In the pilot, the two PDBs were running the same vendor upgrade simultaneously; concurrent DDL and invalidation on shared application objects is the likely trigger. Check v$lock and dba_kg_locks for the holder.
Memory is shared, not per-PDB. A CDB has one SGA and one PGA aggregate. Starting three PDBs consumed 92% because the SGA was sized for an average, not for ten active tenants. There is no hard per-PDB SGA cap; Resource Manager shares and limits govern CPU and I/O, while memory must be sized for the aggregate. Remediate by raising the SGA within hugepages, setting PGA_AGGREGATE_LIMIT, and using Resource Manager shares so one PDB cannot monopolize the shared pool.
Design implication: do not co-locate tenants that must run vendor upgrades in the same maintenance window. Stagger them, or place the upgrade-heavy tenants in separate CDBs. Re-run the pilot at full ten-PDB density before approval; three PDBs is not evidence.
3. NFS or iSCSI: what technical checks should decide the storage protocol for this platform?
Read the worked answer
Both protocols can support Oracle, so the decision should be evidence-based rather than doctrinal.
For NFS: confirm mount options (including direct I/O behavior), whether the array supports atomic writes and stable page writes, latency under mixed read/write, and how snapshots and clones integrate with database provisioning. NFS is often simpler for 38 databases because cloning and backup offload are file-level operations.
For iSCSI: validate multipathing, queue depth, path failover behavior, and latency distribution across paths. Confirm the array can present LUNs with the IOPS and throughput required at peak, and that ASM or filesystem layout matches the failure domain.
Common checks for both: run a representative benchmark at full consolidation density, not a single-database test; measure 99th-percentile latency, not just average; test a node failure and a path failure; and confirm snapshot/clone throughput during backup windows. The protocol that wins on p99 latency and operational simplicity should be chosen, and the result documented as a design constraint.