Managing your Oracle database size with Puppet
The size of a database is one of the more important items you can and must configure when managing a database. Oracle has several different mechanisms for doing this. In this article, we will show you how you can use Puppet and the Enterprise Modules ora_config module to do this. At the end of this article, you’ll be able to choose the best way to manage your database size with Puppet.
When you create the database
The ora_database type allows you to create a database. Using this type, you can specify all the attributes of a database. Including a lot of the sizing attributes. Below, I’ve included part of a manifest that create’s a database.
ora_database{$db_name:
...
logfile_groups => [
{file_name => 'test1.log', size => '50M', reuse => true},
{file_name => 'test2.log', size => '50M', reuse => true},
],
default_tablespace => {
name => 'USERS',
datafile => {
file_name => 'users.dbs',
size => '50M',
reuse => true,
},
extent_management => {
type => 'local',
autoallocate => true,
}
},
datafiles => [
{file_name => 'file1.dbs', size => '100M', reuse => true},
{file_name => 'file2.dbs', size => '100M', reuse => true},
],
default_temporary_tablespace => {
name => 'TEMP',
type => 'bigfile',
tempfile => {
file_name => 'tmp.dbs',
size => '50M',
reuse => true,
autoextend => {
next => '10M',
maxsize => 'unlimited',
}
},
extent_management => {
type => 'local',
uniform_size => '10M',
},
},
undo_tablespace => {
name => 'UNDOTBS',
type => 'bigfile',
datafile => {
file_name => 'undo.dbs',
size => '50M',
reuse => true,
}
},
sysaux_datafiles => [
{file_name => 'sysaux1.dbs', size => '50M', reuse => true},
{file_name => 'sysaux2.dbs', size => '50M', reuse => true},
]
}
All of these settings are Puppet parameters. That means they are just used when creating the database but are not used to manage the attributes. Concrete, this means that if you change the Puppet manifest and specify a different value for the size of for example the default_tablespace, Puppet will not do anything. So if you want to manage these settings you need something else. In a future version of ora_config, we might change this so that Puppet can manage this property.
Managing tablespaces
Oracle has several different types of tablespaces. Besides temporary and redo tablespaces, it also has bigfile and smallfile tablespaces. Bigfile tablespaces were introduced in Oracle 10g. A bigfile tablespace is a special kind of tablespace than can only have a single (normally very large) datafile. Bigfile tablespaces can be up to 4G blocks that 128 TB with 32k blocks. What used to be normal tablespaces, are now called smallfile tablespaces.
A smallfile tablespace can only extend its size by adding more datafiles. A bigfile tablespace can be resized when you need to. How do we use these with Puppet?
Smallfile tablespace
In Puppet you can make a small file tablespace:
ora_tablespace{'my_tablespace@sid':
datafile => 'my_tablespace.dbf',
bigfile => 'no',
size => '10G'
autoextend => 'off'
}
ora_config allows you to specify only one datafile. And Oracle doesn’t allow you to change the size of a smallfile tablespace. These two facts together implicate that once a small file tablespace is made, you can no longer change its size. Let’s say for example, we change the manifest above and change the size:
ora_tablespace{'my_tablespace@sid':
datafile => 'my_tablespace.dbf',
bigfile => 'no',
size => '20G'
autoextend => 'off'
}
When Puppet runs the manifest, it will fail with the message:
Error: Current size is 10737418240, requested size: 21474836480. Oracle doesn't support resizing small file tablespaces
Using autoextend on smallfile tablespaces
If you want Oracle to manage the size, you can use auto extend. When using auto extend, the specified size is the initial size of the tablespace. You can specify the maximum size using the property max_size. The next property specifies the increments in which the tablespace will grow.
ora_tablespace{'my_tablespace2@test':
datafile => 'my_tablespace2.dbf',
bigfile => 'no',
size => '20G',
autoextend => 'on',
max_size => '60G',
next => '20G',
}
When your tablespace is growing, and you decide you need more space, You can change themax_size and next properties. Obviously you need to have enough storage available to allow this.
Bigfile tablespaces
Bigfile tablespaces are a better fit for Puppet. Oracle allows a bigfile tablespace to change in size. You can make a bigfile tablespace bigger or smaller. To make it smaller, the new size must be enough for Oracle to store all information. Let’s see how we can make a bigfile tablespace using Puppet.
ora_tablespace{'my_tablespace@test':
ensure => present,
datafile => 'my_tablespace1.dbf',
bigfile => 'yes',
autoextend => 'off',
size => '20G'
}
In this scenario, you can change the size property and Puppet will execute the change in size. Again you cannot make the tablespace smaller than it’s content.
Bigfile tablespaces and autoextend
You can also use bigfile tablespaces with autoextend:
ora_tablespace{'my_tablespace@test':
ensure => present,
datafile => 'my_tablespace1.dbf',
bigfile => 'yes',
autoextend => 'on',
max_size => '60G',
next => '20G',
size => '20G'
}
When you change the size and run Puppet, nothing happenes. When you put verbose logging on, you’ll see:
Info: Puppet ignores the Tablespace[size] attribute when autoextend is on.
Just like with the smallfile tablespaces, when you put autoextend on, Puppet will let Oracle manage the database size according to your settings of max_size and next. And just like with smallfile tablespaces, you can adjust these parameters.
ora_tablespace{'my_tablespace@test':
ensure => present,
datafile => 'my_tablespace1.dbf',
bigfile => 'yes',
autoextend => 'on',
max_size => '100G',
next => '40G',
size => '20G'
}
Conclusion
The ora_config module allows you to precisely describe the way your database should be configured. It allows you to specify the initial size and -if you want- the parameters describing the growth of your database. Using bigfile tablespaces is the best match for Puppet. When the operation requires, you can change the size of the tablespace. You can make them either bigger or smaller.
Want to know more?
When you want to know more, you can check the description of the ora_config module to see what other properties you can use to manage a database. You can also check out our reference implementation at github.
Image credits WikiMedia Commons
