Archives December 2021

ORA-01920: user name ‘PERFSTAT’ conflicts with another user or role name

Cause:

spcreate.sql throws the following error

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

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: oracle
oracle


Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
EXAMPLE                        PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace:

Using tablespace SYSAUX as PERFSTAT default tablespace.


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace:

Using tablespace TEMP as PERFSTAT temporary tablespace.


... Creating PERFSTAT user
create user perfstat
            *
ERROR at line 1:
ORA-01920: user name 'PERFSTAT' conflicts with another user or role name

Solution:

Drop the existing perfstat account using spdrop.sql and recreate the statspack

SQL> @?/rdbms/admin/spdrop.sql
Dropping old versions (if any)
EXPDP/IMPDP PERFORMANCE IN STANDARD EDITION

In standard edition, it is too sad that “parallel” cannot be used for speed increase.

So in this case, we may need to think in a different way to improve the performance

To reduce the IO in a single dumpfile, specify multiple dumpfiles and spread the data across multiple disks.

Also exclude STATISTICS,GRANT to save time.

Grants backup can be taken separately using a script in a spool file from sqlplus and

Statistics can be gathered using DBMS_STATS package

Specify filesize parameter to limit the size of the dumpfile to increase the dumpfiles to a particular extent but too much of dumpfiles also may reduce the performance.

dumpfile=dir1:file1.dmp,dir2:file2.dmp,dir3:file3.dmp 
logfile=exp.log 
schemas=TEST 
exclude=STATISTICS,GRANT 
filesize=100M

C:\WINDOWS\system32>expdp dumpfile=exp:exp1.dmp,exp:exp2.dmp,exp:exp3.dmp logfile=exp.log schemas=TEST exclude=STATISTICS,GRANT reuse_dumpfiles=Y filesize=100M

Export: Release 12.1.0.1.0 - Production on Tue Dec 14 17:01:11 2021

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

Username: / as sysdba

Connected to: Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA dumpfile=exp:exp1.dmp,exp:exp2.dmp,exp:exp3.dmp logfile=exp.log schemas=TEST exclude=STATISTICS,GRANT reuse_dumpfiles=Y filesize=100M
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type SCHEMA_EXPORT/TABLE/TABLE
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . exported "TEST"."BIG_TABLE"                          98.58 MB 1000000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  D:\EXP1.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Dec 14 17:01:24 2021 elapsed 0 00:00:09
SQL QUERY TO FIND TOP SIZED TABLES OR SEGMENTS IN ORACLE

Use the query to find the top segments or tables in oracle database

select * from (
select segment_name,
            owner,
       bytes/1048576 MB 
from dba_segments
where owner not in ('SYS','SYSTEM')
order by 3 desc)
where rownum < 6;
HOW TO APPLY LATEST OEM RU PATCHING FOR VERSION 13C4

EM13.4CC –> RU13 (13.9.4.2.7 )

Stop OMS service

[oracle@oem ~]$ emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
BI Publisher is disabled, to enable BI Publisher on this host, use the 'emctl config oms -enable_bip' command
Stopping BI Publisher Server...

Take a tar ball of the middleware home 

[oracle@oem em]$ tar -czf /home/oracle/middleware.tar middleware

Rollback plan: As there are no rollback option to go back to previous state, you need to rely on the tar backup of the middleware home to restore back in case of failure

Install the opatch

[oracle@oem ~]$ export ORACLE_HOME=/data01/app/em/middleware/
[oracle@oem ~]$ $ORACLE_HOME/oracle_common/jdk/bin/java -jar /home/oracle/6880880/opatch_generic.jar -silent oracle_home=$ORACLE_HOME

Check the version

[oracle@oem ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 13.9.4.2.7
OPatch succeeded.

Start the OMS 

[oracle@oem ~]$ emctl secure oms
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Securing OMS... Started.
Enter Enterprise Manager Root (SYSMAN) Password :
Enter Agent Registration Password :
Securing OMS... Successful
Restart OMS
[oracle@oem ~]$ emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up

OMS patcher also needs to be upgraded
Take backup of existing OMS patcher directory

[oracle@oem middleware]$ mv OMSPatcher/ OMSPatcherbkp

Unzip the software to middleware home

[oracle@oem ~]$ unzip p19999993_134000_Generic.zip -d /data01/app/em/middleware/

Validate the OMS patcher version

[oracle@oem ~]$ export ORACLE_HOME=/data01/app/em/middleware/
[oracle@oem ~]$ $ORACLE_HOME/OMSPatcher/omspatcher version
OMSPatcher Version: 13.9.4.6.0
OPlan Version: 12.2.0.1.16
OsysModel build: Thu Oct 25 18:18:12 PDT 2018
OMSPatcher succeeded.

Remove or deconfig NFS mount from a host

Stop the NFS service 

[root@dpprod oracle]# /bin/systemctl stop nfs.service
[root@dpprod oracle]# /bin/systemctl status nfs.service

Disable the nfs services 

[root@dpprod oracle]# chkconfig nfslock off
Note: Forwarding request to 'systemctl disable nfslock.service'.
[root@dpprod oracle]# chkconfig rpcgssd off
Note: Forwarding request to 'systemctl disable rpcgssd.service'.
[root@dpprod oracle]# chkconfig rpcidmapd off
Note: Forwarding request to 'systemctl disable rpcidmapd.service'.
[root@dpprod oracle]# chkconfig portmap off
error reading information on service portmap: No such file or directory
[root@dpprod oracle]# chkconfig nfs off
Note: Forwarding request to 'systemctl disable nfs.service'.
Removed symlink /etc/systemd/system/multi-user.target.wants/nfs-server.service.

Remove the installed packages 

[root@dpprod oracle]# yum remove portmap nfs-utils
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package nfs-utils.x86_64 1:1.3.0-0.68.0.1.el7.2 will be erased
--> Processing Dependency: nfs-utils for package: oracle-database-preinstall-19c-1.0-3.el7.x86_64
--> Processing Dependency: nfs-utils for package: oracle-database-server-12cR2-preinstall-1.0-5.el7.x86_64
--> Processing Dependency: nfs-utils for package: ipa-client-4.6.6-11.0.1.el7.x86_64
--> Processing Dependency: nfs-utils for package: libvirt-daemon-driver-storage-core-4.5.0-33.el7.x86_64
--> Processing Dependency: nfs-utils for package: targetd-0.8.6-1.el7.noarch
--> Processing Dependency: nfs-utils for package: oracle-rdbms-server-12cR1-preinstall-1.0-7.el7.x86_64
---> Package rpcbind.x86_64 0:0.2.0-49.el7 will be erased
--> Processing Dependency: rpcbind for package: 1:quota-4.01-19.el7.x86_64
--> Running transaction check
---> Package ipa-client.x86_64 0:4.6.6-11.0.1.el7 will be erased
--> Processing Dependency: ipa-client = 4.6.6-11.0.1.el7 for package: ipa-server-4.6.6-11.0.1.el7.x86_64
---> Package libvirt-daemon-driver-storage-core.x86_64 0:4.5.0-33.el7 will be erased
--> Processing Dependency: libvirt-daemon-driver-storage-core = 4.5.0-33.el7 for package: libvirt-daemon-driver-storage-iscsi-4.5.0-33.el7.x86_64
--> Processing Dependency: libvirt-daemon-driver-storage-core = 4.5.0-33.el7 for package: libvirt-daemon-driver-storage-4.5.0-33.el7.x86_64
--> Processing Dependency: libvirt-daemon-driver-storage-core = 4.5.0-33.el7 for package: libvirt-daemon-driver-storage-mpath-4.5.0-33.el7.x86_64
--> Processing Dependency: libvirt-daemon-driver-storage-core = 4.5.0-33.el7 for package: libvirt-daemon-driver-storage-rbd-4.5.0-33.el7.x86_64
--> Processing Dependency: libvirt-daemon-driver-storage-core = 4.5.0-33.el7 for package: libvirt-daemon-driver-qemu-4.5.0-33.el7.x86_64
--> Processing Dependency: libvirt-daemon-driver-storage-core = 4.5.0-33.el7 for package: libvirt-daemon-driver-storage-scsi-4.5.0-33.el7.x86_64
--> Processing Dependency: libvirt-daemon-driver-storage-core = 4.5.0-33.el7 for package: libvirt-daemon-driver-storage-logical-4.5.0-33.el7.x86_64
--> Processing Dependency: libvirt-daemon-driver-storage-core = 4.5.0-33.el7 for package: libvirt-daemon-driver-storage-gluster-4.5.0-33.el7.x86_64
--> Processing Dependency: libvirt-daemon-driver-storage-core = 4.5.0-33.el7 for package: libvirt-daemon-driver-storage-disk-4.5.0-33.el7.x86_64
---> Package oracle-database-preinstall-19c.x86_64 0:1.0-3.el7 will be erased
---> Package oracle-database-server-12cR2-preinstall.x86_64 0:1.0-5.el7 will be erased
---> Package oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-7.el7 will be erased
---> Package quota.x86_64 1:4.01-19.el7 will be erased
---> Package targetd.noarch 0:0.8.6-1.el7 will be erased
--> Running transaction check
---> Package ipa-server.x86_64 0:4.6.6-11.0.1.el7 will be erased
---> Package libvirt-daemon-driver-qemu.x86_64 0:4.5.0-33.el7 will be erased
--> Processing Dependency: libvirt-daemon-driver-qemu = 4.5.0-33.el7 for package: libvirt-4.5.0-33.el7.x86_64
--> Processing Dependency: libvirt-daemon-driver-qemu = 4.5.0-33.el7 for package: libvirt-daemon-kvm-4.5.0-33.el7.x86_64
---> Package libvirt-daemon-driver-storage.x86_64 0:4.5.0-33.el7 will be erased
---> Package libvirt-daemon-driver-storage-disk.x86_64 0:4.5.0-33.el7 will be erased
---> Package libvirt-daemon-driver-storage-gluster.x86_64 0:4.5.0-33.el7 will be erased
---> Package libvirt-daemon-driver-storage-iscsi.x86_64 0:4.5.0-33.el7 will be erased
---> Package libvirt-daemon-driver-storage-logical.x86_64 0:4.5.0-33.el7 will be erased
---> Package libvirt-daemon-driver-storage-mpath.x86_64 0:4.5.0-33.el7 will be erased
---> Package libvirt-daemon-driver-storage-rbd.x86_64 0:4.5.0-33.el7 will be erased
---> Package libvirt-daemon-driver-storage-scsi.x86_64 0:4.5.0-33.el7 will be erased
--> Running transaction check
---> Package libvirt.x86_64 0:4.5.0-33.el7 will be erased
---> Package libvirt-daemon-kvm.x86_64 0:4.5.0-33.el7 will be erased
--> Processing Dependency: libvirt-daemon-kvm >= 3.9.0-1 for package: 1:libguestfs-1.40.2-9.0.1.el7.x86_64
--> Processing Dependency: libvirt-daemon-kvm for package: gnome-boxes-3.28.5-4.el7.x86_64
--> Running transaction check
---> Package gnome-boxes.x86_64 0:3.28.5-4.el7 will be erased
---> Package libguestfs.x86_64 1:1.40.2-9.0.1.el7 will be erased
--> Finished Dependency Resolution
ol7_UEKR5/x86_64                                                                  | 3.0 kB  00:00:00
ol7_UEKR5/x86_64/updateinfo                                                       | 186 kB  00:00:00
ol7_UEKR5/x86_64/primary_db                                                       |  34 MB  00:00:03
ol7_latest/x86_64                                                                 | 3.6 kB  00:00:00
ol7_latest/x86_64/updateinfo                                                      | 3.4 MB  00:00:00
ol7_latest/x86_64/primary_db                                                      |  37 MB  00:00:03

Dependencies Resolved

=========================================================================================================
 Package                                    Arch      Version                     Repository        Size
=========================================================================================================
Removing:
 nfs-utils                                  x86_64    1:1.3.0-0.68.0.1.el7.2      @ol7_latest      1.1 M
 rpcbind                                    x86_64    0.2.0-49.el7                @anaconda/7.8    101 k
Removing for dependencies:
 gnome-boxes                                x86_64    3.28.5-4.el7                @anaconda/7.8    5.5 M
 ipa-client                                 x86_64    4.6.6-11.0.1.el7            @anaconda/7.8    254 k
 ipa-server                                 x86_64    4.6.6-11.0.1.el7            @anaconda/7.8    1.0 M
 libguestfs                                 x86_64    1:1.40.2-9.0.1.el7          @anaconda/7.8    7.1 M
 libvirt                                    x86_64    4.5.0-33.el7                @anaconda/7.8    0.0
 libvirt-daemon-driver-qemu                 x86_64    4.5.0-33.el7                @anaconda/7.8    1.5 M
 libvirt-daemon-driver-storage              x86_64    4.5.0-33.el7                @anaconda/7.8    0.0
 libvirt-daemon-driver-storage-core         x86_64    4.5.0-33.el7                @anaconda/7.8    707 k
 libvirt-daemon-driver-storage-disk         x86_64    4.5.0-33.el7                @anaconda/7.8     68 k
 libvirt-daemon-driver-storage-gluster      x86_64    4.5.0-33.el7                @anaconda/7.8    132 k
 libvirt-daemon-driver-storage-iscsi        x86_64    4.5.0-33.el7                @anaconda/7.8     64 k
 libvirt-daemon-driver-storage-logical      x86_64    4.5.0-33.el7                @anaconda/7.8     72 k
 libvirt-daemon-driver-storage-mpath        x86_64    4.5.0-33.el7                @anaconda/7.8     60 k
 libvirt-daemon-driver-storage-rbd          x86_64    4.5.0-33.el7                @anaconda/7.8     80 k
 libvirt-daemon-driver-storage-scsi         x86_64    4.5.0-33.el7                @anaconda/7.8     64 k
 libvirt-daemon-kvm                         x86_64    4.5.0-33.el7                @anaconda/7.8    0.0
 oracle-database-preinstall-19c             x86_64    1.0-3.el7                   @ol7_latest       76 k
 oracle-database-server-12cR2-preinstall    x86_64    1.0-5.el7                   @ol7_latest       55 k
 oracle-rdbms-server-12cR1-preinstall       x86_64    1.0-7.el7                   @ol7_latest       53 k
 quota                                      x86_64    1:4.01-19.el7               @anaconda/7.8    887 k
 targetd                                    noarch    0.8.6-1.el7                 @anaconda/7.8    152 k

Transaction Summary
=========================================================================================================
Remove  2 Packages (+21 Dependent packages)

Installed size: 19 M
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Erasing    : libvirt-4.5.0-33.el7.x86_64                                                          1/23
  Erasing    : oracle-database-server-12cR2-preinstall-1.0-5.el7.x86_64                             2/23
  Erasing    : oracle-database-preinstall-19c-1.0-3.el7.x86_64                                      3/23
  Erasing    : targetd-0.8.6-1.el7.noarch                                                           4/23
  Erasing    : oracle-rdbms-server-12cR1-preinstall-1.0-7.el7.x86_64                                5/23
  Erasing    : 1:libguestfs-1.40.2-9.0.1.el7.x86_64                                                 6/23
  Erasing    : gnome-boxes-3.28.5-4.el7.x86_64                                                      7/23
  Erasing    : libvirt-daemon-kvm-4.5.0-33.el7.x86_64                                               8/23
  Erasing    : libvirt-daemon-driver-storage-4.5.0-33.el7.x86_64                                    9/23
  Erasing    : libvirt-daemon-driver-storage-disk-4.5.0-33.el7.x86_64                              10/23
  Erasing    : libvirt-daemon-driver-storage-logical-4.5.0-33.el7.x86_64                           11/23
  Erasing    : libvirt-daemon-driver-storage-scsi-4.5.0-33.el7.x86_64                              12/23
  Erasing    : libvirt-daemon-driver-storage-iscsi-4.5.0-33.el7.x86_64                             13/23
  Erasing    : libvirt-daemon-driver-storage-mpath-4.5.0-33.el7.x86_64                             14/23
  Erasing    : libvirt-daemon-driver-storage-gluster-4.5.0-33.el7.x86_64                           15/23
  Erasing    : libvirt-daemon-driver-storage-rbd-4.5.0-33.el7.x86_64                               16/23
  Erasing    : libvirt-daemon-driver-qemu-4.5.0-33.el7.x86_64                                      17/23
  Erasing    : libvirt-daemon-driver-storage-core-4.5.0-33.el7.x86_64                              18/23
  Erasing    : ipa-server-4.6.6-11.0.1.el7.x86_64                                                  19/23
  Erasing    : ipa-client-4.6.6-11.0.1.el7.x86_64                                                  20/23
  Erasing    : 1:nfs-utils-1.3.0-0.68.0.1.el7.2.x86_64                                             21/23
warning: file /var/lib/nfs/v4recovery: remove failed: No such file or directory
warning: file /var/lib/nfs/statd/sm.bak: remove failed: No such file or directory
warning: file /var/lib/nfs/statd/sm: remove failed: No such file or directory
warning: file /var/lib/nfs/statd: remove failed: No such file or directory
warning: directory /var/lib/nfs/rpc_pipefs: remove failed: Device or resource busy
warning: /var/lib/nfs/etab saved as /var/lib/nfs/etab.rpmsave
  Erasing    : 1:quota-4.01-19.el7.x86_64                                                          22/23
  Erasing    : rpcbind-0.2.0-49.el7.x86_64                                                         23/23
warning: file /var/lib/rpcbind: remove failed: No such file or directory
  Verifying  : 1:nfs-utils-1.3.0-0.68.0.1.el7.2.x86_64                                              1/23
  Verifying  : oracle-rdbms-server-12cR1-preinstall-1.0-7.el7.x86_64                                2/23
  Verifying  : libvirt-daemon-driver-storage-scsi-4.5.0-33.el7.x86_64                               3/23
  Verifying  : libvirt-daemon-driver-storage-disk-4.5.0-33.el7.x86_64                               4/23
  Verifying  : libvirt-daemon-driver-qemu-4.5.0-33.el7.x86_64                                       5/23
  Verifying  : ipa-server-4.6.6-11.0.1.el7.x86_64                                                   6/23
  Verifying  : rpcbind-0.2.0-49.el7.x86_64                                                          7/23
  Verifying  : libvirt-daemon-kvm-4.5.0-33.el7.x86_64                                               8/23
  Verifying  : targetd-0.8.6-1.el7.noarch                                                           9/23
  Verifying  : libvirt-daemon-driver-storage-core-4.5.0-33.el7.x86_64                              10/23
  Verifying  : libvirt-daemon-driver-storage-mpath-4.5.0-33.el7.x86_64                             11/23
  Verifying  : oracle-database-preinstall-19c-1.0-3.el7.x86_64                                     12/23
  Verifying  : oracle-database-server-12cR2-preinstall-1.0-5.el7.x86_64                            13/23
  Verifying  : libvirt-daemon-driver-storage-4.5.0-33.el7.x86_64                                   14/23
  Verifying  : 1:quota-4.01-19.el7.x86_64                                                          15/23
  Verifying  : libvirt-daemon-driver-storage-rbd-4.5.0-33.el7.x86_64                               16/23
  Verifying  : libvirt-4.5.0-33.el7.x86_64                                                         17/23
  Verifying  : gnome-boxes-3.28.5-4.el7.x86_64                                                     18/23
  Verifying  : libvirt-daemon-driver-storage-gluster-4.5.0-33.el7.x86_64                           19/23
  Verifying  : libvirt-daemon-driver-storage-iscsi-4.5.0-33.el7.x86_64                             20/23
  Verifying  : ipa-client-4.6.6-11.0.1.el7.x86_64                                                  21/23
  Verifying  : 1:libguestfs-1.40.2-9.0.1.el7.x86_64                                                22/23
  Verifying  : libvirt-daemon-driver-storage-logical-4.5.0-33.el7.x86_64                           23/23

Removed:
  nfs-utils.x86_64 1:1.3.0-0.68.0.1.el7.2                  rpcbind.x86_64 0:0.2.0-49.el7

Dependency Removed:
  gnome-boxes.x86_64 0:3.28.5-4.el7
  ipa-client.x86_64 0:4.6.6-11.0.1.el7
  ipa-server.x86_64 0:4.6.6-11.0.1.el7
  libguestfs.x86_64 1:1.40.2-9.0.1.el7
  libvirt.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-qemu.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-storage.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-storage-core.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-storage-disk.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-storage-gluster.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-storage-iscsi.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-storage-logical.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-storage-mpath.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-storage-rbd.x86_64 0:4.5.0-33.el7
  libvirt-daemon-driver-storage-scsi.x86_64 0:4.5.0-33.el7
  libvirt-daemon-kvm.x86_64 0:4.5.0-33.el7
  oracle-database-preinstall-19c.x86_64 0:1.0-3.el7
  oracle-database-server-12cR2-preinstall.x86_64 0:1.0-5.el7
  oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-7.el7
  quota.x86_64 1:4.01-19.el7
  targetd.noarch 0:0.8.6-1.el7

Complete!

Clear the directories

rm -rf /etc/exports
rm -rf /nfs

Remove the entries from fstab

Failed to start nfs-server.service: Transaction order is cyclic. See system logs for details.

Cause:

NFS service failed to start due to the following error 

[root@dpprod oracle]# systemctl start nfs-server
Failed to start nfs-server.service: Transaction order is cyclic. See system logs for details.
See system logs and 'systemctl status nfs-server.service' for details.
[root@dpprod oracle]# systemctl status nfs-server
â—Ź nfs-server.service - NFS server and services
   Loaded: loaded (/usr/lib/systemd/system/nfs-server.service; disabled; vendor preset: disabled)
  Drop-In: /run/systemd/generator/nfs-server.service.d
           └─order-with-mounts.conf
   Active: inactive (dead)

Dec 05 14:42:51 dpprod systemd[1]: Found dependency on nfs-server.service/start
Dec 05 14:44:33 dpprod systemd[1]: Found ordering cycle on nfs-server.service/start
Dec 05 14:44:33 dpprod systemd[1]: Found dependency on oranfs.mount/start
Dec 05 14:44:33 dpprod systemd[1]: Found dependency on nfs-server.service/start
Dec 05 14:55:12 dpprod systemd[1]: Found ordering cycle on nfs-server.service/start
Dec 05 14:55:12 dpprod systemd[1]: Found dependency on oranfs.mount/start
Dec 05 14:55:12 dpprod systemd[1]: Found dependency on nfs-server.service/start
Dec 05 14:55:33 dpprod systemd[1]: Found ordering cycle on nfs-server.service/start
Dec 05 14:55:33 dpprod systemd[1]: Found dependency on oranfs.mount/start
Dec 05 14:55:33 dpprod systemd[1]: Found dependency on nfs-server.service/start
[root@dpprod oracle]# systemctl start nfs-server
Failed to start nfs-server.service: Transaction order is cyclic. See system logs for details.
See system logs and 'systemctl status nfs-server.service' for details.

Solution:

Reload the daemon and start nfs 

systemctl daemon-reload
systemctl start nfs-server
ORACLE DATABASE PATCHING RU STEPS 19C

Source version : 19.0.0.0

RU version : 19.13.0.0

Download the patch from the below link

NameDownload Link
Database Release Update 19.12.0.0.210720Patch 32904851

Analyze the Patch conflicts for your version in below link

https://support.oracle.com/epmos/faces/PatchConflictCheck

Copy the software to host and unzip the software in stage directory

-bash-4.2$ unzip p33192793_190000_Linux-x86-64.zip

Take a tar ball of current oracle home 

-bash-4.2$ tar -cvf 19.0.0.tar 19.0.0/ -d /tmp/

Check for active sessions and notify the application owners to stop the applications if any running

SQL> select username,
  2         program,
  3         module,
  4         count(*)
  5  from
  6  v$session
  7  where status<>'INACTIVE'
  8  and
  9  username not in  ('SYS','SYSTEM')
 10  group by username,program,module;

no rows selected

Stop the listener

-bash-4.2$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-DEC-2021 18:22:31

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl19x)(PORT=1521)))
The command completed successfully

Stop the database instance

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Stop the EM agent

-bash-4.2$ /u01/app/oracle/agent/agent_13.4.0.0.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.

Set the environment variables and check patch inventory for existing applied patches

-bash-4.2$ export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/data01/product/19.0.0/bin:/data01/product/19.0.0/OPatch
-bash-4.2$
-bash-4.2$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /data01/product/19.0.0
Central Inventory : /apps01/oraInventory1
   from           : /data01/product/19.0.0/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /data01/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-03_18-33-39PM_1.log

Lsinventory Output file location : /data01/product/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2021-12-03_18-33-39PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: orcl19x
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

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


Interim patches (2) :

Patch  29585399     : applied on Thu Apr 18 12:51:33 IST 2019
Unique Patch ID:  22840393
Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
   Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
   Bugs fixed:
...........................................
OPatch succeeded.

Download the latest opatch utility from the link and upload it to host. (This link may or may not work if the link redirect to the another site)

https://updates.oracle.com/Orion/PatchDetails/handle_plat_lang_change?release=600000000122912&plat_lang=46P&patch_file=&file_id=&password_required=&password_required_readme=&merged_trans=&aru=24499147&patch_num=6880880&patch_num_id=3506013&default_release=600000000122912&default_plat_lang=226P&default_compatible_with=&patch_password=&orderby=&direction=&no_header=0&sortcolpressed=&tab_number=&email=kishan.m%40oracle.com&userid=O-kishan.m%40oracle.com

