Oracle Database

Why It's Good Practice to Run Datapatch -prereq after Creating Database

2 min read

Aug 13, 2019 12:00:00 AM

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.

Testing datapatch on oracle 12.2

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)

Creating the 12.2 rac database

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

Running datapatch -prereq for 12.2

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.

Testing datapatch on oracle 12.1

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)

Creating the 12.1 rac database

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

Running datapatch -prereq for 12.1

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)
**********************************************************************

Conclusion

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.