Tuesday 25 September 2018

How To Find The Unused Indexes In Oracle Database

Getting rid of unused indexes on the database will not only free up space but it will boost the DML performance as well. But if you mistakenly get rid of a used index this may impact the performance. So let's go through the following steps which will let you do this task in a safe way.

Start With The Biggest Indexes In the Database:
I usually start this job by monitoring the biggest indexes in the database which can release much space and relief much of DML overhead on their underlying tables if those indexes got dropped.
This query will show the biggest 100 indexes:

set pages 200 lines 168
col owner for a40
col SEGMENT_NAME for a45
col TABLESPACE_NAME for a35
Select * from (select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 SIZE_MB from dba_segments where SEGMENT_TYPE like 'INDEX%' order by 5 desc)where rownum <101 order by 5;


In order to get Oracle to monitor those indexes to figure out if they are used or not we should use this command:

ALTER INDEX <OWNER>.<INDEX_NAME> MONITORING USAGE;

Monitor The Biggest Indexes:
Now Let's enable monitoring on the biggest 100 indexes on the database using the output from this command:

set pages 200 lines 168
col Enable_Monitoring_Command for a100
Select * from (select 'ALTER INDEX '||OWNER||'.'||SEGMENT_NAME||' MONITORING USAGE;' Enable_Monitoring_Command,BYTES/1024/1024 SIZE_MB  from dba_segments where SEGMENT_TYPE like 'INDEX%' order by 2 desc) where rownum <101 order by 2 desc;

Executing the commands generated under "Enable_Monitoring_Command" will put the concerned indexes under monitoring.

Check the Monitored Indexes:
As a rule of thumb, you should keep those indexes under monitoring for the longest period as possible to avoid dropping an index that is being used somewhere in a weekly/monthly report.
From time to time, you have to check the monitored indexes that are reported as "USED" and remove them from the monitoring:

set linesize 200 pages 1000
col Index_NAME for a40
col TABLE_NAME for a40
col Monitored  for a9
col Used for a4
        select io.name Index_NAME, t.name TABLE_NAME,decode(bitand(i.flags, 65536),0,'NO','YES') Monitored,
        decode(bitand(ou.flags, 1),0,'NO','YES') USED,ou.start_monitoring,ou.end_monitoring
        from sys.obj$ io,sys.obj$ t,sys.ind$ i,sys.object_usage ou
where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#
order by 3 desc,4;

To Un-Monitor indexes reported as "USED", use this command:

select 'ALTER INDEX <Index_owner>.'||io.name||' NOMONITORING USAGE;' INDEXES_IN_USE
from sys.obj$ io,sys.obj$ t,sys.ind$ i,sys.object_usage ou
where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#
and decode(bitand(i.flags, 65536),0,'NO','YES')='YES' and decode(bitand(ou.flags, 1),0,'NO','YES')='YES' order by 1;

After keeping the rest of indexes under monitoring for a sufficent time (at least one month), do the following checks:

Make sure the "UNUSED" indexes are not associated with constraints:

SELECT OWNER,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM DBA_CONSTRAINTS
WHERE INDEX_NAME IN (select io.name from sys.obj$ io,sys.obj$ t,sys.ind$ i,sys.object_usage ou
where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#
and decode(bitand(i.flags, 65536),0,'NO','YES')='YES' and decode(bitand(ou.flags, 1),0,'NO','YES')='NO');

Check that UNUSED Indexes are not appearing in Exec Plans:
Make sure the "UNUSED" indexes are not used in any recent/old SQL Statements Execution plans:

select max(TIMESTAMP),object_name from v$SQL_PLAN
where OBJECT_NAME in ('<INDEX_NAME>')
group by object_name;

select max(TIMESTAMP),object_name from DBA_HIST_SQL_PLAN
where OBJECT_NAME in ('<INDEX_NAME>')
group by object_name;

Make sure the "UNUSED" indexes are not used in any recent SQL Statements Execution plans:

Set the UNUSED Index into INVISIBLE mode for sometime:
After making sure that the "UNUSED" indexes are not associated with any constraints and they are not used anymore in execution plans, you can put them into the INVISIBLE mode for some time:

 select 'ALTER INDEX P_FZ.'||io.name||' INVISIBLE;' SET_INVISIBLE
 from sys.obj$ io,sys.obj$ t,sys.ind$ i,sys.object_usage ou
where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#
and decode(bitand(i.flags, 65536),0,'NO','YES')='YES' and decode(bitand(ou.flags, 1),0,'NO','YES')='NO';

Setting indexes into INVISIBLE mode will make them invisible to the application and thus will not be used by any query, This is much safer and faster than dropping the index, in case you came to know that those indexes are still being used, you can easily set them back to VISIBLE mode rather than re-create them from scratch in case you dropped them. 

