Oracle Lite Part 3 – Our Concise Survival Kit

Introduction

Last August and November my colleague Andre posted some entries right here on Oracle Lite. Where he mainly focussed on installation issues I would like to proceed with using, tuning and working around features. During the last months we have collected all experiences. This post shouldn’t be read as complete work instructions but it’s ‘just’ a partial survival kit for those encountering the same challenges.

Our situation

• We are working with quite a large Lite database; right now it contains 61 tables with overall a little bit less than 3.000.000 records, which leads to an odb-file of somewhere between 300 and 400 Mb.
• Our publication contains two scripts; one for creating a view and one for creating a unique index on the largest table.
• Installation of the Mobile Server database is done on a separate database. We call this our staging area. This way the main production ‘mother’ database is kept clean of all database objects created by publishing an application. Besides synchronisation does not influence performance of other applications using the mother database, and vice versa. The staging area is filled by data transported from the mother database (triggers registering changes, batch job processing changes to staging area).
• Data subsetting is used, as part of the data to be transported to the local database depends on the user of that specific client.
• The local database runs on Windows laptops.
• The database is used by an application written in C#.

Mobile Server

• Be sure you start with the latest versions and patches available for the Mobile Server. Currently we are working with version 10.2.0.2.0. A lot of for us blocking issues are solved in this version compared to earlier ones. Of these issues the following are important:o Republishing didn’t work as it should be. When republishing a publication, user access and data subsetting was lost. This meant that after republishing users would have to be assigned and data subsetting (taking 10-15 minutes for every user) to be entered again. Doing this also meant that after republishing all users would get a full refresh of the database (taking about 20 minutes for every user). With 200 users this would mean a nearly unworkable situation for new releases. As far as we can judge now, all this seems to be fixed…**
  o Bug No. 4093878: An error while synchronising with publications containing scripts, for further details see Metalink
  o Bug No. 4573441: CNS-9019 ERROR DURING COMPRESSION WITH SOME BIG TABLES, for further details see Metalink
  o Synchronisation stops after a while giving error ‘java.lang.OutofMemoryError’. This one was particulary tricky because the memory error was produced during processing of the data on the client, which might lead to the conclusion that the memory problem was local. It wasn’t… Cause and fix are described in Metalink: Doc ID Note: 311356.1) For further details see Metalink.
• Note that you cannot install this against database versions 8 or lower. We are using 10gR2.
• Note also that you should upgrade your Packaging Wizard as well. web.xml files of earlier version won’t deploy on newest version Mobile Server. If you try during publication you get Oracle.xml.parser.v2.XMLParseException.
• To be able to get the right polite.ini-file during client setup win32.inf (for Windows clients that is) can be adjusted. This file can be found on the machine where the Mobile Server is installed (in $ORACLE_HOME\mobile_oc4j\j2ee\mobileserver\applications\
  mobileserver\setup\dmc\common\.
  We did two things (other settings can be found in standard documentation):
  1. Adding entry AUTO_COMMIT_COUNT: you’ll will have to find out what value for this entry works best for you. Setting it too high might produce memory errors during synchronisation; setting it too low badly influences processing speed.
  ….
  <item name=’POLITE.INI’ section=’SYNC’>
  <item name=’TIME_LOG’>1</item>
  <item name=’UPDATE_LOG’>0</item>
  <item name=”AUTO_COMMIT_COUNT”>50000</item>
  <item name=’ENCRYPT_DB’></item>
  …
  2. Changing NLS-settings for local database to Dutch
  …
  <item name=’POLITE.INI’ section=’All Databases’>
  <item name=”DATABASE_ID” replace=’false’>100</item>
  <!–<item name=’NLS_LOCALE’>AMERICAN_AMERICA</item>–>
  <item name=’NLS_LOCALE’>DUTCH_THE NETHERLANDS</item>
  <!–<item lang=’US’ name=’NLS_LOCALE’>AMERICAN_AMERICA</item>–>
  <item lang=’NL’ name=’NLS_LOCALE’>DUTCH_THE NETHERLANDS</item>
  <item lang=’JA’ name=’NLS_LOCALE’>JAPANESE_JAPAN</item>
  <item lang=’ZHS’ name=’NLS_LOCALE’>SIMPLIFIED CHINESE_CHINA</item>
  <item lang=’ZHT’ name=’NLS_LOCALE’>TRADITIONAL CHINESE_TAIWAN</item>
  <item lang=’KO’ name=’NLS_LOCALE’>KOREAN_KOREA</item>
  <item name=”DB_CHAR_ENCODING”>Native</item>
  <item name=”DATA_DIRECTORY”>$APP_DIR$\OLDB40</item>
  <item name=”MESSAGE_FILE”>$APP_DIR$\bin\olite40.msb</item>
  …

** As we have developed and released a production version and working on new releases, we did some investigation on how to deploy new publications without triggering full releases or other heavy tasks. In general:

• Suspend the application currently published (do NOT remove as all access and datasubsetting will be lost)
• Publish application (overwrite existing application)
• Msynch client
• After every first synchronisation after republish, users will be prompted if they want to install a new version of these files even if no changes have been made to these files.

Test cases and results:

• Add a snapshot definition and publish the new JAR file –> after synchronisation an extra table will appear in client database
• Alter table (add column in database) –> after synchronisation the table is NOT adjusted in the client although snapshot definition was select * from table. If republished first and then synchronise the table will be adjusted in the client database
• Remove snapshot definition from publication, republish and synchronise –> table in client still exists.

Client install and initial sync

• Setup files can be downloaded from the Mobile Server. Getting it and running it is straightforward. Know however that during setup at least two files are created in the WINNT-directory. Therefore setup will fail if the user running it doesn’t have write-rights on this directory (normal users in our case don’t)
• One of the files created locally in the WINNT-directory is the polite.ini. For this a ‘template’ is used on the server. If you want changes to the polite.ini, do it on the server (see chapter Mobile Server). That way all clients get the right entries automatically during setup.
• Besides the expected database, defined in your publication, a database file is created in which you can find metadata of your personal database(s). For logging in in that database use the following command: msql system/manager@jdbc:polite:username_conscli in which ‘username’ is the Oracle Lite username used during synchronisation. Select * from all_tables if you want to know what you can find in this database.

Packaging wizard

• Read remark on version packaging wizard at Mobile Server chapter
• Definitions of applications created in the packaging wizard can be found in wtgapp.xml in $orahome\mobile\sdk\wtgsdk\bin. If you want to share with other developers, backup definitions etc.
• You could use the packaging wizard for deploying the application to the mobile server. According to our experience it is better to create a JAR-file and publish it using the Mobile Server administrator.
• Note that for creating the snapshots you will have to define the schema owner of the table the snapshot is based upon. So if you want to deploy to another environment using different schema names you will have to adjust this. You can do this by extract the web.xml from the JAR file and edit it there. Put the altered version back into the JAR-file and deploy.