Manage your Oracle database schemas with Puppet

Use Puppet to manage your Oracle database schemas Puppet already has a name when it comes to managing the configuration of IT infrastructure. But when it comes to managing applications, others tools are better known. In this blog post, we are going to show you how the ora_config module allows you manage your database schemas with Puppet. The ora_config module allows you to seamlessly integrate management of infrastructure and application deployment.

The ora_schema_definition Puppet type

The ora_config Puppet module contains a type called ora_schema_definition. This is the Puppet type that allows you to manage your database schema. Let’s start with a simple definition:

ora_schema_definition{'MYAPP':
    ensure      => '1.0.0',
    schema_name => 'MYAPP,
    password    => 'verysecret',
    source_path => '/opt/stage/myapp/sql',
  }

In this example we tell Puppet, we need schema version 1.0.0 for MYAPP in the database. In layman’s terms now the following things will happen:

  • The Puppet type will log in to the database using username myapp and the very secret password and it will look at the version of the schema already available.
  • If the current version is lower than the specified version, Puppet will execute the upgrade SQL scripts in the source path until the correct version is reached.
  • If the current version is higher than the requested version, Puppet will execute the downgrade scripts until the requested version is reached.

Schema version?

What is this concept of a schema version? To administer the current version, Puppet uses a table called SCHEMA_VERSION. Here is the definition of this table:

CREATE TABLE schema_version
(  id                NUMBER
  ,application       VARCHAR2(255)
  ,version           VARCHAR2(255)
  ,description       VARCHAR2(255)
  ,installation_time TIMESTAMP(6)
);

Puppet uses this table to store the history of all schema versions applied. If the table doesn’t exists, Puppet will create it for you. Using this information, Puppet can tell what the state of the current schema in the database is. Puppet creates this table for every single schema/user you manage. This means, you can have multiple schemas with different versions in your database.

What about these upgrade and downgrade scripts.

In order for Puppet to do it’s magic, the upgrade and downgrade scripts, need to have specific names. Here is a listing of some upgrade scripts:

upgrades/0000_myapp_0.0.1_initial-schema.sql
upgrades/0001_myapp_0.0.2_add-user-table.sql
upgrades/0002_myapp_0.1.0_initial-release.sql

As you can see, all the file names have the following structure:

  • a four-digit sequence number
  • application name
  • version number
  • description

All fields are separated by an underscore. The upgrade scripts contain all SQL statements needed to upgrade the database schema to the desired state. In general upgrade scripts contain statements to create tables and indexes and add or remove columns, but you can also insert data into the lookup-tables or create database packages.

The downgrades directory contains scripts with the same names. The downgrade scripts contain the SQL statements needed to put the database in the state it was before. So if you add a column in an upgrade script, you’ll have to remove this column in the downgrade script.

But I always want the latest version!

The ensure property allows you to specify a specific version. When you specify a version, Puppet will make sure the specified version of the database schema is applied. For some companies, where strict control is mandatory, this is great. But for other companies or during development, this is to strict. Fortunately, Puppet also allows you to use a more convenient way:

ora_schema_definition{'MYAPP':
    ensure      => 'latest',
    schema_name => 'MYAPP,
    password    => 'verysecret',
    source_path => '/opt/stage/myapp/sql',
  }

In this example, the ensure property doesn’t contain a specific version number, but just the term latest. Using this definition, Puppet will look at the directory with the upgrade scripts and make sure they are all applied. So when you want to do a schema upgrade, the only thing you will have to do is add the new the upgrade and downgrade scripts on the source path, run Puppet and Puppet will take care of the rest.

Some more features

Sometimes you want to parameterize the upgrade end/or downgrade SQL-scripts. This is supported by the parameters property. Here is an example of a definition.

ora_schema_definition{'MYAPP':
    ensure      => 'latest',
    schema_name => 'MYAPP,
    password    => 'verysecret',
    source_path => '/opt/stage/myapp/sql',
    parameters  => {
      myapp_data_tablespace => 'MYAPP_DATA',
      myapp_idx_tablespace  => 'MYAPP_DATA',
  }

In the SQL-scripts you can use these parameters like this:

CREATE TABLE order(
...
) TABLESPACE &myapp_data_tablespace

There is one more thing ora_schema_definition has up its sleeve and that is the reinstall property. When you set the reinstall property to true, Puppet will drop all database objects for the specified user and will re-run all the upgrade scripts until the specified version is reached. This feature comes in very handy when you use Puppet in your CI environment. Puppet makes sure all old stuff is removed and al the tables and indexes are in a pristine state before you start your tests.

When the schema is not enough

The ora_schema_definition allows you to manage the database schema. The schema can also include database content. But sometimes the content is dependent on external factors. Factors like in what deployment zone the database is deployed. Or wether it is a development, test or production database. These are the factors Puppet is great at managing. The ora_config module has another type to help you here. This is the ora_record type. In a next blog post, we will describe this type.

The big picture

Recently Puppetlabs introduced Application Orchestration. Among other things, this allows you to use Puppet language to describe the relations and deployment order between different components (e.g. servers) in your infrastructure. As a result, Puppet can now make decisions on the order of actions it has to do when performing full infrastructure deployments. Here is a simple example of a database upgrade in an evironment without High Availability options:

  1. Put the ‘Application is temporarily unavailable page online’
  2. Shutdown the WebLogic Managed servers
  3. Upgrade the database
  4. Start the WebLogic Managed servers
  5. Remove the temporary page

We think that managing your database schemas with Puppet is an important component when you start to do application orchestration with Puppet.

Conclusion

When you need a seamless integration between provisioning your IT infrastructure and your applications, Puppet provides you all the tools you need. The ora_config module provides all tools required to manage your application setup inside an Oracle database. Check out the documentation of ora_config for more information about this module. In our next blog post “Reaching into your Oracle Database with Puppet” we are going to use Puppet to manage content inside of the database.