SQL> select group#,thread#,sequence#,archived,status from v$standby_log; GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 1 0 0 YES UNASSIGNED 2 0 0 YES UNASSIGNED 3 0 0 YES UNASSIGNED 4 0 0 YES UNASSIGNED
SQL> select process,status,thread#,sequence# from v$managed_standby order by 3,1; PROCESS STATUS THREAD# SEQUENCE# --------- ------------ ---------- ---------- ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 1 121388 PROCESS STATUS THREAD# SEQUENCE# MRP0 WAIT_FOR_LOG 2 133749 RFS IDLE 2 133749
可以看到确实没有使用上 standbyredo ，状态是 unassigned ，且 MRP 的状态也是 WAIT_FOR_LOG ，如果是 ADG 的话， standbyredo 的状态应该是 active 这些，且 MRP 的状态是 APPLYING_LOG 。
看了下原因，可能是以下两点造成的：
1 、未指定 thread
SQL> select group#,thread#,sequence#,archived,status from v$standby_log; GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 1 0 0 YES UNASSIGNED 2 0 0 YES UNASSIGNED 3 0 0 YES UNASSIGNED 4 0 0 YES UNASSIGNED
thread 这列都是 0 ，理应是 1 才对
2 、 standbyredo 大小和 logfile 大小不一致
SQL> select thread#,group#,bytes/1024/1024 from v$log; THREAD# GROUP# BYTES/1024/1024 ---------- ---------- --------------- 1 11 2048 1 12 2048 1 13 2048 1 14 2048 1 15 2048 1 16 2048 2 21 2048 2 22 2048 2 23 2048 2 24 2048 2 25 2048 2 26 2048 12 rows selected.
SQL> select group#,thread#,bytes/1024/1024 from v$standby_log; GROUP# THREAD# BYTES/1024/1024 ---------- ---------- --------------- 1 0 500 2 0 500 3 0 500 4 0 500
logfile 大小是 2g ，而 standbyredo 大小是 500m
删除这些 standbyredo( 磁盘上也要物理删下 ) ，然后重建后问题解决
alter database drop standby logfile group 1;
alter database drop standby logfile group 2;
alter database drop standby logfile group 3;
alter database drop standby logfile group 4;
alter database add standby logfile thread 1 group 1 ('/iob/oradata/logfile/std_redo01.log') size 2048m;
alter database add standby logfile thread 1 group 2 ('/iob/oradata/logfile/std_redo02.log') size 2048m;
alter database add standby logfile thread 1 group 3 ('/iob/oradata/logfile/std_redo03.log') size 2048m;
alter database add standby logfile thread 1 group 4 ('/iob/oradata/logfile/std_redo04.log') size 2048m;
重建完查询：
SQL> select process,status,thread#,sequence# from v$managed_standby order by 3,1; PROCESS STATUS THREAD# SEQUENCE# --------- ------------ ---------- ---------- ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 MRP0 APPLYING_LOG 1 121388 PROCESS STATUS THREAD# SEQUENCE# RFS IDLE 1 121390 RFS IDLE 2 133751 13 rows selected. SQL> select group#,thread#,sequence#,archived,status from v$standby_log; GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 1 1 121392 YES ACTIVE 2 1 0 NO UNASSIGNED 3 1 0 YES UNASSIGNED 4 1 0 YES UNASSIGNED
好起来了
3、redo、standby redo的blocksize大小不一致
select a.group#,b.member,a.bytes/1024/1024 m,a.blocksize from v$log a,v$logfile b where a.group#=b.group#;
select a.group#,thread#,b.member,sequence#,archived,a.status from v$standby_log a,v$logfile b where a.group#=b.group#;
关于 redo 这块的 blocksize 问题：
实际上， Oracle 在设计 REDO LOG 文件的时候，已经最大限度的考虑了 REDO LOG 文件的安全性， REDO LOG 文件的 BLOCK SIZE 和数据库的 BLOCK SIZE 是完全不同的， REDO LOG 文件的 BLOCK SIZE 是和操作系统的 IO BLOCK SZIE 完全相同的，这种设计确保了一个 REDO LOG BLOCK 是在一次物理 IO 中同时写入的，因此 REDOLOG BLOCK 不会出现块断裂的现象。
