ORA-19502: write error on file "", block number (block size=)

1. 问题描述

检查alert发现如下报错：

Wed Jun 08 23:03:50 2016
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (19502)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 19502 for archive log file 5 to 'PRODS'
Errors in file /u01/PROD/oracle/diag/rdbms/prod/PROD1/trace/PROD1_nsa2_25439.trc:
ORA-19502: write error on file "", block number  (block size=)
LNS: Failed to archive log 5 thread 1 sequence 22037 (19502)

去查看DG：

发现端午假期几天的归档尚未在备库应用，也未传过去

SQL> l
  1* select dest_name, error from v$archive_dest
SQL> /


DEST_NAME ERROR
---------------------------------------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_2 ORA-19502: write error on file "", block number  (block size=)
LOG_ARCHIVE_DEST_3

2. 问题分析

根据alert应该是由于网络原因导致LOG_ARCHIVE_DEST_2报错之类

再去备库查应用进程MRP0发现是停止的

3. 问题解决

主库查看近几天的日志都在（11g 如果备库日志未应用主库会保留）

先enable主库LOG_ARCHIVE_DEST_2

SQL>  alter system set log_archive_dest_state_2=enable;
System altered.
SQL> 

SQL> select dest_name, error from v$archive_dest;
DEST_NAME ERROR
---------------------------------------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_3
LOG_ARCHIVE_DEST_4
LOG_ARCHIVE_DEST_5

禁用如下：

  alter system set log_archive_dest_state_2=defer; 

日志也已经开始传输：

备库再启动应用进程：

SQL> alter database recover managed standby database disconnect from session;

再次查看备库情况DG已运行正常

SQL> l
  1* select PROCESS,STATUS, THREAD#,SEQUENCE# from v$managed_standby
SQL> /                                                                            


PROCESS   STATUS  THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH  CLOSING 1      22036
ARCH  CLOSING 2      15425
ARCH  CLOSING 2      15116
ARCH  CLOSING 1      22197
RFS  WRITING 1      22198
RFS  WRITING 2      15485
MRP0  APPLYING_LOG1      22034
RFS  WRITING 1      22088
RFS  WRITING 1      22089
RFS  WRITING 2      15532
RFS  OPENING 2      15486


PROCESS   STATUS  THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
RFS  CLOSING 2      15484
RFS  WRITING 1      22087


13 rows selected.


SQL> 

另一种情况，日志传输之后MRP不应用，显示一直在等待存在的日志，可以重启下MRP

SQL> alter database recover managed standby database cancel;


Database altered.


SQL> alter database recover managed standby database using current logfile disconnect from session;


Database altered.


SQL> select PROCESS,STATUS, THREAD#,SEQUENCE# from v$managed_standby;


PROCESS   STATUS  THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH  CLOSING 2 7450
ARCH  CLOSING 1 6856
ARCH  CLOSING 2 7449
ARCH  WRITING 2 7567
RFS  RECEIVING1 7471
RFS  WRITING 2 7568
RFS  RECEIVING2 7495
RFS  RECEIVING2 7497
RFS  RECEIVING1 7470
RFS  WRITING 1 7522
RFS  WRITING 1 7472
MRP0  APPLYING_LOG2 7459
RFS  RECEIVING2 7496


13 rows selected.


SQL>