Oracle 11g Data Guard搭建物理Standby数据库（二）

在上篇（Oracle 11g Data Guard搭建物理Standby数据库（一））中，我们完成了Oracle 11g Data Guard搭建物理Standby数据库，并验证了主备库数据的同步，本篇将延续上篇，为主备库创建Standby Redo Log，并测试实时数据的更新。

一 创建Standby Redo Log

• 主库

在创建前，先查询下主库的Redo Log，Oracle建议Standby Redo Log的Size和主备库的一样，并且Standby Redo Log至少比Redo Log多一个。

查看主库Redo Log

SQL> select group#,thread#,bytes/1024/1024 as size_m ,members from v$log;    GROUP#    THREAD#  SIZE_M    MEMBERS---------- ---------- ---------- ---------- 1    1      50  1 2    1      50  1 3    1      50  1SQL> select group#,type,member from v$logfile;    GROUP# TYPE    MEMBER---------- ------- -------------------------------------------------- 3 ONLINE  /u01/app/oracle/oradata/szpri/redo03.log 2 ONLINE  /u01/app/oracle/oradata/szpri/redo02.log 1 ONLINE  /u01/app/oracle/oradata/szpri/redo01.log

创建Standby Redo Log

SQL> alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo01.log' size 50M;Database altered.SQL> alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo02.log' size 50M;Database altered.SQL> alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo03.log' size 50M;Database altered.SQL> alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo04.log' size 50M;Database altered.SQL> select group#,type,member from v$logfile order by 1;    GROUP# TYPE    MEMBER---------- ------- -------------------------------------------------- 1 ONLINE  /u01/app/oracle/oradata/szpri/redo01.log 2 ONLINE  /u01/app/oracle/oradata/szpri/redo02.log 3 ONLINE  /u01/app/oracle/oradata/szpri/redo03.log 4 STANDBY /u01/app/oracle/oradata/szpri/stdredo01.log 5 STANDBY /u01/app/oracle/oradata/szpri/stdredo02.log 6 STANDBY /u01/app/oracle/oradata/szpri/stdredo03.log 7 STANDBY /u01/app/oracle/oradata/szpri/stdredo04.log7 rows selected.

• 备库

创建备库Standby Redo Log，和在主库的创建脚本相同，在备库创建之前，需要先取消应用日志，否则会报错。

SQL> alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo01.log' size 50M;alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo01.log' size 50M*ERROR at line 1:ORA-01156: recovery or flashback in progress may need access to filesSQL> alter database recover managed standby database cancel;Database altered.SQL> alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo01.log' size 50M;Database altered.SQL> alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo02.log' size 50M;Database altered.SQL> alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo03.log' size 50M;Database altered.SQL> alter database add standby logfile '/u01/app/oracle/oradata/szpri/stdredo04.log' size 50M;Database altered.

二 启用RDG

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

三 测试主备实时数据更新

• 主库

SQL> insert into test values(1,'Liushuai');1 row created.SQL> commit;Commit complete.

注：不需要执行日志切换，主库的更新会实时传到备库，并更新备库。

• 备库

SQL> select *from test;ID NAME---------- -------------------------------------------------- 1 Liushuai