Skip to content

Archive for

Mar 8

1 Comment

RMAN Configuring Tablespaces for Exclusion

In some cases you may want to exclude specified tablespace part of the regular backup schedule, as in these cases:

  •  A tablespace is easy to rebuild, so it is more cost-effective to rebuild it than back it up every day.
  • A tablespace contains temporary or test data that you do not need to back up.
  • A tablespace does not change often and therefore should be backed up on a different schedule from other backups.

Just a Example :

Step 1 : Create tablespace & User


SQL> create tablespace rman_ex datafile 'd:\backup\rman_ex01.dbf' size 50m;

Tablespace created.

SQL> create user rmanex identified by rmanex default tablespace rman_ex;

User created.

SQL> grant connect,resource to rmanex;

Grant succeeded.

SQL> conn rmanex/rmanex;
Connected.
SQL> create table a(empno number, city varchar2(20));

Table created.

SQL> insert into a values (001,'Riyadh');

1 row created.

SQL> insert into a values (002,'Mecca');

1 row created.

SQL> commit;

Commit complete.

Step 2: Configure tablespace Exclusion 


SQL> $rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 8 09:30:17 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ACEME (DBID=4060208819)

RMAN> configure  exclude for tablespace rman_ex;

using target database control file instead of recovery catalog
Tablespace RMAN_EX will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored.

Step 3: Backup Database , We can check here Whether that tablespace Include or Exclude.


RMAN> backup database;

Starting backup at 08-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 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=C:\APP\MAZAR\ORADATA\ACEME\SYSTEM01.DBF
input datafile file number=00003 name=C:\APP\MAZAR\ORADATA\ACEME\UNDOTBS01.DBF
input datafile file number=00002 name=C:\APP\MAZAR\ORADATA\ACEME\SYSAUX01.DBF
input datafile file number=00004 name=C:\APP\MAZAR\ORADATA\ACEME\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 08-MAR-11
channel ORA_DISK_1: finished piece 1 at 08-MAR-11
piece handle=C:\APP\MAZAR\FLASH_RECOVERY_AREA\ACEME\BACKUPSET\2011_03_08\O1_MF_NNNDF_TAG20
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 08-MAR-11
channel ORA_DISK_1: finished piece 1 at 08-MAR-11
piece handle=C:\APP\MAZAR\FLASH_RECOVERY_AREA\ACEME\BACKUPSET\2011_03_08\O1_MF_NCSNF_TAG20
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-MAR-11.

Not Included Here.

Step 4: How do we take tablespace backup while configured exlcude option.


RMAN> backup database noexclude;

Starting backup at 08-MAR-11
using channel ORA_DISK_1
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=C:\APP\MAZAR\ORADATA\ACEME\SYSTEM01.DBF
input datafile file number=00003 name=C:\APP\MAZAR\ORADATA\ACEME\UNDOTBS01.DBF
input datafile file number=00002 name=C:\APP\MAZAR\ORADATA\ACEME\SYSAUX01.DBF
input datafile file number=00005 name=D:\BACKUP\RMAN_EX01.DBF
input datafile file number=00004 name=C:\APP\MAZAR\ORADATA\ACEME\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 08-MAR-11
channel ORA_DISK_1: finished piece 1 at 08-MAR-11
piece handle=C:\APP\MAZAR\FLASH_RECOVERY_AREA\ACEME\BACKUPSET\2011_03_08\O1_MF_NNNDF_TAG20
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 08-MAR-11
channel ORA_DISK_1: finished piece 1 at 08-MAR-11
piece handle=C:\APP\MAZAR\FLASH_RECOVERY_AREA\ACEME\BACKUPSET\2011_03_08\O1_MF_NCSNF_TAG20
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-MAR-11

Step 5: Disable Configure Exclude method.


RMAN> configure exclude for tablespace rman_ex clear;

Tablespace RMAN_EX will be included in future whole database backups
old RMAN configuration parameters are successfully deleted

Ref : http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmconfa.htm#i1015029