Oracle Database 19c | Rollback Patch

Recently I updated one of my posts that discussed about applying patches to Oracle 19c instance, that has minimum one PDB. This time we will discuss about how to rollback a patch. For the exercise I will be rolling back patch number 34110685 “Windows Database Bundle Patch : 19.16.0.0.220719 (34110685)”

Please note, to install patch 34110685 you must have opatch 12.2.0.1.30 or higher. The same applies for rolling back also.

User opatch to find out the latest patch details.

D:\Oracle19c\OPatch>opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.32
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\Oracle19c
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.32
OUI version       : 12.2.0.7.0
Log file location : D:\Oracle19c\cfgtoollogs\opatch\opatch2022-09-15_10-59-45AM_1.log

Lsinventory Output file location : D:\Oracle19c\cfgtoollogs\opatch\lsinv\lsinventory2022-09-15_10-59-45AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: ora.abc.com
ARU platform id: 233
ARU platform description:: Microsoft Windows (64-bit AMD)


Installed Top-level Products (1):

Oracle Database 19c                                                  19.0.0.0.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  34110685     : applied on Sun Aug 14 12:02:50 AST 2022
Unique Patch ID:  24797704
Patch description:  "Windows Database Bundle Patch : 19.16.0.0.220719 (34110685)"
   Created on 28 Jul 2022, 09:34:43 hrs UTC
   Bugs fixed:
     34208548, 34264633, 34291960, 34293849, 34311758, 30151886, 31649223
     32012137, 32079097, 32223654, 33115620, 33182177, 33324055, 33360476
     33390342, 33423383, 33510227, 33899902, 33907346, 33957025, 33964258
     34034279, 34088985, 34088989, 34110342, 34132318, 34147169, 34162335,.......



--------------------------------------------------------------------------------

OPatch succeeded.

Once we have the latest patch details, shutdown all running Oracle instances and stop Windows services for Oracle (listener & database services)

D:\Oracle19c\OPatch>opatch rollback -id 34110685
Oracle Interim Patch Installer version 12.2.0.1.32
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\Oracle19c
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.32
OUI version       : 12.2.0.7.0
Log file location : D:\Oracle19c\cfgtoollogs\opatch\opatch2022-09-15_11-02-48AM_1.log


Patches will be rolled back in the following order:
   34110685
The following patch(es) will be rolled back: 34110685

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'D:\Oracle19c')


Is the local system ready for patching? [y|n]
y
User Responded with: Y

Rolling back patch 34110685...

RollbackSession rolling back interim patch '34110685' from OH 'D:\Oracle19c'

Patching component oracle.has.common.cvu, 19.0.0.0.0...

Patching component oracle.has.rsf, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.has.common, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.odbc.ic, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.ntoledb.odp_net_2, 19.0.0.0.0...

Patching component oracle.has.db, 19.0.0.0.0...

Patching component oracle.ntoramts, 19.0.0.0.0...

Patching component oracle.marvel, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.blaslapack, 19.0.0.0.0...

Patching component oracle.tfa.db, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.install.common, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.ntoledbolap, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.ntoledb, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.install.deinstalltool, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.rsf, 19.0.0.0.0...

Patching component oracle.clrintg.ode_net_2, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.aspnet_2, 19.0.0.0.0...

Patching component oracle.usm.deconfig, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.ons.ic, 19.0.0.0.0...

Patching component oracle.mgw.common, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.oracler.server, 19.0.0.0.0...

Patching component oracle.rdbms.plsql, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms.olap, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.xdk.xquery, 19.0.0.0.0...

Patching component oracle.jdk, 1.8.0.201.0...
RollbackSession removing interim patch '34110685' from inventory
Inactive sub-set patch [33575656] has become active due to the rolling back of a super-set patch [34110685].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: D:\Oracle19c\cfgtoollogs\opatch\opatch2022-09-15_11-02-48AM_1.log

OPatch succeeded.

Restart all Windows services for Oracle & startup the database instance.

D:\Oracle19c\OPatch>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 15 11:07:07 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4294965408 bytes
Fixed Size                  9275552 bytes
Variable Size            1207959552 bytes
Database Buffers         3070230528 bytes
Redo Buffers                7499776 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

Now we should apply the data patch to the database.

D:\Oracle19c\OPatch>datapatch.bat -verbose
SQL Patching tool version 19.14.0.0.0 Production on Thu Sep 15 11:09:47 2022
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: D:\Oracle19c\cfgtoollogs\sqlpatch\sqlpatch_1148_2022_09_15_11_09_47\sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.14.0.0.0 Release_Update 211229195225: Installed
  PDB CDB$ROOT:
    Applied 19.16.0.0.0 Release_Update 220717095735 successfully on 14-AUG-22 12.38.13.440000 PM
  PDB SCT:
    Applied 19.16.0.0.0 Release_Update 220717095735 successfully on 14-AUG-22 12.38.16.841000 PM
  PDB PDB$SEED:
    Applied 19.16.0.0.0 Release_Update 220717095735 successfully on 14-AUG-22 12.38.15.127000 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED SCT
    No interim patches need to be rolled back
    Patch 34110685 (Windows Database Bundle Patch : 19.16.0.0.220719 (34110685)):
      Rollback from 19.16.0.0.0 Release_Update 220717095735 to 19.14.0.0.0 Release_Update 211229195225
    No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...done
Patch 34110685 rollback (pdb CDB$ROOT): SUCCESS
  logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\34110685\24797704/34110685_rollback_SCTCD_CDBROOT_2022Sep15_11_10_47.log (no errors)
Patch 34110685 rollback (pdb PDB$SEED): SUCCESS
  logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\34110685\24797704/34110685_rollback_SCTCD_PDBSEED_2022Sep15_11_13_25.log (no errors)
Patch 34110685 rollback (pdb SCT): SUCCESS
  logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\34110685\24797704/34110685_rollback_SCTCD_SCT_2022Sep15_11_13_26.log (no errors)
SQL Patching tool complete on Thu Sep 15 11:16:25 2022

Now we have to recompile database components.

D:\Oracle19c\OPatch>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 15 11:24:39 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2022-09-15 11:24:49

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2022-09-15 11:24:50

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> alter session set "_oracle_script"=TRUE;

Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb$seed OPEN READ WRITE;

