Rman incremental backup and restore an example

its a simple demonstration of rman incremental backup and restore , any oracle enthusiast can follow this post and can understand how rman incremental backup will works.

C:\Users\Admin\mahi>set ORACLE_SID=idea

C:\Users\Admin\mahi>rman target sys/sys

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 3 10:39:31 2013

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

connected to target database: IDEA (DBID=1142773416)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name IDEA 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 'C:\rman\%F';

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 CHANNEL DEVICE TYPE DISK FORMAT   'c:\rman\idea_%U';

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 'E:\APP\ADMIN\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFIDEA.ORA'; # default

Here i took the full incremental level 0 backup,

RMAN>  BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';

Starting backup at 03-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=160 device type=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=E:\DATA\IDEA\SYSTEM01.DBF

input datafile file number=00002 name=E:\DATA\IDEA\SYSAUX01.DBF

input datafile file number=00009 name=E:\DATA\IDEA\USERS02.DBF

input datafile file number=00007 name=E:\DATA\IDEA\EXAMPLE02.DBF

input datafile file number=00008 name=E:\DATA\IDEA\EXAMPLE03.DBF

input datafile file number=00010 name=E:\DATA\IDEA\USERS03.DBF

input datafile file number=00005 name=E:\DATA\IDEA\EXAMPLE01.DBF

input datafile file number=00006 name=E:\DATA\IDEA\SYSTEM02.DBF

input datafile file number=00003 name=E:\DATA\IDEA\UNDOTBS01.DBF

input datafile file number=00004 name=E:\DATA\IDEA\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 03-JAN-13

channel ORA_DISK_1: finished piece 1 at 03-JAN-13

piece handle=C:\RMAN\IDEA_0TNUFTOB_1_1 tag=FULL_INC comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

Finished backup at 03-JAN-13

Starting Control File and SPFILE Autobackup at 03-JAN-13

piece handle=C:\RMAN\C-1142773416-20130103-02 comment=NONE

Finished Control File and SPFILE Autobackup at 03-JAN-13

RMAN>

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

         1          1          1   52428800        512          1 YES INACTIVE               2107075 02-JAN-13      2136108 03-JAN-13

         2          1          2   52428800        512          1 NO  CURRENT                2136108 03-JAN-13   2.8147E+14

         3          1          0   52428800        512          1 YES UNUSED                       0              0

SQL>

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

           2136108

SQL>

Now create a table and insert some values,

C:\Users\Admin\mahi>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 10:42:58 2013

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

Enter user-name: hr/hr

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  CREATE TABLE T1(C1 NUMBER);

Table created.

