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

最新推荐文章于 2025-03-26 12:12:23 发布

原创最新推荐文章于 2025-03-26 12:12:23 发布 · 4k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

本文记录了一次Oracle数据库中归档日志文件传输失败的问题排查与解决过程。问题表现为归档日志无法在网络中正确传输到备用数据库，导致备用数据库的应用进程停止。通过检查错误日志、调整主库配置并重启备用数据库的应用进程最终解决了该问题。

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>