I've been fighting it for some time now.
I have an old oracle lite database that houses the internals for a BPEL install. the database is orabpel.odb and exists in D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite.
This was used for an integration project that uses EXCEL to call BSSV WS via BPEL PM. Pretty cool hey. BPEL PM might have been over kill, but it was nice and neat! This has been working for about 5 years, and the client called me and told me it was broken... Oh dear...
The orabpel database was 4GB!
I cannot do anything with it.
I learned how to connect to the database, this is all done through the magic of polite.ini and
[All Databases]
DatabaseID=1735
DataDirectory=D:\product\10.1.3.1\OracleAS_1\Mobile\Sdk\OLDB40
NLS_LOCALE=ENGLISH
NLS_LANGUAGE=ENGLISH
MessageFile=D:\product\10.1.3.1\OracleAS_1\Mobile\Sdk\BIN\olite40.msb
DB_CHAR_ENCODING=UTF8
DATABASE_ID=503
#DATA_DIRECTORY=D:\temp\10.1.3.1\OraBPEL_4\Mobile\Sdk\oldb40
#MESSAGE_FILE=D:\temp\10.1.3.1\OraBPEL_4\Mobile\Sdk\BIN\OLITE40.MSB
And odbc.ini (of course)
[ODBC 32 bit Data Sources]
orabpel=Oracle Lite 40 ODBC Driver (32 bit)
WEBTOGO=Oracle Lite 40 ODBC Driver (32 bit)
POLITE=Oracle Lite 40 ODBC Driver (32 bit)
oraesb=Oracle Lite 40 ODBC Driver (32 bit)
orawsm=Oracle Lite 40 ODBC Driver (32 bit)
shannon=Oracle Lite 40 ODBC Driver (Client) (32 bit)
[POLITE]
Driver32=D:\temp\10.1.3.1\OraBPEL_4\Mobile\Sdk\BIN\olod2040.dll
Description=Oracle Lite 40 Data Source
DataDirectory=D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite
Database=POLITE
IsolationLevel=Read Committed
Autocommit=Off
CursorType=Static
[WEBTOGO]
Driver32=D:\temp\10.1.3.1\OraBPEL_4\Mobile\Sdk\BIN\olod2040.dll
Description=Oracle Lite 40 Data Source
DataDirectory=D:\temp\10.1.3.1\OraBPEL_4\Mobile\Sdk\oldb40
Database=WEBTOGO
IsolationLevel=Read Committed
Autocommit=Off
CursorType=Static
[oraesb]
Driver32=D:\product\10.1.3.1\OracleAS_1\Mobile\Sdk\BIN\olod2040.dll
Description=Oracle Lite 40 Data Source
IsolationLevel=Read Committed
Database=oraesb
Autocommit=Off
CursorType=Static
DataDirectory=D:\product\10.1.3.1\OracleAS_1\Mobile\Sdk\OLDB40
[orabpel]
Driver32=D:\temp\10.1.3.1\OraBPEL_4\Mobile\Sdk\bin\olod2040.dll
Description=Oracle Lite 40 Data Source
DataDirectory=D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite
Database=orabpel
IsolationLevel=Repeatable Read
Autocommit=Off
CursorType=Static
[orawsm]
Driver32=D:/product/10.1.3.1/OracleAS_1/Mobile/Sdk/BIN/olod2040.dll
Description=Oracle Lite 40 Data Source
DataDirectory=D:/product/10.1.3.1/OracleAS_1/Mobile/Sdk/OLDB40
Database=orawsm
IsolationLevel=Read Committed
Autocommit=Off
CursorType=Static
[shannon]
Driver32=D:\product\10.1.3.1\OracleAS_1\Mobile\Sdk\BIN\olcl2040.dll
Description=shannon
ServerHostName=localhost
ServerPortNumber=1531
ServerDSN=
DataDirectory=D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite
Database=orabpel.odb
IsolationLevel=Read Committed
Autocommit=Off
CursorType=Forward Only
These two files contain the pointers that BPEL is going to talk to when looking for the internal configuration.
Querying the data was difficult, but finally I used:
D:\temp\10.1.3.1\OracleAS_1\Mobile\Sdk\BIN\msql system/manager@jdbc:polite4@127.0.0.1:1531:orabpel
Note that msql did not work out of the box either, it was complaining about JDK had to be higher that 1.3, I only had 1.5 on the machine. I always dread putting new JDK's on an old machine, as I don't want JAVA_HOME and the path messed with, which could mess with the existing servers. I put the latest install onto the 2000 machine, and it worked, msql started working.
Note that msql did not work out of the box either, it was complaining about JDK had to be higher that 1.3, I only had 1.5 on the machine. I always dread putting new JDK's on an old machine, as I don't want JAVA_HOME and the path messed with, which could mess with the existing servers. I put the latest install onto the 2000 machine, and it worked, msql started working.
Yes, that is the password out of the box, you do not get an option to set it on the standard install.
Cool, we can log in and see that there is 72 tables, 3 are huge, actually about 5. WORK_ITEM is the worst, but CUBE_SCOPE and CUBE_INSTANCE are terrible.
I cannot drop and of the tables... I get "out of memory"
[POL-2000] can't allocate more memory
I cannot alter table drop column.
[POL-2000] can't allocate more memory
I cannot truncate.
[POL-2000] can't allocate more memory
I can delete rows, but he commit takes about 1 hour.
I have to create "create table" statements from describe results if I want to create the tables again.
Wow, I'm in a pickle.
I installed another BPEL server with the same software and got a copy of the tables empty - this was very helpful. Note that when BPEL started, this is when it seemed to create all of the tables - weird.
I then used a series of commands to move data from valid tables (like SUITCASE_BIN) to the empty copy of orabpel.odb.
| WFROUTINGSLIP | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFROUTINGSLIP > d:\temp\WFROUTINGSLIP.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFROUTINGSLIP < d:\temp\WFROUTINGSLIP.dmp |
| WFNOTIFICATION | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFNOTIFICATION > d:\temp\WFNOTIFICATION.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFNOTIFICATION < d:\temp\WFNOTIFICATION.dmp |
| WFATTRIBUTELABELUSAGE | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFATTRIBUTELABELUSAGE > d:\temp\WFATTRIBUTELABELUSAGE.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFATTRIBUTELABELUSAGE < d:\temp\WFATTRIBUTELABELUSAGE.dmp |
| WFUSERVACATION | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFUSERVACATION > d:\temp\WFUSERVACATION.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFUSERVACATION < d:\temp\WFUSERVACATION.dmp |
| WFUSERTASKVIEW | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFUSERTASKVIEW > d:\temp\WFUSERTASKVIEW.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFUSERTASKVIEW < d:\temp\WFUSERTASKVIEW.dmp |
| WFUSERTASKVIEWGRANT | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFUSERTASKVIEWGRANT > d:\temp\WFUSERTASKVIEWGRANT.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFUSERTASKVIEWGRANT < d:\temp\WFUSERTASKVIEWGRANT.dmp |
| WFUSERPREFERENCE | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFUSERPREFERENCE > d:\temp\WFUSERPREFERENCE.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFUSERPREFERENCE < d:\temp\WFUSERPREFERENCE.dmp |
| VERSION | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb VERSION > d:\temp\VERSION.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb VERSION < d:\temp\VERSION.dmp |
| WFTASKMETADATA | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFTASKMETADATA > d:\temp\WFTASKMETADATA.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFTASKMETADATA < d:\temp\WFTASKMETADATA.dmp |
| WFTASKDISPLAY | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFTASKDISPLAY > d:\temp\WFTASKDISPLAY.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFTASKDISPLAY < d:\temp\WFTASKDISPLAY.dmp |
| WFTASKTIMER | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFTASKTIMER > d:\temp\WFTASKTIMER.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFTASKTIMER < d:\temp\WFTASKTIMER.dmp |
| WFNOTIFICATIONMESSAGES | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WFNOTIFICATIONMESSAGES > d:\temp\WFNOTIFICATIONMESSAGES.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WFNOTIFICATIONMESSAGES < d:\temp\WFNOTIFICATIONMESSAGES.dmp |
| CI_INDEXES | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb CI_INDEXES > d:\temp\CI_INDEXES.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb CI_INDEXES < d:\temp\CI_INDEXES.dmp |
| SCOPE_ACTIVATION | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb SCOPE_ACTIVATION > d:\temp\SCOPE_ACTIVATION.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb SCOPE_ACTIVATION < d:\temp\SCOPE_ACTIVATION.dmp |
| WI_EXCEPTION | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb WI_EXCEPTION > d:\temp\WI_EXCEPTION.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb WI_EXCEPTION < d:\temp\WI_EXCEPTION.dmp |
| XML_DOCUMENT | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb XML_DOCUMENT > d:\temp\XML_DOCUMENT.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb XML_DOCUMENT < d:\temp\XML_DOCUMENT.dmp |
| SYNC_TRAIL | olload -dump D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel.odb SYNC_TRAIL > d:\temp\SYNC_TRAIL.dmp | olload -load D:\product\10.1.3.1\OracleAS_1\bpel\system\database\olite\orabpel_works.odb SYNC_TRAIL < d:\temp\SYNC_TRAIL.dmp |
Above are the dump and load commands for each of the tables, I generated these with a excel formula and pasted them into the command window. this gave me a base copy with my BPEL processes, but not the instance history.
Note that I was unable to delete the history too, as the DB would not process this from the BPEL console.
I tried about 10000 other things before I got this to work. I had the defragdb.exe fail every time, so this would not get me any space back.
The following error has occurred:
DBException: [POL-2403] value too large for column
The following error has occurred:
DBException: [POL-3261] there are too many transactions
Note that it was failing for all of the above, which was also painful. I actually think that changing the odbc.ini to IsolationLevel=Repeatable Read for orabpel fixed the error below:
The following error has occured:
DBException: [POL-3261] there are too many transactions
but it then still failed at the end with:
DBException: [POL-2403] value too large for column
my orabpel database is now 2MB, not 4,191,736MB
No comments:
Post a Comment