Make DBA Life Easy

12c opatchauto : Prerequisite check “CheckApplicable” failed

It is a good practice to copy and unzip GI patches with grid user

Using “opatchauto” to apply Jan2018 GI PSU onto 12R1 GI HOME, got “Prerequisite check “CheckApplicable” failed errors.

 # /u01/app/12.1.0.2/grid/OPatch/opatchauto apply /tmp/27010872 
   -oh /u01/app/12.1.0.2/grid
...
..
.
Bringing down CRS service on home /u01/app/12.1.0.2/grid
Prepatch operation log file location: /u01/app/12.1.0.2/grid/
 cfgtoollogs/crsconfig/crspatch_racnode1_2018-01-23_03-52-31PM.log
CRS service brought down successfully on home /u01/app/12.1.0.2/grid

Start applying binary patch on home /u01/app/12.1.0.2/grid
Failed while applying binary patches on home /u01/app/12.1.0.2/grid

Execution of [OPatchAutoBinaryAction] patch action failed, check log 
for more details. Failures:
Patch Target : racnode1->/u01/app/12.1.0.2/grid Type[crs]
Details: [
---------------------------Patching Failed--------------------------
Command execution failed during patching in home: /u01/app/12.1.0.2/
grid, host: racnode1.
Command failed: /u01/app/12.1.0.2/grid/OPatch/opatchauto apply 
 /tmp/27010872 -oh /u01/app/12.1.0.2/grid -target_type cluster 
 -binary -invPtrLoc /u01/app/12.1.0.2/grid/oraInst.loc -jre 
/u01/app/12.1.0.2/grid/OPatch/jre -persistresult /u01/app/12.1.0.2/
grid/OPatch/auto/dbsessioninfo/sessionresult_racnode1_crs.ser 
-analyzedresult /u01/app/12.1.0.2/grid/OPatch/auto/dbsessioninfo/
sessionresult_analyze_racnode1_crs.ser

Command failure output:
==Following patches FAILED in apply:

Patch: /tmp/27010872/26925218
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/
opatch2018-01-23_15-53-43PM_1.log
Reason:Failed during Patching: oracle.opatch.opatchsdk.OPatchException:
Prerequisite check "CheckApplicable" 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 Tue Jan 23 15:56:55 2018
Time taken to complete the session 6 minutes, 39 seconds

Chech opatch logfile:

[Jan 23, 2018 3:56:55 PM] [INFO] Space Needed : 3191.113MB
[Jan 23, 2018 3:56:55 PM] [INFO] Prereq checkPatchApplicableOnCurrentPlatform 
Passed for patch : 26925218
[Jan 23, 2018 3:56:55 PM] [INFO] Patch 26925218:
 onewaycopyAction : Source File "/tmp/27010872/26925218/files/crs/install
/dropdb.pl" does not exists or is not readable
 'oracle.crs, 12.1.0.2.0': Cannot copy file from 'dropdb.pl' to 
'/u01/app/12.1.0.2/grid/crs/install/dropdb.pl'
[Jan 23, 2018 3:56:55 PM] [INFO] Prerequisite check "CheckApplicable" failed.
 The details are:

Patch 26925218:
 onewaycopyAction : Source File "/tmp/27010872/26925218/files/crs/
install/dropdb.pl" does not exists or is not readable
 'oracle.crs, 12.1.0.2.0': Cannot copy file from 'dropdb.pl' to 
'/u01/app/12.1.0.2/grid/crs/install/dropdb.pl'
[Jan 23, 2018 3:56:55 PM] [SEVERE] OUI-67073:UtilSession failed:
 Prerequisite check "CheckApplicable" failed.
[Jan 23, 2018 3:56:55 PM] [INFO] Finishing UtilSession at Tue Jan 23 15:56:55 AEDT 2018
[Jan 23, 2018 3:56:55 PM] [INFO] Log file location: /u01/app/12.1.0.2
/grid/cfgtoollogs/opatchauto/core/opatch/opatch2018-01-23_15-53-43PM_1.log

Check “dropdb.pl” file from unziped patch. the owner is oracle.

# ls -ltr /tmp/27010872/26925218/files/crs/install/dropdb.pl
-rwx------ 1 oracle oinstall 3541 Jan 6 07:48 /tmp/27010872/26925218/
                                          files/crs/install/dropdb.pl

The patch file was unzipped by RAC user ‘oracle’ instead of GI owner ‘grid’. Change this file owner to grid, and run “opatchauto resume” to continue the patching successfully.

#chown grid /tmp/27010872/26925218/files/crs/install/dropdb.pl
#/u01/app/12.1.0.2/grid/OPatch/opatchauto resume

REFERENCES:

12c opatchauto: Prerequisite check “CheckApplicable” failed (Doc ID 1937982.1)

High CPU By /usr/bin/ssh -o StrictHostKeyChecking=no -o BatchMode=yes -o ConnectTimeout=5 /sbin/ifconfig -a

SYMPTOM

In 12.1.0.2 GI/RAC environment, there are a couple of processes consuming high CPU.

$ ps -ef|grep ifconfig
root 18941 1 0 06:25 ? 00:00:00 sh -c /bin/su -l grid -c "/usr/bin/ssh -o StrictHostKeyChecking=no -o BatchMode=yes -o ConnectTimeout=5 RACTEST2 /sbin/ifconfig -a" 2>&1
root 18942 18941 99 06:25 ? 06:07:08 /bin/su -l grid -c /usr/bin/ssh -o StrictHostKeyChecking=no -o BatchMode=yes -o ConnectTimeout=5 RACTEST2 /sbin/ifconfig -a
grid 26928 23166 0 12:32 pts/1 00:00:00 grep ifconfig
root 62153 1 0 Jan23 ? 00:00:00 sh -c /bin/su -l grid -c "/usr/bin/ssh -o StrictHostKeyChecking=no -o BatchMode=yes -o ConnectTimeout=5 RACTEST2 /sbin/ifconfig -a" 2>&1
root 62154 62153 99 Jan23 ? 14:29:31 /bin/su -l grid -c /usr/bin/ssh -o StrictHostKeyChecking=no -o BatchMode=yes -o ConnectTimeout=5 RACTEST2 /sbin/ifconfig -a
root 77170 1 0 10:30 ? 00:00:00 sh -c /bin/su -l grid -c "/usr/bin/ssh -o StrictHostKeyChecking=no -o BatchMode=yes -o ConnectTimeout=5 RACTEST2 /sbin/ifconfig -a" 2>&1
root 77171 77170 99 10:30 ? 02:02:37 /bin/su -l grid -c /usr/bin/ssh -o StrictHostKeyChecking=no -o BatchMode=yes -o ConnectTimeout=5 RACTEST2 /sbin/ifconfig -a

$top
..
.
 PID   USER PR NI VIRT RES  SHR  S %CPU %MEM TIME+ COMMAND
 62154 root 25 0 98.8m 1392 1104 R 100.0 0.0 851:33.36 su
 18942 root 25 0 98.8m 1400 1104 R 99.9  0.0 349:10.86 su
 77171 root 25 0 98.8m 1404 1104 R 99.9  0.0 104:39.33 su
 ..
 .

CAUSES

As per Oracle ID 2340905.1, it is a Bug 24692439 : LNX64-12.2-DIAGSNAP: AUXILIARY CMDS GENERATED BY DIAGSNAP WOULD HOG CPU FOREVER.

It is fixed in 18.1.

WORKAROUND

1)as GI owner:

