Will autobcp work for Oracle databases that have different character sets?

Products

• Autosys Workload Automation

Issue/Introduction

When using the autobcpORA.pl or autobcpORAdp.pl scripts to sync two AutoSys databases in Oracle, will it work if the two databases have different character sets?

Environment

Release : 12.0

Component : CA Workload Automation AE (AutoSys)

Resolution

Using the autobcp scripts to sync two Oracle databases that have differing character sets is not something that has been tested internally by Broadcom. QA testing is performed with Oracle databases using the default character set, which as of Oracle 12 is AL32UTF8. That is the character set recommended for best practice with AutoSys and is also what Oracle recommends for General Purpose/Transaction Processing database types.

There are two autobcp perl scripts for Oracle that can be used for the database sync. Both use Oracle utilities to perform the data migration...

autobcpORA.pl - Uses the older Oracle imp/exp commands
autobcpORAdp.pl (recommended) - Uses Oracle Data Pump

It is really a question of whether these Oracle utilities support character set conversion. Per Oracle documentation, both utilities can convert character sets during data migration. However, there is a risk of data corruption in some cases as detailed below...

For imp/exp...

The Export and Import utilities can convert character sets from the original database character set to the new database character set. However, character set conversions can sometimes cause data loss or data corruption. For example, if you are migrating from character set A to character set B, then the destination character set B should be a superset of character set A. The destination character, B, is a superset if it contains all the characters defined in character set A. Characters that are not available in character set B are converted to replacement characters, which are often specified as ? or ¿ or as a character that is related to the unavailable character. For example, ä (a with an umlaut) can be replaced by a. Replacement characters are defined by the target character set.

Note:
There is an exception to the requirement that the destination character set B should be a superset of character set A. If your data contains no characters that are in character set A but are not in character set B, then the destination character set does not need to be a superset of character set A to avoid data loss or data corruption.

For Data Pump...

1.16.3 Multibyte Character Sets and Export and Import
During an Oracle Data Pump export and import, the character set conversion depends on the importing Oracle Database character set.

During character set conversion, any characters in the export file that have no equivalent in the import database character set are replaced with a default character. The import database character set defines the default character.

If the import system has to use replacement characters while converting DDL, then a warning message is displayed and the system attempts to load the converted DDL.

If the import system has to use replacement characters while converting user data, then the default behavior is to load the converted data. However, it is possible to instruct the import system to reject rows of user data that were converted using replacement characters. See the Import DATA OPTIONS parameter for details.

To guarantee 100% conversion, the import database character set must be a superset (or equivalent) of the character set used to generate the export file.

Caution:When the database character set of the export system differs from that of the import system, the import system displays informational messages at the start of the job that show what the database character set is.
When the import database character set is not a superset of the character set used to generate the export file, the import system displays a warning that possible data loss may occur due to character set conversions.

It is highly recommended to make sure the data in the target database is backed up prior to attempting an autobcp with mixed character sets.