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

 

Difference between oracle SID and Oracle service name

This summary is not available. Please click here to view the post.

Restricted mode of instance

What is Restricted Mode of Instance Startup?



We can start an instance and mount and open a database in restricted mode so that the database is available only to DBAs.
Below are some reasons why we prefer Restricting mode  of instance:

    While performing export or import of database data(this is optional)
    While performing data load (with SQL*Loader)
    To Temporarily prevent users from using data
    During certain migration and upgrade operations

A normal cannot connect to the database which is in restricted mode ,but it can be made possible if RESTRICTED SESSION system privilege is provided to him.It is not recommended to provided RESTRICTED SESSION system privilege to normal user apart from DBAs.


Starting up an instance in restricted mode by using the command:
STARTUP RESTRICT
SQL> select logins from v$instance; --->to know the status of restricted mode.

Changing the restricted mode to normal mode by using the command:
ALTER SYSTEM DISABLE RESTRICTED SESSION;



cheers...!!!

Creating backup control file

How to create backup control file?

Control file is a binary file that consists of database structure, RMAN backup information, SCN number etc. But we can backup that file either into readable format or in to regular binary format.

Backing up control file to trace location in binary format:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;   -->the created backup file in this process will be stored in user_dump_dest location with random name
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';    -->the created backup file in this process will be stored in /oracle/backup/ path with control.bkp name   

Backing up control file to trace location in Readable format:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '\u01\dba\my_control.sql';    -->the created backup file in this process will be stored in /u01/dba/ path with my_control.sql file name 
We can read the database structure (CRD files, sizes, locations, SCN and RMAN information) in my_control.sql file in text format. This helps us in recreating control file whenever we lost control file from it's location.

ARCHIVELOG mode vs NOARCHIVELOG mode

What is the difference between ARCHIVELOG mode and NOARCHIVELOG mode?

ARCHIVELOG mode:
ARCHIVELOG mode is a mode where we put database to backup all transactions into logs(archivelogs) ,which help us in recovery scenarios.With the help of archived logs we can recover database till any point of time.
During archivelog mode redo information is written into archive logs using ARCn process.In order to utilize RMAN and HOT backup type of technologies we need our database to be in archivelog mode.

NOARCHIVELOG mode:
NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any  point of time we need.
NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.


Related links: http://orcl11gdba.blogspot.in/2014/03/enabling-archive-log-mode.html