利用V$MANAGED_STANDBY监控DataGuard-CSDN博客

1、使用alter database recover managed standby database using current logfile disconnect;

备库

=======

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------------------------- ------------------------------------ ---------- ---------- ---------- ----------

MRP0 APPLYING_LOG 1 64 3472 20480

RFS IDLE 1 64 3474 1

RFS IDLE 0 0 0 0

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------------------------- ------------------------------------ ---------- ---------- ---------- ----------

MRP0 APPLYING_LOG 1 64 3474 20480

RFS IDLE 1 64 3476 1

RFS IDLE 0 0 0 0

此时主库没有做任何操作，我们会发现RFS 处于IDLE状态，但BLOCK#不断在增加，查看 truss -p 25761（RFS pid）

SHARKING_PS oracle@qadb121:/export/home/oracle> truss -p 25761

/2: kaio(6, 0x00000000, 0xFFFFFFFF7C7480C0, 0x00000010, 0xFFFFFFFF7C749D58, 0xFFFFFFFF7D500A00) (sleeping...)

/7: lwp_park(0x00000000, 0) (sleeping...)

/4: lwp_park(0x00000000, 0) (sleeping...)

/9: lwp_park(0x00000000, 0) (sleeping...)

/11: lwp_park(0x00000000, 0) (sleeping...)

/5: lwp_park(0x00000000, 0) (sleeping...)

/3: lwp_park(0x00000000, 0) (sleeping...)

/10: lwp_park(0x00000000, 0) (sleeping...)

/1: read(14, 0x106946EB6, 2064) (sleeping...)

/6: lwp_park(0x00000000, 0) (sleeping...)

/8: lwp_park(0x00000000, 0) (sleeping...)

/1: read(14, "038D\0\006\0\0\0\0\003 |".., 2064) = 909

/1: times(0xFFFFFFFF7FFFAFB0) = 5322884230

/1: kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF) Err#22 EINVAL

/1: lwp_unpark(11) = 0

/11: lwp_park(0x00000000, 0) = 0

/1: times(0xFFFFFFFF7FFFAC80) = 5322884230

/1: times(0xFFFFFFFF7FFFAD60) = 5322884230

/1: write(14, "\098\0\006\0\0\0\0\0\b\0".., 152) = 152

/11: pwrite(259, "01 "\0\0\0\0\0 d\0\0\0 A".., 512, 51200) = 512

/1: read(14, 0x1069465C6, 2064) (sleeping...)

/11: lwp_park(0x00000000, 0) (sleeping...)

/1: read(14, "038D\0\006\0\0\0\0\003 |".., 2064) = 909

/1: times(0xFFFFFFFF7FFFAFB0) = 5322884830

/1: kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF) Err#22 EINVAL

/1: lwp_unpark(5) = 0

/5: lwp_park(0x00000000, 0) = 0

/1: times(0xFFFFFFFF7FFFAC80) = 5322884830

/1: times(0xFFFFFFFF7FFFAD60) = 5322884830

/1: write(14, "\098\0\006\0\0\0\0\0\b\0".., 152) = 152