Move the existing opatch directory to a backup directory

-bash-4.2$ cd $ORACLE_HOME
-bash-4.2$ mv OPatch/ OPatch_bkp

Unzip the download new opatch zip file to oracle home 

-bash-4.2$ unzip /home/oracle/p6880880_190000_LINUX.zip -d /data01/product/19.0.0

Perform the prechecks by check conflict against oracle home with the patch staging directory

-bash-4.2$ opatch prereq CheckConflictAgainstOHWithDetail -ph /home/oracle/33192793/
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /data01/product/19.0.0
Central Inventory : /apps01/oraInventory1
   from           : /data01/product/19.0.0/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /data01/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-03_18-44-27PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Check if there are active files accessed by processes

-bash-4.2$ opatch prereq CheckActiveFilesAndExecutables -ph /home/oracle/33192793/
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /data01/product/19.0.0
Central Inventory : /apps01/oraInventory1
   from           : /data01/product/19.0.0/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /data01/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-03_18-49-28PM_1.log

Invoking prereq "checkactivefilesandexecutables"

Prereq "checkActiveFilesAndExecutables" for patch 33192793 passed.

OPatch succeeded.

Start the opatch by applying the patch and monitor the logfile for errors

Note: ‘Installing patches…’ at this point , it may take more time to complete. So dont panic and be patient for the installation to complete 

