Oracle data guard config error - Redo transport service for standby database error

[Former Member]

capture1.png capture.png

Hi All,

I am facing unusual error redo transport service for standby database error as shown below.

ORA-16086: Redo data cannot be written to the standby redo log

I have checked the v$flash_recovery_area_usage and we have enough space both in primary and standby but somehow I can see below error in alert log

Error 16086 creating standby archive log file at host 'xxx' *** 2018-02-12 14:59:11.940 4338 krsh.c LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16086) *** 2018-02-12 14:59:11.940 4338 krsh.c LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned *** 2018-02-12 14:59:11.941 4338 krsh.c Error 16086 for archive log file 4 to 'xxx'

Any assistance much appreciated.

Thanks

Sateesh

[Reagan]

If this is an intermittent issue then you should look at the network side. Faced this issue in the past when the standby goes out of sync when the log shipping gets interrupted due to bad network. We had the network team investigate the network settings and after making few some changes and the issue got resolved.

[Former Member]

Thanks for the update. I will check with network team.

Please find logs as below

alertprimary.txt

primarylog.txt

Regards

Sateesh

[janos_mucsi-besze]

Hi Sateesh,

If you are using DataGuard Manager to transfer the redologs, use below commands to bet an overview and info about the possible cause:

DGMGRL> show configuration -> This will show ypu exactly if there are any errors.

DGMGRL> show database <db_unique_name> -> Same as above but for one node only.

Besides this Oracle provides a very good article for troubleshooting, just search with keywords below:

"dgmgrl log 12.1"

Regards,

János

[JamesZ]

Hi Sateesh,

This seems network unavailability, please attach alert log from both primary and standby.

Although not the same error, please perform the basic check as mentioned in kba 1701375.

Best regards,
James

[Former Member]

Thanks for the update, please find alert logs and I as per KBA1701375 I have checked and everything is OK

Regards

Sateesh

primarylog.txt

alertprimary.txt

[JamesZ]

It seems the problem occurs after disabling archiving to destination:

ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams101.htm#REFRN10087

Please review them, it is likely turned off.

alert log=>

=>LOG_ARCHIVE_DEST_2  issued problem
Mon Feb 12 11:13:39 2018
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
Mon Feb 12 11:13:51 2018
Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION


 log_archive_dest_1       = "LOCATION=/oracle/XXX/oraarch/XXXarch mandatory valid_for=(all_logfiles,all_roles)"
  log_archive_dest_2       = "service="XXX_1""
  log_archive_dest_2       = "LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=1 db_unique_name="XXX_1" net_timeout=30"
  log_archive_dest_2       = "valid_for=(all_logfiles,primary_role)"
  log_archive_dest_state_2 = "DEFER"

=> problem occurs
Mon Feb 12 11:18:31 2018
NSS2 started with pid=28, OS id=27552 
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16086)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 16086 for archive log file 1 to 'XXX_1'

[Former Member]

We restarted the servers so thats why you can see above log and also confirmed with network team that we do not have any firewall or ports blocked between primary and standby server.

Thanks

Sateesh

[JamesZ]

Hi Sateesh,

So you meant "ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;" was set by purpose?

Does "ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;" ever worked?

log_archive_dest_state_2='DEFER' is used to turn off the log shipping, I am not sure the impact of log_archive_dest_state_2='DEFER' afterwards.

Best regards, James