Ensure tablespaces

After you have installed your software and created your primary database, most of the time, you need to have some specific tablespaces. You can do this by adding some data to your hiera data.

In the editor tab, go to the directory hierdata\nodes and open the file ora19.playground.enterprisemodules.com. This file contains all the node-specific data.

Now add this data to it:

#
# Application specific stuff
#
ora_profile::database::db_tablespaces::list:
  APP_TS_1@DB01:
    ensure:     present
    size:       5G

This data tells Puppet to ensure that the tablespace APP_TS_1 needs to be available with the specified size. Make sure that you safe the changes before continuing. See the documentation what kind of properties you can use.

First Puppet run

Puppet runs will read this data and make sure the tablespace is available with the specified properties. Puppet will detect that the tablespace is unknown and create it since we will apply Puppet for the first time with this data.

puppet apply site.pp 

Let’s inspect the Puppet output. Somewhere near the top you’ll see:

Notice: Ensure DB tablespace(s) APP_TS_1@DB01

This is the information Puppet provides you about the tablespaces it manages. When you look at the Puppet output, somewhere near the end, you will see this:

Notice: /Stage[main]/Ora_profile::Database::Db_tablespaces/Ora_tablespace[APP_TS_1@DB01]/ensure: created

So Puppet created the tablespace.

Second Puppet run

One of the essential features of Puppet is that it is idempotent. Idempotent means it will not apply changes a second time. So if we rerun Puppet, it should see that the tablespace already exists with the specified properties and do nothing.

Let’s verify that and rerun Puppet:

puppet apply site.pp 

We still see the message at the top that Puppet manages the tablespace, but we no longer have the creation message, just as we expected.

Change tablespaces

Puppet is not only very easy when creating (or ensuring) tablespaces. It is also straightforward to change an existing tablespace. We will use the same declarative hiera data to change some of the tablespace properties. We will use the tablespace we created before.

Add some changes

A very common use-case is to change the size and the max size of the tablespace. Let increase the size from 5G to 10G

Let’s open the node-specific data file again. In the editor tab, go to the directory hierdata\nodes and open the file ora19.playground.enterprisemodules.com again. Replace the current setting for ora_profile::database::db_tablespaces::list to this one:

#
# Application specific stuff
#
ora_profile::database::db_tablespaces::list:
  APP_TS_1@DB01:
    ensure:     present
    size:       10G

and re-run Puppet again:

puppet apply site.pp 

If you look at the output, you see this:

Notice: /Stage[main]/Ora_profile::Database::Db_tablespaces/Ora_tablespace[APP_TS_1@DB01]/size: size changed 5368709120 to 10737418240

As a final check, let’s re-run Puppet and see if this change is also idempotent.

puppet apply site.pp 

No changes were detected, so it is indeed idempotent.

More tablespace properties

The previous example was very elementary. In reality, you probably need more options when defining a tablespace. No worries. Puppet supports all of the options sqlplus create tablespace command also supports. Let’s investigate some.

Autoextend

Let’s ensure that a second tablespace APP_TS_2 is available and that it will auto-extend. The initial size we need is 5G, any increments created are ‘2G’ and the maximum size we allow is 10G. Here is the definition of this tablespace. Let’s add this to the node hieradata and re-run Puppet

#
# Application specific stuff
#
ora_profile::database::db_tablespaces::list:
  APP_TS_1@DB01:
    ensure:     present
    size:       10G
  APP_TS_2@DB01:
    ensure:   present
    autoextend: 'on'
    max_size:  10G
    next:    2G
    size:    5G

And run Puppet:

puppet apply site.pp

We see:

Notice: /Stage[main]/Ora_profile::Database::Db_tablespaces/Ora_tablespace[APP_TS_2@DB01]/ensure: created

A temporary tablespace

Let’s add the ‘TMP_TS_1` temporary tablespace. Here is the hiera data you have to add to the of the list of tablespaces.

ora_profile::database::db_tablespaces::list:
...
  TMP_TS_1:
    contents: 'temporary'
    size:    5G

When we run Puppet, we see:

Notice: /Stage[main]/Ora_profile::Database::Db_tablespaces/Ora_tablespace[TMP_TS_1]/ensure: created

An undo tablespace

Let’s add the ‘UNDO_TS_1` undo tablespace. Here is the hiera data you have to add to the of the list of tablespaces.

ora_profile::database::db_tablespaces::list:
...
  UNDO_TS_1:
    contents: 'undo'
    size:    5G

When we run Puppet, we see:

Notice: /Stage[main]/Ora_profile::Database::Db_tablespaces/Ora_tablespace[UNDO_TS_1]/ensure: created

More information

See the documentation what kind of table space properties you can manage with Puppet use.

Manage profiles

One of the other database objects that are easy to manage with our Puppet modules is the database profile. A very common use-case is that we wish to change the properties of the default profile. The standard DEFAULT profile allows 10 failed login attempts before taking action. We want to change that to 5. Let’s go ahead and do this with Puppet.

