Tuesday, March 18, 2008

RTC-5150 OWB Error resolution


Problem Scenario
While working at client on an OBISE1 project ,the size of the DB started to grow at an enormous rate.After doing some initial digging around we found that the actual data was only 2GB but somehow the DB was taking 33GB.Contacted the DBA at client ,we went to look into Extent map through OEM and found that there were some blocks towards the end of the tablespace that were sitting between an area of blocks being used and some not used and hence Oracle thought of all that area as being used.Long story short the tablespace was not being reorganized so we moved those tables from the user tablespace to example tablespace and back into user tablespace.The tables that were moved were OWB managed tables (again application managed tables are a little different in there key structures)anyways the three tables that were moved where WB_RT_XXXX tables basically warehouse builder run time tables .So after the DB was resized and the tables were moved back I wanted to log back into OWB and wanted to use the OWB just to make sure things A-OK ,much to my surprise I was able to log into the design center ,but when I tried to access the control center I got a RTC-5150 error ,please refer to the figure above for how the beast looks like , it is an undocumented error.

Solution
Next morning me and DBA started to look into all the possible routes to figure out the cause and we started by looking into all the invalid objects in the databases starting with indexes ,packages and other object ,but the real resolution came back after we had re built the indexes.What happens is each index is ref by the leaf and rowid within a table. So when we move a table ,it becomes our responsibility to have to rebuild all indexes.Luckily indexes can be build online .
Use the following command
Query to see which indexes need to be re-built
"Alter index <> rebuild online";

No comments: