Customer-Managed TDE Keys, Rotation and Audit Retention

ORA-28374 on the standby means the local TDE wallet could not resolve the key version required to decrypt incoming redo. Two causes dominate in this pattern: the standby DB systems' dynamic group or the policy granting them access did not cover the new key version or the vault after a policy change, and a key version was disabled or scheduled for deletion before every consumer had it.

Understand what rotation does in OCI Vault: it creates a new key version. Older versions must remain enabled, because data encrypted with them still references them, and deleting a version is irreversible and can render data permanently unreadable.

Procedure: confirm that both primary and standby DB systems can use the key in the vault and test this in a non-production pair; rotate during the lowest redo window; verify on the primary that the wallet resolves both the old and new versions; watch the standby's apply lag and alert on ORA-28374 specifically; and leave the old version enabled. A rotation that has been rehearsed on a standby pair can complete well inside five minutes; an unrehearsed one cannot.

Split the responsibilities across three principals that never overlap.

1. Key administrators - a security group holding manage on keys and vaults in the security compartment. They rotate and create versions. They have no permissions in the database-family compartment and no data access.
2. DBAs - manage on db-systems and databases in the production compartment, plus use keys on the vault. 'Use keys' is exactly what a DBA needs for TDE operations and precisely does not permit deleting, rotating or exporting key material. That distinction is the whole design.
3. Auditors - read-only on audit trails and on the vault's own audit logs, in a separate group.

Supporting controls: place the vault in a dedicated security compartment, enable audit logging on the vault and on Object Storage buckets so key operations are themselves recorded, and make sure the DBA group has no manage rights on the bucket that holds the immutable backup and audit archives. Verify by attempting the forbidden action as a DBA and recording the denial - that test is also good evidence for the auditor.

Volume first: 20 GB per day is 7.3 TB per year and about 36.5 TB over five years raw. That will not live in a database tablespace, and it probably should not be generated in the first place. Twenty gigabytes a day across six databases usually means someone enabled auditing BY ACCESS on high-frequency operations or is auditing every SELECT on sensitive tables. Tune the unified audit policy to the operations the auditors actually care about - logons, DDL, privilege and role changes, and SELECTs on the regulated tables - before you build storage for the output.

Design: Unified Auditing writes to an audit tablespace and Data Safe collects activity for search and alerting, but Data Safe retention is a bounded operational window, not an archive. The durable archive is Object Storage: export audit records nightly to a bucket with a retention rule or Object Lock for five years, in its own compartment with its own IAM, and a lifecycle rule that moves objects to an archive tier after 90 days. Audit text compresses well, so 36.5 TB raw lands somewhere around 4 to 7 TB stored and is inexpensive on an archive tier. Keep it searchable by querying monthly exports as external tables from Autonomous Data Warehouse through DBMS_CLOUD, and decide explicitly whether the standby's own audit trail must be collected too.