[Mastering Oracle SQLcl Liquibase - Part 3/3] - Synchronize your existing environments

In previous parts, you've learned how to start using SQLcl Liquibase with a new database and then track your database changes made after.

In this part, I will show you how to synchronize your existing environments with Liquibase. Using my tips, you can make your databases equal - having the same version of objects.

After this process, you can use Liquibase with all your environments (DEV, UAT, PROD, etc.)

The settings that I will use in this episode are as follows:

1. Create two empty database schemas - I called them DEV and UAT.

2. Create some objects at DEV and UAT.

At that moment, I assume you already have some objects in your databases.

My DEV and UAT are almost equal (UAT has one extra table and index, but DEV has a different version of the secure_dml procedure)

Synchronize environments

I will use the changelog-sync command for that.

What's the benefit of that?

This command will "tell" Liquibase that this is the actual state of objects in my database.

DEV

1. Use SQLcl and log into the DEV database (using a new, easy way)

Please notice that I set STORAGE, SEGMENT_ATTRIBUTES and TABLESPACE to OFF in my SQLcl because I don't want those options to be generated (it has some bugs)

• Capture the current state of DEV using generate-schema command

• Synchronize DEV with just generated changelogs.

• What happened while executing this command?

  Liquibase created some objects used to store information about database changes. No other objects were created / no scripts were executed.

  

  11 new rows were added to the DATABASECHANGELOG table that "tell" Liquibase the actual state of existing database objects.

• Every change inserted into that table has its substitute in XML changesets that reside in /database/ folder.

• Column MD5SUM is the calculated checksum of every file (it will re-calculate when the object changes and you will generate files once again)

UAT

I need to repeat the steps I did on DEV to synchronize my UAT environment and, again "tell" Liquibase that this is the actual state of my database objects.

• Log into UAT using SQLcl ( please be aware that I created a new folder /database_uat/ for this exercise)

• Capture the current state of UAT using the generate-schema command.

• This time, I will also preview SQL which will be executed to synchronize UAT.

This is an extra (not mandatory, but recommended) step to preview what will happen while running changelog-sync.

I will use the changelog-sync-sql command for that.

Notice that Liquibase's tables will be created ( DATABASECHANGELOG, etc.) and new rows will be added.

Also, all replaceable objects will be created, but other existing objects won't be touched.

• Synchronize UAT with just generated changelogs.

• I can preview the exact SQL executed in the DATABASECHANGELOG_DETAILS view.

Use SQLcl Liquibase to make DEV = UAT

Disclaimer:

If differences between your environments are significant, you should consider using Data Pump / expdp / impdp to move the whole schema to another environment (it could be faster). After that, just synchronize it with Liquibase, using instructions from previous sections.

In my example, I want to make DEV = UAT and use DEV as my source version.

I don't need changesets generated into /database_uat/ folder. I will remove it.

I will use changesets from /database/ folder - files created using generate-schema command executed previously at DEV.

• Go to /database/ folder.

• Use SQLcl to log into UAT.

• Execute update-sql to preview changes that will be executed on UAT. This command will not change anything in the database.

Looking into the preview_uat.sql file, we can see that besides inserting into DATABASECHANGELOG or DATABASECHANGELOG_ACTIONS tables Liquibase will create a new version of the secure_dml procedure.

Important: Liquibase will not delete the LOCATIONS table that exists on UAT but not on DEV.

• Update UAT with new changes.

Changes were executed to my UAT.

We can preview that in DATABASECHANGELOG_ACTIONS.

So far, you have learned how to take database changes snapshots of environments and synchronize them with Liquibase. With that approach, you can easily make your environments equal. However, please remember:

• Use Data Pump or expdb/impdb to move whole schemas if your environments differ much.

• If your target schema has more objects than the source schema - those extra ones won't be dropped automatically.