Index (when and how to rebuild it)

Index should rebuild if blevel column in dba_indexes greater than 3 or you can query INDEX_STATS for lef_rows and del_lef_rows to know the percentage of deleted rows from the index. if the percentage is more than 20% but make sure to analyze index before check blevel column
using command
SQL> analyze index OWNER.INDEX_NAME validate structure;

Now check index from dba_indexes.

SQL> select blevel ,owner,index_name from dba_indexes where index_name='<INDEX_NAME>'
or
SQL>select height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as Percent_del_rows from index_stats;
Note that index_stats carry only one index and should execute this query after analyze index and in the same session as all info
recorded in your PGA.

Below script will help you to get all index that need rebuild for specific schema but first but your schema name in where condition.

SET SERVEROUTPUT ON
DECLARE
  vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
  vIdxName dba_indexes.index_name%TYPE;       /* Index Name             */
  vAnalyze VARCHAR2(100);                     /* String of Analyze Stmt */
  vCursor  NUMBER;                            /* DBMS_SQL cursor        */
  vNumRows INTEGER;                           /* DBMS_SQL return rows   */
  vHeight  index_stats.height%TYPE;           /* Height of index tree   */
  vLfRows  index_stats.lf_rows%TYPE;          /* Index Leaf Rows        */
  vDLfRows index_stats.del_lf_rows%TYPE;      /* Deleted Leaf Rows      */
  vDLfPerc   NUMBER;                          /* Del lf Percentage      */
  vMaxHeight NUMBER;                          /* Max tree height        */
  vMaxDel    NUMBER;                          /* Max del lf percentage  */
  CURSOR cGetIdx IS SELECT owner,index_name
     FROM dba_indexes WHERE OWNER = '<Schema_Owner>';
BEGIN
  /* Define maximums. This section can be customized. */
  vMaxHeight := 3;
  vMaxDel    := 20;

  /* For every index, validate structure */
  OPEN cGetIdx;
  LOOP
     FETCH cGetIdx INTO vOwner,vIdxName;
     EXIT WHEN cGetIdx%NOTFOUND;
     /* Open DBMS_SQL cursor */
     vCursor := DBMS_SQL.OPEN_CURSOR;
     /* Set up dynamic string to validate structure */
     vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
     DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
     vNumRows := DBMS_SQL.EXECUTE(vCursor);
     /* Close DBMS_SQL cursor */
     DBMS_SQL.CLOSE_CURSOR(vCursor);
     /* Does index need rebuilding?  */
     /* If so, then generate command */
     SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
        FROM INDEX_STATS;
     IF vDLfRows = 0 THEN         /* handle case where div by zero */
        vDLfPerc := 0;
     ELSE
        vDLfPerc := (vDLfRows / vLfRows) * 100;
     END IF;
     IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
        DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
     END IF;

  END LOOP;
  CLOSE cGetIdx;
END;
/

OCR and Vote Migrate after Upgrade Cluster from 10 to 11gR2

1-set the ASM Compatibility compatibility attribute to 11.2.0.0
ALTER DISKGROUP <Diskgroup Name> SET ATTRIBUTE 'compatible.asm' = '11.2.0.0';

2-Moving OCR
->crsctl query crs activeversion
->Create OCR and voting Diskgroup using asmca or command line
->ocrconfig -add +new_disk_group */To add ocr to asm disks/*
->ocrconfig -delete old_storage_location */To remove ocr from old RAW device/*

For Example:
# ocrconfig -add +new_disk_group
# ocrconfig -delete /dev/raw/raw2
# ocrconfig -delete /dev/raw/raw1

--> if you have more than one ocr location then use the following command direct:
ocrconfig -replace current_OCR_location -replacement new_OCR_location

3-Moving Vote
crsctl replace votedisk <+diskgroup>|<vdisk>

========Check Your ocr and vote after migration ===========

<$GRID_HOME>/bin/ocrcheck
<$GRID_HOME>/bin/crsctl query css votedisk

SQL>select d.name,  d.path, dg.name as dg_name from v$asm_disk d, v$asm_diskgroup dg where d.group_number = dg.group_number and dg.name = 'OCR'

Delete Attachment files for Oracle E-Bus Suite 11i or R12

If you face issue with huge space for attachment files on Oracle E-Bus Suite and you need to delete it from Cloned instance to reclaim your space, Please follow below procedure:

1-select distinct (entity_name) from fnd_attached_documents; /*to get all attachment entity name related to your module*/

2-Use below API to delete it.

DECLARE
    l_delete_document_flag  varchar2 (1):= 'Y';
    v_user_id               NUMBER := 0;
    v_resp_id               NUMBER := 20707;
    v_resp_appl_id          NUMBER := 201;
BEGIN
    FND_GLOBAL.APPS_INITIALIZE( user_id      => v_user_id
                               ,resp_id      => v_resp_id
                               ,resp_appl_id => v_resp_appl_id
                              );

    FOR I IN (SELECT  entity_name, pk1_value
              FROM fnd_attached_documents
              WHERE entity_name IN (  "Intity name from first select you can exclude any attachment you need on clone for any product")
             ) LOOP
    FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS
                                                  ( X_entity_name                  => I.ENTITY_NAME
                                                  , X_pk1_value                    => I.PK1_VALUE
                                                  , X_delete_document_flag         => l_delete_document_flag);

    COMMIT;
END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Err: '||SQLERRM);
END;

3-
- Create a new tablespace :
Create tablespace test add datafile  size 40G;

- Grant resouce access to the new tablespace :
-->alter user APPLSYS quota unlimited on test;
-->alter user APPLSYS quota unlimited on APPS_TS_MEDIA;

- Move the tables in question to the new tablespace by doing the following:
-->alter table applsys.fnd_lobs move lob (FILE_DATA) store as SYS_LOB0000057442C00004$$ (tablespace test);
-->alter table applsys.fnd_lobs move tablespace test;


-Move the tables back to the original tablespace
alter table applsys.fnd_lobs move lob (FILE_DATA) store as SYS_LOB0000061704C00012$$(tablespace apps_ts_media);
alter table applsys.fnd_lobs move tablespace apps_ts_media;


-Re-build indexes (need to provide the index list)
sql> spool index_rebuild.sql
sql> select 'ALTER INDEX '||owner||'.'||INDEX_NAME||' rebuild; ' from dba_indexes where TABLESPACE_NAME='APPS_TS_MEDIA' and owner='APPLSYS';
sql>spool off
sql>spool index_rebld.lst
sql>@index_rebuild.sql
sql>spool off

- Drop tablespace test including contents and datafiles;

drop tablespace Test including contents and datafiles;
Note: This action has to be done when there are no users logged on to applications.