Month: March 2020

Cleanup .patch_storage Directory under ORACLE_HOME

Now and then, DBA might experience disk space issues while patching and upgrading GI or DB. Under ORACLE_HOME there is a .patch_storage directory which uses huge disk space and stores all the patches already applied onto ORACLE_HOME.  Some are backup files and directories.

Following are the common methods to solve the disk space issues under ORACLE_HOME due to huge .patch_storage directory size.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

CLSRSC-488: Patching the Grid Infrastructure Management Repository database failed

opatchauo failed when applying GI release update. 

[root@racnode1 ~]# /u01/app/12.2.0.1/grid/OPatch/opatchauto 
                     apply /media/sf_Software/12201-190115/30501932
...
..
.
CRS-4123: Oracle High Availability Services has been started.
Oracle Clusterware active version on the cluster is [12.2.0.1.0]. 
                    The cluster upgrade state is [ROLLING PATCH]. 
The cluster active patch level is [1477401644].
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'racnode1'
CRS-2676: Start of 'ora.drivers.acfs' on 'racnode1' succeeded
SQL Patching tool version 12.2.0.1.0 Production on Sat Mar 7 15:46:51 2020
Copyright (c) 2012, 2019, Oracle. All rights reserved.

Connecting to database...OK
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
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED GIMR_DSCREP_10
The following patches will be rolled back:
29708478 (DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190716)
The following patches will be applied:
30593149 (DATABASE JAN 2020 RELEASE UPDATE 12.2.0.1.200114)

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

Validating logfiles...
Patch 29708478 rollback (pdb CDB$ROOT): WITH ERRORS
logfile: /u01/app/grid/cfgtoollogs/sqlpatch/29708478/22988073/
   29708478_rollback__MGMTDB_CDBROOT_2020Mar07_15_47_52.log (errors)
Error at line 25435: Warning: Package Body created with compilation errors.
Error at line 25442: 24/12 PLS-00323: subprogram or cursor 'GET_OPTIONAL_PRIVILEGES' is
Error at line 25446: 2727/5 PL/SQL: Statement ignored
Error at line 25447: 2727/21 PLS-00306: wrong number or types of arguments in call to
Error at line 36437: Warning: Package Body created with compilation errors.
Error at line 36444: 18788/3 PL/SQL: Statement ignored
Error at line 36445: 18788/6 PLS-00201: identifier
Error at line 36448: 18792/3 PL/SQL: Statement ignored
Error at line 36449: 18792/3 PLS-00201: identifier
Patch 29708478 rollback (pdb PDB$SEED): WITH ERRORS
logfile: /u01/app/grid/cfgtoollogs/sqlpatch/29708478/22988073/
   29708478_rollback__MGMTDB_PDBSEED_2020Mar07_15_50_23.log (errors)
Error at line 36419: Warning: Package Body created with compilation errors.
Error at line 36426: 18788/3 PL/SQL: Statement ignored
Error at line 36427: 18788/6 PLS-00201: identifier
Error at line 36430: 18792/3 PL/SQL: Statement ignored
Error at line 36431: 18792/3 PLS-00201: identifier
Patch 29708478 rollback (pdb GIMR_DSCREP_10): WITH ERRORS
logfile: /u01/app/grid/cfgtoollogs/sqlpatch/29708478/22988073/
29708478_rollback__MGMTDB_GIMR_DSCREP_10_2020Mar07_15_50_23.log (errors)
Error at line 52094: Warning: Package Body created with compilation errors.
Error at line 52101: 18788/3 PL/SQL: Statement ignored
Error at line 52102: 18788/6 PLS-00201: identifier
Error at line 52105: 18792/3 PL/SQL: Statement ignored
Error at line 52106: 18792/3 PLS-00201: identifier

Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/grid/cfgtoollogs/sqlpatch/sqlpatch_9475_2020_03_07_15_46_51/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Sat Mar 7 15:54:51 2020
2020/03/07 15:54:51 CLSRSC-488: Patching the Grid Infrastructure 
                            Management Repository database failed.

After fixing the cause of failure Run opatchauto resume

]
OPATCHAUTO-68061: The orchestration engine failed.
OPATCHAUTO-68061: The orchestration engine failed with return code 1
OPATCHAUTO-68061: Check the log for more details.
OPatchAuto failed.

