Even with a robust deployment process, most organizations experience unexpected database changes (known as drift) from time to time. This can come about as a result of an out-of-process deployment, or if an urgent production hotfix has been applied. 

While drift can be unavoidable, it is imperative to be made aware when it occurs to correctly manage the situation. 

Fortunately the Redgate Schema Compare for Oracle command line can be employed to detect drift, both as part of an automated pipeline, or as part of a continuous monitoring process.

Detecting drift in a DevOps Pipeline

To check for drift as part of a DevOps pipeline, simply add a step after a successful deployment to save off a schema snapshot. This snapshot becomes the baseline against which comparison will be made to identify drift.

A simple pipeline that includes a drift check

Add the following logic in your script after you have validated a successful production deployment:

IF %ERRORLEVEL% EQU 0 (
        echo == Congratulations - Deployment was successful!
        rem Save the production baseline for future drift checks
        "C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /b:hdre /i:sdwgvac /source:HR/password@PRODUCTION{HR} /snapshot:D:\Drift_Baseline/Production.onp
)

And add a Drift Check step prior to the production deployment that compares this snapshot against production. Any differences detected are "drift" and can be reviewed in a drift report (called ProductionDrift.html in the example below).

echo ==  Drift Check ==
rem We have previously saved the snapshot state of the last good deployment

"C:\Program Files\Red Gate\Schema Compare for Oracle 5\sco.exe" /i:sdwgvac /filter:Database/Filter.scpf /source:D:\Drift_Baseline/Production.onp{HR} /target:HR/password@PRODUCTION{HR} /report:Artifacts/ProductionDrift.html

rem We expect there to be no differences, so ERRORLEVEL should be 0
IF %ERRORLEVEL% EQU 0 (
     echo == Confirmed that production has not drifted since the last deployment
)

 IF %ERRORLEVEL% NEQ 0 (
     echo == DRIFT DETECTED! The production database schema has changed since the last deployment - aborting the release process
     rem We set the exit code to a value that will halt the deployment process.
     SET ERRORLEVEL=1
)

Tip: You can employ a filter if there are expected changes to production that you would like to ignore for future drift checks.

Tip: You can add the /scriptfile command line switch to generate a script that when run, will undo the drift. 

"Monitoring" continuously for drift

To detect drift as it happens, rather than at deployment time, employ a scheduler, for example Oracle Scheduler (as part of a job), or Windows Task Scheduler.

1. Use the snapshot capability in Schema Compare for Oracle as in the example above to save a "known good" baseline for the schema.
2. Periodically (for example, hourly) schedule a comparison of the database or most recent backup against the baseline snapshot.
3. If a difference is detected, generate a differences report and email it to all stakeholders.
4. There are two ways to deal with drift
   1. Roll back - set the snapshot as the source of a schema comparison and run a roll-back deployment against the target server
   2. Accept - take a new snapshot of production and use this to replace the baseline snapshot. The same snapshot should be used to ensure the changes make it back into development, so to avoid the changes being undone during the next deployment.
   3. Ignore drift - use a filter file to specify changes that you want to preserve that you do not regard as being drift.