Knowing all about your database

In this playground, we will show you how easy it is to use the standard available facts to see all there is to know about your database. We will also show you how you can make your own facts based on content that is available in records in your database.

You can use all of these facts in Puppet manifests describing your Oracle database systems. These facts are also available on the PuppetDB, and you can use them to report on all of your systems.

The playground system

The playground system contains a pre-installed puppet agent. There is no server available, so to run Puppet, you have to use the puppet apply command. The playground text will guide you with this.

On the playground system, you will find an Oracle database with the sid DB01.

Working in the playground

Under this text, you see the working area. You can inspect the system and issue any command you like in the terminal. In the editor window, you can see the Puppet production environment. You can edit anything you wish. The documentation tab shows the documentation for the ora_profile module.


This system will self destruct in about one hour. So please don’t use it to build or create anything you wish to keep!

Subjects in this playground

The playground contains the following sub paragraphs:
  • Other basic Oracle fact
  • Create your own database facts
Happy exploring!!


For many operations, it is essential to know all about your Oracle home. To make it easy to use this information, we have a fact that you can use in all of your Oracle related Puppet code. It is called ora_install_homes

Here is a way to see the content of this fact:

puppet facts | grep -A19 ora_install_homes

Here is the output:

    "ora_install_homes": {
      "/u01/app/oracle/product/": {
        "name": "OraDB19Home1",
        "product_version": "",
        "opatch_version": "",
        "installed_patches": [
        "defined_sids": [
        "running_sids": [
        "running_listeners": [

As you can see, all the information you might need in a Puppet run:

  • The version of Oracle used in this home
  • The version of OPatch installed in this home
  • The patches installed into this home
  • SID’s that are defined in the oratab using this home.
  • SID’s currently running from this home
  • Listeners running from this home

We use these facts for example, to determine if all required patches are installed and to see if the required version of Opatch is available. But you are free to use these facts in any way that helps you.

Other basic Oracle fact

Besides information about your Oracle home, you also need to know about the settings of your database.

The version of Oracle your database is running

The fact ora_version tell’s you what version of Oracle is running. Here is a command to see this:

puppet facts | grep -A4 ora_version

And here is the output you might get:

"ora_version": [
    "name": "",
    "sid": "DB01"

Determine the kind of database you are running

Besides the versio there is more information you need to determine the kind of atabase you are running. To see those facts use this command:

puppet facts | grep -A3 ora_is_

This will give you an overview of all facts that tell you what kind of database you are running at the current node. Here is an example output:

    "ora_is_cluster": [
        "name": "FALSE",
        "sid": "DB01"
    "ora_is_container_db": [
        "name": "FALSE",
        "sid": "DB01"
    "ora_is_pluggable_db": [
        "name": "FALSE",
        "sid": "DB01"
    "ora_is_primary_db": [
        "name": "TRUE",
        "sid": "DB01"
    "ora_is_root_db": [
        "name": "FALSE",
        "sid": "DB01"
    "ora_is_seed_db": [
        "name": "FALSE",
        "sid": "DB01"

Helper functions

Having these facts is very beneficial. But the way they are structured is pretty tough to use them. That’s why we created some helper functions.

  • ora_config::is_cluster
  • ora_config::is_container_db
  • ora_config::is_pluggable_db
  • ora_config::is_primary_db
  • ora_config::is_root_db
  • ora_config::is_seeddb

Go to the editor and create a new file test_db_functions.pp in /etc/puppetlabs/code/environments/production/manifests. Add this content to is:

$database = 'DB01'
if ora_config::is_cluster($database) { 
  notice "${database} is a custer"
} else {
  notice "${database} is NOT a custer"
if ora_config::is_container_db($database) {
  notice "${database} is a container database"
} else {
  notice "${database} is a NOT container database"
if ora_config::is_pluggable_db($database) { 
  notice "${database} is a pluggable database"
} else {
  notice "${database} is NOT a pluggable database"
if ora_config::is_primary_db($database) {
  notice "${database} is a primarry database"
} else {
  notice "${database} is NOT a primarry database"
if ora_config::is_root_db($database) {
  notice "${database} is a root database"
} else {
  notice "${database} is a NOT root database"
if ora_config::is_seed_db($database) {
  notice "${database} is a seed database"
} else {
  notice "${database} is a NOT seed database"

Now run this Puppet manifest

Now let’s run this Puppet manifest and see what it will tell us”

puppet apply test_db_functions.pp

This is the output you will see:

Notice: Scope(Class[main]): DB01 is NOT a custer
Notice: Scope(Class[main]): DB01 is a NOT container database
Notice: Scope(Class[main]): DB01 is NOT a pluggable database
Notice: Scope(Class[main]): DB01 is a primarry database
Notice: Scope(Class[main]): DB01 is a NOT root database
Notice: Scope(Class[main]): DB01 is a NOT seed database
Notice: Compiled catalog for in environment production in 0.08 seconds
Notice: Applied catalog in 0.14 seconds

Create your own database facts

Besides the standard supplied facts, we also provide an API to make your own facts based on records in the database.

Create the fact

In the editor, go to the directory /etc/puppetlabs/code/environments/modules/profile. In here, create a subdirectory lib and under that a subdirectory facter. Here we will create the file my_own_db_fact.rb.

Add this content to that file:

require "puppet_x/enterprisemodules/oracle/define_fact"

ora_define_fact('banner') { "select banner as name from v$version" }

inspect the fact

To check if everything works, enter the next command:

puppet facts | grep -A4 ora_banner

This should show you this:

"ora_banner": [
    "name": "Oracle Database 19c Enterprise Edition Release - Production",
    "sid": "DB01"

Let’s dissect this

So to define a fact base upon a query in your database, you can use ora_define_fact. Between the { and the } put your query in a string. The query MUST return one record. And the value that you want to report must be used as the name column. The fact name you use will be prepended with the ora_ string.## 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.