How to check StaticConnectIdentifier and other properties using Data Guard Broker

DGMGRL> show database cdb 'StaticConnectIdentifier'
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oricdb01.oric.no)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb_DGMGRL.skead.no)(INSTANCE_NAME=cdb)(SERVER=DEDICATED)))'

There are several other properties that you can check, too. See this post for a couple of examples

The documentation differntiates between monitorable (read only) and configurable properties.

How I removed a data guard config completely

Here is my config:

DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  kej01       - Primary database
    kej01_stby1 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 66 seconds ago)

1. stop the redo apply process:

DGMGRL>  edit database kej01_stby1 set state='APPLY-OFF';
Succeeded.

2. remove the standby database from the configuration:

DGMGRL> remove database kej01_stby1;
Removed database "kej01_stby1" from the configuration

3. Remove the configuration itself:

DGMGRL> remove configuration;
Removed configuration

4. On both the primary and standby server, edit the $TNS_ADMIN/listener.ora by removing these entries:

(SID_DESC =
      (GLOBAL_DBNAME = kej01_DGMGRL)
      (ORACLE_HOME = /orasw/19c)
      (SID_NAME = kej01)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = kej01_DGMGRL.skead.no)
      (ORACLE_HOME = /orasw/19c)
      (SID_NAME = kej01)
    )
 

Make sure to stop/start the listener afterwards.
5. On previous standby, remove db_unique_name:

SYS@kej01>SQL>alter system set db_unique_name='kej01' scope=spfile;

6. On both servers, stop the broker processes:

SYS@kej01>SQL>alter system set dg_broker_start=false scope=both;

System altered.

6. On the standby, finish database recovery:

SYS@kej01>alter database recover managed standby database finish;

Database altered.

The database is still mounted as a physical standby:

SYS@kej01>SQL>select open_mode, database_role from v$database;

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

If you open it now, it will be in READ ONLY status:

SYS@kej01>SQL>alter database open;

Database altered.

SYS@kej01>select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY

7. Instruct the former standby database that it is now indeed a normal ("primary") database:

SYS@kej01>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

The alert log will show that we are cutting all strings to the previous role:

2024-05-29T15:55:09.123174+02:00
ALTER DATABASE SWITCHOVER TO PRIMARY (kej01)
2024-05-29T15:55:09.125019+02:00
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2024-05-29 15:55:09.125651
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2024-05-29 15:55:09.126990
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2024-05-29 15:55:09.130097

Standby terminal recovery start SCN: 4695193
RESETLOGS after incomplete recovery UNTIL CHANGE 4695565 time 05/29/2024 15:28:15
ET  (PID:1614518): ORL pre-clearing operation disabled by switchover
Online log /log/oradata/kej01/redo1.log: Thread 1 Group 1 was previously cleared
Online log /log/oradata/kej01/redo2.log: Thread 1 Group 2 was previously cleared
Online log /log/oradata/kej01/redo3.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 4695192
2024-05-29T15:55:16.588617+02:00
Setting recovery target incarnation to 3
2024-05-29T15:55:16.662719+02:00
NET  (PID:1614518): Database role cleared from PHYSICAL STANDBY [kcvs.c:1133]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2024-05-29 15:55:16.667784
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
2024-05-29T15:55:27.547369+02:00
alter database open

At this point, my standby database is once again mounted, but it has now assumed the PRIMARY role, which is what I want:

SYS@kej01>SQL>select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PRIMARY

8. Open it now, and it will be in read write mode:

SYS@kej01>SQL>alter database open;

Database altered.

SYS@kej01>SQL>select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

According to the oracle documentation, the syntax above is pre-12.1 style, which is still compatible but DBAs are encouraged to use

ALTER DATABASE SWITCHOVER TO target_db_name [FORCE] [VERIFY];
and
ALTER DATABASE FAILOVER TO target_db_name;

instead.

Syntax for switching over a container database using data guard broker

Here I am switching over a container database called cdb, to the standby server which is running a container database with unique name cdb_stby1.

First, view the configuration:

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  cdb       - Primary database
    cdb_stby1 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 84 seconds ago)

Perform the switchover:

DGMGRL> switchover to 'cdb_stby1';

Typical output from a successful switchover:

