Our previous solution to fix “one user cannot” logon was doomed by additional issues sooner than expected. We had to regenerate the jar files using ADADMIN finally to close the issue.
===
We are using Oracle EBS R12 for last 12 years and recently upgraded to 12.2.10. We hardly had much technical issues with the instance until recent days, then came a real annoyance.
One particular user was unable to logon. Instead of OA_HTML page, he was either getting redirected to logout page or the OA_HTML page keeps on trying to load. One of the workarounds we came across was, resetting his password. Once the password changed, he was taken to setup password page and to regular landing page. Well, this also turned into troublesome as each time his session expired, we were forced to reset his password.
So what went wrong? We had an issue with concurrent manager outputs and logs opening into a blank page recently. This happened after RMAN backup up, that is scheduled and we had to run autoconfig to fix this issue. Although autoconfig fixed that issue, opened issues with user logon, as explained above. We kept on watching the alerts for possible errors and there were none. Anyhow, we decided to do the following as the ONLY one relevant document we were able to find on Oracle support was “Unable To Login With One User On Oracle EBS Application (Doc ID 2480008.1)” and the case described was not our case.
This is what finally worked:
Cleared global cache (didn’t fix issues)
Shutdown Apps tier
Ran autoconfig on database Tier
Ran autoconfig on both Run and Patch file systems
Restarted Apps tier and the issues were resolved
We really don’t know what happened after the RMAN & we didn’t update/altered the OS (Oracle Linux 7). Well, things could go south once in a while, after all Oracle EBS is such a huge and complex repository!
Currently I’ve 2 different versions of Oracle database installed & instances running from my development machine. Oracle 11G R2 (11.20.4) and Oracle 19c (19.14) & yes, we are supported by Oracle. I had 11G installed first & later installed 19c for checking out APEX and ORDS.
As 11G listener was running, I didn’t notice anything problematic until, tried to connect as a specific user. I started getting the error that the listener doesn’t know of the service name.
PS D:\Oracle19c> sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 12 12:45:30 2023
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> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
PS D:\Oracle19c> sqlplus system@SCTCDB
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 12 12:45:34 2023
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
I went ahead and using netca registered the 19c service with 11g listener, restarted the listener services and this time instead of complaining about the missing service, connection attempt froze.
After some confusion, I decided to drop the 11g listener services and created a new listener from 19c environment.
Registered both 19c & 11g services using 19c NETCA this time & restarted the listener. I was able to connect to both databases using username/password@servicename syntax this time.
So let us see what went wrong. 11g database was configured for connections from Oracle Developer 6i legacy applications.
This cannot be modified without failing the legacy application. Please refer the below to understand how the authentication works with different versions of databases and what has changed.
As I am not allowed to change 11g environment, the ONLY one way I can use the same listener was to register 11g service with 19c listener. Simple, clean and effective. Hope this helps few out there who is not a certified DBA like me ;)
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.
Use 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 immediatePluggable 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.
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.
I apologize for not including the datapatch part with the post. I don’t know how did I miss it entirely, which is not me usually. Amending the datapatch part by the bottom of the article.
Much of the times I use latest Oracle technologies for learning & they hardly make to any of the PRODUCTION environments those I support at work. Recently I wanted to see how to patch Oracle 19c installation on Windows and to be frank, it was buttery smooth.
Let us quickly see how to apply a patch to a Windows installation of Oracle 19c database.
Shutdown all instances & Windows services for Oracle. Make sure you have taken adequate number of backups for the database(s).
Check you computer’s PATH variable. Insure you have %ORACLE_HOME%\perl\bin, in my case “D:\Oracle\19c\perl\bin” as the first entry for PERL.
This is how the PATH information before I make the above changes to it.
Here 11g 32Bit client is the first Oracle software in the PATH, we will modify it like below
Once the patching done, you can rollback the PATH variable according to your requirements.
Visit Oracle support and download the patch, the latest patch for Oracle Database 19c is “32409154”. Patch is only available to customers with a valid support contract.
Extract the archive.
I’ve extracted the archive within the Download folder and the path is
As usual, based on your computer’s hardware capabilities, the patch would finish sooner or later. There will be 2 prompts those you need to say Yes in order to progress the patching.
Once OPatch completes, it is time for us to execute datapatch for the databases that are going to get upgraded to the latest version that you just patched.
Make sure that you have restarted the Windows Services for Oracle. Patching restarts the database, regardless I would suggest you to shutdown and restart the database once after Oracle services are restarted. Insure the PDBs are opened.
Move to $ORACLE_HOME/OPatch folder & execure “datapatch.bat -verbose”
PS D:\Oracle19c\OPatch> .\datapatch.bat -verbose
SQL Patching tool version 19.3.0.0.0 Production on Wed Mar 9 18:32:28 2022
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: D:\Oracle19c\cfgtoollogs\sqlpatch\sqlpatch_12584_2022_03_09_18_32_28\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.11.0.0.0 Release_Update 210417094636: Installed
PDB CDB$ROOT:
No release update patches installed
PDB PDB$SEED:
No release update patches installed
PDB SCT:
No release update patches installed
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 32409154 (Windows Database Bundle Patch : 19.11.0.0.210420 (32409154)):
Apply from 19.1.0.0.0 Feature Release to 19.11.0.0.0 Release_Update 210417094636
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
Patch 32409154 apply (pdb CDB$ROOT): SUCCESS
logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\32409154\24073904/32409154_apply_SCTCDB_CDBROOT_2022Mar09_18_33_46.log (no errors)
Patch 32409154 apply (pdb PDB$SEED): SUCCESS
logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\32409154\24073904/32409154_apply_SCTCDB_PDBSEED_2022Mar09_18_39_19.log (no errors)
Patch 32409154 apply (pdb SCT): SUCCESS
logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\32409154\24073904/32409154_apply_SCTCDB_SCT_2022Mar09_18_39_19.log (no errors)
SQL Patching tool complete on Wed Mar 9 18:45:01 2022
PS D:\Oracle19c\OPatch>
Now we have to check for invalid objects. My database didn’t have any objects, it was a default installation with 0 custom objects in the SCT. However, if you were having a PRODUCTION database, you must confirm that there are no invalid objects post patching. We will see how to accomplish this at next step.
C:\Windows\system32>SET ORACLE_HOME=D:\Oracle19c
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 9 18:54:54 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-03-09 18:55: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-03-09 18:55: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-03-09 19:06:20
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-03-09 19:06:22
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-03-09 18:56:35
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-03-09 18:56:36
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>
Use SQL Developer & check whether there are any invalid objects. In addition to invalid objects, you must check plugged in database violations also. We will see how to in next few steps.
You need to check PDB violations on both root and pdb containers (CDB$ROOT, SCT (my pdb name)). Warnings could be addressed later, TYPE “ERROR” should be immediately addressed before you open the PDB for read write operations.
Fix the errors following instructions available from either Oracle support or blog posts (better & less complex)
Simple & neat right? Share your experience in the comments area.
Once downloaded, you have to extract the package to a folder. Please note, the extracted folder or the folder from which you will execute “Setup.exe” is going to be 19c Oracle Home. So, name the folder wisely. For example, I have extracted the files to a folder D:\Oracle\19c
You can start the installation by right clicking the Setup.exe and running as “Administrator”.
This will kick start the installation.
We will not create a database along with the software installation. Hence select “Set Up Software Only”
I suggest you to create a normal user (no administrator privileges) “oracle”, that will be used for all your future Oracle software installations. If you don’t have an account explicitly created yet for Oracle, the installation gives you an interface to create one. It’s your choice.
Please remember, earlier I said, the folder where you have extracted the installation media is going to be Oracle 19c home, aka Oracle Home. Rename the folder with an appropriate name before going ahead with the installation.
You will be asked to provide a location for Oracle Base. To content within the Oracle Home, you can specify a path like “D:\Oracle\19c\database” or “D:\Oracle\19c\db” or a folder name of your choice.
After few basic checks, the installation will proceed now. I strongly suggest you to assign a STATIC IP for your computer, prior continuing.
That’s it. No hacks, no modifications to config files or anything such to get the software installed on Windows!.
You can use “Database Configuration Assistant” for creating new database. We will see how to create a new database now. This time we will be creating a CDB (Container Database) and one PDB (Pluggable Database). You may go ahead with creating a single instance database when asked to choose between.
Select “Advanced Configuration”
You can choose between a normal database or Container database with PDB at next step. If you don’t wish to create the database as a container, uncheck the option “Create as Container database”. If you chose to continue with creating your database as container, I suggest you to leave the defaults as it is, other than the PDB name. If you need more PDBs, may add later to the container.
Leave the defaults, unless you know what you are doing.
If you are setting up the database for the LAB/Learning purposes, you may ignore these. However, for production instances, you must configure the Recovery area & setup archiving.
You can create a LISTENER for your database now. If you other Oracle database LISTENERs already running, choose a port number that wouldn’t raise a conflict.
Optionally you can setup Database Vault & Label Security at this step ;)
Okay, we need your attention here. Oracle automatically allocates 40% of total available physical memory for the newly created database. As I mentioned in the previous step, if you have more than one database running from the same computer, you must adjust the available memory to avoid choking the resources.
You may leave the defaults untouched here & you can change this value as sys admin when needed.
WebLogic Repos insist upon “AL32UTF8” Character Set & unless you have specific reasons to change the Character set, leave the defaults. If you are planning to migrate a legacy application that uses a different character set like Arabic (AR8MSWIN1256), please read this post
As per the post, you can use a different character set for a PDB starting from Oracle database versions 12.2.
We do love Scott bit too much, if yes, go with sample schemas (Trust me, you need it)
Enterprise Manager configuration is totally optional. If you choose to, will install an Express edition of Oracle Enterprise Manager (means more resources)
As usual, for a LAB environment I will go with same password for all accounts.
I would suggest you to accept the defaults at next step & change the passwords and unlock accounts as and when needed.
That should complete the installation and creation of database for 19c. Have comments? Please post them to me.
