PolarDB:Important considerations before migrating from Oracle

Problem description

Are sequence values in Oracle automatically synchronized to PolarDB? How can they be synchronized?

Analysis

PolarDB cannot automatically synchronize sequence values. PolarDB uses logical replication that is fully compatible with the PostgreSQL protocol, but sequence values are not currently included in the scope of logical replication in PostgreSQL.

• During schema migration, the database synchronizes the current sequence value from the source database once, for example, to Q1. During subsequent full and incremental data synchronization, the source sequence may continue to increase, for example, to Q2, while the destination sequence remains at Q1. However, table data remains synchronized in real time between the two databases.

• During incremental synchronization before the cutover, conflicts may occur if the source sequence is at Q2 and the destination sequence remains at Q1. For example, if you generate a new value from the destination sequence (Q1) and insert it into a table, the value might already exist in the table. This is because the table data was synchronized from the source database, which can cause a primary key uniqueness constraint violation.

Solutions

• Solution 1: Use Synchronize Sequence

  On the basic information page for incremental writes, click Synchronize Sequence. DTS (Data Transmission Service) then generates and executes the statements that synchronize the sequence values. For more information, see Update sequence values in the destination database.

• Solution 2: Manually synchronize sequence values

  Follow the manual sequence synchronization method described in Migrate self-managed Oracle to PolarDB for PostgreSQL (Compatible with Oracle) to manually synchronize sequence values to PolarDB.

Problem description

Oracle may use GBK or UTF-8 encoding. How do you determine the correct encoding for PolarDB?

Solution

Oracle typically uses GBK or UTF-8 for Chinese character sets. When migrating to PolarDB, we recommend that you choose UTF-8 because it offers better compatibility. This is for the following reasons:

• Avoid the risk of encoding conflicts: We recommend that you do not use GBK as the server-side character set for PolarDB. Some GBK characters overlap with the ASCII code range, which can cause encoding errors during migration.

• Prevent garbled text insertion: Oracle lets you insert invalid GBK-encoded strings when you use the GBK character set. This can result in garbled characters when the data is read. PolarDB does not support this behavior. As a result, data insertion may fail if you use GBK.

Therefore, we recommend that you use UTF-8 encoding during migration to ensure data compatibility and a smooth migration process.

Problem description

In Oracle, table and column names default to uppercase. In PolarDB, they default to lowercase. How does PolarDB handle the naming conflicts caused by this difference?

Solution

Oracle converts identifiers such as Schema, TABLE, and Column to uppercase by default. PolarDB for PostgreSQL (Compatible with Oracle) converts them to lowercase by default. During an Oracle-to-PolarDB for PostgreSQL (Compatible with Oracle) migration, DTS applies the following default strategies based on how the identifiers are used:

• Default uppercase-to-lowercase conversion (without quotes)

  • Applicable scenario: No double quotes are used (case-insensitive). In this case, Oracle defaults to using uppercase names.

  • Handling method: DTS migrates all identifiers to PolarDB for PostgreSQL (Compatible with Oracle) in lowercase. After you switch to PolarDB for PostgreSQL (Compatible with Oracle), you do not need to use double quotes. The database uses lowercase names by default. This allows for a seamless cutover without requiring application changes or special configurations.

• Preserve original case (with quotes)

  • Applicable scenario: Mixed-case identifiers are used in Oracle and are enclosed in double quotes to prevent automatic case conversion.

  • Handling method: DTS preserves the original case of identifiers (you must disable specific mapping). After you switch to PolarDB for PostgreSQL (Compatible with Oracle), you must continue to use double quotes. This allows for a seamless cutover without requiring application changes or special configurations.

• Query retry

  PolarDB includes a built-in case-insensitive query retry feature. If a table name is in uppercase, you can query it using its name in all uppercase or all lowercase letters.

Problem description

Does VARCHAR(20) mean the same thing in Oracle and PolarDB?

Analysis

The default meaning of VARCHAR(20) differs in Oracle and PolarDB.

• In Oracle, the precision of CHAR and VARCHAR types is defined in bytes.

• In PolarDB for PostgreSQL (Compatible with Oracle), the precision of CHAR and VARCHAR types defaults to characters.

• PolarDB for PostgreSQL (Compatible with Oracle) provides the polar_default_char_length_semantics parameter to control whether the precision of CHAR and VARCHAR is interpreted as characters or bytes. By default, this parameter is set to off, which means the precision is character-based. If you set it to on, the precision becomes byte-based.

  Note

  You can modify the polar_default_char_length_semantics parameter in the console. For more information, see Set cluster parameters.

Solutions