SQL> INSERT INTO T1 VALUES(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL>

Now i took incremental level 1 backup and it is tagged as INC_1,

RMAN>  BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';

Starting backup at 03-JAN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=160 device type=DISK

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=E:\DATA\IDEA\SYSTEM01.DBF

input datafile file number=00002 name=E:\DATA\IDEA\SYSAUX01.DBF

input datafile file number=00009 name=E:\DATA\IDEA\USERS02.DBF

input datafile file number=00007 name=E:\DATA\IDEA\EXAMPLE02.DBF

input datafile file number=00008 name=E:\DATA\IDEA\EXAMPLE03.DBF

input datafile file number=00010 name=E:\DATA\IDEA\USERS03.DBF

input datafile file number=00005 name=E:\DATA\IDEA\EXAMPLE01.DBF

input datafile file number=00006 name=E:\DATA\IDEA\SYSTEM02.DBF

input datafile file number=00003 name=E:\DATA\IDEA\UNDOTBS01.DBF

input datafile file number=00004 name=E:\DATA\IDEA\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 03-JAN-13

channel ORA_DISK_1: finished piece 1 at 03-JAN-13

piece handle=C:\RMAN\IDEA_0VNUFU0U_1_1 tag=INC_1 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

Finished backup at 03-JAN-13

Starting Control File and SPFILE Autobackup at 03-JAN-13

piece handle=C:\RMAN\C-1142773416-20130103-03 comment=NONE

Finished Control File and SPFILE Autobackup at 03-JAN-13

RMAN>

C:\Users\Admin\mahi>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 10:46:02 2013

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

Enter user-name: hr

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  INSERT INTO T1 VALUES(2);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL>

Now take incremental level 2 backup and it also tagged as INC_2,

RMAN> BACKUP INCREMENTAL LEVEL 2 DATABASE TAG 'INC_2';

Starting backup at 03-JAN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=160 device type=DISK

channel ORA_DISK_1: starting incremental level 2 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=E:\DATA\IDEA\SYSTEM01.DBF

input datafile file number=00002 name=E:\DATA\IDEA\SYSAUX01.DBF

input datafile file number=00009 name=E:\DATA\IDEA\USERS02.DBF

input datafile file number=00007 name=E:\DATA\IDEA\EXAMPLE02.DBF

input datafile file number=00008 name=E:\DATA\IDEA\EXAMPLE03.DBF

input datafile file number=00010 name=E:\DATA\IDEA\USERS03.DBF

input datafile file number=00005 name=E:\DATA\IDEA\EXAMPLE01.DBF

input datafile file number=00006 name=E:\DATA\IDEA\SYSTEM02.DBF

input datafile file number=00003 name=E:\DATA\IDEA\UNDOTBS01.DBF

input datafile file number=00004 name=E:\DATA\IDEA\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 03-JAN-13

channel ORA_DISK_1: finished piece 1 at 03-JAN-13

piece handle=C:\RMAN\IDEA_11NUFU6A_1_1 tag=INC_2 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

Finished backup at 03-JAN-13

Starting Control File and SPFILE Autobackup at 03-JAN-13

piece handle=C:\RMAN\C-1142773416-20130103-04 comment=NONE

Finished Control File and SPFILE Autobackup at 03-JAN-13

RMAN>

C:\Users\Admin\mahi>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 10:49:13 2013

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

Enter user-name: hr/hr

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> INSERT INTO T1 VALUES(3);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

         1          1          1   52428800        512          1 YES INACTIVE               2107075 02-JAN-13      2136108 03-JAN-13

         2          1          2   52428800        512          1 NO  CURRENT                2136108 03-JAN-13   2.8147E+14

         3          1          0   52428800        512          1 YES UNUSED                       0              0

SQL>

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

           2136108

SQL>

SQL> conn sys as sysdba

Enter password:

Connected.

SQL> shut abort;

ORACLE instance shut down.

SQL>

Here all the changes since the last backup has been stored in the redo logs. Now delete the controlfile ,

SQL> host del E:\data\idea\*.CTL

RMAN> startup;

Oracle instance started

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of startup command at 01/03/2013 10:56:44

ORA-00205: error in identifying control file, check alert log for more info

RMAN>

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO  'C:\rman\%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Starting restore at 03-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=156 device type=DISK

AUTOBACKUP search with format "C:\rman\%F" not attempted because DBID was not set

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 01/03/2013 10:57:59

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Take the dbid from controlfile autobackup,

RMAN> set DBID=1142773416

executing command: SET DBID

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Starting restore at 03-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130103

channel ORA_DISK_1: AUTOBACKUP found: C:\rman\c-1142773416-20130103-04

channel ORA_DISK_1: restoring control file from AUTOBACKUP C:\rman\c-1142773416-20130103-04

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=E:\DATA\IDEA\CONTROL01.CTL

output file name=E:\DATA\IDEA\CONTROL02.CTL

Finished restore at 03-JAN-13

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> RESTORE DATABASE;

Starting restore at 03-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=156 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 00001 to E:\DATA\IDEA\SYSTEM01.DBF

channel ORA_DISK_1: restoring datafile 00002 to E:\DATA\IDEA\SYSAUX01.DBF

channel ORA_DISK_1: restoring datafile 00003 to E:\DATA\IDEA\UNDOTBS01.DBF

channel ORA_DISK_1: restoring datafile 00004 to E:\DATA\IDEA\USERS01.DBF

channel ORA_DISK_1: restoring datafile 00005 to E:\DATA\IDEA\EXAMPLE01.DBF

channel ORA_DISK_1: restoring datafile 00006 to E:\DATA\IDEA\SYSTEM02.DBF

channel ORA_DISK_1: restoring datafile 00007 to E:\DATA\IDEA\EXAMPLE02.DBF

channel ORA_DISK_1: restoring datafile 00008 to E:\DATA\IDEA\EXAMPLE03.DBF

channel ORA_DISK_1: restoring datafile 00009 to E:\DATA\IDEA\USERS02.DBF

channel ORA_DISK_1: restoring datafile 00010 to E:\DATA\IDEA\USERS03.DBF

channel ORA_DISK_1: reading from backup piece C:\RMAN\IDEA_0TNUFTOB_1_1

channel ORA_DISK_1: piece handle=C:\RMAN\IDEA_0TNUFTOB_1_1 tag=FULL_INC

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:55

Finished restore at 03-JAN-13

RMAN>

Note:- The database was restored using level 0 full backup tagged as FULL_INC

RMAN> RECOVER DATABASE;

Starting recover at 03-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: E:\DATA\IDEA\SYSTEM01.DBF

destination for restore of datafile 00002: E:\DATA\IDEA\SYSAUX01.DBF

destination for restore of datafile 00003: E:\DATA\IDEA\UNDOTBS01.DBF

destination for restore of datafile 00004: E:\DATA\IDEA\USERS01.DBF

destination for restore of datafile 00005: E:\DATA\IDEA\EXAMPLE01.DBF

destination for restore of datafile 00006: E:\DATA\IDEA\SYSTEM02.DBF

destination for restore of datafile 00007: E:\DATA\IDEA\EXAMPLE02.DBF

destination for restore of datafile 00008: E:\DATA\IDEA\EXAMPLE03.DBF

destination for restore of datafile 00009: E:\DATA\IDEA\USERS02.DBF

destination for restore of datafile 00010: E:\DATA\IDEA\USERS03.DBF

channel ORA_DISK_1: reading from backup piece C:\RMAN\IDEA_0VNUFU0U_1_1

channel ORA_DISK_1: piece handle=C:\RMAN\IDEA_0VNUFU0U_1_1 tag=INC_1

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: E:\DATA\IDEA\SYSTEM01.DBF

destination for restore of datafile 00002: E:\DATA\IDEA\SYSAUX01.DBF

destination for restore of datafile 00003: E:\DATA\IDEA\UNDOTBS01.DBF

destination for restore of datafile 00004: E:\DATA\IDEA\USERS01.DBF

destination for restore of datafile 00005: E:\DATA\IDEA\EXAMPLE01.DBF

destination for restore of datafile 00006: E:\DATA\IDEA\SYSTEM02.DBF

destination for restore of datafile 00007: E:\DATA\IDEA\EXAMPLE02.DBF

destination for restore of datafile 00008: E:\DATA\IDEA\EXAMPLE03.DBF

destination for restore of datafile 00009: E:\DATA\IDEA\USERS02.DBF

destination for restore of datafile 00010: E:\DATA\IDEA\USERS03.DBF

channel ORA_DISK_1: reading from backup piece C:\RMAN\IDEA_11NUFU6A_1_1

channel ORA_DISK_1: piece handle=C:\RMAN\IDEA_11NUFU6A_1_1 tag=INC_2

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file E:\DATA\IDEA\REDO02.LOG

archived log file name=E:\DATA\IDEA\REDO02.LOG thread=1 sequence=2

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

Finished recover at 03-JAN-13

Note:- Watch the output carefully. You can recognise various backups that are being applied. Look for the tags that you have given to backupsets. It will applies all the incrementals one by one. First it will apply level 1 incremental, and then level 2. Then it will search for appropriate log sequence and applies the same if found.

RMAN>  ALTER DATABASE OPEN RESETLOGS;

database opened

RMAN> exit

Recovery Manager complete.

C:\Users\Admin\mahi>sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 11:03:39 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from t1;

        C1

----------

         2

         2

         2

         3

         3

         3

         1

         1

         1

9 rows selected.

Hope its helps to somebody.. :)

