Any impact on existing SQL, PL/SQL scripts and data model due to Oracle DB upgrade from 12cto 19c?

user_13503962 0
Viewed 1035

I wanted to discuss my understanding for impact on existing SQL, PL/SQL scripts and data model due to DB upgrade from 12cto 19c: As per my own research and understanding-

  1. There is no identified change which may lead to failure of my existing SQL and PL/SQL during ETL load and reporting queries.
  2. There is no identified data type change which may impact existing data model. Only extended data type feature is DE supported in Oracle
    Database 19c.We are not using extended data type in existing data model.
  3. The database layout of key features between 12.2 and 19c has not been
    changed. Hence, your setup for 12.2 and 19c should not result into any issue

Please let me know any further comment or input.

oracle12c oracle19c oracle database
2 Answers

Hence, your setup for 12.2 and 19c should not result into any issue

Exactly.

Officially, there are New Features which describe what changed. Unoficially, I found a nice article written by Dave Partridge who said (and I slightly formatted it):

It would be easy to be concerned that 18c and 19c represent major upgrades. However, this is not the case. 18c and 19c are both 12.2 releases of the Oracle database.

  • Oracle Database 18c is Oracle 12c Release 2 (12.2.0.2). (...)
  • Oracle 19c is essentially Oracle 12c Release 2 (12.2.0.3).

Pretty much clear, I'd say. So, from my point of view, no worries, upgrade. Wouldn't harm if you do it first in your test environment.

user_90979060

In theory you may not have any incompatilbity or issue. But experience show that there always some risks involved in upgrading an Oracle database: mainly changes in SQL execution plans because the Cost Based Optimizer (CBO) is always improved: generally it's OK but you might have some exception (ie an execution plan that has changed and become slow); this is why performance testing should be a mandatory step in your upgrade test. Never do an upgrade directly in production without testing it on a physical copy of the database: doing so would be a possible recipe for disaster !

In addition to Oracle documentation and support.oracle.com, I suggest to always check Mike Dietrich blog which is one of the best - if not the best - Oracle upgrade knowledge base: https://mikedietrichde.com/

If there were no issues during Oracle upgrade and patching, Mike Dietrich blog would be very different from what it is ...

user_107268500
Related