Performing switchover NOW, please wait...
Operation requires a connection to database "cdb_stby1"
Connecting ...
Connected to "cdb_stby1"
Connected as SYSDBA.
New primary database "cdb_stby1" is opening...
Operation requires start up of instance "cdb" on database "cdb"
Starting instance "cdb"...
Connected to an idle instance.
ORACLE instance started.
Connected to "cdb"
Database mounted.
Switchover succeeded, new primary is "cdb_stby1"

If I wish to switch back, make sure you log on to whichever server is running your primary database at the time.
(DO NOT use os authentication with dgmgrl / as sysdba, it will throw an error)

dgmgrl sys@cdb_stby1 as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Sep 7 15:27:27 2023
Version 19.20.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Password:
Connected to "cdb_stby1"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  cdb_stby1 - Primary database
    cdb       - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 46 seconds ago)

DGMGRL> switchover to cdb
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb"
Connecting ...
Connected to "cdb"
Connected as SYSDBA.
New primary database "cdb" is opening...
Operation requires start up of instance "cdb" on database "cdb_stby1"
Starting instance "cdb"...
Connected to an idle instance.
ORACLE instance started.
Connected to "cdb_stby1"
Database mounted.
Switchover succeeded, new primary is "cdb"

More on setting redo routes property when adding a terminal standby database to your data guard broker configuration

As described in an earlier post, it is possible to set up a "terminal standby database" which fetches its redo information from another standby database, rather than directly from the primary database.

It is quite facinating to see how the data guard broker simplifies this setup for the DBA.

In a recent excercise @ work, I had a data guard configuration consisting of 1 primary and 3 physical standby database.

A fourth was to be added as a terminal standby database.

First, clone the new database for standby.

You can use any of the databases in the configuration as a source, from which to create a terminal database, both primary or any of the mounted physical standby databases.

When the clone is finished, add the new database to the broker config:

dgmgrl -echo sys/password@primdb_dgmgrl.oric.no as sysdba

Welcome to DGMGRL, type "help" for information.
Connected to "primdb"
Connected as SYSDBA.

Add the new database:

DGMGRL> add database 'tstby' as connect identifier is tstby.oric.no maintained as physical;
add database 'tstby' as connect identifier is tstb.oric.no maintained as physical;
Database "tstby" added
DGMGRL> show configuration;
show configuration;

Configuration - dgconfig1

  Protection Mode: MaxPerformance
  Members:
  primdb     - Primary database
    stby1     - Physical standby database
    stby2     - Physical standby database
    stby3     - Physical standby database
    tstby    - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Notice how all the standby databases are indented directly underneath the primary database, indicating that they receive their redo information directly from the primary database.

I then add the redoroutes property to the primary:

DGMGRL> edit database primdb set property redoroutes='(LOCAL:stby1,stby2,stby3 ASYNC)(stby1:tstby ASYNC)';
edit database primdb set property redoroutes='(LOCAL:stby1,stby2,stby3 ASYNC)(stby1:tstby ASYNC)';
Property "redoroutes" updated

Add the redoroutes property for when the primary and the choosen standby switches roles:

DGMGRL> edit database stby1 set property redoroutes='(LOCAL:primdb,stby2,stby3 ASYNC)(primdb:tstb ASYNC)';
edit database stby1 set property redoroutes='(LOCAL:primdb,stby2,stby3 ASYNC)(primdb:tstb ASYNC)';
Property "redoroutes" updated

Note that both of these rules must be set, otherwise your terminal standby database will not receive logs. You will see messages like the follwing:

Configuration - dgconfig1

  Protection Mode: MaxPerformance
  Members:
  primdb     - Primary database
    stby1  - Physical standby database
    stby2  - Physical standby database
    stby3  - Physical standby database

  Members Not Receiving Redo:
  tstby  - Physical standby database
    Error: ORA-16685: database does not receive redo data

View the configuration again
You will see that the broker has understood that the tstb is acting like a terminal standby database for physical standby database "stby2":

DGMGRL> show configuration;
show configuration;

Configuration - dgconfig1

  Protection Mode: MaxPerformance
  Members:
  primdb     - Primary database
    stby1  - Physical standby database
      tstby  - Physical standby database (disabled)
    stby2 - Physical standby database
    stby3 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 27 seconds ago)

Finally, enable the database:

DGMGRL> enable database 'tstb';
enable database 'tsbt';
Enabled.

The output you should see at the end is:

DGMGRL> show configuration;
show configuration;

