Oracle 12c far sync active dataguard 架构使用DGMGRL管理配置

   最近一直在学习Oracle 12C 数据库，所以进行了一些文档梳理。 这里我主要介绍如何在Oracle 12c far sync active dataguard 架构上配置DG Broker

一：查看数据库版本：

SQL> col BANNER for a90
SQL> select * from v$version;

BANNER                                                 CON_ID
------------------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production                0
PL/SQL Release 12.1.0.2.0 - Production                                  0
CORE    12.1.0.2.0  Production                                      0
TNS for Linux: Version 12.1.0.2.0 - Production                              0
NLSRTL Version 12.1.0.2.0 - Production                                  0

二：去除DG redo log 同步参数设置：

primary：
alter system set log_archive_dest_2='';

alter system set log_archive_dest_3='';

fay sync：
alter system set log_archive_dest_2='';

standby：
alter system set log_archive_dest_2='';
alter system set log_archive_dest_3='';

注：在12cR1以前版本中，配置dg broker是不需要去除。但是在12cR1以上版本中,如果不去除已配置了dg redo log同步参数，那么在创建dg broker的时候会出现如下错误：

DGMGRL> create configuration dg12c_broker as primary database is dg12cpy connect identifier is xxxx;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Failed.

错误说明：

dg12c@dgpy /home/oracle$ oerr ora 16649
16649, 0000, "possible failover to another database prevents this database from being opened"
// *Cause: An attempt to open the primary database was made either after
// a failover occurred, or when it was likely to have occurred as
// the result of the primary being isolated from the fast-start
// failover target standby database and from the fast-start failover
// observer.
// *Action: Check if a failover did occur. If fast-start failover is enabled,
// and a failover did not occur, ensure that connectivity exists
// between the primary database and either the observer or the
// target standby database. Then, try opening the database again.

三：修改dg broker初始化参数：
分别在主库，far sync实例和备库上执行:

SQL> alter system set dg_broker_start=true scope=both;

四：配置dg broker：
 
primary:
 
创建broker配置:

DGMGRL> create configuration dg12c_broker as primary database is dg12cpy connect identifier is dg12cpy;
Configuration "dg12c_broker" created with primary database "dg12cpy"

添加备用数据库到配置中：

DGMGRL> add database dg12csd as connect identifier is dg12csd maintained as physical;
Database "dg12csd" added

启动配置：

DGMGRL> enable configuration
Enabled.

查看配置：

DGMGRL> show configuration
Configuration - dg12c_broker
  Protection Mode: MaxPerformance
  Members:
  dg12cpy - Primary database
    dg12csd - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 20 seconds ago)

DGMGRL> show database verbose 'dg12csd'
Database - dg12csd
  Role: PHYSICAL STANDBY
  Intended State: APPLY-ON
  Transport Lag: 0 seconds (computed 1 second ago)
  Apply Lag: 0 seconds (computed 1 second ago)
  Average Apply Rate: 20.00 KByte/s
  Active Apply Rate: 1.05 MByte/s
  Maximum Apply Rate: 3.18 MByte/s
  Real Time Query: ON
  Instance(s):
    dg12c
  Properties:
    DGConnectIdentifier = 'dg12csd'
    ObserverConnectIdentifier = ''
    LogXptMode = 'ASYNC'
    RedoRoutes = ''
    DelayMins = '0'
    Binding = 'optional'
    MaxFailure = '0'
    MaxConnections = '1'
    ReopenSecs = '300'
    NetTimeout = '30'
    RedoCompression = 'DISABLE'
    LogShipping = 'ON'
    PreferredApplyInstance = ''
    ApplyInstanceTimeout = '0'
    ApplyLagThreshold = '0'
    TransportLagThreshold = '0'
    TransportDisconnectedThreshold = '30'
    ApplyParallel = 'AUTO'
    StandbyFileManagement = 'AUTO'
    ArchiveLagTarget = '0'
    LogArchiveMaxProcesses = '4'
    LogArchiveMinSucceedDest = '1'
    DbFileNameConvert = ''
    LogFileNameConvert = ''
    FastStartFailoverTarget = ''
    InconsistentProperties = '(monitor)'
    InconsistentLogXptProps = '(monitor)'
    SendQEntries = '(monitor)'
    LogXptStatus = '(monitor)'
    RecvQEntries = '(monitor)'
    StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dgsd)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dg12csd_DGMGRL)(INSTANCE_NAME=dg12c)(SERVER=DEDICATED)))'
    StandbyArchiveLocation = '/opt/oracle/oradata/dg12c/archive'
    AlternateLocation = ''
    LogArchiveTrace = '0'
    LogArchiveFormat = 'arch_%r_%t_%s.dbf'
    TopWaitEvents = '(monitor)'
Database Status:
SUCCESS

启动standby 配置：

DGMGRL> ENABLE DATABASE 'dg12csd'
Enabled.

DGMGRL> show database 'dg12csd'
Database - dg12csd
  Role: PHYSICAL STANDBY
  Intended State: APPLY-ON
  Transport Lag: 0 seconds (computed 0 seconds ago)
  Apply Lag: 0 seconds (computed 0 seconds ago)
  Average Apply Rate: 20.00 KByte/s
  Real Time Query: ON
  Instance(s):
    dg12c
Database Status:
SUCCESS

添加far sync 配置：

DGMGRL> ENABLE FAR_SYNC dg12cfy;
Enabled.

DGMGRL> show configuration;
Configuration - dg12c_broker
  Protection Mode: MaxPerformance
  Members:
  dg12cpy - Primary database
    dg12csd - Physical standby database
    dg12cfy - Far sync instance
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 7 seconds ago)

在 Broker 中设置 Redo 传输:

DGMGRL> EDIT DATABASE 'dg12cpy' SET PROPERTY 'RedoRoutes'='(LOCAL : dg12cfy SYNC)';
Property "RedoRoutes" updated

DGMGRL> EDIT FAR_SYNC 'dg12cfy' SET PROPERTY 'RedoRoutes'='(dg12cpy : dg12csd ASYNC )';
Property "RedoRoutes" updated

查看配置：

DGMGRL> SHOW CONFIGURATION;
Configuration - dg12c_broker
  Protection Mode: MaxPerformance
  Members:
  dg12cpy - Primary database
    dg12cfy - Far sync instance
      dg12csd - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 23 seconds ago)

修改高可用模式：

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> SHOW CONFIGURATION;
Configuration - dg12c_broker
  Protection Mode: MaxAvailability
  Members:
  dg12cpy - Primary database
    dg12cfy - Far sync instance
      dg12csd - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 30 seconds ago)

DGMGRL> show configuration when primary is 'dg12cpy';
Configuration when dg12cpy is primary - dg12c_broker
  Members:
  dg12cpy - Primary database
    dg12cfy - Far sync instance
      dg12csd - Physical standby database

五：验证同步：

PRIMARY:

SQL> alter session set container=pdb1;
Session altered.

SQL> create table t2 as select * from t1 where 1=0;
Table created.

SQL> insert into t2(id,name) values (1,'dfrt');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t2;
    ID NAME
---------- ------------------------------------------------------------
     1 dfrt

STANDBY:

SQL> alter session set container=pdb1;
Session altered.

SQL> select * from t2;
no rows selected

SQL> select * from t2;
    ID NAME
---------- ------------------------------------------------------------
     1 dfrt