Skip to main content
Delphix

Oracle Out of Place Patching, Delphix Virtual Pluggable Databases and Delphix Repository Templates (KBA9389)

 

 

KBA

KBA# 9389

 

Issue

Oracle out of place patching requires installation of a new Oracle Home and patching this Oracle Home to the required level.  Each Oracle database that is to be patched to that same level and make use of this new Oracle Home and patched binaries will need to modified to make use of the new home.   These same Oracle database that are to run from this new home will also need to have SQL patches applied to their databases internals to ensure they align with the patched binaries from which the databases will be running.

Delphix Continuous Data Engines also track this same Oracle Home information.  Just like Oracle tracks these details through its inventory, Delphix also stores information about each Oracle home and maps each Oracle dataset, whether it be a dSource, VDB, VPDB, CDB, RAC, or single instance to an Oracle home. The Delphix term for the Oracle Home is repository.  

When provisioning, refreshing, or rewinding VCDB/PDB pairs Delphix establishes what is termed an auxiliary instance and database to recover a VPDB to the desired point in time.  From here the recovered VPDB is unplugged and plugged into its end destination CDB, whether this is a VCDB created by Delphix or a CDB supplied by a DBA.  To control the database parameters used by this auxiliary instance Delphix utilizes a VDB template where the parameters are set.  This VDB template is then linked with a repository, a dataset, and assigned to a repository template through the creation of the repository template.

To control the auxiliary instance parameters during provision or refresh, the VDB template sets the parameters required.  The target Oracle Home the auxiliary database and VPDB is to run from, the source database the provision will be made from and VDB template are all grouped into what is collectively known as the repository template.

Using Repository Templates During Virtual Pluggable Database Provision to Control SGA Size in the Auxiliary Container Database (KBA1756)

To control the auxiliary instance parameters during a VPDB rewind, the VDB template sets the parameters required.  The target Oracle Home the auxiliary database and VPDB is to run from, the source database the provision will be made from and VDB template are all grouped into what is collectively known as the repository template.

Using Repository Templates to Control Auxiliary Database SGA Size During VDB Rewind Operations (KBA9102)

 

This knowledge base article looks at the implications of performing out of place patching of Oracle container databases where that patching impacts dSources, VPDBs, VCDBs and DBA supplied container databases used to house VPDBs.  It attempts to show the entire workflow for patching source and target databases and to align the existing dSources and VPDBs with new repository templates post patching of Oracle.

Prerequisites

Out of place patching of Oracle is occurring in the source pluggable databases and their associated virtual pluggable databases.

VDB templates and repository templates are being used in the environment for controlling VPDB operations like provision, refresh, and rewind.

 

 

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0

6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

Performing out of place patching and re-applying repository templates

To ensure Delphix provisioning, refresh, and rewind operations of VPDBs are performed utilizing repository templates after the patching has occurred new repository templates must be configured.  These new repository templates will utilize the existing VDB template and link this to the new Oracle Home created through out of place patching.

What follows here is one process for performing dSource patching, VPDB patching, and aligning Delphix VPDBs with a suitable repository template that takes into account a new Oracle Home.

The dSource details