Configuration - dgconfig1

  Protection Mode: MaxPerformance
  Members:
  primdb     - Primary database
    stby1  - Physical standby database
      tstby  - Physical standby database (receiving current redo)
    stby2 - Physical standby database
    stby3 - Physical standby database

How to take a standby database out of a data guard configuration and convert it to a standalone read/write database using the Data Guard Broker

Deactive the Data Guard Broker configuration:

dgmgrl / 
show configuration
# disable log shipping
edit database 'stdb' SET STATE='APPLY-OFF';
disable configuration;

Check if the standby database is opened in READ ONLY WITH APPLY, READ ONLY or MOUNTED mode:

select open_mode from v$database;

If in READ ONLY WITH APPLY or READ ONLY mode, close the database:

 alter database close;

Often, a database that has been opened in READ ONLY mode still have active sessions. In such cases, it may be necessary to shut the database down and open it in mount-mode:

 shutdown immediate
 startup mount

Activate the standby database:

 ALTER DATABASE ACTIVATE STANDBY DATABASE;

Verify that the status of the control file has changed from "STANDBY" to "CURRENT":

 select CONTROLFILE_TYPE from v$database;
 
 CONTROL
 -------
 CURRENT

Open the database:

 
 ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
 ALTER DATABASE OPEN;

If not done by the broker, reset the value of log_archive_dest_2:

 Alter system set log_archive_dest_2='';

Your database should now be stand alone and out of the data guard configuration.

To avoid errors related to redo shipping, make sure that your old primary no longer is attempting to ship redo log information to the old standby database, which is now out of the data guard configuration.
On the old primary server, set the relevant log_archive_dest_n parameter to DEFER:

 alter system set log_archive_dest_state_2=defer scope=both;

Workaround for Automatic Diagnostic Repository Errors when executing "validate database" through Data Guard Broker

If you have a Data Guard setup and using the broker, you may see the following error when validating your setup before a switchover:

DGMGRL> validate database stb01

  Database Role:     Physical standby database
  Primary Database:  proddb01

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    proddb01:  Off
    stb01:     Off

  Managed by Clusterware:
    proddb01:  NO
    stb01:     NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Automatic Diagnostic Repository Errors:
    Error                       proddb01 stb01
    System data file missing    NO       YES
    User data file missing      NO       YES

This problem and its solution is outlined in Doc Id 2300040.1 "Known issues when using "Validate database" DGMGRL command" at Oracle Support.

Cause: The issue occurs because the old health check messages were not purged properly and the command VALIDATE DATABASE signals it found a failed check.

To get rid of this warning, rename the file HM_FINDING.ams from the ADR rdbms metadata folder or move it to another folder. This is what I did on my standby server:

 cd /u01/oracle/diag/
find . -name "HM_FINDING.ams"
./rdbms/stb01/stb01/metadata/HM_FINDING.ams
./rdbms/stb01/proddb01/metadata/HM_FINDING.ams
./plsql/user_oracle/host_1804485962_107/metadata/HM_FINDING.ams

Rename or remove the files listed above and execute the "validate database" command in dgmgrl again. The message should now be gone for good.

Note that the file named HM_FINDING.ams will most likely reappear immediately after deletion. But this new copy will not cause the Data Guard Broker to throw warnings.

How to solve message from broker Warning: standby redo logs not configured for thread 1 on primary

DGMGRL> validate database 'proddb01';

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    tdridbt1:  Off

  Managed by Clusterware:
    tdridbt1:  NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

DGMGRL> validate database 'stby01';

  Database Role:     Physical standby database
  Primary Database:  proddb01

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    proddb01:  Off
    stby01:    Off

  Managed by Clusterware:
    proddb01:  NO
    stby01:    NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (proddb01)              (stby01)
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (stby01)              (proddb01)
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on proddb01

The solution was found in Doc ID 1956103.1 "Warning: standby redo logs not configured for thread on "

This is how I corrected the situation:

On the primary, check the status of the standby redo log files:

set lines 200
col member format a50
select s.group#,s.thread#,s.sequence#,s.archived,s.status,f.member, f.type
from v$standby_log s, v$logfile f
where f.type = 'STANDBY'
and s.group# = f.group#;


    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo01.log               STANDBY
         5          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo02.log               STANDBY
         6          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo03.log               STANDBY
         7          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo04.log               STANDBY

