OCI - Data Safe - Part 1

Data is the new currency. It is one of the most valuable organizational assets, however, if that data is not well protected, it can quickly become a liability. At an alarming rate we are seeing more and more data breaches that impact an organization’s brand, their ability to continue to delivery services and do business and of course they are impacted financially. We know that most sensitive data is stored and managed within databases. 

Databases now become a prime target for security threats. Oracle Data Safe is the solution with a packaged unified database security control center for protecting both cloud and on-premises databases. Data Safe provides an integrated set of security features including 5 key components:

What is Oracle Data Safe in OCI ?

Oracle Data Safe is a fully-integrated Cloud service focused on the security of your data. ... Features include Security Assessment, User Assessment, Data Discovery, Data Masking, and Activity Auditing.

This meta link will be useful to setup data safe and register target database with data safe. 

https://docs.oracle.com/en/cloud/paas/data-safe/udscs/set-oracle-data-safe.html

Features of Oracle Data Safe

1) Security Assessment
2) User Assessment

3) Data Discovery

4) Oracle Data Masking
5) Activity Auditing

Also data safe is free to collect 1 million each database audit records per month. When it goes beyond 1 million data safe charge back method is getting applied.
 

This article I will illustrate how we can configure the data safe in cloud.

Enable data safe and setup endpoint

First login to OCI and navigate to Oracle database section , under external database there is data safe.
Once you click on data safe , opens data safe configuration page.

Data safe page , This page has few options for database registration. 

OCI gen 1 supported only cloud registered database. Now data safe is extend to support Cloud and On-Premises databases.

Most important section is creating private endpoint.

1. Private endpoint : 
       This enables to connection to register database in data safe. Traffic flow within the OCI network layer.

Create Private endpoint

Before starting this make sure to create VCN , VNC automatically create two ip subnets.

1. Private
2. Public

As data safe network flow within the OCI we need to select private. Network security Group can be select after creating this.

For this NSG and Security list configuration play major part for the connection.

Create Ingress and Egress rule in security list

For this NSG and Security list play major part for the connection. We need to create ingress and egress rule to establish the connection.

• In the NSG or security list for your target database, add an ingress rule that allows your target database's private endpoint IP address on the target database's port to receive incoming traffic from Oracle Data Safe's private endpoint IP address from all ports.


• In the NSG or security list for your Oracle Data Safe private endpoint, add an egress rule that allows Oracle Data Safe's private endpoint IP address on all ports to send requests to the target database's private endpoint IP address on the target database's port. If the target database has multiple IP addresses, you need configure an egress rule for each IP address. In the case of an Oracle On-Premises Database, you only need to configure an egress rule, and not an ingress rule.

 

Navigate to VCN and add security ingress and egress rule.

For this testing I created these ingress and egress rules to allow traffic to data safe.

• Allow the target database to receive traffic on port 1521 from oracle Data Safe's private endpoint (10.0.1.0) from any port

• Allow Oracle Data Safe's Private endpoint ( from any port ) to send traffic to the target database (10.0.0.0 on port 1521)

Create DATASAFE$ADMIN user.

Create user in database to collect audit information.

#### Registration

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PWHSE_PDB                      READ WRITE NO
SQL> alter session set container=PWHSE_PDB;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PWHSE_PDB                      READ WRITE NO
SQL>

###### create user

CREATE USER DATASAFE$ADMIN IDENTIFIED BY ORacle123## DEFAULT TABLESPACE USERS QUOTA 10M ON USERS TEMPORARY TABLESPACE TEMP;

SQL> CREATE USER DATASAFE$ADMIN IDENTIFIED BY ORacle123## DEFAULT TABLESPACE USERS QUOTA 10M ON USERS TEMPORARY TABLESPACE TEMP;

User created.

This script is there in the data safe target registration section which has a procedure to collection information from database.

Execute the script as sys and feed prompt information for username , Type etc …

SQL> !pwd
/home/oracle/scripts/sql

SQL> !ls -lrth
total 32K
-rw-r--r-- 1 oracle oinstall 30K Oct 26 18:08 datasafe_privileges.sql

SQL> @datasafe_privileges.sql
Enter value for USERNAME (case sensitive matching the username from dba_users)
DATASAFE$ADMIN
Setting USERNAME to DATASAFE$ADMIN
Enter value for TYPE (grant/revoke)
grant
Setting TYPE to grant
Enter value for MODE (audit_collection/audit_setting/data_discovery/masking/assessment/all)
all

SQL> @datasafe_privileges.sql
Enter value for USERNAME (case sensitive matching the username from dba_users)
DATASAFE$ADMIN
Setting USERNAME to DATASAFE$ADMIN
Enter value for TYPE (grant/revoke)
grant
Setting TYPE to grant
Enter value for MODE (audit_collection/audit_setting/data_discovery/masking/assessment/all)
all
Setting MODE to all
Granting AUDIT_COLLECTION privileges to "DATASAFE$ADMIN" ...
Granting AUDIT_SETTING privileges to "DATASAFE$ADMIN" ...
Granting DATA_DISCOVERY role to "DATASAFE$ADMIN" ...
Granting MASKING role to "DATASAFE$ADMIN" ...
Granting ASSESSMENT role to "DATASAFE$ADMIN" ...
Done.
Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
[oracle@dbsdpl21 sql]$

Register target

Please follow below mention steps to perform data safe registration.

Select the private endpoint as we are using private end point to transfer traffic within the OCI.

Connection setting can be obtain from database section

Make sure to provide DATASAFE$ADMIN password to register database.

SQL> !pwd
/home/oracle/scripts/sql

SQL> !ls -lrth
total 32K
-rw-r--r-- 1 oracle oinstall 30K Oct 26 18:08 datasafe_privileges.sql

SQL> @datasafe_privileges.sql
Enter value for USERNAME (case sensitive matching the username from dba_users)
DATASAFE$ADMIN
Setting USERNAME to DATASAFE$ADMIN
Enter value for TYPE (grant/revoke)
grant
Setting TYPE to grant
Enter value for MODE (audit_collection/audit_setting/data_discovery/masking/assessment/all)
all

SQL> @datasafe_privileges.sql
Enter value for USERNAME (case sensitive matching the username from dba_users)
DATASAFE$ADMIN
Setting USERNAME to DATASAFE$ADMIN
Enter value for TYPE (grant/revoke)
grant
Setting TYPE to grant
Enter value for MODE (audit_collection/audit_setting/data_discovery/masking/assessment/all)
all
Setting MODE to all
Granting AUDIT_COLLECTION privileges to "DATASAFE$ADMIN" ...
Granting AUDIT_SETTING privileges to "DATASAFE$ADMIN" ...
Granting DATA_DISCOVERY role to "DATASAFE$ADMIN" ...
Granting MASKING role to "DATASAFE$ADMIN" ...
Granting ASSESSMENT role to "DATASAFE$ADMIN" ...
Done.
Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
[oracle@dbsdpl21 sql]$

Troubleshooting

This happens when you copy entire connection details 
connection Settings

Solution

Solution is to add only service name

Final output after registration target should display in green color.

Login to Data Safe console

Click on security center to login to data safe console.

This covers 5 sections

1. Security Assessment
2. User Assessment
3. Data Discovery
4. Data Masking
5. Activity Auditing

Console dashboard 

Oracle Audit Vault - 20.4 - Part 4 - Enable Auditing

 

As security is a major concern is current data era , Many organization invest colossal sum of money to be on top the compliance scores. AVDF is a greater tool to provide out-of-the-box compliance reports. These reports can be schedulable, customized reporting for regulations such as GDPR, PCI, GLBA, HIPAA, IRS 1075, SOX, and UK DPA. This reports aggregate network events and audit data from the monitored systems.

Now we will focus on setting up the policies using AVDF , AVDF is very powerful tool , we can use this tool to get proactive security measurements for databases.

To configure audit we need to login as superaudit users which we have configured earlier.

Now navigate the policies tab and enable the User Activity/Auditing and add respective users that needs to be monitored.

For this example I have added CHANAKA and HR users.

After enabling the capturing of these schema data will be visible in dashboard.

This UI give us flexibility to click the bar graphs to see what data is getting capture for analysis.

19c PDB remote clone and relocation using dbca

Database world consolidation and managing resources plays a key role. To ease the administration of databases  and efficiently use the hardware resources dbas use concept of database consolidation . To make this process easier oracle introduced cool feature of relocating pluggable database from one cdb to another. 

12.2 database this is happening via db link , A new feature in Oracle 19c is the ability to use DBCA to not only perform a remote clone of a PDB, but also to relocate a PDB from one Container Database to another.

This below mention graph elaborates the relocation process of one cdb to another.

Can read more about pdb relocationg using below mention link

http://ora-srv.wlv.ac.uk/oracle19c_doc/multi/relocating-a-pdb.html#GUID-75519361-3DA2-4558-A7E5-64BC16FAFC7D

This article I will elaborate how we can relocate pdb from one cdb to another. In this testing we are using RWHSE01 as local cdb and we are relocating RMAN_CAT_PDB pdb from TWHSE01 cdb to RWHSE01 cdb within same server.

Just for more clarity please and find local , remote and remote pdb database details.

local         - RWHSE01 (local database use for relocation)
remote        - TWHSE01 (current pdb running cdb database)
remote pdb    - RMAN_CAT_PDB (relocating pdb)

After relocation database ,This is the intended final environment

TWHSE01 : TWHSE_PDB

RWHSE01 : RWHSE_PDB, RMAN_CAT_PDB

First verify the current settings before starting the activity.

-- RMAN_CAT_PDB currently resides in TWHSE01 cdb database
SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TWHSE01
SQL> show pdbs

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

Prerequisites

We need create common user with below mention privileges to access the pdb database in remote database. Unlike a regular relocate, we don't need to create a database link. We just need to supply the credentials we would use to create the link. The DBCA does the rest.

The following are the prerequisites for running the relocate PDB command:

• The database user in the local PDB must have the CREATE PLUGGABLE DATABASE privilege in the local CDB root container.
• The remote CDB must be in the local undo mode.
• The remote and local PDBs must be in the archivelog mode.
• The database user in the remote PDB that the database link connects to must have the CREATE PLUGGABLE DATABASE, SESSION, and SYSOPER privileges.
• The local and remote PDBs must have the same options installed, or the remote PDB must have a subset of the options installed on the local PDB.

CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;

Sample output

SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TWHSE01
SQL> show pdbs

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

SQL> CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;

Grant succeeded.

SQL> GRANT SYSOPER TO c##remote_clone_user CONTAINER=ALL;

Grant succeeded.

We need to check local and remote database undo settings and archive log mode.

Note : No need to put database on read only mode, because database with archive log mode
full fill that requirement.

### Check the remote CDB is in local undo mode and archivelog mode.
-- remote 
SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TWHSE01
SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';SQL> SQL>   2    3

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE



SQL> SELECT log_mode
FROM   v$database;  2

LOG_MODE
------------
ARCHIVELOG

-- local
SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      RWHSE01

SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';SQL> SQL> SQL>   2    3

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE


SELECT log_mode
FROM   v$database;


SQL> SELECT log_mode
FROM   v$database;  2

LOG_MODE
------------
ARCHIVELOG

Let’s verify the connectivity c##remote_clone_user connection check in remote database.

[oracle@crs01 ~]$ sqlplus c##remote_clone_user/remote_clone_user@192.168.56.200:1525/TWHSE01

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 13 16:56:50 2021
Version 19.3.0.0.0

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


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

SQL> show user
USER is "C##REMOTE_CLONE_USER"
SQL>

Sys connection verification

[oracle@crs01 ~]$  sqlplus sys/sys123@192.168.56.200:1525/TWHSE01 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 13 16:58:21 2021
Version 19.3.0.0.0

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


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

SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TWHSE01
SQL>

Relocation

Relocate a PDB with the DBCA In 19c the DBCA -relocate PDB command has been introduced, allowing us to relocate a PDB to a different container database (CDB).

Here is the link with all the syntaxes

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/creating-and-configuring-an-oracle-database.html#GUID-8DD80A8A-DDE1-471F-8CBB-013D85CFE28F

 

   -relocatePDB -                   Command to Relocate a pluggable database.
      -remotePDBName                Name of the pluggable database to clone/relocate
      -pdbName                      Pluggable database name
      -dbLinkUsername               Common user of a remote CDB, used by database link to connect to remote CDB.
      -remoteDBConnString           EZCONNECT string to connect to Source database for example "host:port/servicename"
      -sourceDB                     Database unique name for RAC database or SID for Single Instance database
      [-remoteDBSYSDBAUserName      User name with SYSDBA privileges of remote database
      [-dbLinkUserPassword          Common user password of a remote CDB, used by database link to connect to remote CDB.
      [-useWalletForDBCredentials   true | false Specify true to load database credentials from wallet
         -dbCredentialsWalletLocation  Path of the directory containing the wallet files 
         [-dbCredentialsWalletPassword Password to open wallet with auto login disabled
      [-remoteDBSYSDBAUserPassword Password for remoteDBSYSDBAUserName user of remote database.
      [-sysDBAUserName User name  with SYSDBA privileges
      [-sysDBAPassword Password for sysDBAUserName user name

As per this example we are using below mention dbca command to perform the relocation.

[oracle@crs01 admin]$ . oraenv
ORACLE_SID = [RWHSE01] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@crs01 admin]$

dbca -silent \
  -relocatePDB \
  -pdbName RMAN_CAT_PDB \
  -sourceDB RWHSE01 \
  -remotePDBName RMAN_CAT_PDB \
  -remoteDBConnString localhost:1525/TWHSE01 \
  -remoteDBSYSDBAUserName sys \
  -remoteDBSYSDBAUserPassword sys123 \
  -dbLinkUsername c##remote_clone_user \
  -dbLinkUserPassword remote_clone_user

Verification of the log output

#################### output

[oracle@crs01 admin]$ dbca -silent \
>   -relocatePDB \
>   -pdbName RMAN_CAT_PDB \
>   -sourceDB RWHSE01 \
>   -remotePDBName RMAN_CAT_PDB \
>   -remoteDBConnString localhost:1525/TWHSE01 \
>   -remoteDBSYSDBAUserName sys \
>   -remoteDBSYSDBAUserPassword sys123 \
>   -dbLinkUsername c##remote_clone_user \
>   -dbLinkUserPassword remote_clone_user
Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "RMAN_CAT_PDB" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/RWHSE01/RMAN_CAT_PDB/RWHSE01.log" for further details.
[oracle@crs01 admin]$

Verification of the pdb relocation to RWHSE01

SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      RWHSE01
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 RWHSE_PDB                      MOUNTED
         4 RMAN_CAT_PDB                   READ WRITE NO
SQL>

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 ~]$