MobiLink synchronization and timestamp-based downloads with an Oracle Real Application Cluster   Rows in the consolidated database running on an Oracle RAC may be missed if the clocks of the Oracle cluster nodes differ by more than the time elapsed between the MobiLink server fetching the next last download timestamp and fetching the rows to be downloaded. This problem is unlikely on a RAC system with synchronized node clocks, but the likelihood increases with larger node clock differences. A workaround is to create either a modify_next_last_download_timestamp or modify_last_download_timestamp script to subtract the maximum node clock difference.

At least since version 10i, Oracle has recommended using Network Time Protocol (NTP) to synchronize the clocks on all nodes in a cluster. NTP typically runs by default on Unix and Linux. With cluster nodes properly configured to use NTP, their clocks should all be within 200 microseconds to 10 milliseconds (depending on the proximity of the NTP server). Since Windows Server 2003, the Windows Time Service implements the NTP version 3 protocol which runs by default. Also, as of version 11gR2, Oracle Clusterware includes the Oracle Cluster Time Synchronization Service (CTSS) to either monitor clock synchronization or, if neither NTP or Windows Time Service is running, to actively maintain clock synchronization. However, CTSS and Windows Time Service are less accurate than NTP.

To avoid missing rows when Oracle RAC node clocks differ by up to one second more than the time between fetching the next_last_download_timestamp and the rows to be downloaded, the MobiLink server subtracts one second from the next_last_download_timestamp fetched from the consolidated database if the following are true:

For Oracle RAC node clocks that may differ by greater amounts, you can avoid the problem by defining a generate_next_last_download_timestamp, modify_next_last_download_timestamp or modify_last_download_timestamp script to compensate for the maximum node clock difference.

Data type mapping   The data types of columns must map correctly between your consolidated and remote database. For details, see Oracle data mapping.

XMLTYPE data type   Use of the Oracle XMLTYPE data type with SQL Anywhere or UltraLite requires special care. For details, see Oracle XMLTYPE data type.

CHAR columns   In Oracle, CHAR data types are fixed length and blank-padded to the full length of the string. In MobiLink remote databases (SQL Anywhere or UltraLite), CHAR is the same as VARCHAR: values are not blank-padded to a fixed width. It is strongly recommended that you use VARCHAR in the consolidated database rather than CHAR. If you must use CHAR, the mlsrv16 -b command line option can be used to remove trailing blanks from strings during synchronization. This option is important for string comparisons used to detect conflicts.

See -b mlsrv16 option.

Timestamps   The MobiLink server uses gv$transaction to generate a timestamp for the remote database to be used in the next synchronization, so the MobiLink server login ID must have a SELECT privilege on gv$transaction. Oracle does not allow you to grant access to gv$transaction directly; you must instead grant SELECT privilege on the underlying gv_$transaction view. See Privileges.

Stored procedures   If you are using stored procedures to return result sets or accept VARRAY parameters, you must select the Procedure returns results or uses VARRAY parameters option for the SQL Anywhere 16 - Oracle ODBC driver. Also, Sybase Central requires procedures to return results to use central administration of remote databases, so this option needs to be selected when using central administration.

See SQL Anywhere 16 - Oracle ODBC driver.

Session-wide variables   You can store session-wide information in variables within Oracle packages. Oracle packages allow variables to be created, modified and destroyed; these variables may last as long as the Oracle package is current.

Autoincrement methods   To maintain primary key uniqueness, you can use an Oracle sequence to generate a list of keys similar to that of a SQL Anywhere autoincrement field. The CustDB sample database provides coding examples, which can be found in Samples\MobiLink\CustDB\custora.sql. Unlike autoincrement, however, you must explicitly reference the sequence. SQL Anywhere autoincrement inserts a column value automatically if the column is not referenced in an INSERT statement.

Oracle does not support empty strings   In Oracle, an empty string is treated as null. In SQL Anywhere and UltraLite, empty strings have a different meaning from null. Therefore, you should avoid using empty strings in your client databases when you have an Oracle consolidated database.

When all XML documents are less than or equal to 32KB long, the upload and download scripts can be written as follows   upload_insert

declare v_pk integer; v_c1 clob; x_c1 xmltype; begin v_pk := {ml r.pk}; v_c1 := {ml r.c1}; x_c1 := XMLTYPE.createXML( v_c1 ); insert into test values( v_pk, x_c1 ); end;

download_cursor

select pk, XMLSERIALIZE( content c1 ) from test

This upload_insert script works well when the XML data length is less than or equal to 32 KB. However, if the XML data length is greater than 32 KB, the Oracle server may issue an error.

If there are any XML documents greater than 32 KB long, the upload XML data needs to be uploaded in a global temporary table   The upload_insert script uploads the XML documents into a global temporary table in the Oracle consolidated database. The global temporary table is defined as:

create global temporary table tmp_test (pk int, c1 CLOB)

Then the upload_insert script can written as follows:

insert into tmp_test values( {ml r.pk}, {ml r.c1} )

The end_upload_rows script retrieves the XML documents from the global temporary table, converts them to XML documents, and then stores the XML data into the test table. Following is the end_upload_rows script:

insert into test (pk, c1) (select pk, XMLTYPE.createXML(c1) from tmp_test