Deploying Oracle ADW Changes from development environment to production

Question

I'm currently running an Oracle Autonomous Data Warehouse (Oracle's cloud-based database offering) on version 19c. I have a development database on the Always Free Tier and my production database is a paid instance (with backups etc). I will likely also be making a staging instance soon.

When making changes to the database schema we have been making changes to my database schema as follows:

1. Change schema on the development environment using SQL statements (CREATE, ALTER, etc)
2. Test the development environment manually
3. Change the schema on the production environment using identical SQL statements
4. Test the production environment to make sure nothing is broken

I am aware that this is not the way it should be done, we need to implement some CI/CD to streamline these changes but we've only recently come out of our piloting phase where it was a rush to get the product working etc, I'm very keen to firm up our new systems.

How I'm imagining it'd go (changes in bold):

1. Change schema on the development environment using SQL statements (CREATE, ALTER, etc)
2. Test the development environment with automated tests
3. Apply some tool to apply the same changes to my production system
4. Test the production environment to make sure nothing is broken

I have seen that tools exist for other SQL database tools, such as MySQL, PostgreSQL etc but from my searches on Oracle-specific tools, I'm not sure if where to find a tool that can propagate the schema changes from our development database to a production database.

If you know of any tools that I can look at to do this functionality then I'd appreciate a pointer on where to look!

I'm aware this is similar to other questions, the ones I've looked at are:

• Altering db schema in production environment
• Upgrade production database during software development lifecycle

Both of these questions were asked several years ago and so I'm not sure if new things have come up recently.

Answer 1

Key Infrastructure Pieces

• Code Repository/Version Control System
• Separate (and licensed) Dev/Test/Prod environments

You seem to be missing the Test environment.

Test Environment

I highly recommend using a Test environment. One advantage is: You can use the actual Schema's name irregardless of environment.

One way to use the Test environment:

• Clone Prod to Test
  • usually a clone of a pluggable database
• apply Change scripts
• perform Unit Tests

Automatic Test Scripts

There is a PL/SQL based Unit Testing package on GitHub ( utPLSQL ).

But, if you want an Oracle product, SQL Developer has Unit Testing capabilities.

You'll most likely need to create the scripts that interface with your automation software.

Automatic Schema Changes

Your second link mentioned LiquiBase. This is now integrated into SQL Developer. Demo for Dev Schema to Prod Schema is available here.

You will use the new command line interface (SQLcl) for automation, not SQL Plus.

Another alternative (for new tables) is the SQL Developer Cart feature. It can also work well for creating the INSERT scripts for the small Dimension Tables.

Code Review

One thing you may want to incorporate into your process is a way to automate parts of the Code Review process. I believe the key words to search in Google/Bing are "code sniffer".

SQL Developer has a custom highlighting feature that I use to detect "bad code".

Example

• TO_DATE() without a format.
• commit inside of a loop.
• when others then null ( other forum ).

The review process is manual for me. But, the custom highlighting helps the human code reviewer.

TL;DR

You need to look at SQL Developer's set of features.