RECOVER DATABASE starts with an update

There have been a few questions on my blog about the RECOVER DATABASE command :
Suppose I dont need incomplete recovery but I issued "recover database using backup controlfile"
I notice that I cannot issue normal recover command anymore.
Does this statement modifies something?

UPDATE : I've realised that there were some incorrect references to an "incomplete recovery" in the first version of this blog post. I have marked UPDATE wherever I changed this post.

UPDATE : This is a test that demonstrates that "something is modified".

Note, however, that once I issue RECOVER DATABASE USING BACKUP CONTROLFILE, Oracle knows that it will have to re-synchronise the controlfile -- which is only done by a RESETLOGS.

Here is a very simple test :

I STARTUP MOUNT and check the timestamps of datafiles and controlfiles :

ora10204>date
Sat May 29 19:49:34 SGT 2010
ora10204>ls -ltr sys*
-rw-r----- 1 ora10204 dba 985669632 May 29 19:40 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:40 sysaux01.dbf
ora10204>sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 29 19:49:44 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2087992 bytes
Variable Size             218104776 bytes
Database Buffers          654311424 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> !date
Sat May 29 19:50:03 SGT 2010

SQL> !ls -ltr sys*
-rw-r----- 1 ora10204 dba 985669632 May 29 19:40 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:40 sysaux01.dbf

SQL> !ls -ltr cont*
-rw-r----- 1 ora10204 dba 7389184 May 29 19:50 control02.ctl
-rw-r----- 1 ora10204 dba 7389184 May 29 19:50 control01.ctl

SQL> 

I find that the MOUNT updates the controlfile but not the datafiles.

I then issue a RECOVER DATABASE USING BACKUP CONTROLFILE :

SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL} 

From another terminal, I list the datafiles and controlfiles :

[root@linux64 ORT24FS]# date
Sat May 29 19:51:01 SGT 2010
[root@linux64 ORT24FS]# pwd
/oracle_fs/Databases/ORT24FS
[root@linux64 ORT24FS]# ls -ltr
total 3069876
-rw-r----- 1 ora10204 dba  131080192 May 29 19:25 temp01.dbf
-rw-r----- 1 ora10204 dba   52429312 May 29 19:33 redo01.dbf
-rw-r----- 1 ora10204 dba   52429312 May 29 19:34 redo02.dbf
-rw-r----- 1 ora10204 dba   52429312 May 29 19:40 redo03.dbf
-rw-r----- 1 ora10204 dba 1340874752 May 29 19:50 users01.dbf
-rw-r----- 1 ora10204 dba  104865792 May 29 19:50 undotbs.dbf
-rw-r----- 1 ora10204 dba  985669632 May 29 19:50 system01.dbf
-rw-r----- 1 ora10204 dba  304095232 May 29 19:50 sysaux01.dbf
-rw-r----- 1 ora10204 dba  104865792 May 29 19:50 example01.dbf
-rw-r----- 1 ora10204 dba    7389184 May 29 19:51 control02.ctl
-rw-r----- 1 ora10204 dba    7389184 May 29 19:51 control01.ctl
[root@linux64 ORT24FS]# 

Aah ! The RECOVER DATABASE command has updated all the datafiles. (the controlfile is always updated by a heartbeat every 3 seconds -- so it's timestamp may continue to be updated even if I don't issue any further commands -- but that is a different matter).

Returning to the RECOVER DATABASE, I CANCEL it and check timestamps :

SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> !date
Sat May 29 19:52:16 SGT 2010

SQL> !ls -ltr sys*
-rw-r----- 1 ora10204 dba 985669632 May 29 19:50 system01.dbf
-rw-r----- 1 ora10204 dba 304095232 May 29 19:50 sysaux01.dbf

SQL> !ls -ltr cont*
-rw-r----- 1 ora10204 dba 7389184 May 29 19:52 control02.ctl
-rw-r----- 1 ora10204 dba 7389184 May 29 19:52 control01.ctl

SQL> 

