Monday, May 11, 2015

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>'
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.

  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>';
  /* Define maximums. This section can be customized. */
  vMaxHeight := 3;
  vMaxDel    := 20;

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

  CLOSE cGetIdx;

Thursday, April 3, 2014

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

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

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/crsctl query css votedisk

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

Tuesday, April 1, 2014

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.

    l_delete_document_flag  varchar2 (1):= 'Y';
    v_user_id               NUMBER := 0;
    v_resp_id               NUMBER := 20707;
    v_resp_appl_id          NUMBER := 201;
    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
                                                  ( X_entity_name                  => I.ENTITY_NAME
                                                  , X_pk1_value                    => I.PK1_VALUE
                                                  , X_delete_document_flag         => l_delete_document_flag);


- 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>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.