Oracle 11g R2 Data Guard 容灾系统构建S

作者: chimpansee | 来源:发表于2019-06-17 01:32 被阅读0次

当下，主流的灾备架构是两地三中心，数据中心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>

至此。我们搭建了一个两地三中心的这样一个简单模拟的数据库容灾系统。大家可以试试。

Oracle 11g R2 Data Guard 容灾系统构建S

目录

两地三中心

同城备份系统构建

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读