Using datapatch in a RAC env

Overview

  • Datapatch is the new tool that enables automation of post-patch SQL actions for RDBMS patches.
  • If we have a 3 node Rac cluster datapatch runs 3  jobs named LOAD_OPATCH_INVENTORY_1 ,LOAD_OPATCH_INVENTORY_2, LOAD_OPATCH_INVENTORY_3
  • This inventory updates requires that all RAC nodes are available ( even for Policy managed database )
  • Install Helper package from Node 1585814.1 : [ demo1.sql + demo2.sql ]
  • With 12c we have a SQL interface for quering patches (by reading lsinventory via PLSQL )
  • For patches that do not have post-patch SQL actions to be performed, calling datapatch is a no-op.
  • For patches that do have post-patch SQL instructions to be invoked on the database instance, datapatch will automatically   detect ALL pending actions (from one installed patch or multiple installed patches) and complete the actions as appropriate.

What should I do when the datapatch commands throws any error or warning ?

Rollable VS. Non-Rollable Patches: ( From Oracle Docs )
 - Patches are designed to be applied in either rolling mode or non-rolling mode.
 - If a patch is rollable, the patch has no dependency on the SQL script. 
   The database can be brought up without issue.

 OPatchauto succeeds with a warning on datapatch/sqlpatch.
  ->  For rollable patches:
        In-1gnore datapatch errors on node 1 - node().
        On the last node (node n), run datapatch again. You can cut and paste this command from the log file.
        If you still encounter datapatch errors on the last node, call Oracle Support or open a Service Request.

   -> For non-rollable patches:
        Bring down all databases and stacks manually for all nodes.
        Run opatchauto apply on every node.
        Bring up the stack and databases.
        Note that the databases must be up in order for datapatch to connect and apply the SQL.
        Manually run datapatch on the last node. 
        Note that if you do not run datapatch, the SQL for the patch will not be applied and you will not 
           benefit from the bug fix. In addition, you may encounter incorrect system behavior 
           depending on the changes the SQL is intended to implement.
        If datapatch continues to fail, you must roll back the patch. 
        Call Oracle Support for assistance or open a Service Request.

 

How to check the current patch level and reinstall a SQL patch ?

[oracle@gract1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Sun Jan 25 08:55:31 2015
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches: 19121550
Currently installed C Patches: 19121550
Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  Nothing to apply
Patch installation complete.  Total patches installed: 0
SQL Patching tool complete on Sun Jan 25 08:57:14 2015

--> Patch 19121550 is installed ( both parts C layer and SQL layer are installed )

Rollback the patch
[oracle@gract1 OPatch]$  ./datapatch -rollback 19121550
SQL Patching tool version 12.1.0.1.0 on Sun Jan 25 09:03:03 2015
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  The following patches will be rolled back: 19121550
  Nothing to apply
Installing patches...
Patch installation complete.  Total patches installed: 1
Validating logfiles...done
SQL Patching tool complete on Sun Jan 25 09:04:51 2015

Reapply the patch
oracle@gract1 OPatch]$  ./datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Sun Jan 25 09:06:55 2015
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches:               <-- Here we can see that SQL patch is not yet installed !
Currently installed C Patches: 19121550
Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied: 19121550
Installing patches...
Patch installation complete.  Total patches installed: 1
Validating logfiles...
Patch 19121550 apply: SUCCESS
  logfile: /u01/app/oracle/product/121/racdb/sqlpatch/19121550/19121550_apply_DW_2015Jan25_09_08_51.log (no errors)
  catbundle generate logfile: /u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DW_dw_GENERATE_2015Jan25_09_08_51.log (no errors)
  catbundle apply logfile: /u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DW_dw_APPLY_2015Jan25_09_08_53.log (no errors)
SQL Patching tool complete on Sun Jan 25 09:10:31 2015

Verify the current patch status 
SQL> select * from dba_registry_sqlpatch;
  PATCH_ID ACTION       STATUS       ACTION_TIME              DESCRIPTION
---------- --------------- --------------- ------------------------------ --------------------
LOGFILE
------------------------------------------------------------------------------------------------------------------------
  19121550 APPLY       SUCCESS       26-OCT-14 12.13.19.575484 PM   bundle:PSU
/u01/app/oracle/product/121/racdb/sqlpatch/19121550/19121550_apply_DW_2014Oct26_12_01_54.log

  19121550 ROLLBACK       SUCCESS       25-JAN-15 09.04.51.585648 AM   bundle:PSU
