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

Rman backup through batchfile how to?

we can use rman along with batch file to take the database backup. 

First you have to create a rcv with following contents . I name it as bkp.rcv  and kept in d: drive.

The contents of my bkp.rcv is given below

backup datafile 1 format 'c:\rman\datafile_1_%D%M%Y%U';

backup datafile 2 format 'c:\rman\datafile_2_%D%M%Y%U';

backup datafile 3 format 'c:\rman\datafile_3_%D%M%Y%U';

backup datafile 4 format 'c:\rman\datafile_4_%D%M%Y%U';

backup datafile 5 format 'c:\rman\datafile_5_%D%M%Y%U';

backup datafile 6 format 'd:\rman\datafile_6_%D%M%Y%U';

backup datafile 7 format 'c:\rman\datafile_7_%D%M%Y%U';

exit;

Next create a file named rman_backup.txt , and insert the following contents ,set the PATH and ORACLE_SID accordingly .

set PATH=C:\oracle\product\10.2.0\Asm_1\bin;%PATH%

set ORACLE_SID=oracle1

C:\oracle\product\10.2.0\Asm_1\bin\rman target sys/sys cmdfile d:\bkp.rcv log d:\rmanbkp.log

pause

After creating the file rename it as rman_backup.bat and execute the batch file.

Hopes it will help for anybody :)

Oracle isql plus

iSQLPlus was first introduced with Oracle 8i to reflect the company's focus on Internet technology. iSQLPlus required that the Oracle SQL*Net drivers be installed on the client as well as the iSQLPlus program itself.

Starting with Oracle 9i, iSQLPlus was delivered as a web application. This removed the need to install client side software and enabled any client with an web browser to connect to iSQLPlus to run database queries.

Oracle decided to desupport iSQLPlus with the Oracle 11g release. Users should migrate to SQL Developer instead.

ISQL*PLUS:

• Recognizes SQL statements and sends them to the server.
• Oracle proprietary interface for executing SQL statements.
• Does not allows manipulation of values in the database.
• Uses commands for formatting data.

Oracle stopped supporting iSQL*Plus starting with Oracle11g. Oracle recommend switching to SQL Developer which can be downloaded for free from http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html. 

SQL Developer can do all of the same things iSQL*Plus can do, plus more. There are other third-party Oracle DBA/Developer tools available for purchase which I will not promote in this post, but SQL Developer should meet your needs and is easy to install/use.

Configuring I-SQL Plus

STEP 1

goto dos prompt.....  start...Run....Cmd

on command prompt type:  isqlplusctl start

after issuing the above command following prompt will displayed

Starting iSQL*Plus ...

iSQL*Plus started.

STEP 2

Open Any compatible Internet Browser.

Type the address for ISQL*PLUS

http://PC-NAME:5560/isqlplus/ 

Syntax

http:// host_name:portnumber/isqlplus

to check Port number:

oracle_home=where oracle software installed.

oracle_home\install\portlist.ini

step 3

after opening the iSQL*Plus page on Expolorer.. 

LOGIN SCREEN 

ENTER username , password and the connection string 

Now isqlplus started.
you can query the database here...

Some good question and answers that i got from different oracle forums 

1.I am not able to get much details about the use of STANDBY_ARCHIVE_DEST . Can anyone clarify the same ? Is it the place where archived logs of standby redo logs are stored ? In that case if we have both STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST set then whether both of them are used , if so in which cases each destination is used ?

Ans:- If both STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n are specified, then STANDBY_ARCHIVE_DEST will be used, however it is sufficient enough to simply use LOG_ARCHIVE_DEST_n.  STANDBY_ARCHIVE_DEST is actually deprecated in 11g.

2. what is the need of standby redo log file??
Ans:- Standby redo logs are necessary for the higher protection levels such as 
Guaranteed, Instant, and Rapid. In these protection modes LGWR from the 
Primary host writes transactions directly to the standby redo logs. 
This enables no data loss solutions and reduces the amount of data loss 
in the event of failure. Standby redo logs are not necessary if you are using 
the delayed protection mode. 

If you configure standby redo on the standby then you should also configure 
standby redo logs on the primary database. Even though the standby redo logs
are not used when the database is running in the primary role, configuring 
the standby redo logs on the primary database is recommended in preparation 
for an eventual switchover operation. 
Standby redo logs must be archived before the data can be applied to the 
standby database. The standby archival operation occurs automatically, even if 
the standby database is not in ARCHIVELOG mode. However, the archiver process 
must be started on the standby database. Note that the use of the archiver 
process (ARCn) is a requirement for selection of a standby redo log.

You must have the same number of standby redo logs on the standby as you have 
online redo logs on production. They must also be exactly the same size.

The following syntax is used to create standby redo logs:

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_1.dbf' size 20m;

Database altered.
SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_2.dbf' size 20m;

Database altered.

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_3.dbf' size 20m;

Database altered.


 3. what is the importance of DBID in rman??

In the case that you need to restore control file or spfile using RMAN , you need to set the DBID first.

4. RMAN Not able to find Controlfile autobackup. what may be the reason?
If Your source database has the controlfile autobackup set to a non-default location , for example
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%d/controlfile_backup_%d_%F';
Set the controlfile autobackup format on the target and try to restore it.

Refer this http://waseemslearning.blogspot.com/2011/03/rman-06563-control-file-or-spfile-must.html

Suppose on the target server, you have copied the controlfile autobackup to location "/u01/autobackup/", then on the target server do as:
RMAN>run
{
set controlfile autobackup format for device type disk to '/u01/autobackup/%F';
restore controlfile from autobackup;
}