SQL>

A script to check RAC log switch 

The following script reports how much time passed between log switches in a time period.

SELECT C.INSTANCE,
         C.THREAD#,
         B.SEQUENCE# "START SEQUENCE",
         TO_CHAR (B.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "START TIME",
         A.SEQUENCE# "END SEQUENCE",
         TO_CHAR (A.FIRST_TIME, 'DD-MM-YYYY HH24:MI:SS') "END TIME",
         TO_CHAR (
            TRUNC (SYSDATE)
            + NUMTODSINTERVAL ( (A.FIRST_TIME - B.FIRST_TIME) * 86400,
                               'SECOND'),
            'HH24:MI:SS')
            DURATION
    FROM V$LOG_HISTORY A, V$LOG_HISTORY B, V$THREAD C
   WHERE     A.SEQUENCE# = B.SEQUENCE# + 1
         AND A.THREAD# = C.THREAD#
         AND B.THREAD# = C.THREAD#
         AND A.FIRST_TIME BETWEEN TO_DATE ('28-12-2012 00:00:00',
                                           'DD-MM-YYYY HH24:MI:SS')
                              AND TO_DATE ('29-12-2012 00:00:00',
                                           'DD-MM-YYYY HH24:MI:SS')
ORDER BY 4;

The out put will be like this,
INSTANCE          THREAD# START SEQUENCE START TIME          END SEQUENCE END TIME            DURATION
-------------- ---------- -------------- ------------------- ------------ ------------------- --------------
oracle2                 2          35813 28-12-2012 15:40:53        35814 28-12-2012 15:48:29 00:07:36
oracle2                 2          35814 28-12-2012 15:48:29        35815 28-12-2012 15:56:03 00:07:34
oracle1                 1          41283 28-12-2012 15:48:29        41284 28-12-2012 15:56:02 00:07:33
oracle1                 1          41284 28-12-2012 15:56:02        41285 28-12-2012 16:03:37 00:07:35
oracle2                 2          35815 28-12-2012 15:56:03        35816 28-12-2012 16:03:38 00:07:35
oracle1                 1          41285 28-12-2012 16:03:37        41286 28-12-2012 16:11:10 00:07:33
oracle2                 2          35816 28-12-2012 16:03:38        35817 28-12-2012 16:11:11 00:07:33
oracle1                 1          41286 28-12-2012 16:11:10        41287 28-12-2012 16:18:38 00:07:28
oracle2                 2          35817 28-12-2012 16:11:11        35818 28-12-2012 16:18:39 00:07:28
oracle1                 1          41287 28-12-2012 16:18:38        41288 28-12-2012 16:26:16 00:07:38
oracle2                 2          35818 28-12-2012 16:18:39        35819 28-12-2012 16:26:16 00:07:37

The important of enabling autobackup in rman 

BY enabling few default parameters of RMAN will save the life of dba significantly.
RMAN has following default parameters and its default values:

RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # 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 SNAPSHOT CONTROLFILE NAME TO /app/oracle/dbs/snapcf_EHEALTH.f'; # default

I would be talking the benefits of CONTROLFILE AUTOBACKUP.
By default CONTROLFILE AUTOBACKUP is OFF. Oracle strongly recommend enabling CONTROLFILE AUTOBACKUP ON.

I edited the above setting as follows,
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\rman\idea_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\rman\idea_%F';
new RMAN configuration parameters are successfully stored
RMAN> 

Note:- 
All autobackup formats must include %F variable. %F expands to “C-XXXXXXXXX-YYYYMMDD-NN”, where:

XXXXXXXXX – DBID

YYYYMMDD – day, when backed up

NN – change number during day, starts with 00, and represented in hexadecimal

A quick test to make sure the backup works and is on disk,


RMAN> backup datafile 1;
Starting backup at 24-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4 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=00001 name=E:\DATA\IDEA\SYSTEM01.DBF
channel ORA_DISK_1: starting piece 1 at 24-DEC-12
channel ORA_DISK_1: finished piece 1 at 24-DEC-12
piece handle=E:\APP\ADMIN\PRODUCT\11.2.0\DBHOME_1\DATABASE\01NTMC5P_1_1 tag=TAG20121224T180649 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 24-DEC-12
Starting Control File and SPFILE Autobackup at 24-DEC-12
piece handle=C:\RMAN\idea_C-1142773416-20121224-00 comment=NONE
Finished Control File and SPFILE Autobackup at 24-DEC-12

C:\rman>del idea_C-1142773416-20121224-00 -- i deleted the autobackup file .

Next we need to make a structural change to the database to see if the control file is automatically backed up. For this example i'll add a small datafile to the system tablespace.


SQL> alter tablespace SYSTEM add datafile 'E:\DATA\IDEA\SYSTEM02.DBF' size 100M;

Tablespace altered.


Starting with Oracle 11g Release 2, RMAN creates a single autobackup file encompassing all of the structural changes that have occurred within a few minutes of each other rather than creating a new backup of the controlfile on each structural change to the database. Also this will happen on the next log switch, so switch the logfile,

SQL>alter system switch logfile;


C:\rman>dir
 Volume in drive C is OS
 Volume Serial Number is E258-0ED0
 Directory of C:\rman
24-12-2012  18:59    <DIR>          .
24-12-2012  18:59    <DIR>          ..
24-12-2012  18:59         9,830,400 idea_C-1142773416-20121224-02
               1 File(s)      9,830,400 bytes
               2 Dir(s)  99,976,085,504 bytes free
C:\rman>

Benefits:
With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.
A control file autobackup lets you restore the RMAN repository contained in the control file when the control file is lost and you have no recovery catalog. You do not need a recovery catalog or target database control file to restore the control file autobackup.

The control file is also automatically backed up after database structural changes such as adding a new tablespace, altering the state of a tablespace or datafile (for example, bringing it online), adding a new online redo log, renaming a file, adding a new redo thread, and so on. Losing this information would compromise your ability to recover the database.
If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up.


Conclusion:
You can turn the autobackup feature on or off by running the following commands through RMAN utility:CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP OFF;

source:- http://jaffardba.blogspot.in

Index skip scan in oracle

With Oracle 9i, CBO is equipped with many more features, one of them is “Index skip scan” .This means even if you have a composite index on more than one column and you use the non-prefix column alone in your SQL, it may still use index .CBO will calculate the cost of using the index and if it is more than that of full table scan, then it may not use index.

The index skip scan was introduced to allow Oracle to “skip” leading-edge column  in a multi-column index. You can force an index skip scan with the /*+ index_ss */ hint. For example, consider the following concatenated index on a super-low cardinality column , following by a very selective column . The cardinality of the leading column has a direct impact on the speed of the index skip scan. 
The regular B-tree index will have only one range scan from top to bottom. But skip scan will do many range scan depends on the cardinatlity of the leading index column.  Since the query lacks the leading column it will rewrite the query into smaller queries and each doing a range scan.


SQL> create table mahi.skiptest(a number,b number,c number);

Table created.

SQL> create index mahi.ix1 on mahi.skiptest (a,b);

Index created.

SQL> declare
  2
  3  begin
  4  for i in 1 .. 100000
  5  loop
  6  insert into skiptest values(mod(i, 5), i, 100);
  7  end loop;
  8  commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname => 'MAHI', tabname => 'skiptest', cascade => true);

