Patching a Multi-Tenant Database Using Oracle Enterprise Manager Fleet Maintenance

Before you Begin

This tutorial guides you through the required steps for patching a Multi-Tenant Oracle database using Oracle Enterprise Manager Fleet Maintenance. The time to complete this task is approximately 1 hour.

Background

Database Fleet Maintenance, part of the Database Lifecycle Management Pack, allows administrators to maintain groups or pools of Oracle Homes and associated databases by applying database updates and patches. Database Fleet Maintenance can effectively patch Databases like Grid Infrastructure (GI), Single Instance High Availability (SIHA), and RAC configurations.

For more information on Fleet Maintenance see Database Fleet Maintenance in Oracle Enterprise Manager Cloud Control Database Lifecycle Management Administrator's Guide.

Process Overview

Fleet Maintenance Overview — Description of the illustration Fleet_Overview.png

Oracle Multitenant database customers can patch an individual pluggable database or patch all pluggable databases along with the CDB as needed.

Patch or Upgrade many databases as one multi-tenant: The investment of time and effort to patch (or Upgrade) one multitenant container database results in patching all of its many pluggable databases.
Patch one PDB at a time: To patch (or Upgrade) a single pluggable database, simply unplug/plug to a multitenant container database at a different Oracle Database software version.

What Do You Need?

Enterprise Manager version 13.2 and above. The latest Release Update of EM 13.4 is recommended. For the latest Enterprise Manager Release Updates, see Installation and Upgrade.
An Oracle database discovered in Enterprise Manager. This tutorial uses an Oracle database 18c named ORCL_PDBATT_SAMPLE, version 18.3.0.0.0, running on a Linux system.
A reference Oracle database home, which is a database Oracle Home already patched to the desired patch level. This reference home will be used to create the Gold Image. This tutorial is using an existing Oracle Home, patched to the level version 18.10.0.0.0 and discovered in EM. For more information on how to discover an oracle home see this Discovery section. For concepts and supported image creation tasks, see also Creating a Gold Image and Fleet Maintenance Emergency Patching.
Access to the target database and its host. In this example, the EM administrator has root access to the host the database resides on and privileged credentials set up for the database. For more information on how to set up your access see Checking Host Readiness Before Provisioning or Patching.
Access to the Enterprise Manager command line interface utility, emcli. For more information on how to set up EM CLI see Getting Started with EM CLI.

Credentials that will be used in this tutorial:

<Named Credential>: Named credential for the host where new Oracle home will be deployed
<Credential Owner>: The Enterprise Manager user who owns this Named Credential
This credential is used to run scripts as root

Credential	Description
Name	User specified unique name of the operation
Normal Credential	Enter in the format of <Named Credential: Credential Owner> <Named Credential>: Named credential for the host where new Oracle home will be deployed <Credential Owner>: The Enterprise Manager user who owns this Named Credential
Privilege Credential	Enter in the format of <Named Credential: Credential Owner>
Database Credential	Enter in the format of <Database Credential: Credential Owner> SYSDBA credentials for the database

Step 1: Analyze Your Environment

Before you start a patching task, run an analysis of your environment and check the access to the host you will be patching.

First, check your access to the database host. Log into to the Enterprise Manager console as an Administrator and navigate to Setup > Security > Named Credentials. Select the credential for the database host and click Edit. Click on Test and Save, to test your access.
Run the Configuration Pollution Detection to analyze your database estate. Click Targets > Databases. Then, under Administration, click Software Standardization Advisor. The resulting graphics depict the current configuration and the recommended configuration. To generate reports, click Generate Report. The Recommended Configuration spreadsheet shows you the recommended database versions to standardize on. In this case, the recommended patch level is 18.10.0.0.0.
Review the status of your database targets. Ensure that the target database to be patched shows as UP in the status field, represented by a green arrow.

Step 2: Create and Subscribe the Gold Image

Log in to the host where you have set up EM CLI and create an input file with the details of your reference Oracle Home. In this case, the input file is called db18c.inp, saved in the /home/oracle/patch_files directory. This file includes the exact name of the reference Oracle Home as it is discovered in EM and it looks like this:

