Oracle Database Deployment Automation Using Liquibase Open-Source Solution

Oracle database administrators (DBAs) face significant challenges in managing consistency, efficient collaboration, and complex database schema changes across multiple environments. Manual processes for schema updates can be error-prone, time-consuming, and difficult to coordinate across large teams. Downtime and data inconsistencies are two risks organizations face in this situation. To address this, DBA teams can use reliable database schema management tools such as Liquibase.

What is Liquibase?

Liquibase, launched in 2006, is an open-source database schema management tool written in Java, was designed to simplify the tracking of database changes, particularly in agile software development settings. It offers developers and DBAs a platform-independent solution for tracking, managing, and implementing database changes, with a key feature being its support for database version control.

Oracle DBAs gain a powerful solution to streamline schema versioning, provide continuous integration and delivery, and reduce the risk of human error. Adopting Liquibase can improve the efficiency, reliability, and agility of your workflow, allowing you to focus on more strategic tasks.

Benefits of Liquibase for Oracle Database Deployment Automation

• Database Version Control: Track and manage modifications over time.
• Database Rollback: Get a safety net in the event of CI/CD pipeline deployment errors.
• Cross-Platform Compatibility: Use the databases you want for all of your environments in CI/CD workflows.
• Declarative Database Changes: Improve readability and comprehension compared to imperative scripts.
• Collaboration and Teamwork: Use concurrent work capabilities on database changes to minimize CI/CD conflicts.
• CI/CD Integration: Ensure consistent and reliable schema changes across your environments.

Important Liquibase Terms

Changelogs

Liquibase uses changelog files in SQL, XML, YAML, or JSON formats to list database changes in sequential order.

Here’s an example of a changelog in XML:

Changesets

A database change is called a changeset. You can apply many changeset types to a database,  such as creating a table, adding a primary key, or creating a package.

For example, the file departments_table.sql is a changelog with two changesets:

• Line 1 – –liquibase formatted sql is a syntax you need to add at the beginning of every new SQL changelog. This is how Liquibase identifies changelogs.
• Lines 2 and 13 – Those lines are identifiers that uniquely describe every changeset.
  • Pretius – changeset author
  • departments_table and add_col_description – unique changeset identifiers (id)
• Lines 3 and 14 – These are comments. You are not required to include comments.

Tracking Tables

Liquibase uses the DATABASECHANGELOG table to track which changesets have been run. If the table does not exist in the database, Liquibase creates one automatically.

Liquibase will also create the DATABASECHANGELOGLOCK table. This table creates locks to avoid simultaneous Liquibase runs to your database.

sqlFile

The sqlFile Change Type allows you to specify SQL statements in an external file.

Oracle Verified Database Versions Compatible with Liquibase

Oracle Database:

• 23c
• 21c
• 19c
• 2

AWS RDS for Oracle Database:

• 21c
• 19c

Liquibase Prerequisites

• Liquibase installed on your machine.
• Java installed on your machine. Note: If you used Liquibase’s installer, this is automatically included.
• Set up a new Liquibase project folder and organize your changelogs.

Configure Your Database Connection

Specify the database URL in the liquibase.properties file (defaults file), along with any other properties you need to choose default values for. Liquibase does not parse the URL. You have the option to specify the full database connection string or the URL with your database’s standard connection format.

Property File Variable Declaration Example

• #### Enter the target database ‘url’ information ####

liquibase.command.url=jdbc:h2:tcp://localhost:9090/mem:dev

• # Enter the username for your target database.

liquibase.command.username: dbuser

• # Enter the password for your target database.

liquibase.command.password: letmein

 Oracle Server

url: jdbc:oracle:thin:@<host>:<port>/<service_name>

Oracle on AWS RDS

url: jdbc:oracle:thin:@<endpoint>:<port>:<sid>

How to Use Liquibase for Oracle Database Deployment Automation

You can write your database change code in your selected authoring tool in SQL, YAML, JSON, or XML formats. Liquibase refers to these change scripts as ChangeSets. ChangeSets are grouped into ChangeLogs and placed in version control systems.

In this tutorial, we are going to cover the SQL database change code.

Some sqlFile Liquibase Considerations:

• sqlFile works well for complex changes that Liquibase does not support through automated Change Types, such as stored procedures. You can have multi-line SQL in sqlFile.
• For Single-line SQL statements, separate them with a ; at the end of your final SQL line, or use a GO statement. This statement has to be on a separate line between your SQL statements.
• For Multi-line SQL statements, you can only use a ; or GO statement to finish the line.
• For single SQL statements, you do not need to use either a ; or GO statement.
• You can add comments to sqlFile as follows:
  • Multi-line comments need to be contained within /* and */.
  • Single-line comments can start with – and will finish with that line.

• Liquibase’s default behavior is to split statements with a ; or GO at the line end. If you’re using a comment or non-statement that ends with either of those, keep them away from the end of the line to avoid SQL errors. sqlFile supports comments in these formats:
  • A multi-line comment that starts with /* and ends with */.
  • A single-line comment starting with — and finishing at the end of the line.
  • You can also nest sqlFile within the rollback tag in a changeset:

Example: changeset-sql.txt

--changeset dev:1

create table test1(

id int primary key,

name varchar(255)

);

Run sqlFile

1. Add the Change Type to your changeset.
2. Deploy your changeset by running the update command.

COMMANDS:

--To Execute

./liquibase update

--To Tag

./liquibase tag "BeforeChange2.0"

--To Rollback last 4 changes

./liquibase rollbackCount 4

--To Rollback to a tag BeforeChange2.0

./liquibase rollback BeforeChange2.0

--To see the liquibase commands

./liquibase --help

--To display the commands to execute

./liquibase updateSQL

Note: Liquibase 4.26.0+ allows you to use the rollbackSqlFile statement to specify rollback SQL for a changeset in a separate file:

EXAMPLE:

--changeset liquibase-user:1

DROP PROCEDURE hello_world;

--rollbackSqlFile path:release_1.0/rollback_45895.sql

Example execution if you’re using JIRA and provide the custom properties file:

liquibase --defaults-file=dev.liquibase.properties update-sql --label-filter=JIRA-1

liquibase --defaults-file=dev.liquibase.properties update --label-filter=JIRA-1

Once the update is completed, the changes will be applied as long as there are no typos, SQL issues, or permission problems. New log rows will be inserted into the DATABASECHANGELOG table for future reference and rollback functionality.

Explore more database resources from the experts at Datavail.com.