PL/SQL procedure successfully completed.

SQL> select count(1),a from skiptest group by a;

  COUNT(1)          A
---------- ----------
     20000          1
     20000          2
     20000          4
     20000          3
     20000          0
Note:- column 'a' having 5 unique values ,

SQL> set autotrace on explain
SQL> select * from skiptest where b=88888;


Execution Plan
----------------------------------------------------------
Plan hash value: 380852608

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    10 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SKIPTEST |     1 |    10 |     7   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IX1      |     1 |       |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Optimizer used skip scan index for the above query. During the skip scan, the composite index is accessed once for each distinct value of the leading column(s). For each distinct value, the index is searched to find the query's target values. When optimizer use the skip scan index, the query(select * from skiptest where b=88888;) is broken down into two small range scan as below.

Select * from skiptest where a=0 and b=88888
Union
Select * from skiptest where a=1 and b=88888
Union
Select * from skiptest where a=2 and b=88888
Union
Select * from skiptest where a=3 and b=88888
Union
Select * from skiptest where a=4 and b=88888;

Let us consider the below SQL query and it is using regular B-tree index. Since we use the leading index column(a) in the where clause. This query does only one range scan, not like skip scan index.

SQL> Select * from skiptest where A=1  and B=92271;

         A              B             C

----------   ----------   ----------

          1        92271          100