Pluggable database altered.

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2022-09-15 11:25:56

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2022-09-15 11:25:58

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb$seed OPEN READ ONLY;

Pluggable database altered.

SQL> alter session set "_oracle_script"=FALSE;

Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb$seed OPEN READ ONLY;

Pluggable database altered.

SQL> alter session set container="SCT";

Session altered.

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2022-09-15 11:27:44

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2022-09-15 11:27:46

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  3

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> shutdown immediate
Pluggable Database closed.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

D:\Oracle19c\OPatch>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 15 11:28:27 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

That’s all folks. My PDB SCT has 3 objects in error and they are custom objects created by us & not anymore required for the application.

I hope this gives a fair about how to rollback an applied patch from Oracle database. As my database is pretty small with limited number of objects, I didn’t face any issues. This may not be the case when you are dealing with a huge PRODUCTION database. Insure as many backups are taken prior you rollback a PRODUCTION instance.

Oracle Forms Developer 10g crashes when opening modules

Oracle developer Forms version 10g is still used. Used by Oracle for their E-Business Suite & they will continue using this obsolete, unstable version until the day EBS is discontinued. So, let us see how to make the best out of what is available.

One of the major nuisances a developer (definitely) will come across while using this obsolete version of Oracle Developer on Windows 7 or later versions of Microsoft OS is, opening a module that has underlying database level objects changed (procedures/functions/packages), this IDE crashes without generating any error logs. Simple as it is. This could completely cripple the developer, especially when rapid developments miss to document the changes.

Is it possible to open FMB files without being connected to database? it is another BIG NO NO. Developer 10G will crash immediately incase if you try to open an FMB file that has database objects referred. So what is the next possible solution?

Well there is a solution that is dirty, having to install Oracle Forms Developer 12c (12.2.1.4). Just install the suite as standalone without Weblogic. This version is fault tolerant and will not try to validate the database level object calls & crash. By this, you can verify, compile and see what objects were changed, non-existing etcetera.

Oh yes, make sure you have copied your source FMB files to a new folder prior opening them with Forms Developer 12c!. Always remember, Oracle EBS R12 only compiles FMB files created using Oracle forms 10g. You could lose your original files forever if you forgot to do that.

Here is an excellent article explains how to install standalone Oracle Forms 12c.

https://oracle-base.com/articles/12c/standalone-forms-builder-12c-installation-on-windows-1221

NFO: Skipping line: WSE-00008: Unsupported Oracle home type

Well, Oracle hardly fixes many errors for Windows OS. They “successfully” inherit bugs in latest versions, mostly because not many businesses are hosting Oracle products on Windows? A poll conducted by https://oracle-base.com reveals just 8% Windows OS share for hosting Oracle products! Interested? read details here

We recently migrated to 19c (19.14) & one of our 3rd party solutions needed to create a Linked Server connection to this instance from MS SQL database. Although 11g client could successfully connect to 19c, I decided to go with the 19c client, that looked less challenging to configure. Installed the client, and tried to install Oracle Services for Microsoft Transaction Server component as additional feature and nightmare started. Software was getting installed, however failing when configuring it.

As the GUI didn’t produce much details about what went wrong, I decided to try configuring it from command line after referring Oracle Services for Microsoft Transaction Server installation documents on Oracle site.

C:\Windows\system32>C:\app\oracle\product\19.0.0\client_1\oui\bin\runConfig.bat ORACLE_HOME=C:\app\oracle\product\19.0.0\client_1 MODE=perform ACTION=configure RERUN=true

C:\Windows\system32>REM Copyright (c) 2003, 2018, Oracle and/or its affiliates.

C:\Windows\system32>REM All rights reserved.

C:\Windows\system32>SET PATH=C:\app\oracle\product\19.0.0\client_1\bin;C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\Oracle\product\11.2.0\dbhome_1\bin;C:\oracle\product\11.2.0\client_1\bin;C:\orant\bin;C:\Oracle\19c\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\OpenSSH\;C:\orant\jdk\bin;C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\;C:\Program Files\Azure Data Studio\bin;C:\Users\rajesh.RTHAMPI\AppData\Local\Microsoft\WindowsApps;C:\app\oracle\product\19.0.0\client_1\oui\lib

C:\Windows\system32>"C:\app\oracle\product\19.0.0\client_1\jdk\jre\bin\java" -classpath "C:\app\oracle\product\19.0.0\client_1\oui\jlib\OraInstaller.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\OraInstallerNet.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\xmlparserv2.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\srvm.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\emCfg.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\share.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\ojmisc.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\xml.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\OraCheckPoint.jar;C:\app\oracle\product\19.0.0\client_1\oui\jlib\OraPrereq.jar" oracle.sysman.oii.oiic.OiicRunConfig C:\app\oracle\product\19.0.0\client_1\oui ORACLE_HOME=C:\app\oracle\product\19.0.0\client_1 MODE=perform ACTION=configure RERUN=true

perform - mode is starting for action: configure

Apr 15, 2022 7:20:46 PM oracle.install.config.common.MTSInternalPlugIn invoke
INFO: MTSInternalPlugIn: ... adding </ouiinternal>
Apr 15, 2022 7:20:46 PM oracle.install.commons.util.FileUtils changeUnixFilePermissions
INFO: Not changing the permissions of file \configTools_2022-04-15_07-20-46PM.log, because current platform is not unix.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn invoke
INFO: Executing MTS
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn invoke
INFO: Command C:\Windows\system32\cmd.exe /c call C:\app\oracle\product\19.0.0\client_1\bin\oramtsctl.exe -new -internal
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: ... GenericInternalPlugIn.handleProcess() entered.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: ... GenericInternalPlugIn: getting configAssistantParmas.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: ... GenericInternalPlugIn: checking secretArguments.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: No arguments to pass to stdin
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: ... GenericInternalPlugIn: starting read loop.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line: Service is being created.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: End of argument passing to stdin
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line: Service creation failed.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess

Then came the message that said what went wrong