[oracle@oel7sitde1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/19.11.1/dbhome_1

[oracle@oel7sitde1 ~]$ . oraenv
ORACLE_SID = [oracle] ? cdb19c2
The Oracle base has been set to /u01/app/oracle

[oracle@oel7sitde1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 20 11:39:24 2022
Version 19.11.1.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.1.0.0

SQL> set lines 132
SQL> select dbid, name, db_unique_name, open_mode, database_role from v$database;

      DBID NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
---------- --------- ------------------------------ -------------------- ----------------
2546542994 CDB19C2   cdb19c2                        READ WRITE           PRIMARY

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB19C21                       READ WRITE NO
         4 PDB19C22                       READ WRITE NO

dSource current patch revision

The database patch level.

SQL> set pages 500
set lines 200
col action format a7
col description format a70
col logfile format a130
col status format a7

select CON_ID,PATCH_UID,PATCH_ID,PATCH_TYPE,substr(ACTION,1,7) ACTION,SOURCE_VERSION,TARGET_VERSION,DESCRIPTION,substr(STATUS,1,10) INSTALL_STATUS,to_char(ACTION_TIME,'YYYY-MM-DD HH24:MI') INSTALL_DATE, LOGFILE from CDB_REGISTRY_SQLPATCH order by con_id, to_char(CDB_REGISTRY_SQLPATCH.ACTION_TIME,'YYYY-MM-DD HH24:MI')  desc;SQL> SQL> SQL> SQL> SQL> SQL> SQL> 

    CON_ID  PATCH_UID    PATCH_ID PATCH_TYPE ACTION  SOURCE_VERSION  TARGET_VERSION  DESCRIPTION                                INSTALL_STATUS
---------- ---------- ---------- ---------- ------- --------------- --------------- ---------------------------------------------------------------------- ----------------------------------------
INSTALL_DATE     LOGFILE
---------------- ----------------------------------------------------------------------------------------------------------------------------------
     1   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:14 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_CDBROOT_2022Jan22_17_06_58.log

     1   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:39 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_CDBROOT_2022Jan13_12_38_03.log

     3   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:14 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_PDB19C21_2022Jan22_17_10_02.log

     3   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:44 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_PDBSEED_2022Jan13_12_43_36.log

     4   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:36 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_PDB19C22_2022Jan22_17_33_54.log

     4   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:44 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_PDBSEED_2022Jan13_12_43_36.log

6 rows selected.

The Database Oracle Home binary patch level

[oracle@oel7sitde1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory | grep 'Patch description'
Patch description:  "OCW RELEASE UPDATE REVISION 19.11.1.0.0 (32844491)"
Patch description:  "Database Release Update Revision : 19.11.1.0.210720 (32844504)"

Note

Note:

The dSource has been ingested and has snapshots captured while the database is running the 19.11.1.0 release.

Template Configuration

VDB Template configuration

This template will used within the repository template configuration and will control the memory structures associated with auxiliary CDB that Delphix creates during VPDB provisioning.

dlpx6014> database template create
dlpx6014 database template create *> set sourceType=OracleVirtualSource
dlpx6014 database template create *> edit parameters
dlpx6014 database template create parameters *> set sga_max_size=1500M
dlpx6014 database template create parameters *> set compatible=19.0.0
dlpx6014 database template create parameters *> set pga_aggregate_limit=512M
dlpx6014 database template 'VCDB_Template_1500MSGA' update *> set parameters.pga_aggregate_limit=2048M;
dlpx6014 database template create parameters *> set enable_pluggable_database=TRUE
dlpx6014 database template create parameters *> commit

Repository Template configuration

The repository template links the VDB template (VPDB_repo_template_1500MSGA) with the target Oracle Home (repository) into which the VPDB will be provisioned.  It also links the source PDB (PDB19C1) to the repository template.  The repository template is linking the provision source database and snapshot to the target Oracle Home of the CDB the VPDB is to be provisioned into and the VDB template controls the size of the auxiliary instances SGA that is started as a part of the provisioning process.  This repository template will also be used during refresh operations.

dlpx6014> repository template create
dlpx6014 repository template create *> set name=VPDB_repo_template_1500MSGA
dlpx6014 repository template create *> set container=PDB19C21
dlpx6014 repository template create *> set repository=OEL7SITDE2/'/u01/app/oracle/19.11.1/dbhome_1'
dlpx6014 repository template create *> set template=VCDB_Template_1500MSGA 
dlpx6014 repository template create *> commit

The VPDB details

A VDB has been provisioned from the dSource and version/patch level to a DBA supplied container database running the same patch level as the source database.

The target CDB

[oracle@oel7sitde2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 20 15:29:17 2022
Version 19.11.1.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.1.0.0

SQL> set lines 132
SQL> select dbid, name, db_unique_name, open_mode, database_role from v$database;

      DBID NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
---------- --------- ------------------------------ -------------------- ----------------
 340104261 RCDB2     rcdb2                          READ WRITE           PRIMARY

SQL> set pages 500
set lines 200
col action format a7
col description format a70
col logfile format a130
col status format a7

select CON_ID,PATCH_UID,PATCH_ID,PATCH_TYPE,substr(ACTION,1,7) ACTION,SOURCE_VERSION,TARGET_VERSION,DESCRIPTION,substr(STATUS,1,10) INSTALL_STATUS,to_char(ACTION_TIME,'YYYY-MM-DD HH24:MI') INSTALL_DATE, LOGFILE from CDB_REGISTRY_SQLPATCH order by con_id, to_char(CDB_REGISTRY_SQLPATCH.ACTION_TIME,'YYYY-MM-DD HH24:MI')  desc;SQL> SQL> SQL> SQL> SQL> SQL> SQL> 

    CON_ID  PATCH_UID    PATCH_ID PATCH_TYPE ACTION  SOURCE_VERSION  TARGET_VERSION  DESCRIPTION                                INSTALL_STATUS
---------- ---------- ---------- ---------- ------- --------------- --------------- ---------------------------------------------------------------------- ----------------------------------------
INSTALL_DATE     LOGFILE
---------------- ----------------------------------------------------------------------------------------------------------------------------------
     1   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-07-20 15:27 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_RCDB2_CDBROOT_2022Jul20_15_19_57.log

     1   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-14 16:58 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_RCDB2_CDBROOT_2022Jan14_16_57_03.log


After Provisioning of the VPDB

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 VDBRU1                         READ WRITE NO

SQL> set pages 500
set lines 200
col action format a7
col description format a70
col logfile format a130
col status format a7

select CON_ID,PATCH_UID,PATCH_ID,PATCH_TYPE,substr(ACTION,1,7) ACTION,SOURCE_VERSION,TARGET_VERSION,DESCRIPTION,substr(STATUS,1,10) INSTALL_STATUS,to_char(ACTION_TIME,'YYYY-MM-DD HH24:MI') INSTALL_DATE, LOGFILE from CDB_REGISTRY_SQLPATCH order by con_id, to_char(CDB_REGISTRY_SQLPATCH.ACTION_TIME,'YYYY-MM-DD HH24:MI')  desc;SQL> SQL> SQL> SQL> SQL> SQL> SQL> 

    CON_ID  PATCH_UID    PATCH_ID PATCH_TYPE ACTION  SOURCE_VERSION  TARGET_VERSION  DESCRIPTION                                INSTALL_STATUS
---------- ---------- ---------- ---------- ------- --------------- --------------- ---------------------------------------------------------------------- ----------------------------------------
INSTALL_DATE     LOGFILE
---------------- ----------------------------------------------------------------------------------------------------------------------------------
     1   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-07-20 15:27 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_RCDB2_CDBROOT_2022Jul20_15_19_57.log

     1   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-14 16:58 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_RCDB2_CDBROOT_2022Jan14_16_57_03.log

     3   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:14 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_PDB19C21_2022Jan22_17_10_02.log

     3   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:44 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_PDBSEED_2022Jan13_12_43_36.log

Upgrade the dSource  

The current version details as they exist prior to performing the patching of Oracle.

[oracle@oel7sitde1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/19.11.1/dbhome_1
[oracle@oel7sitde1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory | egrep 'Patch description'
Patch description:  "OCW RELEASE UPDATE REVISION 19.11.1.0.0 (32844491)"
Patch description:  "Database Release Update Revision : 19.11.1.0.210720 (32844504)"

The dSource configuration in the Oracle Clusterware/GRID Infrastructure.

[oracle@oel7sitde1 ~]$ srvctl config database -d cdb19c2
Database unique name: cdb19c2
Database name: cdb19c2
Oracle home: /u01/app/oracle/19.11.1/dbhome_1
Oracle user: grid
Spfile: +DATA/CDB19C2/PARAMETERFILE/spfile.567.1093869909
Password file: 
Domain: plb.internal
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services: 
OSDBA group: 
OSOPER group: 
Database instance: cdb19c2


Migrate the dSource to its new Oracle Home

The Oracle home the dSource and its PDB are to move to is the following.

[oracle@oel7sitde1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -oh /u01/app/oracle/19.0.0/dbhome_1 | egrep 'Patch description'
Patch description:  "OCW RELEASE UPDATE 19.15.0.0.0 (33815596)"
Patch description:  "Database Release Update : 19.15.0.0.220419 (33806152)"

Update the Clusterware/Grid Infrastructure resource for this database to utilize the new Oracle Home (these are in play)

[oracle@oel7sitde1 ~]$ srvctl stop database -db cdb19c2 -o immediate
[oracle@oel7sitde1 ~]$ srvctl modify database -db cdb19c2 -oraclehome /u01/app/oracle/19.0.0/dbhome_1

The new Oracle Home has been set for the dSource CDB and it is now ready to be started and run from this patched Oracle Home.

[oracle@oel7sitde1 ~]$ srvctl config database -d cdb19c2
Database unique name: cdb19c2
Database name: cdb19c2
Oracle home: /u01/app/oracle/19.0.0/dbhome_1
Oracle user: grid
Spfile: +DATA/CDB19C2/PARAMETERFILE/spfile.567.1093869909
Password file: 
Domain: plb.internal
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services: 
OSDBA group: 
OSOPER group: 
Database instance: cdb19c2

Adjust /etc/oratab if required to ensure the CDB is now set to the new Oracle Home.

oracle@oel7sitde1 ~]$ egrep cdb19c2 /etc/oratab
cdb19c2:/u01/app/oracle/19.11.1/dbhome_1:N

[oracle@oel7sitde1 ~]$ vi /etc/oratab
[oracle@oel7sitde1 ~]$ egrep cdb19c2 /etc/oratab
cdb19c2:/u01/app/oracle/19.0.0/dbhome_1:N

Set the environment for the new Oracle Home and start the container database from here.

[oracle@oel7sitde1 ~]$ . oraenv
ORACLE_SID = [cdb19c2] ? 
The Oracle base has been changed from /u01/app/oracle to /u01/app/orabase

[oracle@oel7sitde1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/19.0.0/dbhome_1

Start the database from its new Oracle Home.

[oracle@oel7sitde1 ~]$ srvctl start database -db cdb19c2

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB19C21              READ WRITE NO
     4 PDB19C22              READ WRITE NO

Check the Container Database SQL patch level now that it is  running from the new Oracle Home.  It is still running as 19.11.1.0 and needs to be patched with datapatch.

SQL> set pages 500
set lines 200
col action format a7
col description format a70
col logfile format a130
col status format a7

select CON_ID,PATCH_UID,PATCH_ID,PATCH_TYPE,substr(ACTION,1,7) ACTION,SOURCE_VERSION,TARGET_VERSION,DESCRIPTION,substr(STATUS,1,10) INSTALL_STATUS,to_char(ACTION_TIME,'YYYY-MM-DD HH24:MI') INSTALL_DATE, LOGFILE from CDB_REGISTRY_SQLPATCH order by con_id, to_char(CDB_REGISTRY_SQLPATCH.ACTION_TIME,'YYYY-MM-DD HH24:MI')  desc;SQL> SQL> SQL> SQL> SQL> SQL> SQL> 

    CON_ID  PATCH_UID    PATCH_ID PATCH_TYPE ACTION  SOURCE_VERSION  TARGET_VERSION  DESCRIPTION                                INSTALL_STATUS
---------- ---------- ---------- ---------- ------- --------------- --------------- ---------------------------------------------------------------------- ----------------------------------------
INSTALL_DATE     LOGFILE
---------------- ----------------------------------------------------------------------------------------------------------------------------------
     1   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:14 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_CDBROOT_2022Jan22_17_06_58.log

     1   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:39 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_CDBROOT_2022Jan13_12_38_03.log

     3   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:14 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_PDB19C21_2022Jan22_17_10_02.log

     3   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:44 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_PDBSEED_2022Jan13_12_43_36.log

     4   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:36 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_PDB19C22_2022Jan22_17_33_54.log

     4   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:44 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_PDBSEED_2022Jan13_12_43_36.log


6 rows selected.

Patch the dSource RDBMS using datapatch.  In the context of a PDB, datapatch will only patch PDBs that are open (mounted PDBs will not be patched)

[oracle@oel7sitde1 ~]$ $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 19.15.0.0.0 Production on Thu Jul 21 09:51:42 2022
Copyright (c) 2012, 2022, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/orabase/cfgtoollogs/sqlpatch/sqlpatch_14344_2022_07_21_09_51_42/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

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

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.15.0.0.0 Release_Update 220331125408: Installed
  PDB CDB$ROOT:
    Applied 19.11.1.0.0 Release_Update_Revision 210712203320 successfully on 22-JAN-22 05.14.32.119995 PM
  PDB PDB$SEED:
    Applied 19.11.1.0.0 Release_Update_Revision 210712203320 successfully on 22-JAN-22 05.14.35.845227 PM
  PDB PDB19C21:
    Applied 19.11.1.0.0 Release_Update_Revision 210712203320 successfully on 22-JAN-22 05.14.39.606304 PM
  PDB PDB19C22:
    Applied 19.11.1.0.0 Release_Update_Revision 210712203320 successfully on 22-JAN-22 05.36.33.056427 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDB19C21 PDB19C22
    No interim patches need to be rolled back
    Patch 32844504 (Database Release Update Revision : 19.11.1.0.210720 (32844504)):
      Rollback from 19.11.1.0.0 Release_Update_Revision 210712203320 to 19.11.0.0.0 Release_Update 210413004009
    Patch 33806152 (Database Release Update : 19.15.0.0.220419 (33806152)):
      Apply from 19.11.0.0.0 Release_Update 210413004009 to 19.15.0.0.0 Release_Update 220331125408
    No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 8

Validating logfiles...done
Patch 32844504 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_CDB19C2_CDBROOT_2022Jul21_09_52_21.log (no errors)
Patch 33806152 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_CDB19C2_CDBROOT_2022Jul21_09_52_25.log (no errors)
Patch 32844504 rollback (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_CDB19C2_PDBSEED_2022Jul21_09_55_21.log (no errors)
Patch 33806152 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_CDB19C2_PDBSEED_2022Jul21_09_55_25.log (no errors)
Patch 32844504 rollback (pdb PDB19C21): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_CDB19C2_PDB19C21_2022Jul21_09_55_21.log (no errors)
Patch 33806152 apply (pdb PDB19C21): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_CDB19C2_PDB19C21_2022Jul21_09_55_24.log (no errors)
Patch 32844504 rollback (pdb PDB19C22): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_CDB19C2_PDB19C22_2022Jul21_09_55_21.log (no errors)
Patch 33806152 apply (pdb PDB19C22): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_CDB19C2_PDB19C22_2022Jul21_09_55_25.log (no errors)

Automatic recompilation incomplete; run utlrp.sql to revalidate.
  PDBs: PDB19C21 PDB19C22 PDB$SEED

SQL Patching tool complete on Thu Jul 21 09:57:59 2022

Check the new release has been implemented successfully.

[oracle@oel7sitde1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 10:04:44 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> set pages 500
set lines 200
col action format a7
col description format a70
col logfile format a130
col status format a7

select CON_ID,PATCH_UID,PATCH_ID,PATCH_TYPE,substr(ACTION,1,7) ACTION,SOURCE_VERSION,TARGET_VERSION,DESCRIPTION,substr(STATUS,1,10) INSTALL_STATUS,to_char(ACTION_TIME,'YYYY-MM-DD HH24:MI') INSTALL_DATE, LOGFILE from CDB_REGISTRY_SQLPATCH order by con_id, to_char(CDB_REGISTRY_SQLPATCH.ACTION_TIME,'YYYY-MM-DD HH24:MI')  desc;SQL> SQL> SQL> SQL> SQL> SQL> SQL> 

    CON_ID  PATCH_UID    PATCH_ID PATCH_TYPE ACTION  SOURCE_VERSION  TARGET_VERSION  DESCRIPTION                                INSTALL_STATUS
---------- ---------- ---------- ---------- ------- --------------- --------------- ---------------------------------------------------------------------- ----------------------------------------
INSTALL_DATE     LOGFILE
---------------- ----------------------------------------------------------------------------------------------------------------------------------
     1   24340404    32844504 RUR        ROLLBAC 19.11.1.0.0     19.11.0.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-07-21 09:57 /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_CDB19C2_CDBROOT_2022Jul21_09_52_21.log

     1   24713297    33806152 RU        APPLY   19.11.0.0.0     19.15.0.0.0     Database Release Update : 19.15.0.0.220419 (33806152)           SUCCESS
2022-07-21 09:57 /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_CDB19C2_CDBROOT_2022Jul21_09_52_25.log

     1   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:14 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_CDBROOT_2022Jan22_17_06_58.log

     1   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:39 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_CDBROOT_2022Jan13_12_38_03.log

     3   24340404    32844504 RUR        ROLLBAC 19.11.1.0.0     19.11.0.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-07-21 09:57 /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_CDB19C2_PDB19C21_2022Jul21_09_55_21.log

     3   24713297    33806152 RU        APPLY   19.11.0.0.0     19.15.0.0.0     Database Release Update : 19.15.0.0.220419 (33806152)           SUCCESS
2022-07-21 09:57 /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_CDB19C2_PDB19C21_2022Jul21_09_55_24.log

     3   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:14 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_PDB19C21_2022Jan22_17_10_02.log

     3   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:44 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_PDBSEED_2022Jan13_12_43_36.log

     4   24340404    32844504 RUR        ROLLBAC 19.11.1.0.0     19.11.0.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-07-21 09:57 /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_CDB19C2_PDB19C22_2022Jul21_09_55_21.log

     4   24713297    33806152 RU        APPLY   19.11.0.0.0     19.15.0.0.0     Database Release Update : 19.15.0.0.220419 (33806152)           SUCCESS
2022-07-21 09:57 /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_CDB19C2_PDB19C22_2022Jul21_09_55_25.log

     4   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:36 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_PDB19C22_2022Jan22_17_33_54.log

     4   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:44 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_PDBSEED_2022Jan13_12_43_36.log


12 rows selected.

Migrate the dSource in Delphix 

An environment refresh will need to be performed to pick up the new Oracle Home in the target environment.  In this example it is assumed this has been performed and Delphix is aware of the new patched Oracle Home that the dSource is now using.

important

Important:

The following configuration changes in Delphix need to be performed for each PDB held by the dSource container database.

The repositories for CDB19C2, PDB19C21 (linked as a source) and PDB19C22 (not currently linked as a source) need to be updated to from OEL7SITDE1/'/u01/app/oracle/19.11.1/dbhome_1' to OEL7SITDE1/'/u01/app/oracle/19.0.0/dbhome_1' (which has been updated to 19.15.0.0).

dlpx6014> sourceconfig
dlpx6014 sourceconfig> ls
Objects
NAME       REPOSITORY                                     LINKINGENABLED
..
cdb19c2    OEL7SITDE1/'/u01/app/oracle/19.11.1/dbhome_1'  true
..
PDB19C21   OEL7SITDE1/'/u01/app/oracle/19.11.1/dbhome_1'  true

dlpx6014 sourceconfig 'PDB19C21' update *> set repository=OEL7SITDE1/'/u01/app/oracle/19.0.0/dbhome_1' 
dlpx6014 sourceconfig 'PDB19C21' update *> commit

Each PDB in the dSource container should be updated on the Delphix side, whether it is currently linked as a dSource or not, as it exists within Delphix as a discovered configuration despite not being used.

PDB19C22 (not currently linked.

dlpx6014 sourceconfig> select PDB19C22
dlpx6014 sourceconfig 'PDB19C22'> update
dlpx6014 sourceconfig 'PDB19C22' update *> set repository=OEL7SITDE1/'/u01/app/oracle/19.0.0/dbhome_1'
dlpx6014 sourceconfig 'PDB19C22' update *> commit

The container database, CDB19C2, repository also needs to be aligned with the new Oracle Home that is running 19.15.0.0.

dlpx6014 sourceconfig> select cdb19c2
dlpx6014 sourceconfig 'cdb19c2'> update
dlpx6014 sourceconfig 'cdb19c2' update *> set repository=OEL7SITDE1/'/u01/app/oracle/19.0.0/dbhome_1' 
dlpx6014 sourceconfig 'cdb19c2' update *> commit

After the repository updates the source CDB and each of its PDB should now reflect the patched Oracle Home.

dlpx6014 sourceconfig 'cdb19c2'> 

dlpx6014 sourceconfig> ls
Objects
NAME       REPOSITORY                                     LINKINGENABLED
..
cdb19c1    OEL7SITDE1/'/u01/app/oracle/19.0.0/dbhome_1'   true
..
PDB19C21   OEL7SITDE1/'/u01/app/oracle/19.0.0/dbhome_1'   true
PDB19C22   OEL7SITDE1/'/u01/app/oracle/19.0.0/dbhome_1'   true
..

Operations
create

 

Upgrade the VPDB target Container Database and the VPDB it holds

The virtual pluggable database, VPDBRU1, and the container database, RCDB2, as they exist prior to patching show the following details.

[oracle@oel7sitde2 ~]$ echo $ORACLE_SID
rcdb2

[oracle@oel7sitde2 ~]$ echo $ORACLE_HOME
/u01/app/oracle/19.11.1/dbhome_1

[oracle@oel7sitde2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 11:36:39 2022
Version 19.11.1.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.1.0.0


SQL> set lines 132
SQL> select dbid, name, db_unique_name, open_mode, database_role from v$database;

      DBID NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
---------- --------- ------------------------------ -------------------- ----------------
 340104261 RCDB2     rcdb2                          READ WRITE           PRIMARY

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 VDBRU1                         READ WRITE NO

The container database in this case is also registered in the Oracle GRID Infrastructure.

[oracle@oel7sitde2 ~]$ srvctl config database -d rcdb2
Database unique name: rcdb2
Database name: rcdb2
Oracle home: /u01/app/oracle/19.11.1/dbhome_1
Oracle user: grid
Spfile: +DATA/RCDB2/PARAMETERFILE/spfile.265.1093971851
Password file: 
Domain: plb.internal
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services: 
OSDBA group: 
OSOPER group: 
Database instance: rcdb2

Prior to relocating the VPDB target container database, RCDB2, it will need to be shutdown.  Oracles GRID Infrastructure command srvctl is used to perform this, sqlplus can also be used.

[oracle@oel7sitde2 ~]$ srvctl stop database -d rcdb2 -o immediate

The Oracle Homes in play in the container database hosting RCDB2 are the following:

dbhome1:/u01/app/oracle/19.0.0/dbhome_1:N            # added by plb during install of 19.15.0.0
dbhome2:/u01/app/oracle/19.0.0/dbhome_2:N            # added by plb during install of 19.3.0.0
dbhome3:/u01/app/oracle/19.11.1/dbhome_1:N           # added by plb during install of 19.11.1.0

The User supplied container database is to be migrated from home /u01/app/oracle/19.11.1/dbhome_1 to /u01/app/oracle/19.0.0/dbhome_1 (which has been patched to 19.15.0.0.)
 

OPatch and the opatch lsinventory command can be used to confirm the patch levels of each Oracle Home.

[oracle@oel7sitde2 ~]$ . oraenv
ORACLE_SID = [dbhome1] ? dbhome3
The Oracle base has been changed from /u01/app/orabase to /u01/app/oracle
[oracle@oel7sitde2 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory | egrep 'Patch description'
Patch description:  "OCW RELEASE UPDATE REVISION 19.11.1.0.0 (32844491)"
Patch description:  "Database Release Update Revision : 19.11.1.0.210720 (32844504)"

[oracle@oel7sitde2 ~]$ . oraenv
ORACLE_SID = [rcdb2] ? dbhome1
The Oracle base has been changed from /u01/app/oracle to /u01/app/orabase
[oracle@oel7sitde2 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory | egrep 'Patch description'
Patch description:  "OCW RELEASE UPDATE 19.15.0.0.0 (33815596)"
Patch description:  "Database Release Update : 19.15.0.0.220419 (33806152)"

Adjust the Oracle Home held be the GRID Infrastructure resource for this container database to point to the patched Oracle Home

Modify the clusterware/grid infrastructure resource to use the new home

oracle@oel7sitde2 ~]$ srvctl modify database -d rcdb2 -oraclehome /u01/app/oracle/19.0.0/dbhome_1
[oracle@oel7sitde2 ~]$ srvctl config database -db rcdb2
Database unique name: rcdb2
Database name: rcdb2
Oracle home: /u01/app/oracle/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/RCDB2/PARAMETERFILE/spfile.265.1093971851
Password file: 
Domain: plb.internal
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services: 
OSDBA group: 
OSOPER group: 
Database instance: rcdb2

Start the database from its new home.

[oracle@oel7sitde2 ~]$ echo $ORACLE_SID
dbhome1
[oracle@oel7sitde2 ~]$ egrep rcdb2 /etc/oratab
rcdb2:/u01/app/oracle/19.0.0/dbhome_1:N        # line added by Agent
[oracle@oel7sitde2 ~]$ . oraenv
ORACLE_SID = [dbhome1] ? rcdb2
The Oracle base remains unchanged with value /u01/app/orabase
[oracle@oel7sitde2 ~]$ srvctl start database -db rcdb2

Login to the database and open the VPDB (if it is not already open) in preparation for the execution of datapatch.

[oracle@oel7sitde2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 14:43:30 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 VDBRU1              MOUNTED

SQL> alter pluggable database vdbru1 open;

Pluggable database altered.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 VDBRU1                         READ WRITE NO

The CDB and VPDB are still sitting at the earlier patch level and require datapatch be run.  Execute Oracle datapatch against the CDB and all open VPDB will be patched at the same time.  Any VPDB that is not appearing in the CDB or is seen in a mounted state will not be patched.

oracle@oel7sitde2 ~]$ $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 19.15.0.0.0 Production on Thu Jul 21 15:37:11 2022
Copyright (c) 2012, 2022, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/orabase/cfgtoollogs/sqlpatch/sqlpatch_3422_2022_07_21_15_37_11/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

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

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.15.0.0.0 Release_Update 220331125408: Installed
  PDB CDB$ROOT:
    Applied 19.11.1.0.0 Release_Update_Revision 210712203320 successfully on 20-JUL-22 03.27.11.904881 PM
  PDB PDB$SEED:
    Applied 19.11.1.0.0 Release_Update_Revision 210712203320 successfully on 20-JUL-22 03.27.15.688624 PM
  PDB VDBRU1:
    Applied 19.11.1.0.0 Release_Update_Revision 210712203320 successfully on 22-JAN-22 05.14.39.606304 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED VDBRU1
    No interim patches need to be rolled back
    Patch 32844504 (Database Release Update Revision : 19.11.1.0.210720 (32844504)):
      Rollback from 19.11.1.0.0 Release_Update_Revision 210712203320 to 19.11.0.0.0 Release_Update 210413004009
    Patch 33806152 (Database Release Update : 19.15.0.0.220419 (33806152)):
      Apply from 19.11.0.0.0 Release_Update 210413004009 to 19.15.0.0.0 Release_Update 220331125408
    No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...done
Patch 32844504 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_RCDB2_CDBROOT_2022Jul21_15_37_28.log (no errors)
Patch 33806152 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_RCDB2_CDBROOT_2022Jul21_15_37_31.log (no errors)
Patch 32844504 rollback (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_RCDB2_PDBSEED_2022Jul21_15_39_45.log (no errors)
Patch 33806152 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_RCDB2_PDBSEED_2022Jul21_15_39_47.log (no errors)
Patch 32844504 rollback (pdb VDBRU1): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_RCDB2_VDBRU1_2022Jul21_15_39_44.log (no errors)
Patch 33806152 apply (pdb VDBRU1): SUCCESS
  logfile: /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_RCDB2_VDBRU1_2022Jul21_15_39_45.log (no errors)
SQL Patching tool complete on Thu Jul 21 15:41:59 2022

Confirm the container database and its resident virtual pluggable databases have been patched.

[oracle@oel7sitde2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 21 15:42:51 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL>show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  YES
         3 VDBRU1                         READ WRITE YES
         
SQL> set pages 500
set lines 200
col action format a7
col description format a70
col logfile format a130
col status format a7

select CON_ID,PATCH_UID,PATCH_ID,PATCH_TYPE,substr(ACTION,1,7) ACTION,SOURCE_VERSION,TARGET_VERSION,DESCRIPTION,substr(STATUS,1,10) INSTALL_STATUS,to_char(ACTION_TIME,'YYYY-MM-DD HH24:MI') INSTALL_DATE, LOGFILE from CDB_REGISTRY_SQLPATCH order by con_id, to_char(CDB_REGISTRY_SQLPATCH.ACTION_TIME,'YYYY-MM-DD HH24:MI')  desc;SQL> SQL> SQL> SQL> SQL> SQL> SQL> 

    CON_ID  PATCH_UID    PATCH_ID PATCH_TYPE ACTION  SOURCE_VERSION  TARGET_VERSION  DESCRIPTION                                INSTALL_STATUS
---------- ---------- ---------- ---------- ------- --------------- --------------- ---------------------------------------------------------------------- ----------------------------------------
INSTALL_DATE     LOGFILE
---------------- ----------------------------------------------------------------------------------------------------------------------------------
     1   24340404    32844504 RUR        ROLLBAC 19.11.1.0.0     19.11.0.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-07-21 15:41 /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_RCDB2_CDBROOT_2022Jul21_15_37_28.log

     1   24713297    33806152 RU        APPLY   19.11.0.0.0     19.15.0.0.0     Database Release Update : 19.15.0.0.220419 (33806152)           SUCCESS
2022-07-21 15:41 /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_RCDB2_CDBROOT_2022Jul21_15_37_31.log

     1   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-07-20 15:27 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_RCDB2_CDBROOT_2022Jul20_15_19_57.log

     1   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-14 16:58 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_RCDB2_CDBROOT_2022Jan14_16_57_03.log

SQL> alter session set container=VDBRU1;

Session altered.

SQL> set pages 500
set lines 200
col action format a7
col description format a70
col logfile format a130
col status format a7

select CON_ID,PATCH_UID,PATCH_ID,PATCH_TYPE,substr(ACTION,1,7) ACTION,SOURCE_VERSION,TARGET_VERSION,DESCRIPTION,substr(STSQL> ATUS,1,10) INSTALL_STATUS,to_char(ACTION_TIME,'YYYY-MM-DD HH24:MI') INSTALL_DATE, LOGFILE from CDB_REGISTRY_SQLPATCH order by con_id, to_char(CDB_REGISTRY_SQLPATCH.ACTION_TIME,'YYYY-MM-DD HH24:MI')  desc;SQL> SQL> SQL> SQL> SQL> SQL> 

    CON_ID  PATCH_UID    PATCH_ID PATCH_TYPE ACTION  SOURCE_VERSION  TARGET_VERSION  DESCRIPTION                                INSTALL_STATUS
---------- ---------- ---------- ---------- ------- --------------- --------------- ---------------------------------------------------------------------- ----------------------------------------
INSTALL_DATE     LOGFILE
---------------- ----------------------------------------------------------------------------------------------------------------------------------
     3   24713297    33806152 RU        APPLY   19.11.0.0.0     19.15.0.0.0     Database Release Update : 19.15.0.0.220419 (33806152)           SUCCESS
2022-07-21 15:41 /u01/app/orabase/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_RCDB2_VDBRU1_2022Jul21_15_39_45.log

     3   24340404    32844504 RUR        ROLLBAC 19.11.1.0.0     19.11.0.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-07-21 15:41 /u01/app/orabase/cfgtoollogs/sqlpatch/32844504/24340404/32844504_rollback_RCDB2_VDBRU1_2022Jul21_15_39_44.log

     3   24340404    32844504 RUR        APPLY   19.3.0.0.0        19.11.1.0.0     Database Release Update Revision : 19.11.1.0.210720 (32844504)       SUCCESS
2022-01-22 17:14 /u01/app/oracle/cfgtoollogs/sqlpatch/32844504/24340404/32844504_apply_CDB19C2_PDB19C21_2022Jan22_17_10_02.log

     3   22862832    29517242 RU        APPLY   19.1.0.0.0        19.3.0.0.0        Database Release Update : 19.3.0.0.190416 (29517242)           SUCCESS
2022-01-13 12:44 /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB19C2_PDBSEED_2022Jan13_12_43_36.log

At this point, the target user provided container database and its virtual pluggable databases have been patched.  Delphix must now be adjusted to reflect the new Oracle Home these databases now use.

Migrate the Container Database and any Virtual Pluggable databases to their new repository/Oracle Home

In the scenario shown below the Delphix changes are implemented through the Delphix command line.  The update for the datasets can also be performed through the Delphix GUI.

Update the VPDB to use its new and patched Oracle Home.

dlpx6014 sourceconfig> select vdbru1
dlpx6014 sourceconfig 'vdbru1'> update
dlpx6014 sourceconfig 'vdbru1' update *> set repository=OEL7SITDE2/'/u01/app/oracle/19.0.0/dbhome_1'
dlpx6014 sourceconfig 'vdbru1' update *> commit

Update the CDB that holds the VPDB with its repository/Oracle Home.

dlpx6014 sourceconfig> select rcdb2 
dlpx6014 sourceconfig 'rcdb2'> update
dlpx6014 sourceconfig 'rcdb2' update *> set repository=OEL7SITDE2/'/u01/app/oracle/19.0.0/dbhome_1'
dlpx6014 sourceconfig 'rcdb2' update *> commit

The source configuration for each database will now show the patched home on the Delphix side.

dlpx6014 sourceconfig> ls

NAME       REPOSITORY                                    LINKINGENABLED
..
rcdb2      OEL7SITDE2/'/u01/app/oracle/19.0.0/dbhome_1'  true
vdbru1     OEL7SITDE2/'/u01/app/oracle/19.0.0/dbhome_1'  true
..

 

Establish the new Repository Templates to be used during Delphix provision, refresh, and rewind operations

Repository templates cannot be updated from the perspective of the repository after the patching of Oracle to use a new Oracle Home, the process to alter them would be to create a new one and delete the old ones when they are no longer in use.

The provision/refresh repository template.

dlpx6014 repository template> create 
dlpx6014 repository template create *> ls
Properties
    type: SourceRepositoryTemplate
    name: (required)
    container: (required)
    repository: (required)
    template: (required)
dlpx6014 repository template create *> set name=vdbru1_repo_template_1500MSGA
dlpx6014 repository template create *> set container=PDB19C21 
dlpx6014 repository template create *> set template=VCDB_Template_1500MSGA
dlpx6014 repository template create *> set repository=OEL7SITDE2/'/u01/app/oracle/19.0.0/dbhome_1' 
dlpx6014 repository template create *> commit

The rewind template.

dlpx6014 repository template create *> set name=vdbru1_rewind_repo_template_1500MSGA
dlpx6014 repository template create *> set repository=OEL7SITDE2/'/u01/app/oracle/19.0.0/dbhome_1' 
dlpx6014 repository template create *> set container=vdbru1 
dlpx6014 repository template create *> set template=VCDB_Template_1500MSGA
dlpx6014 repository template create *> commit