Invisible Index

Invisible index is a new feature of Oracle 11g. If we create an invisible index Oracle optimizer will not use it. This can be used to test a query without effecting the application. We can also make an index invisible/visible by using ALTER INDEX index_name [visible/invisible].

Syntax:

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index­_name INVISIBLE;
ALTER INDEX index­_name VISIBLE;

Examples:

SYSTEM@orcl11g>show parameter optimizer_use_invisible_indexes 
NAME TYPE VALUE

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

optimizer_use_invisible_indexes boolean FALSE



SYSTEM@orcl11g>desc test

Name Null? Type

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

N NUMBER

NAME VARCHAR2(10)



SYSTEM@orcl11g>create index idx1 on test(n) invisible;

Index created.



SYSTEM@orcl11g>select owner,index_name,visibility from dba_indexes

2 where visibility = 'INVISIBLE';



OWNER INDEX_NAME VISIBILIT

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

SYSTEM IDX1 INVISIBLE



SYSTEM@orcl11g>select * from test where n=5;



N NAME

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

5 H



Execution Plan

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

Plan hash value: 1357081020



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

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEST | 1 | 20 | 3 (0)| 00:00:01 |

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



SYSTEM@orcl11g>alter index idx1 visible;

Index altered.



SYSTEM@orcl11g>select * from test where n=5;



N NAME

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

5 H



Execution Plan

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

Plan hash value: 1175071277



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

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

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

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

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 20 | 2 (0)| 00:00:01 |

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

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

Use 1: Adding an index

You think adding an index will improve a query but you are not sure. Just create an invisible index. Test the query and decide.

Use 2: Dropping an index

You want to drop an index but don’t know how it will affect the application. Make that index invisible and analyze the performance. If it is not affecting the performance of the application, you can drop it.

Oradebug

Oradebug utility can be used to trace a user session. To trace a session its OS process ID is required which can be obtained by joining v$session and v$process views as shown below:

SQL> select s.username,S.sid,S.serial#,spid

 2  from v$session s, v$process p

 3  where s.username=p.username

 4  and s.paddr=p.addr

 5  and s.username='HNAIK';



USERNAME                              SID    SERIAL# SPID

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

HNAIK                                 154          4 5548

Now login to the database as SYSDBA: 

C:\Documents and Settings\hnaik> sqlplus / as sysdba



SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 29 14:37:40 2009



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





Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Start the tracing:

SQL> oradebug setospid 5548

Oracle pid: 20, Windows thread id: 5548, image: ORACLE.EXE (SHAD)



SQL> oradebug event 10046 trace name context forever,level 8;

Statement processed.

Stop the tracing:

SQL> oradebug event 10046 trace name context off;

Following command identifies the trace file that is created….

SQL> oradebug tracefile_name

c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_5548.trc

Oracle Documentation Library

Below page contains documents of all the versions of Oracle Database:

http://www.oracle.com/technology/documentation/index.html

--

Basic Grid Control commands

[oracle@naikh]export OMS_HOME=/u01/oracle/product/oms10g
[oracle@naikh]export AGENT_HOME=/u01/oracle/product/agent10g

Starting Grid Control Services

$AGENT_HOME/bin/emctl start agent
$OMS_HOME/opmn/bin/opmnctl startall


Stopping Grid Control Services

$AGENT_HOME/bin/emctl stop agent
$OMS_HOME/opmn/bin/opmnctl stoptall


Status of the Grid Control Services

$AGENT_HOME/bin/emctl status agent
$OMS_HOME/bin/emctl status oms
$OMS_HOME/opmn/bin/opmnctl status

Rebooting RAC servers

For some maintenance activity, if RAC servers need to be rebooted then the steps are:

1. Check the status of all the services
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

2. Stop the database
[oracle@naikh] srvctl stop database -d orcl

3. Stop nodeapps
[oracle@naikh] srvctl stop nodeapps hostname1
[oracle@naikh] srvctl stop nodeapps hostname2

4. Reboot the servers
Ask System admin to reboot the both the servers

5. Once the server is up check the status of all the services
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

6. Start the nodeapps if not automatically started.
[oracle@naikh] srvctl start nodeapps hostname1
[oracle@naikh] srvctl start nodeapps hostname2

7. Start the database if not automatically started.
[oracle@naikh] srvctl start database -d orcl

8. Make sure all the services are active
[oracle@naikh] cd $CRS_HOME/bin
[oracle@naikh] crs_stat -t

RMAN Recovery Catalog

This article gives the steps to create the recovery catalog.

1. Identify the database where catalog needs to be created.

Here I am using rcvcat as the catalog database

2. Create the tablespace to store the catalog information.

SQL> CREATE TABLESPACE CATALOG_DATA
DATAFILE '/u01/oradata/rvcvat/catalog_data_01.dbf'
SIZE 500M;

3. Create the user that is going to be the catalog owner

SQL> CREATE USER RMAN
IDENTIFIED BY RMAN
DEFAULT TABLESPACE CATALOG_DATA
TEMPORARY TABLESPACE TEMP;

4. Grant the roles and the privileges

SQL> GRANT RESOURCE TO RMAN;
SQL> GRANT CONNECT TO RMAN;

SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;

SQL> ALTER USER RMAN DEFAULT ROLE ALL;
SQL> ALTER USER RMAN QUOTA UNLIMITED ON RMAN_DATA;

5. Create the catalog

[oracle@naikh]$ rman catalog rman/rman@rvccat
RMAN> create catalog;

6. Register the target database

[oracle@naikh]$ export ORACLE_SID=orcl

[oracle@naikh]$ rman target / catalog rman/rman@rvccat

RMAN> register database;

Applying RMAN Incremental Backup To Standby Database

We can use RMAN incremental backup to roll forward the physical standby database in the following situations:
1. when an archive sequence is missing
2. when lots of archives needs to be applied

Steps:

1. Take the current SCN of the standby database

SQL> select to_char(current_scn) scn from v$database;
SCN
---------------
23339995

2. Stop the redo apply on the standby database

SQL> alter database recover managed standby database cancel;

3. Take the incremental backup of the production database from the current SCN of the standby database

RMAN> run {
allocate channel c1 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c2 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c3 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
allocate channel c4 type disk format '/backup/%d_incr_s%s_p%p' maxpiecesize 2g;
backup as compressed backupset incremental from scn 23339995 database;
}

4. Transfer the incremental backup to the standby system

$ scp /backup/* usename@standbyhost:/tmp/

5. Catalog all the incremental backup pieces to the standby database

RMAN> catalog start with '/tmp/' ;

6. Apply incremental backup to the standby database

RMAN> recover database noredo;

7. Create new standby controlfile at the production database, copy this to standby system and replace current standby controlfile with this new one.

SQL> alter database create standby controlfile as '/backup/standby_incr.ctl';

8. Start redo apply on the physical standby database

SQL> alter database recover managed standby database disconnect;


Note:
If you have added new datafile to the production and if it is not at created at standby you have to restore the datafile.
RAMN> restore datafile 56,57,58;