Lastly, Drop the UNUSED Indexes:
Once we are definitely sure that the "UNUSED" indexes are not used, then we can drop them.
This command will drop all UNUSED monitored indexes:

select 'DROP INDEX P_FZ.'||io.name||' ;' DROP_INDEX
from sys.obj$ io,sys.obj$ t,sys.ind$ i,sys.object_usage ou
where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#
and decode(bitand(i.flags, 65536),0,'NO','YES')='YES' and decode(bitand(ou.flags,1),0,'NO','YES')='NO';


Sunday 23 September 2018

How To Drop An Oracle Database (Complete Guide)

Dropping a database is considered as a rare/dangerous/easy task, because of this it's not well documented as I think.
In general, there are two ways of dropping a database, using dbca and the manual way.
I usually don't trust GUI tools which dbca is one of them, this is why I'll explain the manual way :-)

I'll explain the steps for dropping a STAND ALONE & RAC databases.

Before you drop any DB make sure you're 100% conscious, not in a middle of something else and you're not in a hurry :-)

Dropping a STANDALONE DB:
let's suppose the database name to be dropped is orcl.

If the database is registered with Grid Control:
Connect to Grid Control with an admin user (e.g. SYSMAN)
Targets -> Databases -> Select ORCL -> Remove

Restart the DB in "RESTRICT" "EXCLUSIVE" mode:

[By oracle]
export ORACLE_SID=orcl
sqlplus "/ as sysdba"
SQL> shu immediate;
SQL> startup mount exclusive restrict;
SQL> select INSTANCE_NAME,HOST_NAME from v$instance;

Drop the database from RMAN:
export ORACLE_SID=orcl
rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 10 10:19:48 2018

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

connected to target database: ORCL (DBID=1220202490, not open)

RMAN> DROP DATABASE INCLUDING BACKUPS;
...
database name is "ORCL" and DBID is 1220202490
database dropped
RMAN> exit

Note: INCLUDING BACKUPS option will delete RMAN Backups/Datafiles & Controlfiles copies along with the Database files.

Remove the DB from ORACLE RESTART repository:
[By oracle]
srvctl remove database -d orcl 

Delete the database entry from /etc/oratab: [both Nodes]

# vi /etc/oratab
[Remove the similar line]
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

[Optional] Delete the instance files from $ORACLE_HOME/dbs:

cd $ORACLE_HOME/dbs
ls -l *orcl*

[Optional] Remove database diag directory from $ORACLE_BASE/diag/rdbms:

adrci
ADR base = "/u01/app/oracle"

adrci> show homes rdbms
ADR Homes:
diag/rdbms/orcl/orcl

adrci> exit

cd /u01/app/oracle/diag/rdbms
rm -rf orcl


Dropping a RAC DB:

let's suppose the database name to be dropped is orcl.

Remove the database from Grid/Cloud Control if it's already registered with it:
Connect to Grid Control with an admin user (e.g. SYSMAN)
Targets -> Databases -> Select ORCL -> Remove

Set the cluster_database parameter to false:
SQL> alter system set cluster_database=false scope=spfile;

Shutdown the database on ALL RAC nodes:
srvctl stop database -d orcl

Startup the DB in "RESTRICT" "EXCLUSIVE" mode: from only one node:

[By oracle]
export ORACLE_SID=orcl1
sqlplus "/ as sysdba"
SQL> startup mount exclusive restrict;
SQL> select INSTANCE_NAME,HOST_NAME from v$instance;

Drop the database:
-----------------
export ORACLE_SID=orcl1
# rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 10 10:17:42 2018

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

connected to target database: ORCL (DBID=1675848900, not open)

RMAN> DROP DATABASE INCLUDING BACKUPS;
...
database name is "ORCL" and DBID is 1675848900
database dropped
RMAN> exit

Remove the database from the Clusterware configurations:
[By oracle]
srvctl remove database -d orcl

Delete the database & instance entry from /etc/oratab: [Both RAC nodes]
vi /etc/oratab
orcl1:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent
orcl:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent

[Optional] Delete instance files from $ORACLE_HOME/dbs: [Both RAC nodes]
cd $ORACLE_HOME/dbs
ls -l *orcl*

[Optional] Remove database diag directory from $ORACLE_BASE/diag/rdbms:  [Both RAC nodes]
adrci
ADR base = "/u01/app/oracle"

adrci> show homes rdbms
ADR Homes:
diag/rdbms/orcl/orcl1
diag/rdbms/orcl/orcl
diag/rdbms/test/test
diag/rdbms/test/test1
diag/rdbms/_mgmtdb/-MGMTDB

adrci> exit

# cd /u01/app/oracle/diag/rdbms
# rm -rf orcl