Oracle database deployments

It is possible to achieve automated Oracle database deployments using Deployment Manager and our Deployment Suite for Oracle.

This assumes you are using Source Control for Oracle to version your database.

Deployment script

Add the following script into a file called Deploy.ps1 in the root of your scripts folder.
#Inputs, to be provided via Deployment Manager variables:
#$databaseSourceSchemas = "MySchema" or "MySchema1,MySchema2"
#$databaseUserName = "MyUserName"
#$databasePassword = "MyPassword"
#$databaseHostName = "My.Host.Name"
#$databaseSID = "MySID"
#$databaseTargetSchemas = "MySchema" or "MySchema1,MySchema2"

# Note, this script currently supports just username and password based authentication.

$schemaComparePath = 'C:\Program Files\Red Gate\Schema Compare for Oracle 3\SCO.exe'

Write-Warning "Performing dynamic database upgrade."

# Create upgrade script using compare
& $schemaComparePath -source ".{$databaseSourceSchemas}" -target "$databaseUserName/[email protected]$databaseHostName/$databaseSID{$databaseTargetSchemas}" -deploy | Out-Host
Write-Output "Schema Compare for Oracle exited with code $lastExitCode"

# Exit code 61 is simply telling us there are differences that have been deployed.
if( $lastExitCode -eq 61)
    exit 0

This script assumes the Schema Compare for Oracle tool is installed onto the machine that you are deploying to. This does not need to be the same machine as the Oracle server, it just needs to be able to access the Oracle server via the network.

Creating the package

Use RgPublish to package up your scripts folder:
"C:\Program Files (x86)\Red Gate\Deployment Manager\Tools\rgpublish" /source="C:\MyScriptsFolderLocation" /packageid=MyPackageName /version=1.0.0 /target=feed:http://localhost:8080/nuget

Version number should be substituted with a value generated by the build/continuous integration system.

The above assumes that you're running Deployment Manager on the same machine as the build/continuous integration server. If not, just change paths as appropriate.

Caveats / Todo
- The approach only supports a dynamic deployment method, where the upgrade script is generated on-the-fly.
- TNS connections will need updates to the PowerShell.

I hope this is helpful.
Justin Caldicott
Redgate Software Ltd
Sign In or Register to comment.