Home > Database, Oracle > Using Puppet to Manage Oracle

Using Puppet to Manage Oracle

This module contains custom types that can help you manage DBA objects in an Oracle database. It runs afterthe database is installed. IT DOESN’T INSTALL the Oracle database software. With this module, you can setup a database to receive an application. You can:

  • create a tablespace
  • create a user with the required grants and quota’s
  • create one or more roles
  • create one or more services

Setup

What oracle affects

The types in this module will change settings inside a Oracle Database. No changes are made outside of the database. Take the code from “https://github.com/hajee/oracle”.

Setup Requirements

To use this module, you need a running Oracle database. I can recommend Edwin Biemonds Puppet OraDb module. The Oracle module itself is based on easy_type.

Beginning with oracle module

After you have a running database, (See Edwin Biemonds Puppet OraDb module), you need to install easy_type, and this module.

1
2
puppet module install hajee/easy_type
puppet module install hajee/oracle

Usage

The module contains the following types:

tablespaceoracle_userrole and listener. Here are a couple of examples on how to use them.

listener

This is the only module that does it’s work outside of the Oracle database. It makes sure the Oracle SQL*Net listener is running.

1
2
3
4
listener {'SID':
  ensure  => running,
  require => Exec['db_install_instance'],
}

The name of the resource MUST be the sid for which you want to start the listener.

Specifying the SID

All types have a name like sid\resource. The sid is optional. If you don’t specify the sid, the type will use the first database instance from the /etc/oratab file. We advise you to use a full name, e.g. an sid and a resource name. This makes the manifest much more resilient for changes in the environment.

oracle_user

This type allows you to manage a user inside an Oracle Database. It recognises most of the options that CREATE USER supports. Besides these options, you can also use this type to manage the grants and the quota’s for this user.

1
2
3
4
5
6
7
8
9
10
oracle_user{sid/user_name:
  temporary_tablespace      => temp,
  default_tablespace        => 'my_app_ts,
  password                  => 'verysecret',
  require                   => Tablespace['my_app_ts'],
  grants                    => ['SELECT ANY TABLE', 'CONNECT', 'CREATE TABLE', 'CREATE TRIGGER'],
  quotas                    => {
                                  "my_app_ts"  => 'unlimited'
                                },
}

tablespace

This type allows you to manage a tablespace inside an Oracle Database. It recognises most of the options that CREATE TABLESPACE supports.

1
2
3
4
5
6
7
8
9
10
11
tablespace {'sid/my_app_ts':
  ensure                    => present,
  datafile                  => 'my_app_ts.dbf',
  size                      => 5G,
  logging                   => yes,
  autoextend                => on,
  next                      => 100M,
  max_size                  => 20G,
  extent_management         => local,
  segment_space_management  => auto,
}

You can also create an undo tablespace:

1
2
3
4
tablespace {'sid/my_undots_1':
  ensure                    => present,
  content                   => 'undo',
}

or a temporary taplespace:

1
2
3
4
5
6
7
8
9
10
11
12
tablespace {'sid/my_temp_ts':
  ensure                    => present,
  datafile                  => 'my_temp_ts.dbf',
  content                   => 'temporary',
  size                      => 5G,
  logging                   => yes,
  autoextend                => on,
  next                      => 100M,
  max_size                  => 20G,
  extent_management         => local,
  segment_space_management  => auto,
}

role

This type allows you to create or delete a role inside an Oracle Database. It recognises a limit part of the options that CREATE ROLE supports.

1
2
3
role {'sid/just_a_role':
  ensure    => present,
}

oracle_service

This type allows you to create or delete a service inside an Oracle Database.

1
2
3
oracle_service{'sid/my_app_service':
  ensure  => present,
}

init_param

this type allows you to manage your init.ora parameters

1
2
3
4
init_param{'sid/parameter/instance':
  ensure  => present,
  value   => 'the_value'
}

asm_diskgroup

This type allows you to manage your ASM diskgroups. Like the other Oracle types, you must specify the SID. But for this type it must be the ASM sid. Most of the times, this is +ASM1

1
2
3
4
5
6
7
8
9
10
11
asm_diskgroup {'+ASM1/REDO':
  ensure          => 'present',
  redundancy_type => 'normal',
  compat_asm      => '11.2.0.0.0',
  compat_rdbms    => '11.2.0.0.0',
  failgroups      => {
    'CONTROLLER1' => { 'diskname' => 'REDOVOL1', 'path' => 'ORCL:REDOVOL1'},
    'CONTROLLER2' => { 'diskname' => 'REDOVOL2', 'path' => 'ORCL:REDOVOL2'},
  }
}

At this point in time the type support just the creation and the removal of a diskgroup. Modification of diskgroups is not (yet) supported.

oracle_exec

this type allows you run a specific SQL statement or an sql file on a specified instance.

1
2
3
4
oracle_exec{"instance/drop table application_users":
  username => 'app_user',
  password => 'password,'
}

This statement will execute the sql statement drop table application_users on the instance names instance. There is no way the type can check if it has already done this statement, so the developer must support this by using puppet if statements.

1
2
3
4
5
oracle_exec{"instance/@/tmp/do_some_stuff.sql":
  username  => 'app_user',
  password  => 'password,'
  logoutput => on_failure,  # can be true, false or on_failure
}

This statement will run the sqlscript /tmp/do_some_stuff.sql on the instance named instance. Like the single statement variant, there is no way to check if the statement is already done. So the developer must check for this himself.

When you don’t specify the username and the password, the type will connect as sysdba.

oracle_thread

This type allows you to enable a thread. Threads are used in Oracle RAC installations. This type might not be very useful for regular use, but it is used in the Oracle RAC module.

1
2
3
oracle_thread{"instance_name/2":
  ensure  => 'enabled',
}

This enables thread 2 on instance named instance_name

Limitations

This module is tested on Oracle 11 on CentOS and Redhat. It will probably work on other Linux distributions. It will definitely not work on Windows. As far as Oracle compatibility. Most of the sql commands’s it creates under the hood are pretty much Oracle version independent. It should work on most Oracle versions.

Development

This is an open projects, and contributions are welcome.

OS support

Currently we have tested:

  • Oracle 11.2.0.2 & 11.2.0.4
  • CentOS 5.8
  • Redhat 5

It would be great if we could get it working and tested on:

  • Oracle 12
  • Debian
  • Windows
  • Ubuntu
  • ….

Oracle version support

Currently we have tested:

  • Oracle 11.2.0.2
  • Oracle 11.2.0.4

It would be great if we could get it working and tested on:

  • Oracle 12

Managable Oracle objects

Obviously Oracle has many many many more DBA objects that need management. For some of these Puppet would be a big help. It would be great if we could get help getting this module to support all of the objects.

If you have knowledge in these technologies, know how to code, and wish to contribute to this project, we would welcome the help.

Testing

Make sure you have:

  • rake
  • bundler

Install the necessary gems:

1
bundle install

And run the tests from the root of the source code:

1
rake test
Advertisements
Categories: Database, Oracle Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: