Hemant's Oracle DBA Blog: 2012

29 December, 2012

Book on OEM 12c

My friend and former ACE Director Porus Homi Havewala's new book on Oracle Enterprise Manager 12c Cloud Control, is now available. This is the first published EM 12c Cloud Control book in the world.

If you are interested in learning about the capabilities of Enterprise Manager 12c, please have a look. Electronic copies are available too.

http://www.packtpub.com/oracle-enterprise-manager-12c-cloud-control/book
Oracle Enterprise Manager 12c Cloud Control: Managing Data Center Chaos

.
.
.

10 November, 2012

Oracle 11g Anti-Hackers Cookbook

PACKT has published a new Oracle Security book called the "Oracle 11g Anti-hacker's Cookbook" , written by Adrian Neagu. They have kindly sent me a copy of the book which I shall be reviewing over the next few weeks. The book contains commands and configuration parameters to cover a wide range of security mechanisms from the OS, Network / Database Listener, Data, Authentication etc. Apparently, it contains recipes and is not a full Reference Guide on the commands. It shows usage of commands and scripts, assuming that you access the actual documentation on each command for more information.
.
.
.

08 October, 2012

Separate Child Cursor with varying bind allocation length

Although usage of bind variables can mean that the SQL statement does not need to be re-parsed at every execution, if the length of the bind changes, Oracle may create a seperate child cursor.

For example, if I run this test code :

declare
instring varchar2(2000);
  begin
   for i in 1..1999 loop
     instring := rpad('X',i);
     execute immediate 'insert into abc values (:instring) ' using instring ;
   end loop;
end;
/
commit;

I am making 1999 executions of the statement with a bind length that starts at 1 and ends at 1,999. The resultant SQL has 3 Child Cursors as :

SQL> select sql_id, child_number, executions  from v$sql where sql_id = '5p20kaht8s5bc';

SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
5p20kaht8s5bc            0         32
5p20kaht8s5bc            1         96
5p20kaht8s5bc            2       1871

SQL>

The first child was executed 32 times with a bind length of upto 32 bytes. The second child was executed 96 times for the next bind length "group" of upto 128 bytes. The third child was executed for the "group" of upto 2000 bytes.

If I then run the loop as :

declare
instring varchar2(4000);
  begin
   for i in 1..2001 loop
     instring := rpad('X',i);
     execute immediate 'insert into abc values (:instring) ' using instring ;
    end loop;
end;
/
commit;

I see Child Cursor executions as :

SQL> select sql_id, child_number, executions  from v$sql where sql_id = '5p20kaht8s5bc';

SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
5p20kaht8s5bc            0         32
5p20kaht8s5bc            1         96
5p20kaht8s5bc            2       1872
5p20kaht8s5bc            3          1

SQL>

So the next "group" is 2000 bytes.

See Jonathan Lewis's reference to this behaviour of Bind Variables.

.
.
.

25 September, 2012

Cardinality Decay

There was a forums question on Cardinality decay when a desired value is beyond the MAX value in a column.

Here is a quick demo in 11.2.0.1:

SQL> drop table test_cardinality purge;

Table dropped.

SQL> create table test_cardinality as select mod(rownum,100) as ID, dbms_random.string('X',25) as col_2
  2  from dual  connect by level < 10000;

Table created.

SQL> exec dbms_stats.gather_table_stats('HEMANT','TEST_CARDINALITY',method_opt=>'FOR ALL  COLUMNS SIZE 1',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select min(id), max(id), count(distinct(id)) from test_cardinality;

   MIN(ID)    MAX(ID) COUNT(DISTINCT(ID))
---------- ---------- -------------------
         0         99                 100

SQL> 
SQL> explain plan for select * from test_cardinality where id=50;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   100 |  2900 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |   100 |  2900 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=50)

13 rows selected.

SQL> 

SQL> explain plan for select * from test_cardinality where id=100;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |    99 |  2871 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |    99 |  2871 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=100)

13 rows selected.

SQL> 

SQL> explain plan for select * from test_cardinality where id=125;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |    74 |  2146 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |    74 |  2146 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=125)

13 rows selected.

SQL> 
SQL> explain plan for select * from test_cardinality where id=200;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |    29 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |     1 |    29 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=200)

13 rows selected.

SQL> 
SQL> explain plan for select * from test_cardinality where id=150;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |    48 |  1392 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |    48 |  1392 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("ID"=150)

13 rows selected.

SQL>

You can see that for ID values beyond 100, the expected cardinality does "decay". For the target value of 125, the expected cardinality is 74. For the target value of 150, the expect cardinality is 48. For the target value of 200, the expected cardinality is 1.
.
.
.

IT Contracting in Singapore

Do you agree with this blog post on the Singapore IT Contract market ?
.
.
.

23 September, 2012

Open Invitation from Packt Publishing

Packt is celebrating a milestone with an Open Invitation.
.
.
.

17 August, 2012

Storage Allocation

Here's a quick demo to show how specifying STORAGE parameters can affect the actual allocation.

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> create table A_LARGE_TABLE (col_1 number, col_2 varchar2(5)) storage (initial 400M);

Table created.

SQL> insert into A_LARGE_TABLE values (1,'ABC');

1 row created.

SQL> commit;

Commit complete.

SQL> select bytes/1048576, extents from user_segments where segment_name = 'A_LARGE_TABLE';

BYTES/1048576    EXTENTS
------------- ----------
          400          8

SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'A_LARGE_TABLE' order by extent_id;

 EXTENT_ID BYTES/1024
---------- ----------
         0      65536
         1      65536
         2      65536
         3      65536
         4      65536
         5      65536
         6       8192
         7       8192

8 rows selected.

SQL> select tablespace_name, initial_extent , next_extent, pct_increase
  2  from user_tables where table_name = 'A_LARGE_TABLE'
  3  /

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
------------------------------ -------------- ----------- ------------
USERS                               419430400     1048576

SQL> select tablespace_name, allocation_type, initial_extent, next_extent
  2  from dba_tablespaces where tablespace_name = 'USERS';

TABLESPACE_NAME                ALLOCATIO INITIAL_EXTENT NEXT_EXTENT
------------------------------ --------- -------------- -----------
USERS                          SYSTEM             65536

SQL>

So, I have a USERS tablespace with SYSTEM -- i.e. AUTOALLOCATE -- allocation. I have a table with a defined INITIAL of 400MB which got created with 6 Extents of 64MB and 2 Extents of 8MB. What happens if I TRUNCATE the table ?

SQL> truncate table A_LARGE_TABLE;

Table truncated.

SQL> select bytes/1048576, extents from user_segments where segment_name = 'A_LARGE_TABLE';

BYTES/1048576    EXTENTS
------------- ----------
          400          8

SQL>  select extent_id, bytes/1024 from user_extents where segment_name = 'A_LARGE_TABLE' order by extent_id;

 EXTENT_ID BYTES/1024
---------- ----------
         0      65536
         1      65536
         2      65536
         3      65536
         4      65536
         5      65536
         6       8192
         7       8192

8 rows selected.

SQL>

*None* of the pre-allocated extents were released ! Oracle doesn't follow the rule "keep the first extent", it follows the rule "keep as many extents as required to satisfy the INITIAL_EXTENT size. What if I rebuild the table with an ALTER TABLE MOVE ?

SQL> alter table A_LARGE_TABLE move;

Table altered.

SQL> select bytes/1048576, extents from user_segments where segment_name = 'A_LARGE_TABLE';

BYTES/1048576    EXTENTS
------------- ----------
          400          8

SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'A_LARGE_TABLE' order by extent_id;

 EXTENT_ID BYTES/1024
---------- ----------
         0      65536
         1      65536
         2      65536
         3      65536
         4      65536
         5      65536
         6       8192
         7       8192

8 rows selected.

SQL>

Again, *no difference*. Even the MOVE did not reduce the size of the table. What about a SHRINK ?

SQL> alter table A_LARGE_TABLE shrink space;
alter table A_LARGE_TABLE shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


SQL> alter table A_LARGE_TABLE enable row movement
  2  /

Table altered.

SQL> alter table A_LARGE_TABLE shrink space;

Table altered.

SQL> select bytes/1048576, extents from user_segments where segment_name = 'A_LARGE_TABLE';

BYTES/1048576    EXTENTS
------------- ----------
        .3125          1

SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'A_LARGE_TABLE' order by extent_id;

 EXTENT_ID BYTES/1024
---------- ----------
         0        320

SQL>

Finally, I am able to shrink the table down. Not to 64KB but to 320KB.
.
.
.

14 August, 2012

Issue a RECOVER for a Tablespace/Datafile that does not need recovery

Today's Question : What happens if you issue an RMAN RECOVER for a Tablespace or Datafile that is current and does not need recovery ?

Notice the smart "recover if needed" that is executed ?

RMAN> recover datafile 5;

Starting recover at 14-AUG-12
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/14/2012 22:57:48
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 datafile 5
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 5 - file is in use or recovery
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'

RMAN> sql 'alter tablespace example offline';

sql statement: alter tablespace example offline

RMAN> recover datafile 5;

Starting recover at 14-AUG-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 14-AUG-12

RMAN> sql 'alter tablespace example online';

sql statement: alter tablespace example online

RMAN>

I did a "dummy" recovery with the tablespace offline. .
.
.
.

24 July, 2012

How to use Oracle Virtual Box templates

An article : How to Use Oracle VM VirtualBox Templates
.
.
.

Published with Blogger-droid v2.0.6

11 July, 2012

ON COMMIT Refresh without a Primary Key

Today's question : How can you build an ON COMMIT Refresh Materialized View for a table that has no Primary Key ?
.
.
.

09 July, 2012

Materialized View Refresh ON COMMIT

A quick demonstration of a Materialized View that is refreshed ON COMMIT.

NOTE : This blog post has been updated after Yuri pointed out an error.

SQL> create table source_table
  2  as select rownum as source_pk, dbms_random.string('X',15) as col_2, sysdate-1000+rownum as col_3
  3  from dual connect by level < 1001
  4  /

Table created.

SQL> 
SQL> alter table source_table add constraint source_pk primary key (source_pk);

Table altered.

SQL> 
SQL> create materialized view log on source_table ;

Materialized view log created.

SQL> 
SQL> create materialized view my_mv refresh on commit
  2  as select  source_pk as key_col, col_3 as data_col from source_table
  3  /

Materialized view created.

SQL> 
SQL> select count(*) from source_table;

  COUNT(*)
----------
      1000

SQL> select count(*) from my_mv;

  COUNT(*)
----------
      1000

SQL> 
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> insert into source_table values (5001,'XXXXX',sysdate+50);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from my_mv where key_col=5001;

   KEY_COL DATA_COL
---------- ---------
      5001 03-OCT-12

SQL>

.
The trace file shows, among other SQLs :

INSERT /*+ IDX(0) */ INTO "HEMANT"."MLOG$_SOURCE_TABLE" (dmltype$$,old_new$$,
  snaptime$$,change_vector$$,xid$$,"SOURCE_PK") 