So, the datafiles are not updated now.

Of course, I cannot OPEN the database :

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL> 

UPDATE : "an incomplete recovery" corrected to "use an older controlfile" in the line below.
So, remember : A USING BACKUP CONTROLFILE, once initiated, is an attempt to use an older controlfile and must always be succeeded by an OPEN RESETLOGS.



As a continuation of the exercise, I complete the RECOVERy, applying what was my CURRENT online RedoLog file (redo03.dbf) :

SQL> shutdown;       
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2087992 bytes
Variable Size             218104776 bytes
Database Buffers          654311424 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
alter database openMedia recovery cancelled.
SQL>  resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'


SQL> recover database using backup controlfile;
ORA-00279: change 3553653 generated at 05/29/2010 19:40:56 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_05_29/o1_mf_1_9_%u_.arc
ORA-00280: change 3553653 for thread 1 is in sequence #9


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/Databases/ORT24FS/redo03.dbf
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> shutdown;    
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2087992 bytes
Variable Size             218104776 bytes
Database Buffers          654311424 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


SQL> alter database open resetlogs; 

UPDATE : This paragraph rewritten :
What is normally an INCOMPLETE RECOVERY is done as COMPLETE RECOVERY because I have applied the last CURRENT Online Redo Log as well. The RESETLOGS is required because I initiated a "USING BACKUP CONTROLFILE".


.
.
.

Database Links

Recently I came across a question on Database Links.

A Database Link, I define as, is an implicit connection from one database to another which is used to reference / extract / update data from/in the remote database.

Here are some "guidelines" I would advise :

1. Check with your IT Security department / Guidelines / SOPs / Standards about whether Database Links are permitted and, if so, what conditions/restrictions apply to them before creating a Database Link.

2. Use "shadow accounts" that can clearly identify where a Link connection came from as the Database Link accounts. That way, a DBA monitoring the remote database can identify a session -- whether for performance tuning or for application maintenance (e.g. if the schema or particular tables are to be taken "offline") or for investigating a "security incident".

3. Such "shadow accounts" have Read Only (or, if really necessary, Update) privileges on *selected* tables only. I see implementations of Database Links that connect to the schema that owns data. That is, in my opinion, not acceptable. Apply the principle of Least Privilege and use a "shadow account".

4. Unless it is explicitly permitted and approved (see point 1), a Database Link in a Test/Devt environment that connects to a Production schema is not to be created. As for UAT/SIT environments, review requirements, get approval and set a limited duration for the availability of the Database Link (have it dropped after the duration).

Here is an example implementation :

The "Remote" database which contains data that is being queried is called "ERPFIN".

The database account that contains the schema is called "SYSADM"

One "Client" database that needs to create a database link is called "FINANLTC"

The account in this database is called "BIZINT". This account needs to query the SYSADM.GL_BALANCES table.

Another "Client" database is called "DASHBRD"

The account in this database is called "SNRMGTVW". This account also needs to query the SYSADM.GL_BALANCES table.

Steps :

1. In "ERPFIN" create two database accounts "FROM_FINANLTC_BIZINT" and "FROM_DASHBRD_SNRMGTVW" with two system privileges "CREATE SESSION" and "CREATE SYNONYM".

2. Grant SELECT on SYSADM.GL_BALANCES to "FROM_FINANLTC_BIZINT" and "FROM_DASHBRD_SNRMGTVW".

3. Create a SYNONYM "GL_BALANCES" for SYSADM.GL_BALANCES in the two accounts "FROM_FINANLTC_BIZINT" and "FROM_DASHBRD_SNRMGTVW"

4. Attempt a local connection ERP_FIN and test that these two accounts can only SELECT from this one table alone and can do nothing else.

5. Setup the TNS connect-string for use in the "FINANLTC" and "DASHBRD" database ORACLE_HOMEs. A database link needs to reference the TNS connect-string from the ORACLE_HOME, not from the client desktop or application server. (Alternatively, include the TNS connect-string "as is" in the USING clause).

