Migrating Oracle Databases to UTF-8

As a system and/or database administrator, you need to check if your Oracle database is already UTF-8 encoded or not. If it is not, you need to migrate it to a new UTF-8 one before upgrading your system to v24.

You also have the option to migrate your Oracle database to a UTF-8 encoded one without having to upgrade your system to v24.

Important! Before migrating your database, make sure that you are familiar with all considerations and prerequisites for the migration. For more information, see Migrating AE Databases to UTF-8.

This page includes the following:

Prerequisites to Migrate Oracle Databases Using the Migration Action Pack

As a system/database administration, you need to make sure that your system meets the requirements needed to use the UTF-8 DB Migration Action Pack.

In Client 0, set the following keys of the UC_SYSTEM_SETTINGS variable:
- SQLVAR_INTERNAL key to YES
- SQLVAR_MAX_ROWS key to 1000 lines or higher
- VAR_SECURITY_LEVEL to 3 (allow all)
Create a new, empty UTF-8 enabled AE database. Oracle databases use the AL32UTF8 character set.
You must have an Agent for Database Variables installed
You must have an Agent for Database Jobs and Events (SQL Agent) installed and able to reach the source and destination databases.
You must set both Connection (CONN) objects for the source and destination databases respectively before executing the Workflow.
You must have a database link configured from the destination to the source database. You can do it manually or have the Action Pack create it on the fly.
Make sure that the database user of the destination database has the privilege to create DB links (GRANT CREATE PUBLIC DATABASE LINK TO <destination DB user>) before executing the Action Pack.

As before any migration or upgrade, make sure you also create a backup of your existing database.

For more information, see:

Configuring the Database Link for Oracle Databases

Oracle databases require a database link, a schema object in one database that enables you to access objects in another database. For example:

CREATE DATABASE LINK uc4source CONNECT TO <SOURCE_DATABASE_USERNAME> IDENTIFIED BY '<SOURCE_DATABASE_PASSWORD>' USING '<SOURCE_DATABASE_HOST>:<SOURCE_DATABASE_PORT>/<SOURCE_DATABASE_NAME>;

For more information, see Create a Database Link in the Oracle documentation.

You might need an internal exception to allow a database link:

sqlplus sys as sysdb
aalter session set container = <pdb>
grant create database link to <dbuser>

When you use the Migration Action Pack, you can either create the database link manually (N), or have the Migration Workflow create it for you on the fly (Y).

Manually (N - No), where:
- user = user name of the source database
- password = password of the source database
- hostname = host name of the source database
- port = port of source database
- db_name = source database name
Execute the following commands on the destination instance with a connection to the destination database:

CREATE DATABASE LINK uc4source CONNECT TO <SOURCE_DATABASE_USERNAME> IDENTIFIED BY '<SOURCE_DATABASE_PASSWORD>' USING '<SOURCE_DATABASE_HOST>:<SOURCE_DATABASE_PORT>/<SOURCE_DATABASE_NAME>;
On the fly (Y - Yes), in which case the Action Pack creates a database link. You must enter the source database password for this purpose.

Important! Make sure that you have the privilege to create DB links (GRANT CREATE PUBLIC DATABASE LINK TO <destination DB user>) before you execute the Action Pack.

Migrating the Oracle Database with the Migration Action Pack

Before starting, make sure that you meet all the prerequisites listed above and that you have read all the considerations and recommendations, see:

Follow the steps below to use the Migration Action Pack to migrate your AE database to UTF-8.

Download the Migration Action Pack from https://marketplace.automic.com/. You can either search for Package.UTF8.DB.Migration, or simply type UTF8 to find it.

It contains the Workflow and Connection (CONN) objects that you need for the migration.
In a Client other than Client 0, go to the Administration perspective. You cannot execute the Workflow included in the Action Pack in Client 0.
Click the Packs tab at the bottom of the Navigation pane on the left, see Working with Packs and Plug-ins.
Click Install from File and select your downloaded package. Once selected, click Install.
Once your Pack has been installed successfully, right-click it and select Jump to Source to open it.
Open the SOURCE - MOVE.UC4.DB.TO.UTF8 - SETUP.OBJECTS folder, which contains two Connection (CONN) objects.
Define the Connection (CONN) objects with the required connection data for the source and the destination databases:

