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.