Oracle Source Database Upgrade

[JoshMcNatt]

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.

[narendersarva]

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

[Heinvandenheuvel]

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.

[john_wang]

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.

[SushilKumar]

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