Pythian Blog: Technical Track

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

by Michael Dinh on Aug 13, 2019 12:00:00 AM

Starting with 12c, running datapatch may be required after creating a database, depending on the database version and method for creating the database. Instead of having to remember all the variations, it's much simpler just to run datapatch -prereq after creating the database. I will demonstrate by creating RAC database for version 12.1 and 12.2. Both database homes have had the July 2019 patch applied. Test case for 12.2. Database July 2019 Release Update 12.2 applied: [code] [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 Oracle Instance alive for sid "+ASM1" + /u01/app/12.2.0.1/grid/OPatch/opatch version OPatch Version: 12.2.0.1.17 OPatch succeeded. + /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) 28566910;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:180802.1448.S) (28566910) 26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277) OPatch succeeded. + exit [oracle@racnode-dc2-1 ~]$ [/code] Create 12.2 RAC database: [code] [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 \ > -redoLogFileSize 100 \ > -emConfiguration NONE \ > -nodeinfo racnode-dc2-1,racnode-dc2-2 \ > -listeners LISTENER \ > -ignorePreReqs Copying database files 21% complete Creating and starting Oracle instance 35% complete Creating cluster database views 50% complete Completing Database Creation 57% complete Creating Pluggable Databases 78% complete Executing Post Configuration Actions 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/hawkcdb/hawkcdb.log" for further details. [oracle@racnode-dc2-1 ~]$ [/code] Run datapatch -prereq for 12.2 [code] [oracle@racnode-dc2-1 ~]$ $ORACLE_HOME/OPatch/datapatch -prereq SQL Patching tool version 12.2.0.1.0 Production on Thu Aug 1 17:45:13 2019 Copyright (c) 2012, 2019, Oracle. All rights reserved. Connecting to database...OK Note: Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state, no patches will be applied to closed PDBs. Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1) 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 ********************************************************************** SQL Patching tool complete on Thu Aug 1 17:46:39 2019 [oracle@racnode-dc2-1 ~]$ [/code] Test case for 12.1. Database July 2019 Bundle Patch 12.1 applied: [code] [oracle@racnode-dc1-1 ~]$ /media/patch/lspatches.sh + . /media/patch/gi.env ++ set +x The Oracle base has been set to /u01/app/oracle ORACLE_SID=+ASM1 ORACLE_BASE=/u01/app/oracle GRID_HOME=/u01/app/12.1.0.2/grid ORACLE_HOME=/u01/app/12.1.0.2/grid Oracle Instance alive for sid "+ASM1" + /u01/app/12.1.0.2/grid/OPatch/opatch version OPatch Version: 12.2.0.1.17 OPatch succeeded. + /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) 26983807;WLM Patch Set Update: 12.1.0.2.180116 (26983807) OPatch succeeded. + exit [oracle@racnode-dc1-1 ~]$ [/code] Create 12.1 RAC database: [code] [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! \ > -numberOfPDBs 1 -pdbName pdb01 -pdbAdminPassword Oracle_4U! \ > -databaseType MULTIPURPOSE \ > -automaticMemoryManagement false -totalMemory 3072 \ > -storageType ASM -diskGroupName DATA -recoveryGroupName FRA \ > -redoLogFileSize 100 \ > -emConfiguration NONE \ > -nodeinfo racnode-dc1-1,racnode-dc1-2 \ > -listeners LISTENER \ > -ignorePreReqs Copying database files 23% complete Creating and starting Oracle instance 38% complete Creating cluster database views 54% complete Completing Database Creation 77% complete Creating Pluggable Databases 81% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdbhawk/cdbhawk.log" for further details. [oracle@racnode-dc1-1 ~]$ [/code] Run datapatch -prereq for 12.1: [code] [oracle@racnode-dc1-1 ~]$ $ORACLE_HOME/OPatch/datapatch -prereq SQL Patching tool version 12.1.0.2.0 Production on Thu Aug 1 18:24:53 2019 Copyright (c) 2012, 2017, Oracle. All rights reserved. Connecting to database...OK Note: Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state, no patches will be applied to closed PDBs. Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1) Bootstrapping registry and package to current versions...done 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) ********************************************************************** SQL Patching tool complete on Thu Aug 1 18:26:26 2019 [oracle@racnode-dc1-1 ~]$ [/code] Review the installation queue for 12.2 and 12.1 will show that the datapatch will need to be run for 12.1 to apply DATABASE BUNDLE PATCH 12.1.0.2.190716 for the newly created database. In conclusion, it's good practice to check datapatch prerequisites after creating a database to avoid any issues in the future.

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.