RMAN exclude

A couple of weeks ago i blogged about moving a large old 10g database to a new disk array, this process is continuing across all of our servers and this week i had to move another 10g one (although for the purposes of this post the version is largely irrelevent).

Our new simplified setup is just 2 filesystems - one for data/redo/undo/control and one for arch/redo/control - this is the default setup if you make use of db_create_file_dest and db_recovery_file_dest. This makes admin much easier than having loads of separate locations.

Anyhow we'd been limiting the size of an individual filesystem to 2TB (a recommendation from the unix team) - this particular database was about 3TB. This meant the method from my earlier post couldn't work directly - i would need some extra/modified steps.

Being lazy i wanted to do this in the minimal amount of steps and tried to find the simplest way of doing that.

The backup database as copy couldn't work as the total DB wouldn't fit into one location - i needed to split the datafiles over 2 mount points - so what the easiest way to do that?

Initially i had convinced myself i could run the same command as previously and just add a 'skip tablespace' type clause - a few failed attempt at guessing syntax and then a look at the documentation revealed that this isn't possible.

So what to do?

A quick scan through the docs reveals the exclude option (not something i'd had cause to use before)

This option allows specific tablespaces to be excluded from a full database backup - this is just what i want, and i can then afterwards backup the excluded tablespace separately.

By default nothing is excluded - as can be seen from this output

RMAN> show exclude;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name XXXX are:
RMAN configuration has no stored or default parameters

RMAN>

To exclude a tablespace we can run this

RMAN> configure exclude for tablespace bigone;

Tablespace BIGONE will be excluded from future whole database backups

new RMAN configuration parameters are successfully stored

RMAN>

this is now displayed by the show exclude command

RMAN> show exclude

2> ;

RMAN configuration parameters for database with db_unique_name XXXX are:

CONFIGURE EXCLUDE FOR TABLESPACE 'BIGONE';

RMAN>

If we now do a full database backup everything but this tablespace is backed up - we do however see this message

Starting backup at 14-MAY-2015 13:40:21

file 14 is excluded from whole database backup

file 16 is excluded from whole database backup

To remove the exclude we run

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE 'BIGONE' clear;

Tablespace BIGONE will be included in future whole database backups

old RMAN configuration parameters are successfully deleted

RMAN>

So now the full database backup is complete i now just do an individual backup of the tablespaces i excluded to the 2nd filesystem location

backup as copy tablespace bigone to destination '/fs2';

Now we have the whole database (spread across the 2 new filesystems) ready to switch to  as we did in the previous note.

So we switchover and all is well. Another useful trick i discovered when doing this is that after the switch the original files on the old array are classified as another copy of the database. So to now tidy up and remove all the files form the original locations we can just run

RMAN> delete copy of database;

This removes all the old files - i can see this being very useful if the original layout is a mess and intermixed with other database files - you know that rman will only remove the correct ones and take some of the human error element out of this.

I would say that this could be a sleeper problem - so make sure you switch it off - i can see this being activated and forgotten about. If you don;t then check the logs in detail you could end up with an unrecoverable tablespace at some point - so caveat emptor.....