OPatchauto session completed at Sat Mar 7 15:54:52 2020
Time taken to complete the session 22 minutes, 24 seconds

opatchauto failed with error code 42
[root@racnode1 ~]#

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Apply GI Jan 2020 Release Update 12.2.0.1.200114 ( RU) Patch

This exercise is to apply Patch 30501932 – GI Jan 2020 Release Update 12.2.0.1.200114

Here is the GI environment to be patched:

GI HOME:  12.2.0.1.190716
DB HOMES: 12.2.0.1.190716

Patches downloaded :

Patch 30501932 - GI Jan 2020 Release Update 12.2.0.1.200114
Patch NumberDescriptionApplicable Homes
30593149Database Jan 2020 Release Update 12.2.0.1.200114Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
30585969OCW Jan 2020 Release Update 12.2.0.1.200114Both DB Homes and Grid Home
30586063ACFS Jan 2020 Release Update 12.2.0.1.200114Only Grid Home
26839277DBWLM Release Update 12.2.0.1.170913Only Grid Home
30591794Tomcat Release Update 12.2.0.1.0Only Grid Home
Patch 30501932

1) Check and install the latest OPatch utility , which should be greater than 12.2.0.1.17 on all RAC nodes for both GI and DB HOMES.

[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.18

OPatch succeeded.
[grid@racnode2 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.18

OPatch succeeded.
[grid@racnode2 ~]$

2) Run OPatch Conflict Check.

First run as GI owner grid user:

[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/30501932/30593149
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/30501932/30585969 
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/30501932/30586063 
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/30501932/26839277
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/30501932/30591794

Now run as DB owner grid user:

[oracle@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/30501932/30593149
[oracle@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/30501932/30585969

3)Run OPatch SystemSpace Check:

Check if enough free space is available on the ORACLE_HOME filesystem for the patches to be applied as given below:

  • For Grid Infrastructure Home, as home user grid:
    1. Create file /tmp/patch_list_gihome.txt with the following content: 
      [grid@racnode1 ~]$ cat /tmp/patch_list_gihome.txt
/media/sf_Software/12201-190115/29708822/29708478
/media/sf_Software/12201-190115/29708822/29263382
/media/sf_Software/12201-190115/29708822/28864846
/media/sf_Software/12201-190115/29708822/26839277
/media/sf_Software/12201-190115/29708822/28566910
    2. Run the opatch command to check if enough free space is available in the Grid Infrastructure Home: 
      [grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt
Oracle Interim Patch Installer version 12.2.0.1.18
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/12.2.0.1/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/12.2.0.1/grid/oraInst.loc
OPatch version    : 12.2.0.1.18
OUI version       : 12.2.0.1.4
Log file location : /u01/app/12.2.0.1/grid/cfgtoollogs/opatch/opatch2020-03-07_15-12-59PM_1.log

Invoking prereq "checksystemspace"

Prereq "checkSystemSpace" passed.

OPatch succeeded.

      For Database home, as home user oracle:

      1. Create file /tmp/patch_list_dbhome.txt with the following content: 
        [oracle@racnode1 ~]$ cat /tmp/patch_list_dbhome.txt
/media/sf_Software/12201-190115/30501932/30593149
/media/sf_Software/12201-190115/30501932/30585969
[oracle@racnode1 ~]$
      2. Run opatch command to check if enough free space is available in the Database Home: 
        [oracle@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt
Oracle Interim Patch Installer version 12.2.0.1.18
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.18
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2020-03-07_15-16-25PM_1.log

Invoking prereq "checksystemspace"

Prereq "checkSystemSpace" passed.

OPatch succeeded.
[oracle@racnode1 ~]$

4) One-off patch conflict detection in both the 12.2.0.1 GI home and the 12.2.0.1 DB homes.

  • In case you are applying the patch, run this command: 
    [root@racnode1 ~]# /u01/app/12.2.0.1/grid/OPatch/opatchauto apply /media/sf_Software/12201-190115/30501932 -analyze 
[root@racnode1 ~]#
  • In case you are rolling back the patch, run this command: 
    #/u01/app/12.2.0.1/grid/OPatch/opatchauto rollback /media/sf_Software/12201-190115/30501932 -analyze
#

5) Patch both 12.2.0.2 GI and 12.2.0.1 DB home by running “opatchauto” as root user.

[root@racnode1 ~]# /u01/app/12.2.0.1/grid/OPatch/opatchauto  
                apply /media/sf_Software/12201-190115/30501932

6) Please repeat step 1 to 5 on node 2.

7)Run “datapatch” or post patch SQL as per instructions, if ‘opatchauto’ is not used. In this situation, this step is not required because we have used opatchauto to patch both GI and DB HOME.

On the node  last patched, check the log. Please note if a PDB is not OPEN,  the post patch SQL will not be applied onto this PDB, it has to be manually applied later.

[root@racnode2 ~]#  ls -ltr /u01/app/oracle/cfgtoollogs/sqlpatch/30593149/23299902
total 18556
-rw-r--r-- 1 oracle oinstall 6177501 Mar  7 17:31 30593149_apply_RACTESTB_CDBROOT_2020Mar07_17_29_24.log
-rw-r--r-- 1 oracle oinstall 6409294 Mar  7 17:34 30593149_apply_RACTESTB_PDBSEED_2020Mar07_17_32_40.log
-rw-r--r-- 1 oracle oinstall 6409298 Mar  7 17:35 30593149_apply_RACTESTB_RACTESTBPDB_2020Mar07_17_32_40.log
[root@racnode2 ~]#

8) Check the patches applied.

a) Run ‘opatch lsinventory’ against both GI and DB homes.

As GI Home owner grid user:

[grid@racnode1]$ opatch lsinventory
...
..
Oracle Grid Infrastructure 12c                                       12.2.0.1.0
There are 1 products installed in this Oracle Home.


Interim patches (5) :

Patch  30593149     : applied on Sat Mar 07 15:42:08 AEDT 2020
Unique Patch ID:  23299902
Patch description:  "Database Jan 2020 Release Update : 12.2.0.1.200114 (30593149)"
   Created on 28 Dec 2019, 10:44:46 hrs PST8PDT
   Bugs fixed:
...
..
.

As DB Home owner oracle user:

[oracle@racnode1]$ opatch lsinventory
..
..
Interim patches (2) :

Patch  30593149     : applied on Sat Mar 07 15:38:38 AEDT 2020
Unique Patch ID:  23299902
Patch description:  "Database Jan 2020 Release Update : 12.2.0.1.200114 (30593149)"
   Created on 28 Dec 2019, 10:44:46 hrs PST8PDT
   Bugs fixed:
     30125995, 8480838, 8932139, 12763598, 13554903, 14221306, 14690846
...
..

b) Query any DB database .

SQL>  select PATCH_ID, VERSION, ACTION, STATUS, DESCRIPTION 
      from dba_registry_sqlpatch;

  PATCH_ID VERSION              ACTION          STATUS                    DESCRIPTION
---------- -------------------- --------------- ------------------------- ----------------------------------------------------------------------------------------------------
  28822515 12.2.0.1             APPLY           SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
  28822515 12.2.0.1             ROLLBACK        SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
  29230950 12.2.0.1             APPLY           SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190416
  29230950 12.2.0.1             ROLLBACK        SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190416
  29708478 12.2.0.1             APPLY           SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190716
  29708478 12.2.0.1             ROLLBACK        WITH ERRORS               DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190716
  30593149 12.2.0.1             APPLY           SUCCESS                   DATABASE JAN 2020 RELEASE UPDATE 12.2.0.1.200114

7 rows selected.

c) Query -MGMTDB:

SQL> show con_name

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

SQL> show parameter instance_name;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string -MGMTDB
SQL>

SQL> select PATCH_ID, VERSION, ACTION, STATUS, DESCRIPTION 
     from dba_registry_sqlpatch;

 PATCH_ID VERSION              ACTION          STATUS                    ACTION_TIME                                                                 DESCRIPTION