INFO: Skipping line: WSE-00008: Unsupported Oracle home type.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line:
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line:
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line:
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line: Cause : This error should be treated as an internal error.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line:
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line:
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line:
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line: Action : Contact Oracle Support Services.
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line:
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line:
Apr 15, 2022 7:20:46 PM oracle.install.driver.oui.config.GenericInternalPlugIn handleProcess
INFO: Skipping line: OUI internal: error occurred on localhost

perform - mode finished for action: configure

You can see the log file: C:\app\oracle\product\19.0.0\client_1\cfgtoollogs\oui\configActions2022-04-15_07-20-46PM.log

I found a solution immediately there after on stackoverflow. This answer discusses about the registry values that could cause the installation issues for Oracle client version 12.x & quickly I tried the same, and the configuration completed successfully.

So here is how to address the problem. Please make sure that you will take a backup of Windows Registry prior attempting to manually modify the values. A corrupt registry might force you to re-install Windows.

As we could see here, ORACLE_HOME_TYPE after a default install doesn’t have a value, failing MTS in my case. As I was trying to configure the Oracle client, expected value was “2” and for the Database, the value should be “1”

Modify key & exit registry editor.

Try to configure MTS once again from the command prompt. This time configurations must complete successfully!

Should you know your database technology?

Techrepublic | https://www.techrepublic.com/article/how-to-create-an-sql-database-in-microsoft-azure/

In the past I had couple of opportunities to work with firms those specialize in Small to Medium scale business solutions built on top of Microsoft SQL Database technology. Much of such opportunities turned in to 100% futile as the vendors firmly believed the “Database” was a customer’s responsibility & insuring performance was solely at customer’s dispersal. I will get back to the subject after a brief introduction to how things go all the way wrong with these partners.

Opportunity#1

We were pretty happy with the cost effective ERP Suite & just prior signing the contract, question about who will setup and configure the Database(technology) came up. The solution provider was quick to respond with “Database maintenance is your part, so as it is mentioned in the draft contract”. Agreeing to the maintenance part, that is insuring the availability and backing up the data, we repeated the question who will “setup” the database and parameterize it for best performance, as their ERP Suite was replacing something really huge & users will expect the same slick and quick responses from the new system.

The vendor was so confused & told us few things like below:

  1. There is nothing to configure
  2. Microsoft SQL Database is not like Oracle, you don’t need to worry about anything. Just install it and forget it. Server takes care of it (heeheehee)
  3. Just take a backup
  4. Nothing to configure about memory. You got 32GB memory right? more than enough. If needed we can always add more memory
  5. Yes, you can even install the database on your Windows XP machine and we think the latest Windows server is 2013 (Not exaggerated, we have an email with Windows Server 2013 mentioned in the supported OS list)

With each passing moment, they started getting agitated and the Project Manager from their end started asking questions like “We are a solution developer, why do you expect us to know “everything” about database?”. We had to tell them they don’t know anything about the database technology on top of what they design and develop their entire solution.

They lost the opportunity, which grew to approximately 1 million USD project gradually, using Oracle technologies (the worst blunder they made was “All you need a single license for the database as our application connects to database as same user for all modules.”)

Opportunity#2

A bit different scenario. This time the vendor was smarter. In order to impress us, they have designed the database to look so large, that it sized more than 25-30GB(more than 70% dedicated to Transaction Logs) before the solution was even launched. This company had a pretty wrong idea about tables and views. To “read faster” all their tables had all the columns those were required by their solution. In addition much of their views were having more than 200+ columns and many dozen inline queries to fetch additional data, making a simple query painfully slow.

On top of it, they implemented an always open URL (Obviously, keeping it open is “Your” (customer) responsibility) for processing some data.

Every other time, whenever we pointed out the difficulties with fetching data from their tables and views for custom reporting, we were given answers those should not be quoted here.

We terminated the contract after completing one (painful) year.

So, the question is, Should you know your database technology?

Much of the Prime time database technologies are pre-configured to a certain extend & a developer as a single individual may not be too interested to ponder deep into the available provisions to set it up the instance for optimized performances, mainly because everything works from the development machine, for the “development” perspectives.

Well, this is not the case when a proper business solution with commercial intend is developed based on a particular database technology. The software vendor have to understand the database technology they are targeting for their business solution to such an extended that, their product could benefit the customer in terms of continued availability and deliver insure optimized performances.

So, how to size and parameterize the database for optimized performances? This requires an experienced DBA/team to assist you with these fine tuning and parameterization activities. While Oracle database fine tuning and parameterization possibilities far more stretched than Microsoft SQL Server, you have the ease of using a GUI tool for the later, that helps you to setup many optimization parameters without the help of a DBA. MySQL also comes with a beautiful GUI manager for much of the configurations. However, just having some wonderful tools alone do not help you to achieve the maximum performance! One must know what and how to configure the database and OS specific parameters to get the maximum through output from the database technology. Yes, you need an experienced DBA or a team of DBAs to achieve this & I believe, for a successful software, a software developer must invest adequate efforts and funds in this sector with highest priority, may much before they start designing their solution!

An example case, we had our Oracle database 10g instance set with 2GB memory for SGA and 1GB for PGA for almost 4-5 years by our part-time DBA, while the server had another 40GB memory to spare. Our ERP (Oracle EBS R12) lagged, stuttered for this entire period just because he never attempted to fine tune the instance once after the implementers handed it over to us. His last excuse was “So, everything was working, why we have to trouble something that is working fine”. We terminated his contract. Our new partner setup the instance with highest possible SGA/PGA combinations and made a dozen parameterizations based on the OS specifications. It was followed by internal team introducing HugePages on Linux environment & we never looked back. Well, it took us some time to get there though to “find a right partner to work with”.

Majority of the places, wherever I were asked to investigate the poor performances (especially Microsoft SQL Server), noticed that the database was installed using the defaults and the only one maintenance activity for the instance is limited to full database backup daily. None of the maintenance possibilities like automating the indexing or statistics gatherings were implemented, making the solutions to lag and painful over a period of time.

While vendors like those I have introduced in the very beginning of the article would easily escape their responsibility by stating “database maintenance is your responsibility”, as a developer and as “NOT A Certified DBA” I will argue that.