-bash-4.2$ opatch apply -jdk $ORACLE_HOME/jdk
Oracle Interim Patch Installer version 12.2.0.1.28
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /data01/product/19.0.0
Central Inventory : /apps01/oraInventory1
   from           : /data01/product/19.0.0/oraInst.loc
OPatch version    : 12.2.0.1.28
OUI version       : 12.2.0.7.0
Log file location : /data01/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-03_20-04-26PM_1.log

Verifying environment and performing prerequisite checks...

--------------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.28
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /data01/product/19.0.0
Central Inventory : /apps01/oraInventory1
   from           : /data01/product/19.0.0/oraInst.loc
OPatch version    : 12.2.0.1.28
OUI version       : 12.2.0.7.0
Log file location : /data01/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-03_20-05-40PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   33192793

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/data01/product/19.0.0')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '33192793' to OH '/data01/product/19.0.0'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.perlint, 5.28.1.0.0...

Patching component oracle.rdbms.locator, 19.0.0.0.0...

Patching component oracle.perlint.expat, 2.0.1.0.4...

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

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

Patching component oracle.rdbms, 19.0.0.0.0...

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

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

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

Patching component oracle.buildtools.rsf, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

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

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

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

Patching component oracle.duma, 19.0.0.0.0...

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

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

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

Patching component oracle.marvel, 19.0.0.0.0...

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

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

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

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

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

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

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

Patching component oracle.rhp.db, 19.0.0.0.0...

Patching component oracle.sdo, 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.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.wwg.plsql, 19.0.0.0.0...

Patching component oracle.rdbms.crs, 19.0.0.0.0...

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

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

Patching component oracle.ctx.rsf, 19.0.0.0.0...

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

Patching component oracle.rdbms.hs_common, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

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

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

Patching component oracle.rdbms.drdaas, 19.0.0.0.0...

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

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

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

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

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

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

Patching component oracle.ldap.client, 19.0.0.0.0...

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

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

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

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

Patching component oracle.dbtoolslistener, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.oraolap.api, 19.0.0.0.0...

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

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

Patching component oracle.rdbms.install.common, 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.rdbms.lbac, 19.0.0.0.0...

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

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

Patching component oracle.oraolap, 19.0.0.0.0...

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

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

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

Patching component oracle.jdk, 1.8.0.201.0...
Patch 33192793 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [33192793].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /data01/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-03_20-05-40PM_1.log

OPatch succeeded.

Start the listener

-bash-4.2$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 03-DEC-2021 22:34:53

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

Starting /data01/product/19.0.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /data01/product/19.0.0/network/admin/listener.ora
Log messages written to /data01/base/diag/tnslsnr/orcl19x/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl19x)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl19x)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                03-DEC-2021 22:34:54
Uptime                    0 days 0 hr. 0 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data01/product/19.0.0/network/admin/listener.ora
Listener Log File         /data01/base/diag/tnslsnr/orcl19x/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl19x)(PORT=1521)))
Services Summary...
Service "orcl19x1" has 1 instance(s).
  Instance "orcl19x1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Start the database instance

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 3 22:35:39 2021
Version 19.13.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 1560278088 bytes
Fixed Size                  9135176 bytes
Variable Size             905969664 bytes
Database Buffers          637534208 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.

Run the datapatch to perform the post patch SQL fixes

-bash-4.2$ /data01/product/19.0.0/OPatch/datapatch -verbose
SQL Patching tool version 19.13.0.0.0 Production on Fri Dec  3 22:59:50 2021
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: /data01/base/cfgtoollogs/sqlpatch/sqlpatch_6788_2021_12_03_22_59_50/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
Error: prereq checks failed!
verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/data01/base/cfgtoollogs/sqlpatch/sqlpatch_6788_2021_12_03_22_59_50/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Fri Dec  3 22:59:55 2021
-bash-4.2$ /data01/product/19.0.0/OPatch/datapatch -verbose
SQL Patching tool version 19.13.0.0.0 Production on Fri Dec  3 23:11:51 2021
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: /data01/base/cfgtoollogs/sqlpatch/sqlpatch_7201_2021_12_03_23_11_51/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.13.0.0.0 Release_Update 211004165050: Installed
  PDB CDB$ROOT:
    No release update patches installed
  PDB PDB$SEED:
    No release update patches installed
  PDB PDB1:
    No release update patches installed
  PDB PDB2:
    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 PDB1 PDB2
    No interim patches need to be rolled back
    Patch 33192793 (Database Release Update : 19.13.0.0.211019 (33192793)):
      Apply from 19.1.0.0.0 Feature Release to 19.13.0.0.0 Release_Update 211004165050
    No interim patches need to be applied