6. In FINANLTC and DASHBRD grant CREATE DATABASE LINK to BIZINT and SNRMGTVW respectively.

7. In FINANLTC create a *private* Database Link called "TO_ERPFIN_SYSADM" in the BIZINT schema that connects as "FROM_FINANLTC_BIZINT@ERPFIN" (i.e.

create database link TO_ERPFIN_SYSADM connect to FROM_FINANLTC_BIZINT using 'ERPFIN'; 

8. Similarly, create a private Database Link called "TO_ERPFIN_SYSADM" in the SNRMGTVW schema in the DASHBRD database.

9. Revoke CREATE DATABASE LINK from BIZINT and SNRMGTVW.

Note : If you do not have access to the BIZINT and SNRMGTVW passwords, use the method outlined in my previous post "Creating Database Links".

Benefits :

1. Such a setup allows the DBA in ERPFIN to identify every session and know *where* the session is initiated from (he can pinpoint the database and schema using the DBLink)

2. That information can be used for performance tuning / maintenance / investigations

3. The DBA in the BIZINT and DASHBRD databases can identify where a DBLink connects to

.

.

.

Cardinality Estimation

The Explain Plan output for an SQL statement shows the Execution Plan that the Optimizer has determined to be the "best" to satisfy the submitted SQL statement.
The Cardinality (which I define as "number of rows retrieved for a certain step in the execution plan") estimation done by the Optimizer is critical.

When a Table has an Index that can be used to satisfy a query, how does Oracle estimate Cardinality ? *From the Table's Column Statistics*. It then uses the Index to determine if, *for the estimated number of rows*, the Index would be an "optimal" retrieval method. Remember, it doesn't start with the Index, it starts with the estimated row count. This is based on the "selectivity" of the given query predicates.

Here is a simple demonstration of how the Optimizer doesn't start with the Index statistics but, rather, with the Table and Column Statistics :

First, I create a table with only 1 row and create two indexes on it. Remember that, in 10g (my example here is on 10.2.0.4), a CREATE INDEX automatically includes "COMPUTE STATISTICS".

SQL> drop table my_test_table purge;

Table dropped.

SQL> create table my_test_table as select * from dba_objects where 1=0;

Table created.

SQL> insert into my_test_table select * from dba_objects where object_id = 501;

1 row created.

SQL> create index my_test_objid_ndx on my_test_table(object_id);

Index created.

SQL> create index my_test_owner_ndx on my_test_table(owner);

Index created.

SQL> 

Obviously, the statistics on the two indexes show the presence of only 1 row in each index.

I next insert 50,725 rows of which 1,001 rows will satisfy my first test query -- on OBJECT_ID -- and 77 rows will satisfy my second test query -- on OWNER.

SQL> insert into my_test_table select * from dba_objects where object_id is not null;

50725 rows created.

SQL> alter table my_test_table modify (object_id not null);

Table altered.

SQL>
SQL> select count(*) from my_test_table where object_id between 1001 and 2001;

 COUNT(*)
----------
     1001

SQL> select count(*) from my_test_table where owner = 'HEMANT';

 COUNT(*)
----------
       77

SQL> 

I next Gather Statistics on the Table, including allowing Oracle to compute Histograms on the Columns. I explicitly deny permission to update statistics on the Indexes.

================================================
Side bar : 10g defaults CASCADE to DBMS_STATS.AUTO_CASCADE rather than "TRUE" or "FALSE". This default allows Oracle to "automatically" determine if it should update statistics on the Indexes. Had I left the CASCADE parameter to default, Oracle would have updated statistics on the Indexes as well, as the (again, another 10g default "automatic", table monitoring would have shown that more than 10% of the table has been updated.
================================================

SQL> -- Table statistics are updated
SQL> exec dbms_stats.gather_table_stats('','MY_TEST_TABLE',estimate_percent=>100,-
> method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>FALSE);

PL/SQL procedure successfully completed.

SQL> -- note above : Index statistics are not updated !
SQL>
SQL> select 'Table :', num_rows, blocks from user_tables where table_name = 'MY_TEST_TABLE'
 2  union
 3  select 'Ind OBJID_NDX : ' , num_rows, leaf_blocks from user_indexes where table_name = 'MY_TEST_TABLE'
 4  and index_name = 'MY_TEST_OBJID_NDX'
 5  union
 6  select 'Ind OWNER_NDX : ' , num_rows, leaf_blocks from user_indexes where table_name = 'MY_TEST_TABLE'
 7  and index_name = 'MY_TEST_OWNER_NDX'
 8  /

'TABLE:'           NUM_ROWS     BLOCKS
---------------- ---------- ----------
Ind OBJID_NDX :           1          1
Ind OWNER_NDX :           1          1
Table :               50726        748

SQL> 

Index Statistics reflect only 1 row but Table Statistics reflect the full Table. Although I haven't presented Column Statistics and Histograms, I know that they have been gathered. (You could verify this for yourself if you run a similar test).

I now run my first test query.

SQL> -- Execution Plans and Expected Row Counts
SQL> explain plan for select object_id, owner, object_name from my_test_table
 2  where object_id between 1001 and 2001;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4163210948

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |  1000 | 36000 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TEST_TABLE     |  1000 | 36000 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MY_TEST_OBJID_NDX |  1000 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OBJECT_ID">=1001 AND "OBJECT_ID"<=2001)  14 rows selected.  SQL> 

Oracle has estimated 1,000 values from the Index and a resulting 1,000 values from the Table.
We know that the Index statistics show that the Index MY_TEST_OBJID_NDX has only 1 row present. Yet, how and why did the Optimizer revert with the estimate of 1,000 ?
It computed this expectation from the Column statistics.
This is the information in the Event 10053 trace :

Table Stats::
 Table: MY_TEST_TABLE  Alias: MY_TEST_TABLE
   #Rows: 50726  #Blks:  748  AvgRowLen:  93.00
Index Stats::
 Index: MY_TEST_OBJID_NDX  Col#: 4
   LVLS: 0  #LB: 1  #DK: 1  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00
 Index: MY_TEST_OWNER_NDX  Col#: 1
   LVLS: 0  #LB: 1  #DK: 1  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
 -----------------------------------------
 BEGIN Single Table Cardinality Estimation
 -----------------------------------------
 Column (#4): OBJECT_ID(NUMBER)
   AvgLen: 5.00 NDV: 50725 Nulls: 0 Density: 1.9714e-05 Min: 2 Max: 55982
   Histogram: HtBal  #Bkts: 250  UncompBkts: 250  EndPtVals: 251
 Table: MY_TEST_TABLE  Alias: MY_TEST_TABLE
   Card: Original: 50726  Rounded: 1000  Computed: 999.53  Non Adjusted: 999.53
 -----------------------------------------
 END   Single Table Cardinality Estimation
 -----------------------------------------
 Access Path: TableScan
   Cost:  205.24  Resp: 205.24  Degree: 0
     Cost_io: 204.00  Cost_cpu: 18613351
     Resp_io: 204.00  Resp_cpu: 18613351
 Access Path: index (RangeScan)
   Index: MY_TEST_OBJID_NDX
   resc_io: 2.00  resc_cpu: 14653
   ix_sel: 0.019704  ix_sel_with_filters: 0.019704
   Cost: 2.00  Resp: 2.00  Degree: 1
 Best:: AccessPath: IndexRange  Index: MY_TEST_OBJID_NDX
        Cost: 2.00  Degree: 1  Resp: 2.00  Card: 999.53  Bytes: 0 

Thus, the "Card" estimate is from the Column Statistics. The Table has 50,726 rows, the Column has 50,725 distinct values (shown as NDV from the column statistics),ranging from 2 to 55,982. Oracle doesn't know if all the values are Integers but it computes an expected cardinality of 999.53 which is rounded up to 1,000. It also knows that these are most likely all distinct values. Therefore, the Index would also have 1,000 distinct entries !
(Note how the 10053 trace does show that the Index Statistics reflect only 1 single row, there being only 1 Leaf Block, 1 Distinct Key, 1 Leaf Block per Key and 1 Data Block per Key and a Clutering Factor of 1).

I now run my second test query :

SQL> explain plan for select object_id, owner, object_name from my_test_table
 2  where owner = 'HEMANT';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3831530497

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    77 |  2772 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TEST_TABLE     |    77 |  2772 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MY_TEST_OWNER_NDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OWNER"='HEMANT')

14 rows selected.

SQL> 

Here, Oracle estimated 77 rows in the table would satisfy the query predicate OWNER='HEMANT'. It also expects only one entry for 'HEMANT' in the Index -- this one entry is expected to return 77 rowids.

This is what the 10053 trace shows :

Table Stats::
 Table: MY_TEST_TABLE  Alias: MY_TEST_TABLE
   #Rows: 50726  #Blks:  748  AvgRowLen:  93.00
Index Stats::
 Index: MY_TEST_OBJID_NDX  Col#: 4
   LVLS: 0  #LB: 1  #DK: 1  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00
 Index: MY_TEST_OWNER_NDX  Col#: 1
   LVLS: 0  #LB: 1  #DK: 1  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
 -----------------------------------------
 BEGIN Single Table Cardinality Estimation
 -----------------------------------------
 Column (#1): OWNER(VARCHAR2)
   AvgLen: 6.00 NDV: 28 Nulls: 0 Density: 9.8569e-06
   Histogram: Freq  #Bkts: 28  UncompBkts: 50726  EndPtVals: 28
 Table: MY_TEST_TABLE  Alias: MY_TEST_TABLE
   Card: Original: 50726  Rounded: 77  Computed: 77.00  Non Adjusted: 77.00
 -----------------------------------------
 END   Single Table Cardinality Estimation
 -----------------------------------------
 Access Path: TableScan
   Cost:  205.03  Resp: 205.03  Degree: 0
     Cost_io: 204.00  Cost_cpu: 15476657
     Resp_io: 204.00  Resp_cpu: 15476657
 Access Path: index (AllEqRange)
   Index: MY_TEST_OWNER_NDX
   resc_io: 2.00  resc_cpu: 14653
   ix_sel: 1  ix_sel_with_filters: 1
   Cost: 2.00  Resp: 2.00  Degree: 1
 Best:: AccessPath: IndexRange  Index: MY_TEST_OWNER_NDX
        Cost: 2.00  Degree: 1  Resp: 2.00  Card: 77.00  Bytes: 0 

The "OWNER" column has a Frequency Histogram with 28 Buckets capturing all 28 Distinct Values. Therefore, it actually has an exact count of how many rows had "OWNER"='HEMANT' at the time that Table (and Column) Statistics were gathered. Since the query is for an Equality Range Scan on the Index, (see the "Access Path: index (AllEqRange)", it knows that the Index has only 1 "HEMANT" entry.


So, remember, "selectivity" information on columns determines the expected Cardinality which Oracle then uses to evaluate an Index, if available. It doesn't start with the Index statistics. Table and Column Statistics are more important than Index Statistics.

.
.
.

Read Only Tablespaces and BACKUP OPTIMIZATION

A Read Only Tablespace does not get updated. The datafile headers, too, are not updated.
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.

.
.
.

Database and SQL Training

A friend of mine, Stephane Faroult has created a site "Konagora" with free introductory tutorials on databases and SQL.
Stephane is the author of "The Art of SQL" and "Refactoring SQL Applications". Although both are targeted towards developers (who need not be on Oracle or Oracle-only), the latter book has even been useful to me as a DBA interested in Performance Tuning.

Konagora is a introduction to databases and SQL presented differently from your standard documentation and vendor training. Do visit and review some of his work. You could introduce our technical world to novices and those who only have a "starter" interest but do not know what we actually do -- those may want to be proficient developers and/or DBAs in the near future.
.
.
.