I remember another interesting scenario when the vendor was asked which edition of the database should be installed for their solution. Initially struggling with release numbers and later making statements like “Enterprise edition will have more features” and failing to list few features never landed this vendor in a very bad light. They completely ignored the fact that we were a business that was already using software solutions like Oracle Applications & never thought of answering questions related to database.

A thorough study must be done by the solution developer to identify the best edition of database (Enterprise, Standard or Express) that is suitable for the customer. This is especially important when a solution with generic nature is adapted by businesses of different sizes. A software solution developer must understand that, every business tries to limit the investment for software implementations and unnecessary licensing costs by suggesting a wrong edition of database technology could force the customer to reject their product altogether and discard the project.

I’ve started my developing career with dBASE3 (Without knowing much about it) & last 20 years, worked with different database technologies & as a core application developer, strongly believe that, the developer MUST know many things about the database technology, based on which the targeted solution is being developed. As an individual I might be pardoned, however as a software vendor, I might lose wonderful opportunities just by not knowing enough about the database technology that’s the core of my business application!

So what you think, should I know my database technology?

This article was initially posted with my LinkedIn account.

WordPress Errors due to PHP incompatibility

Our business website uses WordPress. We have a theme bought from the market place and Elementor for page designs. The whole setup costed us something below 200$ & we are happy with the way the site delivers all our needs (not forgetting dozens of plugins that we use for basic features)

Recently we noticed “Elementor” throwing few errors while the pages were getting loaded. Errors were like “Deprecated: _register_skins is deprecated since version“. A quick Google search landed us on this discussion & rolled back Elementor to a previous version as suggested by one of the users and expected no further issues.

We were wrong. We started getting serious issues related to Jetpack, a plugin that is provided by “Automatic”, WordPress’s own repository. Our pages started showing some weird messages while loading and we were completely denied WordPress Admin page access. Our hosting company renamed the plugin folder & we were able to log on and disable Jetpack plugin, which was not recommended by many.

Much of the Jetpack related errors were like get_name(), get_title(),get_description(),get_long_description(),get_features(),get_pricing_for_ui(),get_manage_url() should not be abstract in & Warning: Cannot modify header information…

Jetpack error(s)

Then I remembered about a PHP related warning I have noticed during the last admin panel visit. WordPress was recommending PHP upgrade as they were in the process of stop supporting PHP 5.x versions. So I approached our hosting & asked them to upgrade the available PHP options for us. Once after they installed the latest available versions, we switched to PHP 7.4 (7.3 is deprecated already) from 5.6 . Proceeded with enabling Jetpack and upgraded Elementor to latest.

Everything started working as expected. So if you started facing Elementor or Jetpack related errors all of a sudden, before breaking your head anywhere else, check the version of PHP & give a try by upgrading it to one of the not deprecated versions.

Linux HugePages for Oracle

Few weeks back one DBA told me there is no need to setup HugePages as we are not using 40+GB SGA & we had it enabled from the initial days of 11g migration from 10g (10.2.0.3). Well, here is how you can setup HugePages for your Oracle database instance.

A brief explanation that might help you to decide whether you should enable HugePages or not is quoted from Tim Hall’s article below.

“For large SGA sizes, HugePages can give substantial benefits in virtual memory management. Without HugePages, the memory of the SGA is divided into 4K pages, which have to be managed by the Linux kernel. Using HugePages, the page size is increased to 2MB (configurable to 1G if supported by the hardware), thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory. In addition to these changes, the memory associated with HugePages can not be swapped out, which forces the SGA to stay memory resident. The savings in memory and the effort of page management make HugePages pretty much mandatory for Oracle 11g systems running on x86-64 architectures.

Recently I had a short Twitter exchange with Tim Hall about the subject & He mentioned that he always enables it.

Please refer the article from below link, it is by Tim Hall, a geek in this field and the owner of https://oracle-base.com

https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64

Exercises explained in this article is applicable to 19c also (verified as working). Please make sure that your version of Oracle database supports these settings before attempting. Please do not copy this to PRODUCTION environments without enough testing.

Oracle Database 19c | Convert non CDB Database to PDB

We’ve our Oracle EBS instance hosted from Linux environment. However I post much for the Windows OS as I see there are not many articles targeting Oracle on Windows. I think for better view counts, I should consider posting the articles for both OS.

In my last post about restoring 1g RMAN online backup to 19c non CDB database, I said my ultimate goal was to convert the upgraded database from non CDB to PDB, that is plugged on to an existing CDB. Let us see how to achieve this goal this time.

Before start, there are few mandatory checks you must do to insure a successful migration from Non CDB to PDB. I will list few of them those came as hurdles for me.

  1. Character Set. Make sure that your CDB and the Non CDB database have the same character set. There are few possible conversions. Please check 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1) for more details. My CDB was using Arabic character set and the upgraded non CDB database was using a noncompatible set. So I dropped the CDB and created a new CDB without PDB(s). Dropping a CDB will not be always possible because other PDBs are attached to it.
  2. Available storage space. This could be a real concern incase if you are planning to create a PDB, cross verify & then delete the non CDB database. You will need approximately the same size of your non CDB datafiles size storage available to do this activity. My database was less than 5 GB in size, hence it never occurred to me. If your database has a bigger size, plan accordingly.

Let’s do it now.

From and elevated command prompt (Windows Terminal is not yet matured enough to handle such “heavy” tasks) source the non CDB. Shutdown and startup mount exclusive.

C:\Windows\system32>set ORACLE_SID=KAZEMA
C:\Windows\system32>sqlplus / as sysdba
SQL> shu immediate
SQL> startup mount exclusive
SQL> alter database open read only;

Create manifest file for the non CDB now.

SQL> exec dbms_pdb.describe (pdb_descr_file=>'D:\Oracle19c\kazema_manifest_file.xml');
SQL> shu immediate

Source the CDB now.

C:\Windows\system32>set ORACLE_SID=KAZEMACDB
C:\Windows\system32>sqlplus / as sysdba
SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT

Now, we need to see whether non CDB meets the compatibility requirements. There could be multiple elements those you may have to fix before, the non CDB is ready for a successful migration as PDB. You will have to refer PDB_PLUG_IN_VIOLATIONS view for these details. Go ahead, execute the below code block.

