程式師世界 >> 編程語言 >> JAVA編程 >> JAVA綜合教程 >> Oracle 12c far sync active dataguard 架構使用DGMGRL管理配置

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

編輯：JAVA綜合教程

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