os: centos 7.4
database：12.2.0.1 + dbf
手动配置dataguard比较繁琐，发生故障切换是需要人工介入。broker就是简化而诞生的。
oradb-node1 192.168.56.101 master
安装好了12.2.0.1 的软件，并创建了数据库
dataguard 所有节点的 db_name 是相同的，通过db_unique_name来区分彼此。
主库name相关
db_name: orcl
db_unique_name: orclp
net service name: tns_orclp修改 db_unique_name
SQL> alter system set db_unique_name='orclp' scope=spfile;
System altered.修改 net service name
$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = rac01)
)
(SID_DESC =
(GLOBAL_DBNAME = orclp_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = rac01)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)$ vi tnsnames.ora
tns_orclp =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
tns_orcls1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
) 主库启用 force logging
SQL> alter database force logging;
Database altered.主库启用 archivelog
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 2
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;执行 open pdb,确保处于 read write
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 RAC01PDB MOUNTED
SQL> alter pluggable database RAC01PDB open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 RAC01PDB READ WRITE NO
主库创建 standby redo logfile
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1 1 4 209715200 512 1 NO CURRENT 1572343 2018-07-07 14:47:20 1.8447E+19 0
2 1 2 209715200 512 1 YES INACTIVE 1429048 2018-07-07 11:45:32 1472300 2018-07-07 14:44:53 0
3 1 3 209715200 512 1 YES INACTIVE 1472300 2018-07-07 14:44:53 1572343 2018-07-07 14:47:20 0
SQL> col MEMBER format a40;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------- --- ----------
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO 0最少添加n+1个standby redo logfile，完全可以比n+1多，如下面
alter database add standby logfile '/u01/app/oracle/oradata/standby_redo01.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/standby_redo02.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/standby_redo03.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/standby_redo04.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/standby_redo05.log' size 200M;主库修改dataguard broker 参数
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1879048192 bytes
Fixed Size 8794072 bytes
Variable Size 553648168 bytes
Database Buffers 1308622848 bytes
Redo Buffers 7983104 bytes
SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.2.0
/db_1/dbs/dr1orclp.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.2.0
/db_1/dbs/dr2orclp.dat
SQL> alter system set dg_broker_start=true;
System altered.主库的一些参数
*.db_name='orcl'
*.db_unique_name='orclp'
*.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orclp'
*.log_archive_dest_state_1=enable
*.standby_file_management='auto'
*.db_file_name_convert='/u01/app/oracle/','/u01/app/oracle/'
*.log_file_name_convert='/u01/app/oracle/','/u01/app/oracle/'
*.remote_login_passwordfile='EXCLUSIVE'
*.dg_broker_start=trueoradb-node2 192.168.56.102 physical standby
安装好了12.2.0.1 的软件，不创建数据库,用duplicate命令从master拉过来
备库name相关
db_name: orcl
db_unique_name: orcls1
net service name: tns_orcls1修改 net service name
$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = rac01)
)
(SID_DESC =
(GLOBAL_DBNAME = orclp_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)
(SID_NAME = rac01)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)$ vi tnsnames.ora
tns_orclp =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
tns_orcls1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
上面的 listener.ora、tnsnames.ora 需要和master 保持一致。
备库目录相关
参考主库创建必要的目录
mkdir -p $ORACLE_BASE/admin/orcl/adump
mkdir -p $ORACLE_BASE/archivelog
mkdir -p $ORACLE_BASE/audit
mkdir -p $ORACLE_BASE/oradata/orcl
mkdir -p $ORACLE_BASE/oradata/pdbseed
mkdir -p $ORACLE_BASE/oradata/rac01pdb备库拷贝master 的password file
$scp ./orapwrac01 oracle@192.168.56.102:/u01/app/oracle/product/12.2.0/db_1/dbs备库修改dataguard broker 参数
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1879048192 bytes
Fixed Size 8794072 bytes
Variable Size 553648168 bytes
Database Buffers 1308622848 bytes
Redo Buffers 7983104 bytes
SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.2.0
/db_1/dbs/dr1orclp.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.2.0
/db_1/dbs/dr2orclp.dat
SQL> alter system set dg_broker_start=true;
System altered.备库的一些参数
*.db_name='orcl'
*.db_unique_name='orcls1'
*.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcls1'
*.log_archive_dest_state_1=enable
*.standby_file_management='auto'
*.db_file_name_convert='/u01/app/oracle/','/u01/app/oracle/'
*.log_file_name_convert='/u01/app/oracle/','/u01/app/oracle/'
*.remote_login_passwordfile='EXCLUSIVE'
*.dg_broker_start=true参照上一篇博文配置到duplicate database完成(force logging，且不应用real-time apply)，然后就可以配置 dg broker了。
dataguard broker 设置
在主库、备库或者另外一台独立的机器都可以设置。
本次操作是在备库上。
创建
$ which dgmgrl
/u01/app/oracle/product/12.2.0/db_1/bin/dgmgrl
$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat Jul 7 18:44:38 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL> connect sys/oracleoracle@tns_orclp
Connected to "orclp"
Connected as SYSDBA.
DGMGRL> help create;
Creates a broker configuration
Syntax:
CREATE CONFIGURATION <configuration name> [AS]
PRIMARY DATABASE IS <database name>
CONNECT IDENTIFIER IS <connect identifier>;
DGMGRL> create configuration dgconf as primary database is orclp connect identifier is tns_orclp;
Configuration "dgconf" created with primary database "orclp"
DGMGRL>
增加备库
DGMGRL> help add
Adds a member to the broker configuration
Syntax:
ADD { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC } <object name>
[AS CONNECT IDENTIFIER IS <connect identifier>];
DGMGRL>
DGMGRL> add database orcls1 as connect identifier is tns_orcls1;
Database "orcls1" added
DGMGRL> enable configuration
Enabled.查看
master 节点的参数修改
log_archive_dest_2 string service="tns_orcls1", ASYNC NO
AFFIRM delay=0 optional compre
ssion=disable max_failure=0 ma
x_connections=1 reopen=300 db_
unique_name="orcls1" net_timeo
ut=30, valid_for=(online_logfi
le,all_roles)
show configuration
DGMGRL> show configuration;
Configuration - dgconf
Protection Mode: MaxPerformance
Members:
orclp - Primary database
orcls1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 29 seconds ago)
DGMGRL> show configuration verbose;
Configuration - dgconf
Protection Mode: MaxPerformance
Members:
orclp - Primary database
orcls1 - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'orcl_CFG'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESSshow database verbose
DGMGRL> show database verbose orclp;
Database - orclp
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
rac01
Properties:
DGConnectIdentifier = 'tns_orclp'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = '/u01/app/oracle/, /u01/app/oracle/'
LogFileNameConvert = '/u01/app/oracle/, /u01/app/oracle/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradb-node1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclp_DGMGRL)(INSTANCE_NAME=rac01)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/archivelog'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/orclp/rac01/trace/alert_rac01.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/orclp/rac01/trace/drcrac01.log
Database Status:
SUCCESS
DGMGRL> show database verbose orcls1;
Database - orcls1
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: 0 Byte/s
Active Apply Rate: 0 Byte/s
Maximum Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
rac01
Properties:
DGConnectIdentifier = 'tns_orcls1'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = '/u01/app/oracle/, /u01/app/oracle/'
LogFileNameConvert = '/u01/app/oracle/, /u01/app/oracle/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradb-node2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcls1_DGMGRL)(INSTANCE_NAME=rac01)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/archivelog'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/orcls1/rac01/trace/alert_rac01.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcls1/rac01/trace/drcrac01.log
Database Status:
SUCCESS
参考：
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/high-availability.html