SQL>   SET SERVEROUTPUT ON;
SQL>    DECLARE
       Compatible CONSTANT VARCHAR2(3) :=CASE  DBMS_PDB.CHECK_PLUG_COMPATIBILITY
       (pdb_descr_file => 'D:\Oracle19c\kazema_manifest_file.xml')
       WHEN TRUE THEN 'YES'
       ELSE 'NO'
       END;
       BEGIN
          DBMS_OUTPUT.PUT_LINE(compatible);
       END;
     /
YES

My case I didn’t have incompatibilities blocking possible migration. Your case could be different. If the output is “NO”, run the following query (use SQL Developer for better view)

select * from PDB_PLUG_IN_VIOLATIONS where name='<your non CDB name>';

Fix each and every other ERROR & you can safely ignore the “WARNINGS” for now.

Proceed with creating a new PDB from the manifest file that we have created in the 1st step.

SQL> CREATE PLUGGABLE DATABASE KAZEMA USING 'D:\Oracle19c\kazema_manifest_file.xml' COPY FILE_NAME_CONVERT=('D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE','D:\ORACLE19C\DB\ORADATA\KAZEMA');

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 KAZEMA                         MOUNTED

Once the PDB has been created, confirm the same & proceed with balance activities. Run “noncdb_to_pdb.sql”

SQL> alter session set container=KAZEMA;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Based on the resource available, this might take a while for the script to complete execution. Once completed you can try to open the PDB. Check for Errors and Warnings in the PDB_PLUG_IN_VIOLATIONS view. If there are new ERRORS & WARNINGS fix them.

A typical scenario could list you many things like this (reference image only)

After fix attempts, I am still left with one Warning about sys lock issues. This is mainly due to the password file format 12.2. I hope to find a solution soon for it. Status column states whether the Error/Warning was taken care off.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
KAZEMA
READ WRITE


1 row selected.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 KAZEMA                         READ WRITE NO
SQL>

Now you have a non CDB and PDB with the same SID. You can proceed with dropping the non CDB database after cross verifying the concerns.

Reference documents:

Foot Note: If you are plugging a database that was hosted in another server/computer after converting, make sure to verify the “local_listener” parameter for the PDB exist.

D:\Oracle19c>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 10 11:18:33 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_KAZEMACDB
SQL> alter session set container="KAZEMA";

Session altered.

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_KAZEMACDB
SQL>

If the local listener is not reachable, PDB level services will fail to register with listener and the applications those depend on such services will fail to establish connections. This applies mostly to Oracle E-Business Suite environments and could be mapped to sophisticated applications that use specific service names to establish connections to the database.

Restore 11g RMAN online backup to 19c

Is it possible to restore previous version RMAN database backup to a later version? Depending upon the supported upgrade paths, the lowest version I managed to restore to 19c (19.14) was 11g R2 (11.2.0.4)

Upgrade Path for Oracle database 19c

Please note, my final goal is to convert this NON CDB database to PDB and attach to an existing CDB.

What you need?

  • Intact RMAN online backup
  • Enough memory and storage space available on your computer.
  • Time and patience
  • Oracle support access, for many of the documents mentioned later in the article.

Scenario (fictitious): I’m given a full RMAN backup without any other details. I don’t know the source server details other than that it was windows & the version of the database release was 11.2.0.4.

Step#1

Using DBCA created a new non CDB database with the target database name, eg “KAZEMA”. After shutting down database, stopped listener keeping Windows Service for Oracle database running. Physically deleted all data files including control, redo logs.

(You can create an empty instance using ORADIM. That means ORADIM will create Windows services necessary for Oracle. You will have to create the necessary folders. Not just that, you need create a parameter file (initDATABASENAME.ora) to start the instance. Refer the below example)

D:\Oracle19c>oradim -new -sid KAZEMA -syspwd Oracle123 -startmode auto
Enter password for Oracle service user:

OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character.
Instance created.

Sample parameter file (initKAZEMA.ora)

kazema.__data_transfer_cache_size=0
kazema.__db_cache_size=1509949440
kazema.__inmemory_ext_roarea=0
kazema.__inmemory_ext_rwarea=0
kazema.__java_pool_size=0
kazema.__large_pool_size=33554432
kazema.__oracle_base='D:\Oracle19c\db'#ORACLE_BASE set from environment
kazema.__pga_aggregate_target=1073741824
kazema.__sga_target=2147483648
kazema.__shared_io_pool_size=117440512
kazema.__shared_pool_size=469762048
kazema.__streams_pool_size=0
kazema.__unified_pga_pool_size=0
*.audit_file_dest='D:\Oracle19c\db\admin\KAZEMA\adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='D:\Oracle19c\db\oradata\KAZEMA\CONTROLFILE\control01.ctl','D:\Oracle19c\db\oradata\KAZEMA\CONTROLFILE\control02.ctl'
*.db_block_size=8192
*.db_name='KAZEMA'
*.diagnostic_dest='D:\Oracle19c\db'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=KAZEMAXDB)'
*.local_listener='LISTENER_KAZEMA'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1024m
*.processes=640
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2048m
*.undo_tablespace='UNDOTBS1'
*.max_string_size='STANDARD'

Considering the fact that my target database name is “KAZEMA”, I have modified a copy of parameter file that I had. One of the most important factor you should pay attention here is to create all the physical paths mentioned in the parameter file. Example D:\Oracle19c\db\admin\KAZEMA\adump, D:\Oracle19c\db\oradata\KAZEMA\CONTROLFILE. Failing to find these physical paths might cause errors or your database will not start at all.

D:\Oracle19c\db\admin\KAZEMA has few more folders, like shown in the image. Please create them manually before starting the database.

Add *.max_string_size=’STANDARD’ to the parameter file, necessary for the upgrade. You will have to change this parameter once again after upgrade completes. We will discuss about it later in the article.