In the editor tab, go to the directory hierdata\nodes and open the file ora19.playground.enterprisemodules.com. This file contains all the node-specific data.

Now add this data to it:

#
# Change the default profile
#
ora_profile::database::db_profiles::list:
  'DEFAULT@DB01':
    failed_login_attempts:  5

First Puppet run

Puppet runs will read this data and make sure the profile is available with the specified properties. Puppet will detect that the profile has a different value for failed_login_attemps, and it will change the value.

puppet apply site.pp 

Let’s inspect the Puppet output. Somewhere near the top you’ll see:

Notice: Ensure DB profile(s) DEFAULT@DB01

This is the information Puppet provides you about the profiles it manages. When you look at the Puppet output, somewhere near the end, you will see this:

Notice: /Stage[main]/Ora_profile::Database::Db_profiles/Ora_profile[DEFAULT@DB01]/failed_login_attempts: failed_login_attempts changed 10 to 5

So Puppet adjusted the value for failed_login_attempts of the DEFAULT profile.

Second Puppet run

One of the essential features of Puppet is that it is idempotent. Idempotent means it will not apply changes a second time. So if we rerun Puppet, it should see that the DEFAULT profile already exists with the specified properties and do nothing.

Let’s verify that and rerun Puppet:

puppet apply site.pp 

We still see the message at the top that Puppet manages the profile, but we no longer have the creation message, just as we expected.

More information

See the documentation what kind of profile properties you can manage with Puppet use.

Manage database users

Now let’s add some database users (schema owners). Again you only need to add some yaml settings to your hiera data.

For managing database users, we can use the hiera key ora_profile::database::db_users::list.

Let’s start off with a database use where the application tables will be created. We call it APP_SCHEMA_1

In the editor tab, go to the directory hierdata\nodes and open the file ora19.playground.enterprisemodules.com. This file contains all the node-specific data.

Now add this data to it:

#
# Database users
#
ora_profile::database::db_users::list:
  APP_SCHEMA_1@DB01:
    ensure: present
    profile: DEFAULT
    temporary_tablespace: TMP_TS_1
    default_tablespace: APP_TS_1
    grants:
    - CREATE SESSION
    - RESOURCE

First Puppet run

Puppet runs will read this data and make sure the database user is available with the specified properties. Puppet will detect that the user is unknown and create it since we will apply Puppet for the first time with this data.

puppet apply site.pp 

Let’s inspect the Puppet output. Somewhere near the top you’ll see:

Notice: Ensure DB user(s) APP_SCHEMA_1@DB01

This is the information Puppet provides you about the users it manages. When you look at the Puppet output, somewhere near the end, you will see this:

Notice: /Stage[main]/Ora_profile::Database::Db_users/Ora_user[APP_SCHEMA_1@DB01]/ensure: created

So Puppet created the database user.

Second Puppet run

One of the essential features of Puppet is that it is idempotent. Idempotent means it will not apply changes a second time. So if we rerun Puppet, it should see that the database user already exists with the specified properties and do nothing.

Let’s verify that and rerun Puppet:

puppet apply site.pp 

We still see the message at the top that Puppet manages the user, but we no longer have the creation message, just as we expected.

Interactive users

Puppet also checks and changes the password of the specified database user. For static accounts containing application tables, that is most of the time what you want. But for interactive database users, this is mostly unwanted. If a database user changes his password, we don’t want Puppet to change it back. Fortunately, Puppet has you covered here as well.

We are going to make the interactive user: APP_USER_1. The APP_USER_1 database user is an interactive user. We set the initial password and expiry property. But the parameter create_only tells Puppet to leave the properties as they are on future runs. Add this data to the db_users key:

  APP_USER_1@DB01:
    ensure: present
    password: verysecret
    profile: DEFAULT
    temporary_tablespace: TMP_TS_1
    default_tablespace: APP_TS_1
    expired: 'false'
    locked: false
    create_only:
    - expired
    - password
    grants:
    - CREATE SESSION
    - RESOURCE

First Puppet run

Puppet runs will read this data and make sure the database user is available with the specified properties. Puppet will detect that the user is unknown and create it since we will apply Puppet for the first time with this data.

puppet apply site.pp 

Let’s inspect the Puppet output. Somewhere near the top you’ll see:

Notice: Ensure DB user(s) APP_SCHEMA_1@DB01,APP_USER_1@DB01

This is the information Puppet provides you about the users it manages. When you look at the Puppet output, somewhere near the end, you will see this:

Notice: /Stage[main]/Ora_profile::Database::Db_users/Ora_user[APP_USER_1@DB01]/ensure: created

So Puppet created the database user.

Second Puppet run

One of the essential features of Puppet is that it is idempotent. Idempotent means it will not apply changes a second time. So if we rerun Puppet, it should see that the database user already exists with the specified properties and do nothing.

Let’s verify that and rerun Puppet:

