Upgrade AE version 21.0.5 hf4 to AE version 24.2 hf 1 (UTF-8 Database migration)

[Cristian Suciu]

Hello,

We are in the process of putting together the plan to upgrade our AE system to version 24.2. 

Currently we are running on AE version 21.0.5 hf4.

Database is Oracle.

Going through the documentation, I find it very confusing when it comes to the Database migration to UTF-8.

For incompatibilities it states, as advisory only, not critical, that Custom SQL queries on AE DB do not work anymore.

What does it define as custom SQL queries? Scripts and VARA objects inside of AE? If those will not work anymore after the database migration to UTF-8, then that is not advisory, that is highly critical.

What I can see, when migrating to UTF-8, in the ini files of the engine, the ODBC and JDBC strings change. 

Do the tables of the DB also change? What tables change or what tables are added? What is the reason for SQL queries to not run? 

Has anyone encountered this while doing their upgrade?

Also, if we migrate the database to UTF-8 before the upgrade, will v21 still work with the new database on UTF-8?

Can our DBA team do this migration on their own or do we have to do it from AE? Apologies if this might seem trivial but at least from my perspective, the documentation is very confusing on this matter. For the upgrade from v12 to v21 we did it on our own but this additional part with UTF-8 is something entirely new to us. 

I find very odd to create additional databases and repoint them for the upgrade. 

Thank you in advance.

Cristian

[Leon Carroll]

Hi Cristian

I'd recommend v24.3 over 24.2.  There's quite a few UI bugs that have been fixed in that version.

If your database is already on an instance running UTF-8 then you can just upgrade.  The custom SQL queries will be if you have written ones that go directly to the database.  All our SQLI jobs were basic and continued working.  

Our DBA's exported the v21 database and imported again, changing the tablespace name where needed.  UC4_DATA and UC4_INDEX need to be accessible if it was originally built with these.  I haven't used the migration package, I ran into the original bugs and didn't want to use the v21 tablespace names for the v24 schema.

[Peter Grundler]

Hi Cristian,

>What does it define as custom SQL queries?

it means every customer-specific SQL query. In very specific cases you need to adopt your query. I am developing SQL queries since v3 and can only remember a few situations where is was necessary to adopt the query.

>Scripts and VARA objects inside of AE?

Any place: queries in a VARA.SQL/VARA.SQLI and/or queries running from an external sqlplus/sqlcmd/psql

BTW: you will find this note for every upgrade, not only from v21 to v24

best regards,
Peter

------------------------------
Automic Certified Professional/Expert & Broadcom Knight

For AUTOMIC trainings please check https://www.qskills.de/qs/workshops/automic/
------------------------------

[Markus Embacher]

Hi Cristian,

we have to highlight the possible incompatibility of custom queries, because tables and fields are subject to change with a major release upgrade. If you have SQLI or SQLI_SEC variables which query data from the AE database then you have to make sure those queries are compatible with the new release. Also index may change. A SQLI query which is performant in V21 is not necessarily also performant in V24. We do not know which queries you have developed and therefore we want to make you aware of a potential pitfall.

Regarding the UTF-8 migration you have several options: 

*) DB native tools (like for example datapump for Oracle)
*) the UTF-8 migration action pack
*) Transport Case (migrate objects only)

UTF-8 is not just a setting that you can change in the database, that is why the data from your AE database needs to be migrated into a new database which has been set up using the AL32UTF8 character set.

Since Automic V21 also supports an Oracle UTF-8 database this gives you the option to perform the UTF-8 upgrade separately and upgrade from V21 to V24 at a later point in time.

Regards, Markus

[Olgun Onur Ozmen]

Marcus Hi,

A question in my head while reading :)

Imagine that our v21 environment (None-UTF8 , characterset example: WE8ISO8859P9 )  is UP in parallel and we build a v24  (UTF8) from scratch. 

• When we move objects from v21 with ucybdbun(Transport Case) and then  ucybdbld(Load) them to v24, does it convert and support UFT-8?
• When we send objects from v21 to v24 with ucydbbcc(Client Copy include with copies statistics, messages, reports, version), does it convert and support UFT-8?
• Does UTF-8 have to be in the AL32UTF8 character set on v24 ? What would happen if we used another UTF8 character set on v24?

Thanks.

------------------------------
Olgun Onur Ozmen
https://www.linkedin.com/in/olgunonurozmen/
------------------------------

[Markus Embacher]

Hi Olgun,

let me answer your questions:

1) The DBUnload/DBLoad Utility can be used to migrate objects from a V21 non UTF-8 to a V24 UTF-8 database

https://docs.automic.com/documentation/webhelp/english/AA/24.3/DOCU/24.3/Automic%20Automation%20Guides/Content/AWA/Admin/admin_UTF8_QA.htm

2) The client copy utility cannot be used to migrate between non-UTF8 and UTF8 databases

https://docs.automic.com/documentation/webhelp/english/AA/24.3/DOCU/24.3/Automic%20Automation%20Guides/Content/_Common/GettingStarted/GS_BeforeStart_UTF8.htm

3) AL32UTF8 is the only supported character set for V24.

https://docs.automic.com/documentation/webhelp/english/AA/24.3/DOCU/24.3/Automic%20Automation%20Guides/Content/Installation_Upgrade/install_upgrade_MigratingAEDB.htm

Regards, Markus

[Olgun Onur Ozmen]

Hi Marcus , 

Thanks for answer.

For the 2nd bullet; then I understood that without using the convert v21 to uft8 or upgrade method, it cannot get data such as statistics, messages, reports, version to v24 in any way. right?

Why we ask this. We have an environment where we want data such as "execution, version etc..." . we moved before this data with client copy v12 to v21. Like we can't move it from v21 to v24. only way thar convert v21 to UTF8 , then client copy . right?

