Looking for something? Try here..

Wednesday, February 3, 2021

Oracle Data Guard Broker set up, ORA-12514, ORA-16713, Insufficient SRLs, etc.,

 We have seen how to set up or create an Oracle physical standby database in this post. As a continuation, we will look into how to set up a Data Guard Broker to manage the primary and standby databases. Data Guard Broker provides many advantages such as 

  • enabling configure and manage multiple databases from single location and automatically unifies all DBs in broker configuration, 
  • automatically setting up redo transport services and log apply services, 
  • simplifying switchover and failover, integrating role changes with Oracle clusterware, etc., 
All of the details and advantages can be found in the Oracle Data Guard Broker concepts guide here.

In this post we will look into configuring the Oracle Data Guard Broker and general issues we will encounter during the set up with their work arounds/ fixes. 

Step 1: Preserve current pfile or spfile

As we will be altering/adding a few parameters, it is always a best practice to backup the original pfile/spfile before changing the contents. 

SQL> create pfile='/oracle/ABC/19.0.0/dbs/init_ABC.ora_b4DGBroker' from spfile;

File created.
SQL> 

Step 2
: Starting Data Guard Broker DMON process
  • During physical standby set up we would have set log_archive_dest_2 parameter. We need to clear this parameter where Broker will automatically takes care of this parameter. 
  • If you do not reset the parameter, you will encounter the below error when performing the Create configuration command. 
            Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
  • We can alter the location of the broker configuration files as needed using dg_broker_config_file1 and dg_broker_config_file2 parameters
  • dg_broker_start should be set to true to let the broker DMON process start automatically when the instance starts up
SQL> ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/ABC1/broker1.dat' scope=both;

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2 = '+RECO/ABC1/broker2.dat' scope=both;

System altered.

SQL> alter system reset log_Archive_dest_2 scope=both;

System altered.

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> 

SQL> ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/ABC_STANDBY/broker1.dat' scope=both;

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2 = '+RECO/ABC_STANDBY/broker2.dat' scope=both;

System altered.

SQL> alter system reset log_Archive_dest_2 scope=both;

System altered.

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> 
Step 3: Create configuration

Connect to dgmgrl and create configuration as shown below.
-sh-4.2$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 21 23:40:44 2021
Version 19.8.0.0.0

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected to "ABC1"
Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION 'ABC_DGD' AS PRIMARY DATABASE IS 'ABC1' CONNECT IDENTIFIER IS ABC1;
Configuration "ABC_DGD" created with primary database "ABC1"
DGMGRL> show configuration;

Configuration - ABC_DGD

  Protection Mode: MaxPerformance
  Members:
  ABC1 - Primary database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL> 
Step 4: Add Standby database
DGMGRL> add database ABC_STANDBY as connect identifier is ABC_STANDBY;
Database "ABC_standby" added
DGMGRL> show configuration;

Configuration - ABC_DGD

  Protection Mode: MaxPerformance
  Members:
  ABC1        - Primary database
    ABC_standby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL> 
Now you can see the standby database is added but the status is showing as DISABLED

Step 5: Enable configuration
DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - ABC_DGD

  Protection Mode: MaxPerformance
  Members:
  ABC1        - Primary database
    ABC_standby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 8 seconds ago)

DGMGRL> show database ABC1;

Database - ABC1

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ABC

Database Status:
SUCCESS

DGMGRL> show database ABC_STANDBY;

Database - ABC_standby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          1 second (computed 0 seconds ago)
  Average Apply Rate: 66.78 MByte/s
  Real Time Query:    OFF
  Instance(s):
    ABC

Database Status:
SUCCESS

DGMGRL> 
We can see the Configuration status is now showing as SUCCESS. 
Line 18 and 30 provides the properties of both Primary and Standby database where the status of both the database is SUCCESS

Step 6: Validate the databases