Installing patches...

Patch installation complete.  Total patches installed: 4

Validating logfiles...done
Patch 33192793 apply (pdb CDB$ROOT): SUCCESS
  logfile: /data01/base/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_DBEMT_CDBROOT_2021Dec03_23_14_15.log (no errors)
Patch 33192793 apply (pdb PDB$SEED): SUCCESS
  logfile: /data01/base/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_DBEMT_PDBSEED_2021Dec03_23_44_35.log (no errors)
Patch 33192793 apply (pdb PDB1): SUCCESS
  logfile: /data01/base/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_DBEMT_PDB1_2021Dec03_23_44_36.log (no errors)
Patch 33192793 apply (pdb PDB2): WITH ERRORS
  logfile: /data01/base/cfgtoollogs/sqlpatch/33192793/24462514/33192793_apply_DBEMT_PDB2_2021Dec04_00_19_11.log (errors)
  -> Error at line 301629: script md/admin/catmdloc.sql
      - ORA-04031: unable to allocate 4096 bytes of shared memory ("java
      - pool","java/time/Period","JOXLE^575d66d7",":SGAClass")

Automatic recompilation incomplete; run utlrp.sql to revalidate.
  PDBs: PDB1 PDB2 PDB$SEED


Please refer to MOS Note 1609718.1 and/or the invocation log
/data01/base/cfgtoollogs/sqlpatch/sqlpatch_7201_2021_12_03_23_11_51/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Sat Dec  4 00:42:15 2021

Check the latest applied patch 

select PATCH_ID,PATCH_UID,PATCH_TYPE,ACTION,STATUS,DESCRIPTION,SOURCE_VERSION,TARGET_VERSION from dba_registry_sqlpatch;

  PATCH_ID  PATCH_UID PATCH ACTION     STATUS          DESCRIPTION          SOURCE_VER TARGET_VERSION
---------- ---------- ----- ---------- --------------- -------------------- ---------- ---------------
  33192793   24462514 RU    APPLY      SUCCESS         Database Release Upd 19.1.0.0.0 19.13.0.0.0
                                                       ate : 19.13.0.0.2110
                                                       19 (33192793)

Validate the application connectivity

LsInventorySession failed: RawInventory gets null OracleHomeInfo

Cause:

Opatch lsinventory fails with the error code 73

-bash-4.2$ /data01/product/19.0.0/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /data01/product/19.0.0
Central Inventory : /apps01/oraInventory1
   from           : /data01/product/19.0.0/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /data01/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-03_16-50-15PM_1.log

List of Homes on this system:

  Home name= oms13c1, Location= "/u01/app/oracle/OEM/middleware"
  Home name= agent13c1, Location= "/u01/app/oracle/agent/agent_13.4.0.0.0"
LsInventorySession failed: RawInventory gets null OracleHomeInfo

OPatch failed with error code 73

Workaround:

Due to improper configuration of inventory in oracle home, the home needs to be attached to the central inventory

-bash-4.2$ /data01/product/19.0.0/oui/bin/attachHome.sh
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4095 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
You can find the log of this install session at:
 /apps01/oraInventory1/logs/AttachHome2021-12-03_05-29-01PM.log
'AttachHome' was successful.

Check OPatch lsinventory

-bash-4.2$ /data01/product/19.0.0/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /data01/product/19.0.0
Central Inventory : /apps01/oraInventory1
   from           : /data01/product/19.0.0/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /data01/product/19.0.0/cfgtoollogs/opatch/opatch2021-12-03_17-33-58PM_1.log

Lsinventory Output file location : /data01/product/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2021-12-03_17-33-58PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: orcl19x
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

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


Interim patches (2) :

Patch  29585399     : applied on Thu Apr 18 12:51:33 IST 2019
Unique Patch ID:  22840393
Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
   Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
   Bugs fixed:
     27222128, 27572040, 27604329, 27760043, 27877830, 28302580, 28470673
     28621543, 28642469, 28699321, 28710663, 28755846, 28772816, 28785321
     28800508, 28808652, 28815557, 28847541, 28847572, 28870496, 28871040
     28874416, 28877252, 28881191, 28881848, 28888083, 28911140, 28925250
     28925460, 28935956, 28940472, 3, 28942694, 28951332, 28963036, 28968779
     28980448, 28995287, 29003207, 29003617, 29016294, 29018680, 29024876
     29026154, 29027933, 29047127, 29052850, 29058476, 29111631, 29112455
     29117337, 29123444, 29125708, 29125786, 29129476, 29131772, 29132456
     29139727, 29146157, 29147849, 29149170, 29152603, 29152752, 29154631
     29154636, 29154829, 29159216, 29159661, 29160462, 29161923, 29169540
     29169739, 29170717, 29173618, 29181568, 29182920, 29183298, 29186091
     29191827, 29201143, 29201695, 29209545, 29210577, 29210610, 29210624
     29210683, 29213641, 29219627, 29224294, 29225861, 29229839, 29235934
     29242906, 29243749, 29244495, 29244766, 29244968, 29248723, 29249583
     29251564, 29255616, 29260224, 29261695, 29271019, 29273360, 29282090
     29282666, 29285453, 29285621, 29290235, 29292232, 29293806, 29294753
     29299830, 29307090, 29307109, 29311336, 29329675, 29330791, 29339299
     29357821, 29360467, 29360775, 29367971, 29368725, 29379299, 29379381
     29380527, 29381000, 29382296, 29391301, 29393649, 29402110, 29411931
     29413360, 29457319, 29465047

Patch  29517242     : applied on Thu Apr 18 12:51:17 IST 2019
Unique Patch ID:  22862832
Patch description:  "Database Release Update : 19.3.0.0.190416 (29517242)"
   Created on 17 Apr 2019, 23:27:10 hrs PST8PDT
   Bugs fixed:
     29515240, 14735102, 19697993, 20313356, 21965541, 25806201, 25883179
     25986062, 26476244, 26611353, 26872233, 27369515, 27423500, 27666312
     27710072, 27846298, 27957203, 28064977, 28072567, 28129791, 28181021
     28210681, 28279456, 28313275, 28350595, 28371123, 28379065, 28431445
     28463226, 28489419, 28502773, 28513333, 28534475, 28561704, 28569897
     28572533, 28572544, 28587723, 28593682, 28594086, 28597221, 28601957
     28605066, 28606598, 28625862, 28627033, 28636532, 28643718, 28644549
     28645570, 28646200, 28646939, 28649388, 28655209, 28663782, 28673945
     28692275, 28694872, 28696373, 28705231, 28710385, 28710734, 28714461
     28718469, 28730079, 28740708, 28760206, 28772390, 28774416, 28777214
     28789531, 28791852, 28795551, 28802734, 28804517, 28810381, 28811560
     28815123, 28815355, 28819640, 28824482, 28833912, 28835937, 28862532
     28863432, 28873575, 28876253, 28876639, 28884931, 28888327, 28892794
     28897512, 28899663, 28901126, 28905457, 28907196, 28912691, 28915561
     28917080, 28918429, 28919145, 28922227, 28922532, 28922608, 28925634
     28925880, 28933158, 28936114, 28937717, 28938698, 28940179, 28940281
     28941901, 28942455, 28945421, 28945994, 28951533, 28952168, 28954762
     28955606, 28957292, 28957723, 28962775, 28965231, 28966444, 28974083
     28977322, 28983095, 28983486, 28986326, 28986481, 28988482, 28989306
     28993295, 28994307, 28996376, 29000000, 29001888, 29002927, 29003738
     29006318, 29006621, 29007321, 29007775, 29008035, 29011936, 29013475
     29014076, 29015118, 29017265, 29018655, 29019121, 29021063, 29021352
     29024552, 29026582, 29026606, 29027456, 29027694, 29027940, 29031575
     29031600, 29032234, 29032457, 29032607, 29033052, 29033145, 29033200
     29033280, 29034587, 29037290, 29038528, 29039089, 29039510, 29043554
     29043651, 29043725, 29044763, 29044954, 29047850, 29048289, 29048498
     29048605, 29050560, 29050765, 29051702, 29052726, 29053783, 29056024
     29056270, 29056560, 29059011, 29061959, 29062692, 29062848, 29062860
     29062868, 29110526, 29110783, 29110790, 29110797, 29110802, 29110805
     29111598, 29113282, 29113305, 29117526, 29117642, 29119077, 29120223
     29122224, 29122254, 29123297, 29123432, 29123482, 29124368, 29125380
     29126345, 29127957, 29128935, 29129450, 29129497, 29129712, 29130219
     29131539, 29132938, 29134447, 29136111, 29138641, 29141316, 29141341
     29141685, 29142667, 29144995, 29145214, 29145730, 29149829, 29150338
     29151520, 29152357, 29155099, 29157389, 29158680, 29158899, 29159909
     29159936, 29160174, 29162095, 29163415, 29163437, 29163524, 29163567
     29167111, 29167342, 29167374, 29167940, 29168219, 29168433, 29169073
     29169215, 29171942, 29172618, 29172826, 29173140, 29173373, 29173817
     29174004, 29176318, 29177466, 29177543, 29177886, 29178385, 29180313
     29180455, 29180559, 29180893, 29181153, 29181231, 29181620, 29181743
     29181923, 29182019, 29183912, 29184297, 29184666, 29185193, 29186456
     29189302, 29189307, 29189889, 29190235, 29190474, 29190740, 29191541
     29192419, 29192468, 29192685, 29193207, 29194205, 29194367, 29194493
     29194827, 29195279, 29195337, 29195758, 29196725, 29199635, 29199733
     29200316, 29200700, 29201494, 29201539, 29202104, 29202850, 29203122
     29203166, 29203425, 29203443, 29203604, 29205281, 29205323, 29205419
     29205463, 29205767, 29205918, 29206109, 29206605, 29207073, 29208260
     29208732, 29211457, 29211724, 29212012, 29212433, 29212611, 29213351
     29213775, 29213850, 29213879, 29214561, 29214960, 29216746, 29216984
     29217294, 29217472, 29217828, 29217848, 29218570, 29219205, 29219273
     29220079, 29221248, 29221891, 29222031, 29222784, 29223833, 29223859
     29223967, 29224065, 29224605, 29225076, 29227602, 29228869, 29229164
     29229754, 29229844, 29229955, 29230252, 29230565, 29231133, 29232117
     29232154, 29234123, 29237538, 29240307, 29241345, 29242017, 29242884
     29243958, 29245137, 29245160, 29246163, 29247415, 29247712, 29247906
     29248552, 29248835, 29248858, 29249991, 29250059, 29251259, 29253184
     29253871, 29254031, 29254930, 29255178, 29255273, 29255431, 29255435
     29256426, 29259119, 29259320, 29261906, 29262512, 29262887, 29265448
     29266248, 29266899, 29267292, 29268412, 29269171, 29270585, 29273539
     29273735, 29273847, 29274428, 29274564, 29274627, 29275461, 29278218
     29279658, 29279751, 29279854, 29282898, 29285503, 29285788, 29285956
     29286037, 29287130, 29287705, 29292837, 29293072, 29297863, 29297915
     29298220, 29299049, 29299082, 29299844, 29301566, 29302963, 29303918
     29304781, 29311588, 29312310, 29312734, 29312753, 29313347, 29313417
     29313525, 29314539, 29318410, 29319441, 29321489, 29323946, 29324568
     29325087, 29325105, 29325257, 29325993, 29327044, 29329087, 29331209
     29331380, 29331493, 29332771, 29333500, 29337310, 29338315, 29338453
     29338780, 29338913, 29339155, 29341209, 29343086, 29346943, 29347620
     29348176, 29350052, 29351386, 29351716, 29351749, 29352867, 29353271
     29355654, 29356547, 29356752, 29358828, 29360672, 29361801, 29363151
     29364171, 29364177, 29367019, 29367561, 29368253, 29373418, 29373588
     29374179, 29377986, 29378029, 29379978, 29382784, 29385652, 29386502
     29386635, 29386660, 29387274, 29388094, 29388524, 29388830, 29389889
     29390435, 29394140, 29394749, 29397954, 29398863, 29399100, 29399121
     29405012, 29405651, 29412066, 29412269, 29417884, 29423826, 29429264
     29429566, 29431485, 29435652, 29438736, 29439522, 29450421, 29451386
     29452576, 29452953, 29457807, 29460252, 29462957, 29486181, 29507616
     29521748, 29530812, 29531654, 29557336, 29558975, 29601461



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

OPatch succeeded.
ORA-12096: error in materialized view log on “SCHEMA”.”MVMASTERTABLE” ..ORA-10632: Invalid rowid

Cause:

This error occur due to prevention of corruption in snapshot MV log while performing DML (insert/delete/update) on master or base table of materialized views

Issue affect versions till 19c

Objects which start with MLOG$ are MV snapshot log table created for a base table. Every time we perform DML operation on the base table, the rows are populated into the MV log during MV refresh. After the rows are deleted from the base table, the inherited MV also shrink the space automatically. If the MV has corruptions in the high water mark, then the corresponding DML fails with the below error

You may see symptoms like below 

MVRF: kkzlShrinkMVLog: executed: alter table "<schema name>"."MLOG$_XXXXX_LOGS_TABLE" shrink space
ORA-31934: error occurred while shrinking the materialized view log at kkzlRunSA:execute
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

In trace file messages similar to below creep in

ORA-12096: error in materialized view log on "schema name"."base table name"
<error barrier> at 0x7fff8f7e34e5 placed xxxx.y@1284
ORA-10632: Invalid rowid
<error barrier> at 0x7fff8f7e9642 placed delexe.c@5967

Error stack traces related to issue

(kntxslt()+4363 -> kserbc2())

To identify the cause generate a 10046 trace and run the failing DML and look for parse error section and note down the obj# at which the execution fail

WAIT #139836060156120: nam='enq: TM - contention' ela= 47834 name|mode=1414332419 object #=108796 table/partition=0 obj#=108796 tim=13882466772554
EXEC #139836060156120:c=144063,e=192357,p=0,cr=122819,cu=24,mis=0,r=0,dep=1,og=1,plh=4195192068,tim=13882466916886
ERROR #139836060156120:err=12096 tim=13882466916928

Then using the obj# , find out the object on which the error occur

Workaround:

Workaround may differ depending on the type of error. So its always recommended to research the cause before applying any changes

Disable the hidden parameter _mv_refresh_shrink_log . There are some limitations of disabling the parameter.

–Automatic shrink will be disabled by setting the parameter to false. You have to perform manual shrink of MV after each iteration of DML

alter system set "_mv_refresh_shrink_log" = FALSE;

If there are ora-600 errors with ORA-00600 [ktsbnew:objdchk_kcbnew_3]

Call stack –> kcb_objdchk_assert <- kcbnew <- ktsbnew <- ktspfmdb

Then you may need to drop the MV log and recreate it 

drop snapshot log on <schema name>.XXXXX_LOGS_TABLE;
create snapshot log on <schema name>.XXXXX_LOGS_TABLE;
EMD upload error:full upload has failed: uploadXMLFiles skipped :: OMS version not checked .. EMD pingOMS error: Failure connecting to – OMS is unreachable

Cause:

Agent is unable to communicate with the OMS due to network error. When try upload and ping to the OMS server, it fails with the following errors

-bash-4.2$ /u01/app/oracle/agent/agent_13.4.0.0.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error:full upload has failed: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors. (OMS_DOWN)

Ping failure from Agent server to OMS server

-bash-4.2$ /u01/app/oracle/agent/agent_inst/bin/emctl pingOMS
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD pingOMS error: Failure connecting to https://192.168.56.123:4903/empbs/upload/ , err

In gcagent.log file ,the following ping communication errors are displayed 

2021-12-01 15:59:10,631 [68:CDEB16E1:GC.SysExecutor.9 (Ping OMS)] INFO - attempting initial heartbeat
2021-12-01 15:59:10,633 [68:CDEB16E1] WARN - Ping communication error
o.s.emSDK.agent.comm.exception.ConnectException [Failure connecting to - Subsystem (Ping Manager) returned bad status of {+ Ping Manager: *Warning* +}
2021-12-01 16:00:40,388 [64:EDF2D361] WARN - Subsystem (Upload Manager) returned bad status of {+ Upload Manager: *Warning* +}
2021-12-01 16:00:40,642 [66:BE87CC23] INFO - attempting initial heartbeat
2021-12-01 16:00:40,643 [66:BE87CC23] WARN - Ping communication error
o.s.emSDK.agent.comm.exception.ConnectException [Failure connecting to

Even wget fails with the message “No route to host.”

-bash-4.2$ wget https://192.168.56.123:4903/empbs/upload --no-check-certificate
--2021-12-01 15:59:19--  https://192.168.56.123:4903/empbs/upload
Connecting to 192.168.56.123:4903... failed: No route to host.

Both OMS version and Heartbeat to OMS shows null value 

-bash-4.2$ /u01/app/oracle/agent/agent_13.4.0.0.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 13.4.0.0.0
OMS Version            : (unknown)     --> *************************
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/agent/agent_inst
Agent Log Directory    : /u01/app/oracle/agent/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/agent/agent_13.4.0.0.0
Core JAR Location      : /u01/app/oracle/agent/agent_13.4.0.0.0/jlib
Agent Process ID       : 2963
Parent Process ID      : 2896
Started at             : 2021-12-01 18:18:50
Started by user        : oracle
Operating System       : Linux version 4.1.12-61.1.18.el7uek.x86_64 (amd64)
Number of Targets      : (none)
Last Reload            : (none)
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload           : 6
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 78.28%
Collection Status                            : Collections enabled
Heartbeat Status                             : OMS is unreachable
Last attempted heartbeat to OMS              : 2021-12-01 18:27:00
Last successful heartbeat to OMS             : (none)  --> ******************************
Next scheduled heartbeat to OMS              : 2021-12-01 18:27:30

---------------------------------------------------------------
Agent is Running and Ready

Solution:

Check ping,telnet to port and wget from Agent server to OMS server

http://alphaoragroup.com/2021/12/01/no-route-to-host/

Follow below steps

--Stop agent

-bash-4.2$ /u01/app/oracle/agent/agent_13.4.0.0.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.

--Secure agent

-bash-4.2$ /u01/app/oracle/agent/agent_13.4.0.0.0/bin/emctl secure agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Agent is already stopped...   Done.
Securing agent...   Started.
Enter Agent Registration Password :
Securing agent...   Successful.

--Start agent

-bash-4.2$ /u01/app/oracle/agent/agent_13.4.0.0.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation.  All rights reserved.
Starting agent ......... started.

Verify if both OMS and agent URL are same

--Agent server
-bash-4.2$ /u01/app/oracle/agent/agent_inst/bin/emctl status agent |grep 'URL'

--OMS server
[oracle@oem ~]$ emctl status oms -details|grep 'URL'

This should solve the issue