SQL> startup nomount (pfile=D:\Oracle19c\database\initKAZEMA.ora # if instance was created using ORADIM) 
ORACLE instance started.

Total System Global Area 2147482424 bytes
Fixed Size                  9030456 bytes
Variable Size             503316480 bytes
Database Buffers         1627389952 bytes
Redo Buffers                7745536 bytes

From another elevated command prompt started RMAN & restored control file from backup that I had in “D:\BAHRMAN” folder.

D:\BAHRMAN>set ORACLE_SID=KAZEMA

D:\BAHRMAN>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 23 09:52:14 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to target database: KAZEMA (DBID=964058362, not open)

RMAN>restore controlfile from 'D:\BAHRMAN\CTL_C-964058362-20210927-02';
RMAN>alter database mount;
RMAN>report schema; #This will populate the data file details

Shutdown the database now. Restart the database in “nomount” mode using parameter file.

Once database shutdown and restarted, exit RMAN and connect again. We’ll duplicate database using the RMAN backup now using “NOOPEN” switch that is supported from versions 12.

D:\BAHRMAN>rman auxiliary /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 23 09:56:42 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: KAZEMA (not mounted)

run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to "KAZEMA" NOOPEN backup location 'D:\BAHRMAN' nofilenamecheck
db_file_name_convert=('D:\ORACLE\ORADATA\KAZEMA\','D:\Oracle19c\db\oradata\KAZEMA\DATAFILE\')
LOGFILE
GROUP 1 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo01a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo01b.log'
) SIZE 1200M ,
GROUP 2 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo02a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo02b.log'
) SIZE 1200M ,
GROUP 3 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo03a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo03b.log'
) SIZE 1200M ,
GROUP 4 (
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo04a.log',
'D:\Oracle19c\db\oradata\KAZEMA\LOGFILE\redo04b.log'
) SIZE 1200M ;
# 19c & later, no need to exclusively release channels. If you restoring to other versions, make sure to release the channels.
# https://www.thegeekdiary.com/oracle-database-19c-rman-06012-channel-d1-not-allocated/
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

allocated channel: c1
channel c1: SID=498 device type=DISK

allocated channel: c2
channel c2: SID=619 device type=DISK

allocated channel: c3
channel c3: SID=742 device type=DISK

allocated channel: c4
channel c4: SID=862 device type=DISK

Starting Duplicate Db at 23-MAR-22
searching for database ID
found backup of database ID 964058362

contents of Memory Script:
.....Removed........
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=1100081253 file name=D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE\LC01.DBF
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=1100081253 file name=D:\ORACLE19C\DB\ORADATA\KAZEMA\DATAFILE\BAHAR01.DBF
Leaving database unopened, as requested
Finished Duplicate Db at 23-MAR-22
#You will see the below errors for 19c and later while trying to release the channels exclusively. Remove the release channel instructions from the RMAN scripts to avoid seeing these messages.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of release command at 03/23/2022 10:07:32
RMAN-06012: channel: c1 not allocated

RMAN>

Ignore errors by the end of RMAN process, if they are related to releasing channels. If there are errors of other nature, you must resolve them. Now we will open the database resetting logs and ready to upgrade.

D:\Oracle19c>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 23 10:09:51 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> alter database open resetlogs upgrade;

Database altered.

As we have restored a RMAN backup, it is obvious that the source database had archive logs enabled. Let us disable archive logging from our new database. Shutdown the database and open it in mount mode.

SQL> startup mount pfile=D:\Oracle19c\database\initKAZEMA.ora
ORACLE instance started.

Total System Global Area 2147482424 bytes
Fixed Size                  9030456 bytes
Variable Size             503316480 bytes
Database Buffers         1627389952 bytes
Redo Buffers                7745536 bytes

SQL> alter database noarchivelog;
SQL> shutdown immediate;

SQL> startup upgrade pfile=D:\Oracle19c\database\initKAZEMA.ora

Now switch to %ORACLE_HOME%\perl\bin folder and call catctl.pl to upgrade the database.

D:\Oracle19c\perl\bin>perl D:\Oracle19c\rdbms\admin\catctl.pl -n 4 -l D:\Oracle19c\ D:\Oracle19c\rdbms\admin\catupgrd.sql

This is a long process for many reasons. First of all, no prerequisites were executed and recommended actions were performed against the source 11G database for 19c upgrade. This will cause upgrade process to fix prerequisites during upgrade & brace for few errors. Without MAX_STRING_SIZE set as ‘STANDARD’ for 11g, upgrade will fail. I have started the database instance using parameter file ONLY for this reason. I’m not sure whether RMAN restore crated SPFILE copies this value. So, I didn’t take chances.

Argument list for [D:\Oracle19c\rdbms\admin\catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = D:\Oracle19c\
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 4
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.14.0.0.0DBRU_WINDOWS.X64_211224.3]


D:\Oracle19c\rdbms\admin\orahome.exe = [D:\Oracle19c]
D:\Oracle19c\bin\orabasehome.exe = [D:\Oracle19c]
catctlGetOraBaseLogDir = [D:\Oracle19c]

Analyzing file D:\Oracle19c\rdbms\admin\catupgrd.sql

Log file directory = [D:\Oracle19c]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [D:\Oracle19c\catupgrd_catcon_8056.lst]

