How to find Unused indexes?

What is index monitoring?

Oracle databases consist of several custom indexes which are of different types useful for fast result retrieval of query. There are indexes which are not at all used and created just with an assumption that optimizer going to consider it in it's plan and our application will speed up. But creating unnecessary indexes for tables consume space. So, how to figure out these unused indexes in our database?

One of the best ways to identify this is index monitoring despite its disadvantages. Index monitoring gives the complete list of used/unused indexes that we enabled for. We can enable index monitoring on any index of any schema.

Enabling index monitoring:

ALTER INDEX sales.ground_idx MONITORING USAGE;

From the moment we enabled the index monitoring it starts gathering information and puts in V$OBJECT_USAGE table. We can't conclude the index usability on one single day. index monitoring has to be enabled for some more days and in the long run only we can decide whether index can be deleted or not.

select   * from  V$OBJECT_USAGE where index_name = 'GROUND_IDX';

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
GROUND_IDX	GROUND_SELLER_DETAILS	YES	NO	7/18/2014 10:43

After few days we are going to disable the index monitoring and will see the report

Disabling index monitoring:

ALTER INDEX sales.ground_idx   NOMONITORING USAGE;

This is the result we get from v$object_usage with end_monitoring(date on which we stopped the monitoring)

INDEX_NAME	TABLE_NAME	MONITORING	USED	START_MONITORING	END_MONITORING
GROUND_IDX	GROUND_SELLER_DETAILS	YES	NO	7/18/2014 10:43	9/18/2014 7:24

Now the span of two months none of my application/users used this index. So I can drip this index now.

Note: Even though I have enabled the index monitoring I am not getting any result from V$OBJECT_USAGE. I don’t find rows in this table .Why?

Reason:  As per oracle documents, V$OBJECT_USAGE displays the index usage information belong to that schema only.so any other schema apart from sales won’t get the above result. So we have to login to individual schemas to check their respective index usage details.

Scripts:

    a) Enabling index monitoring on bulk number of indexes

spool start_index_monitoring.sql

SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;' FROM dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM','SYSMAN','DBSNMP');

SPOOL off

@start_index_monitoring.sql

b)Disabling  index monitoring on bulk number of indexes 

spool stop_index_monitoring.sql

SELECT 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;' FROM dba_indexes WHERE owner NOT IN ('SYS', 'SYSTEM','SYSMAN','DBSNMP');

SPOOL off

@stop_index_monitoring.sql

 

ASMCMD-08102: no connection to ASM; command requires ASM to run

How to resolve the error  "ASMCMD-08102: no connection to ASM; command requires ASM to run" ?

[oracle@host1.xxx.com]$ asmcmd
Connected to an idle instance.
ASMCMD> ls
ASMCMD-08102: no connection to ASM; command requires ASM to run

Cause: This is a simple error occurred because the ASM instance environment variables are not set properly

Solution:
We need to Set ASM instance environment variables
[oracle@host1.xxx.com ]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid  
                                                                                   (or)
                                                setenv ORACLE_HOME  /u01/app/oracle/product/11.2.0/grid
[oracle@host1.xxx.com ]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
                                                                                   (or )
                                                setenv PATH /u01/app/oracle/product/11.2.0/grid/bin
[oracle@host1.xxx.com ]$ export ORACLE_SID=+ASM1
                                                                                    (or)
                                                 setenv ORACLE_SID  +ASM1

Now the variables are set properly you can check the list
[oracle@host1.xxx.com ]$ asmcmd

ASMCMD> ls
DATA/
FRA/
OCR_VOTE/

Deinstalling/Uninstalling Oracle database in Linux machine

How to de-install Oracle database software including database instance?

Follow the below steps to de-install Oracle software:

Step-1: Go to DB home path

cd $ORACLE_HOME

Step-2:Go to deinstall dorectory

  [oracle@ttd-server db_1]$ cd deinstall/

[oracle@ttd-server deinstall]$ ls

deinstall  deinstall.xml  jlib  response  sshUserSetup.sh

Step-3:Now de-install the software as shown below.it includes database de-installation also

[oracle@ttd-server deinstall]$ ./deinstall

Checking for required files and bootstrapping ...

Please wait ...

Location of logs /tmp/deinstall2014-08-07_05-26-56-PM/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################## CHECK OPERATION START ########################

Install check configuration START

Checking for existence of the Oracle home location /u01/app/oracle/product/11.2.0/db_1

Oracle Home type selected for de-install is: SIDB

Oracle Base selected for de-install is: /u01/app/oracle

Checking for existence of central inventory location /u01/oracle/oraInventory

Install check configuration END

Network Configuration check config START

Network de-configuration trace file location: /tmp/deinstall2014-08-07_05-26-56-PM/logs/netdc_check4894214071504988600.log

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /tmp/deinstall2014-08-07_05-26-56-PM/logs/databasedc_check7761846242532288656.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home [pavantes]: pavantes    

Note: In this step we are going to give our database name inorder to uninstall it .my database instance is pavantes

###### For Database 'pavantes' ######

Single Instance Database

The diagnostic destination location of the database: /u01/app/oracle/diag/rdbms/pavantes

Storage type used by the Database: FS

Database file location: /u01/app/oracle/oradata/pavantes

Flash recovery area location: /u01/app/oracle/oradata/pavantes/FRA

database spfile location: /u01/app/oracle/product/11.2.0/db_1/dbs/spfilepavantes.ora

The details of database(s) pavantes have been discovered automatically. Do you still want to modify the details of pavantes database(s)? [n]: n

Note: in this step we are confirming that our database details are correct by saying no need for modification ‘n’

Database Check Configuration END

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /tmp/deinstall2014-08-07_05-26-56-PM/logs/emcadc_check.log

Checking configuration for database pavantes

Enterprise Manager Configuration Assistant END

Oracle Configuration Manager check START

OCM check log file location : /tmp/deinstall2014-08-07_05-26-56-PM/logs//ocm_check9847.log

Oracle Configuration Manager check END

######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################

Oracle Home selected for de-install is: /u01/app/oracle/product/11.2.0/db_1

Inventory Location where the Oracle home registered is: /u01/oracle/oraInventory

The following databases were selected for de-configuration : pavantes

Database unique name : pavantes

Storage used : FS

No Enterprise Manager configuration to be updated for any database(s)

No Enterprise Manager ASM targets to update

No Enterprise Manager listener targets to migrate

Checking the config status for CCR

Oracle Home exists with CCR directory, but CCR is not configured

CCR check is finished

Note:till now the pre-checks for deinstall are completed . now it will start the clean(de-install) process by taking our input as ‘y’

Do you want to continue (y - yes, n - no)? [n]: y

A log of this session will be written to: '/tmp/deinstall2014-08-07_05-26-56-PM/logs/deinstall_deconfig2014-08-07_05-27-36-PM.out'

Any error messages from this session will be written to: '/tmp/deinstall2014-08-07_05-26-56-PM/logs/deinstall_deconfig2014-08-07_05-27-36-PM.err'

######################## CLEAN OPERATION START ########################

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /tmp/deinstall2014-08-07_05-26-56-PM/logs/emcadc_clean.log

Updating Enterprise Manager ASM targets (if any)

Updating Enterprise Manager listener targets (if any)

Enterprise Manager Configuration Assistant END

Database de-configuration trace file location: /tmp/deinstall2014-08-07_05-26-56-PM/logs/databasedc_clean1338915545980048440.log

Database Clean Configuration START pavantes

This operation may take few minutes.

Database Clean Configuration END pavantes

Network Configuration clean config START

Network de-configuration trace file location: /tmp/deinstall2014-08-07_05-26-56-PM/logs/netdc_clean8676820294296695118.log

De-configuring backup files...

Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START

OCM clean log file location : /tmp/deinstall2014-08-07_05-26-56-PM/logs//ocm_clean9847.log

Oracle Configuration Manager clean END

Oracle Universal Installer clean START

Detach Oracle home '/u01/app/oracle/product/11.2.0/db_1' from the central inventory on the local node : Done

Delete directory '/u01/app/oracle/product/11.2.0/db_1' on the local node : Done

Delete directory '/u01/oracle/oraInventory' on the local node : Done

Delete directory '/u01/app/oracle' on the local node : Done

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END

Oracle install clean START

Clean install operation removing temporary directory '/tmp/install' on node 'ttd-server'

Oracle install clean END

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################

Successfully de-configured the following database instances : pavantes

Cleaning the config for CCR

As CCR is not configured, so skipping the cleaning of CCR configuration

CCR clean is finished

Successfully detached Oracle home '/u01/app/oracle/product/11.2.0/db_1' from the central inventory on the local node.

Successfully deleted directory '/u01/app/oracle/product/11.2.0/db_1' on the local node.

Successfully deleted directory '/u01/oracle/oraInventory' on the local node.

Successfully deleted directory '/u01/app/oracle' on the local node.

Oracle Universal Installer cleanup was successful.

Run 'rm -rf /etc/oraInst.loc' as root on node(s) 'ttd-server' at the end of the session.

Oracle install successfully cleaned up the temporary directories.

#######################################################################

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

[oracle@ttd-server deinstall]$ cd /u01/app/oracle/product/11.2.0/db_1

/u01/app/oracle/product/11.2.0/db_1: No such file or directory.

[oracle@ttd-server deinstall]$ cd /u01/app/oracle

/u01/app/oracle: No such file or directory.

Congrats.our de-install process is completed.during the entire above process what we observed is, it checked the databases on the local-node(server) and confirmed the details with us and did the clean process.

cheers....!!!