Oracle Base Database patching to 19.17 using DBCLI

 

OCI - (Oracle Cloud Infrastructure)

Intro

Cloud platforms are booming and many organizations are planning on a cloud journey to get a competitive advantage.  The cloud market is vast, with a diverse selection of services. It's not easy to select a suitable cloud platform for an organization.  There are certain criteria you need to consider when selecting a platform. 

• Certifications & Standards
• Technologies & Service Roadmap
• Data Security, Data Governance, and Business policies
• Service Dependencies & Partnerships
• Contracts, Commercials & SLAs
• Reliability & Performance
• Migration Support, Vendor Lock-in & Exit Planning
• Business health & Company profile
• Cost of the cloud services and License.

All these factors play a huge role when selecting a cloud platform suited for your system workloads. If your IT infrastructure consists of many Oracle databases,  I would say there is no better place than OCI (Oracle Cloud Infrastructure) to host your workload.  Oracle has been there for the market for a long time and gives immense selection when hosting Oracle workloads. 

Oracle cloud Infrastructure (OCI) - Part 2 : OCI -VM Server and DB Patching

 

Acronym - 

OCI - Oracle Cloud Infrastructure  

Intro

Security is a major concern in current data era and value of the data is getting higher in every minute .To keep the data secured , organization are trying hard to meet the industry standard compliance scores for their systems. database security plays a major role in any organization. To address these vulnerabilities organizations needs to be proactive to update their systems with the latest security patches. Significant  part of the maintenance is applying the psu patches quarterly to meet the database compliance requirements. This process required considerable amount of time to stage the patches and test in lower environments.

OCI enables really stable automation to all the maintenance work such as patching , creating homes and server os patching etc ... .Also oracle has ease this task by taking the responsibility of staging . 

In-addition, there are many tools introduced by oracle for addressing these work , major tools are 

1. dbaascli

2. exacli

3. dbcli

4. exadbcpatchmulti

To easy the work on bare metal and vm server patching in OCI introduces new tool called dbcli.

We have two ways of patching the database:

1. Oracle Cloud GUI
2. Command line tool 

In this article I will illustrate about the commands line patching using dbcli.

Now we will discuss about the steps and best practices for vm patching. 

This patching includes server , db and grid components.

1. Gather environment details

First we need to gather environment details using dbcli tool

List database details

[root@dbsdpl21 ~]# dbcli  list-databases
ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
08bae22b-c9c6-4fdf-b90e-894e43d3b431     PWSH01     Rac      19.11.0.0.0          true       Oltp              ASM        Configured   42935588-54f1-4f08-98cb-5f95d95cb881

List of database homes

[root@dbsdpl21 ~]# dbcli list-dbhomes

ID                                       Name                 DB Version                               Home Location                                 Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
42935588-54f1-4f08-98cb-5f95d95cb881     OraDB19000_home1     19.11.0.0.0  

List storages

[root@dbsdpl21 ~]# dbcli list-dbstorages

ID                                       Type   DBUnique Name        Status
---------------------------------------- ------ -------------------- ----------
60728c08-a03e-409e-80cc-6bea218efc79     Asm    PWSH01_yyz16x        Configured

List components

[root@dbsdpl21 ~]# dbcli describe-component
System Version
---------------
21.2.3.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
GI                                        19.11.0.0.0           19.12.0.0
DB                                        19.11.0.0.0           19.12.0.0

List storages

[root@dbsdpl21 ~]# dbcli list-dbstorages

ID                                       Type   DBUnique Name        Status
---------------------------------------- ------ -------------------- ----------
60728c08-a03e-409e-80cc-6bea218efc79     Asm    PWSH01_yyz16x        Configured

List available patches

We need to identify which psu are available to patch. dbcli describe-latestpatch will display the currently patches. Oracle will stage these patches for us, ease our work on staging.

[root@dbsdpl21 ~]# dbcli describe-latestpatch
componentType   availableVersion
--------------- --------------------
gi              12.2.0.1.210720
gi              12.1.0.2.210720
gi              18.15.0.0.0
gi              19.12.0.0.0
gi              21.3.0.0.0
db              11.2.0.4.210720
db              12.2.0.1.210720
db              12.1.0.2.210720
db              18.15.0.0.0
db              19.12.0.0.0
db              21.3.0.0.0
[root@dbsdpl21 ~]#

dbcli -h commands gives all the possible commands that we can execute this tool. Also if you are not sure about commands still can check the options using dbcli -h. Please find the sample below.

 
  [root@dbsdpl21 ~]# dbcli update-dbhome -h -- check the update-home options
Usage: update-dbhome [options]
  Options:
    --cloneOcid, -o
      clone OCID
    --cloneType, -cc
      Clone type
    --cloneVersion, -cv
      cloneVersion
  * --dbhomeid, -i
      Db home to be updated
    --help, -h
      get help
    --json, -j
      json output
    --local, -l
      Update Db home Locally
    --node, -n
      Node to be updated
    --parUrl, -pu
      par Url
    --precheck, -p
      Only Analyze the patch
    --sha256sum, -ss
      sha256sum
    --version, -v
      Patch version to be updated
  

Patching Prechecks

OCI vm server patching consist 3 major components.

1. Server patching (updating the os version)
2. Grid patching
3. DB Patching

1. Pre-requisites

Access to the Oracle Cloud Infrastructure Object Storage service,
including connectivity to the applicable Swift endpoint for Object Storage.
Oracle recommend using Service Gateway to enable this access.

• /u01 FS with at least 15Gb of free space
• Clusterware should be up and running
• All DB system nodes running

Backup

Backup your database prior to the patch event.

Non Prod first

Test this patch on non prod (or test) server first

Patching

Before performing patching run pre check commands to validate the environment with these patches.Also make sure to update the cliadm before patching..

I always recommend to patch standby database first before moving to prod.
 

  
  [root@dbsdpl251 ~]#  cliadm update-dbcli

Job details
----------------------------------------------------------------
                     ID:  89f9b8b6-1955-43f8-855e-a5de265bfa44
            Description:  DcsCli patching
                 Status:  Created
                Created:  October 21, 2021 6:42:06 PM UTC
                Message:  Dcs cli will be updated

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
  
  

List Running Jobs

  
  [root@dbsdpl251 ~]# dbcli list-jobs

ID                                       Description                                                                 Created                             Status
---------------------------------------- --------------------------------------------------------------------------- ----------------------------------- ----------
ae6866f3-d990-47e9-8e98-ba2620355ec2     Provisioning service creation                                               Friday, October 15, 2021, 19:49:31 UTC Success
1d1c9757-69ec-4e3b-a203-f0286ca55122     Authentication key update for DCS_ADMIN                                     Friday, October 15, 2021, 20:30:54 UTC Success
bfc170d1-a6f4-4df5-837a-f70c7b982953     Infra upgrade                                                               Friday, October 15, 2021, 20:31:42 UTC Success
f7a6e930-6e33-4c90-bb79-5791fe5b805b     Authentication key update for DCS_ADMIN                                     Friday, October 15, 2021, 20:31:48 UTC Success
6d2a0753-047d-4e72-8c6d-e6abe27a15a6     Infra upgrade                                                               Friday, October 15, 2021, 20:31:50 UTC Success
dc1b0f78-7292-4b3c-a47b-b60e30ccfcda     SSH keys update                                                             Friday, October 15, 2021, 20:33:26 UTC Success
03b9fed6-a69a-4263-89dc-a1497b94599b     Manage AHF telemetry                                                        Friday, October 15, 2021, 20:34:54 UTC Success
5d5ec39b-2cd6-4666-be3e-a16e378a9c51     Manage AHF telemetry                                                        Friday, October 15, 2021, 20:35:19 UTC Success
691faa9f-586c-4a09-9f2f-7e8caae8b0d4     SSH key delete                                                              Friday, October 15, 2021, 20:35:23 UTC Success
b6154703-23a0-47b0-8508-b75bbe1d2e20     SSH key delete                                                              Friday, October 15, 2021, 20:35:31 UTC Success
d9c5168c-3272-49b9-b360-afe7dbe209d7     DG Config service creation                                                  Friday, October 15, 2021, 20:41:54 UTC Success
b8a249cb-f0f3-45f1-9e51-a7ad9a645902     Dataguard new status creation                                               Friday, October 15, 2021, 20:56:24 UTC Success
1cce75d2-c5ce-4fe3-8f5c-26022e6cf5b8     Server Patching Prechecks                                                   Thursday, October 21, 2021, 18:11:01 UTC Success
eb316d24-72b6-4880-9213-755df5a5f691     DBHome Prechecks                                                            Thursday, October 21, 2021, 18:26:19 UTC Success
89f9b8b6-1955-43f8-855e-a5de265bfa44     DcsCli patching                                                             Thursday, October 21, 2021, 18:42:06 UTC Success

[root@dbsdpl251 ~]#
  

1.1 Update cliadm

Check patching status cliadm

  [root@dbsdpl251 ~]# dbcli describe-job -i 89f9b8b6-1955-43f8-855e-a5de265bfa44

