Sunday, September 14, 2008

unable to extend temp segment by 8192 in tablespace MSCX

Issue Development team send email with given below issue. Concurrent program failed with given error. ---------------------------------------------------------- <> <>CREATE INDEX ITEM_CATEGORIES_N3_IKN ON ITEM_CATEGORIES_IKN(CATEGORY_SET_ID,ORGANIZATION_ID,SR_INSTANCE_ID,SR_CATEGORY_ID,CATEGORY_NAME,DESCRIPTION) PARALLEL TABLESPACE MSCX STORAGE( INITIAL 106496 NEXT 67108864 PCTINCREASE 0) PCTFREE 10 INITRANS 11 MAXTRANS 255 ORA-12801: error signaled in parallel query server P005, instance ios0901e:EPRODR1 (1) ORA-01652: unable to extend temp segment by 8192 in tablespace MSCX <> <>ALTER TABLE MSC_ITEM_CATEGORIES EXCHANGE PARTITION ITEM_CATEGORIES__1 WITH TABLE ITEM_CATEGORIES_IKN INCLUDING INDEXES ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION Calling Linking of Sales Order for 11i source ... Procedure LINK_SUPP_SO_DEMAND_EXT started. Truncated Source AD Tables Successfully ---------------------------------------------------------- Troubleshooting Steps Script was failing while creating index. I check initial and next space utilization of tablespace, which was good. I checked space utilization using given below query. #CONTIG_FREE_SPACE.sql #PL/SQL script to determine how much contiguous #free space is available for each tablespace in the database. Oracle will #acquire space by searching first for exact fit, then next best fit, and lastly#coalesce if possible. create table SPACE_TEMP ( TABLESPACE_NAME CHAR(30), CONTIGUOUS_BYTES NUMBER) / declare cursor query is select * from dba_free_space WHERE TABLESPACE_NAME='MSCX' order by tablespace_name, block_id; this_row query%rowtype; previous_row query%rowtype; total number; begin open query; fetch query into this_row; previous_row := this_row; total := previous_row.bytes; loop fetch query into this_row; exit when query%notfound; if this_row.block_id = previous_row.block_id + previous_row.blocks then total := total + previous_row.bytes; insert into SPACE_TEMP (tablespace_name) values (previous_row.tablespace_name); else insert into SPACE_TEMP values (previous_row.tablespace_name, total); total := this_row.bytes; end if; previous_row := this_row; end loop; insert into SPACE_TEMP values (previous_row.tablespace_name, total); end; / set pagesize 60 set newpage 0 set echo off ttitle center 'Contiguous Extents Report' - skip 1 center new_today skip 3 break on "TABLESPACE NAME" skip page duplicate spool contig_free_space.lis rem column "CONTIGUOUS BYTES" format 999,999,999 column "COUNT" format 999 column "TOTAL BYTES" format 999,999,999 column "TODAY" noprint new_value new_today format a1 rem select TABLESPACE_NAME "TABLESPACE NAME", CONTIGUOUS_BYTES "CONTIGUOUS BYTES", count(*) "COUNT", CONTIGUOUS_BYTES*count(*) "TOTAL BYTES", to_char(sysdate,'FMMonth DD, YYYY') "TODAY "from SPACE_TEMP where CONTIGUOUS_BYTES is not nullgroup by TABLESPACE_NAME, CONTIGUOUS_BYTES order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc / spool off drop table SPACE_TEMP / Solution We dropped recyle bin object from tablespace to free space. SQL> purge tablespace Again I checked contigouse space which was more than required next space. Note--> Next space allocation is in form of next value (its not number of block. It is number of byte)

Unable to invoke "SOAP Endpoint URI" URL

Issue Development team were unable to invokd "SOAP Endpoint URI" URL from ESB console. Environment SOA Suite 10.1.3.1 Installation and Update to 10.1.3.3 Produce Error Login to ESB console http://jai1.singh.com:8890/esb User id = oc4jadmin Password = ******** Click on “WSF_To_OM_Integration”  “Validate_Line_Attribute” and then Link against “SOAP Endpoint URI” It opens blank page with error "The page cannot be found" Troubleshooting Steps http://jai1.singh.com:8890/em User id = oc4jadmin Password = Go to Home à “Web Services” à Click on Any “Port Name” e.g. “__soap_ChangeStatus_to_DB_RS_execute_ppt” It has reference of two test link one using 8890 (http) and other one 4443 (https). Note--> We never configured https protocol on this server. We searched for text file having reference to port 4443 and noticed $ORACLE_HOME/j2ee/home/config/secure-web-site.xml find . -type f -printxargs file cut -f1 -d: xargs grep -i -l "4443" tee -a 4443.log Solution - This file was not there on any instance and analyst advised to move this file to some other name. $mv $ORACLE_HOME/j2ee/home/config/secure-web-site.xml $ORACLE_HOME/j2ee/home/config/secure-web-site.xml.orig - Restarted opmn services

Monday, September 8, 2008

Change ONS Port

Unable To Start Opmn - "Listener bind failed" error ins ons.log Problem Unable To Start Opmn - "Listener bind failed" error ins ons.log I was not able to start opmn services. I noticed given below error in $ORACLE_HOME/opmn/logs/ons.log file 08/09/08 18:43:18 [2] BIND (Address already in use)08/09/08 18:43:18 [2] 10.11.62.124:394526720 - listener BIND failed08/09/08 18:43:18 [4] Listener thread 2057: 10.11.62.124:394526720 (0x404) termi ons port was 6220 $ cat $ORACLE_HOME/opmn/conf/ons.conf nodes=ios5102e.idc.ikon.org:6220 I checked uses of 6220 port. netstat -na grep 6220 There was no process still I was getting above error. Solution I performed following. 1. Changed ons port of $ORACLE_HOME/opmn/conf/opmn.xml file $cp $ORACLE_HOME/opmn/conf/opmn.xml $ORACLE_HOME/opmn/conf/opmn.xml.090908 2. Changed notification-server port from 6020 to 6060. 3. Updated change using dcmctl $ORACLE_HOME/dcm/bin/dcmctl updateConfig -ct opmn -v -d 4. Started opmn services $ORACLE_HOME/opmn/bin/opmnctl startall