30 September, 2011

Another example of GATHER_TABLE_STATS and a Histogram

In response to a forum thread "Why not selecting index path .", I ran up this example :
SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL>
SQL> drop table test_index_count5 purge;

Table dropped.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> create table test_index_count5 (a number, b varchar2(15));

Table created.

SQL>
SQL> insert into test_index_count5
2 select 2, 'A String'
3 from dual
4 connect by level <= 1000000; 1000000 rows created. SQL>
SQL> insert into test_index_count5
2 select 3, 'Another String'
3 from dual
4 connect by level <= 2; 2 rows created. SQL>
SQL> create index ind_a_tic5 on TEST_INDEX_COUNT5(a);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,tabname=>'TEST_INDEX_COUNT5',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL>
SQL> set serverout off
SQL> select /*+ gather_plan_statistics */ * from test_index_count5 where a=3;

A B
---------- ---------------
3 Another String
3 Another String

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 2qu5fmh95yc4c, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test_index_count5 where a=3

Plan hash value: 1766596593

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.11 | 2393 |
|* 1 | TABLE ACCESS FULL| TEST_INDEX_COUNT5 | 1 | 500K| 2 |00:00:00.11 | 2393 |
-------------------------------------------------------------------------------------------------

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

1 - filter("A"=3)


18 rows selected.

SQL> set serverout on
SQL>
SQL> -- execute the same query in different forms a few times
SQL> select * from test_index_count5 where a=3;

A B
---------- ---------------
3 Another String
3 Another String

SQL> select * FROM test_index_count5 where a=3;

A B
---------- ---------------
3 Another String
3 Another String

SQL> -- execute dummy ddl to force a hard parse for subsequent queries
SQL> comment on table TEST_INDEX_COUNT5 is 'TEST_INDEX_COUNT5';

Comment created.

SQL> SELECT * from test_index_count5 where a=3;

A B
---------- ---------------
3 Another String
3 Another String

SQL> select * from TEST_INDEX_COUNT5 where a=3;

A B
---------- ---------------
3 Another String
3 Another String

SQL> select * from test_index_count5 WHERE a=3;

A B
---------- ---------------
3 Another String
3 Another String

SQL>
SQL> -- check col_usage
SQL> select * from sys.col_usage$
2 where obj# = (select object_id from user_objects where object_name = 'TEST_INDEX_COUNT5');

no rows selected

SQL> -- Remember ! Col_usage is updated only on flush database monitoring
SQL> -- normally this is done periodically by the background
SQL> -- but we'll forcefully invoke it
SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select * from test_index_count5 where a=3;

A B
---------- ---------------
3 Another String
3 Another String

SQL> select * from sys.col_usage$
2 where obj# = (select object_id from user_objects where object_name = 'TEST_INDEX_COUNT5');

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ----------
TIMESTAMP
---------
85617 1 1 0 0 0 0 0
30-SEP-11