Execution Plan

----------------------------------------------------------

Plan hash value: 3161369405

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |    10 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SKIPTEST |     1 |    10 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX1      |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Avoid Sorting done by 'ORDER BY' clause

TASTCASE 1:

Step1: Create a table EMP as

SQL> conn mahi/mahi
Connected.
SQL> CREATE TABLE  EMP AS SELECT * FROM SCOTT.EMP;
Table created.

Step2: Query the EMP

SQL> set autotrace on explain;
SQL> select * from EMP ORDER BY EMPNO;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 2007178810

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 4 (25) | 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 1218 | 4 (25) | 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------

Observation: Full Table Scan of EMP and SORTING IS DOING WITH EMPNO

Step3: Create a index TESTEMPIDX on EMPNO OF TESTEMP table as

SQL> create index emp_empno_indx on emp(empno);
Index created.
SQL> select * from emp order by empno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.

Execution Plan
---------------------------------------------------------- 
Plan hash value: 150391907 

--------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
--------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 14 | 1218 | 4 (25)| 00:00:01 | 
| 1 | SORT ORDER BY | | 14 | 1218 | 4 (25)| 00:00:01 | 
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 | 
--------------------------------------------------------------------------- 

Observation: Full Table Scan of EMP and SORTING (ORDERBY) even after creating index

