oracle 12C ADG Far Sync 配置实验

概述：11G及之前的ADG，redo日常传输是主备库之间直接进行传输，对应的配置多少都会对主库有影响；到了12C版本ADG中一个重要功能Active Data Guard Far Sync很好的方案解决了上述的问题；Far Sync的实现功能是：通过在距离主库(Primary Database)相对较近的地点配置Far Sync实例，主库(Primary Database) 同步(synchronous)传输redo到Far Sync实例，然后Far Sync实例再将redo异步(asynchronous)传输到终端备库(Standby Database)。这样既可以保证零数据丢失又可以降低主库压力。Far Sync实例只有密码文件，init参数文件和控制文件，而没有数据文件。 如果redo 传输采用Maximum Availability模式，我们可以在距离生产中心(Primary Database)相对较近的地点配置Far Sync实例，主库(Primary Database)同步(synchronous)传输redo到Far Sync实例，保证零数据丢失（zero data loss），同时主库和Far Sync距离较近，网络延时很小，因此对主库性能影响很小。然后Far Sync实例再将redo异步(asynchronous)发送到终端备库(Standby Database)。 如果redo 传输采用Maximum Performance模式，我们可以在距离生产中心(Primary Database)相对较近的地点配置Far Sync实例，主库(Primary Database) 异步传输redo到Far Sync实例，然后Far Sync实例再负责传输redo到其他多个终端备库(Standby Database)。这样可以减少主库向多个终端备库(Standby Database)传输redo的压力（offload）。 Far Sync配置对于Data Guard 角色转换(role transitions)是透明的，即switchover/failover命令方式与12c之前相同。参考文档：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dgbkr/examples-using-data-guard-broker-DGMGRL-utility.html#GUID-82319941-58E8-4672-8609-7CC496D3DC29

实验：这里使用的是DG Broker进行配置

首先我们按照11G ADG按照方式搭建好ADG：

DGMGRL> SHOW CONFIGURATION;
Configuration - DRSolution
  Protection Mode: MaxAvailability
  Members:
  CUBE1 - Primary database
    CUBE2 - Physical standby database 
Fast-Start Failover: DISABLED

1、Far Sync 实例搭建：

按照数据库软件，不建库；

从主库生成参数文件：

create pfile from spfile;

将参数文件和密码文件拷贝至far sync实例的对应目录：

[oracle@jakki1 dbs]$ scp initCUBE.ora orapwCUBE far1:/u01/app/oracle/product/11.2.0/db_1/dbs/
oracle@far1's password: 
initCUBE.ora                                                                                     100% 1448     1.4KB/s   00:00    
orapwCUBE                                                                                        100% 3584     3.5KB/s   00:00   
Configuration Status:

SUCCESS   (status updated 4 seconds ago)

修改参数文件:

[oracle@far1 dbs]$ cat initCUBE.ora 
*.audit_file_dest='/u01/app/oracle/admin/CUBE/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/CUBE/control01.ctl','/u01/app/oracle/fast_recovery_area/CUBE/control02.ctl'
*.db_block_size=8192
*.db_name='CUBE'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'
*.db_recovery_file_dest_size=5g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CUBEXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_CUBE'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=689m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=FAR1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(CUBE1,CUBE2,FAR1)'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER='CUBE1'

STANDBY_FILE_MANAGEMENT=auto

log_file_name_convert='/u01/app/oracle/oradata/CUBE/','/u01/app/oracle/oradata/CUBE/'

创建对应的目录：

[oracle@jakki2 dbs]$ mkdir -p /u01/app/oracle/oradata/CUBE2/
[oracle@jakki2 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/
[oracle@jakki2 dbs]$ mkdir -p /u01/app/oracle/oradata/CUBE/
[oracle@jakki2 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/CUBE/

[oracle@jakki2 dbs]$ mkdir -p /u01/app/oracle/admin/CUBE/adump 

将Far Sync实例启动到nomount状态并创建spfile文件：

startup nomount；

create spfile from pfile;

startup mount force;

Far Sync主机配置hosts文件及配置监听：

[oracle@far1 admin]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.0.2.10  jakki1
192.0.2.11  jakki2
192.0.2.12  far1

[oracle@far1 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = far1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_listener=
(SID_LIST=
 (SID_DESC=
  (GLOBAL_DBNAME=FAR1)
  (SID_NAME=CUBE)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1))
 (SID_DESC=
  (GLOBAL_DBNAME=FAR1_DGMGRL)
  (SID_NAME=CUBE)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1))
)
[oracle@far1 admin]$ cat tnsnames.ora 
CUBE1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jakki1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CUBE1)
    )
  )

CUBE2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jakki2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CUBE2)
      (UR=A)
    )
  )

FAR1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = far1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FAR1)
      (UR=A)
    )

  )

