topic Re: Oracle Source Database Upgrade in Qlik Replicate

Oracle Source Database Upgrade

JoshMcNatt — Mon, 15 Apr 2024 19:25:52 GMT

We are upgrading an Oracle environment from 18.12 to 19.22.

The plan is to stop Replicate tasks and restart after upgrade completes.

Is there more to this? It seems straightforward but don't want to overlook anything.

Re: Oracle Source Database Upgrade

narendersarva — Mon, 15 Apr 2024 20:29:29 GMT

Hi @JoshMcNatt

1. Before maintenance, please stop the replicate tasks and services

2. upgrade the oracle database

3. Make sure your replicate version is compatible with latest oracle db

4. After maintenance, once the oracle database is up on running, please restart the replicate services and tasks.

Try to test this in lower environments if you can, before doing it in production.

Thanks
Naren

Re: Oracle Source Database Upgrade

Heinvandenheuvel — Mon, 15 Apr 2024 21:25:36 GMT

It should be transparant with a stop & resume before / after source db software upgrade.

Still, monitor your latency while stopping and be ready to advance run ' start by timestamp' if anything odd happens

You might even want to temporarily switch your task(s) error handling to allow duplicates on insert and missing records on updates if there is an issue. Not that I expect any trouble, but you need to know your tools just in case.

Hein.

Re: Oracle Source Database Upgrade

john_wang — Tue, 16 Apr 2024 01:22:19 GMT

Hello @narendersarva , @Heinvandenheuvel , @JoshMcNatt ,

In my opinion RESUME a task after the database upgrading is not a recommended approach. We'd better to startup from a timestamp, or SCN (for Oracle source endpoint).

There are pretty volume of DDL and/or DML changes occurred during the Oracle database upgrade, these changes maybe fully or partially recorded into REDO Log Files, these changes may meet the Qlik Replicate CDC prerequisites or not. Let Qlik Replicate to parse and capture these useless changes may lead potential errors, the most important fact is, Qlik Replicate do not need these changes (during the database upgrade) at all, and certainly it takes time to parse these changes although all these changes are discarded finally.

In summary I'd like to propose the below steps:

1. Production Oracle system down
Stop all user apps, No users apps make new transactions (DDL/DML) to the source Oracle DB

2. Make sure Replicate completes the consuming all the existing changes in REDO LOGs
you can use a flag table/flag rows to make sure all changes in REDO LOGs are consumed by Replicate.

Now the source and target are synchronized (*).

3. Stop Replicate tasks
4. Perform Oracle database upgrade
There are many DDL/DML operations being recorded in REDO LOGs however we will 'ignore' them smartly in below steps

5. After the Oracle database upgrade done, startup the Replicate tasks FROM NOW (or the time after the Oracle database upgrade).
Replicate start processing changes (certainly there should be NO real data changes because
the user apps are not open yet)

6. In a short period the latency is almost zero, the source and target are synchronized (**) again

7. Oracle database is ready, Open to users apps, new transactions will be made and Replicate continue to process the new changes

*, and **, are two important check points where you should make sure the source/target are synchronized.

This is a standard way for many DBs, it's better you can test it in lower env to make sure all the steps are moving forward smoothly.

Hope it helps. Any feedback about the above procedures are welcome.

John.

Re: Oracle Source Database Upgrade

SushilKumar — Tue, 16 Apr 2024 07:45:27 GMT

Hello @JoshMcNatt

Would suggest post DB upgrade to run from the ' start by timestamp. when Database is fully upgraded ' means changing the Compatible and collection of fresh table Statistics.

Regards,

Sushil Kumar