问题描述
早上巡检的时候看到MRP0的状态是wati_for_log
备库只能接收归档日志,不能实时应用redo log,如果主端日志不切换,备端一直会有adg延迟
按照以前的处理方式,重新开启同步,但是依然是wati_for_log状态。
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database using current logfile disconnect from session;
虽然是wait_for_log模式,但是序列号和主库还是一样的。查看告警日志:
Tue May 19 00:00:06 2020
Archived Log entry 299958 added for thread 1 sequence 139091 rlc 966507798 ID 0x3975ba22 dest 2:
RFS[15]: No standby redo logfiles available for thread 1
RFS[15]: Opened log for thread 1 sequence 139092 dbid 801114584 branch 966507798
Tue May 19 00:00:10 2020
Archived Log entry 299959 added for thread 2 sequence 162196 rlc 966507798 ID 0x3975ba22 dest 2:
RFS[12]: No standby redo logfiles available for thread 2
RFS[12]: Opened log for thread 2 sequence 162197 dbid 801114584 branch 966507798
Tue May 19 00:00:11 2020
备库standby redo状态全为UNASSIIGNED:
SQL> select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# MB ARC STATUS
---------- ---------- ---------- ---------- --- ----------
1 1 0 300 NO UNASSIGNED
2 1 0 300 NO UNASSIGNED
3 1 0 300 NO UNASSIGNED
4 1 0 300 NO UNASSIGNED
5 1 0 300 NO UNASSIGNED
6 2 0 300 NO UNASSIGNED
7 2 0 300 NO UNASSIGNED
8 2 0 300 NO UNASSIGNED
9 2 0 300 NO UNASSIGNED
10 2 0 300 NO UNASSIGNED
22 2 0 300 NO UNASSIGNED
23 2 0 300 NO UNASSIGNED
24 2 0 300 NO UNASSIGNED
25 2 0 300 NO UNASSIGNED
26 2 0 300 NO UNASSIGNED
已选择15行。
正常情况下应该有一个为active状态的日志组
原因是前一天做了rac的在线日志扩展,只扩展了主库,以为备库会同步过去,因此没有修改备库的redo日志。同时,standby log也要重建。
前一天的操作
看awr,top2的就是日志切换慢,还有好几个都是log file switch的等待
查看组信息
SQL> select * from v$logfile order by 1;
SQL> select group#,thread#,bytes/1024/1024,archived,status,members from v$log;
GROUP# THREAD# BYTES/1024/1024 ARC STATUS MEMBERS
---------- ---------- --------------- --- ---------------- ----------
14 1 300 YES INACTIVE 2
15 1 300 YES INACTIVE 2
16 1 300 YES ACTIVE 2
17 1 300 NO CURRENT 2
18 2 300 YES INACTIVE 2
19 2 300 YES INACTIVE 2
20 2 300 YES INACTIVE 2
21 2 300 NO CURRENT 2
已选择8行。
原始是4个group,每个节点4组,group大小300M,有2个member。
计划扩展后每个group大小500M,2个member。
但是只改了主库。
问题解决
重建standby日志
1.停止备库日志应用
SQL> alter database recover managed standby database cancel;
2.删除已有standby log日志组
SQL> alter database drop standby logfile group xx;
3.重建standby log
SQL> alter database add standby logfile group xx '/xx/std_redoxx.log' size 500M;
4.重新开启实时日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
查看下standby log的状态,应该有一个为active状态的日志组,如果还是不能实时应用可以重启备库
SQL> select GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
41 1 0 NO UNASSIGNED
42 1 139419 YES ACTIVE
43 1 0 YES UNASSIGNED
44 1 0 YES UNASSIGNED
45 1 0 YES UNASSIGNED
46 2 0 NO UNASSIGNED
47 2 162527 YES ACTIVE
48 2 0 YES UNASSIGNED
49 2 0 YES UNASSIGNED
50 2 0 YES UNASSIGNED
已选择10行。
status 可以有两种状态,UNASSIGNED和ACTIVE
UNASSIGNED的解释:
ARCHIVED = NO, 表明该standby redo log已经被归档,可再次使用。
ARCHIVED = YES, 表明该standby redo log从未被使用过,处于可用状态。
ACTIVE 的解释:
ARCHIVED = NO, 表明该standby redo log 已完成等待被归档。
ARCHIVED = YES, 表明该 standby redo log 正在被写入所以不能被归档.。对于任一给定线程,应该只有一个 standby redo log 处于该状态。