IMAGE_NAME=DB18 
IMAGE_DESCRIPTION=Gold Image 18c 
REF_TARGET_NAME=refdb18c_home1_2020_05_13_04_10_9.sample.com
IMAGE_SWLIB_LOC=Fleet Maintenance/
STORAGE_TYPE_FOR_SWLIB=OmsShared
STORAGE_NAME_FOR_SWLIB=default_loc
REF_HOST_CREDENTIALS=ORACLE:MYADMIN
WORKING_DIRECTORY=/tmp
VERSION_NAME=18.10

Description of Input File Parameters

Parameter	Description
IMAGE_NAME, IMAGE_DESCRIPTION, VERSION_NAME	User specified unique name for the operation
REF_TARGET_NAME	The Oracle home in Enterprise Manager that has all current patches applied and can be used as reference Oracle home.
IMAGE_SWLIB_LOC	The path in the software library(Enterprise > Provisioning and Patching > Software Library) where the gold image will be stored. This should be created by the SYSMAN user.
STORAGE_TYPE_FOR_SWLIB	Storage type configured for software library.
STORAGE_NAME_FOR_SWLIB	Storage name for the software library, located at: Setup > Provisioning and Patching > Software Library.
REF_HOST_CREDENTIALS	The credentials used to connect to the target host. The format for the credential is: <name:credential owner>
workingDir	Path of temp directory if location needs to be different from /tmp.

A standard software configuration is referred to as a Gold Image. Create a new Gold Image based on your reference Oracle Home:
```
emcli login -username=SYSMAN
emcli db_software_maintenance -createSoftwareImage -input_file="data:/home/oracle/patch_files/db18c.inp"
```
Output:
Create Gold Image operation has been submitted successfully with the instance name : 'CreateGoldImageProfile_SYSMAN__21_2020_06_45_AM' and execution_guid='AAFAB54748EF28A0E0533400000A43D6' You can track the status of operation using the following: EMCLI: emcli get_instance_status -exec=AAFAB54748EF28A0E0533400000A43D6 Browser: https://EM Host:EM Port/em/faces/core-jobs-procedureExecutionTracking?executionGUID=AAFAB54748EF28A0E0533400000A43D6 Verb createSoftwareImage completed successfully

Tip:
To review the execution activity details in Enterprise Manager navigate, Enterprise > Provisioning and Patching > Procedure Activity. Click on your execution under the Run column.
Check the list of all Gold Images available for deployment. Your newly created image should be on this list:
```
emcli db_software_maintenance -getImages
```
Output:
=================================================================================================== IMAGE ID IMAGE NAME DESCRIPTION VERSION CREATION DATE OWNER =================================================================================================== AAFAB54748EF28A0E0533400000A43D6 DB18 Gold Image 18c 18.0.0.0.0 2020-07-21 20:45:50.0 MYADMIN --------------------------------------------------------------------------------------------------- Total Rows: 1 Verb getImages completed successfully
View the software version available for a specific image:
```
emcli db_software_maintenance -getVersions -image_id=AAFAB54748EF28A0E0533400000A43D6
```
Output:
=================================================================================================== POSITION VERSION ID VERSION NAME DATE CREATED EXTERNAL ID HASH CODE STATUS =================================================================================================== 1 A5F368AF3DE87756E053 18.10 2020-05-18 21:28:43. oracle:default[...] C2891773972:B5D7872C CURRENT
Verify that the newly created Gold Image is applicable. Execute the following command to perform a check to identify the patch conflicts:
```
emcli db_software_maintenance -checkApplicability -image_id=" AAFAB54748EF28A0E0533400000A43D6 " -target_list=ORCL_PDBATT_SAMPLE -target_type=oracle_database > /home/oracle/patch_files/applicability.out
cat /home/oracle/patch_files/applicability.out
```
Output:
Checking applicability for target [ORCL_PDBATT_SAMPLE]... Image is applicable More Information: Image has 996 additional bugs fixed. Following are the bugs list: =================================================================================================== # BUG# PATCH# PATCH UPI BUG DESCRIPTION =================================================================================================== 1 27006120 30872794 INFRASTRUCTURE FOR BACKPORTING [...] Total Rows: 996 Following are the patches: =================================================================================================== PATCH# PATCH DESCRIPTION =================================================================================================== 30872794 Database Release Update : 18.10.0.0.200414 (30872794) --------------------------------------------------------------------------------------------------- 30112122 Database Release Update : 18.8.0.0.191015 (30112122) --------------------------------------------------------------------------------------------------- 30805598 OJVM RELEASE UPDATE: 18.10.0.0.200414 (30805598) --------------------------------------------------------------------------------------------------- Total Rows: 3