---------- -------------------- --------------- ------------------------- --------------------------------------------------------------------------- ------------------------------------------------------------
  28822515 12.2.0.1             APPLY           SUCCESS                   27-FEB-20 10.03.25.304409 PM                                                DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
  28822515 12.2.0.1             ROLLBACK        SUCCESS                   05-MAR-20 10.21.16.554049 PM                                                DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
  29230950 12.2.0.1             APPLY           SUCCESS                   05-MAR-20 10.21.17.546585 PM                                                DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190416
  29230950 12.2.0.1             ROLLBACK        SUCCESS                   07-MAR-20 11.40.37.212051 AM                                                DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190416
  29708478 12.2.0.1             APPLY           SUCCESS                   07-MAR-20 11.40.39.815732 AM                                                DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190716
  29708478 12.2.0.1             ROLLBACK        WITH ERRORS               07-MAR-20 03.54.44.017473 PM                                                DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190716
  30593149 12.2.0.1             APPLY           SUCCESS                   07-MAR-20 03.54.45.345308 PM                                                DATABASE JAN 2020 RELEASE UPDATE 12.2.0.1.200114
  29708478 12.2.0.1             ROLLBACK        SUCCESS                   07-MAR-20 04.54.54.703065 PM                                                DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190716

8 rows selected.

9) Recompile invalid objects if any.

SQL> select owner, count(*) 
     from dba_objects 
     where status!='VALID' group by owner;

no rows selected

If the OJVM RU or RUR is also installed, you may see invalid objects after execution of datapatch in the previous step. If this is the case, run utlrp.sql to re-validate these objects.

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

10)  Upgrade Oracle Recovery Manager Catalog.

If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it. The UPGRADE CATALOG command must be entered twice to confirm the upgrade.

$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
RMAN> UPGRADE CATALOG;
RMAN> EXIT;

Apply GI 12.2.0.1.190716 Release Update Revision 2 ( RUR) Patch

This exercise is to apply Patch 29708822 – GI Jan 2019 Release Update Revision 12.2.0.1.190716.

Here is the GI environment to be patched:

GI HOME:  12.2.0.1.190416
DB HOMES: 12.2.0.1.190416

Patches downloaded :

Patch 29708822 - GI Jan 2019 Release Update Revision 12.2.0.1.190716
Patch Number Description Applicable Homes
29708478 Database Jan 2019 Release Update Revision 12.2.0.1.190716 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
29263382 OCW Jan 2019 Release Update Revision 12.2.0.1.190416 Both DB Homes and Grid Home
28864846 ACFS Jan 2019 Release Update 12.2.0.1.190115 Only Grid Home
26839277 DBWLM Release Update 12.2.0.1.170913 Only Grid Home
28566910 Tomcat Release Update 12.2.0.1.0 Only Grid Home

1) Check and install the latest OPatch utility , which should be greater than 12.2.0.1.14 on all RAC nodes .

