代码改变世界

【Oracle】Oracle 12C -- Far Sync

2022-06-09 13:38  abce  阅读(465)  评论(0)  编辑  收藏  举报

 

Far Sync是Oracle Data Guard的 Oracle 12c新特性。此功能旨在解决当你维护一个地理上远离主数据库的备用数据库时由网络延迟引起的性能问题。在这种情况下，你有时必须在性能和数据丢失之间做出妥协。Far Sync功能为你提供了两者。

Far Sync是如何工作的

原理相当简单，Far Sync实例从主数据库同步接收数据，然后将其异步转发到多达29个远程目标。

 

far sync数据库不是标准数据库，它只包含一个特定的控制文件、一个spfile和standby redo日志。此数据库必须放置在主数据库附近，以保证同步复制期间的最佳网络延迟。但是请注意，不要将此数据库与主数据库放在同一地理位置，因为如果你的主数据库遇到地理灾难，你的far sync也会受到影响，并且可能会丢失一些数据。

如果主数据库发生中断，将启用标准故障转移过程，并且far sync实例保证在故障转移期间不会丢失任何数据。

配置测试

主机：三个虚拟机，内存为2GB

OS：OEL6.4

Oracle版本：oracle 12.1.0企业版

SERVER NAMEROLE
SVR-ORA-03 PRIMARY SERVER
SVR-ORA-04 FAR SYNC SERVER
SVR-ORA-05 STANDBY SERVER

Standby库创建

使用以下脚本创建standby库(SVR-ORA-05)

