My new book as Co-Author, Oracle Global Data services

 

Happy to announce that my second book as Co-Author is releasing soon.

Books can be pre-ordered at below.
Amazon
BarnesandNoble

Active Data Guard DML Redirection Feature in 19c

 

In this post we will see how we can we DML Redirection feature in Oracle 19c Active Data Guard Standby.

Primary Source Environment setup

RAC Database : RENODBPR ( renodbpr1 & renodbpr2)
PDB                        : ONEPDB
GRID Home : /u01/app/19.3.0.0/grid
RDBMS Home    : /u01/app/oracle/product/19.3.0.0/db_1
Version         : Oracle Database 19c EE - Production Version 19.17.0.0.0
hosts : labhost01
                          labhost02

Standby database environment setup

RAC Database : RENODBDR ( renodbdr1 & renodbdr2)
GRID Home : /u01/app/19.3.0.0/grid
RDBMS Home    : /u01/app/oracle/product/19.3.0.0/dbhome_1
Version         : Oracle Database 19c EE - Production Version 19.17.0.0.0
hosts : labdrhost01
                          labdrhost02

What is DML redirection feature ?
DML Redirection is new feature in Oracle 19c which allows DML operations to be executed on an Active Data Guard standby database.

How it works :  When a user performs a DML operation in the Active Standby database, the DML operation is actually passed to the Primary database where it is executed and the redo generated from that particular DML operation will be shipped and applied in the Standby database and after that the control is returned to the user who ran the command.

Lets see how it works.

We have this schema, appuser in the pluggable database, onepdb.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ONEPDB                         READ WRITE YES
SQL>

we have a table with name, APP_TAB1 in the pdb, one_pdb.

On Primary database:

On Standby database

without  enabling DML redirection feature, when we run insert operation in the standby database, it will error out as expected.

Enable DML redirection at session level  by using  below command 

 alter session enable adg_redirect_dml; 
and retry the insert operation.

Even though it works fine, this might not helpful for all the application as internally all the DMLs are being redirected to Primary database where its getting executed and the Standby database is getting updated with the redo data that it receives from the primary database for that DML transaction.

May be it will be useful for applications who perform mostly the read operations and very minimum write operations.

Thanks

Sambaiah Sammeta

Oracle OUD error : ORA-28030: Server encountered problems accessing LDAP directory service

  

Recently we started observing this issue where when we try to connect OUD integrated Oracle database and got the below exception..

"dbhost01:/u01/app/oracle->sqlplus globaluser1@TESTDB
SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 3 18:31:16 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-28030: Server encountered problems accessing LDAP directory service"

First we need to identify the exact cause that's forcing the connection to give this particular error. 

Enable the tracing as shown below in the oracle database and then try the connection.

1) Enable 28033 event tracing using below SQL.

SQL> alter system set events '28033 trace name context forever, level 9';

2) Run the sqlplus connection again.

sqlplus globaluser1@TESTDB

3) Disable the tracing using below SQL.

SQL> alter system set events '28033 trace name context off';

4) Check the dump directory for the trace it generated 

In my case, it generated below trace.

/oracle/app/diag/rdbms/testdb1/TESTDB/trace/TESTDB1_ora_142721.trc

Below is the output of this trace file.

As you can see from the 2nd line that its not able to get the correct credentials from the wallet.

kzld_discover received ldaptype: OID
KZLD_ERR: failed to get cred from wallet     <--------------------------------------------
KZLD_ERR: Failed to bind to LDAP server. Err=28032
KZLD_ERR: 28032
KZLD is doing LDAP unbind
KZLD_ERR: found err from kzldini.

This error means that either wallet location is not correct in the sqlner.ora or the wallet doesnt have correct password.

In My case, my sqlnet.ora had wrong wallet location mentioned and once I corrected , I was able to connect the database.

Hope this helps incase if you run into similar issue.

Thanks

Sambaiah Sammeta

Dataguard switchover error - ORA-16597: Oracle Data Guard broker detects two or more primary databases

I ran into below error when performed a switchover operation in my dataguard configuration which has one primary and two standby databases.

"ORA-16597: Oracle Data Guard broker detects two or more primary databases"

Please see below

 [oracle@dbhost01 ]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Apr 19 15:13:05 2023
Version 19.19.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "tureepr"
Connected as SYSDG.
DGMGRL> connect sys/ringrose;
Connected to "tureepr"
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - tureepr_cfg

  Protection Mode: MaxPerformance
  Members:
  tureepr - Primary database
    tureedr - Physical standby database
    tureetr - Physical standby database
Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS   (status updated 35 seconds ago)

Performing the swithcover operation.

DGMGRL> switchover to tureetr;
Performing switchover NOW, please wait...
Operation requires a connection to database "tureetr"
Connecting ...
Connected to "tureetr"
Connected as SYSDBA.
New primary database "tureetr" is opening...
Oracle Clusterware is restarting database "tureepr" ...
Connected to "tureepr"
Switchover succeeded, new primary is "tureetr"

DGMGRL>

DGMGRL> show configuration;
Configuration - tureepr_cfg
  Protection Mode: MaxPerformance
  Members:
  tureetr - Primary database
    tureepr - Physical standby database
    tureedr - Physical standby database (disabled)
      ORA-16597: Oracle Data Guard broker detects two or more primary databases
Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS   (status updated 60 seconds ago)
DGMGRL> 

Even though the switchover was completed successfully, for some reason, I see one of the second standby database in disabled state and I also see below error.
   ORA-16597: Oracle Data Guard broker detects two or more primary databases

First, I went ahead and enabled the second standby database which got disabled during the switchover operation

DGMGRL> enable database tureedr
Enabled.
DGMGRL> 

Surprisingly  When I checked the configuration, I saw that the configuration came back to normal, wondering how and why ...

DGMGRL> show configuration;

Configuration - tureepr_cfg
  Protection Mode: MaxPerformance
  Members:
  tureetr - Primary database
    tureepr - Physical standby database
    tureedr - Physical standby database

Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS   (status updated 52 seconds ago)
DGMGRL>

anyone has any idea why the below error popped up?

   ORA-16597: Oracle Data Guard broker detects two or more primary databases

Thanks

Sambaiah Sammeta

Applying April, 2023 Release update on a 19c 2-node RAC environment

 

In this post, we will see how we can apply the 19.19 RU to both the Database and Grid homes in our 19c RAC Lab environment.

19.19 RU details

35037840 - GI Release Update 19.19.0.0.230418

35042068 - Database Release Update 19.19.0.0.230418 

My Lab environment is a 2 node RAC cluster with patch set 19.17 applied. Below is the current RU version applied to the existing GI and DB homes

Source Environment setup

RAC Database : RENODBPR ( renodbpr1 & renodbpr2)

GRID Home : /u01/app/19.3.0.0/grid

RDBMS Home    : /u01/app/oracle/product/19.3.0.0/dbhome_1

Version         : Oracle Database 19c EE - Production Version 19.17.0.0.0

hosts : labhost01

                          labhost02

Grid Home Current patch level

[oracle@labhost01 software]$ echo $ORACLE_HOME

/u01/app/19.3.0.0/grid

[oracle@labhost01 software]$ $ORACLE_HOME/OPatch/opatch lspatches

34580338;TOMCAT RELEASE UPDATE 19.0.0.0.0 (34580338)

34444834;OCW RELEASE UPDATE 19.17.0.0.0 (34444834)

34428761;ACFS RELEASE UPDATE 19.17.0.0.0 (34428761)

34419443;Database Release Update : 19.17.0.0.221018 (34419443)

33575402;DBWLM RELEASE UPDATE 19.0.0.0.0 (33575402)

OPatch succeeded.

[oracle@labhost01 software]$

Database home current patch level

[oracle@labhost01 software]$ echo $ORACLE_HOME

/u01/app/oracle/product/19.3.0.0/db_1

[oracle@labhost01 software]$ $ORACLE_HOME/OPatch/opatch lspatches

34444834;OCW RELEASE UPDATE 19.17.0.0.0 (34444834)

34419443;Database Release Update : 19.17.0.0.221018 (34419443)

OPatch succeeded.

[oracle@labhost01 software]$

Download the Grid Patch , Database patch and the latest OPatch from Oracle and stage it on all the nodes of the cluster(if its RAC)

35037840 - GI Release Update 19.19.0.0.230418

35042068 - Database Release Update 19.19.0.0.230418 

p6880880_190000_Linux-x86-64.zip   - Latest Opatch for 19c, we need 12.2.0.1.36 to apply 19.19 RU.

The Oracle Grid Infrastructure and  Database patches are cumulative and include the database CPU program security content.

Patch Installation 

It is highly recommended to take a backup of the Oracle home binaries, the Grid home binaries, and Central Inventory prior to applying patches

Patch Installation prerequisites

1. Opatch utility version information

You must use the OPatch utility version 12.2.0.1.36 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 12.2 which is available for download from My Oracle Support note 6880880 by selecting the ARU link for the 12.2.0.1.0 OPatch release.

Check the current OPatch version on the server

[oracle@labhost01 software]$ $ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.2.0.1.35

OPatch succeeded.

[oracle@labhost01 software]$

Download and unzip the latest OPatch utility to both the Grid and Database homes.

Grid home : 

Node 1

Perform the below steps as root user

[root@labhost01 ~]#  mv /u01/app/19.3.0.0/grid/OPatch /u01/app/19.3.0.0/grid/OPatch.orig1

[root@labhost01 ~]# unzip p6880880_190000_Linux-x86-64.zip -d /u01/app/19.3.0.0/grid

[root@labhost01 ~]# chown -Rf oracle:dba /u01/app/19.3.0.0/grid/OPatch

let's check the OPatch version to see if its updated.

[oracle@labhost01 software]$ $ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.2.0.1.36

OPatch succeeded.

[oracle@labhost01 software]$

Node2

[root@labhost02 ~]#  mv /u01/app/19.3.0.0/grid/OPatch /u01/app/19.3.0.0/grid/OPatch.orig

[root@labhost02 ~]# unzip p6880880_190000_Linux-x86-64.zip -d /u01/app/19.3.0.0/grid

[root@labhost02 ~]# chown -Rf oracle:dba /u01/app/19.3.0.0/grid/OPatch

let's check the OPatch version to see if its updated.

[oracle@labhost02 software]$ $ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.2.0.1.36

OPatch succeeded.

[oracle@labhost02 software]$

Database home 

Node 1:

[oracle@labhost01 software]$mv /u01/app/oracle/product/19.3.0.0/db_1/OPatch /u01/app/oracle/product/19.3.0.0/db_1/OPatch.orig

[oracle@labhost01 software]$ unzip p6880880_190000_Linux-x86-64.zip -d /u01/app/oracle/product/19.3.0.0/db_1/

[oracle@labhost01 software]$ . oraenv

ORACLE_SID = [+ASM1] ? renodbpr1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@labhost01 software]$ echo $ORACLE_HOME

/u01/app/oracle/product/19.3.0.0/db_1

[oracle@labhost01 software]$  $ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.2.0.1.36

OPatch succeeded.

[oracle@labhost01 software]$

Node 2:

[oracle@labhost02 software]$mv /u01/app/oracle/product/19.3.0.0/db_1/OPatch /u01/app/oracle/product/19.3.0.0/db_1/OPatch.orig

[oracle@labhost02 software]$ unzip p6880880_190000_Linux-x86-64.zip -d /u01/app/oracle/product/19.3.0.0/db_1/

[oracle@labhost02 software]$ . oraenv

ORACLE_SID = [+ASM2] ? renodbpr2

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@labhost02 software]$ echo $ORACLE_HOME

/u01/app/oracle/product/19.3.0.0/db_1

[oracle@labhost02 software]$  $ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.2.0.1.36

OPatch succeeded.

[oracle@labhost02 software]$

2. Check the consistency for the Oracle inventory of both Grid and Database homes

Before we begin apply any patches, it is always strongly  recommended to check the consistency of the inventory information for both the Grid home and Oracle homes where we are applying the patch. 

$ <ORACLE_HOME>/OPatch/opatch lsinventory -detail -oh <ORACLE_HOME>

If this command succeeds, it lists the Oracle components that are installed in the home. Save the output so that you have the status prior to the patch application. However if this command fails, contact Oracle Support Services for assistance.

In our case, its checks out without any issues. Below is formatted output as the output from above command is very huge.

3. Run the Opatch conflict check 

Determine whether any currently installed one-off patches conflict with this patch 34416665 as follows:

Run OPatch Conflict Check

For Grid Infrastructure Home, as oracle user:

