Exclude Tablespace from RMAN Backup

Every day you take your backup whole database. Sometimes we may want to omit a specified tablespace from part of the regular backup schedule in that case: a tablespace the data don't change or the tablespace contains test data only or possibly a scenario may occur when we are cloning the database using the RMAN backup where we do not need all the schemas.

In such cases, either we might change our backup strategy or skip the certain tablespace in the database. To overcome this type of issue, we can configure the exclude option to exclude the specified tablespace from the Backup Database command. Though we can generally skip the tablespace during the Backup Database command but only when the tablespace is offline or readonly. The exclusion condition applies to any data files that we add to this tablespace in the future. 

C:\> rman  target sys/abcd@manoj.world catalog catalog/catalog@rman.world
connected to target database: MANOJ (DBID=63198018)

connected to recovery catalog database

RMAN> Configure exclude for tablespace USERS;
Tablespace USERS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
Now if you backup your database by:
RMAN> BACKUP DATABASE;
Then RMAN backs up all tablespaces in the database except users tablespace.

You can see which table is excluded from your backup strategy:
RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name MANOJ are:
CONFIGURE EXCLUDE FOR TABLESPACE USERS;
If  you already configure "exclude" option, even then you can backup the excluded tablespsace by explicitly specifying them in a Backup command or by specifying the “NOEXCLUDE” option on a Backup Database command as:

RMAN> backup database NOEXCLUDE;

You can disable the exclude tablespace feature as:
RMAN> configure exclude for tablespace USERS clear;
Tablespace USERS will be included in future whole database backups
old RMAN configuration parameters are successfully deleted

RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name MANOJ are:
RMAN configuration has no stored or default parameters
You need to back up a read-only tablespace only once after it has been made read-only. You can use the SKIP READONLY option to skip read-only datafiles. If you use the SKIP OFFLINE option, then the BACKUP command does not attempt to access offline datafiles. Use this option if the offline datafiles are not available. In order to skip READONLY and OFFLINE tablespace you can issue backup database command as:
RMAN>backup database skip READONLY, skip OFFLINE;

READ ONLY Tablespace Restore and Recovery:

To restore and recover from ‘READONLY’ tablespace you must take at least one backup of that tablespace after it has been made read only then after you can use the ‘SKIP READONLY’ command to exclude this tablespace from your backup script.

RMAN> Restore database check readonly;

While restoring you need to use ‘CHECK READONLY’ otherwise by default read only tablespace will not be restored and recovery will also bypass this tablespace. Finally when you try to open the database will fail.

How to recover database from excluded tablespace backup?

RMAN> Restore database skip tablespace ‘users’;

RMAN> alter database mount;

RMAN> SQL ‘alter database datafile 6 offline’;

RMAN> Recover database SKIP tablespace ‘USERS’;

You can use ‘skip forever’ command to skip the particular tablespace from your recovery scripts. RMAN specifies the DROP option of Alter database datafile ….OFFLINE when taking the datafiles that belongs to the tablespace offline before the restore. But in practice datafiles are still listed in V$DATAFILE and their associated tablespace are still ONLINE. So now you need to drop and recreate the tablespace.

RMAN> Recover database SKIP forever tablespace USERS;

You can also perform incomplete recovery from the excluded tablespace backup.
run {
set until logseq 6 thread 1;
restore database skip tablespace “USERS”;
recover database skip tablespace “USERS”;
SQL ‘alter database datafile 6 offline drop’;
SQL ‘alter database open resetlogs’;
}