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)