DataGuard 之四：Using Real-Time Apply (Contuine DataGuard 之一 )

本文接着前面做的DataGuard 实验, 前面搭建的Physical Standby 不能做到实时同步主库数据，当主库Online Redo 归档后，才会将这个归档日志传到备机并Applied 到备库，这个延迟时间较长，如果主库业务不繁忙，可能好几天才产生一个归档文件，这时备库数据和主库数据相差就好几天了，今天看了下
Oracle DataGuard官方文档，Oracle 提供实时应用日志的方式，以下摘自官网，就不翻译了。

Using Real-Time Apply to Apply Redo Data Immediately
If the real-time apply feature is enabled, log apply services can apply redo data as it is received,
without waiting for the current standby redo log file to be archived. This results in faster switchover
and failover times because the standby redo log files have been applied already to the standby database by the time
the failover or switchover begins.

Figure 6-1 shows a Data Guard configuration with a local destination and a standby destination.
As the remote file server (RFS) process writes the redo data to standby redo log files on the standby database,
log apply services can recover redo from standby redo log files as they are being filled.

下面是在已经搭建Physical Standby 的基础上进行，

1 停 Standby REDO 应用进程 (On standby )

1
2

SQL> alter database recover managed standby database cancel;  
Database altered.

2 增加 STANDBY redo 日志 (Both on standby and Primary)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 4 ('/oradata/MANUA/stadnbylog/stdlog01.rdo') SIZE 50M reuse;  
ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 5 ('/oradata/MANUA/stadnbylog/stdlog02.rdo') SIZE 50M reuse;  
ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 6 ('/oradata/MANUA/stadnbylog/stdlog03.rdo') SIZE 50M reuse;  
ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 7 ('/oradata/MANUA/stadnbylog/stdlog04.rdo') SIZE 50M reuse;  
ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 8 ('/oradata/MANUA/stadnbylog/stdlog05.rdo') SIZE 50M reuse;  
ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 9 ('/oradata/MANUA/stadnbylog/stdlog06.rdo') SIZE 50M reuse;  
ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 10 ('/oradata/MANUA/stadnbylog/stdlog7.rdo') SIZE 50M reuse;

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
 GROUP# THREAD# SEQUENCE# ARC STATUS  
---------- ---------- ---------- --- ----------  
 4 1 14 YES ACTIVE  
 5 1 0 YES UNASSIGNED  
 6 1 0 YES UNASSIGNED  
 7 1 0 YES UNASSIGNED  
 8 1 0 YES UNASSIGNED  
 9 1 0 YES UNASSIGNED  
 10 1 0 YES UNASSIGNED
7 rows selected.

3 启动数据库到实时恢复管理模式(On Standby)

1
2

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

4 主库创建一张测试表

1
2
3
4
5
6
7
8
9
10
11
12
13

SQL> create table test_1 (id integer,name varchar2(32));
Table created.
SQL> insert into test_1 values (1,'francs');
1 row created.
SQL> insert into test_1 values(2,'tf');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_1;
 ID NAME  
---------- --------------------------------  
 1 francs  
 2 tf

注意，这步操作后，不执行日志切换操作 “Alter system switch log file”, 并接着观察日志是否进来。

5 备库上初次验证

1
2
3
4
5
6
7
8

SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select * from test_1;
 ID NAME  
---------- --------------------------------------------------
2 rows selected.

说明：第下次验证发现数据没有传过来，表是已经创建上了，猜想可能有延迟，再等等。

6 过了大概五分钟后，备库上再次验证

1
2
3
4
5
6
7
8
9
10

SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select * from test_1;
 ID NAME  
---------- --------------------------------------------------  
 1 francs  
 2 tf
2 rows selected.

发现数据已经同步了。

总结：

1. 在备库上创建 standby log ，可以实现主备机实时数据同步。
2. 这种实时数据同步的方法比归档日志传递的方法高效得多，但也有延迟，这个延迟应该在五分钟以内。