0

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:

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

  • Do not assume people know what ADW is. Please add a description of it (Oracle's Cloud offering) and which version of the database you are using. Also add if you are using the Free Tier. – Michael Kutz Jun 30 at 13:24
  • Thanks Michael, I'll change that now – Ash Oldershaw Jun 30 at 15:12
0

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:

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.

| improve this answer | |
  • Thanks for the detailed answer, Michael - this is fantastic and just what we need. One extra question, will this also version items ORDS modules, handlers etc? – Ash Oldershaw Jun 30 at 15:17
  • @AshOldershaw - I don't know. That would be a good question for the Project Manager ( ThatJeffSmith ). You can check his web site to see if he has mentioned such a feature. – Michael Kutz Jun 30 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.