RMAN Hot Backup Script

The following unix shell script will do a full RMAN hot backup to your database and will copy the backup files compressed to the directory you will specify. This is a hot-backup, and the database must to be in ARCHIVELOG mode for this to work.

A backup retention policy with a recovery window of 2 days is defined in this script. With this retention policy RMAN will keep archive logs and backup files necessary to recover to any point of time within those 2 days in the recovery window. Older backups and archivelogs not needed to satisfy this retention policy will be automatically deleted by this script. No RECOVERY CATALOG is being used with this script, instead database controlfiles are used to record the RMAN repository information.

Make sure you set the rman CONFIGURE CONTROLFILE AUTOBACKUP parameter to ON in in RMAN in order to take extra backups of the controlfile and spfile (RMAN will not backup init.ora files) as extra protection.

This script will delete obsolete backups and not needed archive logs from the disks only after a successful backup. This means you don’t need to set up cronjobs or manually delete not needed backups. This is the beauty of using RMAN. It does this automatically with the commands: “…DELETE NOPROMPT OBSOLETE;…” and “…DELETE NOPROMPT EXPIRED BACKUP…” See last lines in the script.

The RMAN hot backup script rman_backup.sh

# Declare your ORACLE environment variables
export ORACLE_SID= (put your SID here)
export ORACLE_BASE= (put your ORACLE BASE here)
export ORACLE_HOME= (put your ORACLE_HOME here)
export PATH=$PATH:${ORACLE_HOME}/bin

# Start the rman commands
rman target=/ << EOF
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/(put backup directory here)/autobackup_control_file%F’;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
CROSSCHECK BACKUP;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT ‘/(put backup directory here)/databasefiles_%d_%u_%s_%T’;
sql ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT ‘/(put backup directory here)/archivelogs_%d_%u_%s_%T’ DELETE INPUT;
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT ‘/(put backup directory here)/controlfile_%d_%u_%s_%T’;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL RMAN_BACK_CH01;
}
EXIT;
EOF
#

Note: If you run this script on a database which was being backed up using OS methods and user managed backups, initially it will fail as it will look to satisfy the ‘… ARCHIVELOG ALL…” clause. That is it will try to backup archivelogs since day 1. Well, unless you have lots of money to allocate for storage, we know that is not practical to keep all archivelogs and with OS backups usually we delete them manually according to the chosen backup retention pollicy. The script once run initially will fail like this:

Starting backup at 24-MAR-08
current log archived
using channel ORA_DISK_1
archived log /u00/arch/1_2_650282994.arc not found or out of sync with catalog
trying alternate file for archivelog thread 1, sequence 2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/24/2008 10:43:29
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/arch/1_2_650282994.arc
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

As you can see the script assumes that you have all archivelogs, if that is not the case (most likely), and you have been periodically deleting them ie. crontab, or manually, you can always sync the existing RMAN catalog (or controlfile) by crosschecking what is there on the disk before you run it with the following command from RMAN:

For before Oracle 9i

RMAN> change archivelog all crosscheck;

or in Oracle 9i,10g

RMAN> crosscheck archivelog all;

This will crosscheck existing archivelogs and will show you the ones which are EXPIRED and need to be deleted from the RMAN catalog, that is, they are not on the disks anymore, cause you have deleted them not using RMAN. RMAN doesn’t know this, it expects them! To delete these misleading entries from your control file you will have to run the command:

RMAN> delete expired archivelog all;

After you delete the expired archivelog records from the control file you can run the script and take your backups.

To see how a recovery is done with a backup taken with this script on a New Host with the same directory structure search this blog for RMAN Recovery.

Backing up, Restoring and Recovering Read Only tablespaces with RMAN

This post is all about making historical data read only in the data warehouse. By making historical data read-only the aim is to reduce storage needs and backup times. RMAN can skip read-only tablespaces once backed up and will not repeatedly back them up.

1. Part One: Moving a large range partitioned table to a read only tablespace
2. Part Two: Backing up, Restoring and Recovering Read Only tablespaces with RMAN.



1. Part One: Moving a large range partitioned table to a read only tablespace

This post is about making historical data read only in a data warehouse. My goal is to convert some of the historical data in this data warehouse to read only and stop backing it up. This way I am planning to save space and improve daily backup times. I have a tablespace called EXAMPLE which has got a large partitioned table called PT and I would like to move the last 12 years data out of this table into a read only tablespace and stop backing up this old historical portion of the table.

Here is how the table data is:

