This post is the first one of a set that I will write about Oracle Lite. I have been working with Oracle Lite and I am impressed. Not by the level of documentation, but by the functionality of the package. Oracle Lite gives you the ability to create data snapshots on laptops, PDA’s, mobile phones etc. The synchronisation mechanism (provided by the tool msync) will synchronise data bi-directionally between the snapshot and the main database server. Client applications that use the Oracle lite snapshot can also be distributed en synchronised by Oracle Lite’s synchronisation functionallity. This makes deployment of applications very simple. I have written and distributed a simple .Net client written in C++ this way. My application uses ODBC to access the database. JDBC is also provided. Unfortunately it’s not possible to access the database by using OCI which is my favorite while programming in C/C++.
Oracle Lite applications can be defined with the wtgpack tool. With this tool you can define the snapshots by entering select statements. Queries can be restricted by using bind variables. The actual values of these variables can be entered by the administrator on a user of group level. The wtgpack tool can be used to deploy the application on the Oracle Lite middle tier. Clients use this tier for their synchronisation or to install the initial Lite database software. Deployment with wtgpack can end up with a 401 of 500 error. Tool at the tips below when you have one of these errors because the description of the error will not help you at all. The wtgpack tool will be described in a future post.
,
Installation of Oracle Lite is easy. Just install the server part by using the supplied installer. Here are a number of apparently simple and easy tips that will make life easier when you start:
- Make sure to install patch set 1 after the installation of Oracle Lite 10.0.0.0.0. This way the deployment of an application defined by wtgpack will work much better and reliably.
- The tables that are synchronised MUST have a primary key. Oracle Lite can also work with virtual primary key (mandatory unique keys?). But my experience tells me to use real primary keys. wtgpack will error with a 401 or 500 error during deployment of the application when one or more primary keys are missing.
- Oracle Lite will create three tables per synchronised table in the schema that owns the tables. This messes up your database. It’s better to create an extra user holding synonyms to the synchronised tables. Synchronisation should be done via this extra user on the synonyms.
- The msync tool will not work on very large tables because it will only commit after all the records are copied to the Oracle Lite database. It’s best to modify polite.ini (it’s in the windows directory) before you perform the first msync. Add AUTO_COMMIT_COUNT=500 in the [SYNC] section of the file. This makes sure the during synchronisation a commit is performed after each 500 rows.
- wtgpack will display the tables that are used in the snapshot in a (for me) random order. Deployment of a snapshot will fail when there is a syntax error in the query; again a 401 or 500 error during deployment. Count the number of snapshots created in the log of OC4J. This way you can easily tell which table is failing because the snapshots are created in the order they are displayed in the wtgpack application.
![New in Oracle 11g: PL/SQL Function Result Cache]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "New in Oracle 11g: PL/SQL Function Result Cache")
Do you provide a blog feed subscription for this blog so
I can get it via email?
The just nature of oracle lite and database replication has many locks and at times blocking locks. Have you identified what tables/user sessions are causing the blocks? Is it Mobileadmin or something else? Do you have an application that is directly connected to the database server that is also performing insert/update/deletes?
We are using WebToGo version 2.0.3.4.0 and we are having DB lock issues, we suspec that DB locks are happening during data synchronization, has anybody experienced this? is it a version issue that is resolved in a newer version?
I will appreciate your thoughts.
LO
Ensure you have a Primary Key on all your base tables. I have not read any bugs on creating replication for large dataset. There are some known bugs and patches for replicating large sets, but not on creating them.
Hi,
I guess u r master in Oracle Lite.
One thing i wanted to add is that when u try to pubilsh through packaging waizard it gives Internal Server error when i have a large data.So what i do i create a jar file through packaging wizard and the import the application through mobile administrator.
Also the samples that they have provided on palm using codewarrior never run.
They have not clearly mentioned which library file to include.
If have developed any small application on Palm please help me………
Thanx
Devendra Sutar,
Palm OS developer.
Oracle Lite Admin.
Andre – We apreciate your comments on Database Lite and welcome your suggestion for product imporvements or please contact me if you have additinoal unanswered questions about the product. We look forward to additional installments. – Phil
Oracle Lite actually creates 5 tables per snapshot, 3 in the application schema, 2 in the MOBILEADMIN schema. The application schema has the Error Queue, Log Tables, and Version Tables. The MOBILEADMIN Schema has the In and Out Queues.
This is the first I have heard of not being able to sync large tables. Our application uses 9i Lite and we are able to sync tables with 500,000+ records.
WTGPACK gives the In and Out queue a sequenced number when you publish an application. To get around this, use the Java API provided with the application. This is for more advanced users but it will save time in identifying what tables the In and Out queues are associated with.
I totally agree with you enthusiasm for the application. Once you get everything working, it’s a joy, but to get to that point can be very frustrating. One thing that Oracle did wrong with this product is the gave it the name “Lite.” There is nothing light weight about this application. The sync server and the MGP process are complete hogs of the resources. You want to have a knowledgable DBA to help out with the fine tuning.
Oracle Lite actually create 5 tables for each base table you publish. 3 tables in the application schema CEQ$, CLG$ , CVR$ . 2 Tables in the MOBILEADMIN schema, CFM$ . The tables in the application schema are your Version Table, Log Table, and Error Queue. While the tables in the MOBILEADMIN schema are your IN and OUT queues.
I will have to look at the documentation, but I don’t believe Oracle mentions anything about large recordsets. In fact, we have an application that has large volume users that get about 1GB of data, and most of that data resides in one of 2 tables. Both tables having about 500,000+ records. I would also think that you can set the POLITE.INI on the server side somehow. Maybe using the WSH utility or the Java API.
One thing to consided if you do have large tables to sync, is to use an Java API (Consolidator in 9i Lite) to Partition your Out Queue.
If you do not like the random numbers that are associated with the snapshots and publications, I would recommend using the Java API. More advanced users will prefer the use of the Java API over the WTGPACK. I usually use WTGPACK to create small applications for testing/debugging purposes.