Code Analysis for Oracle

An HTML and a JSON report listing all rule violations in the migration scripts. Details of the rules themselves and the violating code itself can be found by following the relevant links.

Invalid Objects

A CSV file containing a list of any invalid database objects in the relevant schema after the migration scripts have been deployed in the build environment.

Changes Report

An HTML report containing details of each of the database changes that will be deployed to the target database by the release perform verb.

Drift Detection

One of the key benefits to using Redgate Change Automation for managing automated deployments is the drift detection analysis performed by the release prepare verb. It is not unusual for Production environments to require patches or hot-fixes between official deployments and these changes can cause problems with automated deployments if they touch database objects that are modified as part of the next automated deployment. Redgate Change Automation will check for any such drift during whilst preparing the release artifact and users can review the results of this check prior to deploying and decide whether or not to proceed to deployment via release perform.

Drift Correction

If drift has been detected, the release artifact will contain two additional scripts which users may find useful for correcting the detected drift. Once the release artifact has been generated, the changes detected can either be reversed in the target database (using driftRevertScript.sql) or a new migration script can be added to the project (using driftCommitScript.sql as a basis) which adds the detected changes to the project. This script must be idempotent so that it can be executed on environments that don't have the change (e.g. development) and those that do (e.g. target). In either scenario, the CI/CD pipeline needs to be re-run so that a new release artifact can be generated for testing and deployment. Users can choose to continue with deployment at their own risk even after drift has been detected should they choose to do so - the drift correction scripts present in the artifact will not be deployed to the target database.

Please note that if the detected drift is fixed by adding a new migration script to the project, then subsequent executions of release prepare and release perform will still report drift in the target database because the stored database snapshot used for comparison will not have been updated (see section below for more details). Therefore, in this scenario, it is important to check that the drift detected on re-execution of release prepare and release perform corresponds to that detected originally, and if there is no further drift then the drift can be ignored and deployment can proceed. If further drift has occurred then the correction process should be repeated.

How is Drift detected?

Redgate Change Automation manages drift detection by taking snapshots of the database and storing these for future comparisons using the Redgate Schema Compare engine. The target database in a Redgate Change Automation pipeline has two additional tables: the redgate_schema_history table which contains a record of all the migrations applied to the database and the redgate_schema_snapshot table which contains a blob consisting of a database snapshot which represents the state of the target database after the previous deployment.

In order to detect if drift has occurred, the snapshot stored in the redgate_schema_snapshot table is compared to a snapshot taken of the current target database during release prepare. In this way, any changes that were made to the target database since the previous deployment via Redgate Change Automation will be detected and reported as drift.

During deployment, in release perform, the snapshot stored in the redgate_schema_snapshot table is replaced with a snapshot of the CI database post-migration which is taken during the build step and stored in the build and release artifacts - this is the expected state of the target database after deployment.

Drift Detection during deployment

In addition to the drift detection performed during release prepare above, a further drift detection is performed prior to deployment during release perform. This is to safeguard against the deployment target drifting after the release has been prepared but before it has been deployed. The process of detecting drift is the same as described above, with the addition of comparing the redgate_schema_history tables between when release prepare and release perform were run, to ensure no migrations have occurred since the release was prepared. Unlike during release prepare, any drift detected during release perform will prevent the deployment. This is a safety-net to avoid a broken deployment to the target. As with release prepare, correction scripts will be output for reference. If you wish to continue with the deployment even in the event of drift being detected, the "–force | -f" flag can be specified - Note: this is a risky operation. Only do this if you are absoutely confident that the drift detected will not cause the deployment to fail.

Shadow scripts folder desynchronization

For projects that used a shadow scripts folder rather than a shadow database to generate the migration scripts in Redgate Change Control, an additional automatic validation step is performed during the build step. This validation checks whether the shadow scripts folder is still in sync with the migration scripts. This is important because if the shadow scripts folder becomes out of sync then the migration scripts may not be generated correctly. This is not a problem for migration scripts generated using a shadow database (the recommended approach). If the shadow scripts folder and migration scripts have become desynchronized, the build step will abort and a new shadow scripts folder will be generated based on the migration scripts in the project. This folder (called redgate.shadow) should replace the existing shadow scripts folder in its entirety in the Redgate Change Control project. This must be done manually. It is likely that after this has been completed, Redgate Change Control will identify comparison differences which will reflect any differences between the development source and the migration scripts. These should be reviewed and either corrected in the development source or added to a new migration script.