Starting with Oracle 12c, running datapatch may be required after creating a database, depending on the version and the method used for creation. Instead of trying to remember every variation, a simpler strategy is to run datapatch -prereq immediately after database creation.
Below, I will demonstrate this using RAC database installations for versions 12.1 and 12.2, both of which have the July 2019 patches applied to their respective database homes.
First, let's verify the patch level of the 12.2 database home. The July 2019 Release Update has already been applied.
[oracle@racnode-dc2-1 ~]$ /media/patch/lspatches.sh
+ . /media/patch/gi.env
++ set +x
The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.2.0.1/grid
ORACLE_HOME=/u01/app/12.2.0.1/grid
+ /u01/app/12.2.0.1/grid/OPatch/opatch lspatches
29770090;ACFS JUL 2019 RELEASE UPDATE 12.2.0.1.190716 (29770090)
29770040;OCW JUL 2019 RELEASE UPDATE 12.2.0.1.190716 (29770040)
29757449;Database Jul 2019 Release Update : 12.2.0.1.190716 (29757449)
We proceed to create a 12.2 RAC container database in silent mode.
[oracle@racnode-dc2-1 ~]$ dbca -silent -createDatabase -characterSet AL32UTF8 \
> -createAsContainerDatabase true \
> -templateName General_Purpose.dbc \
> -gdbname hawkcdb -sid hawkcdb -responseFile NO_VALUE \
> -sysPassword Oracle_4U! -systemPassword Oracle_4U! \
> -numberOfPDBs 1 -pdbName pdb01 -pdbAdminPassword Oracle_4U! \
> -databaseType MULTIPURPOSE \
> -automaticMemoryManagement false -totalMemory 3072 \
> -storageType ASM -diskGroupName DATA -recoveryGroupName FRA \
> -nodeinfo racnode-dc2-1,racnode-dc2-2 \
> -ignorePreReqs
After creation, we check if any SQL fixes are required.
[oracle@racnode-dc2-1 ~]$ $ORACLE_HOME/OPatch/datapatch -prereq
...
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
**********************************************************************
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB01
Nothing to roll back
Nothing to apply
**********************************************************************
In this 12.2 test case, the database was created with the necessary fixes already in place.
Now, let's look at the 12.1 environment where the July 2019 Bundle Patch is applied to the home.
[oracle@racnode-dc1-1 ~]$ /media/patch/lspatches.sh
...
+ /u01/app/12.1.0.2/grid/OPatch/opatch lspatches
29509318;OCW PATCH SET UPDATE 12.1.0.2.190716 (29509318)
29496791;Database Bundle Patch : 12.1.0.2.190716 (29496791)
29423125;ACFS PATCH SET UPDATE 12.1.0.2.190716 (29423125)
The creation process is similar to the 12.2 version.
[oracle@racnode-dc1-1 ~]$ dbca -silent -createDatabase -characterSet AL32UTF8 \
> -createAsContainerDatabase true \
> -templateName General_Purpose.dbc \
> -gdbname cdbhawk -sid cdbhawk -responseFile NO_VALUE \
> -sysPassword Oracle_4U! -systemPassword Oracle_4U! \
...
> -nodeinfo racnode-dc1-1,racnode-dc1-2 \
> -ignorePreReqs
Unlike the 12.2 instance, the 12.1 check reveals that the SQL portion of the bundle patch still needs to be applied to the newly created database.
[oracle@racnode-dc1-1 ~]$ $ORACLE_HOME/OPatch/datapatch -prereq
...
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
**********************************************************************
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB01
Nothing to roll back
The following patches will be applied:
29496791 (DATABASE BUNDLE PATCH 12.1.0.2.190716)
**********************************************************************
Comparing the two versions, the 12.1 database requires a manual execution of datapatch to apply the DATABASE BUNDLE PATCH 12.1.0.2.190716, whereas the 12.2 database was already up to date.
To ensure your environment is fully patched and consistent, always make it a standard practice to check datapatch -prereq after any database creation.
Ready to optimize your Oracle Database for the future?