Key considerations for migrating an Oracle database to a PolarDB cluster

Sequence values are not included in the logical replication scope of the PostgreSQL protocol. PolarDB captures a one-time snapshot of sequence values at the start of schema migration. As new data is written to Oracle after the snapshot, Oracle's sequence values advance while PolarDB's remain at the snapshot value. The table data in the destination PolarDB cluster is synchronized with the source Oracle database in real time, but sequence values are not. Using an outdated sequence value to insert a record into PolarDB causes a duplicate key error.

Use one of the following solutions:

• Use DTS to synchronize sequences: On the Increment Write page of the DTS instance, click Synchronize Sequence. DTS generates a sequence synchronization statement to keep sequence values current. For details, see Update the values of sequences in the destination database.

• Manually synchronize sequence values: Query the current maximum sequence value in the Oracle database, then set that value as the starting point for the corresponding sequence in PolarDB before switching traffic. For details, see Migrate data from a self-managed Oracle database to a PolarDB for PostgreSQL (Compatible with Oracle) cluster.

Use the UTF-8 character set for the PolarDB cluster. GBK has characters that overlap with the ASCII encoding range, which can cause encoding errors during migration. Additionally, Oracle allows inserting invalid GBK-encoded data, but PolarDB rejects such data — records with invalid GBK encoding may be refused on write.

Oracle converts all unquoted identifiers (schema names, table names, and column names) to uppercase by default. PolarDB for PostgreSQL (Compatible with Oracle) converts unquoted identifiers to lowercase by default. Data Transmission Service (DTS) handles this difference automatically:

Scenario	DTS behavior	Post-migration query
Unquoted names (all uppercase in Oracle)	DTS converts to lowercase	Query using lowercase or uppercase names — both work
Quoted names with mixed case (double-quoted in Oracle)	DTS preserves original case	Continue querying with double-quoted names

Oracle measures CHAR and VARCHAR lengths in bytes. PolarDB for PostgreSQL (Compatible with Oracle) measures them in characters by default. The polar_default_char_length_semantics parameter controls this behavior:

• Default (OFF): lengths are measured in characters

• ON: lengths are measured in bytes (matches Oracle behavior)

Configure this parameter in the PolarDB console. For details, see Configure cluster parameters.

Forward synchronization issue (parameter set to ON)

A CHAR(10) column in Oracle uses GBK encoding. The string "test test test" occupies exactly 10 bytes in Oracle (2 bytes per character x 5). In PolarDB with UTF-8 encoding, the same string occupies 15 bytes, which exceeds the 10-byte column limit. An error is reported.

Solutions:

• Set polar_default_char_length_semantics to OFF so PolarDB measures lengths in characters.

• Increase the column length in PolarDB to accommodate the larger UTF-8 representation.

Reverse synchronization issue (parameter set to OFF)

A CHAR(10) column in PolarDB stores the string "测试" (2 Chinese characters + 8 single-byte characters = 10 characters). When synchronized back to Oracle's GBK-encoded column, the same string occupies 14 bytes (2 x 3 + 8), which exceeds Oracle's CHAR(10) byte limit. A data truncation error is reported.

Solutions:

• Increase the column length in Oracle.

• Configure an extract, transform, and load (ETL) task in DTS to truncate excess characters. For details, see Configure ETL in a data migration or synchronization task.

Tables without a primary key or unique key cannot guarantee data consistency during migration, which may result in duplicate entries in the destination cluster.

Choose an approach based on your consistency requirements:

• Consistency not required: Proceed with migration and deduplicate afterwards.

• Consistency required: Use the Oracle ROWID column as a hidden primary key in the destination cluster. In the DTS console, set Add a hidden primary key for a table without a primary key/a table without a unique key to Yes. For details, see Migrate data from a self-managed Oracle database to a PolarDB for PostgreSQL (Compatible with Oracle) cluster. After the migration task completes, delete the hidden primary key before running data validation.

PolarDB for PostgreSQL (Compatible with Oracle) does not support \0 characters. DTS removes them during migration, which can cause two types of issues:

• In regular columns: Removed \0 characters create discrepancies between source and destination data.

• In primary key columns: Records with keys dts\0 and dts both become dts after migration, causing a data conflict.

Audit your data for \0 characters before migrating, especially in primary key columns.

Oracle treats empty strings ('') as NULL values, which differs from the SQL standard. PolarDB supports this Oracle behavior through the polar_empty_string_is_null_enable parameter, which is ON by default.

The default time zone for a PolarDB cluster is UTC (Coordinated Universal Time). All stored and retrieved timestamps are based on UTC.

For example, querying the current time at 18:13:34 on December 3, 2024 (UTC+8) returns:

SELECT * FROM now();

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

To use UTC+8, set the timezone parameter to PRC in the PolarDB console. After the change, the same query returns:

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

For details, see Configure cluster parameters.

In Oracle, INTEGER is an alias for NUMBER(38), which supports integers up to 38 digits. In PolarDB for PostgreSQL (Compatible with Oracle), INTEGER is a 32-bit integer.

Querying a value that exceeds the 32-bit limit produces different results:

SELECT CAST(99999999999 AS INTEGER) FROM dual;

Database	Result
Oracle	99999999999
PolarDB for PostgreSQL (Compatible with Oracle)	ERROR: integer out of range

Replace CAST(val AS INTEGER) with CAST(val AS NUMBER(38)) in queries that handle large integers:

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

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