puppet apply site.pp 

More information

See the documentation what kind of user properties you can manage with Puppet use.

Manage database parameters

You can also manage your database parameters with Puppet. When managing these with SQL you can use BOTH to manage both the MEMORY and the SPFILE` setting. With Puppet these are two distinctive values. Values that you have to manage separately.

Let’s change the maxmimum number of open cursors for this database from 300 to 600.

For managing database users, we can use the hiera key oora_profile::database::db_init_params::parameters.

In the editor tab, go to the directory hierdata\nodes and open the file ora19.playground.enterprisemodules.com. This file contains all the node-specific data.

Now add this data to it:

#
# Init.ora parameters
#
ora_profile::database::db_init_params::parameters:
  'SPFILE/OPEN_CURSORS@DB01':
    ensure:	present
    value:          600
  'MEMORY/OPEN_CURSORS@DB01':
    ensure:	present
    value:          600

First Puppet run

Puppet runs will read this data and make sure the parameters are available and have the correct value. Puppet will detect that the parameters have a different value since we will apply Puppet for the first time with this data.

puppet apply site.pp 

Let’s inspect the Puppet output. Somewhere near the top you’ll see:

Notice: Ensure DB init parameter(s) SPFILE/OPEN_CURSORS:*@DB01,MEMORY/OPEN_CURSORS:*@DB01

This is the information Puppet provides you about the parameters it manages. When you look at the Puppet output, somewhere near the end, you will see this:

Notice: /Stage[main]/Ora_profile::Database::Db_init_params/Ora_init_param[SPFILE/OPEN_CURSORS:*@DB01]/value: value changed 300 to 600
Notice: /Stage[main]/Ora_profile::Database::Db_init_params/Ora_init_param[MEMORY/OPEN_CURSORS:*@DB01]/ensure: created

So Puppet ensured that both parameters are present and set to the correct value.

In this case, we set both the MEMODY and SPFILE parameters. This is possible because the open_cursors is a dynamic value. For some parameters, you can only modify the SPFILE parameters. To make them active a restart of the database is required.

Second Puppet run

One of the essential features of Puppet is that it is idempotent. Idempotent means it will not apply changes a second time. So if we rerun Puppet, it should see that the database parameters are already set with the correct values and do nothing.

Let’s verify that and rerun Puppet:

puppet apply site.pp 

We still see the message at the top that Puppet manages the user, but we no longer have the creation message, just as we expected.

More information

See the documentation what kind of user properties you can manage with Puppet use.

Secure your database

A standard installed Oracle database is pretty open. Meaning insecure. The Center For Internet Security (CIS) has defined a benchmark for Oracle database security. With Puppet it is pretty easy to apply this benchmark to your database and make your database secure.

Apply the CIS benchmark

In the editor tab, go to the directory hierdata\nodes and open the file ora19.playground.enterprisemodules.com. This file contains all the node-specific data. Now look for the line that starts with role. It now contains the string : role::database. To make your database a secured database, you need to change it to role::secured_database.

First noop Puppet run

When you run Puppet now for the first time, it wil start inspecting the security and directly fix it. This is probably not what you want right now. For now to see what it will change, we will use the --noop switch for Puppet.

puppet apply site.pp --noop

You’ll see a large list of things that Puppet would change. The generated notices look like this:

Notice: Ora_cis::Rules::R_4_1_10[DB01]......

The part after Ora_cis::Rules:: is the CIS paragraf (e.g. rule) that this change belongs to. It is very likely that your application deosn work anymore if you apply ALL rules. You have to start inspecting and evaluating the changes.

Customise

Puppet allows you to customise what rules you want and what rules you want to skip. At the top of te output you see this message:

Alert: Scope(Ora_cis::Rules::R_2_2_5[DB01]): On Oracle 19 and higher O7_DICTIONARY_ACCESSIBILITY initialization parameter has been made obsolete. Rule skipped for DB01

We don’t want this message anymor. So we know for sure that we want to skip rule R2_2_5 for database DB01. Let’s add a skip rule to our hiera data.

ora_profile::database::cis_rules::ignore:
  - r_2_2_5

The ora_cis module also issues warnings about rules that are not enforcable by Puppet. To bypass these warnings, a set of standard skip rules have been already added. We want to merge our own skip rule with the ones already available. To do this, we need to provide Puppet with the merge instructions. Here is the hiera data for this:

lookup_options:
  ora_profile::database::cis_rules::ignore:
    merge:
      strategy: deep
      merge_hash_arrays: true

Second noop Puppet run

No when we run Puppet, the warning will no longer be issues.

puppet apply site.pp --noop

The real deal

You probably will have to apply more customisations for your database, but for teaching porposes, we are ready to apply all these changes to the database. Let’s run Puppet for real:

puppet apply site.pp

You like it?

Do you like what you see here and want to test this on your own infrastructure? No problem. You can sign up for a free trial.

If you have any questions, don’t hesitate to contact us.