Database DevOps for Oracle

Deployments

The ultimate goal is to deploy the database changes to production. To maximize your confidence that this deployment will be trouble-free the production deployment process should be duplicated as much as possible for all other databases in the pipeline.

Using an acceptance environment as a rehearsal

It is highly recommended to conduct at least one deployment rehearsal that mirrors production exactly, or as close as is possible. The pre-production environment that is created for this purpose is often called a staging or acceptance environment. This database is created by restoring the most recent backup to a server that is configured identically to production for both software and hardware.

Often the deployment to acceptance is done manually after the deployment script has been reviewed and approved, but if it is possible to reset acceptance via automation in a sensible time frame, there's little reason not to configure your release management process to automatically deploy to this environment. This means that the DBA, or whoever is tasked to review the deployment, is armed with the additional confidence that the deployment ran successfully before reviewing the deployment script and reports. Of course it also means that if the script were to fail, the DBA doesn't waste any time reviewing the changes unnecessarily.

Deploying to QA and Integration environments

After the build has passed successfully through the continuous integration testing, and before it has reached acceptance and production, it may pass through other intermediate environments for further testing. An integration environment is often used to test the entire application stack as an integrated whole, as unit testing tests only the components separately. 

Organizations often employ testing teams to conduct manual test runs, covering smoke tests, functional testing, regression testing and performance testing. These teams require up-to-date QA databases, although testing teams like to complete a test run on a specific version, so deployments to a QA database will often be triggered manually.

If possible, these intermediate environments should resemble production as much as possible, but this can be impractical. Likewise, keeping these intermediate databases up-to-date at the latest version should ideally mirror the production upgrade process, but testers may value their test data and be unwilling to allow their data set to be refreshed for each test run, particularly in organizations where testers are unable to use a copy of production data. It can therefore be a pragmatic decision to forgo the full deployment process for these environments and simply keep them "synced" incrementally using the /deploy switch of the Schema Compare for Oracle command line.

Worked example

Deploying to QA

This example demonstrates how the QA database can be kept up to date using the /deploy switch of the Schema Compare for Oracle command line. This is a scenario where we have chosen not to use the QA database as a deployment rehearsal. There may be other environments that you choose to treat in the same way, such as Integration, that are earmarked for functional testing rather than deployment testing.

Deploy to QA

"C:\Program Files\Red Gate\Schema Compare for Oracle 4\sco.exe" /i:sdwgvac /source State{SOCO_DEV} /target SOCO_QA/demopassword@localhost/XE{SOCO_QA} /deploy

rem IF ERRORLEVEL is 0 there are no differences, which we don't expect during a deployment.
IF %ERRORLEVEL% EQU 0 (
    echo ========================================================================================================
    echo == No changes were found to deploy! 
    echo ========================================================================================================
    rem Set the ERRORLEVEL to 1 so the job status is unstable to alert user
    SET ERRORLEVEL=1
)

rem IF ERRORLEVEL is 61 there are differences, which we expect.
IF %ERRORLEVEL% EQU 61 (
    echo ========================================================================================================
    echo == Changes were deployed. 
    echo ========================================================================================================
    rem Set the ERRORLEVEL to 0 so the build doesn't fail 
    SET ERRORLEVEL=0
)

Deploying to Acceptance (or Staging)

First we need to provision the acceptance database. This is usually done by restoring the most recent production backup. If this process is time-consuming you may choose not to automate deployments to acceptance on every checkin. It may make sense to run these on a nightly schedule, or to simply trigger them manually on-demand.

  1. Not included in the example is the acceptance database provision step, which is ordinarily a restore of the most recent production backup.
  2. A drift check is performed to double check that the acceptance database is equivalent to production. It could differ if:
    1. In the time between the acceptance database has been provisioned a change has been applied to production.
    2. There is a problem with the provisioning step
  3. A schema snapshot is saved as a deployment artifact. This is a binary representation of the schema state of the acceptance database. This can be used to:
    1. Perform a second drift check before deployment to production to ensure that production hasn't drifted since the deployment rehearsal.
    2. A selective schema rollback can be done by setting the snapshot as the source of a schema comparison in Schema Compare for Oracle.
  4. The SQL*Plus command line is used to apply the deployment script to acceptance. The deployment script artifact was saved in a previous step.
  5. We compare the resulting database with the desired state scripts folder, as these should now be identical. If they aren't identical, a possible cause is that production or acceptance has drifted since the deployment script was generated. A 
    deployment_success_report.html report is generated for troubleshooting.

Deploy to Acceptance

echo off
rem Acceptance DRIFT CHECK to validate that the schema state is consistent with production
rem If it isn't, it could be that production has drifted since the acceptance database was provisioned

echo == Check that the restored acceptance database is equivalent to production ==
"C:\Program Files\Red Gate\Schema Compare for Oracle 4\sco.exe" /i:sdwgvac /source SOCO_ACCEPTANCE/demopassword@localhost/XE{SOCO_ACCEPTANCE} /target SOCO_PRODUCTION/demopassword@localhost/XE{SOCO_PRODUCTION} /report:Artifacts/acceptance_validation_report.html
echo Acceptance vs Production check:%ERRORLEVEL%