$ oclumon manage -disable diagsnap
Diagsnap option is successfully Disabled on RACTEST1
Diagsnap option is successfully Disabled on RACTEST2
Successfully Disabled diagsnap

2) kill the existing “su” processes.

#kill -9 77170

....

datapatch -verbose Fails with Error “patch xxxxxxxx: Archived patch directory is empty”

Always keeps ORACLE_HOME applied patches consistent in situations like cloning ORALE_HOME, switching database role in DataGuard

SYMPTOM

“datapatch -verbose” failes on 12.1.0.2 database with following errors:

$ ./datapatch -verbose -skip_upgrade_check
...
..
.
Error: prereq checks failed!
 patch 22139226: Archived patch directory is empty
Prereq check failed, exiting without installing any patches.
...
..
.

Check $ORACLE_HOME/sqlpatch, there is no files for patch 22139226 to be used for rollback.

$ ls -ltr $ORACLE_HOME/sqlpatch|grep 22139226
$

CAUSE

The database is just migrated from old ORACLE_HOME to a new ORACLE_HOME.
The database is just switched over or failed over. The applied patches are different between ORACLE_HOME of primary and standby.

SOLUTION

Copy missing patch files from the old ORACLE_HOME, or from standby ORACLE_HOME to primary ORACLE_HOME.

REFERENCE

datapatch -verbose Fails with Error :” Patch xxxxxx: Archived Patch Directory Is Empty” (Doc ID 2235541.1).

How to Remove PDB From Restricted Mode

Run “datapatch” might remove PDB from restricted mode

1) Check PDB status.

SQL> select INST_ID,NAME,OPEN_MODE,RESTRICTED 
     from gv$pdbs order by 1,2;

INST_ID   NAME        OPEN_MODE  RES
-------- ------------ ---------- ---
 1       RACTESTPDB   READ WRITE YES
 1       PDB$SEED     READ ONLY  NO
 2       RACTESTPDB   READ WRITE YES
 2       PDB$SEED     READ ONLY  NO

