The BACKUP OPTIMIZATION feature of Oracle RMAN identifies files not changed since the last backup as files for which the backup can be "optimized" -- i.e. *skipped*. I disagree with the title "OPTIMIZATION" and find it misleading.
Here is an example of a Read Only Tablespace and BACKUP OPTIMIZATION.
First a create a Read Only Tablespace with some data :
ora10204>sqlplus ' /as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Sun May 2 23:48:19 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create tablespace tbs_ro ;
Tablespace created.
SQL> connect hemant/hemant
Connected.
SQL> create table in_tbs_ro tablespace tbs_ro as select * from dba_objects;
Table created.
SQL> select tablespace_name, bytes/1024 from user_segments where segment_name = 'IN_TBS_RO';
TABLESPACE_NAME BYTES/1024
------------------------------ ----------
TBS_RO 6144
SQL> connect / as sysdba
Connected.
SQL> alter tablespace tbs_ro read only;
Tablespace altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ora10204>
These are my current RMAN parameters :
ra10204>rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sun May 2 23:49:52 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORT24FS (DBID=4163910544)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle_fs/ora10204/product/10.2.0.4/dbs/snapcf_ORT24FS.f';
RMAN>
My first BACKUP DATABASE run does include the datafile of the Read Only Tablespace :
RMAN> backup database plus archivelog;
Starting backup at 02-MAY-10
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=100 recid=6 stamp=717982288
input archive log thread=1 sequence=101 recid=21 stamp=717983423
channel ORA_DISK_1: starting piece 1 at 02-MAY-10
channel ORA_DISK_1: finished piece 1 at 02-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_annnn_TAG20100502T235023_5xv7t152_.bkp tag=TAG20100502T235023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 02-MAY-10
Starting backup at 02-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle_fs/Databases/ORT24FS/users01.dbf
input datafile fno=00002 name=/var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
input datafile fno=00001 name=/oracle_fs/Databases/ORT24FS/system01.dbf
input datafile fno=00003 name=/oracle_fs/Databases/ORT24FS/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAY-10
channel ORA_DISK_1: finished piece 1 at 02-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235028_5xv7t4z0_.bkp tag=TAG20100502T235028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/oracle_fs/Databases/ORT24FS/example01.dbf
input datafile fno=00006 name=/oracle_fs/Databases/ORT24FS/undotbs.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAY-10
channel ORA_DISK_1: finished piece 1 at 02-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235028_5xv7x4m8_.bkp tag=TAG20100502T235028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 02-MAY-10
channel ORA_DISK_1: finished piece 1 at 02-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_ncsnf_TAG20100502T235028_5xv7xfcp_.bkp tag=TAG20100502T235028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-MAY-10
Starting backup at 02-MAY-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=102 recid=23 stamp=717983534
channel ORA_DISK_1: starting piece 1 at 02-MAY-10
channel ORA_DISK_1: finished piece 1 at 02-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_annnn_TAG20100502T235214_5xv7xj5m_.bkp tag=TAG20100502T235214 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 02-MAY-10
RMAN>
RMAN> list backup of tablespace tbs_ro;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62 Full 1.20G DISK 00:01:30 02-MAY-10
BP Key: 62 Status: AVAILABLE Compressed: NO Tag: TAG20100502T235028
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235028_5xv7t4z0_.bkp
List of Datafiles in backup set 62
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
RMAN>
The second BACKUP DATABASE run also re-included the tablespace, as BACKUP OPTIMIZATION is set to OFF :
RMAN> backup database;
Starting backup at 02-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle_fs/Databases/ORT24FS/users01.dbf
input datafile fno=00002 name=/var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
input datafile fno=00001 name=/oracle_fs/Databases/ORT24FS/system01.dbf
input datafile fno=00003 name=/oracle_fs/Databases/ORT24FS/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAY-10
channel ORA_DISK_1: finished piece 1 at 02-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235608_5xv84rpn_.bkp tag=TAG20100502T235608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/oracle_fs/Databases/ORT24FS/example01.dbf
input datafile fno=00006 name=/oracle_fs/Databases/ORT24FS/undotbs.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAY-10
channel ORA_DISK_1: finished piece 1 at 02-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235608_5xv87rpr_.bkp tag=TAG20100502T235608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 02-MAY-10
channel ORA_DISK_1: finished piece 1 at 02-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_ncsnf_TAG20100502T235608_5xv882fx_.bkp tag=TAG20100502T235608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-MAY-10
RMAN>
RMAN> list backup of tablespace tbs_ro;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62 Full 1.20G DISK 00:01:30 02-MAY-10
BP Key: 62 Status: AVAILABLE Compressed: NO Tag: TAG20100502T235028
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235028_5xv7t4z0_.bkp
List of Datafiles in backup set 62
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
66 Full 1.20G DISK 00:01:27 02-MAY-10
BP Key: 66 Status: AVAILABLE Compressed: NO Tag: TAG20100502T235608
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235608_5xv84rpn_.bkp
List of Datafiles in backup set 66
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
RMAN>
I then set BACKUP OPTIMIZATION to ON :
RMAN> configure backup optimization on;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
RMAN>
This time, the tablespace did not get included in the backup (with RMAN reporting "skipping datafile 2; already backed up 2 time(s)") :
RMAN> backup database;
Starting backup at 03-MAY-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
skipping datafile 2; already backed up 2 time(s)
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle_fs/Databases/ORT24FS/users01.dbf
input datafile fno=00001 name=/oracle_fs/Databases/ORT24FS/system01.dbf
input datafile fno=00003 name=/oracle_fs/Databases/ORT24FS/sysaux01.dbf
input datafile fno=00005 name=/oracle_fs/Databases/ORT24FS/example01.dbf
input datafile fno=00006 name=/oracle_fs/Databases/ORT24FS/undotbs.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-10
channel ORA_DISK_1: finished piece 1 at 03-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_nnndf_TAG20100503T000008_5xv8d9mw_.bkp tag=TAG20100503T000008 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-MAY-10
channel ORA_DISK_1: finished piece 1 at 03-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_ncsnf_TAG20100503T000008_5xv8hyfv_.bkp tag=TAG20100503T000008 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-MAY-10
RMAN>
RMAN> list backup of tablespace tbs_ro;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62 Full 1.20G DISK 00:01:30 02-MAY-10
BP Key: 62 Status: AVAILABLE Compressed: NO Tag: TAG20100502T235028
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235028_5xv7t4z0_.bkp
List of Datafiles in backup set 62
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
66 Full 1.20G DISK 00:01:27 02-MAY-10
BP Key: 66 Status: AVAILABLE Compressed: NO Tag: TAG20100502T235608
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235608_5xv84rpn_.bkp
List of Datafiles in backup set 66
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
RMAN>
However, I can still run an explicit backup of the Tablespace :
RMAN> backup tablespace tbs_ro;
Starting backup at 03-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-10
channel ORA_DISK_1: finished piece 1 at 03-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_nnndf_TAG20100503T000347_5xv8m4fd_.bkp tag=TAG20100503T000347 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAY-10
RMAN> list backup of tablespace tbs_ro;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62 Full 1.20G DISK 00:01:30 02-MAY-10
BP Key: 62 Status: AVAILABLE Compressed: NO Tag: TAG20100502T235028
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235028_5xv7t4z0_.bkp
List of Datafiles in backup set 62
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
66 Full 1.20G DISK 00:01:27 02-MAY-10
BP Key: 66 Status: AVAILABLE Compressed: NO Tag: TAG20100502T235608
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235608_5xv84rpn_.bkp
List of Datafiles in backup set 66
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71 Full 5.69M DISK 00:00:01 03-MAY-10
BP Key: 71 Status: AVAILABLE Compressed: NO Tag: TAG20100503T000347
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_nnndf_TAG20100503T000347_5xv8m4fd_.bkp
List of Datafiles in backup set 71
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
RMAN>
Therefore, I confirm that BACKUP OPTIMIZATION applies only to a BACKUP DATABASE :
RMAN> backup database;
Starting backup at 03-MAY-10
using channel ORA_DISK_1
skipping datafile 2; already backed up 3 time(s)
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle_fs/Databases/ORT24FS/users01.dbf
input datafile fno=00001 name=/oracle_fs/Databases/ORT24FS/system01.dbf
input datafile fno=00003 name=/oracle_fs/Databases/ORT24FS/sysaux01.dbf
input datafile fno=00005 name=/oracle_fs/Databases/ORT24FS/example01.dbf
input datafile fno=00006 name=/oracle_fs/Databases/ORT24FS/undotbs.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-10
channel ORA_DISK_1: finished piece 1 at 03-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_nnndf_TAG20100503T000457_5xv8odn2_.bkp tag=TAG20100503T000457 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-MAY-10
channel ORA_DISK_1: finished piece 1 at 03-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_ncsnf_TAG20100503T000457_5xv8ro56_.bkp tag=TAG20100503T000457 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-MAY-10
RMAN>
If I disable BACKUP OPTIMIZATION, the Tablespace get's included in a BACKUP DATABASE :
RMAN> configure backup optimization off;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION OFF;
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> backup database;
Starting backup at 03-MAY-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle_fs/Databases/ORT24FS/users01.dbf
input datafile fno=00002 name=/var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
input datafile fno=00001 name=/oracle_fs/Databases/ORT24FS/system01.dbf
input datafile fno=00003 name=/oracle_fs/Databases/ORT24FS/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-10
channel ORA_DISK_1: finished piece 1 at 03-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_nnndf_TAG20100503T000946_5xv8yc80_.bkp tag=TAG20100503T000946 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/oracle_fs/Databases/ORT24FS/example01.dbf
input datafile fno=00006 name=/oracle_fs/Databases/ORT24FS/undotbs.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-10
channel ORA_DISK_1: finished piece 1 at 03-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_nnndf_TAG20100503T000946_5xv91bvk_.bkp tag=TAG20100503T000946 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-MAY-10
channel ORA_DISK_1: finished piece 1 at 03-MAY-10
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_ncsnf_TAG20100503T000946_5xv91mlr_.bkp tag=TAG20100503T000946 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-MAY-10
RMAN>
RMAN> list backup of tablespace tbs_ro;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62 Full 1.20G DISK 00:01:30 02-MAY-10
BP Key: 62 Status: AVAILABLE Compressed: NO Tag: TAG20100502T235028
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235028_5xv7t4z0_.bkp
List of Datafiles in backup set 62
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
66 Full 1.20G DISK 00:01:27 02-MAY-10
BP Key: 66 Status: AVAILABLE Compressed: NO Tag: TAG20100502T235608
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_02/o1_mf_nnndf_TAG20100502T235608_5xv84rpn_.bkp
List of Datafiles in backup set 66
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71 Full 5.69M DISK 00:00:01 03-MAY-10
BP Key: 71 Status: AVAILABLE Compressed: NO Tag: TAG20100503T000347
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_nnndf_TAG20100503T000347_5xv8m4fd_.bkp
List of Datafiles in backup set 71
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
74 Full 1.20G DISK 00:01:36 03-MAY-10
BP Key: 74 Status: AVAILABLE Compressed: NO Tag: TAG20100503T000946
Piece Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2010_05_03/o1_mf_nnndf_TAG20100503T000946_5xv8yc80_.bkp
List of Datafiles in backup set 74
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 3453431 02-MAY-10 /var/tmp/ORT24FS/datafile/o1_mf_tbs_ro_5xv7pb9v_.dbf
RMAN>
So, BACKUP OPTIMIZATION stops backups of Read Only Tablespaces (as long as there is at least 1 backup of the Tablespace !). However, this can be overrriden with a BACKUP TABLESPACE.
.
.
.
9 comments:
Dear Hemant,
Thank you for the post. It led to further analysis.
When the tablespace is made readonly checkpointing is done and headers of the datafile, controlfiles and redolog files are updated (sychronization). The tablespace need to be included in first backup to ensure a consistent backup exist for the read-only tablespace.
last_change# in v$datafile will show this checkpoint which is updated whenever a datafile is checkpointed or tablespace made read-only in this case.
Further investigation revealed that after enabling optimization the read-only tablespace is backed up twice before being skipped from the backup list. No matter how many times(checkpoint change) the tablespace is made read write and read only the optimization allowed to backup only twice.
Regards,
Balaji
Dear Hemant,
RMAN skips readonly tablespace datafiles after backed up twice.
RMAN> backup database;
Starting backup at 19-AUG-10
using channel ORA_DISK_1
skipping datafile 4; already backed up 2 time(s)
skipping datafile 6; already backed up 2 time(s)
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00005 name=C:\ORACLE\PRODUCT\ORADATA\ORCL\EXAMPLE01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 19-AUG-10
channel ORA_DISK_1: finished piece 1 at 19-AUG-10
piece handle=C:\WINDOWS\SYSTEM32\1PLLNJR1_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 19-AUG-10
Starting Control File and SPFILE Autobackup at 19-AUG-10
piece handle=C:\ORACLE\PRODUCT\RMANBACKUP\C-1254902050-20100819-0D comment=NONE
Finished Control File and SPFILE Autobackup at 19-AUG-10
Regards,
Balaji
I am bit confused with READ-ONLY tablespace, here is what I did:
1. Made a tablespace read-only
2. backed up read-only tablespace using rman.
3. Did full backup of database (rman) and skipped read-only tablespace.
4. Now after 3 weeks I don't see the backup of my read-only tablespace on the disk, yet when I did restore/recover from my full backup, the read-only file was also restored. I am confused - how can this happend when you don't have backup of read-only file,
the retention is set to 1.
Your reponse is much appreciated.
Thanks
Zahid
Zahid,
If you really deleted the backup of the read only tablespace I cannot explain how it got created --- unless it was such a new tablespace that *all* Archivelogs since the CREATE TABLESPACE were available on disk or in backups.
Hemant
I have always found this strange, but you can drop tables from a read only tablespace.
Nice post with a lot of information about rman, which is something I really haven't got to grips with yet!
Andrew,
Dropping a table only requires access to the data dictionary -- i.e. the SYSTEM tablespace. It does not need to update the read only tablespace.
Hemant
Hi Hemant,
I ran full compressed backup twice and the third time, it skipped all the ROT (roughly 400G of compressed data). That was really good. My backup time was drastically reduced.
During the day, I have a maintenance script that does "Crosscheck Backups", "delete obsolete" and "delete expired backupset".
After that script, my backup runs again. Now its the 4th time the backup ran and it started backing up all the ROT again.
I looked at the backup location, all my ROT backups were gone.
How did RMAN deleted the ROT considering that its "obsolete"? Is this the normal behavior?
Thanks
Ajay
Hi Hemant,
Does the "Delete Obsolete" command delete all the ROT backup(s)?
I backed up my database compressed -- backup size 660G.
When the RMAN ran for the fourth time, it couldn't find all the ROT backups and it had to start backup again. The ROT backup itself is about 400G and I believe somehow RMAN thought that they were "obsolete".
Has anyone seen any behavior?
Ajay
Gandhi Family,
Do you have logs of the runs of "DELETE OBSOLETE" ? Do they show deletion of the BackupPiece containing the ROT ?
Was the ROT changed to READ/WRITE at any time ? That would have caused an update to the datafiles and required a fresh backup.
Post a Comment