Indeed, there is no standby redo logs assigned to thread 1.

First, set standby_file_management to MANUAL on both primary and standby, if not already done.

alter system set standby_file_management=MANUAL;

Still on the primary, drop the standby redo logs:

alter database drop logfile group 4;

Database altered.

alter database drop logfile group 5;

Database altered.

alter database drop logfile group 6;

Database altered.

alter database drop logfile group 7;

Database altered.

Still on the primary, add new standby redo logs:

alter database add standby logfile thread 1 group 4('/u03/oradata/proddb01/stb_redo01.log') size 2048M REUSE;
alter database add standby logfile thread 1 group 5('/u03/oradata/proddb01/stb_redo02.log') size 2048M REUSE;
alter database add standby logfile thread 1 group 6('/u03/oradata/proddb01/stb_redo03.log') size 2048M REUSE;
alter database add standby logfile thread 1 group 7('/u03/oradata/proddb01/stb_redo04.log') size 2048M REUSE;

Run the query above again, and you should see a thread number assigned to each group:

    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo01.log               STANDBY
         5          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo02.log               STANDBY
         6          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo03.log               STANDBY
         7          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo04.log               STANDBY

On the standby database, stop managed recovery:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Check the status:

     GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1         46 YES ACTIVE     /u03/oradata/stby01/stb_redo01.log                 STANDBY
         5          1          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo02.log                 STANDBY
         6          0          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo03.log                 STANDBY
         7          0          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo04.log                 STANDBY        

Drop the two standby redo log groups not having been assigned a thread number:

alter database drop logfile group 6;

Database altered.

alter database drop logfile group 7;

Database altered.

Add two new groups:

alter database add standby logfile thread 1 group 6('/u03/oradata/stby01/stb_redo03.log') size 2048M REUSE;

Database altered.

alter database add standby logfile thread 1 group 7('/u03/oradata/stby01/stb_redo04.log')  size 2048M REUSE;

Database altered.

Start managed recovery again:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.

Verify by running the same query once more:

    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1         46 YES ACTIVE     /u03/oradata/stby01/stb_redo01.log               STANDBY
         5          1          0 NO  UNASSIGNED /u03/oradata/stby01/stb_redo02.log               STANDBY
         6          1          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo03.log               STANDBY
         7          1          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo04.log               STANDBY

The warning is now gone from the output of data guard broker:

DGMGRL> validate database 'stby01';

  Database Role:     Physical standby database
  Primary Database:  proddb01

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    proddb01:  Off
    stby01:  Off

  Managed by Clusterware:
    proddb01:  NO
    stby01:  NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

Finally, remember to adjust standby_file_management to AUTO on both primary and standby databases:

alter system set standby_file_management=AUTO;

How to fix error message from data guard broker "Property 'DbFileNameConvert' has inconsistent values"

After a rebuild of a physical standby database, I was tailing the broker log file drcSALES.log, and noticed the following warning:

Property 'DbFileNameConvert' has inconsistent values:
METADATA='/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES', 
SPFILE=  '/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES', 
DATABASE='/data1/oradata/SALES/, /data1_tstb/oradata/SALES/, /data2/oradata/SALES/, /data2_tstb/oradata/SALES/'

Note that the metadata and spfile is missing the trailing slash character /.

To fix this incorrect setup:

dgmgrl / as sysdba
show configuration;
Configuration - SALES

  Protection Mode: MaxPerformance
  Members:
  SALES      - Primary database
    SALES_STB  - Physical standby database
      SALES_TSTB - Physical standby database (receiving current redo)
    SALES_RO   - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

Check the current setting:

show database 'SALES_TSTB' DbFileNameConvert
DbFileNameConvert = '/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES'

Update the property:

DGMGRL> edit database 'SALES_STB' set property DbFileNameConvert='/data1/oradata/SALES/,/data1_stb/oradata/SALES/, /data2/oradata/SALES/, /data2_stb/oradata/SALES/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "dbfilenameconvert" updated

Verify the new setting:

show database 'SALES_TSTB' DbFileNameConvert
DbFileNameConvert = '/data1/oradata/SALES/, /data1_tstb/oradata/SALES/, /data2/oradata/SALES/, /data2_tstb/oradata/SALES/'

Restart your physical database, and the issue is fixed.

How to set up "RedoRoutes" in a Data Guard Broker configuration