2) Check PDB_PLUG_IN_VIOLATIONS.

SQL> select status, message, action 
     from   pdb_plug_in_violations 
     where  status !='RESOLVED';

STATUS      MESSAGE       ACTION
---------   ------------ ------------
PNDING     PSU bundle    Call datapatch.
           patch 180116 
           (DATABASE PATCH
           SET UPDATE
           12.1.0.2.180116):
           Installed in the 
           CDB but not in 
           the PDB.
PENDING     ...
            ..
            .

3) Rerun “datapatch” as per “run datapatch”.

4)Bounce all instances and pdbs.

$srvctl stop database -d RACTEST;

$srvctl start database -d RACTEST;

SQL> alter pluggable database RACTESTPDB open instances=all;

Pluggable database altered.

SQL> select INST_ID,NAME,OPEN_MODE,RESTRICTED 
     from gv$pdbs order by 1,2;

INST_ID  NAME         OPEN_MODE  RES
-------- ------------ ---------- ---
 1       RACTESTPDB   READ WRITE NO
 1       PDB$SEED     READ ONLY  NO
 2       RACTESTPDB   READ WRITE NO
 2       PDB$SEED     READ ONLY  NO

SQL> alter pluggable database RACTESTPDB save state instances=all;

Pluggable database altered.

SQL>  select CON_ID,CON_NAME,INSTANCE_NAME,state, restricted from  DBA_PDB_SAVED_STATES;

-- make sure PDB' restricted=NO

The pluggable databases that need to be patched must be in upgrade

Normally “datapatch” for OJVM requires database in UPGRADE mode

After applied PSU and OJVM binary patches, and run “datapatch”, then get error “The pluggable databases that need to be patched must be in upgrade mode”.

$ ./datapatch -verbose
...
..
.
 The following patches will be rolled back:
 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016))
 The following patches will be applied:
 27001733 (Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018))
 26925311 (DATABASE PATCH SET UPDATE 12.1.0.2.180116)

Error: prereq checks failed!
 patch 22139226: The pluggable databases that need to be patched must be in upgrade mode
Prereq check failed, exiting without installing any patches.

...
..
.

The workaround is to add “skip_upgrade_check” option for “datapatch” command:

$ ./datapatch -verbose -skip_upgrade_check
SQL Patching tool version 12.1.0.2.0 Production on Fri Jan 19 17:33:18 2018
Copyright (c) 2012, 2016, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_74462_2018_01_19_17_33_18/sqlpatch_invocation.log

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

Current state of SQL patches:
Patch 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)):
 Installed in RACTESTPDB only
Patch 27001733 (Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018)):
 Installed in the binary registry and CDB$ROOT PDB$SEED
Bundle series PSU:
 ID 180116 in the binary registry and ID 180116 in PDB CDB$ROOT, ID 180116 in PDB PDB$SEED, ID 160419 in PDB RACTESTPDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED
 Nothing to roll back
 Nothing to apply
 For the following PDBs: RACTESTPDB
 The following patches will be rolled back:
 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016))
 The following patches will be applied:
 27001733 (Database PSU 12.1.0.2.180116, Oracle JavaVM Component (JAN2018))
 26925311 (DATABASE PATCH SET UPDATE 12.1.0.2.180116)

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

Validating logfiles...
Patch 22139226 rollback (pdb RACTESTPDB): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22139226/19729684/22139226_rollback
Patch 27001733 apply (pdb RACTESTPDB): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27001733/21728084/27001733_apply_LG
Patch 26925311 apply (pdb RACTESTPDB): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26925311/21850549/26925311_apply_LG
SQL Patching tool complete on Fri Jan 19 17:37:33 2018


SQL> select PATCH_ID,VERSION,ACTION,STATUS,DESCRIPTION 
     from dba_registry_sqlpatch order by ACTION_TIME;
PATCH_ID  VERSION ACTION     STATUS  DESCRIPTION
---------- --------------    ------- --------------- ----------------------------------------------------------------------------------------------------
 22139226 12.1.0.2 APPLY     SUCCESS Database PSU 12.1.0.2.160119, 
                                     Oracle JavaVM Component (Jan2016)
 22291127 12.1.0.2 APPLY     SUCCESS Database Patch Set Update : 
                                     12.1.0.2.160419 (22291127)
 22139226 12.1.0.2 ROLLBACK  SUCCESS Database PSU 12.1.0.2.160119, 
                                     Oracle JavaVM Component (Jan2016)
 27001733 12.1.0.2 APPLY     SUCCESS Database PSU 12.1.0.2.180116, 
                                     Oracle JavaVM Component (JAN2018)
 26925311 12.1.0.2 APPLY     SUCCESS DATABASE PATCH SET UPDATE 
                                     12.1.0.2.180116