Should Run datapatch After DBCA Mustafa, 2022-11-032022-11-03 Yesterday, I was testing some stuff in database and I needed to create new homes/new databases (new life new beginning). at a point I realized that something is not right about database. One thing to another I found the reason and it was interesting to me. I used DBCA to create database ( also I used General Purpose template which we shouldn’t use in a production environment. this was just a test and it was a virtual machine). starting from, I don’t know 12.2 I guess, dbca runs datapatch after database creation. datapatch will apply necessary sql patches into your database like RU (Revision Update) or one off patches. I waited dbca to complete its job and it completed without any error. if dbca says “it’s ok” then you don’t look more right? How many times did you check all log files that dbca produced? Anyway, during my actual tests, as I said before, I realized something is not right so I wanted to check if all patches are applied correctly. I checked CDB_REGISTRY_SQLPATCH view (this is a noncdb database by the way so I can use dba version of view too): set linesize 300 select CON_ID, TO_CHAR(action_time, 'YYYY-MM-DD HH24:MI:SS') AS action_time, PATCH_ID, PATCH_TYPE, ACTION, DESCRIPTION, SOURCE_VERSION, TARGET_VERSION from CDB_REGISTRY_SQLPATCH order by CON_ID, action_time, patch_id; CON_ID ACTION_TIME PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION ---------- ------------------- ---------- ---------- --------------- ---------------------------------------------------------------------------------------------------- --------------- --------------- 0 2022-11-02 18:55:12 34086870 INTERIM APPLY OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870) 19.1.0.0.0 19.1.0.0.0 0 2022-11-02 18:55:12 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 0 2022-11-02 18:55:13 34620690 INTERIM APPLY MERGE ON DATABASE RU 19.16.0.0.0 OF 34570619 30155338 30430932 30582819 31050896 19.1.0.0.0 19.1.0.0.0 0 2022-11-02 19:04:56 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 12345678910111213141516171819 set linesize 300 select CON_ID, TO_CHAR(action_time, 'YYYY-MM-DD HH24:MI:SS') AS action_time, PATCH_ID, PATCH_TYPE, ACTION, DESCRIPTION, SOURCE_VERSION, TARGET_VERSION from CDB_REGISTRY_SQLPATCH order by CON_ID, action_time, patch_id; CON_ID ACTION_TIME PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION---------- ------------------- ---------- ---------- --------------- ---------------------------------------------------------------------------------------------------- --------------- --------------- 0 2022-11-02 18:55:12 34086870 INTERIM APPLY OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870) 19.1.0.0.0 19.1.0.0.0 0 2022-11-02 18:55:12 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 0 2022-11-02 18:55:13 34620690 INTERIM APPLY MERGE ON DATABASE RU 19.16.0.0.0 OF 34570619 30155338 30430932 30582819 31050896 19.1.0.0.0 19.1.0.0.0 0 2022-11-02 19:04:56 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 at first it seems OK to me but then I realized there were two “RU APPLY” rows with a small time difference. As you see all patch operations are done to container 0, They both applied to same container(which this is a noncdb database). Since it was around 02:40 AM, I went to bed and in the morning I run datapatch (by the way date of virtual machine is not correct). here is the output of datapatch: oracle@localhost ~ $ datapatch -verbose SQL Patching tool version 19.16.0.0.0 Production on Thu Nov 3 03:15:53 2022 Copyright (c) 2012, 2022, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_3517_2022_11_03_03_15_53/sqlpatch_invocation.log Connecting to database...OK Gathering database info...done Bootstrapping registry and package to current versions...done Determining current state...done Current state of interim SQL patches: Interim patch 34086870 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): Binary registry: Installed SQL registry: Applied successfully on 02-NOV-22 06.55.12.497306 PM Interim patch 34620690 (MERGE ON DATABASE RU 19.16.0.0.0 OF 34570619 30155338 30430932 30582819 31050896): Binary registry: Installed SQL registry: Applied successfully on 02-NOV-22 06.55.13.275783 PM Current state of release update SQL patches: Binary registry: 19.16.0.0.0 Release_Update 220703022223: Installed SQL registry: Applied 19.16.0.0.0 Release_Update 220703022223 with errors on 02-NOV-22 07.04.56.062757 PM Adding patches to installation queue and performing prereq checks...done Installation queue: No interim patches need to be rolled back Patch 34133642 (Database Release Update : 19.16.0.0.220719 (34133642)): Apply from 19.1.0.0.0 Feature Release to 19.16.0.0.0 Release_Update 220703022223 No interim patches need to be applied Installing patches... Patch installation complete. Total patches installed: 1 Validating logfiles...done Patch 34133642 apply: SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34133642/24865470/34133642_apply_ORCL_2022Nov03_03_16_09.log (no errors) SQL Patching tool complete on Thu Nov 3 03:26:37 2022 123456789101112131415161718192021222324252627282930313233343536373839 oracle@localhost ~ $ datapatch -verboseSQL Patching tool version 19.16.0.0.0 Production on Thu Nov 3 03:15:53 2022Copyright (c) 2012, 2022, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_3517_2022_11_03_03_15_53/sqlpatch_invocation.log Connecting to database...OKGathering database info...doneBootstrapping registry and package to current versions...doneDetermining current state...done Current state of interim SQL patches:Interim patch 34086870 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): Binary registry: Installed SQL registry: Applied successfully on 02-NOV-22 06.55.12.497306 PMInterim patch 34620690 (MERGE ON DATABASE RU 19.16.0.0.0 OF 34570619 30155338 30430932 30582819 31050896): Binary registry: Installed SQL registry: Applied successfully on 02-NOV-22 06.55.13.275783 PM Current state of release update SQL patches: Binary registry: 19.16.0.0.0 Release_Update 220703022223: Installed SQL registry: Applied 19.16.0.0.0 Release_Update 220703022223 with errors on 02-NOV-22 07.04.56.062757 PM Adding patches to installation queue and performing prereq checks...doneInstallation queue: No interim patches need to be rolled back Patch 34133642 (Database Release Update : 19.16.0.0.220719 (34133642)): Apply from 19.1.0.0.0 Feature Release to 19.16.0.0.0 Release_Update 220703022223 No interim patches need to be applied Installing patches...Patch installation complete. Total patches installed: 1 Validating logfiles...donePatch 34133642 apply: SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34133642/24865470/34133642_apply_ORCL_2022Nov03_03_16_09.log (no errors)SQL Patching tool complete on Thu Nov 3 03:26:37 2022 pay attention on “Current state of release update SQL Patches:” section. it says for SQL registery: “Applied 19.16.0.0.0 Release_Update 220703022223 with errors on 02-NOV-22 07.04.56.062757 PM”. there is an error on update of this apply. I don’t know what that was and it also says 1 patch installed. Maybe I did something wrong during the installation. I rechecked registry: set linesize 300 select CON_ID, TO_CHAR(action_time, 'YYYY-MM-DD HH24:MI:SS') AS action_time, PATCH_ID, PATCH_TYPE, ACTION, DESCRIPTION, SOURCE_VERSION, TARGET_VERSION from CDB_REGISTRY_SQLPATCH order by CON_ID, action_time, patch_id; CON_ID ACTION_TIME PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION ---------- ------------------- ---------- ---------- --------------- ---------------------------------------------------------------------------------------------------- --------------- --------------- 0 2022-11-02 18:55:12 34086870 INTERIM APPLY OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870) 19.1.0.0.0 19.1.0.0.0 0 2022-11-02 18:55:12 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 0 2022-11-02 18:55:13 34620690 INTERIM APPLY MERGE ON DATABASE RU 19.16.0.0.0 OF 34570619 30155338 30430932 30582819 31050896 19.1.0.0.0 19.1.0.0.0 0 2022-11-02 19:04:56 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 0 2022-11-03 03:26:37 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 1234567891011121314151617181920 set linesize 300 select CON_ID, TO_CHAR(action_time, 'YYYY-MM-DD HH24:MI:SS') AS action_time, PATCH_ID, PATCH_TYPE, ACTION, DESCRIPTION, SOURCE_VERSION, TARGET_VERSION from CDB_REGISTRY_SQLPATCH order by CON_ID, action_time, patch_id; CON_ID ACTION_TIME PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION---------- ------------------- ---------- ---------- --------------- ---------------------------------------------------------------------------------------------------- --------------- --------------- 0 2022-11-02 18:55:12 34086870 INTERIM APPLY OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870) 19.1.0.0.0 19.1.0.0.0 0 2022-11-02 18:55:12 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 0 2022-11-02 18:55:13 34620690 INTERIM APPLY MERGE ON DATABASE RU 19.16.0.0.0 OF 34570619 30155338 30430932 30582819 31050896 19.1.0.0.0 19.1.0.0.0 0 2022-11-02 19:04:56 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 0 2022-11-03 03:26:37 34133642 RU APPLY Database Release Update : 19.16.0.0.220719 (34133642) 19.1.0.0.0 19.16.0.0.0 here is a third row for 19.16 RU apply and from that point everything worked smoothly. So, there is no harm to run datapatch after creating a new database via dbca. You must run datapatch if you create your database with CREATE DATABASE command anyway. We must be careful out there, anything can happen š there is no harm run datapatch and see: “Installation queue: No interim patches need to be rolled back No release update patches need to be installed No interim patches need to be applied” wish you all healthy, beautiful days. 19c Administration datapatchdbca