[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.18

OPatch succeeded.
[grid@racnode2 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.18

OPatch succeeded.
[grid@racnode2 ~]$

2) Run OPatch Conflict Check.

First run as GI owner grid user:

[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29708822/29708478
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29708822/29263382
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29708822/28864846
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29708822/26839277
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29708822/28566910

Now run as DB owner grid user:

[oracle@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29708822/29708478
[oracle@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29708822/29263382

3)Run OPatch SystemSpace Check:

Check if enough free space is available on the ORACLE_HOME filesystem for the patches to be applied as given below:

  • For Grid Infrastructure Home, as home user grid:
    1. Create file /tmp/patch_list_gihome.txt with the following content: 
      [grid@racnode1 ~]$ cat /tmp/patch_list_gihome.txt
/media/sf_Software/12201-190115/29708822/29708478
/media/sf_Software/12201-190115/29708822/29263382
/media/sf_Software/12201-190115/29708822/28864846
/media/sf_Software/12201-190115/29708822/26839277
/media/sf_Software/12201-190115/29708822/28566910
    2. Run the opatch command to check if enough free space is available in the Grid Infrastructure Home: 
      [grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt
Oracle Interim Patch Installer version 12.2.0.1.18
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/12.2.0.1/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/12.2.0.1/grid/oraInst.loc
OPatch version    : 12.2.0.1.18
OUI version       : 12.2.0.1.4
Log file location : /u01/app/12.2.0.1/grid/cfgtoollogs/opatch/opatch2020-03-07_11-08-09AM_1.log

Invoking prereq "checksystemspace"

Prereq "checkSystemSpace" passed.
OPatch succeeded.

      For Database home, as home user oracle:

      1. Create file /tmp/patch_list_dbhome.txt with the following content: 
        [oracle@racnode1 ~]$ cat /tmp/patch_list_dbhome.txt
/media/sf_Software/12201-190115/29708822/29708478
/media/sf_Software/12201-190115/29708822/29263382
[oracle@racnode1 ~]$
      2. Run opatch command to check if enough free space is available in the Database Home: 
        [oracle@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt
Oracle Interim Patch Installer version 12.2.0.1.18
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/12.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.18
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2020-03-07_11-11-23AM_1.log

Invoking prereq "checksystemspace"

Prereq "checkSystemSpace" passed.

OPatch succeeded.
[oracle@racnode1 ~]$

4) Check for conflicts in both the 12.2.0.1 GI home and the 12.2.0.1 DB homes.

  • In case you are applying the patch, run this command: 
    [root@racnode1 ~]# /u01/app/12.2.0.1/grid/OPatch/opatchauto apply /media/sf_Software/12201-190115/29708822 -analyze 
[root@racnode1 ~]#
  • In case you are rolling back the patch, run this command: 
    #/u01/app/12.2.0.1/grid/OPatch/opatchauto rollback /media/sf_Software/12201-190115/29708822 -analyze

5) Patch both 12.2.0.2 GI and 12.2.0.1 DB home by running “opatchauto” as root user.

[root@racnode1 ~]# /u01/app/12.2.0.1/grid/OPatch/opatchauto  
                apply /media/sf_Software/12201-190115/29708822

6) Please repeat step 1 to 5 on node 2.

7)Run “datapatch” or post patch SQL as per instructions, if ‘opatchauto’ is not used. In this situation, this step is not required because we have used opatchauto to patch both GI and DB HOME.

On the node was last patched, check the log. Please note if a PDB is not OPEN,  the post patch SQL will not be applied onto this PDB, it has to be manually applied later.

[root@racnode2 ~]# ls -ltr /u01/app/oracle/cfgtoollogs/sqlpatch/29708478/22988073
total 14120
-rw-r--r-- 1 oracle oinstall 4783857 Mar  7 12:08 29708478_apply_RACTESTB_CDBROOT_2020Mar07_12_07_58.log
-rw-r--r-- 1 oracle oinstall 4833929 Mar  7 12:11 29708478_apply_RACTESTB_RACTESTBPDB_2020Mar07_12_10_08.log
-rw-r--r-- 1 oracle oinstall 4833925 Mar  7 12:11 29708478_apply_RACTESTB_PDBSEED_2020Mar07_12_10_10.log
[root@racnode2 ~]#

8) Check the patches applied.

a) Run ‘opatch lsinventory’ against both GI and DB homes.

As GI Home owner grid user:

[grid@racnode1]$ opatch lsinventory
...
..
Oracle Grid Infrastructure 12c                                       12.2.0.1.0
There are 1 products installed in this Oracle Home.


Interim patches (5) :

Patch  29708478     : applied on Sat Mar 07 11:28:36 AEDT 2020
Unique Patch ID:  22988073
Patch description:  "Database Jan 2019 Release Update Revision : 12.2.0.1.190716 (29708478)"
   Created on 26 Jun 2019, 03:34:53 hrs PST8PDT
   Bugs fixed:
     8480838, 8932139, 13554903, 14221306, 14690846, 15931756, 16002385

As DB Home owner oracle user:

[oracle@racnode1]$ opatch lsinventory
..
..
Oracle Database 12c 12.2.0.1.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch 29708478 : applied on Sat Mar 07 11:27:44 AEDT 2020
Unique Patch ID: 22988073
Patch description: "Database Jan 2019 Release Update Revision : 12.2.0.1.190716 (29708478)"
Created on 26 Jun 2019, 03:34:53 hrs PST8PDT
Bugs fixed:
...
..

b) Query database .