select tablespace_name, table_name, partition_name, blocks, num_rows from user_tab_partitions
where table_name='PT'
order by partition_position

TABLE_NAME                     PARTITION_NAME                     BLOCKS   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------
PT                             PART_95                               748     100539
PT                             PART_96                               748     100540
PT                             PART_97                               748     100539
PT                             PART_98                               748     100539
PT                             PART_99                               748     100539
PT                             PART_00                               748     100539
PT                             PART_01                               748     100539
PT                             PART_02                               748     100539
PT                             PART_03                               748     100539
PT                             PART_04                               748     100539
PT                             PART_05                               748     100539
PT                             PART_06                               748     100539
PT                             PART_07                               748     100539
PT                             PART_MAX                                0          0

14 rows selected.

This are the segments and the total bytes in the EXAMPLE Tablespace which belong to table PT:

SEGMENT_NAME                        MB
----------------------------------- --------------------
IDX_PT_X_UNIQUE                     26 MB
PT                                  78.0625 MB

I want to move these segments, except for the current partition 2007, out of the EXAMPLE tablespace in a READ ONLY EXAMPLE_RO tablespace that I will create.I then used the following SQL to get the commands to move the partitions out of EXAMPLE to EXAMPLE_RO tablespace.

SELECT 'ALTER TABLE ' || TABLE_NAME || ' MOVE PARTITION ' || partition_name || ' TABLESPACE EXAMPLE_RO COMPRESS; '
FROM user_tab_partitions
WHERE TABLE_NAME = 'PT'
AND partition_name NOT IN('PART_MAX',   'PART_07')
ORDER BY partition_position DESC;



'ALTERTABLE'||TABLE_NAME||'MOVEPARTITION'||PARTITION_NAME||'TABLESPACEEXAMPLE_ROCOMPRESS;'                          
-------------------------------------------------------------------------------------------------------------------------
ALTER TABLE PT MOVE PARTITION PART_06 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_05 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_04 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_03 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_02 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_01 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_00 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_99 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_98 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_97 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_96 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_95 TABLESPACE EXAMPLE_RO COMPRESS;                                               

First I have to create the read-only tablespace as normal

SQL> create tablespace example_ro datafile '/usr/local/oracle/test01/example_ro.dbf' size 100M reuse autoextend on;

Tablespace created.

Then move the data in:
(Note that I also compress this data for maximum saving on space)

ALTER TABLE PT MOVE PARTITION PART_06 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_05 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_04 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_03 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_02 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_01 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_00 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_99 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_98 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_97 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_96 TABLESPACE EXAMPLE_RO COMPRESS;                                               
ALTER TABLE PT MOVE PARTITION PART_95 TABLESPACE EXAMPLE_RO COMPRESS;

Looking at the partitions and their tablespaces, it seems like I have got what I wanted.

TABLESPACE_NAME                TABLE_NAME PARTITION_     BLOCKS   NUM_ROWS
------------------------------ ---------- ---------- ---------- ----------
EXAMPLE_RO                     PT         PART_95           748     100539
EXAMPLE_RO                     PT         PART_96           748     100540
EXAMPLE_RO                     PT         PART_97           748     100539
EXAMPLE_RO                     PT         PART_98           748     100539
EXAMPLE_RO                     PT         PART_99           748     100539
EXAMPLE_RO                     PT         PART_00           748     100539
EXAMPLE_RO                     PT         PART_01           748     100539
EXAMPLE_RO                     PT         PART_02           748     100539
EXAMPLE_RO                     PT         PART_03           748     100539
EXAMPLE_RO                     PT         PART_04           748     100539
EXAMPLE_RO                     PT         PART_05           748     100539

TABLESPACE_NAME                TABLE_NAME PARTITION_     BLOCKS   NUM_ROWS
------------------------------ ---------- ---------- ---------- ----------
EXAMPLE_RO                     PT         PART_06           748     100539
EXAMPLE                        PT         PART_07           748     100539
EXAMPLE                        PT         PART_MAX            0          0

Next lets turn the EXAMPLE_RO tablespace read only.

SQL> alter tablespace example_ro read only;

Tablespace altered.

Good, now let's test our table PT see if it is accepting row insertions in partitions PART_2007 and PART_MAX and not in any other partition before that as is read only.

SQL> insert into pt values (111111111111, 2007, 'kubilay', 'kubilay', 'kubilay')

*
ERROR at line 1:
ORA-01502: index 'OE.IDX_PT_X_UNIQUE' or partition of such index is in unusable
state

