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: orcldb_unique_name: orclpnet 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.oratns_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 ModeAutomatic archival DisabledArchive destination /u01/app/oracle/product/12.2.0/db_1/dbs/archOldest online log sequence 1Current log sequence 2SQL> 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 MOUNTEDSQL> 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 0SQL> 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 bytesFixed Size 8794072 bytesVariable Size 553648168 bytesDatabase Buffers 1308622848 bytesRedo Buffers 7983104 bytesSQL> show parameter dg_broker_config_fileNAME TYPE VALUE------------------------------------ ----------- ------------------------------dg_broker_config_file1 string /u01/app/oracle/product/12.2.0 /db_1/dbs/dr1orclp.datdg_broker_config_file2 string /u01/app/oracle/product/12.2.0 /db_1/dbs/dr2orclp.datSQL> 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=true
oradb-node2 192.168.56.102 physical standby
安装好了12.2.0.1 的软件，不创建数据库,用duplicate命令从master拉过来
备库name相关
db_name: orcldb_unique_name: orcls1net 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.oratns_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/adumpmkdir -p $ORACLE_BASE/archivelogmkdir -p $ORACLE_BASE/auditmkdir -p $ORACLE_BASE/oradata/orclmkdir -p $ORACLE_BASE/oradata/pdbseedmkdir -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 bytesFixed Size 8794072 bytesVariable Size 553648168 bytesDatabase Buffers 1308622848 bytesRedo Buffers 7983104 bytesSQL> show parameter dg_broker_config_fileNAME TYPE VALUE------------------------------------ ----------- ------------------------------dg_broker_config_file1 string /u01/app/oracle/product/12.2.0 /db_1/dbs/dr1orclp.datdg_broker_config_file2 string /u01/app/oracle/product/12.2.0 /db_1/dbs/dr2orclp.datSQL> 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$ dgmgrlDGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat Jul 7 18:44:38 2018Copyright (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 configurationSyntax: CREATE CONFIGURATION[AS] PRIMARY DATABASE IS CONNECT IDENTIFIER IS ;DGMGRL> create configuration dgconf as primary database is orclp connect identifier is tns_orclp;Configuration "dgconf" created with primary database "orclp"DGMGRL>
增加备库
DGMGRL> help addAdds a member to the broker configurationSyntax: ADD { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC } 查看
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: DISABLEDConfiguration 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: DISABLEDConfiguration Status:SUCCESS
show 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.logDatabase 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.logDatabase Status:SUCCESS
参考：