How to Exclude Specific Tablespace from the RMAN Backup in Oracle 11gR2
Posted by Mir Sayeed Hassan on February 20th, 2021
How to Exclude Specific Tablespace from the RMAN Backup in Oracle 11gR2
Check the database details
SQL> select instance_name,version,open_mode from v$instance, v$database; INSTANCE_NAME VERSION OPEN_MODE ---------------- ----------------- ----------- testdb1 11.2.0.4.0 READ WRITE
Check the RMAN Configuration
[oracle@testdb ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 20 17:44:41 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB (DBID=2848017595) RMAN> show all; RMAN configuration parameters for database with db_unique_name TESTDB are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/controlfile_auto_bkp/%F'; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_testdb1.f'; # default
Check the tablespace exist in databae
SQL> select * from v$tablespace; TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES 6 TESTING NO NO YES 6 rows selected.
Select the tablespace you need to exclude as “TESTING” Tablespace
RMAN> configure exclude for tablespace TESTING; using target database control file instead of recovery catalog Tablespace TESTING will be excluded from future whole database backups new RMAN configuration parameters are successfully stored
RMAN> SHOW EXCLUDE; RMAN configuration parameters for database with db_unique_name TESTDB are: CONFIGURE EXCLUDE FOR TABLESPACE 'TESTING';
Take the database backup, Here tablespace TESTING will be excluded from backup.
RMAN> BACKUP DATABASE; Starting backup at 20-FEB-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=50 device type=DISK file 5 is excluded from whole database backup channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/testdb/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/testdb/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/testdb/users01.dbf channel ORA_DISK_1: starting piece 1 at 20-FEB-21 channel ORA_DISK_1: finished piece 1 at 20-FEB-21 piece handle=/u01/app/oracle/fast_recovery_area/TESTDB/backupset/2021_02_20/o1_mf_nnndf_TAG20210220T175035_j326oms0_.bkp tag=TAG20210220T175035 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 20-FEB-21 Starting Control File and SPFILE Autobackup at 20-FEB-21 piece handle=/backup/rman/controlfile_auto_bkp/c-2848017595-20210220-00 comment=NONE Finished Control File and SPFILE Autobackup at 20-FEB-21
In Case if you want to override this exclusion feature by explicitly issuing keyword NOEXCLUDE in order to take database backup.
RMAN> BACKUP DATABASE NOEXCLUDE; Starting backup at 20-FEB-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=92 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/testdb/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/testdb/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/testdb/testing.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/testdb/users01.dbf channel ORA_DISK_1: starting piece 1 at 20-FEB-21 channel ORA_DISK_1: finished piece 1 at 20-FEB-21 piece handle=/u01/app/oracle/fast_recovery_area/TESTDB/backupset/2021_02_20/o1_mf_nnndf_TAG20210220T180704_j327njcr_.bkp tag=TAG20210220T180704 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 20-FEB-21 Starting Control File and SPFILE Autobackup at 20-FEB-21 piece handle=/backup/rman/controlfile_auto_bkp/c-2848017595-20210220-02 comment=NONE Finished Control File and SPFILE Autobackup at 20-FEB-21
Verify the TESTING Tablespace not exist in backup
RMAN> LIST BACKUP OF TABLESPACE TESTING; specification does not match any backup in the repository
Disable the exclude tablespace “TESTING” from RMAN Configuration
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE TESTING CLEAR; Tablespace TESTING 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 TESTDB are: RMAN configuration has no stored or default parameters
Note: In order to skip READONLY & OFFLINE tablespace you can issue below command as.
RMAN> BACKUP DATABASE SKIP READONLY, SKIP OFFLINE;