VALUES
 (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          1          2         29           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          1          2         29           1

DELETE FROM "HEMANT"."MY_MV" SNAP$
WHERE
 "KEY_COL" IN (SELECT DISTINCT LOG$."KEY_COL" FROM (SELECT MLOG$."SOURCE_PK"
  "KEY_COL" FROM "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "XID$$" = :1
  AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$."KEY_COL") NOT IN (SELECT
  MAS_TAB$."SOURCE_PK" "KEY_COL" FROM "SOURCE_TABLE" "MAS_TAB$" WHERE
  LOG$."KEY_COL" = MAS_TAB$."SOURCE_PK"))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0         10          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0         10          0           0

MERGE INTO "HEMANT"."MY_MV" "SNA$" USING (SELECT CURRENT$."KEY_COL",
  CURRENT$."DATA_COL" FROM (SELECT "SOURCE_TABLE"."SOURCE_PK" "KEY_COL",
  "SOURCE_TABLE"."COL_3" "DATA_COL" FROM "SOURCE_TABLE" "SOURCE_TABLE")
  CURRENT$, (SELECT DISTINCT MLOG$."SOURCE_PK" "KEY_COL" FROM
  "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "XID$$" = :1      AND
  ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."KEY_COL" = LOG$."KEY_COL")"AV$"
  ON ("SNA$"."KEY_COL" = "AV$"."KEY_COL") WHEN MATCHED THEN UPDATE  SET
  "SNA$"."KEY_COL" = "AV$"."KEY_COL","SNA$"."DATA_COL" = "AV$"."DATA_COL"
  WHEN NOT MATCHED THEN INSERT  (SNA$."KEY_COL",SNA$."DATA_COL") VALUES
  (AV$."KEY_COL",AV$."DATA_COL")


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          1         33         26           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          1         33         26           1

delete from "HEMANT"."MLOG$_SOURCE_TABLE"
where
 xid$$ = :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          7          2           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          7          2           1

05 July, 2012

An Oracle Installer that automatically switches to Console mode

The WebLogic installer can switch automatically to console mode.

UPDATE : However, quickstart.sh still requires an X-display as I discovered after the install completed.

-sh-3.2$ echo $HOME
/newfs/wlogic
-sh-3.2$ ./wls1211_linux32.bin
Extracting 0%....................................................................................................100%
Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified

Unable to instantiate GUI, defaulting to console mode.





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Welcome:
--------

This installer will guide you through the installation of WebLogic 12.1.1.0. Type "Next" or enter 
to proceed to the next prompt.  If you want to change data entered previously, type "Previous".  
You may quit the installer at any time by typing "Exit".




Enter [Exit][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Choose Middleware Home Directory:
---------------------------------

    "Middleware Home" = [Enter new value or use default "/newfs/wlogic/Oracle/Middleware"]




Enter new Middleware Home OR [Exit][Previous][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

   1|Email:[]
   2|Support Password:[]
   3|Receive Security Update:[Yes]



Enter index number to select OR [Exit][Previous][Next]> 3





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

    "Receive Security Update:" = [Enter new value or use default "Yes"]



Enter [Yes][No]? No





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

    "Receive Security Update:" = [Enter new value or use default "Yes"]


    ** Do you wish to bypass initiation of the configuration manager and
    **  remain uninformed of critical security issues in your configuration?


Enter [Yes][No]? Yes





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

   1|Email:[]
   2|Support Password:[]
   3|Receive Security Update:[No]



Enter index number to select OR [Exit][Previous][Next]> 




<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Register for Security Updates:
------------------------------

Provide your email address for security updates and  to initiate configuration manager.

   1|Email:[]
   2|Support Password:[]
   3|Receive Security Update:[No]



Enter index number to select OR [Exit][Previous][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Choose Install Type:
--------------------

Select the type of installation you wish to perform. 

 ->1|Typical
    |  Install the following product(s) and component(s):
    | - WebLogic Server
    | - Oracle Coherence

   2|Custom
    |  Choose software products and components to install and perform optional configuration.





Enter index number to select OR [Exit][Previous][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Choose Product Installation Directories:
----------------------------------------

Middleware Home Directory: [/newfs/wlogic/Oracle/Middleware]

Product Installation Directories:


   1|WebLogic Server: [/newfs/wlogic/Oracle/Middleware/wlserver_12.1]
   2|Oracle Coherence: [/newfs/wlogic/Oracle/Middleware/coherence_3.7]




Enter index number to select OR [Exit][Previous][Next]> 





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

The following Products and JDKs will be installed:
--------------------------------------------------

    WebLogic Platform 12.1.1.0
    |_____WebLogic Server
    |    |_____Core Application Server
    |    |_____Administration Console
    |    |_____Configuration Wizard and Upgrade Framework
    |    |_____Web 2.0 HTTP Pub-Sub Server
    |    |_____WebLogic SCA
    |    |_____WebLogic JDBC Drivers
    |    |_____Third Party JDBC Drivers
    |    |_____WebLogic Server Clients
    |    |_____Xquery Support
    |    |_____Evaluation Database
    |_____Oracle Coherence
    |    |_____Coherence Product Files
    |_____JDKs
         |_____SUN SDK 1.6.0_29
         |_____Oracle JRockit 1.6.0_29 SDK

    *Estimated size of installation: 1,178.0 MB




Enter [Exit][Previous][Next]> 
Jul 5, 2012 3:23:23 AM java.util.prefs.FileSystemPreferences$2 run
INFO: Created user preferences directory.





<------------------------------ Oracle Installer - WebLogic 12.1.1.0 ----------------------------->

Installing files..

0%          25%          50%          75%          100%
[------------|------------|------------|------------]
[******************

Cool. Isn't it ?

18 June, 2012

OOW 2012 Content Catalog

The Oracle OpenWorld 2012 Content Catalog is now out.
.
.
.

07 June, 2012

CONTROLFILE AUTOBACKUPs are OBSOLETE[d]

There was a recent forums discussion about manual controlfile backups being obsolete. Here I show that even autobackups are obsoleted by Oracle.

First I show that CONTROLFILE AUTOBACKUP is ON an RETENTION is set to REDUNDANCY 1

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
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 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE 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>

Next, I list all my controlfile backups. This shows that I do not have manual backups but only autobackups.

RMAN> list backup of controlfile;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13      Full    9.33M      DISK        00:00:00     01-JAN-12      
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20120101T115229
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771421948_7hzp3xf6_.bkp
  Control File Included: Ckp SCN: 4957876      Ckp time: 01-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    9.33M      DISK        00:00:01     13-FEB-12      
        BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20120213T234617
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_02_13/o1_mf_n_775179977_7mld2b7l_.bkp
  Control File Included: Ckp SCN: 5120748      Ckp time: 13-FEB-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20      Full    9.33M      DISK        00:00:01     23-MAR-12      
        BP Key: 20   Status: AVAILABLE  Compressed: NO  Tag: TAG20120323T231640
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778720600_7ps4ys8j_.bkp
  Control File Included: Ckp SCN: 5192490      Ckp time: 23-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22      Full    9.33M      DISK        00:00:00     23-MAR-12      
        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20120323T235653
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778723013_7ps7b5l1_.bkp
  Control File Included: Ckp SCN: 5194773      Ckp time: 23-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23      Full    9.33M      DISK        00:00:00     24-MAR-12      
        BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001027
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778723827_7ps83mpw_.bkp
  Control File Included: Ckp SCN: 5196407      Ckp time: 24-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    9.33M      DISK        00:00:00     24-MAR-12      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001528
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778724128_7ps8f0o8_.bkp
  Control File Included: Ckp SCN: 5196673      Ckp time: 24-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
33      Full    1.05M      DISK        00:00:00     01-JAN-12      
        BP Key: 33   Status: AVAILABLE  Compressed: YES  Tag: TAG20120101T110008
        Piece Name: /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp
  Control File Included: Ckp SCN: 4955357      Ckp time: 01-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38      Full    9.33M      DISK        00:00:01     15-MAY-12      
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: TAG20120515T002039
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_15/o1_mf_n_783303639_7v2d6qkz_.bkp
  Control File Included: Ckp SCN: 5288840      Ckp time: 15-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40      Full    9.33M      DISK        00:00:00     20-MAY-12      
        BP Key: 40   Status: AVAILABLE  Compressed: NO  Tag: TAG20120520T230909
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783817749_7vl28oln_.bkp
  Control File Included: Ckp SCN: 5313644      Ckp time: 20-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41      Full    9.33M      DISK        00:00:00     20-MAY-12      
        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: TAG20120520T232651
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783818811_7vl39vgv_.bkp
  Control File Included: Ckp SCN: 5322969      Ckp time: 20-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    9.33M      DISK        00:00:00     03-JUN-12      
        BP Key: 42   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T224133
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025693_7wpxwxhm_.bkp
  Control File Included: Ckp SCN: 5353308      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43      Full    9.33M      DISK        00:00:01     03-JUN-12      
        BP Key: 43   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T224515
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025915_7wpy3vt1_.bkp
  Control File Included: Ckp SCN: 5353844      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44      Full    9.33M      DISK        00:00:00     03-JUN-12      
        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T224540
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025940_7wpy4n49_.bkp
  Control File Included: Ckp SCN: 5353875      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45      Full    9.33M      DISK        00:00:01     03-JUN-12      
        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T230026
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785026826_7wpz0bxo_.bkp
  Control File Included: Ckp SCN: 5356135      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46      Full    9.33M      DISK        00:00:00     03-JUN-12      
        BP Key: 46   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T230639
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785027199_7wpzczo7_.bkp
  Control File Included: Ckp SCN: 5356475      Ckp time: 03-JUN-12

RMAN>

So, I have controlfile autobackups going as far back as 01-Jan. Quite obvious : I haven't been deleting "obsolete" files. (This is a "play" environment with adequate disk space for multiple backups of a small database).

I now list the OBSOLETE Backups.

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           34     09-MAY-12         
  Backup Piece       34     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp
Backup Set           26     09-MAY-12         
  Backup Piece       26     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp
Backup Set           36     09-MAY-12         
  Backup Piece       36     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp
Backup Set           33     09-MAY-12         
  Backup Piece       33     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp
Backup Set           27     09-MAY-12         
  Backup Piece       27     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp
Backup Set           30     09-MAY-12         
  Backup Piece       30     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp
Backup Set           32     09-MAY-12         
  Backup Piece       32     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp
Backup Set           29     09-MAY-12         
  Backup Piece       29     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
Backup Set           28     09-MAY-12         
  Backup Piece       28     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp
Backup Set           35     09-MAY-12         
  Backup Piece       35     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp
Backup Set           31     09-MAY-12         
  Backup Piece       31     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp
Backup Set           37     09-MAY-12         
  Backup Piece       37     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp
Archive Log          23     15-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_15/o1_mf_1_3_7k5vc0nf_.arc
Backup Set           13     01-JAN-12         
  Backup Piece       13     01-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771421948_7hzp3xf6_.bkp
Archive Log          24     15-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_15/o1_mf_1_4_7k5wo9bl_.arc
Archive Log          25     22-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_22/o1_mf_1_5_7koxgt4y_.arc
Archive Log          26     10-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_10/o1_mf_1_6_7mbfpjnf_.arc
Archive Log          27     12-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_12/o1_mf_1_7_7mhmgbrx_.arc
Archive Log          28     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_8_7mlcok0g_.arc
Archive Log          29     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_9_7mlctdf6_.arc
Archive Log          30     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_10_7mld23vp_.arc
Archive Log          32     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_9_7mld241b_.arc
Archive Log          31     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_8_7mld23yr_.arc
Archive Log          33     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_1_7mldpt71_.arc
Backup Set           14     13-FEB-12         
  Backup Piece       14     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_02_13/o1_mf_n_775179977_7mld2b7l_.bkp
Archive Log          34     18-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_18/o1_mf_1_2_7pcwy7lm_.arc
Archive Log          35     20-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_20/o1_mf_1_3_7pk6bsr9_.arc
Archive Log          36     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_23/o1_mf_1_4_7ps4pghr_.arc
Archive Log          37     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_23/o1_mf_1_5_7ps4tvvj_.arc
Backup Set           15     23-MAR-12         
  Backup Piece       15     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4tww3_.bkp
Backup Set           16     23-MAR-12         
  Backup Piece       16     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4txz4_.bkp
Backup Set           17     23-MAR-12         
  Backup Piece       17     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4vf2f_.bkp
Backup Set           18     23-MAR-12         
  Backup Piece       18     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
Archive Log          38     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_23/o1_mf_1_6_7ps4yqkg_.arc
Backup Set           19     23-MAR-12         
  Backup Piece       19     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231639_7ps4yqqm_.bkp
Backup Set           20     23-MAR-12         
  Backup Piece       20     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778720600_7ps4ys8j_.bkp
Backup Set           22     23-MAR-12         
  Backup Piece       22     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778723013_7ps7b5l1_.bkp
Datafile Copy        4      24-MAR-12          /home/oracle/app/oracle/oradata/orcl/add_tbs.dbf
Backup Set           23     24-MAR-12         
  Backup Piece       23     24-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778723827_7ps83mpw_.bkp
Backup Set           24     24-MAR-12         
  Backup Piece       24     24-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp
Backup Set           25     24-MAR-12         
  Backup Piece       25     24-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778724128_7ps8f0o8_.bkp
Backup Set           38     15-MAY-12         
  Backup Piece       38     15-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_15/o1_mf_n_783303639_7v2d6qkz_.bkp
Backup Set           39     20-MAY-12         
  Backup Piece       39     20-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp
Backup Set           40     20-MAY-12         
  Backup Piece       40     20-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783817749_7vl28oln_.bkp
Backup Set           41     20-MAY-12         
  Backup Piece       41     20-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783818811_7vl39vgv_.bkp
Backup Set           42     03-JUN-12         
  Backup Piece       42     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025693_7wpxwxhm_.bkp
Backup Set           43     03-JUN-12         
  Backup Piece       43     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025915_7wpy3vt1_.bkp
Backup Set           44     03-JUN-12         
  Backup Piece       44     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025940_7wpy4n49_.bkp
Backup Set           45     03-JUN-12         
  Backup Piece       45     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785026826_7wpz0bxo_.bkp

RMAN>

My CONTROLFILE AUTOBACKUP Pieces are shown as OBSOLETE.

Today's question : Why are all controlfile backups but the last one (I had 5 autobackups on 03-Jun and 4 of them are obsolete) reported as OBSOLETE ?
.
.
.

03 June, 2012

RMAN BACKUP AS COPY

By default the BACKUP command in RMAN creates BackupSet(s) -- each of which is one or more BackupPiece(s). A datafile may span BackupPieces but may not span a BackupSet.

However, RMAN does allow another method -- BACKUP AS COPY. This is akin to "User Managed Backups" created with OS commands -- except that the ALTER TABLESPACE | DATABASE BEGIN BACKUP command does not have to be issued.

BACKUP AS COPY creates a byte-for-byte copy of each datafile [except, inasmuch, blocks being modified by concurrent writes to the datafile].

If an active datafile is corrupted, the DBA can choose to SWITCH TO COPY instead of having to restore the datafile copy. Thus, a switch can be a fast operation. Obviously, the DBA must plan carefully where he creates such copies if he intends to SWITCH anytime later (he wouldn't keep a datafile copy on a non-protected [RAID or ASM] storage target).

Here's a simple demo :

SQL> select file_id, file_name 
  2  from dba_data_files
  3  where tablespace_name = 'ADD_TBS';

   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf

SQL> alter tablespace add_tbs
  2  add datafile '/oradata/orcl/add_tbs_02.dbf' size 100M;

Tablespace altered.

SQL> 
SQL> create table hemant.add_table tablespace add_tbs as select * from dba_source;

Table created.

SQL> select file_id, count(*)
  2  from dba_Extents
  3  where owner = 'HEMANT'
  4  and segment_name = 'ADD_TABLE'
  5  group by file_id
  6  order by 1
  7  /

   FILE_ID   COUNT(*)
---------- ----------
        14         48
        15         37

SQL>

So, I have begun with two datafiles and extents spread across both datafiles.
I now take backups of the two datafiles.

RMAN> backup as copy datafile 14 format '/tmp/ADDTBS01.DBF';

Starting backup at 03-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/oradata/orcl/add_tbs_01.dbf
output file name=/tmp/ADDTBS01.DBF tag=TAG20120603T224508 RECID=5 STAMP=785025915
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 03-JUN-12

Starting Control File Autobackup at 03-JUN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025915_7wpy3vt1_.bkp comment=NONE
Finished Control File Autobackup at 03-JUN-12

RMAN> backup as copy datafile 15 format '/tmp/add_tbs_second.dbf' ;

Starting backup at 03-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/oradata/orcl/add_tbs_02.dbf
output file name=/tmp/add_tbs_second.dbf tag=TAG20120603T224538 RECID=6 STAMP=785025939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-JUN-12

Starting Control File Autobackup at 03-JUN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025940_7wpy4n49_.bkp comment=NONE
Finished Control File Autobackup at 03-JUN-12

RMAN> 
RMAN> list backup of tablespace add_tbs completed after "sysdate-1";

specification does not match any backup in the repository

RMAN> list backup of datafile 14;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    530.30M    DISK        00:01:44     23-MAR-12      
        BP Key: 18   Status: AVAILABLE  Compressed: YES  Tag: TAG20120323T231454
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
  List of Datafiles in backup set 18
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5192430    23-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Full    530.70M    DISK        00:01:23     23-MAR-12      
        BP Key: 21   Status: AVAILABLE  Compressed: YES  Tag: TAG20120323T235528
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T235528_7ps77jf2_.bkp
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5194695    23-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24      Full    1.23M      DISK        00:00:00     24-MAR-12      
        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001527
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5196666    24-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    487.60M    DISK        00:00:00     01-JAN-12      
        BP Key: 29   Status: AVAILABLE  Compressed: YES  Tag: TAG20120101T112516
        Piece Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
  List of Datafiles in backup set 29
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 4956816    01-JAN-12 /oradata/orcl/add_tbs_01.dbf

RMAN>

Notice how a LIST BACKUP doesn't show the backups ! Why ? Because it shows only BACKUPSET Backups ! If I want to see these BACKUP AS COPY backups, I must use LIST COPY :

RMAN> list copy of datafile 14;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
5       14   A 03-JUN-12       5353830    03-JUN-12      
        Name: /tmp/ADDTBS01.DBF
        Tag: TAG20120603T224508

4       14   A 24-MAR-12       5196322    24-MAR-12      
        Name: /home/oracle/app/oracle/oradata/orcl/add_tbs.dbf


RMAN> list copy of datafile 15;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
6       15   A 03-JUN-12       5353869    03-JUN-12      
        Name: /tmp/add_tbs_second.dbf
        Tag: TAG20120603T224538


RMAN>

What happens when I lose the datafiles ?

SQL> !rm /oradata/orcl/add_tbs_0[1-2].dbf

SQL> select count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL> alter tablespace add_tbs offline;

Tablespace altered.

SQL> alter tablespace add_Tbs online;
alter tablespace add_Tbs online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/oradata/orcl/add_tbs_01.dbf'


SQL>

As an aside : Note how the extents and datafiles seemed to be accessible until I tried to ONLINE the datafiles.
Now, I do not have regular backups. Can I use the datafile copies ? YES.

RMAN> restore datafile 14;

Starting restore at 03-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK

channel ORA_DISK_1: restoring datafile 00014
input datafile copy RECID=5 STAMP=785025915 file name=/tmp/ADDTBS01.DBF
destination for restore of datafile 00014: /oradata/orcl/add_tbs_01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00014
output file name=/oradata/orcl/add_tbs_01.dbf RECID=0 STAMP=0
Finished restore at 03-JUN-12

RMAN> switch datafile 15 to copy;

datafile 15 switched to datafile copy "/tmp/add_tbs_second.dbf"

RMAN>

I actually restored datafile 14 to the target location but only switched datafile 15 to the copy. This is clearly evidenced by :

SQL> select file_id, file_name  
  2  from dba_data_files
  3  where tablespace_name = 'ADD_TBS';

   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf
        15 /tmp/add_tbs_second.dbf

SQL>

I can now simply recover the tablespace.

RMAN> recover tablespace add_tbs;

Starting recover at 03-JUN-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 03-JUN-12

RMAN> sql 'alter tablespace add_tbs online';

sql statement: alter tablespace add_tbs online

RMAN>

and query it now.

SQL> select /*+ FULL */ count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL> alter tablespace add_tbs offline;

Tablespace altered.

SQL> alter tablespace add_tbs online;

Tablespace altered.

SQL> select /*+ FULL */ count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL>

I can also restore datafile 15 by using the same "AS COPY" and "SWITCH" trick.

RMAN> backup as copy datafile 15 format '/oradata/orcl/add_tbs_02.dbf' ;

Starting backup at 03-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/tmp/add_tbs_second.dbf
output file name=/oradata/orcl/add_tbs_02.dbf tag=TAG20120603T230025 RECID=7 STAMP=785026826
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-JUN-12

Starting Control File Autobackup at 03-JUN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785026826_7wpz0bxo_.bkp comment=NONE
Finished Control File Autobackup at 03-JUN-12

RMAN> sql 'alter tablespace add_tbs offline';

sql statement: alter tablespace add_tbs offline

RMAN> switch datafile 15 to copy;

datafile 15 switched to datafile copy "/oradata/orcl/add_tbs_02.dbf"

RMAN> recover tablespace add_tbs;

Starting recover at 03-JUN-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 03-JUN-12

RMAN> sql 'alter tablespace add_tbs online';

sql statement: alter tablespace add_tbs online

RMAN> 

SQL> select file_id, file_name
  2  from dba_data_files
  3  where tablespace_name = 'ADD_TBS';

   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf
        15 /oradata/orcl/add_tbs_02.dbf

SQL>

Today's question : When is BACKUP AS COPY [and, optionally, SWITCH DATAFILE TO COPY] really useful ? Which are the scenarios you would use this facility ?

.
.
.

OEM 12c : New Book

PACKT will be releasing a new book by Porus Homi Havewala : "Oracle Enterprise Manager 12c Cloud Control : Managing Data Centre Chaos". It is currently available as a Pre-Order.
.
.
.

01 June, 2012

Java for PLSQL Developers

An introduction to Java for PLSQL developers by Lucas Jellema.
.
.
.

30 May, 2012

SQL written by Lisbeth Salander

A review of the SQL that Lisbeth Salander [in the movie "The Girl With The Dragon Tattoo"] writes.
.
.
.

20 May, 2012

CHECKPOINT_CHANGE#

In my previous post CURRENT_SCN and CHECKPOINT_CHANGE#, I had asked : "In which scenarios could the checkpoint_change# value differ between V$DATAFILE and V$DATAFILE_HEADER ?"

Here's a little demo :

SQL> create tablespace NEWTBS datafile '/tmp/newtbs.dbf' size 50M;

Tablespace created.

SQL> create table hemant.objcopy tablespace newtbs as select * from dba_objects;

Table created.

SQL> select file_id from dba_data_files where tablespace_name = 'NEWTBS';

   FILE_ID
----------
        15

SQL> alter system checkpoint;

System altered.

SQL> select f.checkpoint_change#, h.checkpoint_change#
  2  from v$datafile f, v$datafile_header h
  3  where f.file#=15 and h.file#=15 and f.file#=h.file#;

CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
           5312187            5312187

SQL>

I then backup the tablespace :

RMAN> backup tablespace newtbs;

Starting backup at 20-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00015 name=/tmp/newtbs.dbf
channel ORA_DISK_1: starting piece 1 at 20-MAY-12
channel ORA_DISK_1: finished piece 1 at 20-MAY-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp tag=TAG20120520T230907 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-MAY-12

Starting Control File Autobackup at 20-MAY-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783817749_7vl28oln_.bkp comment=NONE
Finished Control File Autobackup at 20-MAY-12

RMAN>

Next, I update the object(s) in the tablespace.

SQL> connect hemant/hemant    
Connected.
SQL> select segment_name from user_segments where tablespace_name = 'NEWTBS';

SEGMENT_NAME
--------------------------------------------------------------------------------
OBJCOPY

SQL> insert into objcopy select * from dba_objects;

76670 rows created.

SQL> update objcopy set owner = owner || '_1';

153301 rows updated.

SQL> commit;

Commit complete.

SQL> 
SQL> alter system switch logfile;

System altered.

SQL>

I then remove and restore the datafile :

SQL> connect / as sysdba
Connected.
SQL> !rm /tmp/newtbs*.dbf

SQL> alter database datafile 15 offline;

Database altered.

SQL> 
RMAN> restore datafile 15;

Starting restore at 20-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to /tmp/newtbs.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp tag=TAG20120520T230907
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 20-MAY-12

RMAN>

If I now query the V$DATAFILE and V$DATAFILE_HEADER views, I see :

SQL> select f.checkpoint_change#, h.checkpoint_change#
  2  from v$datafile f, v$datafile_header h
  3  where f.file#=15 and h.file#=15 and f.file#=h.file#
  4  /

CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
           5315901            5313637

SQL>

I then RECOVER the datafile :

SQL> recover datafile 15;
ORA-00279: change 5313637 generated at 05/20/2012 23:09:08 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_05_20/o1_mf_1_1
3_7vl2f3nf_.arc
ORA-00280: change 5313637 for thread 1 is in sequence #13


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

Log applied.
Media recovery complete.
SQL> 
SQL> select f.checkpoint_change#, h.checkpoint_change#
  2  from v$datafile f, v$datafile_header h
  3  where f.file#=15 and h.file#=15 and f.file#=h.file#
  4  /

CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
           5321980            5321980

SQL>

Note : The CHECKPOINT_CHANGE# has been incremented and both the views now show the same value. Bringing the datafile online again increments the CHECKPOINT_CHANGE#.

SQL> select f.checkpoint_change#, h.checkpoint_change#
  2  from v$datafile f, v$datafile_header h
  3  where f.file#=15 and h.file#=15 and f.file#=h.file#
  4  /

CHECKPOINT_CHANGE# CHECKPOINT_CHANGE#
------------------ ------------------
           5322278            5322278

SQL>

I could ask the question : Why is the CHECKPOINT_CHANGE# incremented for a datafile that was OFFLINE and [merely] RECOVERed ? But I am sure that you know the answer now.

.
.
.

15 May, 2012

CURRENT_SCN and CHECKPOINT_CHANGE#

The V$DATABASE view provides the current SCN at the database level.
The V$DATAFILE view provides the SCN at which the last checkpoint was issued for each datafile.

For example :

SQL> select current_scn from v$database; 

    CURRENT_SCN
---------------
        5288289

SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#; 

CHECKPOINT_CHANGE#        COUNT(*)
------------------ ---------------
           5287941              14

SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#;

CHECKPOINT_CHANGE#        COUNT(*)
------------------ ---------------
           5287941              14

SQL>

In this case, V$DATABASE's current_scn is ahead of those of the datafiles last checkpoint. We know that this is acceptable -- because the database SCN is continuously being incremented while datafiles are checkpointed only on occasion. The above values are after a database instance startup.
What if I checkpoint selective datafiles ?

SQL> alter system checkpoint;

System altered.

SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#;

CHECKPOINT_CHANGE#        COUNT(*)
------------------ ---------------
           5288402              14

SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#;

CHECKPOINT_CHANGE#        COUNT(*)
------------------ ---------------
           5288402              14

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#;

CHECKPOINT_CHANGE#        COUNT(*)
------------------ ---------------
           5288402              13
           5288445               1

SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#;

CHECKPOINT_CHANGE#        COUNT(*)
------------------ ---------------
           5288402              13
           5288445               1

SQL>

Here I have caused the datafile for the USERS tablespace to be at a higher checkpoint scn than the other datafiles.

Question : In which scenarios could the checkpoint_change# value differ between V$DATAFILE and V$DATAFILE_HEADER ?

.
.
.

11 May, 2012

Index Block Splits

Here are some blog posts about Index Block Splits that I had done earlier :

Index Block Splits : 90-10 (17-May-09)

Index Block Splits : 50-50 (23-May-09)

Index Block Splits and REBUILD (26-May-09)

Index Block Splits with REVERSE KEY Index (20-Feb-11)

(Good to remind myself about these posts)

08 May, 2012

RMAN Tips -- 4

Here I have backups that go backup more then 4 months :

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T110005
2       B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T110005
3       B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T110008
4       B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T110008
5       B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T110205
6       B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T112514
7       B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T112514
8       B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T112516
9       B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T112516
10      B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T112516
11      B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T112516
12      B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T112655
13      B  F  A DISK        01-JAN-12       1       1       NO         TAG20120101T115229
14      B  F  A DISK        13-FEB-12       1       1       NO         TAG20120213T234617
15      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231436
16      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231436
17      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231436
18      B  F  A DISK        23-MAR-12       1       1       YES        TAG20120323T231454
19      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231639
20      B  F  A DISK        23-MAR-12       1       1       NO         TAG20120323T231640
21      B  F  A DISK        23-MAR-12       1       1       YES        TAG20120323T235528
22      B  F  A DISK        23-MAR-12       1       1       NO         TAG20120323T235653
23      B  F  A DISK        24-MAR-12       1       1       NO         TAG20120324T001027
24      B  F  A DISK        24-MAR-12       1       1       NO         TAG20120324T001527
25      B  F  A DISK        24-MAR-12       1       1       NO         TAG20120324T001528

RMAN>

And the backups are located in the FRA (what can you surmise about the 13-FEB backup that doesn't exist here ?)

[oracle@linux64 backupset]$ pwd
/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset
[oracle@linux64 backupset]$ ls -l
total 12
drwxrwx--- 2 oracle oracle 4096 Jan  1 11:26 2012_01_01
drwxrwx--- 2 oracle oracle 4096 Mar 23 23:55 2012_03_23
drwxrwx--- 2 oracle oracle 4096 Mar 24 00:15 2012_03_24
[oracle@linux64 backupset]$ 
[oracle@linux64 backupset]$ du -sh *
1.1G    2012_01_01
1.1G    2012_03_23
1.3M    2012_03_24
[oracle@linux64 backupset]$

What if one of the backups was relocated to another mountpoint ?

[oracle@linux64 backupset]$ mv 2012_01_01 /newmountpoint/FRA
[oracle@linux64 backupset]$ du -sh /newmountpoint/FRA/*
1.1G    /newmountpoint/FRA/2012_01_01
[oracle@linux64 backupset]$ 


RMAN> crosscheck backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=195 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp RECID=1 STAMP=771418806
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp RECID=2 STAMP=771418807
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp RECID=3 STAMP=771418808
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp RECID=4 STAMP=771419318
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp RECID=5 STAMP=771419318
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp RECID=6 STAMP=771420314
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp RECID=7 STAMP=771420315
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp RECID=8 STAMP=771420317
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp RECID=9 STAMP=771420392
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp RECID=10 STAMP=771420407
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp RECID=11 STAMP=771420414
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp RECID=12 STAMP=771420415
..... some lines deleted ....
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4tww3_.bkp RECID=15 STAMP=778720476
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4txz4_.bkp RECID=16 STAMP=778720477
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4vf2f_.bkp RECID=17 STAMP=778720493
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp RECID=18 STAMP=778720494
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231639_7ps4yqqm_.bkp RECID=19 STAMP=778720599
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778720600_7ps4ys8j_.bkp RECID=20 STAMP=778720601
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T235528_7ps77jf2_.bkp RECID=21 STAMP=778722928
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778723013_7ps7b5l1_.bkp RECID=22 STAMP=778723013
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778723827_7ps83mpw_.bkp RECID=23 STAMP=778723827
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp RECID=24 STAMP=778724127
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778724128_7ps8f0o8_.bkp RECID=25 STAMP=778724128
Crosschecked 25 objects


RMAN> 

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1       1       1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp
2       2       1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp
3       3       1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp
4       4       1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp
5       5       1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp
6       6       1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp
7       7       1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp
8       8       1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
9       9       1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp
10      10      1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp
11      11      1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp
12      12      1   1   EXPIRED     DISK        /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp RECID=1 STAMP=771418806
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp RECID=2 STAMP=771418807
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp RECID=3 STAMP=771418808
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp RECID=4 STAMP=771419318
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp RECID=5 STAMP=771419318
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp RECID=6 STAMP=771420314
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp RECID=7 STAMP=771420315
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp RECID=8 STAMP=771420317
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp RECID=9 STAMP=771420392
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp RECID=10 STAMP=771420407
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp RECID=11 STAMP=771420414
deleted backup piece
backup piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp RECID=12 STAMP=771420415
Deleted 12 EXPIRED objects


RMAN> 

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
13      B  F  A DISK        01-JAN-12       1       1       NO         TAG20120101T115229
14      B  F  A DISK        13-FEB-12       1       1       NO         TAG20120213T234617
15      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231436
16      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231436
17      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231436
18      B  F  A DISK        23-MAR-12       1       1       YES        TAG20120323T231454
19      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231639
20      B  F  A DISK        23-MAR-12       1       1       NO         TAG20120323T231640
21      B  F  A DISK        23-MAR-12       1       1       YES        TAG20120323T235528
22      B  F  A DISK        23-MAR-12       1       1       NO         TAG20120323T235653
23      B  F  A DISK        24-MAR-12       1       1       NO         TAG20120324T001027
24      B  F  A DISK        24-MAR-12       1       1       NO         TAG20120324T001527
25      B  F  A DISK        24-MAR-12       1       1       NO         TAG20120324T001528

RMAN>

Apparently, Oracle is no longer "aware" of the 01-Jan-12 backup.
This is the problem statement : What if I were restoring an old backup (say 01-Jan) to another server but I did NOT have the controlfile that was part of the backup ? If I can use my media manager to restore the backup pieces from tape to disk (e.g. to the /newmountpoint/FRA filesystem), how can I restore the database without the controlfile ?

This is the "solution" :
(a) I restore a backup of the current controlfile (or any recent backup of the controlfile) to the new server.
(b) I then CATALOG all the backuppieces of 01-Jan that I have restored to disk.
(c) I can then RESTORE DATABASE --- even though the controlfile I am using is as of 08-May-12 and the backuppieces are from 01-Jan-12.
(d) I can RECOVER DATABASE to any point from 01-Jan to 08-May as long as I have (or can restore) the Archivelogs.

RMAN> catalog start with '/newmountpoint/FRA';

searching for all files that match the pattern /newmountpoint/FRA

List of Files Unknown to the Database
=====================================
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.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: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp
File Name: /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
13      B  F  A DISK        01-JAN-12       1       1       NO         TAG20120101T115229
14      B  F  A DISK        13-FEB-12       1       1       NO         TAG20120213T234617
15      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231436
16      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231436
17      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231436
18      B  F  A DISK        23-MAR-12       1       1       YES        TAG20120323T231454
19      B  A  A DISK        23-MAR-12       1       1       YES        TAG20120323T231639
20      B  F  A DISK        23-MAR-12       1       1       NO         TAG20120323T231640
21      B  F  A DISK        23-MAR-12       1       1       YES        TAG20120323T235528
22      B  F  A DISK        23-MAR-12       1       1       NO         TAG20120323T235653
23      B  F  A DISK        24-MAR-12       1       1       NO         TAG20120324T001027
24      B  F  A DISK        24-MAR-12       1       1       NO         TAG20120324T001527
25      B  F  A DISK        24-MAR-12       1       1       NO         TAG20120324T001528
26      B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T110005
27      B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T110205
28      B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T112516
29      B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T112516
30      B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T112514
31      B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T112516
32      B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T112514
33      B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T110008
34      B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T110005
35      B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T112516
36      B  F  A DISK        01-JAN-12       1       1       YES        TAG20120101T110008
37      B  A  A DISK        01-JAN-12       1       1       YES        TAG20120101T112655

RMAN>

After using the CATALOG START WITH for the new location of the 01-Jan-12 backuppieces, I can now "see" the backup in RMAN. Therefore, I should also be able to restore this backup.

Questions :
1. What could be the 13-FEB-12 backup that doesn't appear in the directory but does appear in a LIST BACKUP SUMMARY ?
2. What could be the 01-JAN-12 backup that still appears even though I have moved all the backup pieces ? (Note : I have deleted a few line in the "crosscheck backup" output that are about these two "backups".
3. What differences could be present between the 01-Jan-12 backup and the current / latest controlfile that I am using ?
4. Can I use "CROSSCHECK BACKUP" with backuppieces on tape ? 5. What would the COMPLETION_TIME for the 01-JAN-12 Archivelogs in V$ARCCHIVED_LOG ?

.
.
.

Also see RMAN Tips 1, 2, 3
.
.
.
.

"Debugging" stories

A non-Oracle story.

An email story.

An Oracle story.

.
.
.

07 May, 2012

A Poll on the usage of SQL Plan Management

Dominic Brooks has put up a poll on the usage of SQL Plan Management. If you have used it or tested it, please contribute. Your comments would be useful.
Note : In all honesty, I haven't used it yet.
.
.
.

01 May, 2012

USER_TAB_MODIFICATIONS -- 1

Last week, conducting a portion of a training program on SQL Tuning, I briefly spoke about USER_TAB_MODIFICATIONS.

Here is a quick example to show how this view maintains information about the level of DML (INSERT / UPDATE / DELETE row counts) that have occurred since the last GATHER_STATS execution against the table. This information is used by the GATHER_STATS call to determine if statistics are stale (by comparing the level of DML with the last known NUM_ROWS in the table).

Notes :
(1) Once a GATHER_STATS is executed, the view is "reset" for the table(s) where Stats have been updated.
(2) The View is updated only by a DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO call which can be executed manually OR is, nevertheless, updated automatically by the background processes -- just as Workload statistics (used for AWRs) are updated automatically.

In view of the above two points, you should not rely on simply querying USER_TAB_MODIFICATIONS to identify the level of DML.

23:13:45 SQL> REM Demo User_Tab_Modifications
23:13:45 SQL> 
23:13:45 SQL> alter session set nls_date_format='DD-MON HH24:MI:SS';

Session altered.

23:13:45 SQL> 
23:13:45 SQL> -- create the target table
23:13:45 SQL> drop table UTM_TARGET  purge;

Table dropped.

23:13:45 SQL> create table UTM_TARGET
23:13:45   2  as select * from dba_objects
23:13:45   3  where 1=2;

Table created.

23:13:45 SQL> select count(*) from UTM_TARGET;

  COUNT(*)
----------
         0

23:13:45 SQL> 
23:13:45 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:15:45 SQL> 
23:15:45 SQL> -- query USER_TAB_MODIFICATIONS
23:15:45 SQL> select timestamp,inserts,updates,deletes
23:15:45   2  from user_tab_modifications
23:15:45   3  where table_name = 'UTM_TARGET';

no rows selected

23:15:45 SQL> 
23:15:45 SQL> -- insert rows and commit
23:15:45 SQL> insert into UTM_TARGET
23:15:45   2  select * from dba_objects;

76630 rows created.

23:15:46 SQL> commit;

Commit complete.

23:15:46 SQL> 
23:15:46 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:17:46 SQL> 
23:17:46 SQL> -- query USER_TAB_MODIFICATIONS
23:17:46 SQL> select timestamp,inserts,updates,deletes
23:17:46   2  from user_tab_modifications
23:17:46   3  where table_name = 'UTM_TARGET';

no rows selected

23:17:46 SQL> 
23:17:46 SQL> -- flush monitoring info
23:17:46 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

23:17:46 SQL> 
23:17:46 SQL> -- query USER_TAB_MODIFICATIONS
23:17:46 SQL> select timestamp,inserts,updates,deletes
23:17:46   2  from user_tab_modifications
23:17:46   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:17:46      76630          0          0

23:17:46 SQL> 
23:17:46 SQL> -- what about Direct Path INSERTs ? are they captured
23:17:46 SQL> insert /*+ APPEND */ into UTM_TARGET
23:17:46   2  select * from dba_objects;

76630 rows created.

23:17:46 SQL> 
23:17:46 SQL> -- run a query. It should error if Direct Path INSERT has been used
23:17:46 SQL> select count(*) from UTM_TARGET;
select count(*) from UTM_TARGET
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


23:17:46 SQL> 
23:17:46 SQL> -- commit so that the Direct Path INSERT is visible
23:17:46 SQL> commit;

Commit complete.

23:17:46 SQL> 
23:17:46 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:19:46 SQL> 
23:19:46 SQL> -- query USER_TAB_MODIFICATIONS
23:19:46 SQL> select timestamp,inserts,updates,deletes
23:19:46   2  from user_tab_modifications
23:19:46   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:17:46      76630          0          0

23:19:46 SQL> 
23:19:46 SQL> -- flush monitoring info
23:19:46 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

23:19:46 SQL> 
23:19:46 SQL> -- query USER_TAB_MODIFICATIONS
23:19:46 SQL> select timestamp,inserts,updates,deletes
23:19:46   2  from user_tab_modifications
23:19:46   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:19:46     153260          0          0

23:19:46 SQL> 
23:19:46 SQL> -- run an UPDATE
23:19:46 SQL> update UTM_TARGET
23:19:46   2  set owner = 'SYTEM2' where owner = 'SYSTEM';

1058 rows updated.

23:19:47 SQL> commit;

Commit complete.

23:19:47 SQL> 
23:19:47 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:21:47 SQL> 
23:21:47 SQL> -- flush monitoring info
23:21:47 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

23:21:47 SQL> 
23:21:47 SQL> -- query USER_TAB_MODIFICATIONS
23:21:47 SQL> select timestamp,inserts,updates,deletes
23:21:47   2  from user_tab_modifications
23:21:47   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:21:47     153260       1058          0

23:21:47 SQL> 
23:21:47 SQL> 
23:21:47 SQL> -- what happens after we call  DBMS_STATS.GATHER_TABLE_STATS  ?
23:21:47 SQL> exec dbms_stats.gather_table_stats('HEMANT','UTM_TARGET');

PL/SQL procedure successfully completed.

23:21:47 SQL> 
23:21:47 SQL> -- query USER_TAB_MODIFICATIONS
23:21:47 SQL> select timestamp,inserts,updates,deletes
23:21:47   2  from user_tab_modifications
23:21:47   3  where table_name = 'UTM_TARGET';

no rows selected

23:21:47 SQL> -- the GATHER_STATS call has now flushed the view
23:21:47 SQL> -- the DML "counter" has got reset to 0 -- w.r.t. the time when the Gather_Stats is executed
23:21:47 SQL> 
23:21:47 SQL> 
23:21:47 SQL> -- run a DELETE
23:21:47 SQL> delete UTM_TARGET
23:21:47   2  where owner = 'SYTEM2' ;

1058 rows deleted.

23:21:47 SQL> commit;

Commit complete.

23:21:47 SQL> 
23:21:47 SQL> exec dbms_lock.sleep(120);

PL/SQL procedure successfully completed.

23:23:47 SQL> 
23:23:47 SQL> -- flush monitoring info
23:23:47 SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

23:23:47 SQL> 
23:23:47 SQL> -- query USER_TAB_MODIFICATIONS
23:23:47 SQL> select timestamp,inserts,updates,deletes
23:23:47   2  from user_tab_modifications
23:23:47   3  where table_name = 'UTM_TARGET';

TIMESTAMP          INSERTS    UPDATES    DELETES
--------------- ---------- ---------- ----------
01-MAY 23:23:47          0          0       1058

23:23:47 SQL>

As you can see, even if I populate my target table with rows, USER_TAB_MODIFICATIONS does not reflect this information until and unless DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO is called. Similarly, the last set of commands demnostrates how a DBMS_STATS.GATHER_TABLE_STATS has purged this view of information on my target tble (UTM_TARGET).
.
.
.