Job details
----------------------------------------------------------------
                     ID:  89f9b8b6-1955-43f8-855e-a5de265bfa44
            Description:  DcsCli patching
                 Status:  Success
                Created:  October 21, 2021 6:42:06 PM UTC
               Progress:  100%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------

[root@dbsdpl251 ~]#
  

1.2 Server Precheck

Execute Server precheck

 
[root@dbsdpl21 ~]# dbcli update-server --precheck
{
  "jobId" : "053f8b83-d4d0-4560-9004-ec13d78f1bc0",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "October 15, 2021 19:22:14 PM UTC",
  "resourceList" : [ ],
  "description" : "Server Patching Prechecks",
  "updatedTime" : "October 15, 2021 19:22:14 PM UTC",
  "percentageProgress" : "0%",
  "cause" : null,
  "action" : null
}

This is really convenient feature , once you execute this it will automatically run in background . Status can be checked from describing the job id. For each job there will be unique job id.

 
[root@dbsdpl21 ~]# dbcli describe-job -i 053f8b83-d4d0-4560-9004-ec13d78f1bc0

Job details
----------------------------------------------------------------
                     ID:  053f8b83-d4d0-4560-9004-ec13d78f1bc0
            Description:  Server Patching Prechecks
                 Status:  Running
                Created:  October 15, 2021 7:22:14 PM UTC
               Progress:  0%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for Server Patching  

1.3 DB Precheck

  Execute DB Precheck

[root@dbsdpl21 ~]# dbcli update-dbhome -i 42935588-54f1-4f08-98cb-5f95d95cb881 --precheck
{
  "jobId" : "60b6303f-a92d-4aec-b1f3-dcad87630f09",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "October 15, 2021 17:41:34 PM UTC",
  "resourceList" : [ ],
  "description" : "DBHome Prechecks",
  "updatedTime" : "October 15, 2021 17:41:34 PM UTC",
  "percentageProgress" : "0%",
  "cause" : null,
  "action" : null
}

Check the job status using dbcli describe-job -i.

-- While it's running
[root@dbsdpl21 ~]# dbcli describe-job -i 60b6303f-a92d-4aec-b1f3-dcad87630f09

Job details
----------------------------------------------------------------
                     ID:  60b6303f-a92d-4aec-b1f3-dcad87630f09
            Description:  DBHome Prechecks
                 Status:  Running
                Created:  October 15, 2021 5:41:34 PM UTC
               Progress:  33%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for DBHome patching                                       October 15, 2021 5:41:35 PM UTC     October 15, 2021 5:45:18 PM UTC     Success
DBHome patching                                                          October 15, 2021 5:45:18 PM UTC     October 15, 2021 5:45:18 PM UTC     Running


--- Precheck complete 
[root@dbsdpl21 ~]# dbcli describe-job -i 60b6303f-a92d-4aec-b1f3-dcad87630f09

Job details
----------------------------------------------------------------
                     ID:  60b6303f-a92d-4aec-b1f3-dcad87630f09
            Description:  DBHome Prechecks
                 Status:  Success
                Created:  October 15, 2021 5:41:34 PM UTC
               Progress:  100%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for DBHome patching                                       October 15, 2021 5:41:35 PM UTC     October 15, 2021 5:45:18 PM UTC     Success
DBHome patching                                                          October 15, 2021 5:45:18 PM UTC     October 15, 2021 5:55:45 PM UTC     Success
Post-operations for DBHome patching                                      October 15, 2021 5:55:45 PM UTC     October 15, 2021 5:56:27 PM UTC     Success
Install object store swift module                                        October 15, 2021 5:55:51 PM UTC     October 15, 2021 5:56:26 PM UTC     Success

2. Patching

-– rolling fashion

Patching happen in rolling fashion.

We need to follow the correct patching sequence. start with the server patch. 

This server patch includes with grid patching.

2.1 Server Patching

[root@dbsdpl251 ~]# dbcli update-server
{
  "jobId" : "4b5e32fe-bd08-4684-b49d-9361c444b45a",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "October 21, 2021 18:46:16 PM UTC",
  "resourceList" : [ ],
  "description" : "Server Patching",
  "updatedTime" : "October 21, 2021 18:46:16 PM UTC",
  "percentageProgress" : "0%",
  "cause" : null,
  "action" : null
}
[root@dbsdpl251 ~]#

Check status

[root@dbsdpl251 ~]# dbcli describe-job -i 4b5e32fe-bd08-4684-b49d-9361c444b45a

Job details
----------------------------------------------------------------
                     ID:  4b5e32fe-bd08-4684-b49d-9361c444b45a
            Description:  Server Patching
                 Status:  Running
                Created:  October 21, 2021 6:46:16 PM UTC
               Progress:  0%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for Server Patching                                       October 21, 2021 6:46:16 PM UTC     October 21, 2021 6:46:16 PM UTC     Running

[root@dbsdpl251 ~]#

Update status

[root@dbsdpl251 ~]# dbcli describe-job -i 4b5e32fe-bd08-4684-b49d-9361c444b45a

Job details
----------------------------------------------------------------
                     ID:  4b5e32fe-bd08-4684-b49d-9361c444b45a
            Description:  Server Patching
                 Status:  Running
                Created:  October 21, 2021 6:46:16 PM UTC
               Progress:  28%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for Server Patching                                       October 21, 2021 6:46:16 PM UTC     October 21, 2021 6:48:07 PM UTC     Success
Server Patching                                                          October 21, 2021 6:48:07 PM UTC     October 21, 2021 6:48:07 PM UTC     Running

[root@dbsdpl251 ~]#

2.2 Server patching failures.

While performing the server patch , patched failed due to conflicts . 

In this senario best option is to raise a SR with oracle and get the work plan. There can be change oracle will provide merge patch to address some of the bugs.

root@dbsdpl251 ~]# dbcli describe-job -i 4b5e32fe-bd08-4684-b49d-9361c444b45a

Job details
----------------------------------------------------------------
                     ID:  4b5e32fe-bd08-4684-b49d-9361c444b45a
            Description:  Server Patching
                 Status:  Failure
                Created:  October 21, 2021 6:46:16 PM UTC
               Progress:  42%
                Message:  DCS-10001:Internal error encountered: Failure : failed to apply 32904851 on /u01/app/19.0.0.0/grid.
                  Cause:  This is an internal error.
                 Action:  Contact Oracle Support Services for assistance.

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Server Patching                                                          October 21, 2021 6:46:16 PM UTC     October 21, 2021 7:11:58 PM UTC     Failure
Pre-operations for Server Patching                                       October 21, 2021 6:46:16 PM UTC     October 21, 2021 6:48:07 PM UTC     Success
Server Patching                                                          October 21, 2021 6:48:07 PM UTC     October 21, 2021 7:11:58 PM UTC     Failure
task:TaskSequential_4802                                                 October 21, 2021 6:48:08 PM UTC     October 21, 2021 7:11:58 PM UTC     Failure
task:TaskSequential_4861                                                 October 21, 2021 6:56:25 PM UTC     October 21, 2021 7:11:58 PM UTC     Failure
task:TaskSequential_4862                                                 October 21, 2021 6:56:25 PM UTC     October 21, 2021 7:11:58 PM UTC     Failure
GI: Applying clusterware patch[dbsdpl251]                                October 21, 2021 6:56:25 PM UTC     October 21, 2021 7:11:58 PM UTC     Failure

[root@dbsdpl251 ~]#


---------------------------Patching Failed---------------------------------
Command execution failed during patching in home: /u01/app/19.0.0.0/grid, host: dbsdpl251.
Command failed:  /u01/app/19.0.0.0/grid/OPatch/opatchauto  apply /u01/odaPatchingTempDir/32895426 -oh /u01/app/19.0.0.0/grid -target_type cluster -binary -invPtrLoc /u01/app/19.0.0.0/grid/oraInst.loc -jre /u01/app/19.0.0.0/grid/OPatch/jre -persistresult /u01/app/19.0.0.0/grid/opatchautocfg/db/sessioninfo/sessionresult_analyze_dbsdpl251_crs_1.ser -analyze -online -prepare_home
Command failure output:
==Following patches FAILED in analysis for apply:

Patch: /u01/odaPatchingTempDir/32895426/32916816
Log: /u01/app/19.0.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-10-21_19-10-44PM_1.log
Reason: Failed during Analysis: CheckConflictAgainstOracleHome Failed, [ Prerequisite Status: FAILED, Prerequisite output:
Summary of Conflict Analysis:

Patches that can be applied now without any conflicts are :
32904851, 32915586, 32918050

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
32847378, 32916816

Following patches are not required, as they are subset of the patches in Oracle Home or subset of the patches in the given list :
3258 ...

After fixing the cause of failure Run opatchauto resume

]]. Failures:
OPATCHAUTO-68067: Check the details to determine the cause of the failure.
        at com.oracle.glcm.patch.auto.action.PatchActionExecutor.execute(PatchActionExecutor.java:172)
        at com.oracle.glcm.patch.auto.wizard.silent.tasks.PatchActionTask.execute(PatchActionTask.java:102)
        ... 2 more
2021-10-21 19:11:21,951 INFO  [1] com.oracle.glcm.patch.auto.db.integration.model.productsupport.DBBaseProductSupport - Space available after session: 173521 MB
2021-10-21 19:11:21,996 SEVERE [1] com.oracle.glcm.patch.auto.OPatchAuto - OPatchAuto failed.

Troubleshooting

DCS logs are located under /opt/oracle mount point

======================
Patching Pre check log 
======================
/opt/oracle/dcs/log/

Under the hood dbcli relies on opatchauto so you can also check $ORACLE_HOME/cfgtoollogs/opatchauto directory for logs for verification.

 

Below mention url is really useful link to get understanding and troubleshooting this oci patching.

Patching Failures on Bare Metal and Virtual Machine DB Systems

Solution
Better option is to create a SR with oracle get for merge patch or the steps to avoid this failure. (rollback conflict patches)


--- Action plan
1. Please rollback conflict patch 32847378

2. and proceed with 19.12 grid patch( note: This patch 32847378 is already included in 19.12)

Rdbsm Home:

1.Please rollback conflict patch 29780459

2. proceed with 19.12 patch


3.Then apply below one off patch on top of 19.12 db home

INCREASE _LM_RES_HASH_BUCKET AND BACK OUT CHANGES FROM THE BUG 29416368 FIX (Patch)
p29780459_1912000DBRU_Linux-x86-64.zip

--- Steps
1. Run the pre root script.

I1.f this is a GI Home, as the root user execute:

# $GI_HOME/crs/install/rootcrs.sh -prepatch >>>>>>>>>>>>>>>>>>This step will shut down CRS and unlock the binaries


2.As the GI home owner execute:

$ $GI_HOME/OPatch/opatch apply or rollback

3. Run the post script.

If this is a GI Home, as the root user execute:

# $GI_HOME/crs/install/rootcrs.sh -postpatch >>>>>>>>>>>>this step will start the crs

############# rollback patch 

[grid@dbsdpl251 ~]$   $ORACLE_HOME/OPatch/opatch lsinventory | grep 32847378
Patch  32847378     : applied on Mon May 17 15:34:08 UTC 2021
Patch description:  "OCW Interim patch for 32847378"
     32847378, 29160462, 29248723, 28642469, 29003617, 29186091, 29169739
[grid@dbsdpl251 ~]$

$ORACLE_HOME/OPatch/opatch rollback -id 32847378

Once the rollback complete rerun the server patch again to start the patching.

[root@dbsdpl251 ~]# dbcli describe-job -i 98de8e8e-72f5-42dc-be10-dc4f4e267a39

Job details
----------------------------------------------------------------
                     ID:  98de8e8e-72f5-42dc-be10-dc4f4e267a39
            Description:  Server Patching
                 Status:  Running
                Created:  November 3, 2021 7:27:52 PM UTC
               Progress:  71%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for Server Patching                                       November 3, 2021 7:27:53 PM UTC     November 3, 2021 7:30:10 PM UTC     Success
Server Patching                                                          November 3, 2021 7:30:10 PM UTC     November 3, 2021 7:30:10 PM UTC     Running

--- Complete server patching.

[root@dbsdpl251 ~]# dbcli describe-job -i 98de8e8e-72f5-42dc-be10-dc4f4e267a39

Job details
----------------------------------------------------------------
                     ID:  98de8e8e-72f5-42dc-be10-dc4f4e267a39
            Description:  Server Patching
                 Status:  Success
                Created:  November 3, 2021 7:27:52 PM UTC
               Progress:  100%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for Server Patching                                       November 3, 2021 7:27:53 PM UTC     November 3, 2021 7:30:10 PM UTC     Success
Server Patching                                                          November 3, 2021 7:30:10 PM UTC     November 3, 2021 8:47:14 PM UTC     Success 

2.4 DB Patching

Once the server patching completes , we can move the db patching. run the precheck command to verify the conflicts.

  [root@dbsdpl251 ~]# dbcli update-dbhome -i dea9250b-3dc7-45d5-b60c-f143aef7c550

  {
  "jobId" : "ae119988-9c62-4707-942f-6ce1b61e10e8",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "November 08, 2021 17:27:01 PM UTC",
  "resourceList" : [ ],
  "description" : "DBHome Patching: Home ID is dea9250b-3dc7-45d5-b60c-f143aef7c550",
  "updatedTime" : "November 08, 2021 17:27:02 PM UTC",
  "percentageProgress" : "0%",
  "cause" : null,
  "action" : null
 }

db patching job completion details.

  
  [root@dbsdpl251 ~]# dbcli describe-job -i ae119988-9c62-4707-942f-6ce1b61e10e8

Job details
----------------------------------------------------------------
                     ID:  ae119988-9c62-4707-942f-6ce1b61e10e8
            Description:  DBHome Patching: Home ID is dea9250b-3dc7-45d5-b60c-f143aef7c550
                 Status:  Success
                Created:  November 8, 2021 5:27:01 PM UTC
               Progress:  100%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
Pre-operations for DBHome patching                                       November 8, 2021 5:27:02 PM UTC     November 8, 2021 5:28:59 PM UTC     Success
DBHome patching                                                          November 8, 2021 5:28:59 PM UTC     November 8, 2021 6:48:27 PM UTC     Success
Post-operations for DBHome patching                                      November 8, 2021 6:48:27 PM UTC     November 8, 2021 6:48:35 PM UTC     Success

[root@dbsdpl251 ~]#




Verification

Once the patching complete on grid use dbcli describe-component to verify the component.

[root@dbsdpl251 ~]# dbcli describe-component
System Version
---------------
21.2.3.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
GI                                        19.12.0.0.0           up-to-date
DB                                        19.11.0.0.0           19.12.0.0

[root@dbsdpl251 ~]#

[root@dbsdpl252 ~]# dbcli describe-component
System Version
---------------
21.2.3.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
GI                                        19.12.0.0.0           up-to-date
DB                                        19.11.0.0.0           19.12.0.0

[root@dbsdpl252 ~]#

Verify the db component patch using dbcli describe-component. It should display as up-to-date.

[root@dbsdpl251 ~]# dbcli describe-component
System Version
---------------
21.3.2.2.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
GI                                        19.13.0.0.0           up-to-date
DB                                        19.13.0.0.0           up-to-date

As a practice I used to check from opatch lsinventory.

export GI=/u01/app/19.0.0.0/grid
export OH=/u01/app/oracle/product/19.0.0.0/dbhome_1
/u01/app/19.0.0.0/grid/OPatch/opatch lsinventory -oh $GI | grep ^Patch << to collect data at end.
/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch lsinventory -oh $OH | grep ^Patch << to collect data at end.

===================
node01
===================

[grid@dbsdpl251 ~]$ /u01/app/19.0.0.0/grid/OPatch/opatch lsinventory -oh $GI | grep ^Patch
Patch  33197296     : applied on Mon Nov 08 16:43:36 UTC 2021
Patch description:  "JDK BUNDLE PATCH 19.0.0.0.211019"
Patch  33239955     : applied on Mon Nov 08 16:37:08 UTC 2021
Patch description:  "TOMCAT RELEASE UPDATE 19.0.0.0.0 (33239955)"
Patch  33208123     : applied on Mon Nov 08 16:35:21 UTC 2021
Patch description:  "OCW RELEASE UPDATE 19.13.0.0.0 (33208123)"
Patch  33208107     : applied on Mon Nov 08 16:33:19 UTC 2021
Patch description:  "ACFS RELEASE UPDATE 19.13.0.0.0 (33208107)"
Patch  33192793     : applied on Mon Nov 08 16:27:13 UTC 2021
Patch description:  "Database Release Update : 19.13.0.0.211019 (33192793)"
Patch  31732095     : applied on Mon May 17 15:26:17 UTC 2021
Patch description:  "UPDATE PERL IN 19C DATABASE ORACLE HOME TO V5.32"
Patch  32585572     : applied on Mon May 17 15:22:03 UTC 2021
Patch description:  "DBWLM RELEASE UPDATE 19.0.0.0.0 (32585572)"
[grid@dbsdpl251 ~]$




[oracle@dbsdpl251 ~]$ export OH=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@dbsdpl251 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch lsinventory -oh $OH | grep ^Patch
Patch  33028462     : applied on Mon Nov 08 18:12:27 UTC 2021
Patch description:  "FAILOVER FAILED FOR MAX AVAILABILITY + FAST SYNC MODE FOR ALL DB VERSIONS"
Patch  29780459     : applied on Mon Nov 08 18:10:30 UTC 2021
Patch description:  "INCREASE _LM_RES_HASH_BUCKET AND BACK OUT CHANGES FROM THE BUG 29416368 FIX"
Patch  33197296     : applied on Mon Nov 08 18:08:57 UTC 2021
Patch description:  "JDK BUNDLE PATCH 19.0.0.0.211019"
Patch  33208123     : applied on Mon Nov 08 18:04:47 UTC 2021
Patch description:  "OCW RELEASE UPDATE 19.13.0.0.0 (33208123)"
Patch  33192793     : applied on Mon Nov 08 17:58:14 UTC 2021
Patch description:  "Database Release Update : 19.13.0.0.211019 (33192793)"
Patch  32327201     : applied on Tue May 04 03:29:07 UTC 2021
Patch description:  "RDBMS - DSTV36 UPDATE - TZDATA2020E"
Patch  31335037     : applied on Tue May 04 03:27:51 UTC 2021
Patch description:  "RDBMS - DSTV35 UPDATE - TZDATA2020A"
Patch  30432118     : applied on Tue May 04 03:26:37 UTC 2021
Patch description:  "MERGE REQUEST ON TOP OF 19.0.0.0.0 FOR BUGS 28852325 29997937"
Patch  31732095     : applied on Tue May 04 03:25:16 UTC 2021
Patch description:  "UPDATE PERL IN 19C DATABASE ORACLE HOME TO V5.32"
Patch  32399816     : applied on Tue May 04 03:20:20 UTC 2021
Patch description:  "OJVM RELEASE UPDATE: 19.11.0.0.210420 (32399816)"
[oracle@dbsdpl251 ~]$

Oracle cloud Infrastructure (OCI) - Part 2 : OCI -VM Standby database creation

 

Intro

acronym - 

DG -  dataguard

OCI - Oracle Cloud Infrastructure  

Most important part of the IT Infrastructure is to have a proper disaster recovery plan . Organization are spending colossal sum of money to have proper DR infrastructure. Best practice is to create your DR site on different city or different geographical location.  OCI provides greater flexibility to create DR within the same region or different region. Even small/medium scale companies can afford this DR sites. These OCI cloud features enable greater enhancement to infrastructure.

Furthermore , OCI provides Oracle Data Guard features address DR (disaster recovery) situations. DG to enables & ensures high availability, data protection, and disaster recovery for your enterprise database.

Oracle Data Guard provides an extensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters (such as natural disasters) and data corruption.

 

Active Data Guard vs Normal Data Guard

Oracle Data Guard maintains these standby databases as a cloned version of the production database and regularly applies changes from Primary Database to Standby Database. Then, If the production database becomes unavailable because of a planned or an unplanned outage, Oracle Data Guard can switch any standby database to the primary role, minimizing the downtime associated with the outage.

Active Data Guard:  Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, Web-based access, and so on, while continuously applying changes received from the primary database. In active data guard standby database work as  be read only with apply state.

Note: For Active Data Guard, when you create the database, you need to select the Extreme Performance.

There are 4 different types of editions in Oracle Cloud

1. Enterprise Edition
2. Standard Edition
3. High-Performance
4. Extreme Performance.

Normal  Data Guard:  Normal Data Guard enables you to switch to the Standby Database when your primary database is down due to some outage or natural disasters. In normal Data guard, you can still open the standby database in read-mode but changes not applied while in read-only mode. 

Types of DG : Physical | Logical | Snapshot

• Physical: Redo logs are Shipped & Applied on Standby Database
• Logical: Mainly used for Rolling Upgrades & Redo logs are shipped & transformed to SQL & then they are applied.
• Snapshot: A snapshot Standby Database receives and archives, but does not apply redo logs data from a primary database.

Creating DG in cloud environment now simplify to less than 4 clicks.  This article I will cover the how to create standby database within the same region.

First Navigate to the database and view the database this shows the enable data guard.

As per this figure we need to feed to Display Name , Region and Availability domain ..etc

Next select the required shape for the database server and provide hostname for the standby server.

Important to select the correct network vcn. We are creating this in public segment to access this via internet.

Next select the database mode and transport mode. For this testing I'm selecting Maximum performance and transport mode as Async.

Error

This errors comes when the port 1512 is not enable for destination.

Data Guard Association cannot be created when standard database service port (1521) is 
blocked for instances in 
Subnet: ocid1.subnet.oc1.ca-toronto-1.aaaaaaaavkfa3xtnoyn4ce5l4g2ttgzf54lhkofaavnaoxiv3irog7ghkika 
by security rules associated with 
Subnet: ocid1.subnet.oc1.ca-toronto-1.aaaaaaaaceab2pepfsk54ektbyuvfl2rulk4d4luzbfu2iduyr7cmd2gwibq

Solution

To overcome this we need to create two rules for one for Ingress and one for Egress. Port 1512 should be available to communicate with standby server in region level.

 

Follow the same steps above to create the standby database.

Once the provisioning is complete check the status from work request.

Verification

We can validate data guard status using dgmgrl utility . Also it's better to check the database role by login to database.

Database Role :

