28 January 2020

Deployment Suite for Oracle

Deployment Suite for Oracle: What’s New?

Stephanie Herr describes the new features in the Deployment Suite for Oracle that will help you automate your database releases safely, on Linux or Windows.

The Deployment Suite for Oracle is designed to help make Oracle database development and deployments simpler, safer and faster so that database changes can be more easily managed and audited. The latest release now fully supports a migrations-based approach to Oracle database development and deployment on both Linux and Windows. This is in addition to the existing support for automated state-based development and deployments on Windows, which this release extends to Linux.

Specifically, we’ve introduced the following new capabilities and platform support:

Automated migrations-based development and deployment on Linux or Windows – we’ve added Redgate Change Control to help teams understand development changes and generate migration scripts for version control. This gives teams precise control over the migration path during automated Oracle deployments on Linux or Windows.
Code Analysis for Oracle – a new capability that can run as part of an automated continuous integration (CI) process and check your code for compliance with a series of static code analysis rules that help enforce naming conventions and encourage good coding practices. Again, this is available for Windows and Linux.
Automated state-based deployments and checks extended to Linux – our Schema Compare for Oracle and Data Compare for Oracle command line tools now run on Linux as well as Windows.

Redgate Change Control for migrations-based Oracle development and deployment

Up until now, the Deployment Suite for Oracle has supported the state-based approach to database development, with Source Control for Oracle. In this model, the source for the database version is a set of DDL CREATE scripts describing the current state of each database object. At deployment time, Schema Compare for Oracle compares the CREATE scripts in the repository to the metadata of the target database, and automatically generates a deployment script that will update the target database to the version in the repository.

This approach works well for code module deployments and is fine for many types of schema changes too. However, sometimes the complexity of the changes, such as when we need to rename tables or columns, renders it impossible for the automated script to make them, while preserving existing data. It is hard to adapt the auto-generated script to accommodate such changes. Some customizations are possible using pre- and post-deployment scripts, but if this happens a lot, then you may want to consider a migrations-based approach, which gives teams precise control over how every database release proceeds.

The latest Deployment Suite for Oracle now supports a migrations-based approach to database development and deployment.

Automatically generating migration scripts during development

As a team of developers make and test changes on their development instance, Redgate Change Control highlights the differences that haven’t yet been scripted to the project. Developers can select which changes are ready and Redgate Change Control generates the next migration script with the correct dependencies and name. These can be CREATE scripts for new objects or ALTER scripts for changes. Developers can even write INSERT or UPDATE scripts for transactional data or configuration data changes. These migration scripts are then committed to a version control system.

Figure 1: Generating Migrations in Redgate Change Control

The result is a set of migration scripts, in version control, which describe the exact sequence of changes required to move the existing database, schema and data, from one version to another.

Automating migrations-based database deployments, safely

When running automated deployments, all migration scripts that haven’t yet been applied to the target database are concatenated together and executed, in the correct order. This gives the developers full control over the deployment and means nothing is generated at deployment time, so you get a consistent, repeatable process that can form part of a continuous integration and continuous delivery pipeline.

The following checks and reports can now also be run as a standard part of your automated migrations-based deployments:

database drift detection – detects database objects that have been created, deleted or modified outside of the official change management process, such as by applying a hot fix directly to the production database. These “out of process” changes should be captured into version control before the deployment proceeds. This also helps to make sure that your dev/test databases accurately reflect what’s in Production. Drift detection can also be used to perform a rehearsal of the database deployment on a Staging or Pre-Production environment and make sure the Production database hasn’t changed since you performed this rehearsal, which helps ensures safer releases
comprehensive change report – allows you to quickly see how each object will be changed as part of this deployment. It’s a lot easier to understand how the database objects will change from a summary report than by reading through thousands of lines of PL/SQL code. We provide the full deployment script too.

Simplifying migrations-based development and deployment

Just as the state-based approach becomes complex when we need to perform custom deployments that are ‘outside the bounds’ of the auto-generated script, so the migrations-based approach also has a few ‘gotchas’. The two biggest ones we hear about from our users are that the number of migration scripts can get very large, and it’s hard to know if different migrations scripts (maybe from different team members or different teams) are touching the same object, which may cause a problem when they are released.

Redgate Change Control addresses these by scripting the code objects (like packages, procedures, functions) as repeatable migrations. This keeps the number of migration scripts down and allows the team a simple way to track changes to these objects over time. Repeatable migrations are only deployed if the objects have changed, so you don’t have to worry about writing the script idempotently. This will also highlight any conflicts to the objects, early in the development process.

Automated code analysis for Oracle databases

Static code analysis analyzes the syntax of your source code files, and highlights any potential problems, such as use of deprecated syntax or syntax that is known to cause performance or instability issues. It is a standard practice during application development, and many teams run these sorts of checks on Java or .Net code, as part of an automated process, like continuous integration, to identify issues early before they are released to Production.

The latest Deployment Suite for Oracle now offers Code Analysis for Oracle, meaning that your database code can also benefit from the same standard best practice checks as your application code, and can be run as part of these same automated CI processes. The code analysis report can be generated for both state-based and migrations-based deployments.

Code Analysis for Oracle provides a built-in set of rules that check your PL/SQL code for “smells”, such as use of SELECT *, which could cause performance issues, or statements that modify or even delete data in a table without a WHERE clause, and many others.

Figure 2: The different categories of Oracle Code Analysis rules.

The standard rules are easily customizable, to suit your organization’s naming conventions, or other standards, policies and best practices. The severity level of the rules can be set as warnings or errors. You can also turn off any rules that aren’t a concern for your code base.

This can all be customized at a project level and run as part of a continuous integration process to make sure issues are identified early and can be reviewed before going to other environments.

Figure 3: Using migration scripts and code analysis for automated deployments in Jenkins, a popular CI/CD system for Oracle users

Automated state-based deployments extended to Linux

Our state-based, command-line deployment tools, Schema Compare for Oracle and Data Compare for Oracle have been available on Windows for 10 years. However, as we work with more and more Oracle users, we find that, increasingly, they are running databases on a mix of Windows and Linux servers, and so this latest release of the Deployment Suite for Oracle now adds Linux support. Therefore, if you’re automating your deployments using a continuous integration or release management system, the agents that run the processes for the build and deployments can be either Linux or Windows. This hopefully gives you a lot more flexibility to use your existing infrastructure.

These command lines also perform database drift detection checks, and generate change reports, as described earlier for migrations-based deployments, and so now become available for automated state-based deployments running on Linux.

Summary

We’d love to hear from you about your thoughts on these new capabilities, and work with you to help you automate your Oracle database releases. So, visit the Deployment Suite for Oracle page on our website to learn more and get in touch with us. You can also learn more about Database DevOps for Oracle in this whitepaper.

Tools in this post