In this example, the following members participate in my Data Guard Configuration: 

Database Name	Role	Open Mode	Function
pksprod	Primary	OPEN	Primary database
pks_stb	Cascading Physical Standby	MOUNTED	Used for failover
pks_ro	Active Data Guard	READ ONLY WITH APPLY	Used for reporting
pks_tstb	Terminal Physical Standby	MOUNTED	Used for migration to a new geographical location

I am using the concept of a "Terminal Standby Database" to move the database from one geographical location to another.
In order for the cascading database to send its redo log stream to the terminal standby database, I had to configure the Data Guard Broker attribute "redoroutes", like this:

edit database "pksprod" set property redoroutes='(LOCAL : pks_stb, pks_ro ASYNC) (pks_stb : pks_tstb ASYNC)';
edit database "pks_stb" set property redoroutes =' (LOCAL :pksprod ASYNC, pks_ro ASYNC)(pksprod : pks_tstb ASYNC)';

which means

* When pksprod is primary, it shall send redo to pks_stb and pks_ro, while pks_stb shall send its redo to pks_tstb
* When pks_stb is primary, it shall send redo to pksprod and pks_ro, while pksprod shall send its redo to pks_tstb

When done, check the outcome like this:

DGMGRL> show database "pksprod" redoroutes
  RedoRoutes = '(LOCAL : pks_stb,pks_ro ASYNC)(pks_stb : pks_tstb ASYNC)'
DGMGRL> show database "pks_stb" redoroutes
  RedoRoutes = '(LOCAL : pksprod ASYNC, pks_ro ASYNC)(pksprod : pks_tstb ASYNC)'

The 12.2 documentation for the RedoRoutes attribute can be found here. You should familarize yourself with the how you can set up the redoroutes to suit your needs. In my case it was the only way I was able to get the DG configuration to work the way I was intending. It was setup using the 12.2 version of the Oracle database software.

How to view a specific property for a databasen using dgmgrl

DGMGRL> show database 'PROD_STB' 'DbFileNameConvert';

Potential solution to dgmgrl error ORA-16665: time out waiting for the result from a member

After having added a terminal standby database to an existing configuration, the Data Guard Broker configuration seemed unhappy with communicating with the new member. The output from "show configuration" showed the following:

DGMGRL> show configuration;

Configuration - DB01

  Protection Mode: MaxPerformance
  Members:
  DB01      - Primary database
    DB01_STB  - Physical standby database
      DB01_TSTB - Physical standby database (receiving current redo)
        Error: ORA-16665: time out waiting for the result from a member

    DB01_RO   - Physical standby database

When looking at the details by using

show database verbose "DB01_TSTB"

the entire operation would take very long, and at the, the following message is displayed:

Database Status:
DGM-17016: failed to retrieve status for database "DB01_TSTB"
ORA-16665: time out waiting for the result from a member

The broker log file showed:

09/02/2020 15:08:52
Data Guard Broker Status Summary:
  Type                        Name                            Severity  Status
  Configuration               DB01                            Warning  ORA-16607
  Primary Database            DB01                            Success  ORA-0
  Physical Standby Database   DB01_STB                        Success  ORA-0
  Physical Standby Database   DB01_RO                         Success  ORA-0
  Physical Standby Database   DB01_TSTB                       Error  ORA-16665

Root cause here was firewalls. The terminal standby database could not reach the primary database. Although the terminal standby database isn't set up to receive redo data from the primary database directly, in a broker configuration all members must be able to communicate with eachother. A good tool for troubleshooting issues dealing with ports and firewalls is nmap. I installed it on the terminal server and issued:

[root@db04_server ~]# nmap -n -p 1511 db01_sever.oric.no

