Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set (Dataguard Broker Configuration)
3 min readThe ORA-16698 error in Oracle indicates that a Data Guard member has a LOG_ARCHIVE_DEST_n parameter with the SERVICE attribute set, which is causing a conflict or an incorrect configuration in your Data Guard setup.
[oracle@primary admin]$
[oracle@primary admin]$ dgmgrl / as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Jun 22 09:49:00 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "prod101"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS prod101 CONNECT IDENTIFIER IS prod101;
Configuration "dg_config" created with primary database "prod101"
DGMGRL>
DGMGRL> show configuration;
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
prod101 - Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> ADD DATABASE prod101s AS CONNECT IDENTIFIER IS prod101s MAINTAINED AS PHYSICAL;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Failed.
Solution : Steps to Resolve ORA-16698
1. Check Current LOG_ARCHIVE_DEST_n Parameters
2. Modify the Incorrect Parameter
3. Validate the Data Guard Configuration
4. Check the Standby Database Configuration
1. Remove the DG Broker configuration from Primary
DGMGRL> remove configuration;
Removed configuration
2. Disable log_archive_dest_2 from Primary and standby
Primary:
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
-------------------- ----------- ------------------------------
log_archive_dest_2 string service=prod101s LGWR AFFIRM SYNC valid_for=
(online_logfiles ,primary_role)
db_unique_name=prod101s
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
SQL>
Standby:
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
----------------------- ----------- ------------------------------
log_archive_dest_2 string SERVICE=prod101s NOAFFIRM ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=prod101s
SQL>
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
SQL>
3. Disable / Enable DG Broker:
Primary:
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
SQL> alter system set dg_broker_start=false scope=both;
System altered.
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
Standby:
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
SQL> alter system set dg_broker_start=false scope=both;
System altered.
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
4. Configure DG Broker
Primary:
[oracle@primary admin]$ dgmgrl / as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Jun 22 10:08:36 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "prod101"
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS prod101 CONNECT IDENTIFIER IS prod101;
Configuration "dg_config" created with primary database "prod101"
DGMGRL> ADD DATABASE prod101s AS CONNECT IDENTIFIER IS prod101s MAINTAINED AS PHYSICAL;
Database "prod101s" added
DGMGRL>
DGMGRL> show configuration;
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
prod101 - Primary database
prod101s - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
5. Enable LOG_ARCHIVE_DEST_2
Primary:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod101,prod101s)' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=prod101s LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod101s' scope=both;
System altered.
Standby:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod101,prod101s)' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=prod101 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod101' scope=both;
System altered.
6. Enable DG Broker Configuration
DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL> show configuration;
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
prod101 - Primary database
prod101s - Physical standby database
Warning: ORA-16809: multiple warnings detected for the member
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 2 seconds ago)
After Few second/minutes, just validate the Configuration status = SUCCESS.
DGMGRL> show configuration;
Configuration - dg_config
Protection Mode: MaxPerformance
Members:
prod101 - Primary database
prod101s - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 41 seconds ago)
DGMGRL>
Hope it helped 🙂
