tag:blogger.com,1999:blog-34068063044597921592024-03-08T11:33:37.698-08:00Emad Mansour - Sr. Oracle APPS DBAAny topics on my blog may be not match your issue so you have to test it first.Emad Mansourhttp://www.blogger.com/profile/04205512182189813955noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-3406806304459792159.post-37619592860077894192015-05-11T11:30:00.001-07:002015-05-13T23:50:46.021-07:00Index (when and how to rebuild it)<div dir="ltr" style="text-align: left;" trbidi="on">
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<br />
using command<br />
SQL> analyze index OWNER.INDEX_NAME validate structure;<br />
<br />
Now check index from dba_indexes.<br />
<br />
SQL> select blevel ,owner,index_name from dba_indexes where index_name='<INDEX_NAME>'<br />
or<br />
SQL>select height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as Percent_del_rows from index_stats;<br />
Note that index_stats carry only one index and should execute this query after analyze index and in the same session as all info<br />
recorded in your PGA.<br />
<br />
Below script will help you to get all index that need rebuild for specific schema but first but your schema name in where condition.<br />
<br />
SET SERVEROUTPUT ON<br />
DECLARE<br />
vOwner dba_indexes.owner%TYPE; /* Index Owner */<br />
vIdxName dba_indexes.index_name%TYPE; /* Index Name */<br />
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */<br />
vCursor NUMBER; /* DBMS_SQL cursor */<br />
vNumRows INTEGER; /* DBMS_SQL return rows */<br />
vHeight index_stats.height%TYPE; /* Height of index tree */<br />
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */<br />
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */<br />
vDLfPerc NUMBER; /* Del lf Percentage */<br />
vMaxHeight NUMBER; /* Max tree height */<br />
vMaxDel NUMBER; /* Max del lf percentage */<br />
CURSOR cGetIdx IS SELECT owner,index_name<br />
FROM dba_indexes WHERE OWNER = '<Schema_Owner>';<br />
BEGIN<br />
/* Define maximums. This section can be customized. */<br />
vMaxHeight := 3;<br />
vMaxDel := 20;<br />
<br />
/* For every index, validate structure */<br />
OPEN cGetIdx;<br />
LOOP<br />
FETCH cGetIdx INTO vOwner,vIdxName;<br />
EXIT WHEN cGetIdx%NOTFOUND;<br />
/* Open DBMS_SQL cursor */<br />
vCursor := DBMS_SQL.OPEN_CURSOR;<br />
/* Set up dynamic string to validate structure */<br />
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';<br />
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);<br />
vNumRows := DBMS_SQL.EXECUTE(vCursor);<br />
/* Close DBMS_SQL cursor */<br />
DBMS_SQL.CLOSE_CURSOR(vCursor);<br />
/* Does index need rebuilding? */<br />
/* If so, then generate command */<br />
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows<br />
FROM INDEX_STATS;<br />
IF vDLfRows = 0 THEN /* handle case where div by zero */<br />
vDLfPerc := 0;<br />
ELSE<br />
vDLfPerc := (vDLfRows / vLfRows) * 100;<br />
END IF;<br />
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN<br />
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');<br />
END IF;<br />
<br />
END LOOP;<br />
CLOSE cGetIdx;<br />
END;<br />
/</div>
Emad Mansourhttp://www.blogger.com/profile/04205512182189813955noreply@blogger.com1tag:blogger.com,1999:blog-3406806304459792159.post-34983697398856090072014-04-03T03:27:00.002-07:002014-04-03T03:32:06.001-07:00OCR and Vote Migrate after Upgrade Cluster from 10 to 11gR2<div dir="ltr" style="text-align: left;" trbidi="on">
1-set the ASM Compatibility compatibility attribute to 11.2.0.0<br />
ALTER DISKGROUP <Diskgroup Name> SET ATTRIBUTE 'compatible.asm' = '11.2.0.0';<br />
<br />
2-Moving OCR<br />
->crsctl query crs activeversion<br />
->Create OCR and voting Diskgroup using asmca or command line<br />
->ocrconfig -add +new_disk_group */To add ocr to asm disks/*<br />
->ocrconfig -delete old_storage_location */To remove ocr from old RAW device/*<br />
<br />
For Example:<br />
# ocrconfig -add +new_disk_group<br />
# ocrconfig -delete /dev/raw/raw2<br />
# ocrconfig -delete /dev/raw/raw1<br />
<br />
--> if you have more than one ocr location then use the following command direct:<br />
ocrconfig -replace current_OCR_location -replacement new_OCR_location<br />
<br />
3-Moving Vote<br />
crsctl replace votedisk <+diskgroup>|<vdisk><br />
<br />
========Check Your ocr and vote after migration ===========<br />
<br />
<$GRID_HOME>/bin/ocrcheck<br />
<$GRID_HOME>/bin/crsctl query css votedisk<br />
<br />
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'<br />
<br />
<br /></div>
Emad Mansourhttp://www.blogger.com/profile/04205512182189813955noreply@blogger.com0tag:blogger.com,1999:blog-3406806304459792159.post-1501402839381242742014-04-01T02:31:00.002-07:002014-04-02T05:52:44.116-07:00Delete Attachment files for Oracle E-Bus Suite 11i or R12<div dir="ltr" style="text-align: left;" trbidi="on">
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:<br />
<br />
1-select distinct (entity_name) from fnd_attached_documents; /*to get all attachment entity name related to your module*/<br />
<br />
2-Use below API to delete it.<br />
<br />
DECLARE<br />
l_delete_document_flag varchar2 (1):= 'Y';<br />
v_user_id NUMBER := 0;<br />
v_resp_id NUMBER := 20707;<br />
v_resp_appl_id NUMBER := 201;<br />
BEGIN<br />
FND_GLOBAL.APPS_INITIALIZE( user_id => v_user_id<br />
,resp_id => v_resp_id<br />
,resp_appl_id => v_resp_appl_id<br />
);<br />
<br />
FOR I IN (SELECT entity_name, pk1_value<br />
FROM fnd_attached_documents <br />
WHERE entity_name IN ( "Intity name from first select you can exclude any attachment you need on clone for any product")<br />
) LOOP<br />
FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS<br />
( X_entity_name => I.ENTITY_NAME<br />
, X_pk1_value => I.PK1_VALUE<br />
, X_delete_document_flag => l_delete_document_flag);<br />
<br />
COMMIT;<br />
END LOOP;<br />
EXCEPTION<br />
WHEN OTHERS THEN<br />
DBMS_OUTPUT.PUT_LINE('Err: '||SQLERRM);<br />
END;<br />
<br />
3-<br />
- Create a new tablespace :<br />
Create tablespace test add datafile size 40G;<br />
<br />
- Grant resouce access to the new tablespace :<br />
-->alter user APPLSYS quota unlimited on test;<br />
-->alter user APPLSYS quota unlimited on APPS_TS_MEDIA;<br />
<br />
- Move the tables in question to the new tablespace by doing the following:<br />
-->alter table applsys.fnd_lobs move lob (FILE_DATA) store as SYS_LOB0000057442C00004$$ (tablespace test);<br />
-->alter table applsys.fnd_lobs move tablespace test;<br />
<br />
<br />
-Move the tables back to the original tablespace<br />
alter table applsys.fnd_lobs move lob (FILE_DATA) store as SYS_LOB0000061704C00012$$(tablespace apps_ts_media);<br />
alter table applsys.fnd_lobs move tablespace apps_ts_media;<br />
<br />
<br />
-Re-build indexes (need to provide the index list)<br />
sql> spool index_rebuild.sql<br />
sql> select 'ALTER INDEX '||owner||'.'||INDEX_NAME||' rebuild; ' from dba_indexes where TABLESPACE_NAME='APPS_TS_MEDIA' and owner='APPLSYS';<br />
sql>spool off<br />
sql>spool index_rebld.lst<br />
sql>@index_rebuild.sql<br />
sql>spool off<br />
<br />
- Drop tablespace test including contents and datafiles;<br />
<br />
drop tablespace Test including contents and datafiles;<br />
Note: This action has to be done when there are no users logged on to applications.<br />
<br /></div>
Emad Mansourhttp://www.blogger.com/profile/04205512182189813955noreply@blogger.com3