在上篇(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 1
SQL> 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.log
7 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 files
SQL> 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