Day: January 22, 2018

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

  1. The database is just migrated from old ORACLE_HOME to a new ORACLE_HOME.
  2. 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).

Advertisement

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;

Pluggable database altered.

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