Oooops, we broke the index with the move partition command as the index is left behind, still in the EXAMPLE tablespace. See below

SQL> select index_name, tablespace_name from user_indexes where index_name='IDX_PT_X_UNIQUE';

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
IDX_PT_X_UNIQUE                EXAMPLE

Let's rebuild this index to repoint it to the moved partitions.

SQL> alter index IDX_PT_X_UNIQUE rebuild;

Index altered.

And now we can do our test again

SQL> insert into pt values (111111111111, 2007, 'kubilay', 'kubilay', 'kubilay');

1 row created.

SQL> commit;

Commit complete.


SQL> insert into pt values (1111111111110, 2005,  'kubilay', 'kubilay', 'kubilay')
      *
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/usr/local/oracle/test01/example_ro.dbf'

Success! What we have is the current portions of this partitioned table PT in a READ WRITE tablespace, and this will be backed up as default by RMAN and the rest of the historical data, which will never change again, in a READ ONLY tablespace EXAMPLE_RO which will only be backed up once at the beginning and never again.


2. Part Two: Backing up, Restoring and Recovering Read Only tablespaces with RMAN

If you ever use an RMAN script (no catalog) with the option SKIP READONLY in the BACKUP.. command you have to make sure you have to have backed up at least once your read only datafiles (tablespace). Otherwise you are doing nothing and in case you loose everything and want to restore the database to a New Host you will not have the read only datafile! You will get an error like this:

Starting recover at 24-FEB-08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/24/2008 14:16:52
RMAN-06094: datafile 6 must be restored

Realising this mistake, you decide to copy the read only file from somewhere (tape, an OS backup) into the location on the New Host. But you will find out bitterly that this still doesn't work and you will get an error like:

RMAN> recover database until sequence=48;

Starting recover at 24-FEB-08
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/24/2008 14:23:55
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 6 belongs to an orphan incarnation
ORA-01110: data file 6: '/usr/local/oracle/test01/example_ro.dbf'

This happens because RMAN wants the Read Only file that backed up itself! Where is it? It asks. Well this is bad, and you are in a bad situation now.

What you should have don is to take a backup of this read-only tablespace just after you have made it read-only, by modifying your backup script slightly only for once, then use the modified RMAN command below in your backup script. Or just don't SKIP READONLY when you take the backup after you make the tablespace read-only. Make sure it backs up!

RMAN> backup database force;

Once you have taken the backup with the option above RMAN WILL KEEP the backup of this read-only tablespace and will NEVER delete it, no matter what retention policy you have, unless obviously you delete it with OS commands yourself. RMAN Will always keep the backupset containing the read only tablespace.

At the full database restore point you will have to get RMAN to check for read-only files, as it doesn't restore them!? I was puzzled with this myself, yes but that is the case. A simple full restore database will not work! So you have to restore like below, by indicating RMAN to check to see if any read-only tablespaces exist and need to be restored.

RMAN> restore database check readonly;

Starting restore at 24-FEB-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /usr/local/oracle/test01/example_ro.dbf
channel ORA_DISK_1: reading from backup piece /u00/test01/databasefiles_TEST01_1qj9rmti_58_20080222
channel ORA_DISK_1: restored backup piece 1
...

At this point RMAN will find the read-only tablespace/datafiles in the backupset, and will restore them before proceeding with restoring the other files. If you do not use the check readonly option during a full database restore RMAN will not restore the file and you will keep on getting the error.

RMAN monitoring commands

Some Quick RMAN monitoring commands I find useful and their explanations



1. LIST commands


To show the incarnation of the database after the OPEN RESETLOGS operation for a database. As the Oracle Docs say "When performing incremental backups, RMAN can use a backup from a previous incarnation or the current incarnation as a basis for subsequent incremental backups. When performing restore and recovery, RMAN can use backups from a previous incarnation in restore and recovery operations just as it would use backups from the current incarnation, as long as all archived logs are available."

RMAN> list incarnation;


Backup commands to see summaries of available backups.

RMAN> list backup of database summary;
RMAN> list expired backup of archivelog all summary;
RMAN> list backup of tablespace users summary;

To see backups eligible for recovery which have status AVAILABLE use:

RMAN> list recoverable backup of database;

To see expired backups which will probably be deleted in the next backup run - if you have set up, delete expired, in your scripts.

RMAN> list expired backup;


To see each backup by database file

RMAN> list backup by file;


To see a particular tablespace backup

RMAN> list backup of tablespace users;