The report typically shows three distinct categories:
- Applicable: The image and database target contain the same set of bug fixes. This image can be applied to the specified target.
- Applicable and Image has more bug fixes: The image contains more bug fixes than those applied on the database, the list of extra bugs is displayed. The image can be applied to the specified target.
- Not Applicable: The database contains more bug fixes than those included in the image, the list of missing bugs is displayed. In this case, you have to create a new version of the image that includes the missing bug fixes. In this scenario, the image is applicable.
Subscribe the database target ORCL_PDBATT_SAMPLE to the Gold Image you created:
```
emcli db_software_maintenance -subscribeTarget -target_name=ORCL_PDBATT_SAMPLE -target_type=oracle_database -image_id="AAFAB54748EF28A0E0533400000A43D6"
```
Output:
Target 'ORCL_PDBATT_SAMPLE' subscribed successfully. Verb subscribeTarget completed successfully

Note:
You can only subscribe one database target at a time to the Gold Image.
Verify the subscription:
```
emcli db_software_maintenance -getImageSubscriptions -image_id=AAFAB54748EF28A0E0533400000A43D6
```
Output:
************************************************************************************** TARGETNAME TARGETTYPE VERSIONNAME DATESUBSCRIBED CLUSTER1 CLUSTER 81044 Version 2020-05-18 21:29:08.0 ************************************************************************************** Total Rows:1

Step 3: Stage and Deploy the Software

In this step you copy the Gold Image previously created from software library to the new Oracle Home. The entire patching process consists of two phases: the staging and deployment of the software in this step, and the Update Database operation in the following step.

Description of Variables used in the step:

Variable	Description
name	User specified unique name for the operation.
Purpose	`'DEPLOY_DB_SOFTWARE'` is used to deploy database software. `'migrate_listener'` is used to migrate the listener service.
Target_type	The target type the Container Database: `'oracle_database'`. The target type for the Pluggable Database: `'oracle_pdb'`.
Target_list	Comma separated list of all homogenous targets to be patched. For stage and deploy the operation is done at the Oracle Home level. Provide a single database name in the Oracle Home, there is no need to provide all the names under an Oracle Home.