SQL>  select PATCH_ID, VERSION, ACTION, STATUS, DESCRIPTION 
      from dba_registry_sqlpatch;

  PATCH_ID VERSION              ACTION          STATUS                    DESCRIPTION
---------- -------------------- --------------- ------------------------- ----------------------------------------------------------------------------------------------------
  28822515 12.2.0.1             APPLY           SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
  28822515 12.2.0.1             ROLLBACK        SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
  29230950 12.2.0.1             APPLY           SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190416
  29230950 12.2.0.1             ROLLBACK        SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190416
  29708478 12.2.0.1             APPLY           SUCCESS                   DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190716

9) Recompile invalid objects if any.

SQL> select owner, count(*) 
     from dba_objects 
     where status!='VALID' group by owner;

no rows selected

If the OJVM RU or RUR is also installed, you may see invalid objects after execution of datapatch in the previous step. If this is the case, run utlrp.sql to re-validate these objects.

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

Apply GI 12.2.0.1.190416 Release Update Revision 1 ( RUR) Patch

This exercise is to apply patch 29231084 – GI Jan 2019 Release Update Revision 12.2.0.1.190416

Here is the GI environment to be patched:

GI HOME:  12.2.0.1.190115
DB HOMES: 12.2.0.1.190115

Patches downloaded :

Patch 29231084 - GI Jan 2019 Release Update Revision 12.2.0.1.190416
29230950 Database Jan 2019 Release Update Revision 12.2.0.1.190416 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup.
29263382 OCW Jan 2019 Release Update Revision 12.2.0.1.190416 Both DB Homes and Grid Home
28864846 ACFS Jan 2019 Release Update 12.2.0.1.190115 Only Grid Home
26839277 DBWLM Release Update 12.2.0.1.170913 Only Grid Home
28566910 Tomcat Release Update 12.2.0.1.0 Only Grid Home

1) Check and install the latest OPatch utility , which should be greater than 12.2.0.1.14 on all RAC nodes .

[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.18

OPatch succeeded.
[grid@racnode2 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.18

OPatch succeeded.
[grid@racnode2 ~]$

2) Run OPatch Conflict Check.

First run as GI owner grid user:

[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29231084/29230950
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29231084/29263382
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29231084/28864846
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29231084/26839277
[grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29231084/28566910

Now run as DB owner grid user:

[oracle@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29231084/29230950 
[oracle@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /media/sf_Software/12201-190115/29231084/29263382

3)Run OPatch SystemSpace Check:

Check if enough free space is available on the ORACLE_HOME filesystem for the patches to be applied as given below:

  • For Grid Infrastructure Home, as home user grid:
    1. Create file /tmp/patch_list_gihome.txt with the following content: 
      % cat /tmp/patch_list_gihome.txt

[grid@racnode1 ~]$ cat /tmp/patch_list_gihome.txt
/media/sf_Software/12201-190115/29231084/29230950
/media/sf_Software/12201-190115/29231084/29263382
/media/sf_Software/12201-190115/29231084/28864846
/media/sf_Software/12201-190115/29231084/26839277
/media/sf_Software/12201-190115/29231084/28566910
    2. Run the opatch command to check if enough free space is available in the Grid Infrastructure Home: 
      [grid@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt
...
..
.
Invoking prereq "checksystemspace"

Prereq "checkSystemSpace" passed.

OPatch succeeded.
[grid@racnode1 ~]$

      For Database home, as home user oracle:

      1. Create file /tmp/patch_list_dbhome.txt with the following content: 
        [oracle@racnode1 ~]$ cat /tmp/patch_list_dbhome.txt
/media/sf_Software/12201-190115/29231084/29230950
/media/sf_Software/12201-190115/29231084/29263382
[oracle@racnode1 ~]$
      2. Run opatch command to check if enough free space is available in the Database Home: 
        [oracle@racnode1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt
...
..
.
Invoking prereq "checksystemspace"

Prereq "checkSystemSpace" passed.

OPatch succeeded.
[oracle@racnode1 ~]$

4) Check for conflicts in both the 12.2.0.1 GI home and the 12.2.0.1 DB homes.

  • In case you are applying the patch, run this command: 
    [root@racnode1 ~]# /u01/app/12.2.0.1/grid/OPatch/opatchauto apply /media/sf_Software/12201-190115/29231084 -analyze

[root@racnode1 ~]#
  • In case you are rolling back the patch, run this command: 
    #/u01/app/12.2.0.1/grid/OPatch/opatchauto rollback /media/sf_Software/12201-190115/29231084 -analyze

5) Patch both 12.2.0.2 GI and 12.2.0.1 DB home by running “opatchauto” as root user.

[root@racnode1 ~]# /u01/app/12.2.0.1/grid/OPatch/opatchauto  
                apply /media/sf_Software/12201-190115/29231084

6) Please repeat step 1 to 5 on node 2.

7)Run “datapatch” or post patch SQL as per instructions, if ‘opatchauto’ is not used. In this situation, this step is not required because we have used opatchauto to patch.

On the node was last patched, check the log :

[root@racnode2 ~]# [oracle@racnode2 OPatch]$  ls -ltr /u01/app/oracle/cfgtoollogs/sqlpatch/29230950/22819305/
total 9720
-rw-r--r-- 1 oracle oinstall 4587177 Mar  5 22:36 29230950_apply_RACTESTB_CDBROOT_2020Mar05_22_36_00.log
-rw-r--r-- 1 oracle oinstall 4833931 Mar  5 22:38 29230950_apply_RACTESTB_PDBSEED_2020Mar05_22_37_21.log
-rw-r--r-- 1 oracle oinstall  527239 Mar  5 23:01 29230950_apply_RACTESTB_RACTESTBPDB_2020Mar05_23_00_55.log

8) Check the patches applied.

a) Run ‘opatch lsinventory’ against both GI and DB homes.

