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.

.

.

.

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

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 !

.
.
.

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.

.
.
.

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.

.
.
.

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 ?

.
.
.

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 ?

.
.
.

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.

.
.
.

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.

.
.
.