Starting Nmap 6.40 ( http://nmap.org ) at 2020-09-02 14:23 CEST
Nmap scan report for db01_sever.oric.no (xxx.xxx.xxx.xxx)
Host is up (0.016s latency).
PORT     STATE    SERVICE
1511/tcp filtered 3l-l1

Nmap done: 1 IP address (1 host up) scanned in 0.49 seconds

A filtered port means that it is not possible to determine whether the port is open or closed, most often due to firewalls along the way. Further checks in the firewall log files showed

action=Drop service=1511 dst=xxx.xxx.xxx.xxx scr=yyy.yyy.yyy.yyy

where xxx.xxx.xxx.xxx was matching the ipadress of the terminal standby server, while yyy.yyy.yyy.yyy was matching the ipadress of the primary server. The network admin opened the port, and the ORA-16665 immediately disappeared from the dgmgrl output.

How to set parallelism for a data guard recovery process

If you need to manually set the apply-process DOP (Degree of Parallelism), you can do this by setting the property ApplyParallel=num where num is the number of parallel processes. 

In my setup, I have three databases: 

•  A primary database (hdal) 
• A physical standby database (hdal_stb) which serves as a "cascading" database 
• A terminal standby database (hdal_tstb) which receives redo from the cascading standby database  

Logged in on any one of the participating servers of your data guard configuration, start dgmgrl: 

dgmgrl / as sysdba
Connected to "hdal_stb"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  hdal      - Primary database
    hdal_stb  - Physical standby database
      hdal_tstb - Physical standby database (receiving current redo)

DGMGRL> edit database 'hdal_stb' set property ApplyParallel=4;

In the database's alert log, we can se that Oracle is cancelling the ongoing recovery process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
2020-08-10T09:26:24.249847+02:00
MRP0: Background Media Recovery cancelled with status 16037
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 27754456
2020-08-10T09:26:24.510262+02:00
ORA-16037: user requested cancel of managed recovery operation
2020-08-10T09:26:24.617519+02:00
MRP0: Background Media Recovery process shutdown (hdal)
2020-08-10T09:26:25.250904+02:00
Managed Standby Recovery Canceled (hdal)

And then starting it again:

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT PARALLEL 4 NODELAY
2020-08-10T09:26:25.284547+02:00
Attempt to start background Managed Standby Recovery process (hdal)
Starting background process MRP0
2020-08-10T09:26:25.306150+02:00
MRP0 started with pid=77, OS id=115854
2020-08-10T09:26:25.311038+02:00
MRP0: Background Managed Standby Recovery process started (hdal)
2020-08-10T09:26:30.339894+02:00
 Started logmerger process
2020-08-10T09:26:30.378497+02:00
Managed Standby Recovery starting Real Time Apply
2020-08-10T09:26:30.515607+02:00
Parallel Media Recovery started with 4 slaves
2020-08-10T09:26:30.741846+02:00
Media Recovery Waiting for thread 1 sequence 8299 (in transit)

Verify the new setting by looking at the properties (output truncated for brevity):

DGMGRL> show database verbose hdal_stb

Database - hdal_stb

  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: 0 Byte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    hdal

  Properties:
    DGConnectIdentifier             = 'hdal_stb'
    LogXptMode                      = 'ASYNC'
    ApplyParallel                   = '4'

The change will be propagated to the other members of the data guard configuration. The data guard broker logfile, located in $ORACLE_BASE/diag/rdbms/$UNIQUE_NAME/$ORACLE_SID/trace, shows:

08/10/2020 09:26:24
Forwarding EDIT_RES_PROP operation to member hdal for processing
08/10/2020 09:26:31
Updated broker configuration file available, loading from "$ORACLE_HOME/dbs/dr2hdal_stb.dat"
08/10/2020 09:42:35
Forwarding MON_PROPERTY operation to member hdal_tstb for processing

The cascading database hdal_tstb simply confirms that the broker configuration has been updated:

08/10/2020 09:26:31
Updated broker configuration file available, loading from "$ORACLE_HOME/dbs/dr2hdal_tstb.dat"

The primary database is also verifying the change, although a bit more detailed:

08/10/2020 09:26:24
EDIT DATABASE hdal_stb SET PROPERTY applyparallel = 4
08/10/2020 09:26:31
EDIT INSTANCE hdal ON DATABASE hdal_stb SET PROPERTY applyparallel = 4 completed successfully

Documentation for dgmgrl version 19c can be found here

How to connect to and start a specific database using dgmgrl

The data guard broker allows you to connect to any of the participating members of the configuration:

oracle@myserver:[hdal]# dgmgrl / as sysdba
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jul 31 10:44:47 2020

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

Welcome to DGMGRL, type "help" for information.
Connected to "hdal"
Connected as SYSDBA.
DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  hdal      - Primary database
    hdal_stb  - Physical standby database
      hdal_tstb - Physical standby database (receiving current redo)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 51 seconds ago)

