Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set (DataGuard Broker Configuration)

by

The 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[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

1
2
DGMGRL> remove configuration;
Removed configuration

2. Disable log_archive_dest_2 from Primary and standby

Primary:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[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:

1
2
3
4
5
6
7
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:

1
2
3
4
5
6
7
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 🙂