测试各个节点登入都是正常的：

[oracle@far1 admin]$ lsnrctl  status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 08-JUN-2018 10:30:39
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=far1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                08-JUN-2018 09:13:05
Uptime                    0 days 1 hr. 17 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/far1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=far1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "FAR1" has 1 instance(s).
  Instance "CUBE", status UNKNOWN, has 1 handler(s) for this service...
Service "FAR1_DGMGRL" has 1 instance(s).
  Instance "CUBE", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

SYS@AS SYSDBA@CUBE> conn sys/oracle@cube1 as sysdba
Connected.
SYS@AS SYSDBA@cube1> conn sys/oracle@cube2 as sysdba
Connected.
SYS@AS SYSDBA@cube2>  conn sys/oracle@far1 as sysdba

Connected.

主库生成far sync控制文件：

SYS@AS SYSDBA@CUBE> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/tmp/controlfs01.ctl';

将主库生成的/tmp/controlfs01.ctl'拷贝至far sync实例的对应目录并将far sync实例启动到mount状态：

[oracle@jakki1 tmp]$ scp controlfs01.ctl far1:/tmp/
[oracle@far1 tmp]$ cp controlfs01.ctl  /u01/app/oracle/oradata/CUBE/control01.ctl

[oracle@far1 tmp]$ cp controlfs01.ctl /u01/app/oracle/fast_recovery_area/CUBE/control02.ctl

alter database mount;

2、将Far Sync实例加入DG Broker进行管理：

far sync 开启dg broker

SYS@AS SYSDBA@CUBE> alter system set dg_broker_start=true;

将Far Sync加入

DGMGRL> ADD FAR_SYNC 'FAR1'  AS CONNECT IDENTIFIER IS FAR1;
far sync instance "FAR1" added
DGMGRL> SHOW CONFIGURATION;
Configuration - DRSolution
  Protection Mode: MaxAvailability
  Members:
  CUBE1 - Primary database
    CUBE2 - Physical standby database 
    FAR1  - Far sync instance (disabled)
Fast-Start Failover: DISABLED
Configuration Status:

SUCCESS   (status updated 18 seconds ago)

DGMGRL> ENABLE FAR_SYNC 'FAR1';

Enabled.

DGMGRL> SHOW CONFIGURATION;
Configuration - DRSolution
  Protection Mode: MaxAvailability
  Members:
  CUBE1 - Primary database
    CUBE2 - Physical standby database 
    FAR1  - Far sync instance 
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 18 seconds ago)
DGMGRL> SHOW FAR_SYNC VERBOSE 'FAR1';
Far Sync Instance - FAR1
  Transport Lag:      (unknown)
  Instance(s):
    CUBE
  Properties:
    DGConnectIdentifier             = 'far1'
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    LogFileNameConvert              = '/u01/app/oracle/oradata/CUBE/, /u01/app/oracle/oradata/CUBE/'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Far Sync Instance Status:

SUCCESS

2、配置redo传输先以同步的方式传输到far sync，再由far sync 传输至备端：

 DGMGRL> disABLE FAR_SYNC 'FAR1';
Disabled.
DGMGRL> DGMGRL> SHOW CONFIGURATION;
Configuration - DRSolution
  Protection Mode: MaxPerformance
  Members:
  CUBE1 - Primary database
    CUBE2 - Physical standby database 
    FAR1  - Far sync instance (disabled)

Fast-Start Failover: DISABLED

EDIT DATABASE 'CUBE1' SET PROPERTY RedoRoutes='(LOCAL : FAR1 SYNC)';

DGMGRL> EDIT FAR_SYNC 'FAR1' SET PROPERTY RedoRoutes='(CUBE1 : CUBE2 ASYNC)';
Property "redoroutes" updated
DGMGRL> enable far_sync 'FAR1';
Enabled.
DGMGRL> SHOW CONFIGURATION;
Configuration - DRSolution
  Protection Mode: MaxPerformance
  Members:
  CUBE1 - Primary database
    FAR1  - Far sync instance 
      CUBE2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 16 seconds ago)
修改保护模式为最大可用模式：
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.
DGMGRL> SHOW CONFIGURATION;
Configuration - DRSolution
  Protection Mode: MaxAvailability
  Members:
  CUBE1 - Primary database
    FAR1  - Far sync instance 
      CUBE2 - Physical standby database 
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 52 seconds ago)
查看当主库切换为CUBE2时候的状态：
DGMGRL> SHOW CONFIGURATION WHEN PRIMARY IS 'CUBE2';
Configuration when CUBE2 is primary - DRSolution
  Members:
  CUBE2 - Primary database
    CUBE1 - Physical standby database 
    FAR1  - Far sync instance 
DGMGRL> 

到此配置就算结束了。