rem We expect there to be no differences, with exit code 0
IF %ERRORLEVEL% EQU 0 (
    echo ========================================================================================================
    echo == Validation successful: acceptance and production are the same
    echo ========================================================================================================
    rem Create a schema snapshot artifact of acceptance so we can later use this to perform the production drift check and, if necessary, for roll back.
    "C:\Program Files\Red Gate\Schema Compare for Oracle 4\sco.exe" /i:sdwgvac /source SOCO_ACCEPTANCE/demopassword@localhost/XE{SOCO_ACCEPTANCE} /snapshot:Artifacts/predeployment_snapshot.onp 
)

IF %ERRORLEVEL% NEQ 0 (
    echo ========================================================================================================
    echo == Validation FAILED! The acceptance database schema  isn't consistent with production
    echo ========================================================================================================
    GOTO END
)

echo == Applying deployment script to the acceptance database ==
echo on
Call exit | sqlplus SOCO_ACCEPTANCE/demopassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) @Artifacts/deployment_script.sql
echo off

echo == Check that the deployed acceptance database is now the same as the desired state ==
"C:\Program Files\Red Gate\Schema Compare for Oracle 4\sco.exe" /i:sdwgvac /source State{SOCO_DEV} /target SOCO_ACCEPTANCE/demopassword@localhost/XE{SOCO_ACCEPTANCE} /report:Artifacts/deployment_success_report.html
echo Acceptance Deployment Check:%ERRORLEVEL%


:END
EXIT /B %ERRORLEVEL%

Deploying to Production

Now that we've validated the script against acceptance, the deployment to production should be relatively straightforward and, hopefully, incident-free. 

  1. We start by checking that production is at the same state as acceptance was when we validated the rehearsal. If production has since drifted there are two courses of action. 
    1. Undo the drift prior to the deployment. This can be done by using Schema Compare for Oracle with the pre-deployment snapshot set as the source and the production database as the target.
    2. Re-validate. We accept the drift but we ensure that the rehearsal step is repeated, and ideally all other validation steps in the deployment pipeline.
    3. False positive - do nothing. Drift could just be a result of a deployment step mistakenly being run twice. The drift check doubles as a gate to prevent such duplicate deployments.
  2. Assuming there is no drift we use sqlplus to apply the script to production.
  3. The last step is to do a final comparison to ensure that the end state of production corresponds to the desired state.

Deploy to Production

echo off
rem Here we apply the same deployment script that has been reviewed, approved and run successfully on an acceptance or staging database
rem To check that production hasn't drifted since the acceptance database deployment, we run a second drift check.

echo == Acceptance Drift Check ==
rem We have previously saved the pre-deployment snapshot state of the acceptance database as an artifact
"C:\Program Files\Red Gate\Schema Compare for Oracle 4\sco.exe" /i:sdwgvac /source:Artifacts/predeployment_snapshot.onp{SOCO_ACCEPTANCE} /target SOCO_PRODUCTION/demopassword@localhost/XE{SOCO_PRODUCTION} /report:Artifacts/drift_report.html

rem We expect there to be no differences, so ERRORLEVEL should be 0
IF %ERRORLEVEL% EQU 0 (
     echo ========================================================================================================
     echo == Production hasn't drifted since the deployment rehearsal
     echo ========================================================================================================
)

 IF %ERRORLEVEL% NEQ 0 (
     echo ========================================================================================================
     echo == DRIFT DETECTED! The production database schema is not at the validated starting point
     echo ========================================================================================================
    rem We set the exit code to a value that will halt the deployment process.
     SET ERRORLEVEL=1
     GOTO END
)

echo == Deployment time! ==
echo on
rem Here we apply the deployment script 
Call exit | sqlplus SOCO_PRODUCTION/demopassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) @Artifacts/deployment_script.sql
echo SQLPLUS exit code:%ERRORLEVEL%
echo off
rem Finally we validate that production is equal to the state

"C:\Program Files\Red Gate\Schema Compare for Oracle 4\sco.exe" /i:sdwgvac /source State{SOCO_DEV} /target SOCO_PRODUCTION/demopassword@localhost/XE{SOCO_PRODUCTION} /report:Artifacts/production_deploy_success_report.html
echo Production Deployment Check:%ERRORLEVEL%
IF %ERRORLEVEL% NEQ 0 (
     echo ========================================================================================================
     echo == Deployment FAILED! The production database schema is not equivalent to the desired state
     echo ========================================================================================================
     GOTO END
 )
IF %ERRORLEVEL% EQU 0 (
     echo ========================================================================================================
     echo == Congratulations - Deployment was successful!
     echo ========================================================================================================
     GOTO END
 )
:END
EXIT /B %ERRORLEVEL%




Didn't find what you were looking for?