Create an input file for deploy1810_hr.inp This file simply has the location of the new Oracle Home where the database software will be configured. In this case, the content of this file is:
```
NEW_ORACLE_HOME_LIST=/u01/app/oracle/product/18/db_home2
dispatchLoc=/u02/app/temp/dispatchloc
workingDir=/u02/app/temp
```
Stage and deploy the software:
```
emcli db_software_maintenance -performOperation -name="Deploy Database Software" -purpose='DEPLOY_DB_SOFTWARE' -target_type='oracle_database' -target_list='ORCL_PDBATT_SAMPLE' -normal_credential="NC_HOST_2018-07-21-054557:SYSMAN" -privilege_credential="ROOT:SYSMAN" -input_file="data:/home/oracle/patch_files/deploy1810_hr.inp"
```
Output:
Processing target "ORCL_PDBATT_SAMPLE"... Checking image subscription... Check image subscription passed. Oracle home target list is F67B751F23275F49281579AAF8B164DC Operation 'Deploy Db' created successfully. Deploy Oracle home software operation for Database ORCL_PDBATT_SAMPLE can be monitored using: EMCLI: emcli get_instance_status -exec=AAFAB54748EF28A0E0533400000A43D6 -details -xml Browser: https://EM Host:EM Port/em/faces/core-jobs-procedureExecutionTracking?executionGUID=AAFAB54748EF28A0E0533400000A43D6 Verb performOperation completed successfully
Migrate the listener to the newly deployed Oracle Home with the following command:
```
emcli db_software_maintenance -performOperation -name="Migrate Listener" -purpose=migrate_listener -target_type=oracle_database -target_list="ORCL_PDBATT_SAMPLE" -normal_credential="ORACLE:MYADMIN" -privilege_credential="ROOT:MYADMIN"
```
Output:
Processing target "ORCL_PDBATT_SAMPLE"... Checking if target is already running from the current version of the image... Checking image subscription... Check image subscription passed. Operation 'Migrate Listener' created successfully. Listener migration for ORCL_PDBATT_SAMPLE can be monitored using: EMCLI: emcli get_instance_status -exec=AAFAB54748EF28A0E0533400000A43D6 -details -xml Browser: https://EM Host:EM Port/em/faces/core-jobs-procedureExecutionTracking?executionGUID=AAFAB54748EF28A0E0533400000A43D6 Verb performOperation completed successfully
To review the execution activity details in Enterprise Manager, navigate to Enterprise > Provisioning and Patching > Procedure Activity. Click on Fleet_(migrate_date and time) under the Run column.

Step 4: Update Your Database

This step migrates the database targets running from the old Oracle Database home to the newly created Oracle Database home. You can move one or multiple databases in same operation by specifying the database list under -target_list. This command can be used to perform minor upgrades and major upgrades.

Description of Variables used in the step:

Variable	Description
name	User specified unique name for the operation.
Purpose	`DEPLOY_CDB` is used to deploy a new container database. `UPDATE_PDB` is used to update pluggable database attaching it to the new container database. `ROLLBACK_PDB` is used to roll back the update procedure in case of errors or failure.
Target_type	The target type the Container Database: `'oracle_database'`. The target type for the Pluggable Database: `'oracle_pdb'`.
db_prefix	User defined database name prefix for the new Container database.
Database_credential	SYSDBA credentials for the database

Deploy a new container database that will house the individual PDB's:
```
emcli db_software_maintenance -performOperation -name="Create New CDB" -purpose=DEPLOY_CDB -target_type=oracle_database -target_list="ORCL_CDB_SAMPLE" -db_prefix="TECDB" -normal_credential="CONTAINER_ORACLE:SYSMAN" -privilege_credential="CONTAINER_ROOT:SYSMAN" -database_credential=""
```
Output:
Processing target "ORCL_CDB_SAMPLE"... Checking image subscription... Check image subscription passed. Operation 'DeployHome' created successfully. EMCLI: emcli get_instance_status -exec=AAFAB54748EF28A0E0533400000A43D6 -details -xml Browser: https://EM Host:EM Port/em/faces/core-jobs-procedureExecutionTracking?executionGUID=AAFAB54748EF28A0E0533400000A43D6 Verb performOperation completed successfully SQL> select name from v$database; NAME --------- TECDB001

Note:
You can also update PDB's to an existing CDB using the ATTACH_CDB verb. The existing CDB should be at Gold Image standard before attaching PDB's. For more information see: MOS Note 2434260.1.
Update the database targets to switch to the new Oracle Home by relocating individual PDB’s.
```
emcli db_software_maintenance -performOperation -purpose=UPDATE_PDB -target_list="ORCL_PDBATT_SAMPLE" -target_type="oracle_pdb" -name=”Switch_Operation" –description=”Switch Database Home Operation" -normal_credential="CONTAINER_ORACLE:SYSMAN" -privilege_credential="CONTAINER_ROOT:SYSMAN" -database_credential="ORCL_PDB:SYSMAN"
```
Output:
Processing target "ORCL_PDBATT_SAMPLE"... Checking if target is already running from the current version of the image... Check Passed. Checking image subscription... Check image subscription passed. Operation '”Switch_Operation –description=”Switch Database Home Operation' created successfully. EMCLI: emcli get_instance_status -exec=AAFAB54748EF28A0E0533400000A43D6 -details -xml Browser: https://EM Host:EM Port/em/faces/core-jobs-procedureExecutionTracking?executionGUID=AAFAB54748EF28A0E0533400000A43D6 Verb performOperation completed successfully
Review the execution activity details in Enterprise Manager, Enterprise > Provisioning and Patching > Procedure Activity.

