Multitenant CPU Contention and PDB Security
CDBPLAT is a 19c multitenant database hosting 22 PDBs on one 32-core host. All PDBs use the default CDB resource plan; no PDB has explicit shares, utilization limits, or performance profiles. PDB_RPT runs month-end reports that scan a 900 GB reporting schema and saturate the storage. During those windows, PDB_OLTP read latency rises from 1 ms to 18 ms, and host DB CPU reaches 98%. PDB_RPT's local administrator was granted a common role with ALTER SYSTEM anywhere and has changed a CDB-wide initialization parameter. Security requires that PDB administrators cannot change CDB root parameters, cannot use the SYS common user for application work, and must have 90-day password expiry. One exception: PDB_RPT's administrator must be able to run ALTER SYSTEM KILL SESSION only for sessions inside PDB_RPT. The application owners refuse to move PDB_RPT to another CDB. There is no Exadata or Database In-Memory option.
1. Design a CDB resource plan to protect PDB_OLTP while still allowing PDB_RPT to complete its batch.
Read the worked answer
Create a pending area, create a CDB plan, and add directives for each PDB. Give PDB_OLTP a higher share and no low utilization cap, and cap PDB_RPT so it cannot consume the whole host. For example, PDB_OLTP might get shares 3 and utilization_limit 100; PDB_RPT gets shares 1, utilization_limit 50, and a parallel server limit. If the platform supports PDB I/O rate limits, also set MAX_IOPS and MAX_MBPS for PDB_RPT, because CPU shares alone do not stop a reporting scan from saturating storage.
Use DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN, CREATE_CDB_PLAN_DIRECTIVE, and VALIDATE_PENDING_AREA before SUBMIT_PENDING_AREA. Set RESOURCE_MANAGER_PLAN to the CDB plan. Do not rely on shares alone: shares only matter when CPU is contended. The utilization limit is the hard ceiling that protects OLTP during the batch. Verify with DBA_CDB_RSRC_PLAN_DIRECTIVES and monitor V$RSRCPDBMETRIC during a representative load.
2. Which lockdown profile and profile/password settings would meet the security requirements?
Read the worked answer
Separate privilege from profile. Revoke the common role's ALTER SYSTEM grant made with CONTAINER=ALL; that grant is why a PDB administrator could change a CDB-wide parameter. Create a local administrator user inside PDB_RPT and grant ALTER SYSTEM locally only in that PDB, so the administrator can kill sessions in PDB_RPT but cannot act in the root or other PDBs. The SYS common user should not be used for application work; create application users locally.
Create a lockdown profile that disables ALTER SYSTEM for the PDBs where administrators should not change parameters, and assign it to those PDBs. For PDB_RPT, use a profile that permits the local operation, or rely on the local-only grant without a disabling rule. Create a common or local password profile with PASSWORD_LIFE_TIME 90, PASSWORD_GRACE_TIME as required, and assign it to the administrator accounts. Verify with DBA_USERS, DBA_ROLE_PRIVS, DBA_SYS_PRIVS, and DBA_PROFILES; test that an administrator in another PDB receives ORA-01031 for ALTER SYSTEM.
3. How would you verify the plan and security controls before month-end?
Read the worked answer
Run a controlled load test with PDB_RPT's reporting job and a synthetic OLTP workload. Query V$RSRCPDBMETRIC and V$RSRCPDBMETRIC_HISTORY for CPU wait time, consumption, and utilization limits per PDB. Check AWR or Statspack for resmgr:cpu quantum waits, which show that the resource manager is enforcing limits, and confirm PDB_OLTP latency stays within its target. Verify that the plan is active with SHOW PARAMETER RESOURCE_MANAGER_PLAN and DBA_CDB_RSRC_PLAN_DIRECTIVES.
For security, attempt the prohibited actions from each PDB administrator account in a test PDB: change a CDB-wide parameter, use SYS for an application connection, and run ALTER SYSTEM KILL SESSION against another PDB. Confirm the expected ORA-01031 or lockdown-profile error. Check password expiry with DBA_PROFILES and DBA_USERS.EXPIRY_DATE. Also review common grants with DBA_SYS_PRIVS where COMMON='YES' and CONTAINER='YES'; those are the grants that cross PDB boundaries and are the usual cause of this type of incident.