Octopus Deploy is a dedicated release management tool, ideal for orchestration of application and database deployment. 

For detailed on how to set this up, check out this article hosted on the Octopus Deploy website.

Publishing the validated database build package

The last stage of the CI process should publish the validated database artifact to make it available to Octopus Deploy. Many well-known CI tools have an Octopus Deploy plug-in that can achieve this. Failing this, it is straightforward to achieve with the Octo.exe Command Line.

For example, assuming your database scripts folder is called Database, and that the build number is held in a %BUILD_NUMBER% variable:

Octo.exe pack --id=RedgateOracle --format=Zip --version=1.0.0.%BUILD_NUMBER% --basePath=Database --verbose --overwrite
Octo.exe push --package RedgateOracle.1.0.0.%BUILD_NUMBER%.zip --replace-existing --server https://rgdemo.octopus.app --apiKey API-<TYPEYOURAPIKEYHERE>

Optional: To trigger a deployment from the CI tool (eg, for staging/acceptance environment rehearsals, or for those who have adopted full continuous deployment)

Octo create-release --project "Oracle DevOps demo" --deployto Acceptance --server https://rgdemo.octopus.app --apiKey API-<TYPEYOURAPIKEYHERE>

Configuring Octopus Deploy to deploy your database package

Now configure your Octopus Deploy release process by adding the following steps:

1. Redgate - Create Oracle Release step template to convert the database package into the release artifacts relevant to the target database.
2. Manual Intervention Required. Unless you have adopted full continuous deployment, it is highly recommended to include a step to allow the release manage or DBA to review the change report and SQL script prior to deployment.
3. Deploy Oracle Release using SQLPlus. This applies the deployment script created in step 1, and reviewed/approved in step 2, to your target database.

Sample deployment process (image source: Octopus.com)

For more fine-grained control, replace the Redgate Create Oracle Release step with the following PowerShell:

$user = "HR"
$password = "RedgatePassword"
$server = "PRODUCTION"
$deploymentSchema = "HR"
$exportPath = "D:\\OctopusExport\\"
$packageInstallDirectory = $OctopusParameters["Octopus.Action.Package.InstallationDirectoryPath"]
Write-Host "packageInstallDirectory is: $packageInstallDirectory"

$unmaskedConnectionString = "$user/$password@$server{$deploymentSchema}"
$maskedConnectionString = "$user/*****@$server{$deploymentSchema}"
Write-Host "Will create a delta script by connecting to: $maskedConnectionString"
$schemaCompareExe = "C:\\Program Files\\Red Gate\\Schema Compare for Oracle 5\\SCO.exe"
Write-Host "Running the exe $schemaCompareExe"
$deltaReportPath = "$exportPath\\PreDeploy.html"
Write-Host "deltaReportPath: $deltaReportPath"
$changeScript = "$exportPath\\Delta.sql"
Write-Host "changeScript: $changeScript"

$AllArgs = @("/source:$packageInstallDirectory\\{$deploymentSchema}", "/target:$user/$password@$server{$deploymentSchema}",
     "/includeidentical", "/scriptfile:$changeScript","/report:$deltaReportPath", "/reporttype:Simple")

& "$schemaCompareExe" $AllArgs

Write-Host "SCO has been run, lastexitcode: $LastExitCode"

$successful = $false
$upload = $false
if ($lastExitCode -eq 61){
                $successful = $true
    $upload = $true
    }
if ($lastExitCode -eq 0){
                $successful = $true
    }
if ($upload){
  $environmentName = $OctopusParameters["Octopus.Environment.Name"]
  $artifactName = "$environmentName" + "PreDeploy.html"
  New-OctopusArtifact -Path "$deltaReportPath" -Name "$artifactName"
  $scriptArtifactName = "$environmentName" + "Delta.sql"
  New-OctopusArtifact -Path "$changeScript" -Name "$scriptArtifactName"
}

if ($successful){  
                exit 0
}