ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
==================================================================
DGMGRL>
DGMGRL> create configuration 'india' as primary database is 'india' connect identifier is india;
Configuration "india" created with primary database "india"
DGMGRL>
DGMGRL> show configuration;
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>
DGMGRL> add database 'india_stby' as connect identifier is india_stby maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Failed.
DGMGRL>
Steps to resolve issue in 12c
========================================
DGMGRL> disable configuration;
DGMGRL> remove configuration;
Disable log_archive_dest_2 on both Primary/standby
=====================================================
alter system set log_archive_dest_2='' scope=both;
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=india_stby async valid
_for=(online_logfiles,primary_
role) db_unique_name=india_stb
y
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
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL>
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=india_stby_stby async
valid_for=(online_logfiles,pri
mary_role) db_unique_name=indi
a_stby
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
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
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL>
Disable/Enable Data Guard broker on both Primary/standby
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>
SQL>
[oracle@ip-10-0-1-12 ~]$ dgmgrl sys/sys@india
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat May 8 12:45:37 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "india"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> create configuration 'india' as primary database is 'india' connect identifier is india;
Configuration "india" created with primary database "india"
DGMGRL>
DGMGRL> show configuration
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>
DGMGRL>
DGMGRL> create configuration 'india' as primary database is 'india' connect identifier is india;
Configuration "india" created with primary database "india"
DGMGRL>
DGMGRL> show configuration
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> add database 'india_stby' as connect identifier is india_stby maintained as physical;
Database "india_stby" added
DGMGRL>
DGMGRL>
DGMGRL> show configuration
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>
Enbale LOG_ARCHIVE_DEST_2 and check log shipping is working fine
============================================================================
Primary
*************
ALTER SYSTEM SET log_archive_dest_2='service=india_stby async valid_for=(online_logfiles,primary_role) db_unique_name=india_stby' SCOPE=BOTH;
Standby
************
ALTER SYSTEM SET log_archive_dest_2='service=india async valid_for=(online_logfiles,primary_role) db_unique_name=india' SCOPE=BOTH;
DGMGRL>
DGMGRL> show configuration
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>
DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL>
DGMGRL> show configuration
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 51 seconds ago)
DGMGRL>
DGMGRL> show database india InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
DGMGRL>
DGMGRL>
DGMGRL> show database india_stby InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
DGMGRL>
DGMGRL>
DGMGRL> show database india
Database - india
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
india
Database Status:
SUCCESS
DGMGRL> show database india_stby
Database - india_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: 1.00 KByte/s
Real Time Query: ON
Instance(s):
india_stby
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show database verbose india
Database - india
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
india
Properties:
DGConnectIdentifier = 'india'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = '/u01/app/oracle/oradata/india, /u01/app/oracle/oradata/india_stby'
LogFileNameConvert = '/u01/app/oracle/oradata/india, /u01/app/oracle/oradata/india_stby'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXXX)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india_DGMGRL)(INSTANCE_NAME=india)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/india/india/trace/alert_india.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/india/india/trace/drcindia.log
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show database verbose india_stby
Database - india_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: 1.00 KByte/s
Active Apply Rate: 64.00 KByte/s
Maximum Apply Rate: 64.00 KByte/s
Real Time Query: ON
Instance(s):
india_stby
Properties:
DGConnectIdentifier = 'india_stby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '5'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = '/u01/app/oracle/oradata/india, /u01/app/oracle/oradata/india_stby'
LogFileNameConvert = '/u01/app/oracle/oradata/india, /u01/app/oracle/oradata/india_stby'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXXXX)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india_stby_DGMGRL)(INSTANCE_NAME=india_stby)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/india_stby/india_stby/trace/alert_india_stby.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/india_stby/india_stby/trace/drcindia_stby.log
Database Status:
SUCCESS
DGMGRL>
THIS WAS REALLY HELPFUL. thanks
ReplyDelete