Table of Contents
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)