Optional Rollback Step:

If the database needs to be switched back to the original Oracle Home, you may perform a Rollback operation at this time:

emcli db_software_maintenance -performOperation -name="Rollback Home" -purpose=ROLLBACK_PDB -target_type=oracle_pdb -target_list="ORCL_PDBATT_SAMPLE" -normal_credential="ORACLE:MYADMIN" -privilege_credential="ROOT:MYADMIN"

Output:

Checking image subscription... Check image subscription passed. Operation 'Rollback' created successfully. [...]

Step 5: Cleanup the Old Oracle Homes

A cleanup operation is recommended to remove old Oracle Home that is no longer used. This will remove old Oracle Home and free up valuable disk space.

First, run a reportOnly cleanup operation to list the possible Oracle Homes to be cleaned:
```
emcli db_software_maintenance -performOperation -name="Cleanup old oracle homes" -purpose=CLEANUP_SOFTWARE -target_type=oracle_database -normal_credential=ORACLE:MYADMIN -privilege_credential=ROOT:MYADMIN -target_list=ORCL_PDBATT_SAMPLE -workDir=/tmp -reportOnly=true
```
Output:
Processing cleanup database software. The following Oracle homes are chosen for removal by policy. =================================================================================================== NO. HOST ORACLE HOME TARGET NAME NO. OF ASSOCIATED TARGETS =================================================================================================== 1 ORCL_PDBATT_SAMPLE /u01/app/1806/hr ORCL_PDBATT_SAMPLE 0 --------------------------------------------------------------------------------------------------- Total Rows: 1 Verb performOperation completed successfully
Cleanup the Oracle Home listed in the report:
```
emcli db_software_maintenance -performOperation -name="Cleanup old oracle homes" -purpose=CLEANUP_SOFTWARE -target_type=oracle_database -normal_credential=ORACLE:MYADMIN -privilege_credential=ROOT:MYADMIN -target_list=ORCL_PDBATT_SAMPLE -workDir=/tmp
```
Output:
Processing cleanup database software. The following Oracle homes are chosen for removal by policy. =================================================================================================== NO. HOST ORACLE HOME TARGET NAME NO. OF ASSOCIATED TARGETS =================================================================================================== 1 ORCL_PDBATT_SAMPLE /u01/app/1806/hr ORCL_PDBATT_SAMPLE 0 --------------------------------------------------------------------------------------------------- Total Rows: 1 Cleaning up targets OraDB18Home3_6_db.subnetvcn.sample.com_3478
To review the cleanup activity details in Enterprise Manager, go to Enterprise > Provisioning and Patching > Procedure Activity. Click on CLEANUP_SOFTWARE_(date and time) under the Run column.

Learn More

Oracle Enterprise Manager general information
Oracle Enterprise Manager Documentation Library
Oracle Enterprise Manager Command Line Interface
Database Fleet Maintenance
For a complete list and definition of all parameters and variables used in this tutorial see:
db_software_maintenance and Fleet Management Software

Title and Copyright Information

Patching a Multi-Tenant Database Using Oracle Enterprise Manager Fleet Maintenance

F37044-01

January 2021

Patching a Multi-Tenant Database Using Oracle Enterprise Manager Fleet Maintenance

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs) and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government end users are "commercial computer software" or "commercial computer software documentation" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in the license contained in the applicable contract. The terms governing the U.S. Government's use of Oracle cloud services are defined by the applicable contract for such services. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc, and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.