As GI Home owner grid user:

[grid@racnode1]$ opatch lsinventory
...
..
Interim patches (5) :
Patch  29263382     : applied on Thu Mar 05 22:12:49 AEDT 2020
Unique Patch ID:  22704686
Patch description:  "OCW JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190416 (29263382)"
   Created on 18 Mar 2019, 04:41:32 hrs PST8PDT
   Bugs fixed:
     12816839, 13250991, 18701017, 20559126, 21477269, 21679331, 22322021
     22734786, 22986384, 22999793, 23170674, 23297046, 23301767, 23340259

As DB Home owner oracle user:

[oracle@racnode1]$ opatch lsinventory
..
..
Interim patches (2) :

Patch  29263382     : applied on Thu Mar 05 22:11:30 AEDT 2020
Unique Patch ID:  22704686
Patch description:  "OCW JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190416 (29263382)"
   Created on 18 Mar 2019, 04:41:32 hrs PST8PDT
   Bugs fixed:
     12816839, 13250991, 18701017, 20559126, 21477269, 21679331, 22322021
     22734786, 22986384, 22999793, 23170674, 23297046, 23301767, 23340259

...
..
Patch  29230950     : applied on Thu Mar 05 22:11:00 AEDT 2020
Unique Patch ID:  22819305
Patch description:  "Database Jan 2019 Release Update Revision : 12.2.0.1.190416 (29230950)"
   Created on 22 Mar 2019, 04:35:49 hrs PST8PDT
   Bugs fixed:

b) Query database .

SQL> select PATCH_ID, VERSION, ACTION, STATUS, DESCRIPTION 
     from dba_registry_sqlpatch;
SQL> select PATCH_ID, VERSION, ACTION, STATUS, DESCRIPTION 
     from dba_registry_sqlpatch;

  PATCH_ID VERSION              ACTION          STATUS    DESCRIPTION
---------- -------------------- --------------- --------- ------------------------------------------------------------
  28822515 12.2.0.1             APPLY           SUCCESS   DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
  28822515 12.2.0.1             ROLLBACK        SUCCESS   DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115
  29230950 12.2.0.1             APPLY           SUCCESS   DATABASE JAN 2019 RELEASE UPDATE REVISION 12.2.0.1.190416

9) Recompile invalid objects if any.

SQL> select owner, count(*) 
     from dba_objects 
     where status!='VALID' group by owner;

no rows selected

If the OJVM RU or RUR is also installed, you may see invalid objects after execution of datapatch in the previous step. If this is the case, run utlrp.sql to re-validate these objects.

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql