Oracle Data Guard switchover with DG Broker on oracle database 19C

 Dear All,

In this post i am going to share oracle Data Guard switchover  activity using  data guard  broker.

perform an Oracle Data Guard switchover with broker, you can follow below steps:

Prepare for the switchover

Before starting the switchover process, make sure that both the primary and standby databases are synchronized and that all required logs have been applied to the standby database. Also, ensure that the broker is configured and running on both the primary and standby sites.

Manual Switchover Read Here

Step 1: Check Archvie Gap

Before starting the switchover activity let's verify the archive gap between primary and standby.

On Primary:

SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d

where v.resetlogs_change# = d.resetlogs_change#

group by thread# order by 1;

On Standby:

SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d

where v.resetlogs_change# = d.resetlogs_change#

group by thread# order by 1; 

Step 2: Connect to the broker
dgmgrl sys/Welcome_123

Step 3: Verify the configuration

DGMGRL> show configuration

Step 4: Prepare for the switchover

Validate Primary Database

DGMGRL> validate database prod;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:

    prod:  Off

  Managed by Clusterware:

    prod:  NO

    Validating static connect identifier for the primary database prod...

    The static connect identifier allows for a connection to database "prod".

Validate Standby Database

DGMGRL> validate database stby;

  Database Role:     Physical standby database

  Primary Database:  prod

  Ready for Switchover:  Yes

  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:

    prod:  Off

    stby :  Off

  Managed by Clusterware:

    prod:  NO

    stby :  NO

    Validating static connect identifier for the primary database prod...

    The static connect identifier allows for a connection to database "prod".

  Current Log File Groups Configuration:

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status

              (prod)               (stby)

    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status

              (stby)                (prod)

    1         3                       0                       Insufficient SRLs

    Warning: standby redo logs not configured for thread 1 on prod

Step 5: Initiate the switchover

Initiate the switchover by running the "switchover to " command in dgmgrl. This will start the switchover process and automatically failover the primary database to the standby database.

DGMGRL> switchover to stby;

Performing switchover NOW, please wait...

Operation requires a connection to database "stby"

Connecting ...

Connected to "stby"

Connected as SYSDBA.

New primary database "stby" is opening...

Operation requires start up of instance "prod" on database "prod"

Starting instance "prod"...

Connected to an idle instance.

ORACLE instance started.

Connected to "prod"

Database mounted.

Database opened.

Connected to "prod"

Switchover succeeded, new primary is "stby"

Step 6: Verify the switchover

After the switchover, verify that the new primary database is running by running the "show database" command in dgmgrl.

Current Primary stby

DGMGRL> show database stby

Database - stby

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    stby

Database Status:

SUCCESS

Current standby prod

DGMGRL> show database prod

Database - prod

  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: 0 Byte/s

  Real Time Query:    ON

  Instance(s):

    prod

Database Status:

SUCCESS

Database Warnings

Sometimes you will face the below error, then you have to check the log_archive_dest_1 and log_archive_dest_2 parameter on the standby side and configure it properly.

DGMGRL> show database prod

Database - prod

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      (unknown)

  Apply Lag:          (unknown)

  Average Apply Rate: (unknown)

  Real Time Query:    ON

  Instance(s):

    prod

  Database Warning(s):

    ORA-16854: apply lag could not be determined

    ORA-16856: transport lag could not be determined

    ORA-16858: last communication time from redo source could not be determined

Database Status:

WARNING

DGMGRL> show database stby;

Database - stby

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

   

Database Status:

ERROR

Step 7: Start Recovery

After the switchover start the MRP process using the broker or manually.

Enable with Broker:

DGMGRL> EDIT DATABASE prod SET STATE='APPLY-ON';

Succeeded.

Check MRP status using SQL command, if no row is selected that means MRP is disabled.

SQL> select inst_id, process, status from gv$managed_standby where process='MRP0';

   INST_ID PROCESS   STATUS

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

         1 MRP0      APPLYING_LOG

Disable with Broker

DGMGRL> EDIT DATABASE prod SET STATE='APPLY-OFF';

Enable/Disable MRP process manually:

stop MRP :

SQL> alter database recover managed standby database cancel;

start MRP:

SQL> alter database recover managed standby database disconect from session;

Thanks,

Srini

Oracle Data Guard Failover with DG Broker on oracle database 19C

  Dear All,

In this post i am going to share oracle Data Guard Failover activity using  data guard  broker.

Note :::::
Before performing the FAILOVER activity, you should enable the FLASHBACK on standby and primary. If you missed to enable you need to rebuild the standby post failover activity.

Primary Database:

The primary database name is STBY.

Standby Database:
The Standby database name is prod.

Check the MRP process on Standby

Before doing anything let's check the MRP process on the STANDBY machine.

SQL> select sequence#,process,status from v$managed_standby;

As per the above screenshot, MRP and RFS processes are running.

Failover

To start this activity, I'm going to perform a failover on the primary database.

Step 1: Shutdown the PRIMARY Database

SQL> alter system switch logfile;

SQL> shut immediate;

Step 2: Check the MRP process again

After the shutdown of the PRIMARY database, check the MRP processes again on STANDBY.

SQL> select sequence#,process,status from v$managed_standby;

MRP process

 this time RFS is not running on the STANDBY server. after primary stoped RFS not running .

before primary stop see the RFS status 

Step 3: Connect with DG Broker on Standby

Let's connect with DG Broker on the Standby machine using the following command and check the database configuration.

$dgmgrl sys/Welcome_123

DGMGRL> show configuration

In the above screenshot, you can see there is an error "ORA-1034: ORACLE not available" with the PRIMARY database.

Step 4: START FAILOVER

Perform failover using DG broker, use the below query:

DGMGRL> failover to prod;

failover to prod

After failover, now "prod" is the primary database.

DGMGRL> show configuration

show configuration

After failover, you can see the log sequence number is reset and started from 1, as you can check in the below screenshot.

archive log list

Reinstate the old Primary

Let's reinstate the old primary database using the below steps.

Step 5: Start the old Primary till mount

To perform reinstate start the old primary till mount stage.

$sqlplus / as sysdba

SQL> startup mount

start database till mount

After mounting check the database role.

reinstate the old Primary

As per the above screenshot, the STBY database role is PRIMARY. But in the Data Guard environment, we can maintain only one Primary, and right now the prod is our Primary database.

Step 6: Reinstate Database

Now, this is the time to reinstate the database using the following command.

DGMGRL> reinstate database STBY;

reinstate database STBY

Let's check the database configuration now.

DGMGRL> show configuration

DGMGRL> reinstate database STBY;

Reinstating database "stby", please wait...

Reinstatement of database "stby" succeeded

DGMGRL> show configuration

Configuration - prod

  Protection Mode: MaxPerformance

  Members:

  prod - Primary database

    stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:

SUCCESS   (status updated 14 seconds ago)

DGMGRL> show database stby;

Database - stby

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 1 second ago)

  Apply Lag:          0 seconds (computed 1 second ago)

  Average Apply Rate: 6.00 KByte/s

  Real Time Query:    ON

  Instance(s):

    stby

Database Status:

SUCCESS

DGMGRL> show database prod;

Database - prod

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    prod

Database Status:

SUCCESS

DGMGRL>

reinstate the old Primary

Now you can see the error is removed and the STBY database becomes a physical standby database.

SQL> select sequence#,process,status from v$managed_standby;

 SEQUENCE# PROCESS   STATUS

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

         0 ARCH      CONNECTED

         0 DGRD      ALLOCATED

         0 DGRD      ALLOCATED

         0 ARCH      CONNECTED

         1 ARCH      CLOSING

         0 ARCH      CONNECTED

         0 ARCH      CONNECTED

         2 RFS       IDLE

         0 RFS       IDLE

         2 MRP0      APPLYING_LOG

10 rows selected.

Primary Node :

Standby Node :

Thanks,

Srini

Data Guard Broker step on Oracle 19C Database step by step Instructions

 Dear All,

In this post i am going to show you how to enable DG Broker in Oracle 19C step by step.

Step 1. TO Enable DB Broker this  DG_BROKER_START parameter should be  TRUE value on the both the nodes.. 

Check the current status of DG Broker parameter on both sides:

Primary Side:

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     FALSE

Standby Side:

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     FALSE

Step 2:check the redo log  Gap on both the nodes 

Status on Primary

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

            25

Status on Standby

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

            25

Note : log sync up to date there is no gap in between primary and standby .

Step 3: Disable log_archvie_dest_2 parameter on both sides

Note :   To avoid "ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set" and perform the below steps.

Primary side Action

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      service="stby", ASYNC NOAFFIRM

                                                  delay=0 optional compression=

                                                 disable max_failure=0 reopen=3

                                                 00 db_unique_name="stby" net_t

                                                 imeout=30, valid_for=(online_l

                                                 ogfile,all_roles)

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string

Standby Side Action:

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string      service="prod", ASYNC NOAFFIRM

                                                  delay=0 optional compression=

                                                 disable max_failure=0 reopen=3

                                                 00 db_unique_name="prod" net_t

                                                 imeout=30, valid_for=(online_l

                                                 ogfile,all_roles)

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string

Step 4: Enable the Data Guard Broker

To enable the Data Guard Broker, 

set the parameter DG_BROKER_START to TRUE in both the primary and standby databases 

Primary Side:

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

SQL> show parameter dg_broker_config

Standby Side :

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE

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

dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0

                                                 /db_home/dbs/dr1prod.dat

dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0

                                                 /db_home/dbs/dr2prod.dat

SQL>

Data Guard Broker Configuration

Step 5: Configure Listener on both sides

Add the below entries on both sides listener.ora files.

Primary node :  listener.ora

  (SID_DESC =

   (GLOBAL_DBNAME = prod_DGMGRL)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = prod)

 )

Standby node :  listener.ora

(SID_DESC =

   (GLOBAL_DBNAME = stby_DGMGRL)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = stby)

 )

After adding the above entries in the "listener.ora" files you must reload the listener on the both the nodes .

$lsnrctl reload LISTENER

Step 6: Create the Broker Configuration

Use the DGMGRL command-line interface to create the broker configuration. This involves creating a broker configuration object, adding the primary and standby databases to the configuration, and configuring other parameters such as the observer.

$ dgmgrl sys/Welcome_123

DGMGRL>

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Nov 27 15:16:10 2023

Version 19.21.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected to "prod"

Connected as SYSDBA.

DGMGRL> create configuration 'prod' as primary database is 'prod' connect identifier is prod;

DGMGRL> show configuration;

Add standby in Broker

DGMGRL> add database 'stby' as connect identifier is stby maintained as physical;

DGMGRL> show configuration;

Database "stby" added

DGMGRL> show configuration;

Configuration - prod

  Protection Mode: MaxPerformance

  Members:

  prod - Primary database

    stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:

DISABLED

Step 7: Enable Data Guard Broker

Enable the Data Guard Broker by executing the DGMGRL command ENABLE CONFIGURATION from the primary database.

DGMGRL> enable configuration;

DGMGRL> show configuration;

Configuration - prod

  Protection Mode: MaxPerformance

  Members:

  prod - Primary database

    stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:

SUCCESS   (status updated 21 seconds ago)

Thanks,

Srini

How to Install Oracle database 19C on OEL 9.2 - Part 2

 Dear All,

In this post i am going to show you how to Install Oracle 19C on OEL 9.2 , 

If you want to watch this on Video , check on my channel ..

part  1 : YouTube 

https://www.youtube.com/watch?v=plV5YsRuOVE

Part 2 : 

https://www.youtube.com/watch?v=F-o2ih25qOI&t=87s

Install Oracle 19C database on OEL 9  .. Part 2

Database pre-requisite RPM

we can use yum or dnf tool to install the pre-requisite rpms  from OEL 8 onwards .

yum install -y oracle-database-preinstall-19c

or 

dnf install -y oracle-database-preinstall-19c

as part of this rpm we will get the oracle user with groups , earlier we need to create the oracle user and groups but now no need to create user and groups here* , but if you want to use custom user then you need to create user.

reset the oracle user password using :

passwd oracle 

also we no need to update some of OS kernel and other parameter that also as part of this preinstall rpm will take care .

update the below value to disable ..

vi /etc/selinux/config

stop and disable firewall 

Create the database home and other required directories as per below snap.

Unzip 19C s/w 

Update bash profile login as oracle user 

Upgrade opatch utility before 19C install .

Start Database installation with Latest RU .

for this error fixup :

export CV_ASSUME_DISTID=OEL7.9

Remote the inventory and then apply RU while install

 this will apply the latest RU and then install the database , if you missed to apply the latest or above 19.19 RU you will get below errors .

 

libasmclntsh19.ohso

we have applied the latest RU so we will not get any errors now , it worked for me for you guys also it should work but it depends on how you proceed *.. 

root.sh scripts needs to run from root user 

using dbca to create database : 

select general purpose .. 

select how many pdbs you need or if you want only cdb you select only cdb .. 

enable fra and archive if you want now .. else skip now later you can enable ..

Network setup .. we can do now else after db configuration we can setup network using NECA command '

remain all are default values .. db vault , EM console and other things .

set the admin passwords .. 

check the details and click on Finish ..

db configuration started .. 

Monitor the logs .. 

db configuration completed click on finish ..

Installation and db configuration completed .. 

here is the part 1 for OEL 9.2 step by step install guide .

https://anjaniappsdba.blogspot.com/2023/11/how-to-install-oracle-database-19c-on.html

Thanks,

Srini