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

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