rman target sys/******@DB12CE_PRIMARY auxiliary sys/*******@DB12CE_STANDBY
run 
{
    DUPLICATE TARGET DATABASE
    FOR STANDBY
    FROM ACTIVE DATABASE
    DORECOVER
    NOFILENAMECHECK;
}

增加standby redologs文件

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (
    '/u01/app/oracle/oradata/DB12CE/onlinelog/stdbyrdo41.log',
    '/u01/app/oracle/fast_recovery_area/DB12CE/onlinelog/stdbyrdo42.log'
  ) SIZE 50M BLOCKSIZE 512;
​
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (
    '/u01/app/oracle/oradata/DB12CE/onlinelog/stdbyrdo51.log',
    '/u01/app/oracle/fast_recovery_area/DB12CE/onlinelog/stdbyrdo52.log'
  ) SIZE 50M BLOCKSIZE 512;
​
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (
    '/u01/app/oracle/oradata/DB12CE/onlinelog/stdbyrdo61.log',
    '/u01/app/oracle/fast_recovery_area/DB12CE/onlinelog/stdbyrdo62.log'
  ) SIZE 50M BLOCKSIZE 512;

standby准备结束。接下来就是准备far sync实例了。

far sync创建

我们需要创建一个指定的控制文件和pfile。在主库上创建。

首先，创建为far sync创建控制文件：

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/u01/app/oracle/control01.ctl';
​
Database altered.

然后，为far sync实例创建一个pfile：

SQL> create pfile='/u01/app/oracle/initDB12CE.ora' from spfile;
​
File created.

创建结束后，需要将这些文件拷贝到far sync服务器：

[oracle@svr-ora-03 oracle]$ scp control01.ctl initDB12CE.ora svr-ora-04:/u01/app/oracle
oracle@svr-ora-04's password:
control01.ctl                                                             100% 9840KB   9.6MB/s   00:00
initDB12CE.ora                                                            100% 1078     1.1KB/s   00:00

 

在far sync服务器上，编辑参数文件中的control_files参数：

*.control_files='/u01/app/oracle/oradata/DB12CE/controlfile/control01.ctl'

 

创建相对应的文件目录，并将控制文件、参数文件拷贝到指定的位置：

[oracle@svr-ora-04 oracle]$ mkdir -p /u01/app/oracle/oradata/DB12CE/controlfile/
[oracle@svr-ora-04 oracle]$ mkdir -p /u01/app/oracle/oradata/DB12CE/onlinelog/
[oracle@svr-ora-04 oracle]$ mv control01.ctl /u01/app/oracle/oradata/DB12CE/controlfile/
[oracle@svr-ora-04 oracle]$ mv initDB12CE.ora $ORACLE_HOME/dbs

 

现在需要创建一个spfile，将数据库mount起来，创建standby redolog：

SQL> create spfile from pfile;
​
File created.
​
SQL> startup mount
ORACLE instance started.
​
Total System Global Area  521936896 bytes
Fixed Size                  2290264 bytes
Variable Size             264244648 bytes
Database Buffers          251658240 bytes
Redo Buffers                3743744 bytes
Database mounted.
​
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (
    '/u01/app/oracle/oradata/DB12CE/onlinelog/stdbyrdo41.log',
    '/u01/app/oracle/fast_recovery_area/DB12CE/onlinelog/stdbyrdo42.log'
  ) SIZE 50M BLOCKSIZE 512;
​
Database altered
​
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (
    '/u01/app/oracle/oradata/DB12CE/onlinelog/stdbyrdo51.log',
    '/u01/app/oracle/fast_recovery_area/DB12CE/onlinelog/stdbyrdo52.log'
  ) SIZE 50M BLOCKSIZE 512;
​
Database altered
​
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (
    '/u01/app/oracle/oradata/DB12CE/onlinelog/stdbyrdo61.log',
    '/u01/app/oracle/fast_recovery_area/DB12CE/onlinelog/stdbyrdo62.log'
  ) SIZE 50M BLOCKSIZE 512;
​
Database altered
​
SQL> select GROUP#,MEMBER,TYPE from v$logfile;
​
   GROUP# MEMBER                                                                           TYPE
---------- -------------------------------------------------------------------------------- -------
         3 /u01/app/oracle/oradata/DB12CE/onlinelog/o1_mf_3_9jsrb6dz_.log                   ONLINE
         3 /u01/app/oracle/fast_recovery_area/DB12CE/onlinelog/o1_mf_3_9jsrb6n4_.log        ONLINE
         2 /u01/app/oracle/oradata/DB12CE/onlinelog/o1_mf_2_9jsrb4qo_.log                   ONLINE
         2 /u01/app/oracle/fast_recovery_area/DB12CE/onlinelog/o1_mf_2_9jsrb4vc_.log        ONLINE
         1 /u01/app/oracle/oradata/DB12CE/onlinelog/o1_mf_1_9jsrb34c_.log                   ONLINE
         1 /u01/app/oracle/fast_recovery_area/DB12CE/onlinelog/o1_mf_1_9jsrb3br_.log        ONLINE
         4 /u01/app/oracle/oradata/DB12CE_FS/onlinelog/stdbyrdo41.log                       STANDBY
         4 /u01/app/oracle/fast_recovery_area/DB12CE_FS/onlinelog/stdbyrdo42.log            STANDBY
         5 /u01/app/oracle/oradata/DB12CE_FS/onlinelog/stdbyrdo51.log                       STANDBY
         5 /u01/app/oracle/fast_recovery_area/DB12CE_FS/onlinelog/stdbyrdo52.log            STANDBY
         6 /u01/app/oracle/oradata/DB12CE_FS/onlinelog/stdbyrdo61.log                       STANDBY
         6 /u01/app/oracle/fast_recovery_area/DB12CE_FS/onlinelog/stdbyrdo62.log            STANDBY
​
SQL>

现在可以通过Data Guard Broker配置far sync实例了。

DataGuard配置

首先要保证所有的节点都配置了监听、tnsnames.ora。这是我三个实例的tnsnames.ora

DB12CE_PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = svr-ora-03)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DB12CE)
    )
  )

DB12CE_FS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = svr-ora-04)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DB12CE)
    )
  )

DB12CE_STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = svr-ora-05)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DB12CE)
    )
  )

我修改了三个节点的db_unique_name。

主库：

SQL> alter system set db_unique_name='DB12C_PRIM' scope=spfile;

System altered.

far sync：

SQL> alter system set db_unique_name='DB12C_FS' scope=spfile;

System altered.

standby库：

SQL> alter system set db_unique_name='DB12C_STBY' scope=spfile;

System altered.

重启数据库。

使用以下命令开启dg_broker：

SQL>  alter system set dg_broker_start=true;

System altered.

在主库上连接到dgmgrl，创建配置：

DGMGRL> connect target /
Password:
Connected as SYSDG.

DGMGRL> CREATE CONFIGURATION dgb_db12c AS PRIMARY DATABASE IS DB12C_PRIM CONNECT IDENTIFIER IS DB12CE_PRIMARY;

Configuration "dgb_db12c" created with primary database "db12c_prim"

DGMGRL> ADD DATABASE DB12C_STBY AS CONNECT IDENTIFIER IS DB12CE_STANDBY MAINTAINED AS PHYSICAL;

Database "db12c_stby" added

DGMGRL> ADD FAR_SYNC DB12C_FS AS CONNECT IDENTIFIER IS DB12CE_FS;
far sync instance "db12c_fs" added

DGMGRL>

现在开启配置：

DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration verbose

Configuration - dgb_db12c

  Protection Mode: MaxPerformance
  Databases:
  db12c_prim - Primary database
    db12c_stby - Physical standby database
    db12c_fs   - Far Sync (inactive)

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

配置是开启了，但是far sync还没有激活，还需要配置redo和激活far sync：

DGMGRL> edit database db12c_prim set property redoroutes='(local:db12c_fs sync)';
Property "redoroutes" updated
DGMGRL> edit far_sync db12c_fs set property redoroutes='(db12c_prim:db12c_stby)';
Property "redoroutes" updated
DGMGRL> enable far_sync db12c_fs;
Enabled.
DGMGRL> show configuration verbose

Configuration - dgb_db12c

  Protection Mode: MaxPerformance
  Databases:
  db12c_prim - Primary database
    db12c_fs   - Far Sync
      db12c_stby - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

至此，far sync就被启用了，我们来修改redo配置，来表示主库应该以同步的模式将redo转发给far sync实例。

我们编辑far sync配置，表示会从主库接受redo并转发给standby。

 

现在，得益于far sync 实例，我们可以将保护模式从maxperformance修改成maxavailability。

DGMGRL> edit configuration set protection mode as MaxAvailability;
Succeeded.
DGMGRL> show configuration verbose

Configuration - dgb_db12c

  Protection Mode: MaxAvailability
  Databases:
  db12c_prim - Primary database
    db12c_fs   - Far Sync
      db12c_stby - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

ok，配置好了。

 

以下是每个数据库的参数：

DATABASELOG_ARCHIVE_DEST_1LOG_ARCHIVE_DEST_2
PRIMARY location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES,ALL_ROLES) service=”db12ce_fs”, SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name=”db12c_fs” net_timeout=30, valid_for=(online_logfile,all_roles)
FAR SYNC location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES,ALL_ROLES) service=”db12ce_standby”, ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name=”db12c_stby” net_timeout=30, valid_for=(standby_logfile,all_roles)
STANDBY location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES,ALL_ROLES)  
 

 

 

https://www.dba-scripts.com/articles/dataguard-standby/data-guard-far-sync/