Database DevOps for Oracle

The most important release artifact is the SQL deployment script. This is the script that will be applied to production and it should therefore be exercised as much as possible beforehand. Reviewing and approving the SQL deployment script is a mandatory part of the release process for many organizations.

Sample deployment script and deployment report

Worked example

We run the Schema Compare for Oracle command line, sco.exe, comparing the desired state to production*, with a number of switches:
1. /abortonewarnings:high - this will detect warnings that may cause deployment issues, or in the worst case, the deployment to fail.
2. /report:Artifacts/changes_report.html - this generates a report with all object changes with before-after differences to assist the review process.
3. /scriptfile:Artifacts/deployment_script.sql - this generates the deployment script artifact.
4. > Artifacts\Warnings.txt - this channels the console output to a file, as this contains the list of warnings that have been detected.

*If access to production is restricted, it is equally valid to use any target that is equivalent to production such as an acceptance or staging database. Another approach would be to save a snapshot post-release to an artifact repository or network drive, and compare against this instead.

The resulting exit code will determine how we proceed:
1. If the exit code is 0, it means that no changes have been identified to deploy. We can either choose to fail the build or output a warning.
2. If the exit code is 63 we have detected High Warnings. We should fail the build or at the very least flag this as a potential issue to be thoroughly reviewed before proceeding with the deployment. Examples of these warnings can be found in the Schema Compare documentation. Note that the Schema Compare for Oracle command must be run once more to generate the deployment script as this operation does not complete when High Warnings are detected.
3. If the exit code is 61 we have identified differences but no high warnings have been detected.

Note: If there are no detected high warnings, it doe not necessarily guarantee that the script is issue-free. It is still prudent and recommended to review and test the script before deployment.

Deployment Review Step

echo off
rem  We generate the deployment preview script artifact here
"C:\Program Files\Red Gate\Schema Compare for Oracle 4\sco.exe" /abortonwarnings:high /b:hdre /i:sdwgvac /source State{SOCO_DEV} /target SOCO_PRODUCTION/demopassword@localhost/XE{SOCO_PRODUCTION} /report:Artifacts/changes_report.html /scriptfile:Artifacts/deployment_script.sql > Artifacts\Warnings.txt
rem Note that for this demo example /b:e is used to exclude the target schema in the deployment script

echo Warnings exit code:%ERRORLEVEL%
rem In the unlikely event that the exit code is 63, this mean that a deployment warning has exceeded the allowable threshold (eg, data loss may have been detected)
rem If this occurs it is recommended to review the script, customize it, and perform a manual deployment

IF %ERRORLEVEL% EQU 0 (
    echo ========================================================================================================
    echo == No schema changes to deploy
    echo ========================================================================================================
    rem If desirable we halt the build process at this stage.
    SET ERRORLEVEL=1
    GOTO END
)

IF %ERRORLEVEL% EQU 63 (
    echo ========================================================================================================
    echo == High Severity Warnings Detected! Aborting the build. 
    echo == Review the deployment script and consider deploying manually.
    echo ========================================================================================================
    rem Here we run the same comparison without /abortonwarnings to generate the deployment script as warnings abort the generation of the deployment script
    rem It is useful to keep the deployment script artifact for troublshooting purposes, or as the starting point for a manual deployment
    "C:\Program Files\Red Gate\Schema Compare for Oracle 4\sco.exe" /b:hdre /i:sdwgvac /source State{SOCO_DEV} /target SOCO_PRODUCTION/demopassword@localhost/XE{SOCO_PRODUCTION} /report:Artifacts/changes_report.html /sf:Artifacts/deployment_script.sql
    GOTO END
)

rem This is the happy path where we've identified changes and not detected any high warnings
IF %ERRORLEVEL% EQU 61 (
    echo ========================================================================================================
    echo == Schema changes found to deploy - generating deployment script for review
    echo ========================================================================================================
    rem Set ERROLEVEL to 0 so the build job doesn't fail
	SET ERRORLEVEL=0
    GOTO END
)

:END
EXIT /B %ERRORLEVEL%

Database DevOps for Oracle

Generating the deployment script and reports

Worked example