Step4: Create a NOT NULL Constraint on EMPNO of EMP table as

SQL> ALTER TABLE EMP MODIFY (EMPNO CONSTRAINT NOT_NULL_CONS NOT NULL);
Table altered.
SQL> desc emp;

Name Null? Type
----------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select * from emp order by empno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1969959806

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 1218 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | EMP_EMPNO_INDX | 14 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------- 

Observation: INDEX TESTEMPIDX of TESTEMP is USING, TABLE ACCESS BY INDEX ROWID AND NOSORTING

Learning:

1) Order by column should have NOT NULL constraint.
2) Index column should be in the Order by Clause

TESTCASE 2 :

SQL> desc emp;

Name Null? Type
----------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select index_name,column_name from user_ind_columns where table_name='EMP';

INDEX_NAME COLUMN_NAME 
------------- -------------- 
EMP_EMPNO_INDX EMPNO 


Step1: QUERY the EMP as

SQL> SELECT * FROM EMP ORDER BY ENAME;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
14 rows selected.


Execution Plan
---------------------------------------------------------- 
Plan hash value: 150391907 

--------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
--------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 14 | 1218 | 4 (25)| 00:00:01 | 
| 1 | SORT ORDER BY | | 14 | 1218 | 4 (25)| 00:00:01 | 
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 | 
--------------------------------------------------------------------------- 


Observation: After adding ENAME in Order by clause, index TESTEMPIDX is not using

Step 2: Create a Composite index on EMPNO and ENAME and run a SQL again as

SQL> CREATE INDEX TESTEMPIDX2 ON EMP(EMPNO,ENAME);
Index created.
SQL> SELECT * FROM EMP ORDER BY EMPNO,ENAME;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10


14 rows selected.
Execution Plan
---------------------------------------------------------- 
Plan hash value: 1356242066 

------------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
------------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 | 
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 1218 | 2 (0)| 00:00:01 | 
| 2 | INDEX FULL SCAN | TESTEMPIDX2 | 14 | | 1 (0)| 00:00:01 | 
------------------------------------------------------------------------------------------- 

Observation: INDEX TESTEMPIDX2 of EMP is USING, TABLE ACCESS BY INDEX ROWID AND NOSORTING

Step3: alternate the order of EMPNO and ENAME and run a SQL again as

SQL> SELECT * FROM EMP ORDER BY ENAME,EMPNO;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30


14 rows selected.
Execution Plan
---------------------------------------------------------- 
Plan hash value: 150391907 

--------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
--------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 14 | 1218 | 4 (25)| 00:00:01 | 
| 1 | SORT ORDER BY | | 14 | 1218 | 4 (25)| 00:00:01 | 
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 | 
--------------------------------------------------------------------------- 

Observation: Full Table Scan of EMP and SORTING (ORDER BY) (THE INDEX TESTEMPIDX2 is not used !)
Conclusion:

1. All the Order by column should be in composite index

2. Order by columns should be of the same order as of composite index

3. At least one of the composite columns should have NOT NULL constraint.

source:- http://shaharear.blogspot.in