/5: pwrite(259, "01 "\0\0\0\0\0 e\0\0\0 A".., 512, 51712) = 512

/5: lwp_park(0x00000000, 0) (sleeping...)

/1: read(14, 0x106946EB6, 2064) (sleeping...)

在RFS处于 IDLE是，BLOCK#不断增加不说明它在接受信息，只是一种对block#的维护检查操作。

2、在主库创建一个表tmp_test: create table tmp_test as select * from v$instance;

主库

===========

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

alter system switch logfile;

备库

=======

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------------------------- ------------------------------------ ---------- ---------- ---------- ----------

MRP0 APPLYING_LOG 1 65 1 20480

RFS IDLE 1 65 2 1

RFS IDLE 0 0 0 0

我们会发现RFS很快又回到了IDLE，同时因为日志切换后RFS从新的日志文件开始（BLOCKE#=2）进行检查操作，然后MRP0从新的日志文件开始进行恢复操作（BLOCKE#=1）,BLOCKS代表MRP0进行恢复的文件的大小（单位:512byte），而RFS则是以一个块为操作单位(见truss -p部分）

-rw-rw---- 1 oracle dba 10M Jul 26 01:33 logc1.rdo

-rw-rw---- 1 oracle dba 10M Jul 26 01:56 logc2.rdo

-rw-rw---- 1 oracle dba 10M Jul 22 05:33 logc3.rdo

-rw-rw---- 1 oracle dba 10M Jul 22 05:33 logc4.rdo

进过一段时间后

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------------------------- ------------------------------------ ---------- ---------- ---------- ----------

MRP0 APPLYING_LOG 1 65 21 20480

RFS IDLE 1 65 23 1

RFS IDLE 0 0 0 0

而启用实时应用时（APPLYING_LOG），RFS处于IDLE执行操作的对象也是在线日志(也就是MRP0操作的对象)

3、停止REDO Apply，启用alter database recover managed standby database disconnect;

备库

====

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

24451 MRP0 WAIT_FOR_LOG 1 65 0 0

25761 RFS IDLE 1 65 1516 1

25763 RFS IDLE 0 0 0 0

25909 RFS IDLE 0 0 0 0

此时不是实时应用，而且主库这边没有操作，所以，MRP0处于wait_for_log状态，没有对任何redo 文件进行操作block# blocks都为0

进过一段时间

备库

======

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

24451 MRP0 WAIT_FOR_LOG 1 65 0 0

25761 RFS IDLE 1 65 1547 1

25763 RFS IDLE 0 0 0 0

25909 RFS IDLE 0 0 0 0

发现RFS(IDLE)的BLOCK#继续增加，它实际是在检查当前的在线日志中的块的操作

主库

=====

alter system switch logfile;

备库

======

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

24451 MRP0 WAIT_FOR_LOG 1 66 0 0

25761 RFS IDLE 1 66 2 1

25763 RFS IDLE 0 0 0 0

25909 RFS IDLE 0 0 0 0

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

24451 MRP0 WAIT_FOR_LOG 1 66 0 0

25761 RFS IDLE 1 66 8 1

25763 RFS IDLE 0 0 0 0

25909 RFS IDLE 0 0 0 0

此时切换了在线日志，所以RFS的block#从新开始检查数据块，说明此时（WAIT_FOR_LOG）RFS处于IDLE执行操作的对象就是在线日志

4、在主库删除一个表，然后进行日志切换

主库

=====

SQL> drop table tmp_test;

Table dropped.

SQL> alter system switch logfile;

System altered.

备库

====

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

24451 MRP0 APPLYING_LOG 1 66 94 95

25761 RFS IDLE 1 67 2 1

25763 RFS IDLE 0 0 0 0

25909 RFS IDLE 0 0 0 0

此时RFS从对新的在线日志进行操作

备库

======

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

24451 MRP0 WAIT_FOR_LOG 1 67 0 0

25761 RFS IDLE 1 67 257 1

25763 RFS IDLE 0 0 0 0

25909 RFS IDLE 0 0 0 0

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

28738 MRP0 APPLYING_LOG 1 67 275 20480

25761 RFS IDLE 1 67 277 1

25763 RFS IDLE 0 0 0 0

25909 RFS IDLE 0 0 0 0

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

28738 MRP0 APPLYING_LOG 1 67 277 20480

25761 RFS IDLE 1 67 279 1

25763 RFS IDLE 0 0 0 0

25909 RFS IDLE 0 0 0 0

发现切换到实时应用后，MRP0的block#是接在之前RFS处于IDLE状态下不断读的BLOCK#之后，于是再次验证了RFS处于IDLE时读取的是在线日志文件，对于一些系统维护生产的日志不会改变status至receiving。

5、在主库做一个switch logfile

主库

======

备库切换前

======

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

28738 MRP0 APPLYING_LOG 1 67 397 20480

25761 RFS IDLE 1 67 399 1

25763 RFS IDLE 0 0 0 0

25909 RFS IDLE 0 0 0 0

备库切换后

======

SQL> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

---------- --------------------------- ------------------------------------ ---------- ---------- ---------- ----------

28738 MRP0 APPLYING_LOG 1 68 1 20480

25761 RFS IDLE 1 68 2 1

25763 RFS IDLE 0 0 0 0

总结：

RFS处于IDLE时还是会处理在线redo的block，在采用实时应用时，RFS的BLOCK#大于MRP0的BLOCK#；在非实时应用时，RFS继续不断读取在线日志的BLOCK#，MRP0要么处于WAIT_FOR_LOG（BLOCK# BLOCKS都为0）或者处于APPLYING_LOG (BLOCK# BLOCKS有具体值但和RFS的BLOCK# BLOCKS没有关系)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24104518/viewspace-712570/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24104518/viewspace-712570/