Development teams often require access to production data, or at least similar sized data sets to ensure performance issues are discovered and addressed before full deployment.
The process can be complicated by the presence of multiple teams of developers with potentially overlapping data requirements, non-serializable reports, or projects with incompatible data modifications. As a result, you may end up with multiple copies of your production database.
While this may not be an issue, it can leave you with volumes of data in the range of terabytes or petabytes. Even with modern hardware, it may not be cost efficient to have storage dedicated to idle data. Even if space is not an issue, storing large quantities of unnecessary data adds significant time to the cloning process and goes against best practices.
For environments with these data requirements you could consider using the Oracle ACFS filesystem with copy-on-write (COW) functionality, created on top of Oracle ASM volume.
This approach can be used for cloning directly from production instance or it can be done in a staging manner using Data Guard standby database for enterprise edition or log shipping based replication for standard edition.
The primary database provides streaming replication for the standby database, which can be used directly as a source for clones. As soon as managed recovery has stopped, you can create a snapshot of ACFS and the clone database will be started from the snapshot version. The standby database can be started in ‘snapshot’ mode whereby it’s available in read/write mode, data can be redacted according to the security requirements into a sanitised data clone and then all subsequent clones are created using this database. After cloning, the standby database is converted back to managed replication mode.
You can have as many cloned instances as required by enabling the copy-on-write functionality. This is because the space required for the clones only amounts to the modified blocks representing the difference between the source and the clones. This usually equates to control files for the new database, redo logs, temporary files, and changes to data. All original data will be kept only in one copy without requirements for additional space.
In the scenario above we are using a standby database with Oracle enterprise edition, however similar logic can be implemented for other configurations. For example, we can implement it for standard edition databases in recovery mode with log shipping.
ACFS also provides remastering functionality which allows a modified clone to be reverted to a previous configuration at the time of snapshot creation. This is very useful with Oracle standard edition databases, where flashback functionality is not available, as an additional mechanism for code deployments or software upgrades.
It should be mentioned that ACFS usage with Oracle databases is covered by Oracle cloud file system licensing. For all non-Oracle database purposes, it must be licensed separately.
What do you need to begin cloning?
- Oracle grid Infrastructure with ASM including two network interfaces for public and private interconnect even for single node/non-RAC systems. Virtual interfaces can be provided using n2n software if required. Oracle restart can be used instead, with some limitations such as ACFS not being mounted automatically during start up.
- Available memory to start clone instances.
Steps to consider when automating cloning procedures
- If the source database is in managed recovery mode, stop managed recovery mode temporarily, because without stopping it will corrupt the redo files which will not be possible to apply during the recovery stage
- If data redaction is required, the standby database should be converted to snapshot mode before implementing the data changes
- Create ACFS snapshot
- Setup infrastructure for the cloned databases if required (e.g. spfile, password file etc.) to reflect path changes in spfile of the clone
- Create control files for clones with the proper location and database name
- Recover the clone database to the point of consistency using available redo/standby redo and archive logs
- Open clone with ‘resetlogs’ option
An example script for this process can be found at the following GitHub repository.
If you have questions surrounding cloning of Oracle databases, please submit them using the form below.