INSTANCE_NAME    STATUS       HOST_NAME                                                        STARTUP_TIME
---------------- ------------ ---------------------------------------------------------------- ----------------
PWSH011          MOUNTED      dbsdpl251                                                        26/10/2021 20:55
PWSH012          MOUNTED      dbsdpl252                                                        26/10/2021 20:55

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY
MOUNTED              PHYSICAL STANDBY

SQL>

DGMRRL Utility :

#### DG status

DGMGRL> show configuration;

Configuration - PWSH01_yyz16x_PWSH01_yyz1k6

  Protection Mode: MaxPerformance
  Members:
  PWSH01_yyz16x - Primary database
    PWSH01_yyz1k6 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 22 seconds ago)

DGMGRL> show database verbose 'PWSH01_yyz1k6';

Database - PWSH01_yyz1k6

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 4.00 KByte/s
  Active Apply Rate:  198.00 KByte/s
  Maximum Apply Rate: 198.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    PWSH011
    PWSH012 (apply instance)

Oracle Cloud Infrastructure (OCI) - Part 1 : RAC using OCI VM Servers

 

Intro

Most of the companies do not want to maintain data centers because it’s too costly for the organization. Cloud platforms like Oracle , Azure ,AWS and Google Cloud enables companies to host their server farms on cloud. This is major game changer of the current era, Cloud hosting enables many features to optimize the cost and new features like scaling the resource as per the organization needs.

I strongly believe use of pay as you go concept enable companies to save money and help them to focus on their core business. Also many features are introduced on cloud environments optimize security of the environment.

This is simple explanation of what is oracle cloud and the service offering of the database.

What is oracle cloud ?

Oracle Cloud is a cloud computing service offered by Oracle Corporation providing servers, storage, network, applications and services through a global network of Oracle Corporation managed data centers.

This article we are going to cover database RAC database creation on OCI.

1. Setup network

Initial step is to create a network , because we need public network to access server.

Login to OCI console and navigate to Networking → Virtual Cloud Network.

As we are creating this database for testing purpose you can use VCN Wizard and create two network

1. Public (Access database via internet)
2. Private (Internal network)

This figure illustrates the VCN network creation.

2. Create VM (RAC environment)

Oracle Database → Bare Metal , VM and Exadata.

Once you login to database page click the “Create DB system”

To create a database we need to feed information like compartment name , region , shape and configure db system . As we are going to create a RAC instance we need to give node count as two(2).

As we are setting up RAC give node count as 2. Then installation will be complete on two node RAC.

Now select the vm shape that required for your database load.

2.1 Setup SSH keys

Use below link to generate ssh-keys. ssh keys are required to login database servers.

Generating an SSH Key Pair for Oracle Compute Cloud Service Instances

For this database installation we are going to use “Oracle Grid Infrastructure “ , so data files will be resides in ASM storage.

Once you feed all the required information click the create button. This process will take close to 45min - 1 hour . As we are creating this using “Oracle Grid Infrastructure “ installation will take some time .

1. Install grid
2. Create ASM storage.
3. Install database software and create database.

Once  the database creation is complete check the status of the server.

3. Login to environment

you can use Cmder | Console Emulator console to to login OCI , using this we can execute linux commands.

To login OCI navigate to where your private key is located and 

run  : ssh -i ./myOracleCloudKey.ppk opc@<ip_address>

D:\Cloud-Lab\chana885391
λ ssh -i ./myOracleCloudKey.ppk opc@140.238.152.37
Enter passphrase for key './myOracleCloudKey.ppk':
Last login: Fri Oct 15 20:47:43 2021 from 173.34.200.48
[opc@dbsdpl21 ~]$ sudo su - oracle
Last login: Fri Oct 15 21:42:12 UTC 2021
[oracle@dbsdpl21 ~]$ ps -ef | grep pmon
oracle    4261  3978  0 19:37 pts/0    00:00:00 grep --color=auto pmon
[oracle@dbsdpl21 ~]$

[oracle@dbsdpl21 ~]$ ps -ef | grep pmon
grid      7184     1  0 19:38 ?        00:00:00 asm_pmon_+ASM1
grid      8080     1  0 19:39 ?        00:00:00 apx_pmon_+APX1
oracle    8535     1  0 19:39 ?        00:00:00 ora_pmon_PWSH011
oracle    9568  3978  0 19:39 pts/0    00:00:00 grep --color=auto pmon

SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      PWSH01_yyz16x

SQL> show pdbs

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

INSTANCE_NAME    STATUS       HOST_NAME                                                        STARTUP_TIME
---------------- ------------ ---------------------------------------------------------------- ----------------
PWSH011          OPEN         dbsdpl21                                                         19/10/2021 19:39
PWSH012          OPEN         dbsdpl22                                                         19/10/2021 19:39