Thanks. 

------------------------------
Olgun Onur Ozmen
https://www.linkedin.com/in/olgunonurozmen/
------------------------------

[Markus Embacher]

Hi Olgun,

if the V21 database is Oracle and UTF-8 then you should be able to copy a client to a V24 database.

Otherwise just use the UTF-8 Migration Action Pack, it will migrate the exeuctions, versions, too.

Regards, Markus

[Cristian Suciu]

Our V21 database is Oracle but not UTF-8. I am still unclear on how the database gets migrated to UTF-8. And when is that supposed to happen? During the upgrade process? Before? I do not plan on using the UTF-8 Migration Action Pack as I can see from several threads on the community that it has issues. I am not handling the database side. That is done by our DBA team and we will be doing the AE upgrade with them. Is it recommended to migrate the database before we upgrade AE? Lets say we migrate the database to UTF-8. Will our AE v21 work with that new database until we upgrade? What changes need to be done to the AE in that case? 
Is it better to migrate the database to UTF-8 at the time of the AE upgrade during the downtime? How much additional downtime will that mean?

[Markus Embacher]

Hi Cristian,

upgrading the database to UTF-8 and upgrading to V24 are 2 separate steps. However migrating the database is mandatory before you upgrade to V24. Since you are running on Oracle you have 2 options:

1) upgrade database to UTF-8 and continue running V21
Make sure your settings follow the instructions in the V21 docu:
https://docs.automic.com/documentation/webhelp/english/AA/21.0/DOCU/21.0.12/Automic%20Automation%20Guides/Content/Installation_Common/PreparationSteps/PrepareAEDB_Oracle.htm#link3

2) upgrade database to UTF-8 and immediately also upgrade to V24 during the same downtime
Make sure your DB Connections strings are updated to UTF-8 accordingly

For the UTF-8 migration itself you have 2 options:

*) our UTF-8 migration Action Pack
*) vendor tools like Oracle datapump

I have used both options at customers successfully.

It is always recommended to test the migration with a clone of the production database beforehand. This will also show how long the migration and the upgrade takes in your environment. It is not possible to give an estimate regarding the downtime in your environment because it depends on many factors.

Regards, Markus

[Ron Gates]

Markus Embacher,

I see you have done both updating to utf-8 on v21 and then at a later time upgrade to v24.. and..

updated to utf-8 at the same time you upgrade to v24... 

Since you have done both approaches... what would be your suggestion for others?

I was leaning towards v21 to utf-8 and at a later date upgrade to v24.. that way its not as much change

happening at the same time.. thoughts? thanks..

[Markus Embacher]

Hi Ron,

I assume you are on Oracle too.

The advantage of first doing the UTF-8 migration and running the upgrade to V24 at a later stage is, that you are minimizing risk by changing only one component at a time. The disadvantage of that method is that you will require a second downtime later for the V24 upgrade. 

Regards, Markus

[Olgun Onur Ozmen]

Hi Marcus ,

one more question came to my mind. let me explain my purpose before I ask my question.

while we were doing v12-v21 migration, there were hosts we ran the agents of both versions same time. we use both v12 and v21 agents on the X host as 8871 and 8872 from the service manager port. Now we want to do the same thing in v21-v24 . I mean running both v21 and v24 agent on same X host as 8871 and 8872 from service manager port. Is there a problem with UTF-8 or any other difference? Can we open 2 separate service managers on the same machine and run v21-v24 agents at the same time?

Thanks.

------------------------------
Olgun Onur Ozmen
https://www.linkedin.com/in/olgunonurozmen/
------------------------------

[Markus Embacher]

Hi Olgun,

in my opinion you can proceed in the same way as for the 12 to 21 upgrade.

Regards, Markus 

[Cristian Suciu]

We raised a case with Broadcom specifically with the UTF-8 migration because our DBA team found out tens of thousands of errors with special characters in our database. They said that they cannot convert the database to UTF-8 until those get resolved. Has anyone encountered this issue? 

Also, Broadcom said that in order to use the UTF-8 migration pack in our system we need to be at least on version 21.0.7. Is this true? We are currently on version 21.0.5 hf4 and they said this time that the migration will not work unless we first go the version 21.0.7.

This causes serious impacts on our overall upgrade plan and we have not been informed in the previous cases we raised of this aspect. 

Is there any documentation confirming this? I could not find anything in their documentation. 

[Markus Embacher]

Hi Cristian,

can you please be more specific on the errors you are mentioning?

Thank you!

Regards, Markus

[Cristian Suciu]

Hi Markus,

The errors are encountered by our DBA team when they are using their internal tool to migrate the database to UTF-8 identifying thousands of characters in some tables on the database that prevent their tool to migrate the database.

These characters appear like this:

In most cases these <?> are actually normal characters like letters or numbers or even paces. Sometimes these characters are letters from the French or German alphabet.

We're currently running the Migration Package from our DEV instance to a test database. So far we have not encountered the same issue but the Initial run of the Workflow is running long so we still have time to encounter them.

[Markus Embacher]

Hi Cristian,

to be honest I don't understand why it is not possible to migrate certain characters. UTF-8 should be able to cover any character using a 1-4 byte representation. It's just, that german umlauts of french characters will use 2-4 bytes in UTF-8 instead of 1 byte in your current database.

Which tool is our DBA team looking at?

Regards, Markus

[Cristian Suciu]

DBA team is using Oracle Database Migration Assistant for Unicode (DMU) as their tool for doing it.

With the Migration Workflow from AE, it looks like it is extremely slow. We have started it 5 hours ago at it only copied 35 GB as of yet. Is this normal? The test database is not on the same server as our DEV database. Does this impact the runtime?

Cristian