We can now validate the configuration in which the actual connectivity testing along with a comprehensive set of database checks are being performed. We need to make sure validate database works without any issue for the conversion to take place if intended to. 
DGMGRL> validate database verbose ABC1;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    ABC1:  Off

  Capacity Information:
    Database  Instances        Threads
    ABC1      1                1

  Managed by Clusterware:
    ABC1:  NO
    Validating static connect identifier for the primary database ABC1...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hic023124.dc.honeywell.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ABC1_DGMGRL)(INSTANCE_NAME=ABC)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed.
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Temporary Tablespace File Information:
    ABC1 TEMP Files:  31

  Data file Online Move in Progress:
    ABC1:  No

  Transport-Related Information:
    Transport On:  Yes

  Log Files Cleared:
    ABC1 Standby Redo Log Files:  Cleared

DGMGRL> 
We can see that the validate database is throwing connection error w.r.to connect identifier even though we have properly set up tnsnames and is working fine. The reason being broker adds the static entry in broker configuration as SID_DGMGRL where as the GLOBAL_NAME in listener will be SID which causes the mismatch. Refer Oracle support note Doc ID 1582927.1 for details.
The fix is to set the StaticConnectIdentifier configuration property properly as below 
DGMGRL> edit database ABC1 set property StaticConnectIdentifier='ABC1';
Property "staticconnectidentifier" updated

DGMGRL> edit database ABC_STANDBY set property StaticConnectIdentifier='ABC_STANDBY';                                                                     
Property "staticconnectidentifier" updated

DGMGRL> 
Next we will validate the standby database similar to the above we did for primary database. 
DGMGRL> validate database verbose ABC_STANDBY;
Error: ORA-16713: The Oracle Data Guard broker command timed out.

DGMGRL> 
Now we have a different issue when validating the standby database. Referring Support note Doc ID 1322877.1 and Doc ID 2300040.1 we can extend the Operationtimeout parameter of the broker configuration. The ADR can also be cleaned up prior to running validate database if the database is too huge.
DGMGRL> show configuration OperationTimeout;
  OperationTimeout = '30'
DGMGRL> validate database verbose ABC_STANDBY;
Error: ORA-16713: The Oracle Data Guard broker command timed out.
DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=600;
Property "operationtimeout" updated
DGMGRL> validate database verbose ABC_STANDBY;

  Database Role:     Physical standby database
  Primary Database:  ABC1

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

  Flashback Database Status:
    ABC1       :  Off
    ABC_standby:  Off

  Capacity Information:
    Database     Instances        Threads
    ABC1         1                1
    ABC_standby  1                1

  Managed by Clusterware:
    ABC1       :  NO
    ABC_standby:  NO
    Validating static connect identifier for the primary database ABC1...
    The static connect identifier allows for a connection to database "ABC1".

  Temporary Tablespace File Information:
    ABC1 TEMP Files:         31
    ABC_standby TEMP Files:  30

  Data file Online Move in Progress:
    ABC1:         No
    ABC_standby:  No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 0 seconds ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:  Yes
    Gap Status:    No Gap
    Transport Lag:  0 seconds (computed 0 seconds ago)
    Transport Status:  Success

  Log Files Cleared:
    ABC1 Standby Redo Log Files:         Cleared
    ABC_standby Online Redo Log Files:   Cleared
    ABC_standby Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (ABC1)                  (ABC_standby)
    1         8                       9                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (ABC_standby)           (ABC1)
    1         8                       4                       Insufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (ABC1)                    (ABC_standby)
    1          3136 MBytes               3136 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (ABC_standby)             (ABC1)
    1          3136 MBytes               3136 MBytes

  Apply-Related Property Settings:
    Property                        ABC1 Value               ABC_standby Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        ABC1 Value               ABC_standby Value
    LogShipping                     ON                       ON
    LogXptMode                      ASYNC                    ASYNC
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         optional                 optional
    MaxFailure                      0                        0
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE

DGMGRL> 
The command now executed successfully without any issues after configuration change. 
We can see in line 62, broker is reporting insufficient Standby Redo Logs. This is because when the SRLs were added the thread number is not specified in the command. We can drop and recreate the SRLs for which the thread number is not correct to overcome this issue. Refer support note Doc ID 1956103.1 for more details
SQL> select thread#,group#,bytes,status from v$standby_log;

   THREAD#     GROUP#      BYTES STATUS
---------- ---------- ---------- ----------
         1         11 4.1943E+10 UNASSIGNED
         1         12 4.1943E+10 UNASSIGNED
         1         13 4.1943E+10 UNASSIGNED
         1         14 4.1943E+10 UNASSIGNED
         0         15 4.1943E+10 UNASSIGNED
         0         16 4.1943E+10 UNASSIGNED
         0         17 4.1943E+10 UNASSIGNED
         0         18 4.1943E+10 UNASSIGNED
         0         19 4.1943E+10 UNASSIGNED

9 rows selected.

SQL> alter database drop logfile group 15;

Database altered.

SQL> alter database add standby logfile thread 1 group 15 size 40000m;

Database altered.

/* Drop and recreate other groups as well
--alter database drop logfile group 16;
--alter database drop logfile group 17;
--alter database drop logfile group 18;
--alter database drop logfile group 19;

--alter database add standby logfile thread 1 group 16 size 40000m;
--alter database add standby logfile thread 1 group 17 size 40000m;
--alter database add standby logfile thread 1 group 18 size 40000m;
--alter database add standby logfile thread 1 group 19 size 40000m;
*/

SQL> select thread#,group#,bytes/1024/1024, status from v$standby_log;

   THREAD#     GROUP# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
         1         11           40000 UNASSIGNED
         1         12           40000 UNASSIGNED
         1         13           40000 UNASSIGNED
         1         14           40000 UNASSIGNED
         1         15           40000 UNASSIGNED
         1         16           40000 UNASSIGNED
         1         17           40000 UNASSIGNED
         1         18           40000 UNASSIGNED
         1         19           40000 UNASSIGNED

9 rows selected.

SQL> 
You can now see that all the SRLs are with proper thread number assigned. Let's check the validate database command for the standby database again. 
DGMGRL> validate database verbose ABC_STANDBY;

  Database Role:     Physical standby database
  Primary Database:  ABC1

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

  Flashback Database Status:
    ABC1       :  Off
    ABC_standby:  Off

  Capacity Information:
    Database     Instances        Threads
    ABC1         1                1
    ABC_standby  1                1

  Managed by Clusterware:
    ABC1       :  NO
    ABC_standby:  YES
    Validating static connect identifier for the primary database ABC1...
    The static connect identifier allows for a connection to database "ABC1".

  Temporary Tablespace File Information:
    ABC1 TEMP Files:         31
    ABC_standby TEMP Files:  30

  Data file Online Move in Progress:
    ABC1:         No
    ABC_standby:  No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        1 second (computed 0 seconds ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:  Yes
    Gap Status:    No Gap
    Transport Lag:  0 seconds (computed 0 seconds ago)
    Transport Status:  Success

  Log Files Cleared:
    ABC1 Standby Redo Log Files:         Cleared
    ABC_standby Online Redo Log Files:   Cleared
    ABC_standby Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (ABC1)                  (ABC_standby)
    1         8                       9                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (ABC_standby)           (ABC1)
    1         8                       9                       Sufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (ABC1)                    (ABC_standby)
    1          3136 MBytes               3136 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (ABC_standby)             (ABC1)
    1          3136 MBytes               3136 MBytes

  Apply-Related Property Settings:
    Property                        ABC1 Value               ABC_standby Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        ABC1 Value               ABC_standby Value
    LogShipping                     ON                       ON
    LogXptMode                      ASYNC                    ASYNC
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         optional                 optional
    MaxFailure                      0                        0
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE

DGMGRL> exit
-sh-4.2$
Everything is set up properly now. We are ready to rock and roll standby management using Oracle Data Guard Broker and the system is ready for switchover and failover operations if needed. 

References

Happy Brokering!!! 

2 comments: