Oracle 11g R2 Data Guard 容灾系统构建S
2019-06-17 本文已影响0人
chimpansee
目录
- 两地三中心
- 同城备份系统构建
两地三中心
当下，主流的灾备架构是两地三中心，数据中心A和数据中心B在同城作为生产级的机房，当用户访问的时候随机访问到数据中心A或B。因为A和B会同步做数据复制，所以两边的数据是完全一样的。因为是同步复制的，所以只能在同城去做两个数据中心，否则太远的话同步复制的延时会太长。A，B这两个生产级的数据中心是必须在同一个城市，或者在距离很近的另外一个城市也可以，但是距离是有要求的。数据中心A和数据中心B之间的延迟要保证在1ms内，这个距离要求在40公里之内。
千里之外的异地备份数据中心C通过异步复制过去，很明显的是异地备份的数据中心一般不做实时性要求高的业务使用，这是因为数据从生产级数据中心到异地的节点是异步去复制，数据有延时。
我们在上一篇文章中默认构建的data guard集群基本上就属于北京的数据中心A和千里之外的成都的数据中心C之间的这种异步数据传输模式。只有当北京的主数据库发送日志切换的时候，才将归档日志发送给成都的备份数据库，才能在数据中心C中访问到更新后的数据。下图是个两地三中心的概要描述。
image.png
同城备份系统构建
按照两地三中心的灾备架构，为了减小可能的数据损失风险，为了提供冗余的报表服务要求，必需要搭建同城的备份数据库系统。实现数据的同步更新。
data guard集群提供了三种可能的运行模式：
-
最大保护（maximize protection）
-
最大可用性（maximize availability）
-
最大性能（maximize performance）
三种模式优劣不同，各有各自的应用场景。对于构建同城备份系统。我们可以选择第一种或第二种。区别在于最大保护必须要求网络同步不出现任何意外，否则主数据库可能会停止服务。第二种在出现网络同步意外时，主数据库可以自动降低保护级别，取消同步，而继续提供对外服务，网络恢复了，又可以自动恢复到同步状态。
我们这里选择最大可用性保护模式。
我们添加了同城的备用数据库系统。重新规划了结构图，更新如下：
image.png
- 创建同城备用数据库。
为节省时间和简化步骤，我们直接从成都服务器克隆了一台。自行安装和配置一台新的也可。
- 修改系统基本参数，主机名，IP地址。
[root@chengdu ~]# cat /etc/hostname
beijing_2
[root@chengdu ~]# cat /etc/sysconfig/network-scripts/ifcfg-ens33
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
NETMASK=255.255.255.0
IPADDR=192.168.1.35
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
NAME=ens33
UUID=7f29b95d-7854-4069-acb9-cb60afbc88f4
DEVICE=ens33
ONBOOT=yes
[root@chengdu ~]# reboot
因为直接克隆的是安装完数据库软件的镜像，所以省去了配置oracle环境以及安装数据库的步骤。我们需要微调部分参数。
- 修改参数文件
[oracle@beijing_2 dbs]$ cat initorcl.ora
orcl.__db_cache_size=264241152
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=285212672
orcl.__sga_target=427819008
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=142606336
orcl.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oracle/oradata/ORCL/controlfile/o1_mf_gj3nkplw_.ctl','/home/oracle/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_gj3nkpmd_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oracle/oradata'
*.db_name='orcl'
*.db_domain=''
*.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=713031680
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
##standby role parameters
*.db_unique_name=beijing_2
*.service_names=beijing_2
*.log_archive_config='DG_CONFIG=(beijing,beijing_2,chengdu)'
standby_file_management=auto
fal_server=beijing
fal_client=beijing_2
将chengdu都修改为了beijing_2。唯一不同的是在DG_CONFIG中添加了beijing_2。因为我们现在是两地三中心的容灾系统了。
创建审计目录和控制文件目录，不然RMAN复制的时候会提示错误。
[oracle@beijing_2 ~]$ ll /home/oracle/app/oracle/admin/orcl/adump
ls: cannot access /home/oracle/app/oracle/admin/orcl/adump: No such file or directory
[oracle@beijing_2 ~]$ mkdir -p /home/oracle/app/oracle/admin/orcl/adump
[oracle@beijing_2 ~]$ ll /home/oracle/app/oracle/oradata/ORCL/controlfile/
ls: cannot access /home/oracle/app/oracle/oradata/ORCL/controlfile/: No such file or directory
[oracle@beijing_2 ~]$ mkdir -p /home/oracle/app/oracle/oradata/ORCL/controlfile/
[oracle@beijing_2 ~]$
修改静态侦听。
[oracle@beijing_2 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = beijing_2)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
把新的Net Service Name也添加到tnsnames.ora
[oracle@beijing_2 admin]$ cat tnsnames.ora
beijing =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = beijing)
)
)
chengdu =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = chengdu)
)
)
beijing_2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = beijing_2)
)
)
启动侦听，测试一下连通性。
[oracle@beijing_2 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 16-JUN-2019 23:47:08
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /home/oracle/app/oracle/diag/tnslsnr/beijing_2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beijing_2)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 16-JUN-2019 23:47:09
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/beijing_2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=beijing_2)(PORT=1521)))
Services Summary...
Service "beijing_2" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@beijing_2 admin]$ tnsping beijing
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 16-JUN-2019 23:47:14
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = beijing)))
OK (30 msec)
[oracle@beijing_2 admin]$ tnsping beijing_2
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 16-JUN-2019 23:47:17
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.35)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = beijing_2)))
OK (10 msec)
[oracle@beijing_2 admin]$
一切OK。
- 复制创建同城备用数据库
启动同城备用数据库
[oracle@beijing_2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 00:04:08 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup pfile=?/dbs/initorcl.ora nomount
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 436208184 bytes
Database Buffers 264241152 bytes
Redo Buffers 7155712 bytes
SQL>
RMAN复制数据库。
开始复制前，需要提前在主数据库中添加侦听参数。
[oracle@beijing admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
CHENGDU =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = chengdu)
)
)
BEIJING =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = beijing)
)
)
BEIJING_2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = beijing_2)
)
)
开始复制。
[oracle@beijing_2 admin]$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 17 00:18:07 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys@beijing
target database Password:
connected to target database: ORCL (DBID=1538750300)
RMAN> connect auxiliary sys@beijing_2
auxiliary database Password:
connected to auxiliary database: ORCL (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
PFILE ?/dbs/initorcl.ora
DORECOVER
NOFILENAMECHECK2> 3> 4>
5>
6> ;
Starting Duplicate Db at 17-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format
'/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' ;
}
中间信息省略。
contents of Memory Script:
{
set until scn 1128753;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-JUN-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
starting media recovery
archived log for thread 1 with sequence 37 is already on disk as file /home/oracle/app/oracle/fast_recovery_area/BEIJING_2/archivelog/2019_06_17/o1_mf_1_37_0ou49hq7_.arc
archived log file name=/home/oracle/app/oracle/fast_recovery_area/BEIJING_2/archivelog/2019_06_17/o1_mf_1_37_0ou49hq7_.arc thread=1 sequence=37
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-JUN-19
Finished Duplicate Db at 17-JUN-19
RMAN>
提示复制完成，我们成功创建了同城备份数据库beijing_2。
为实现最大可用模式，我们创建standby redo log。
我们在dbs目录下存放standby redo log。
[oracle@beijing_2 dbs]$ mkdir srl
[oracle@beijing_2 dbs]$ cd srl
[oracle@beijing_2 srl]$ pwd
/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/srl
大小和个数要和主数据库相同。
我们先看看主数据库的日志组情况。
[oracle@beijing admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 00:28:33 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select group#,members,bytes from v$log;
GROUP# MEMBERS BYTES
---------- ---------- ----------
1 2 52428800
2 2 52428800
3 2 52428800
SQL>
我们需要创建3组standby redo log
[oracle@beijing_2 srl]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 00:25:49 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database add standby logfile group 10 '?/dbs/srl/srl10.dbf' size 52428800;
Database altered.
SQL> alter database add standby logfile group 11 '?/dbs/srl/srl11.dbf' size 52428800;
Database altered.
SQL> alter database add standby logfile group 12 '?/dbs/srl/srl12.dbf' size 52428800;
Database altered.
SQL> select group#,type,status ,member from v$logfile;
GROUP# TYPE STATUS MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
3 ONLINE /home/oracle/app/oracle/oradata/BEIJING_2/onlinelog/o1_mf_3_gjdv2bt5_.log
3 ONLINE /home/oracle/app/oracle/fast_recovery_area/BEIJING_2/onlinelog/o1_mf_3_gjdv2ct0_
.log
2 ONLINE /home/oracle/app/oracle/oradata/BEIJING_2/onlinelog/o1_mf_2_gjdv28sm_.log
2 ONLINE /home/oracle/app/oracle/fast_recovery_area/BEIJING_2/onlinelog/o1_mf_2_gjdv29oq_
.log
1 ONLINE /home/oracle/app/oracle/oradata/BEIJING_2/onlinelog/o1_mf_1_gjdv271o_.log
1 ONLINE /home/oracle/app/oracle/fast_recovery_area/BEIJING_2/onlinelog/o1_mf_1_gjdv27ql_
.log
GROUP# TYPE STATUS MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
10 STANDBY /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/srl/srl10.dbf
11 STANDBY /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/srl/srl11.dbf
12 STANDBY /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/srl/srl12.dbf
9 rows selected.
SQL> select group#,dbid,bytes,status from v$standby_log;
GROUP# DBID BYTES STATUS
---------- ---------------------------------------- ---------- ----------
10 UNASSIGNED 52428800 UNASSIGNED
11 UNASSIGNED 52428800 UNASSIGNED
12 UNASSIGNED 52428800 UNASSIGNED
SQL>
- 主数据库配置更新。
主数据库修改部分参数后的生成的pfile有些变化。
[oracle@beijing dbs]$ cat initorcl.ora
orcl.__db_cache_size=385875968
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=469762048
orcl.__sga_target=671088640
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=234881024
orcl.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oracle/oradata/ORCL/controlfile/o1_mf_gj7n6w3b_.ctl','/home/oracle/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_gj7n6w3q_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oracle/oradata'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='BEIJING'
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_config='DG_CONFIG=(beijing,beijing_2,chengdu)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST db_unique_name=beijing'
*.log_archive_dest_2='service=chengdu valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=chengdu reopen=20'
*.log_archive_dest_3='service=beijing_2 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) lgwr sync affirm db_unique_name=beijing_2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1135607808
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='BEIJING'
*.undo_tablespace='UNDOTBS1'
我们看看修改了什么参数；
*.log_archive_dest_3='service=beijing_2 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) lgwr sync affirm db_unique_name=beijing_2'
- 配置了向同城备份数据库传输日志的参数
lgwr sync affirm 表示同步传送redo log到beijing_2
*.log_archive_config='DG_CONFIG=(beijing,beijing_2,chengdu)'
-添加了新节点beijing_2到data guard集群中 。
*.log_archive_dest_state_3='DEFER'
- 制定log_archive_dest_3延迟生效。
- 主数据库切换到最大可用模式
[oracle@beijing dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 00:54:41 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area 1135747072 bytes
Fixed Size 2227664 bytes
Variable Size 738198064 bytes
Database Buffers 385875968 bytes
Redo Buffers 9445376 bytes
Database mounted.
SQL> alter database set standby to maximize availability;
Database altered.
SQL>
SQL> alter database open;
Database altered.
我们开启到同城备用数据库的日志传送。
SQL> /
DEST_ID DEST_NAME STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE DESTINATIO LOG_SEQUENCE REOPEN_SECS DELAY_MINS MAX_CONNECTIONS
---------- ------------------------------ --------- --------- ------- ------- ---------- -------- ---------- ------------ ----------- ---------- ---------------
NET_TIMEOUT PROCESS REG FAIL_DATE FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE ERROR ALTERNATE DEPENDENCY REMOTE_TEM QUOTA_SIZE
----------- ---------- --- --------- ------------- ---------- ------------- ----------- -------------------- ---------- ---------- ---------- ----------
QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE VALID_NOW VALID_TYPE VALID_ROLE DB_UNIQUE_NAME VER COMPRES APPLIED_SCN
---------- ---------- ------------ ------------ --- ------- ---------------- --------------- ------------ ------------------------------ --- ------- -----------
3 LOG_ARCHIVE_DEST_3 DEFERRED OPTIONAL SYSTEM STANDBY LGWR PENDING beijing_2 0 300 0 1
30 LGWR YES 0 0 0 0 NONE NONE NONE 0
0 0 PARALLELSYNC 0 YES PUBLIC UNKNOWN ONLINE_LOGFILE PRIMARY_ROLE beijing_2 NO DISABLE 0
SQL> alter system set log_archive_dest_state_3=enable
2 ;
System altered.
SQL> select * from v$archive_dest where dest_id=3;
DEST_ID DEST_NAME STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE DESTINATIO LOG_SEQUENCE REOPEN_SECS DELAY_MINS MAX_CONNECTIONS
---------- ------------------------------ --------- --------- ------- ------- ---------- -------- ---------- ------------ ----------- ---------- ---------------
NET_TIMEOUT PROCESS REG FAIL_DATE FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE ERROR ALTERNATE DEPENDENCY REMOTE_TEM QUOTA_SIZE
----------- ---------- --- --------- ------------- ---------- ------------- ----------- -------------------- ---------- ---------- ---------- ----------
QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE VALID_NOW VALID_TYPE VALID_ROLE DB_UNIQUE_NAME VER COMPRES APPLIED_SCN
---------- ---------- ------------ ------------ --- ------- ---------------- --------------- ------------ ------------------------------ --- ------- -----------
3 LOG_ARCHIVE_DEST_3 VALID OPTIONAL SYSTEM STANDBY LGWR PENDING beijing_2 0 300 0 1
30 LGWR YES 0 0 0 0 NONE NONE NONE 0
0 0 PARALLELSYNC 0 YES PUBLIC YES ONLINE_LOGFILE PRIMARY_ROLE beijing_2 NO DISABLE 0
SQL>
- 在同城备用数据库上启用实时日志恢复
复制完，数据库状态处于mount状态。我们首先打开数据库。
[oracle@beijing_2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 01:16:20 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database open;
Database altered.
我们启用实时应用redo log。
SQL> SQL> SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL>
- 数据同步的简单测试
主数据库创建新表
[oracle@beijing dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 01:21:01 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table newtab10 as select * from dba_users;
Table created.
SQL> select count(*) from newtab10;
COUNT(*)
----------
30
SQL>
同城备份数据库查看结果
[oracle@beijing_2 srl]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 01:21:30 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from newtab10;
COUNT(*)
----------
30
SQL>
结果直接传送到了同城备份数据库。再也不用等到日志切换的时候了。
我们看看日志和传输状态。
SQL> select dest_id,dest_name,status,error,type,target from v$archive_dest ;
DEST_ID DEST_NAME STATUS ERROR TYPE TARGET
---------- ---------------------------------------- --------- -------------------- ------- -------
1 LOG_ARCHIVE_DEST_1 VALID PUBLIC PRIMARY
2 LOG_ARCHIVE_DEST_2 ERROR ORA-12543: PUBLIC STANDBY
TNS:destination host
unreachable
3 LOG_ARCHIVE_DEST_3 VALID PUBLIC STANDBY
4 LOG_ARCHIVE_DEST_4 INACTIVE PUBLIC PRIMARY
5 LOG_ARCHIVE_DEST_5 INACTIVE PUBLIC PRIMARY
同城的传输正常，到异地（成都）的报错，因为我们关闭了成都的备用数据库。
5.监控data guard的运行
我们希望看到日志传送的过程和状态。
我们可以模拟一个简单的插入过程，不断的提交insert语句，模拟实际的业务发生。
在主数据上面我们编写模拟脚本。
[oracle@beijing ~]$ cat batchinsert.sql
declare
v_sql varchar2(400);
begin
for i in 1..10000 loop
v_sql:='insert into tab1(c1) values('||i||')';
execute immediate(v_sql);
execute immediate('commit');
DBMS_LOCK.SLEEP(1);
end loop;
end;
/
我们开始插入数据的业务模拟。
[oracle@beijing ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 11:33:18 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table tab1(c1 int);
Table created.
SQL> @/home/oracle/batchinsert.sql
打开另一个终端，我们看看业务是否在运行。
[oracle@beijing ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 11:35:22 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),count(*) from tab1;
TO_CHAR(SYSDATE,'YY COUNT(*)
------------------- ----------
2019-06-17 11:35:45 128
SQL> /
TO_CHAR(SYSDATE,'YY COUNT(*)
------------------- ----------
2019-06-17 11:35:50 133
SQL>
数据在不断进入表tab1中。
我们看看当前主数据库的日志情况。
为观察整个data guard集群的情况，我们把异地成都的数据库也打开。
我们可以观察主数据库的log_archive_dest_1，log_archive_dest_2和log_archive_dest_3的状态以及它的日志归档情况。
[oracle@beijing ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 11:37:29 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select dest_id,dest_name,status,target,archiver,log_sequence,error,transmit_mode,type from v$archive_dest;
DEST_ID DEST_NAME STATUS TARGET ARCHIVER LOG_SEQUENCE ERROR TRANSMIT_MOD TYPE
---------- -------------------- --------- ------- ---------- ------------ ----- ------------ -------
1 LOG_ARCHIVE_DEST_1 VALID PRIMARY ARCH 58 SYNCHRONOUS PUBLIC
2 LOG_ARCHIVE_DEST_2 VALID STANDBY LGWR 59 ASYNCHRONOUS PUBLIC
3 LOG_ARCHIVE_DEST_3 VALID STANDBY LGWR 59 PARALLELSYNC PUBLIC
4 LOG_ARCHIVE_DEST_4 INACTIVE PRIMARY ARCH 0 SYNCHRONOUS PUBLIC
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 57
Next log sequence to archive 59
Current log sequence 59
SQL>
我们看到传输到异地成都的日志log_archive_dest_2采用的是异步方式。而传输到同城的log_archive_dest_3采用的是并发同步方式。
最新的日志序号是59。
我们再看看同城备用数据库和异地备用数据库的日志归档情况。
同城备库的日志情况。（我们只摘录最后的几条信息）
SQL> select REGISTRAR,CREATOR,THREAD#,APPLIED,sequence#,first_change#,next_change#, COMPLETION_TIME from v$archived_log;
REGISTR CREATOR THREAD# APPLIED SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# COMPLETIO
------- ------- ---------- --------- ---------- ------------- ------------ ---------
RFS ARCH 1 YES 48 1138175 1141489 17-JUN-19
RFS ARCH 1 YES 49 1141489 1141955 17-JUN-19
RFS ARCH 1 YES 50 1141955 1143011 17-JUN-19
RFS ARCH 1 YES 51 1143011 1143172 17-JUN-19
RFS ARCH 1 YES 52 1143172 1143186 17-JUN-19
RFS ARCH 1 YES 53 1143186 1143572 17-JUN-19
RFS ARCH 1 YES 54 1143572 1143577 17-JUN-19
RFS ARCH 1 YES 55 1143577 1143584 17-JUN-19
RFS ARCH 1 YES 56 1143584 1143593 17-JUN-19
RFS ARCH 1 YES 57 1143593 1143672 17-JUN-19
RFS ARCH 1 IN-MEMORY 58 1143672 1143681 17-JUN-19
异地备库的日志情况。
SQL> select REGISTRAR,CREATOR,THREAD#,APPLIED,sequence#,first_change#,next_change#, COMPLETION_TIME from v$archived_log;
REGISTR CREATOR THREAD# APPLIED SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# COMPLETIO
------- ------- ---------- --------- ---------- ------------- ------------ ---------
RFS ARCH 1 YES 38 1128753 1133314 17-JUN-19
RFS ARCH 1 YES 39 1133314 1136088 17-JUN-19
RFS LGWR 1 YES 47 1138151 1138175 17-JUN-19
RFS LGWR 1 YES 48 1138175 1141489 17-JUN-19
RFS LGWR 1 YES 49 1141489 1141955 17-JUN-19
RFS LGWR 1 YES 50 1141955 1143011 17-JUN-19
RFS LGWR 1 YES 51 1143011 1143172 17-JUN-19
RFS LGWR 1 YES 52 1143172 1143186 17-JUN-19
RFS LGWR 1 YES 53 1143186 1143572 17-JUN-19
RFS LGWR 1 YES 54 1143572 1143577 17-JUN-19
RFS LGWR 1 YES 55 1143577 1143584 17-JUN-19
REGISTR CREATOR THREAD# APPLIED SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# COMPLETIO
------- ------- ---------- --------- ---------- ------------- ------------ ---------
RFS LGWR 1 YES 56 1143584 1143593 17-JUN-19
RFS LGWR 1 YES 57 1143593 1143672 17-JUN-19
RFS LGWR 1 YES 58 1143672 1143681 17-JUN-19
47 rows selected.
对于最后的日志的归档，两者有明显的不同。异地的已经归档，同城的在内存中。
我们在看看同城的standby redo log情况。
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
11 1538750300 1 59 52428800 512 5375488 YES ACTIVE 1143681 17-JUN-19 1153098 17-JUN-19 1153098 17-JUN-19
12 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
11 1538750300 1 59 52428800 512 5402624 YES ACTIVE 1143681 17-JUN-19 1153152 17-JUN-19 1153152 17-JUN-19
12 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
11 1538750300 1 59 52428800 512 5593600 YES ACTIVE 1143681 17-JUN-19 1153627 17-JUN-19 1153627 17-JUN-19
12 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
standby redo log 和主数据库是一样的，而且bytes字段的值在不断的增加，说明主数据库的插入操作的日志源源不断的传入了同城备库的standby redo log中。
这里注意一下两个字段的组合应用，如果status=active且，archived=yes，表示，正在使用，不能归档；如果status=active且，archived=no，表示没有使用，等待归档；如果status=unassigned且，archived=no，表示，已经归档；如果status=unassigned，且archived=yes表示该日志未启用；
下面是我们额外添加了一个standby redo log group后，并且修改脚本，模拟业务吞吐量加大后，在同城备库截取的一段结果，能正确显示出standby redo log 的切换规律， 一个standby redo log 很快就满了，并且切换到另一个standby redo log 。
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 1538750300 1 66 52428800 512 5817856 YES ACTIVE 1172288 17-JUN-19 1172320 17-JUN-19 1172320 17-JUN-19
11 1538750300 1 65 52428800 512 45786624 NO ACTIVE 1171940 17-JUN-19 1172288 17-JUN-19 1172288 17-JUN-19
12 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 1538750300 1 66 52428800 512 45787136 YES ACTIVE 1172288 17-JUN-19 1172438 17-JUN-19 1172438 17-JUN-19
11 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
12 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 1538750300 1 66 52428800 512 45787136 NO ACTIVE 1172288 17-JUN-19 1172553 17-JUN-19 1172553 17-JUN-19
11 1538750300 1 67 52428800 512 4673024 YES ACTIVE 1172553 17-JUN-19 1172579 17-JUN-19 1172579 17-JUN-19
12 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 1538750300 1 66 52428800 512 45787136 NO ACTIVE 1172288 17-JUN-19 1172553 17-JUN-19 1172553 17-JUN-19
11 1538750300 1 67 52428800 512 13785600 YES ACTIVE 1172553 17-JUN-19 1172605 17-JUN-19 1172605 17-JUN-19
12 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 1538750300 1 66 52428800 512 45787136 NO ACTIVE 1172288 17-JUN-19 1172553 17-JUN-19 1172553 17-JUN-19
11 1538750300 1 67 52428800 512 45785088 NO ACTIVE 1172553 17-JUN-19 1172825 17-JUN-19 1172825 17-JUN-19
12 1538750300 1 68 52428800 512 512 YES ACTIVE 1172825 17-JUN-19 1172788 17-JUN-19 1172788 17-JUN-19
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
11 1538750300 1 67 52428800 512 45785088 NO ACTIVE 1172553 17-JUN-19 1172825 17-JUN-19 1172825 17-JUN-19
12 1538750300 1 68 52428800 512 18091008 YES ACTIVE 1172825 17-JUN-19 1172891 17-JUN-19 1172891 17-JUN-19
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
11 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
12 1538750300 1 68 52428800 512 30494208 YES ACTIVE 1172825 17-JUN-19 1175321 17-JUN-19 1175321 17-JUN-19
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
11 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
12 1538750300 1 68 52428800 512 30557184 YES ACTIVE 1172825 17-JUN-19 1175345 17-JUN-19 1175345 17-JUN-19
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
11 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
12 1538750300 1 68 52428800 512 30558208 YES ACTIVE 1172825 17-JUN-19 1175345 17-JUN-19 1175345 17-JUN-19
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
事实是，同城备用主机在性能上最好和主数据库系统主机相当，在高峰期，才能跟得上主数据库的日志吞吐量。额外的添加standby redo log group，也能起到一定的缓存的效果。
针对异地备用主机，Oracle提供了压缩的手段，保证远距离日志传送时，减少对网络带宽的消耗。这也是很有效的方式之一。
不过据文档提示，属于要收费的选项之一。（Note: Redo transport compression is a feature of the Oracle Advanced Compression option. You must purchase a license for this option before using the redo transport compression feature.）
我们切换一下主数据的日志。
SQL> alter system switch logfile;
System altered.
SQL>
我们看看同城备库的standby redo log。
SQL> /
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- ------------ ---------
10 1538750300 1 60 52428800 512 4608 YES ACTIVE 1153939 17-JUN-19 1153948 17-JUN-19 1153948 17-JUN-19
11 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
12 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
13 UNASSIGNED 0 0 52428800 512 0 YES UNASSIGNED
standby redo log马上从GROUP#=11的日志组切换到了GROUP#=10的日志组。
异地备库的归档，我们也查看下。
REGISTR CREATOR THREAD# APPLIED SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# COMPLETIO
------- ------- ---------- --------- ---------- ------------- ------------ ---------
RFS LGWR 1 YES 56 1143584 1143593 17-JUN-19
RFS LGWR 1 YES 57 1143593 1143672 17-JUN-19
RFS LGWR 1 YES 58 1143672 1143681 17-JUN-19
RFS LGWR 1 YES 59 1143681 1153939 17-JUN-19
48 rows selected.
SQL>
最新的归档日志是59号，而且已经应用于数据的修改了。
最后我们再看看归档目标整体的一个状态。
SQL> select dest_name,status,type,database_mode,protection_mode,destination,standby_logfile_count,standby_logfile_active,srl from v$archive_dest_status where dest_id in (1,2,3);
DEST_NAME STATUS TYPE DATABASE_MODE PROTECTION_MODE DESTINATION STANDBY_LOGFILE_COUNT STANDBY_LOGFILE_ACTIVE SRL
-------------------- --------- -------------- --------------- -------------------- -------------------- --------------------- ---------------------- ---
LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN MAXIMUM PERFORMANCE 0 0 NO
LOG_ARCHIVE_DEST_2 VALID PHYSICAL OPEN_READ-ONLY MAXIMUM PERFORMANCE chengdu 0 0 NO
LOG_ARCHIVE_DEST_3 VALID PHYSICAL OPEN_READ-ONLY MAXIMUM AVAILABILITY beijing_2 4 1 YES
SQL>
至此。我们搭建了一个两地三中心的这样一个简单模拟的数据库容灾系统。大家可以试试。
我们下一文进行一个灾备的演练的配置和实验。