DGMGRL> connect sys@hdal_stb as sysdba
Password:
Connected to "hdal_stb"
Connected as SYSDBA.

Note that the password files must be identical for all members of the configuration.
You can startup and shutdown individual members of your configuration without leaving the data guard broker interface, for example:

DGMGRL> startup mount
ORACLE instance started.
Database mounted.

Documentation on how to use Oracle 12.2 version of dgmgrl is found here

Potential solution to ORA-1033 during configuration of standby database in data guard broker

In my environment, the following message was displayed in dgmgrl when trying to configure a data guard environment:

DGMGRL> add database 'hdal_stb' as  connect identifier is 'hdal_stb';
Error: ORA-1033: ORACLE initialization or shutdown in progress

Failed.

I had prior to this attempt duplicated the target database to the auxiliary using "duplicate target databas for standby from active database" - and the output looked fine.

Turns out that you cannot set the parameter redo_transport_user to any other value than SYS when cloning for standby.
In my environment, we use a dedicated user for this purpuse, as we avoid using the SYS user as much as possible.

I adjusted the parameter to read SYS on both the primary and standby instance, and reran the duplication.

Afterwards, the database could be added:

DGMGRL> add database 'hdal_stb' as  connect identifier is 'hdal_stb';
Database "hdal_stb" added

Workaround for error Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the member setting

Error from data guard:

oracle@oric-dbserver01:[vegdb01]# dgmgrl /
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Thu Mar 12 08:02:54 2020
Version 18.9.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "vegdb01"
Connected as SYSDG.
DGMGRL> show configuration;

Configuration - myConfig

  Protection Mode: MaxPerformance
  Members:
  vegdb01      - Primary database
    vegdb01_stby - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with member setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 10 seconds ago)

DGMGRL> show database vegdb01_stby

Database - vegdb01_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: 143.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    vegdb01
      Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the member setting

Database Status:
WARNING

Solved by setting the log_file_name_convert on the standby side.
Using dgmgrl:

edit DATABASE "vegdb01_stby" SET PROPERTY LogFileNameConvert =  " "," "; 

Or set it by using sqlplus on the standby site, see this post for how to do that

How do identify a transport error in a Data Guard setup using the broker

Applicable to Oracle 12.2

If the broker has been setup, check the configuration basics:

dgmgrl /
show configuration;
Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  proddb01       - Primary database
    Error: ORA-16778: redo transport error for one or more members

    proddb01_stby1 - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member

    proddb01_stby2 - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member

In the configuration above, I have a primary + two physical standby databases.

Check the database:

DGMGRL> show database proddb01;

Database - proddb01

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    proddb01
      Error: ORA-16737: the redo transport service for member "proddb01_stby1" has an error

Use the 'LogXptStatus' monitorable property to check for transporation errors:

DGMGRL> show database proddb01 'LogXptStatus'
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME     STATUS                ERROR
            proddb01       proddb01_stby1      ERROR ORA-16191: Primary log shipping client not logged on standby
            proddb01       proddb01_stby2      VALID

The error ORA-16191 was in my case related to password files not being identical on the standby and the primary database.

For the standby databases, you can use the monitorable property 'RecvQEntries', which returns a table indicating all log files that were received by the standby database but have not yet been applied:

DGMGRL>  show database proddb01_stby1 'RecvQEntries'
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
         NOT_APPLIED        894183587                1               110406  07/19/2019 10:28:10  07/19/2019 10:29:19          73008920461          73009040943           424661
         NOT_APPLIED        894183587                1               110408  07/19/2019 10:30:35  07/19/2019 10:31:50          73009169966          73009301192           442727
         NOT_APPLIED        894183587                1               110409  07/19/2019 10:31:50  07/19/2019 10:33:14          73009301192          73009443216           426723
         NOT_APPLIED        894183587                1               110410  07/19/2019 10:33:14  07/19/2019 10:34:32          73009443216  

The same information can be obtained by checking the view v$archived_log, like this:

select sequence#
from v$archived_log
where registrar = 'RFS'
and   applied = 'NO';

The actual lag that these non-applied logs causes, can be found with the query:

set lines 200
col source_db_unique_name format a20
col value format a20
SELECT source_db_unique_name,name,value,unit,time_computed,datum_time
FROM v$dataguard_stats
WHERE name = 'apply lag';

The RecvQEntries property is documented here

The LogXptStatus property is documentet here