To see a particular datafile backup

RMAN> list backup of datafile 4;


To see all archivelogs currently on disk even the ones that have not been backed up by RMAN use:

RMAN> list archivelog all; -- does not mean that they have been backed up;


Lists the archivelogs available for recovery by RMAN

RMAN> list backup of archivelog all;


To see the controlfiles backed up and available for recovery

RMAN> list backup of controlfile;

To see the SPFILE backup, which is done automatically with the controlfile if you have set CONTROLFILE AUTOBACKUP ON.

RMAN> list backup of spfile;



2. REPORT commands

To see what datafiles/tablespaces haven't been backed up in the last 3 days use:

RMAN> report need backup days=3;


To see for which datafiles/tablespaces you haven't got at least 2 copies of backups (Retention policy to REDUNDANCY 2).

RMAN> report need backup redundancy 2;


To see objects which have been created with options like UNRECOVERABLE (for reasons of speed) and have not been backed up yet use:

RMAN> report unrecoverable;


This is the best ever RMAN command, shows you your database. If you have read these so far and you haven't used this command you will like it.

RMAN> report schema;

Oracle LOBs and Redo Logging

Oracle LOBs are great data types to store unstructured data like pictures, audio files and movies. LOBs can also store semistructured data like XML and Word documents. A single LOB column in a table can store data up to 8 Terrabytes in Oracle 10g and up to 128 Terrabytes in Oracle 11g. Great stuff!Although databases are invented for the purposes of storing structured data there is a huge demand to store the 'other' stuff in the database as well. Many times I have been asked to store really weird stuff like core dumps of an OS system, java .jar files etc as binary files in the database. Brilliant thinking. But all this comes at a cost.

I think people see databases as hard disks, a store everything, persist everything -that might be needed for debugging later- kind of place. Absolutely everything, no discrimination whatsoever. Yes, databases are here to store (store is the magic word), are here to remember everything that a program or a person will query later. But this 'store everything' in the database approach does have a cost.What I mean by cost is this, when you have large CLOBs in a table which contains data such as XML which frequently changes (is being updated) by and XML update intensive application - i.e. used for object persistence -, lots of redo data which needs to be archived is generated. Thus, lots of log switches occur and things can get out of control easily if there is a high velocity of OLTP transactions constantly updating this CLOB.

The Test

I was testing this excessive redo generation on an Oracle 11g test database with 5 MB redo log size and on a CLOB data type which I updated 10,000 times with LOGGING and with NOLOGGING.My test produced the following results and the test script is at the end of this post

CLOBs with LOGGING 18 log switches 88 mb or redo

CLOBs with NOLOGGING 2 log switches 12 mb of redo

That is, quite a bit of redo!

I have been reading on this blog http://www.liberidu.com/blog/?p=264 about Oracle 11g Secure Files and how this new feature can help us "Deduplicate" the redundant information we store (XML) in CLOBs and achieve better performance and less redo logging. Other features like Automatic Compression and Deduplication of LOBs in Oracle 11g also suggest to help. Secure Files seems to be the way to go if storing XML in CLOBs.

My conclusion is, if you have CLOBs storing XML for persistence of your application objects in your database and these are large CLOBs updated frequently, you are better off not LOGGING them, otherwise be ready for surprises.

The test script

Use it on a test database only. The redo log sizes must be quite small to see the effects, in my case 5MB to see the effects.

--- ### CREATE THE TABLE
--- DROP AND RECREATE THE ABOVE TABLE AND
--- CHANGE THE STORAGE PARAMETERS ON THE ABOVE LINE to NOCACHE AND NOLOGGING
--- to try the NOLOGGING VERSION OF THE BLOB
create table thelobshouse
(
house_id number primary key,
theclob clob
)
lob(theclob) store as theclob_seg (cache logging)
/
--- ### INSERT 1 TEST RECORD
insert into thelobshouse values( 1, ' lobb ')
/
commit
/
--- ### CREATE THE PROCEDURE BELOW WHICH UPDATES THE BLOB
---
CREATE OR REPLACE PROCEDURE trashmylobz(p_id IN NUMBER, p_text IN VARCHAR2)
AS
l_clob CLOB;
BEGIN
SELECT theclob
INTO l_clob
FROM thelobshouse
WHERE house_id = p_id FOR
UPDATE;
FOR i IN 1 .. 10000
LOOP
-- dbms_lob.writeappend(l_clob, LENGTH(p_text), p_text);
update thelobshouse set theclob = rpad(p_text, 200000 ,p_text);
END LOOP;
COMMIT;
END;
/
---
--- ### RUN THE ABOVE PROCEDURE AND UPDATE THE LOB
---
exec trashmylobz(1, ' * ');
---