• When polar_default_char_length_semantics is set to on, you may encounter the following issue:

  • Business environment: Oracle uses CHAR(10) with GBK encoding. PolarDB for PostgreSQL (Compatible with Oracle) uses CHAR(10) with UTF-8 encoding.

  • Problem: During forward synchronization from Oracle to PolarDB for PostgreSQL (Compatible with Oracle), inserting a five-character Chinese string uses exactly 2 × 5 = 10 bytes in Oracle under GBK encoding, which matches its maximum byte limit. If this limit is exceeded, an error occurs.

  • Solutions:

    Solution 1: Set polar_default_char_length_semantics to off to avoid issues during forward migration.

    Solution 2: Increase the column length in PolarDB for PostgreSQL (Compatible with Oracle).

• When polar_default_char_length_semantics is set to off, you may encounter the following issue:

  • Business environment: Oracle uses CHAR(10) with GBK encoding. PolarDB for PostgreSQL (Compatible with Oracle) uses CHAR(10) with UTF-8 encoding.

  • Problem: In a remote sync from PolarDB for PostgreSQL (Compatible with Oracle) to Oracle, a string that fits in a CHAR(10) column in PolarDB for PostgreSQL (Compatible with Oracle) may exceed the byte limit in Oracle. For example, a 10-character string containing multi-byte characters may become 14 bytes after conversion. This exceeds the 10-byte limit defined in the Oracle CHAR(10) column, causing a data truncation error.

  • Solutions:

    Solution 1: Increase the byte length in the source Oracle table to accommodate longer strings.

    Solution 2: Configure character truncation in DTS using an ETL task. For more information, see Configure ETL in DTS migration or sync tasks.

Problem description

How do you handle tables without primary keys or unique keys in Oracle? Does this affect data validation?

Solution

For tables without primary keys (PK) or unique keys (UK), DTS cannot guarantee data consistency, and duplicate rows may appear in the destination database. Choose one of the following solutions based on your business needs:

• If strict data consistency for tables without a PK or UK is not required, or if your application handles deduplication, you can proceed with the migration.

• If data consistency is required, use Oracle’s ROWID as a hidden primary key (a non-null unique key) in the destination. This column must be deleted before the cutover. In the DTS console, set Add hidden primary key for tables without PK/UK to Yes. For more information, see Migrate self-managed Oracle to PolarDB for PostgreSQL (Compatible with Oracle). After the migration, click the appropriate button to remove the hidden primary key to enable data validation. Note: Data validation is currently not supported for tables without primary keys.

Problem description

Oracle data may contain hidden \0 characters. Will this cause issues during migration?

Solution

PolarDB for PostgreSQL (Compatible with Oracle) cannot store the \0 character, and the internal logic of DTS removes the \0 character.

• For regular fields, this may cause the migrated data to differ from the source data. You must confirm this behavior in advance.

• If a primary key field contains \0 characters, removing them may cause data inconsistency. For example, the primary key values dts\0 and dts become identical after migration. This results in data loss because only one row is retained.

Problem description

In Oracle, empty strings ('') are treated the same as NULL. Is PolarDB compatible with this behavior?

Solution

As an early database system, Oracle treats empty strings as NULL for VARCHAR/VARCHAR2 types, which deviates from the SQL standard. PolarDB supports this behavior through the polar_empty_string_is_null_enable parameter. This feature is enabled by default. If you no longer require this feature, you can disable it in the console. For more information, see Set cluster parameters.

Problem description

How do you set the time zone in PolarDB? What are the effects?

Solution

PolarDB uses Coordinated Universal Time (UTC) by default. All timestamps in the database use UTC as the reference.

For example, querying the current time in China Standard Time (2024-12-03 18:13:34):

SELECT * FROM now();

Returns:

              now               
--------------------------------
 2024-12-03 10:13:34.018557 +00
(1 row)

To switch to China Standard Time (Beijing time), set the timezone parameter to PRC in the console. For more information, see Set cluster parameters. After you set the time zone, run the query again:

SELECT * FROM now();

After you set the time zone, the time is offset by 8 hours, as shown in the following output:

              now               
--------------------------------
 2024-12-03 18:14:34.841027 +08
(1 row)

Problem description

In Oracle, INTEGER is equivalent to NUMBER(38). In PolarDB for PostgreSQL (Compatible with Oracle), INTEGER is a 32-bit integer. How does PolarDB handle this difference?

Solution

In Oracle, statements that use CAST(val AS INTEGER) must be rewritten as CAST(val AS NUMBER(38)) to avoid an integer overflow error.

For example, in PolarDB for PostgreSQL (Compatible with Oracle), the following query will cause an out of integer error, whereas Oracle can execute it normally.

SELECT CAST(99999999999 AS INTEGER) FROM dual;

• Oracle returns:

     CAST(99999999999ASINTEGER)
  _____________________________
                    99999999999

• PolarDB for PostgreSQL (Compatible with Oracle) returns:

  ERROR:  integer out of range

You can rewrite the statement in PolarDB for PostgreSQL (Compatible with Oracle) as follows:

SELECT CAST(99999999999 AS NUMBER(38)) FROM dual;

PolarDB for PostgreSQL (Compatible with Oracle) returns the following:

   numeric   
-------------
 99999999999
(1 row)