/u01/app/oracle/product/121/racdb/sqlpatch/19121550/19121550_rollback_DW_2015Jan25_09_04_43.log

  19121550 APPLY       SUCCESS       25-JAN-15 09.10.31.872019 AM   bundle:PSU
/u01/app/oracle/product/121/racdb/sqlpatch/19121550/19121550_apply_DW_2015Jan25_09_08_51.log

--> Here we can identify that we re-applied the SQL part of patch  19121550 at : 25-JAN-15 09.10.31

 

Using  Queryable Patch Inventory [ DEMOQP helper package ]

Overview DEMOQP helper package 
Install Helper package from Node 1585814.1 : [ demo1.sql + demo2.sql ] 
Have a short look on these package details:
SQL> desc DEMOQP
PROCEDURE CHECK_PATCH_INSTALLED
 Argument Name            Type            In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUGS                QOPATCH_LIST        IN
PROCEDURE COMPARE_CURRENT_DB
 Argument Name            Type            In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUGS                QOPATCH_LIST        IN
PROCEDURE COMPARE_RAC_NODE
 Argument Name            Type            In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NODE                VARCHAR2        IN
 INST                VARCHAR2        IN
FUNCTION GET_BUG_DETAILS RETURNS XMLTYPE
 Argument Name            Type            In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PATCH                VARCHAR2        IN
FUNCTION GET_DEMO_XSLT RETURNS XMLTYPE

Script to test Queryable Patch Inventory : check_patch.sql  
/*     
        For details see : 
        Queryable Patch Inventory -- SQL Interface to view, compare, validate database patches (Doc ID 1585814.1)
*/
set echo on
set pagesize 20000
set long 200000

/* Is patch 19849140 installed  ?  */
set serveroutput on
exec DEMOQP.check_patch_installed (qopatch_list('19849140'));

/* Return details about pacht 19849140 */
select xmltransform(DEMOQP.get_bug_details('19849140'), dbms_qopatch.get_opatch_xslt()) from dual;

/* As we are running on a PM managed db let's have look on host_names and instance names */
col HOST_NAME format A30
select host_name, instance_name from gv$instance;
select host_name, instance_name from v$instance;

/* check Instance ERP_1 on gract2.example.com */
exec DBMS_QOPATCH.SET_CURRENT_OPINST ('gract2.example.com','ERP_1');
select xmltransform (dbms_qopatch.get_opatch_lsinventory(), dbms_qopatch.GET_OPATCH_XSLT()) from dual;

/* Compare RAC nodes - this is not working in my env ! --> Getting   ORA-06502: PL/SQL: numeric or value error */
set serveroutput on
exec demoqp.compare_rac_node('gract2.example.com','ERP_1');



1) Check whether a certain patch ins installed

SQL> /* Is patch 19849140 installed    ?    */
SQL> set serveroutput on
SQL> exec DEMOQP.check_patch_installed (qopatch_list('19849140'));
----------Patch Report----------
19849140 : INSTALLED


2) Check patch details for patch  19849140

SQL> /* Return details about pacht 19849140 */
SQL> select xmltransform(DEMOQP.get_bug_details('19849140'), dbms_qopatch.get_opatch_xslt()) from dual;
XMLTRANSFORM(DEMOQP.GET_BUG_DETAILS('19849140'),DBMS_QOPATCH.GET_OPATCH_XSLT())
--------------------------------------------------------------------------------

Patch     19849140:   applied on 2015-01-23T16:31:09+01:00
Unique Patch ID: 18183131
  Patch Description: Grid Infrastructure Patch Set Update : 12.1.0.1.1 (HAS Comp
onent)
  Created on     : 23 Oct 2014, 08:32:20 hrs PST8PDT
  Bugs fixed:
     16505840  16505255  16505717  16505617  16399322  16390989  17486244  1
6168869  16444109  16505361  13866165  16505763  16208257  16904822  17299876  1
6246222  16505540  16505214  15936039  16580269  16838292  16505449  16801843  1
6309853  16505395  17507349  17475155  16493242  17039197  16196609  18045611  1
7463260  17263488  16505667  15970176  16488665  16670327  17551223
  Files Touched:

    cluvfyrac.sh
    crsdiag.pl
    lsnodes
..


3) Read in the inventory stuff from a gract2.example.com running instance  ERP_1
SQL> /* As we are running on a PM managed db let's have look on host_names and instance names */
SQL> col HOST_NAME format A30
SQL> select host_name, instance_name from gv$instance;

