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
禁用如下:
日志也已经开始传输:
备库再启动应用进程:
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>