Scripts you will need to do the observations

1. You can use the Redo Log Generation and Log Switch SQL Script found on this blog to see the effects of this test to your blob. Get the script here: http://kubilaykara.blogspot.com/2008/02/redo-log-generation.html
2. Use the below Script to find out how much redo your session generated.

set lines 500
column username format a10
column program format a20
SELECT TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi')
logtime, (value/1048576) MB
FROM gv$sesstat st, gv$statname sn, gv$session se
WHERE sn.name = 'redo size'
AND sn.statistic# = st.statistic#
AND st.sid = se.sid
AND se.sid = ( SELECT sid FROM V$SESSION
WHERE audsid = SYS_CONTEXT('userenv','sessionid'))
AND value 0
/

Anyway, here is what hapened to my redo logs when the above test is run on a test database with a 5 MB redo log size
NOLOGGING case
(Using script 2 above)

SID USERNAME LOGTIME PROGRAM MB
---------- ---------- --------------- -------------------- ----------
123 KUBI 01-feb-08 13:50 sqlplus@kongo (TNS 13.8038025
V1-V3)

Look at hour 14 on 01-FEB-08 only 2 redo log switches and 13MB of redo with the NOLOGGING case

DAY       00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
--------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
25-JAN-08  10   0   1   0   1   0   1   0   1   0   1   0   0   1   1   1   0   1   0   1   0   1   8   0
26-JAN-08  10   0   1   0   1   0   7   0   1   0   2   1   2   0   2   0   1   1   1   0   1   1   1   1
27-JAN-08   9   1   0   1   0   1   6   1   0   1   2   0   2   0   2   0   1   0   2   0   1   0   2   1
28-JAN-08   9   0   1   0   1   0   1   0   1   1   0   1   2   0   1   0   1   0   1   0   1   0   7   1
29-JAN-08   9   1   0   1   0   1   1   0   1   0   1   0   3   0   1   0   1   1   0   1   0   1   7   1
30-JAN-08  10   1   0   1   1   0   1   0   1   0   1   0   3   0   1   0   1   1   0   1   0   1   7   1
31-JAN-08  11   1   0   1   0   1   1   0   1   0   1   0   0   1   1   2   0   1   1   0   1   0   9   1
01-FEB-08   8   0   1   1   0   1   0   1   0   1   0   1   2   1   2   0   0   0   0   0   0   0   0   0

LOGGING case
(Using script 1 above:

SID USERNAME LOGTIME PROGRAM MB
---------- ---------- --------------- -------------------- ----------
125 KUBI 01-feb-08 13:45 sqlplus@kongo 88.6263351

Look at hour 14 on 01-FEB-08, 18 (20-2) redo log switches and 88MB of redo with the LOGGING case

DAY       00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
--------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
25-JAN-08  10   0   1   0   1   0   1   0   1   0   1   0   0   1   1   1   0   1   0   1   0   1   8   0
26-JAN-08  10   0   1   0   1   0   7   0   1   0   2   1   2   0   2   0   1   1   1   0   1   1   1   1
27-JAN-08   9   1   0   1   0   1   6   1   0   1   2   0   2   0   2   0   1   0   2   0   1   0   2   1
28-JAN-08   9   0   1   0   1   0   1   0   1   1   0   1   2   0   1   0   1   0   1   0   1   0   7   1
29-JAN-08   9   1   0   1   0   1   1   0   1   0   1   0   3   0   1   0   1   1   0   1   0   1   7   1
30-JAN-08  10   1   0   1   1   0   1   0   1   0   1   0   3   0   1   0   1   1   0   1   0   1   7   1
31-JAN-08  11   1   0   1   0   1   1   0   1   0   1   0   0   1   1   2   0   1   1   0   1   0   9   1
01-FEB-08   8   0   1   1   0   1   0   1   0   1   0   1   2   1  20   0   0   0   0   0   0   0   0   0

20 Log Switches, with Logging Enabled! That is trashing!


Further Reading

* Oracle 11g Secure Files LOBs
* Thanks to Marco Gralike who pointed out this as well.
Also read Jonathan Gennick great post on OTN regarding deduplication and securefiles here: http://www.oracle.com/technology/oramag/oracle/07-sep/o57securefiles.html