In the Connection sections:
- Database Type
  
  The type of your source/destination database.
- Server:Port
  
  Name of the source/destination computer on which the database is available and the port number.
- Database Name
  
  Name of the source/destination database that should be used.
- Use Oracle Service
  Check this box if you want to use Oracle Service.
Test the database connections using the Test button in the Connection Test section of each Connection object.
Execute the PCK.AUTOMIC_UTF-8-MIG.START Workflow. It guides you through a documented migration process, where you can input all the relevant parameters using a prompt with various fields.

Important!
- The Workflow recognizes if the source database was loaded with partitioning (ILM) and automatically selects to proceed with or without ILM. You have to consider that and the database you use when defining the PromptSets.
- If possible, make sure there is no ILM activity (no drop partition operation) during the database migration process with ILM.
- Upon executing the Workflow, it checks the encoding of the source (not UTF-8) and destination (UTF-8) AE databases. If the checks are as expected, the migration proceeds; otherwise, it aborts.
- You must execute the Workflow running each Run Mode at least once.

Define the SQL Agent for the source and destination database.
Choose if the DB link should be created by the Migration Action Pack on the fly or not.
Define the Run Mode of the Workflow. Each run mode uses data from the various database areas with different requirements. Therefore, you must run each mode at least once.
- 0 - Initial (default)
- 1 - Refresh
- 2 - Final
For more information, see First Migration Part - System Online.
Enter the email address of the person or group that should receive the SQL queries required to complete the migration process during system downtime after the Workflow has finished.

Submit your entries.
Once done and depending on the defined Run Mode, you can do the following:
- Initial: Once the Workflow finishes successfully, you can start Workflow in Refresh mode.
- Refresh: Once the Workflow finishes successfully, you can start, you can start the Workflow again in Refresh mode until you are ready to start the offline migration part, or start it again in Final mode.
- Final: Once the Workflow finishes and generates the required SQL queries, it sends an email to the defined address, provided that SMTP is correctly configured and works. The SQL statements are also stored in the PCK.AUTOMIC_UTF-8.EMAIL.CONTENT.FINAL.STEPS VARA object that is provided in the SOURCE - MOVE.NON-UTF8.DB.TO.UTF8 - EMAIL folder in case sending the email is not possible for any reason.
Important!
- If the email cannot be sent successfully in this final step, and the corresponding task fails with an error status, you can ignore this task and resume the Workflow.
- No rollback function is required because the Workflow does not change anything in your source database. It is copied to the destination database and a temp table (uc_migration) that controls the data for the migration process is created. If something goes wrong, you can easily delete the destination database and the temp table and restart the migration process. If you want to perform a test run of the Action Pack, you can use a copy of your existing AE database with actual life data.
Shut down your system.
Manually execute the SQL queries generated in the Final mode.

After executing the queries, the structure and data of the source AE database are completely copied to the destination AE database, which is a clone of the source AE database, but UTF-8 is configured, including the data that was converted to UTF-8. The source and destination databases have the same version.

Next Steps

Now you can decide one of these optiones:

Use the new UTF-8 AE database and upgrade to v24

Now that the database has been migrated, you have a UTF-8 AE database that has not been upgraded to v24 yet. You can now proceed with the system upgrade.

During the upgrade, make sure that the connection strings in all INI files are adapted so that they point to the new UTF-8 AE database and have the encoding set correctly to UTF-8. For more information, see Upgrading an Automation Engine System Manually.
Use the new UTF-8 AE database in your v21 Automic Automation system and upgrade your system later.

This options allows you to decouple the AE database migration to UTF-8 from the v24 system upgrade for more flexibility and less time pressure. This means that, after the database has been migrated, you connect it to your existing v21 Automic Automation.

Since you have not upgraded your system yet, both database and system are still on v21; only the data got converted to UTF-8. There is no need to adapt the connection string encoding in the INI file as the v21 system expects the same encoding as in the past. The data is processed/handled in v21 Automic Automation context in legacy encoding (such as latin9) but stored as UTF-8 in the AE database.