SQL>
SQL> -- re gather_stats
SQL> exec dbms_stats.gather_table_stats(user,tabname=>'TEST_INDEX_COUNT5',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL>
SQL> -- now execute the query
SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ * from test_index_count5 where a=3;

A B
---------- ---------------
3 Another String
3 Another String

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 2qu5fmh95yc4c, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test_index_count5 where a=3

Plan hash value: 4117659660

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 6 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_INDEX_COUNT5 | 1 | 2 | 2 |00:00:00.01 | 6 |
|* 2 | INDEX RANGE SCAN | IND_A_TIC5 | 1 | 2 | 2 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------

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

2 - access("A"=3)


19 rows selected.

SQL> set serveroutput on
SQL>
SQL> select * from user_tab_histograms where table_name = 'TEST_INDEX_COUNT5' and column_name = 'A';

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
--------------------------------------------------------------------------------------------------------------
TEST_INDEX_COUNT5
A
1000000 2


TEST_INDEX_COUNT5
A
1000002 3



SQL>


The test cases in that forum thread were differently using 100,000 rows and 1million rows. As Tom Kyte observed in his response when the same test case was posted to him (see the link in the forums thread) : In fact, you probably don't even need the method_opt, just the running of the queries should be enough (but might not be in this truly massively skewed example - we might not sample 2 at all - it might not get picked up) .

Whenever I ran the 1million row test case, Oracle didn't create the right histogram until I explicitly added ESTIMATE_PERCENT 100.

Remember that when you have very high data skew, sampling may not be able to identify the skew. The greater the skew, the larger should your sampling be to identify the skew.
.
.
.

22 September, 2011

Oracle Android App

Yesterday, I downloaded and installed the Oracle Android App onto my Samsung Galaxy S phone.
I must say : the App provides a quick overview of what's happening in the Oracle World.
It is certainly much faster and better organised than the FaceBook App.
It provides listings of news updates and a few blog posts.

.
Published with Blogger-droid v1.7.4

21 September, 2011

An Index that is a "subset" of a pre-existing Index

It is a common [mis]understanding that creating a new Index that is a subset of an existing Index is an unnecessary overhead. The argument goes "if you have an Index on (A,B,C) this Index can be used to satisfy queries where predicate 'A' is specified, so creating an additional Index on (A) alone is an unnecessary overhead".
It is true that the the Index could be used, but the Cost Based Optimizer also computes a "COST" for using the Index. A composite Index is larger (in terms of Leaf Blocks to navigate) then a single-column Index. Similarly, at times, the composite Index becomes so large that many queries are executed as FullTableScans !

This question arose in a forums discussion today on how a CREATE INDEX an be speeded up. The CREATion of a Index on (A) can actually read the existing Index on (A,B,C) without having to do a FullTableScan. Furthermore, the new Index on (A) can satisfy queries on the single predicate better than the existing Index.

Here I demonstrate both cases :
1. A CREATE INDEX reading an existing Index and avoiding a FullTableScan
2. An Index that is a "subset" of an existing Index being useful

I first setup the "demo" table
SQL> drop table objects_list purge;

Table dropped.

SQL> -- assume a "largeish" table -- the Remarks column makes the row length greater
SQL> create table objects_list as
2 select owner, object_name, subobject_name, object_type, created, status,
3 dbms_random.string('X',200) as Remarks
4 from dba_objects
5 /

Table created.

SQL>
SQL> -- in the absence of object_id, these columns constitute the Unique Index
SQL> create unique index objects_list_u1 on objects_list (owner, object_name, subobject_name, object_type);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','OBJECTS_LIST',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks, num_rows from user_tables where table_name = 'OBJECTS_LIST';

BLOCKS NUM_ROWS
---------- ----------
2867 76955

SQL>


Now, I verify if I can use the composite Index to execute a query on the leading column alone :
SQL> --- we should be able to use the Unique Index when querying for the leading column alone
SQL> explain plan for
2 select owner, count(*) from objects_list where owner like 'O%' group by owner order by owner;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 544682346

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 39 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 3 | 21 | 39 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_LIST_U1 | 5141 | 35987 | 39 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

2 - access("OWNER" LIKE 'O%')
filter("OWNER" LIKE 'O%')

15 rows selected.

SQL> select owner, count(*) from objects_list where owner like 'O%' group by owner order by owner;

OWNER COUNT(*)
------------------------------ ----------
OBE 75
OE 84
OE1 55
OLAPSYS 719
ORACLE_OCM 8
ORDDATA 248
ORDPLUGINS 10
ORDSYS 2532
OUTLN 9
OWBSYS 2
OWBSYS_AUDIT 12

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 544682346

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 39 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 3 | 21 | 39 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_LIST_U1 | 5141 | 35987 | 39 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

2 - access("OWNER" LIKE 'O%')
filter("OWNER" LIKE 'O%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed

SQL>

Yes. The composite Index is used even for the single column predicate "OWNER". The query does 30 block gets. (I've actually executed the query twice to eliminate recursive calls at the second execution).

What if I created an Index on "OWNER" alone ? It would be a "subset of the existing Index.
SQL> create index objects_list_n1 on objects_list (owner);

Index created.

SQL>
SQL> --- now Oracle can use the leaner index for the same query
SQL> --- check the "Cost"
SQL> explain plan for
2 select owner, count(*) from objects_list where owner like 'O%' group by owner order by owner;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1721948621

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 14 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 3 | 21 | 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_LIST_N1 | 5141 | 35987 | 14 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

2 - access("OWNER" LIKE 'O%')
filter("OWNER" LIKE 'O%')

15 rows selected.

SQL>
SQL> select owner, count(*) from objects_list where owner like 'O%' group by owner order by owner;

OWNER COUNT(*)
------------------------------ ----------
OBE 75
OE 84
OE1 55
OLAPSYS 719
ORACLE_OCM 8
ORDDATA 248
ORDPLUGINS 10
ORDSYS 2532
OUTLN 9
OWBSYS 2
OWBSYS_AUDIT 12

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1721948621

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 14 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 3 | 21 | 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_LIST_N1 | 5141 | 35987 | 14 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

2 - access("OWNER" LIKE 'O%')
filter("OWNER" LIKE 'O%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed

SQL>


The new Index is used ! It has a lower "COST" (14 for the new Index versus 39 for the composite Index) and doing fewer block gets (12 versus 30).

So, this new Index is better than the existing one for such queries.

How was the Index created ? I actually obtained a trace of the CREATE INDEX run.
LOCK TABLE "OBJECTS_LIST" IN SHARE MODE  NOWAIT

create index objects_list_n1 on objects_list (owner)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 1 0
Execute 1 0.27 0.29 1 77000 714 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.27 0.29 1 77002 715 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 187

Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE OBJECTS_LIST_N1 (cr=77119 pr=1 pw=184 time=0 us)(object id 0)
76955 SORT CREATE INDEX (cr=76965 pr=1 pw=0 time=857870 us)
76955 INDEX FAST FULL SCAN OBJECTS_LIST_U1 (cr=76965 pr=1 pw=0 time=1649397 us)(object id 88291)
Thus the CREATE INDEX OBJECTS_LIST_N1 actually did an Index Fast Full Scan of the existing Index OBJECTS_LIST_U1 !

.
.
.



20 September, 2011

RMAN Tips -- 2

Incremental Backup from a particular SCN

RMAN allows you to take an Incremental Backup "FROM SCN". Why ? For the purpose of updating a Standby Database.


[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 20 23:35:38 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
9250081

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 20 23:36:07 2011

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

connected to target database: ORCL (DBID=1229390655)

RMAN> backup as compressed backupset incremental from SCN=9250021 database format '/addtl/oracle/tmp/%U';

Starting backup at 20-SEP-11

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=34 device type=DISK
backup will be obsolete on date 27-SEP-11
archived logs will not be kept or backed up
RMAN-06755: WARNING: datafile 1: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 2: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 3: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 4: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 5: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 6: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 7: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 8: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 9: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 10: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 11: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 12: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 13: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 15: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 16: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 17: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
RMAN-06755: WARNING: datafile 18: incremental-start SCN is too recent; using checkpoint SCN 9249229 instead
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00016 name=/addtl/oracle/oradata/orcl/Uniform_64KB.dbf
input datafile file number=00018 name=/addtl/oracle/oradata/orcl/MSSM.dbf
input datafile file number=00015 name=/addtl/oracle/oradata/orcl/UNDO.dbf
input datafile file number=00017 name=/usr/tmp/X.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00014 name=/home/oracle/app/oracle/oradata/orcl/hemant_01.dbf
channel ORA_DISK_1: starting piece 1 at 20-SEP-11
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf
input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf
input datafile file number=00013 name=/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf
channel ORA_DISK_2: starting piece 1 at 20-SEP-11
channel ORA_DISK_2: finished piece 1 at 20-SEP-11
piece handle=/addtl/oracle/tmp/52mn2bl3_1_1 tag=TAG20110920T233703 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: finished piece 1 at 20-SEP-11
piece handle=/addtl/oracle/tmp/51mn2bl2_1_1 tag=TAG20110920T233703 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:16

using channel ORA_DISK_1
using channel ORA_DISK_2
backup will be obsolete on date 27-SEP-11
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 20-SEP-11
channel ORA_DISK_1: finished piece 1 at 20-SEP-11
piece handle=/addtl/oracle/tmp/53mn2bpb_1_1 tag=TAG20110920T233703 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-SEP-11

RMAN>


RMAN "automatically" determines an appropriate SCN.

Is this database backup usable for any other purpose ?
Is it "visible" ?

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 20 23:41:06 2011

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

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
130 B F A DISK 31-MAY-11 1 1 YES TAG20110531T224447
132 B A A DISK 31-MAY-11 1 1 YES TAG20110531T224945
133 B F A DISK 31-MAY-11 1 1 NO TAG20110531T224947
134 B F A DISK 05-JUL-11 1 1 YES TAG20110705T230230
135 B F A DISK 05-JUL-11 1 1 YES TAG20110705T230230
136 B F A DISK 05-JUL-11 1 1 NO TAG20110705T230640
137 B F A DISK 13-JUL-11 1 1 NO TAG20110713T224918
138 B F A DISK 03-SEP-11 1 1 NO TAG20110903T232648
139 B F A DISK 03-SEP-11 1 1 YES TAG20110903T234602
140 B F A DISK 03-SEP-11 1 1 YES TAG20110903T234602
141 B F A DISK 03-SEP-11 1 1 NO TAG20110903T235631
142 B A A DISK 04-SEP-11 1 1 YES TAG20110904T001454
143 B A A DISK 04-SEP-11 1 1 YES TAG20110904T001454
144 B F A DISK 04-SEP-11 1 1 NO TAG20110904T001459
145 B A A DISK 16-SEP-11 1 2 NO TAG20110916T225318
146 B A A DISK 16-SEP-11 1 2 NO TAG20110916T225318
147 B F A DISK 16-SEP-11 1 1 NO TAG20110916T225321
148 B A DISK 20-SEP-11 1 1 YES TAG20110920T233703
149 B A DISK 20-SEP-11 1 1 YES TAG20110920T233703
150 B A DISK 20-SEP-11 1 1 YES TAG20110920T233703

RMAN>

Note the last 3 entries. The Backup Level (column "LV") is not updated. There is no controlfile autobackup !
Note the warning towards the end of the backup run : "archived logs will not be kept or backed up" ! I must explicitly backup the archivelogs that I need for the Standby.
(The BACKUP FROM INCREMENTAL SCN cannot include a "PLUS ARCHIVELOG")


[oracle@localhost ~]$ cd /addtl/oracle/tmp
[oracle@localhost tmp]$ ls -ltr
total 2596
-rw-rw---- 1 oracle oracle 106496 Sep 20 23:37 52mn2bl3_1_1
-rw-rw---- 1 oracle oracle 1343488 Sep 20 23:39 51mn2bl2_1_1
-rw-rw---- 1 oracle oracle 1196032 Sep 20 23:39 53mn2bpb_1_1
[oracle@localhost tmp]$


However, I can list a datafile individually, thus "seeing" the backup done today :

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
135 Full 659.84M DISK 00:04:08 05-JUL-11
BP Key: 135 Status: AVAILABLE Compressed: YES Tag: TAG20110705T230230
Piece Name: /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w8s7_.bkp
List of Datafiles in backup set 135
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 8286449 05-JUL-11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
140 Full 2.81G DISK 00:10:28 03-SEP-11
BP Key: 140 Status: AVAILABLE Compressed: YES Tag: TAG20110903T234602
Piece Name: /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxvd6_.bkp
List of Datafiles in backup set 140
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 9008606 03-SEP-11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
149 Incr 1.27M DISK 00:02:13 20-SEP-11
BP Key: 151 Status: AVAILABLE Compressed: YES Tag: TAG20110920T233703
Piece Name: /addtl/oracle/tmp/51mn2bl2_1_1
Keep: NOLOGS Until: 27-SEP-11
List of Datafiles in backup set 149
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Incr 9250390 20-SEP-11 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN>


There are 3 different SCNs now. The one I specified in the INCREMENTAL FROM SCN. The one that RMAN "automatically" used. And the SCN tht appears as the Checkpoint SCN in the LIST BACKUP.

.
.
.

17 September, 2011

Very successful Golden Gate workshop at SG RACSIG

Jigar led the Singapore RACSIG Golden Gate workshop very well today. It was quite apparent that he knows the product well and is enthusiastic about sharing his knowledge.
Ravi also added a few observations and asked "review questions" of the members present.

Thank you very much Jigar.
Thank you Ravi.

.
.
.

16 September, 2011

RMAN Tips -- 1

Restoring ArchiveLogs to an alternate location


RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
806 1 237 A 11-SEP-11
Name: /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_09_16/o1_mf_1_237_776rnnow_.arc


RMAN>
RMAN> backup archivelog all;

Starting backup at 16-SEP-11
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=237 RECID=806 STAMP=762043927
channel ORA_DISK_1: starting piece 1 at 16-SEP-11
channel ORA_DISK_2: starting archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=238 RECID=807 STAMP=762043998
channel ORA_DISK_2: starting piece 1 at 16-SEP-11
channel ORA_DISK_1: finished piece 1 at 16-SEP-11
piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp tag=TAG20110916T225318 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: finished piece 1 at 16-SEP-11
piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp tag=TAG20110916T225318 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
Finished backup at 16-SEP-11

Starting Control File and SPFILE Autobackup at 16-SEP-11
piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_16/o1_mf_s_762044001_776rq1r5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-SEP-11

RMAN>


So, I have two ArchiveLogs (SEQUENCE# 237 and 238) backed up to two BackupSets in /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/

I now delete the two ArchiveLogs :

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ cd /addtl/oracle/flash_recovery_area/ORCL/archivelog/2011_09_16/
[oracle@localhost 2011_09_16]$ ls -l
total 21604
-rw-rw---- 1 oracle oracle 22071296 Sep 16 22:52 o1_mf_1_237_776rnnow_.arc
-rw-rw---- 1 oracle oracle 17920 Sep 16 22:53 o1_mf_1_238_776rpyof_.arc
[oracle@localhost 2011_09_16]$
[oracle@localhost 2011_09_16]$ rm *.arc
[oracle@localhost 2011_09_16]$ ls -l
total 0
[oracle@localhost 2011_09_16]$


If I were to restore the ArchiveLogs to an alternate / test server, where I have copied the BackpSets to /tmp and want to restore the ArchiveLogs to /tmp as well, I must first begin with CATALOGing the BackupSets :

[oracle@localhost2 2011_09_16]$ ls -l /tmp/*bkp
-rw-rw---- 1 oracle oracle 22072832 Sep 16 22:57 /tmp/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp
-rw-rw---- 1 oracle oracle 19456 Sep 16 22:57 /tmp/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp
[oracle@localhost2 2011_09_16]$
[oracle@localhost2 2011_09_16]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 16 22:58:59 2011

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

connected to target database: ORCL (DBID=1229390655)

RMAN> catalog start with '/tmp/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/

List of Files Unknown to the Database
=====================================
File Name: /tmp/hsperfdata_oracle/2469
File Name: /tmp/.X0-lock
File Name: /tmp/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp
File Name: /tmp/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /tmp/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp
File Name: /tmp/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp

List of Files Which Where Not Cataloged
=======================================
File Name: /tmp/hsperfdata_oracle/2469
RMAN-07517: Reason: The file header is corrupted
File Name: /tmp/.X0-lock
RMAN-07517: Reason: The file header is corrupted

RMAN>

I can now restore the ArchiveLogs to /tmp (Note : The SET ARCHIVELOG DESTINATION command must be in a Run Block. It cannot be an independent command like a SET UNTIL) :

RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> run
2> {
3> set ARCHIVELOG DESTINATION TO '/tmp';
4> restore archivelog sequence between 237 and 238;
5> }

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 16-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/tmp
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=237
channel ORA_DISK_1: reading from backup piece /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp
channel ORA_DISK_2: starting archived log restore to user-specified destination
archived log destination=/tmp
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=238
channel ORA_DISK_2: reading from backup piece /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp
channel ORA_DISK_2: piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzx5_.bkp tag=TAG20110916T225318
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_16/o1_mf_annnn_TAG20110916T225318_776rpzf6_.bkp tag=TAG20110916T225318
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-SEP-11

RMAN>
RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
811 1 237 A 11-SEP-11
Name: /tmp/1_237_749171050.dbf

810 1 238 A 16-SEP-11
Name: /tmp/1_238_749171050.dbf


RMAN>


Why would I want to / need to restore ArchiveLogs to another location ?
a. On the alternate / test server I do not have the same mount point point for ArchiveLogs as the primary.
b. On the alternate / test server, the mount point is much smaller but I do have another mount point that has enough space to restore ArchiveLogs to.


Question : What other way(s) can be used to restore ArchiveLogs to another location ?

.
.
.

11 September, 2011

Outer Join Queries

Outer Join queries are difficult to understand initially. Effectively, an Outer Join query allows the developer to create dummy rows (with NULLs for the returned columns) for a table when a join to another table would fail. Thus, the developer can see rows in the other table for which there are no corresponding entries in the "join failed" table.

A simple example (from the classic SCOTT schema) is a DEPTNO 40 which has been created in the DEPT table but for which no employees exist in the EMP table. Thus a join between DEPT and EMP on DEPTNO would, normally, never return the row for DEPTNO=40 from the DEPT table because the join fails. An OuterJoin definition against EMP allows this row to be retrieved from DEPT with a "dummy" row (and NULL values) from the EMP table.

In how many ways can Outer Joins be achieved ? Here I present an alternative that does not have to use the Outer Join (+) syntax :

SQL> select deptno from dept;

DEPTNO
----------
10
20
30
40

SQL> select deptno, count(*) from emp group by deptno order by deptno;

DEPTNO COUNT(*)
---------- ----------
10 3
20 5
30 6

SQL>
SQL> -- dept 40 exists in dept but no employees present in emp
SQL>
SQL> REM Other ways to write Outer Joins
SQL>
SQL> -- most common way
SQL> select d.deptno, nvl(e.ename,'No employee')
2 from dept d, emp e
3 where d.deptno=e.deptno(+)
4 order by deptno
5 /

DEPTNO NVL(E.ENAME
---------- -----------
10 CLARK
10 KING
10 MILLER
20 JONES
20 FORD
20 ADAMS
20 SMITH
20 SCOTT
30 WARD
30 TURNER
30 ALLEN
30 JAMES
30 BLAKE
30 MARTIN
40 No employee

15 rows selected.

SQL>
SQL> -- another way
SQL> select d.deptno, e.ename
2 from dept d, emp e
3 where d.deptno=e.deptno
4 union all
5 select d.deptno,'No employee'
6 from dept d
7 where deptno not in (select distinct e.deptno from emp e)
8 order by deptno
9 /

DEPTNO ENAME
---------- -----------
10 CLARK
10 MILLER
10 KING
20 JONES
20 SMITH
20 SCOTT
20 FORD
20 ADAMS
30 WARD
30 TURNER
30 ALLEN
30 JAMES
30 MARTIN
30 BLAKE
40 No employee

15 rows selected.

SQL>


How would you write this query using ANSI syntax ? Are there any other ways to fetch the result set ?

.
.
.

04 September, 2011

Splitting a Range Partitioned Table

Here's a simple demonstration of splitting a Range Partitioned Table :


SQL> --- Create a Table with a MAXVALUE partition
SQL> drop table sales purge;

Table dropped.

SQL> create table sales
2 (order_id number not null,
3 sale_date date,
4 cust_id number,
5 prod_id number,
6 remarks varchar2(128))
7 partition by range (sale_date)
8 (partition P_2007 values less than (to_date('01-JAN-2008')),
9 partition P_2008 values less than (to_date('01-JAN-2009')),
10 partition P_2009 values less than (to_date('01-JAN-2010')),
11 partition P_2010 values less than (to_date('01-JAN-2011')),
12 partition P_MAX values less than (MAXVALUE)
13 )
14 /

Table created.

SQL>
SQL> -- insert sales for years 2007 to 2011
SQL> insert into sales values (1,to_date('15-JAN-2007','DD-MON-YYYY'),1,1,'First Sale in 2007');

1 row created.

SQL> insert into sales values (2,to_date('15-JUL-2008','DD-MON-YYYY'),1,2,'Sale in 2008');

1 row created.

SQL> insert into sales values (3,to_date('15-JUL-2009','DD-MON-YYYY'),1,3,'Sale in 2009');

1 row created.

SQL> insert into sales values (4,to_date('15-JUL-2010','DD-MON-YYYY'),1,2,'Sale in 2010');

1 row created.

SQL> insert into sales values (5,to_date('15-AUG-2010','DD-MON-YYYY'),2,3,'Cust_2 in 2010');

1 row created.

SQL> insert into sales values (6,to_date('01-FEB-2011','DD-MON-YYYY'),1,2,'Feb 2011');

1 row created.

SQL> insert into sales values (7,to_date('01-MAR-2011','DD-MON-YYYY'),2,3,'Mar 2011');

1 row created.

SQL> insert into sales values (8,to_date('01-APR-2011','DD-MON-YYYY'),2,3,'Apr 2011');

1 row created.

SQL> insert into sales values (9,to_date('01-MAY-2011','DD-MON-YYYY'),3,3,'Cust_3 !');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> -- create a global index and a local index
SQL> create unique index S_ORDER_ID_NDX on SALES (ORDER_ID);

Index created.

SQL> create index S_SALE_DT_NDX on SALES (SALE_DATE) LOCAL ;

Index created.

SQL>
SQL> -- gather stats
SQL> exec dbms_stats.gather_table_stats('','SALES',estimate_percent=>100,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'SALES'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
P_2007 1
P_2008 1
P_2009 1
P_2010 2
P_MAX 4

SQL>
SQL> -- We now find that year 2011 sales went into the Max value partition !
SQL>
SQL> -- split Max value partition
SQL> alter table sales
2 split partition P_MAX
3 at (to_date('01-JAN-2012','DD-MON-YYYY'))
4 into (partition P_2011,partition P_MAX)
5 update global indexes
6 /

Table altered.

SQL> alter table sales
2 split partition P_MAX
3 at (to_date('01-JAN-2013','DD-MON-YYYY'))
4 into (partition P_2012,partition P_MAX)
5 update global indexes
6 /

Table altered.

SQL> alter table sales
2 split partition P_MAX
3 at (to_date('01-JAN-2014','DD-MON-YYYY'))
4 into (partition P_2013,partition P_MAX)
5 update global indexes
6 /

Table altered.

SQL>
SQL> -- check the indexes
SQL> select status from user_indexes where index_name = 'S_ORDER_ID_NDX';

STATUS
--------
VALID

SQL>
SQL> select partition_name, status
2 from user_ind_partitions
3 where index_name = 'S_SALE_DT_NDX'
4 order by partition_position
5 /

PARTITION_NAME STATUS
------------------------------ --------
P_2007 USABLE
P_2008 USABLE
P_2009 USABLE
P_2010 USABLE
P_2011 USABLE
P_2012 USABLE
P_2013 USABLE
P_MAX USABLE

8 rows selected.

SQL>
SQL> -- The LOCAL index (equi-partitioned) is automatically maintained
SQL> -- with even new partitions created
SQL>
SQL> -- gather stats
SQL> exec dbms_stats.gather_table_stats('','SALES',estimate_percent=>100,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'SALES'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
P_2007 1
P_2008 1
P_2009 1
P_2010 2
P_2011 4
P_2012 0
P_2013 0
P_MAX 0

8 rows selected.

SQL>


The time to execute the SPLIT commands depends on the number of rows present in the P_MAX partition.

.
.
.

03 September, 2011

Understanding Obsolescence of RMAN Backups

Questions about the the LIST OBSOLETE command keep appearing in RMAN forums.

Given this configuration :
RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN>

which indicates that my Recovery Window is set to 15 days.

And given this listing of backups :
RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
130 B F A DISK 31-MAY-11 1 1 YES TAG20110531T224447
132 B A A DISK 31-MAY-11 1 1 YES TAG20110531T224945
133 B F A DISK 31-MAY-11 1 1 NO TAG20110531T224947
134 B F A DISK 05-JUL-11 1 1 YES TAG20110705T230230
135 B F A DISK 05-JUL-11 1 1 YES TAG20110705T230230
136 B F A DISK 05-JUL-11 1 1 NO TAG20110705T230640
137 B F A DISK 13-JUL-11 1 1 NO TAG20110713T224918
138 B F A DISK 03-SEP-11 1 1 NO TAG20110903T232648
139 B F A DISK 03-SEP-11 1 1 YES TAG20110903T234602
140 B F A DISK 03-SEP-11 1 1 YES TAG20110903T234602
141 B F A DISK 03-SEP-11 1 1 NO TAG20110903T235631

RMAN>


which backups do you expect to be OBSOLETE ?

.
.
.
.














.
.
.
Why are these reported as OBSOLETE ?
RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 15 days
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 130 31-MAY-11
Backup Piece 130 31-MAY-11 /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_nnndf_TAG20110531T224447_6y9zq1fr_.bkp
Backup Set 132 31-MAY-11
Backup Piece 132 31-MAY-11 /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_annnn_TAG20110531T224945_6yb009xg_.bkp
Backup Set 133 31-MAY-11
Backup Piece 133 31-MAY-11 /addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_05_31/o1_mf_s_752626187_6yb00d1h_.bkp
Backup Set 136 05-JUL-11
Backup Piece 136 05-JUL-11 /addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_05/o1_mf_s_755737600_716b41kb_.bkp

RMAN>

Why are the Backups in Key 134 and 135 (both on 05-Jul-11) not OBSOLETE ?
(and yet, Key 136 is OBSOLETE)
Why is the Backup of 13-Jul-11 not OBSOLETE ?

.
.
.

UPDATE : 05-Sep-11 :

Here is some more information :
sh-3.2$ pwd
/addtl/oracle/flash_recovery_area/ORCL
sh-3.2$ du -sk */*
4 archivelog/2011_08_30
4 archivelog/2011_09_01
4 archivelog/2011_09_02
4 archivelog/2011_09_03
28884 archivelog/2011_09_04
11156 autobackup/2011_04_22
11156 autobackup/2011_05_31
11156 autobackup/2011_07_05
11156 autobackup/2011_07_13
22308 autobackup/2011_09_03
11156 autobackup/2011_09_04
1276 backupset/2011_05_31
677584 backupset/2011_07_05
2951688 backupset/2011_09_03
6400 backupset/2011_09_04
sh-3.2$
sh-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Sep 5 22:32:18 2011

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

connected to target database: ORCL (DBID=1229390655)

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=44 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_nnndf_TAG20110531T224447_6y9zq1fr_.bkp RECID=130 STAMP=752625889
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_annnn_TAG20110531T224945_6yb009xg_.bkp RECID=132 STAMP=752626185
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_05_31/o1_mf_s_752626187_6yb00d1h_.bkp RECID=133 STAMP=752626188
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w9t3_.bkp RECID=134 STAMP=755737353
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w8s7_.bkp RECID=135 STAMP=755737352
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_05/o1_mf_s_755737600_716b41kb_.bkp RECID=136 STAMP=755737601
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_13/o1_mf_s_756427758_71vd3h8n_.bkp RECID=137 STAMP=756427759
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_03/o1_mf_s_760922808_764kssdh_.bkp RECID=138 STAMP=760922809
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxwdb_.bkp RECID=139 STAMP=760923964
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxvd6_.bkp RECID=140 STAMP=760923963
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_03/o1_mf_s_760924591_764mkhww_.bkp RECID=141 STAMP=760924591
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_04/o1_mf_annnn_TAG20110904T001454_764nmzy4_.bkp RECID=142 STAMP=760925695
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_04/o1_mf_annnn_TAG20110904T001454_764nmzf8_.bkp RECID=143 STAMP=760925695
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_04/o1_mf_s_760925699_764nn3xy_.bkp RECID=144 STAMP=760925699
Crosschecked 14 objects


RMAN>


The Backups of 31-May-11 and 05-July-11 are present on disk and found to be AVAILABLE when I run CROSSCHECK.
Also : I have not marked any Backup for KEEPing.


You will also note that RMAN is listing backups that are much older than the control_file_record_keep_time which is 7 days.
SQL> show parameter control_file_record

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
SQL>


UPDATE 07-Sep-11 : Answers

BackupSet 136 is OBSOLETEd because it is a Controlfile AutoBackup. (The listing of LIST BACKUP SUMMARY shows that it is not a Compressed Backup-- controfile autobackups are not compressed -- and the REPORT OBSOLETE shows that it is an autobackup piece).
BackupSets 134 and 135 are *Database* (i.e. Datafile) backups. These are not obsolete because they comprise the two BackupSets of the most recent BACKUP DATABASE run just before the Retention Policy obsolescence date. As of 03-Sep, the Retention Policy of 15 days sets a date of 19-Aug. There was no database backup on 18-Aug or 17-Aug or 16-Aug ... so Oracle works backwards and finds the database backup of 05-July in the BackupSets of 134 and 135.

Why is it two BackupSets ? Because I have configured PARALLELISM 2. Every BACKUP DATABASE command creates two BackupSets. So the run on 05-July created two BackupSets.

What about the BackupSet (137) of 13-July ? It, too, is a controlfile autobackup. This is the most recent controlfile autobackup that I have to match the obsolescence date. So, this canot be OBSOLETEd.

Also note that backups much older than 7 days (i.e. exceeding the control_file_record_keep_time) are still "maintained" by RMAN -- some of them (e.g. those of 31-May and the controlfile autobackup of 05-July) are OBSOLETE and yet still listed. They will continue to be listed until I DELETE them.

Even now, on 07-Sep, the backups are still present :

[oracle@localhost ORCL]$ pwd
/addtl/oracle/flash_recovery_area/ORCL
[oracle@localhost ORCL]$ du -sk */*
4 archivelog/2011_08_30
4 archivelog/2011_09_01
4 archivelog/2011_09_02
4 archivelog/2011_09_03
28884 archivelog/2011_09_04
26268 archivelog/2011_09_07
11156 autobackup/2011_04_22
11156 autobackup/2011_05_31
11156 autobackup/2011_07_05
11156 autobackup/2011_07_13
22308 autobackup/2011_09_03
11156 autobackup/2011_09_04
1276 backupset/2011_05_31
677584 backupset/2011_07_05
2951688 backupset/2011_09_03
6400 backupset/2011_09_04
[oracle@localhost ORCL]$

[oracle@localhost ORCL]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 7 22:33:33 2011

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

connected to target database: ORCL (DBID=1229390655)

RMAN> crosscheck backup ;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=47 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_nnndf_TAG20110531T224447_6y9zq1fr_.bkp RECID=130 STAMP=752625889
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_annnn_TAG20110531T224945_6yb009xg_.bkp RECID=132 STAMP=752626185
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_05_31/o1_mf_s_752626187_6yb00d1h_.bkp RECID=133 STAMP=752626188
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w9t3_.bkp RECID=134 STAMP=755737353
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w8s7_.bkp RECID=135 STAMP=755737352
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_05/o1_mf_s_755737600_716b41kb_.bkp RECID=136 STAMP=755737601
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_13/o1_mf_s_756427758_71vd3h8n_.bkp RECID=137 STAMP=756427759
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_03/o1_mf_s_760922808_764kssdh_.bkp RECID=138 STAMP=760922809
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxwdb_.bkp RECID=139 STAMP=760923964
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_03/o1_mf_nnndf_TAG20110903T234602_764lxvd6_.bkp RECID=140 STAMP=760923963
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_03/o1_mf_s_760924591_764mkhww_.bkp RECID=141 STAMP=760924591
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_04/o1_mf_annnn_TAG20110904T001454_764nmzy4_.bkp RECID=142 STAMP=760925695
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_09_04/o1_mf_annnn_TAG20110904T001454_764nmzf8_.bkp RECID=143 STAMP=760925695
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_09_04/o1_mf_s_760925699_764nn3xy_.bkp RECID=144 STAMP=760925699
Crosschecked 14 objects


RMAN>

RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
130 B F A DISK 31-MAY-11 1 1 YES TAG20110531T224447
132 B A A DISK 31-MAY-11 1 1 YES TAG20110531T224945
133 B F A DISK 31-MAY-11 1 1 NO TAG20110531T224947
134 B F A DISK 05-JUL-11 1 1 YES TAG20110705T230230
135 B F A DISK 05-JUL-11 1 1 YES TAG20110705T230230
136 B F A DISK 05-JUL-11 1 1 NO TAG20110705T230640
137 B F A DISK 13-JUL-11 1 1 NO TAG20110713T224918
138 B F A DISK 03-SEP-11 1 1 NO TAG20110903T232648
139 B F A DISK 03-SEP-11 1 1 YES TAG20110903T234602
140 B F A DISK 03-SEP-11 1 1 YES TAG20110903T234602
141 B F A DISK 03-SEP-11 1 1 NO TAG20110903T235631
142 B A A DISK 04-SEP-11 1 1 YES TAG20110904T001454
143 B A A DISK 04-SEP-11 1 1 YES TAG20110904T001454
144 B F A DISK 04-SEP-11 1 1 NO TAG20110904T001459

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 15 days
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 130 31-MAY-11
Backup Piece 130 31-MAY-11 /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_nnndf_TAG20110531T224447_6y9zq1fr_.bkp
Backup Set 132 31-MAY-11
Backup Piece 132 31-MAY-11 /addtl/oracle/flash_recovery_area/ORCL/backupset/2011_05_31/o1_mf_annnn_TAG20110531T224945_6yb009xg_.bkp
Backup Set 133 31-MAY-11
Backup Piece 133 31-MAY-11 /addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_05_31/o1_mf_s_752626187_6yb00d1h_.bkp
Backup Set 136 05-JUL-11
Backup Piece 136 05-JUL-11 /addtl/oracle/flash_recovery_area/ORCL/autobackup/2011_07_05/o1_mf_s_755737600_716b41kb_.bkp

RMAN>



So there you have it :

1. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS does not necessarily mean that every backup more than 16 days old is obosleted. Every backup older than the most recent backup before 15 days ago is obsoleted. If the most recent backup before 15 days from now is 62 days ago (05-July) so be it. It cannot be OBSOLETEd.

2. control_file_record_keep_time of 7 days does not necessarily mean that backups older than 7 days are "purged" from the controlfile or "unknown" to RMAN. I show some backups (of 31-May and 05-July) that are Obsoleted and yet maintained in the controlfile.

.
.
.