Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 24 March 2013

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@sadhan.world catalog catalog/catalog@rman.world
connected to target database: SADHAN (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 SADHAN 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 SADHAN 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’;
}

2 comments:

  1. Hello Shahid,
    Very good information. Thanks.
    I need your help.
    I understand that when we exclude tablespace(s), the RMAN will not backup that tablespace/datafiles but let say that data is good in these tablespaces, is there a way to secure the data whatever is on the disk? Is there any command or parameter during restore/recover that would keep these datafile intact since they are not corrupted?

    any and all suggestions are appreciated.

    Why Exclude TS: we have a 32 TB database right now and we will be loading additional 20 TB data, this will take lot longer to Backup the database. Existing DB is taking 50 hours. so, we would like to back up only 32 TB and worst case scenario, we can reload the 20 TB from flat files - this will take 25 days.

    ReplyDelete
  2. Are you trying to make money from your visitors by popunder advertisments?
    If so, have you tried using Ero Advertising?

    ReplyDelete