catcon::set_log_file_base_path: catcon: See [D:\Oracle19c\catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [D:\Oracle19c\catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 8
Database Name         = KAZEMA
DataBase Version      = 11.2.0.4.0
Parallel SQL Process Count            = 4
Components in [KAZEMA]
    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
DataBase Version      = 11.2.0.4.0

------------------------------------------------------
Phases [0-107]         Start Time:[2022_03_23 10:18:27]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [KAZEMA] Files:1    Time: 273s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [KAZEMA] Files:5    Time: 31s
Restart  Phase #:2    [KAZEMA] Files:1    Time: 1s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [KAZEMA] Files:19   Time: 11s
Restart  Phase #:4    [KAZEMA] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [KAZEMA] Files:7    Time: 14s
*****************   Catproc Start   ****************
Serial   Phase #:6    [KAZEMA] Files:1    Time: 11s
*****************   Catproc Types   ****************
Serial   Phase #:7    [KAZEMA] Files:2    Time: 6s
Restart  Phase #:8    [KAZEMA] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [KAZEMA] Files:70   Time: 11s
Restart  Phase #:10   [KAZEMA] Files:1    Time: 1s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [KAZEMA] Files:1    Time: 42s
Restart  Phase #:12   [KAZEMA] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [KAZEMA] Files:95   Time: 4s
Restart  Phase #:14   [KAZEMA] Files:1    Time: 0s
Parallel Phase #:15   [KAZEMA] Files:122  Time: 6s
Restart  Phase #:16   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:17   [KAZEMA] Files:25   Time: 2s
Restart  Phase #:18   [KAZEMA] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [KAZEMA] Files:32   Time: 8s
Restart  Phase #:20   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:21   [KAZEMA] Files:3    Time: 8s
Restart  Phase #:22   [KAZEMA] Files:1    Time: 0s
Parallel Phase #:23   [KAZEMA] Files:25   Time: 157s
Restart  Phase #:24   [KAZEMA] Files:1    Time: 0s
Parallel Phase #:25   [KAZEMA] Files:12   Time: 96s
Restart  Phase #:26   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:27   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:28   [KAZEMA] Files:4    Time: 2s
Serial   Phase #:29   [KAZEMA] Files:1    Time: 0s
Restart  Phase #:30   [KAZEMA] Files:1    Time: 1s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [KAZEMA] Files:1    Time: 0s
Restart  Phase #:32   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:34   [KAZEMA] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [KAZEMA] Files:297  Time: 19s
Serial   Phase #:36   [KAZEMA] Files:1    Time: 0s
Restart  Phase #:37   [KAZEMA] Files:1    Time: 1s
Serial   Phase #:38   [KAZEMA] Files:10   Time: 3s
Restart  Phase #:39   [KAZEMA] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [KAZEMA] Files:3    Time: 40s
Restart  Phase #:41   [KAZEMA] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [KAZEMA] Files:13   Time: 91s
Restart  Phase #:43   [KAZEMA] Files:1    Time: 0s
Parallel Phase #:44   [KAZEMA] Files:11   Time: 7s
Restart  Phase #:45   [KAZEMA] Files:1    Time: 0s
Parallel Phase #:46   [KAZEMA] Files:3    Time: 1s
Restart  Phase #:47   [KAZEMA] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [KAZEMA] Files:1    Time: 9s
Restart  Phase #:49   [KAZEMA] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [KAZEMA] Files:1    Time: 15s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [KAZEMA] Files:1    Time: 0s
Restart  Phase #:52   [KAZEMA] Files:1    Time: 1s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [KAZEMA] Files:2    Time: 363s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:56   [KAZEMA] Files:3    Time: 22s
Serial   Phase #:57   [KAZEMA] Files:3    Time: 1s
Parallel Phase #:58   [KAZEMA] Files:10   Time: 2s
Parallel Phase #:59   [KAZEMA] Files:25   Time: 3s
Serial   Phase #:60   [KAZEMA] Files:4    Time: 4s
Serial   Phase #:61   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:62   [KAZEMA] Files:32   Time: 2s
Serial   Phase #:63   [KAZEMA] Files:1    Time: 0s
Parallel Phase #:64   [KAZEMA] Files:6    Time: 5s
Serial   Phase #:65   [KAZEMA] Files:2    Time: 14s
Serial   Phase #:66   [KAZEMA] Files:3    Time: 59s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:69   [KAZEMA] Files:1    Time: 1s
Parallel Phase #:70   [KAZEMA] Files:2    Time: 25s
Restart  Phase #:71   [KAZEMA] Files:1    Time: 0s
Parallel Phase #:72   [KAZEMA] Files:2    Time: 1s
Serial   Phase #:73   [KAZEMA] Files:2    Time: 1s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:76   [KAZEMA] Files:1    Time: 29s
Serial   Phase #:77   [KAZEMA] Files:2    Time: 1s
Restart  Phase #:78   [KAZEMA] Files:1    Time: 1s
Serial   Phase #:79   [KAZEMA] Files:1    Time: 14s
Restart  Phase #:80   [KAZEMA] Files:1    Time: 0s
Parallel Phase #:81   [KAZEMA] Files:3    Time: 28s
Restart  Phase #:82   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:83   [KAZEMA] Files:1    Time: 4s
Restart  Phase #:84   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:85   [KAZEMA] Files:1    Time: 8s
Restart  Phase #:86   [KAZEMA] Files:1    Time: 1s
Parallel Phase #:87   [KAZEMA] Files:4    Time: 56s
Restart  Phase #:88   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:89   [KAZEMA] Files:1    Time: 1s
Restart  Phase #:90   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:91   [KAZEMA] Files:2    Time: 7s
Restart  Phase #:92   [KAZEMA] Files:1    Time: 0s
Serial   Phase #:93   [KAZEMA] Files:1    Time: 0s
Restart  Phase #:94   [KAZEMA] Files:1    Time: 1s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [KAZEMA] Files:1    Time: 18s
Restart  Phase #:96   [KAZEMA] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:97   [KAZEMA] Files:1    Time: 2s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [KAZEMA] Files:1    Time: 61s
*******************   Migration   ******************
Serial   Phase #:99   [KAZEMA] Files:1    Time: 30s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [KAZEMA] Files:1    Time: 1s
Serial   Phase #:101  [KAZEMA] Files:1    Time: 0s
Serial   Phase #:102  [KAZEMA] Files:1    Time: 80s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [KAZEMA] Files:1    Time: 49s
****************   Summary report   ****************
Serial   Phase #:104  [KAZEMA] Files:1    Time: 0s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [KAZEMA] Files:1    Time: 1s
Serial   Phase #:106  [KAZEMA] Files:1    Time: 0s
Serial   Phase #:107  [KAZEMA] Files:1     Time: 41s

------------------------------------------------------
Phases [0-107]         End Time:[2022_03_23 10:48:42]
------------------------------------------------------
Grand Total Time: 1847s



*** WARNING: ERRORS FOUND DURING UPGRADE ***

 1. Evaluate the errors found in the upgrade logs
    and determine the proper action.
 2. Rerun the upgrade when the problem is resolved

REASON:
      ERRORS FOUND: During Upgrade
         FILENAME: D:\Oracle19c\catupgrd0.log AT LINE NUMBER: 1124126
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT    = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR     = [ORA-13509: error encountered during updates to a AWR table ORA-12899: value too large for column ORA-12899: value too large for column "SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30)
 (actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [BEGIN
  /* Perform the required AWR catalog operations for this release */
  dbms_swrf_internal.refresh_catalog;
END;]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT    = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR     = [ORA-12899: value too large for column ORA-12899: value too large for column "SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30)
 (actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT    = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR     = ["SYS"."OBJ$"."SUBNAME" (actual: 31, maximum: 30)  (actual: , maximum: )
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT    = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR     = [(actual: , maximum: ) ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524
ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT    = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR     = [ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 524 ORA-06512: at line 3
]
STATEMENT = [as above]
------------------------------------------------------
------------------------------------------------------
Identifier CATPROC 22-03-23 10:31:12
SCRIPT    = [D:\Oracle19c\rdbms\admin\execsvrm.sql]
ERROR     = [ORA-06512: at line 3]
STATEMENT = [as above]
------------------------------------------------------

 LOG FILES: (D:\Oracle19c\catupgrd*.log)

Upgrade Summary Report Located in:
D:\Oracle19c\upg_summary.log

Grand Total Upgrade Time:    [0d:0h:30m:47s]

I didn’t remove lines from the output to give you an overview of what happened during the initial “catupgrd.sql” call without MAX_STRING_SIZE set as “STANDARD“. As you can see, it has generated few errors & we need to fix them.

ERROR = [ORA-12899: value too large for column ORA-12899: value too large for column “SYS”.”OBJ$”.”SUBNAME” (actual: 31, maximum: 30) is pretty easy to fix. This is mainly due to strings lengths & follow the below steps to fix it.

Move to %ORACLE_HOME%\rdbms\admin folder.

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
alter system set MAX_STRING_SIZE=EXTENDED;
@utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP; 

Once the script runs successfully, shutdown the database once again and start in upgrade mode. Rerun the “catupgrd.sql” as instructed above. Let the upgrade finish. Insure there are no errors. A successfully finished upgrade process will look like below. I mentioned earlier, this process could be pretty time taking based on the size of the database you are upgrading. My entire database was less than 5GB in size & I’ve NVMe+SSD based work laptop running i7 10th generation processor & 32GB memory. The waiting was frustrating.

Serial   Phase #:101  [KAZEMA] Files:1    Time: 0s
Serial   Phase #:102  [KAZEMA] Files:1    Time: 78s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [KAZEMA] Files:1    Time: 23s
****************   Summary report   ****************
Serial   Phase #:104  [KAZEMA] Files:1    Time: 1s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [KAZEMA] Files:1    Time: 1s
Serial   Phase #:106  [KAZEMA] Files:1    Time: 0s
Serial   Phase #:107  [KAZEMA] Files:1     Time: 36s

------------------------------------------------------
Phases [0-107]         End Time:[2022_03_23 15:15:34]
------------------------------------------------------

Grand Total Time: 1209s

 LOG FILES: (D:\Oracle19c\catupgrd0\catupgrd*.log)

Upgrade Summary Report Located in:
D:\Oracle19c\catupgrd0\upg_summary.log

Grand Total Upgrade Time:    [0d:0h:20m:9s]

I came across the errors while I called the upgrade and went ahead and fixed them. Kept the same in the article so that the search engine could pick up the errors for someone else out there. Now it is time for to recompile all invalid objects after upgrade.

SQL>@utlrp.sql

Unless you have a huge number of PL/SQL libraries and other, this should complete in few minutes. A count for invalid objects will be provided to you & you can proceed to fix them once after a database restart. Keep a tight watch on the alert log file and address additional errors, if any.

Finally create SPFILE from current parameter file, shutdown and restart the database. You should be good to go now. Check for the Temporary files, Redo Log files and other vitals.

These invalid objects are from custom schema & could be fixed/dropped if not needed. Make sure there are no system level invalid objects.

Another area of interest could be OLAP.

Check and confirm OLAP objects are not invalid. OLAP catalog was deprecated after 11g. You may proceed removing it if needed.

Reference articles

  • https://shivanandarao-oracle.com/2015/09/16/rman-restore-backup-of-lower-version-database-to-a-higher-version/
  • Upgrade using RMAN Duplicate with Noopen clause and Backup Location (Doc ID 2022820.1)
  • How to Convert Non-CDB to PDB Database on same local host machine in 12c – Testcase (Doc ID 2012448.1)
  • Utlu122s.sql Fails with ‘ORA-13509: error encountered during updates to a AWR table’ During Upgrade to 12c From 11g (Doc ID 2524115.1)
  • How to Increase the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in 12C Database using MAX_STRING_SIZE ? (Doc ID 1570297.1)
  • 12.2 : Database upgrade fails with error “SELECT TO_NUMBER(’32K_MIGRATION_NOT_COMPLETED’)” (Doc ID 2344458.1)

Footnote: For a PRODUCTION environment, please follow standard procedures like running upgrade check, prerequisites etc.

TNS-03505: Failed to resolve name

One of the major annoyances with Oracle EBS CDB/PDB environment settings I found that, I cannot connect to the PDB (for EBS) from the CDB environment (default). I always have to source the PDB environment in order to connect the PDB and do my stuffs.

As usual, I am doing this for my LAB & you may not copy this to your PRODUCTION environment.

So this what I get when I try to connect to PDB from CDB environment.

oracle@erp-devp.xyz.com:/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin>tnsping DEVP

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-MAR-2022 14:59:03

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora

TNS-03505: Failed to resolve name

The easiest solution was to add an additional entry for the PDB within the $TNS_ADMIN tnsnames.ora file. Let us see how to. Please note these might get overwritten when you run autoconfig.

Save the file & you are ready to go. Notice carefully the service name and instance name values for “DEVP” and “DEVPCDB”. The PDB will have own name for the service, however the instance name will point towards the CDB name & this is the way it should be.

oracle@erp-devp.xyz.com:/home/oracle>tnsping DEVP

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-MAR-2022 15:06:20

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL=tcp)(HOST=erp-devp.xyz.com)(PORT=1526)) (CONNECT_DATA = (SERVICE_NAME=DEVP)(INSTANCE_NAME=DEVPCDB)))
OK (0 msec)
oracle@erp-devp.xyz.com:/home/oracle>

That’s all. Regardless whether it is EBS or not, you can keep adding the PDB details into the tnsnames.ora file & if the service is registered with the listener, will able to connect without sourcing the specific environment variables.