HOST_NAME               INSTANCE_NAME
------------------------------ ----------------
gract1.example.com           ERP_2
gract2.example.com           ERP_1
gract3.example.com           ERP_3

SQL> select host_name, instance_name from v$instance;

HOST_NAME               INSTANCE_NAME
------------------------------ ----------------
gract1.example.com           ERP_2

SQL> 
SQL> /* check Instance ERP_1 on gract2.example.com */
SQL> exec DBMS_QOPATCH.SET_CURRENT_OPINST ('gract2.example.com','ERP_1');

SQL> select xmltransform (dbms_qopatch.get_opatch_lsinventory(), dbms_qopatch.GET_OPATCH_XSLT()) from dual;
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY(),DBMS_QOPATCH.GET_OPATCH_XSLT(
--------------------------------------------------------------------------------

Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------

Oracle Home      : /u01/app/oracle/product/121/racdb
Inventory      : /u01/app/oraInventory
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 12c                       12.1.0.1.0
Installed Products ( 131)
..

4) Compare RAC nodes 
This very exiting feature doesn't work - sorry not time for debugging !

SQL> /* Compare RAC nodes - this is not working in my env ! --> Getting   ORA-06502: PL/SQL: numeric or value error */
SQL> set serveroutput on
SQL> exec demoqp.compare_rac_node('gract2.example.com','ERP_1');
BEGIN demoqp.compare_rac_node('gract2.example.com','ERP_1'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "SYS.DEMOQP", line 40
ORA-06512: at line 1

gract2.example.com           ERP_1

 

Why rollback and reapply SQL patch results in a NO-OP operation ?

[oracle@gract1 OPatch]$ ./datapatch -rollback 19849140 -force
SQL Patching tool version 12.1.0.1.0 on Sat Jan 24 19:39:29 2015
Copyright (c) 2014, Oracle.  All rights reserved.
Connecting to database...OK
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
  The following patches will be rolled back: 19849140
  Nothing to apply
Error: prereq checks failed!
  patch 19849140: rollback script /u01/app/oracle/product/121/racdb/sqlpatch/19849140/19849140_rollback.sql does not exist
Prereq check failed!  Exiting without installing any patches
See support note 1609718.1 for information on how to resolve the above errors
SQL Patching tool complete on Sat Jan 24 19:39:29 2015

What is this ?
Lets check dba_registry_sqlpatch whether patch 19849140 comes with any SQL changes 

SQL> col action_time format A30
SQL> col DESCRIPTION format A20
SQL> select * from dba_registry_sqlpatch ;
  PATCH_ID ACTION       STATUS       ACTION_TIME              DESCRIPTION
---------- --------------- --------------- ------------------------------ --------------------
LOGFILE
------------------------------------------------------------------------------------------------------------------------
  19121550 APPLY       SUCCESS       26-OCT-14 12.13.19.575484 PM   bundle:PSU
/u01/app/oracle/product/121/racdb/sqlpatch/19121550/19121550_apply_DW_2014Oct26_12_01_54.log

--> Patch doesn't provide any SQL changes - so above error isn't more an informational message.

What is the root cause of ORA-20006 in a RAC env?

Stop an instance 
[oracle@gract2 ~]$  srvctl stop instance -d dw -i dw_3
Resource NAME               INST   TARGET       STATE        SERVER          STATE_DETAILS
--------------------------- ----   ------------ ------------ --------------- -----------------------------------------
ora.dw.db                      1   ONLINE       ONLINE       gract1          Open,STABLE  
ora.dw.db                      2   ONLINE       ONLINE       gract3          Open,STABLE  
ora.dw.db                      3   OFFLINE      OFFLINE      -               Instance Shutdown,ST ABLE

[oracle@gract1 OPatch]$ ./datapatch  -verbose
SQL Patching tool version 12.1.0.1.0 on Sat Jan 24 20:03:22 2015
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches: 19121550
DBD::Oracle::st execute failed: ORA-20006: Number of RAC active instances and opatch jobs configured are not same
ORA-06512: at "SYS.DBMS_QOPATCH", line 1007
ORA-06512: at line 4 (DBD ERROR: OCIStmtExecute) [for Statement "DECLARE
       x XMLType;
     BEGIN
       x := dbms_qopatch.get_pending_activity;
       ? := x.getStringVal();
     END;" with ParamValues: :p1=undef] at /u01/app/oracle/product/121/racdb/sqlpatch/sqlpatch.pm line 1293.

Note even for policy managed database we need all instances up running on all servers to apply the patch !

Start the instance and and rerun  ./datapatch command
[oracle@gract1 OPatch]$ srvctl start instance -d dw -i dw_3
[oracle@gract1 OPatch]$ vi check_it.sql
[oracle@gract1 OPatch]$  ./datapatch  -verbose
SQL Patching tool version 12.1.0.1.0 on Sat Jan 24 20:17:33 2015
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches: 19121550

...................

ORA-20008 during datapatch installation on a RAC env

You get  ORA-20008 during running datapatch tool or during quering the patch status 
SQL> exec DEMOQP.check_patch_installed (qopatch_list('19849140'));
----------Patch Report----------
BEGIN DEMOQP.check_patch_installed (qopatch_list('19849140')); END;

*
ERROR at line 1:
ORA-20008: Timed out, Job Load_opatch_inventory_3execution time is more than 120Secs
ORA-06512: at "SYS.DBMS_QOPATCH", line 1428
ORA-06512: at "SYS.DBMS_QOPATCH", line 182
ORA-06512: at "SYS.DEMOQP", line 157
ORA-06512: at line 1

SQL> set linesize 120
SQL> col NODE_NAME format A20
SQL> col JOB_NAME format A30
SQL> col START_DATE format A35
SQL> col INST_JOB   format A30
SQL> select NODE_NAME, INST_ID, INST_JOB from opatch_inst_job;

NODE_NAME        INST_ID INST_JOB
-------------------- ---------- ------------------------------
gract1.example.com          1 Load_opatch_inventory_1
gract3.example.com          2 Load_opatch_inventory_2
gract2.example.com          3 Load_opatch_inventory_3

SQL> 
SQL> select job_name,state, start_date from dba_scheduler_jobs where job_name like 'LOAD_OPATCH%';

JOB_NAME               STATE           START_DATE
------------------------------ --------------- -----------------------------------
LOAD_OPATCH_INVENTORY_2        SUCCEEDED       24-JAN-15 11.35.41.629308 AM +01:00
LOAD_OPATCH_INVENTORY_3        SCHEDULED       24-JAN-15 11.35.41.683097 AM +01:00
LOAD_OPATCH_INVENTORY_1        SUCCEEDED       24-JAN-15 11.35.41.156565 AM +01:00
 
JOB was scheduled but was never succeeded ! 
--> After fixing the the connections problem to  gract2.example.com the job runs to completion

SQL> select job_name,state, start_date from dba_scheduler_jobs where job_name like 'LOAD_OPATCH%';
JOB_NAME               STATE           START_DATE
------------------------------ --------------- -----------------------------------
LOAD_OPATCH_INVENTORY_2        SUCCEEDED       24-JAN-15 11.59.29.078730 AM +01:00
LOAD_OPATCH_INVENTORY_3        SUCCEEDED       24-JAN-15 11.59.29.148714 AM +01:00
LOAD_OPATCH_INVENTORY_1        SUCCEEDED       24-JAN-15 11.59.29.025652 AM +01:00

Verify the patch install on all cluster nodes
SQL> set echo on
SQL> set pagesize 20000
SQL> set long 200000
SQL> 
SQL> /* As we are running on a PM managed db let's have look on host_names and instance names */
SQL> col HOST_NAME format A30
SQL> select host_name, instance_name from gv$instance;
HOST_NAME               INSTANCE_NAME
------------------------------ ----------------
gract1.example.com           dw_1
gract2.example.com           dw_3
gract3.example.com           dw_2
SQL> select host_name, instance_name from v$instance;
HOST_NAME               INSTANCE_NAME
------------------------------ ----------------
gract1.example.com           dw_1

SQL> /* exec DBMS_QOPATCH.SET_CURRENT_OPINST ('gract2.example.com','ERP_1'); */
SQL> set serveroutput on
SQL> exec DEMOQP.check_patch_installed (qopatch_list('19849140'));
----------Patch Report----------
19849140 : INSTALLED

SQL> exec DBMS_QOPATCH.SET_CURRENT_OPINST ('gract2.example.com','dw_3');
SQL> exec DEMOQP.check_patch_installed (qopatch_list('19849140'));
----------Patch Report----------
19849140 : INSTALLED

SQL> exec DBMS_QOPATCH.SET_CURRENT_OPINST ('gract3.example.com','dw_2');
SQL> exec DEMOQP.check_patch_installed (qopatch_list('19849140'));
----------Patch Report----------
19849140 : INSTALLED

 

Monitor Script to track  dba_scheduler_jobs and  opatch_inst_job tables

[oracle@gract1 ~/DATAPATCH]$ cat check_it.sql
 connect / as sysdba
 alter session set NLS_TIMESTAMP_TZ_FORMAT = 'dd-MON-yyyy HH24:mi:ss';
 set linesize 120
 col NODE_NAME format A20
 col JOB_NAME format A30
 col START_DATE format A25
 col LAST_START_DATE format A25
 col INST_JOB   format A30
 select NODE_NAME, INST_ID, INST_JOB from opatch_inst_job;
 select job_name,state, start_date, LAST_START_DATE from dba_scheduler_jobs where job_name like 'LOAD_OPATCH%';

 

How to cleanup after ORA-27477 errors ?

oracle@gract1 OPatch]$  ./datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Fri Jan 23 20:44:48 2015
Copyright (c) 2014, Oracle.  All rights reserved.
Connecting to database...OK
Determining current state...
Currently installed SQL Patches: 19121550
DBD::Oracle::st execute failed: ORA-27477: "SYS"."LOAD_OPATCH_INVENTORY_3" already exists
ORA-06512: at "SYS.DBMS_QOPATCH", line 1011
ORA-06512: at line 4 (DBD ERROR: OCIStmtExecute) [for Statement "DECLARE
x XMLType;
BEGIN
x := dbms_qopatch.get_pending_activity;
? := x.getStringVal();
END;" with ParamValues: :p1=undef] at /u01/app/oracle/product/121/racdb/sqlpatch/sqlpatch.pm line 1293.

sqlplus /nolog @check_it
NODE_NAME        INST_ID INST_JOB
-------------------- ---------- ------------------------------
gract2.example.com          1 Load_opatch_inventory_1
gract1.example.com          2 Load_opatch_inventory_2

JOB_NAME               STATE           START_DATE
------------------------------ --------------- -----------------------------------
LOAD_OPATCH_INVENTORY_1        DISABLED        23-JAN-15 08.38.11.746811 PM +01:00
LOAD_OPATCH_INVENTORY_3        DISABLED        23-JAN-15 08.36.18.506279 PM +01:00
LOAD_OPATCH_INVENTORY_2        DISABLED        23-JAN-15 08.38.11.891360 PM +01:00

Drop the jobs and cleanup the  opatch_inst_job table
SQL> exec DBMS_SCHEDULER.DROP_JOB('LOAD_OPATCH_INVENTORY_1');
SQL> exec DBMS_SCHEDULER.DROP_JOB('LOAD_OPATCH_INVENTORY_2');
SQL> exec DBMS_SCHEDULER.DROP_JOB('LOAD_OPATCH_INVENTORY_3');
SQL>  delete from opatch_inst_job;
2 rows deleted.
SQL> commit;

Now rerun  ./datapatch verbose command and monitor progress
SQL> @check_it
Connected.
NODE_NAME        INST_ID INST_JOB
-------------------- ---------- ------------------------------
gract2.example.com          1 Load_opatch_inventory_1
gract1.example.com          2 Load_opatch_inventory_2
gract3.example.com          3 Load_opatch_inventory_3
--> All our cluster nodes are ONLINE and the required JOBS are SCHEDULED !
JOB_NAME               STATE           START_DATE
------------------------------ --------------- -----------------------------------
LOAD_OPATCH_INVENTORY_1        SUCCEEDED       23-JAN-15 08.46.08.885038 PM +01:00
LOAD_OPATCH_INVENTORY_2        SUCCEEDED       23-JAN-15 08.46.08.933665 PM +01:00
LOAD_OPATCH_INVENTORY_3        RUNNING           23-JAN-15 08.46.09.014492 PM +01:00

Reference

  • 12.1.0.1 datapatch issue : ORA-27477: “SYS”.”LOAD_OPATCH_INVENTORY_1″ already exists (Doc ID 1934882.1)
  • Oracle Database 12.1 : FAQ on Queryable Patch Inventory [ID 1530108.1]
  • Datapatch errors at “SYS.DBMS_QOPATCH” [ID 1599479.1]
  • Queryable Patch Inventory — SQL Interface to view, compare, validate database patches (Doc ID 1585814.1)

Leave a Reply

Your email address will not be published. Required fields are marked *