export ORACLE_HOME=/u01/app/19.3.0.0/grid

export PATH=$ORACLE_HOME/bin:$PATH

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/35037840/35042068

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/35037840/35050331

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/35037840/35050325

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/35037840/35107512

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/35037840/33575402

Below is the formatted output of above commands from the node1.

For Oracle database home, as home user:

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/35037840/35042068

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/software/35037840/35050331

Below is the formatted output of above commands from the node1.

Note : Repeat the steps for all the nodes of the cluster.

4) Run the OPatch System Space Check

Check if enough free space is available on the ORACLE_HOME filesystem for the patches to be applied as given below:

For Grid Infrastructure home, as home user:

Create file /tmp/patch_list_gihome.txt with the following content:

/u01/software/35037840/35042068

/u01/software/35037840/35050331

/u01/software/35037840/35050325

/u01/software/35037840/35107512

/u01/software/35037840/33575402

Run the OPatch command to check if enough free space is available in the Grid Infrastructure home:

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt

For the database home , create file /tmp/patch_list_db_home.txt with below content.

/u01/software/35037840/35042068

/u01/software/35037840/35050331

Run the OPatch command to check if enough free space is available in the Database home as below.

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_db_home.txt

5) One-off Patch Conflict Detection and Resolution

The following commands check for conflicts in both the 19c Grid home and the 19c DB homes.

In case you are applying the patch, run this command:

As root user,

[root@labhost01 ~]# export ORACLE_HOME=/u01/app/19.3.0.0/grid

[root@labhost01 ~]# export PATH=$ORACLE_HOME/bin:$PATH

[root@labhost01 ~]# $ORACLE_HOME/OPatch/opatchauto apply /u01/software/35037840 -analyze

6) Patch Installation 

Lets apply the patch to both Grid and DB homes using 'Opatchauto' 

The OPatch utility has automated the patch application for the Oracle Grid Infrastructure (Grid) home and the Oracle RAC database homes. It operates by querying existing configurations and automating the steps required for patching each Oracle RAC database home of same version and the Grid home.

The utility must be executed by an operating system (OS) user with root privileges, and it must be executed on each node in the cluster if the Grid home or Oracle RAC database home is in non-shared storage. 

To patch the Grid home and all Oracle RAC database homes of the same version: we can use the below command.

export ORACLE_HOME=/u01/app/19.3.0.0/grid

export PATH=$ORACLE_HOME/bin:$PATH

$ORACLE_HOME/OPatch/opatchauto apply /u01/software/35037840

Node 1: 

Check the applied patches for the database and grid homes.

Repeat the same steps in all the nodes of the cluster.

Please note that the datapatch will run when the last node gets patched.

7) Check the DBA registry of the database

With this the patching 19c with 19.19 RU is completed.

I did ran into one issue when the patch was applying for the grid home, its discussed in below post, please check.

http://myoracle-world.blogspot.com/2023/04/grid-patch-failed-with-checksystemspace.html

Thanks

Sambaiah Sammeta

Grid patch failed with "CheckSystemSpace" error

 

Today I was apply the 19.19 RU to my LAB RAC environment and after applying the patch for database home, the opatchauto failed with the 'checkSystemspace' error while applying the patch to grid home.

I did ran the 'CheckSystemSpace' check before applying the patch and it completed successfully and it didnt raised any flag with space.

May the space got filled in the cfgtools during the patching which resulted in the space issue. 

Anyhow, I cleaned up space the space and resumed the opatchauto as below and this time it went through.

As you can see, this time it completed without any issues.

Thanks

Sambaiah Sammeta

April, 2023 Release Update is out now

 

Oracle had released the April,2023 Release update for the available versions. Below are the details of patches for 19c versions.

Patch           -     Description

-----------            -----------------------------------------------------

35037840     -     GI Release Update 19.19.0.0.230418
35042068     -     Database Release Update 19.19.0.0.230418
35050331     -     OCW Release Update 19.19.0.0.230418
35050325     -     ACFS Release Update 19.19.0.0.230418
35107512     -     Tomcat Release Update 19.0.0.0.0
33575402     -     DBWLM Release Update 19.0.0.0.0

In the next blog post, we will see how to apply the 19.19 RU to the 19c RAC environment.

Thanks
Sambaiah Sammeta