一、前言
前面我的文章列举了几种ADG常见的搭建方式，此处我以最佳的方式作为实践过程演示；架构为RAC到单机，通常这种架构大家用得比较多；
这里实践的案例ADG全程是broker进行管理，broker其实是ADG非常简单易用的工具，尽量我们将相关的工作交给Oracle本身管理是最好的。
比如broker本身自动守护mrp进程就很不错了。
二、主库参数环境配置
1、添加备库日志
查看日志组数
select thread#,group#,members,bytes/1024/1024/1024 from v$log order by thread#;
select thread#,group#,bytes/1024/1024/1024 from v$standby_log;
Note：这里我们查出来主库的redo日志组数为4组，所以我们standby log创建5组；因为官方建议是比主库多一组，没有硬性要求。
添加
alter database add standby logfile thread 1 group 13('+DG_DATA','+DG_ARCH') size 1024m;
alter database add standby logfile thread 1 group 14('+DG_DATA','+DG_ARCH') size 1024m;
alter database add standby logfile thread 1 group 15('+DG_DATA','+DG_ARCH') size 1024m;
alter database add standby logfile thread 1 group 16('+DG_DATA','+DG_ARCH') size 1024m;
alter database add standby logfile thread 1 group 17('+DG_DATA','+DG_ARCH') size 1024m;
alter database add standby logfile thread 2 group 18('+DG_DATA','+DG_ARCH') size 1024m;
alter database add standby logfile thread 2 group 19('+DG_DATA','+DG_ARCH') size 1024m;
alter database add standby logfile thread 2 group 20('+DG_DATA','+DG_ARCH') size 1024m;
alter database add standby logfile thread 2 group 21('+DG_DATA','+DG_ARCH') size 1024m;
alter database add standby logfile thread 2 group 22('+DG_DATA','+DG_ARCH') size 1024m;
Note：注意thread 数要跟主库对应上。
2、开启归档和强制日志
archive log list;
alter database archivelog;
Note：这里不详细展开
select force_logging from v$database;
alter database force logging;
3、主库参数配置
alter system set log_archive_config='DG_CONFIG=(rick,rickdg)';
select * from v$dataguard_config;
alter system set db_unique_name='rick'scope=spfile;
alter system set standby_file_management='AUTO';
alter system set db_file_name_convert='/data/oradata/rickdg/','+DG_DATA/RICK/DATAFILE/' scope=spfile;
alter system set log_file_name_convert='/data/oradata/rickdg/','+DG_DATA/RICK/ONLINELOG/' scope=spfile;
Note：备库为单机环境，所以转换参数要配好
三、备库参数环境配置
Note：备库参数这里看个人习惯，我习惯性拿主库的参数过来修改，出错率小
1、主库创建个pfile
create pfile='/home/oracle/pfile.bak' from spfile;
2、备库参数修改
*.audit_file_dest='/u01/app/oracle/admin/rickdg/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/data/oradata/rickdg/controlfile01.ctl'
*.db_block_size=8192
*.db_file_name_convert='+DG_DATA/RICK/DATAFILE/','/data/oradata/rickdg/','+DG_DATA/RICK/TEMPFILE/','/data/oradata/rickdg/'
*.db_files=4000
*.db_name='rick'
*.db_recovery_file_dest_size=536870912000
*.db_recovery_file_dest='/data/oradata/fast_recovery_area'
*.db_unique_name='rickdg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers=''
family:dw_helper.instance_mode='read-only'
*.local_listener='-oraagent-dummy-'
*.log_archive_config='DG_CONFIG=(rick,rickdg)'
*.log_file_name_convert='+DG_DATA/RICK/ONLINELOG/','/data/oradata/rickdg/','+DG_ARCH/RICK/ONLINELOG/','/data/oradata/rickdg/'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=2000
*.pga_aggregate_limit=12g
*.pga_aggregate_target=6g
*.processes=2000
*.remote_login_passwordfile='exclusive'
*.sga_max_size=10g
*.sga_target=10g
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Note：参数样例，供大家参考，注意很多细节改对，集群相关的参数要去掉
3、创建备库关键目录
mkdir -p /u01/app/oracle/admin/rickdg/adump
mkdir /data/oradata/rickdg
mkdir /data/oradata/fast_recovery_area
四、配置TNS网络
Note：主备库之间同步是走TCP网络，故要配好TNS；有条件的话，建议配置专门的网络，避免走业务网
1、配置TNS客户端信息
vi tnsnames.ora
RICK=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rick-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rick)
(FAILOVER_MODE =
(TYPE = SELECT)(METHOD = BASE)(RETIRES = 20)(DELAY = 15)
)
)
)
RICKDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rickdg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rickdg)
)
)
2、配置静态监听
主库
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rick)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = rick1)
)
(SID_DESC =
(GLOBAL_DBNAME = rick_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = rick1)
)
)
Note：两个节点配置好
重启下监听
srvctl stop lisenter
srvctl start lisenter
备库
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rickdg)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = rickdg)
)
(SID_DESC =
(GLOBAL_DBNAME = rickdg_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = rickdg)
)
)
重启下监听
lsnrctl start
lsnrctl stat
五、backup-based方式搭建备库
1、拷贝密码文件
主库
asmcmd
cp +DG_DATA/RICK/PASSWORD/pwdrick.282.1093287491 /home/grid
scp /home/grid/pwdrick.282.1093287491 oracle@rickdg:/u01/app/oracle/product/19.0.0/dbhome_1/dbs
备库
cd $ORACLE_HOME/dbs
mv pwdrick.282.1093287491 orapwrickdg
2、备库启动nomount
create spfile from pfile='/home/oracle/pfile.bak';
startup nomount
3、主库备份
创建备份目录
mkdir /home/oracle/backup
Note：此目录主备库要创建一致
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/home/oracle/backup/%U' plus archivelog;
backup format '/home/oracle/backup/%U' current controlfile ;
}
scp /home/oracle/backup/* rickdg:/home/oracle/backup
4、备库恢复还原
rman target sys/Oracle123@rick auxiliary sys/Oracle123
Note：注意要进行主备库连接，要利用主库的catalog信息进行恢复
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate AUXILIARY channel c3 type disk;
allocate AUXILIARY channel c4 type disk;
duplicate target database for standby;
}
恢复过程：
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jan 7 12:00:29 2022
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RICK (DBID=3475847471)
connected to auxiliary database: RICK (not mounted)
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate AUXILIARY channel c3 type disk;
allocate AUXILIARY channel c4 type disk;
duplicate target database for standby;
}2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=197 instance=hip1 device type=DISK
allocated channel: c2
channel c2: SID=2465 instance=hip1 device type=DISK
allocated channel: c3
channel c3: SID=1513 device type=DISK
allocated channel: c4
channel c4: SID=1137 device type=DISK
Starting Duplicate Db at 07-JAN-22
contents of Memory Script:
{
restore clone standby controlfile;
}
executing Memory Script
Starting restore at 07-JAN-22
channel c3: starting datafile backup set restore
channel c3: restoring control file
channel c3: reading from backup piece +DG_ARCH/RICK/AUTOBACKUP/2022_01_07/s_1093348509.298.1093348509
channel c3: ORA-19870: error while restoring backup piece +DG_ARCH/RICK/AUTOBACKUP/2022_01_07/s_1093348509.298.1093348509
ORA-19505: failed to identify file "+DG_ARCH/RICK/AUTOBACKUP/2022_01_07/s_1093348509.298.1093348509"
ORA-17503: ksfdopn:2 Failed to open file +DG_ARCH/RICK/AUTOBACKUP/2022_01_07/s_1093348509.298.1093348509
ORA-15001: diskgroup "DG_ARCH" does not exist or is not mounted
failover to previous backup
channel c3: starting datafile backup set restore
channel c3: restoring control file
channel c3: reading from backup piece /home/oracle/backup/0a0imb4r_10_1_1
channel c3: piece handle=/home/oracle/backup/0a0imb4r_10_1_1 tag=TAG20220107T115506
channel c3: restored backup piece 1
channel c3: restore complete, elapsed time: 00:00:01
output file name=/data/oradata/rickdg/controlfile01.ctl
Finished restore at 07-JAN-22
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/data/oradata/rickdg/temp.290.1093287863";
set newname for tempfile 2 to
"/data/oradata/rickdg/temp.293.1093346713";
set newname for tempfile 3 to
"/data/oradata/rickdg/temp.292.1093346713";
switch clone tempfile all;
set newname for datafile 1 to
"/data/oradata/rickdg/system.283.1093287729";
set newname for datafile 2 to
"/data/oradata/rickdg/system.289.1093346719";
set newname for datafile 3 to
"/data/oradata/rickdg/sysaux.284.1093287763";
set newname for datafile 4 to
"/data/oradata/rickdg/undotbs1.285.1093287789";
set newname for datafile 5 to
"/data/oradata/rickdg/undotbs2.291.1093288043";
set newname for datafile 7 to
"/data/oradata/rickdg/users.286.1093287789";
set newname for datafile 8 to
"/data/oradata/rickdg/undotbs1.288.1093346723";
set newname for datafile 9 to
"/data/oradata/rickdg/undotbs1.320.1093346727";
set newname for datafile 10 to
"/data/oradata/rickdg/undotbs2.321.1093346731";
set newname for datafile 11 to
"/data/oradata/rickdg/undotbs2.322.1093346735";
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /data/oradata/rickdg/temp.290.1093287863 in control file
renamed tempfile 2 to /data/oradata/rickdg/temp.293.1093346713 in control file
renamed tempfile 3 to /data/oradata/rickdg/temp.292.1093346713 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-JAN-22
channel c3: starting datafile backup set restore
channel c3: specifying datafile(s) to restore from backup set
channel c3: restoring datafile 00002 to /data/oradata/rickdg/system.289.1093346719
channel c3: restoring datafile 00005 to /data/oradata/rickdg/undotbs2.291.1093288043
channel c3: restoring datafile 00007 to /data/oradata/rickdg/users.286.1093287789
channel c3: restoring datafile 00009 to /data/oradata/rickdg/undotbs1.320.1093346727
channel c3: restoring datafile 00011 to /data/oradata/rickdg/undotbs2.322.1093346735
channel c3: reading from backup piece /home/oracle/backup/070imb48_7_1_1
channel c4: starting datafile backup set restore
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00001 to /data/oradata/rickdg/system.283.1093287729
channel c4: restoring datafile 00003 to /data/oradata/rickdg/sysaux.284.1093287763
channel c4: restoring datafile 00004 to /data/oradata/rickdg/undotbs1.285.1093287789
channel c4: restoring datafile 00008 to /data/oradata/rickdg/undotbs1.288.1093346723
channel c4: restoring datafile 00010 to /data/oradata/rickdg/undotbs2.321.1093346731
channel c4: reading from backup piece /home/oracle/backup/060imb48_6_1_1
channel c3: piece handle=/home/oracle/backup/070imb48_7_1_1 tag=TAG20220107T115447
channel c3: restored backup piece 1
channel c3: restore complete, elapsed time: 00:00:25
channel c4: piece handle=/home/oracle/backup/060imb48_6_1_1 tag=TAG20220107T115447
channel c4: restored backup piece 1
channel c4: restore complete, elapsed time: 00:00:25
Finished restore at 07-JAN-22
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1093348896 file name=/data/oradata/rickdg/system.283.1093287729
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1093348896 file name=/data/oradata/rickdg/system.289.1093346719
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1093348896 file name=/data/oradata/rickdg/sysaux.284.1093287763
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1093348896 file name=/data/oradata/rickdg/undotbs1.285.1093287789
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1093348896 file name=/data/oradata/rickdg/undotbs2.291.1093288043
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=1093348896 file name=/data/oradata/rickdg/users.286.1093287789
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=1093348896 file name=/data/oradata/rickdg/undotbs1.288.1093346723
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=1093348897 file name=/data/oradata/rickdg/undotbs1.320.1093346727
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=1093348897 file name=/data/oradata/rickdg/undotbs2.321.1093346731
datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=1093348897 file name=/data/oradata/rickdg/undotbs2.322.1093346735
Finished Duplicate Db at 07-JAN-22
released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN>
Note：至此备库恢复完成
六、配置broker
Note：剩下关键步骤，配置broker建立主备库之间的通讯同步
1、主库启动broker
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+DG_DATA/RICK/BROKER/DR1.DAT' SCOPE=BOTH;
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2 = '+DG_DATA/RICK/BROKER/DR2.DAT' SCOPE=BOTH;
alter system set dg_broker_start=true;
Note：这里我们将broker配置文件放在ASM当中
2、备库启动broker
alter system set dg_broker_start=true;
3、创建dg配置
Note：主备库任意节点操作即可
连接
dgmgrl /
创建
create configuration 'rickadg' as primary database is 'rick' connect identifier is 'rick';
添加备库
add database 'rickdg' as connect identifier is 'rickdg';
4、启用配置
enable configuration;
Note：启用后自动同步配置文件到主库或者备库，次过程为关键过程，会自动建立主备库的通讯连接，配置arch dest、fal server 等参数，启动启动mrp等进程
5、查看配置
show configuration;
Configuration - rickadg
Protection Mode: MaxPerformance
Members:
rick - Primary database
rickdg - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 52 seconds ago)
Note：可以看到配置状态正常
6、查看数据库配置
show database verbose rickdg;
Database - rickdg
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: 1.00 KByte/s
Active Apply Rate: 40.00 KByte/s
Maximum Apply Rate: 40.00 KByte/s
Real Time Query: ON
Instance(s):
rickdg
Properties:
DGConnectIdentifier = 'rickdg'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rickdg'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hipdg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=rickdg_DGMGRL)(INSTANCE_NAME=hipdg)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/rickdg/hipdg/trace/alert_rickdg.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/rickdg/hipdg/trace/drchipdg.log
Database Status:
SUCCESS
DGMGRL>
Note：至此ADG搭建完成
七、后续配置与主备切换
一个合格的ADG是可以进行主备之前来回任意切换的，并且创建数据文件和切换归档是正常同步的
1、配置最高可用模式
edit database 'rick' set property 'LogXptMode'='SYNC';
edit database 'rickdg' set property 'LogXptMode'='SYNC';
edit configuration set protection mode as maxavailability;
查看
show configuration;
DGMGRL> show configuration;
Configuration - rickadg
Protection Mode: MaxAvailability
Members:
rick - Primary database
rickdg - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 52 seconds ago)
Note：可以看到已经改变为最高可用模式了
2、主备切换
连接
dgmgrl
connect sys/Oracle123@rick
Note：注意一定要使用密码连接。
开启转换
switchover to rickdg;
DGMGRL> switchover to rickdg
Performing switchover NOW, please wait...
New primary database "rickdg" is opening...
Operation requires start up of instance "rick" on database "rick"
Starting instance "rick"...
Connected to an idle instance.
ORACLE instance started.
Connected to "rick"
Database mounted.
Database opened.
Connected to "rick"
Switchover succeeded, new primary is "rickdg"
DGMGRL>
Note：可以看到切换是相当的方便，在切换的过程中注意观察主备库之间alert日志，监测实时过程。
查看配置
show configuration;
Configuration - rickadg
Protection Mode: MaxAvailability
Members:
rickdg - Primary database
rick - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 27 seconds ago)
DGMGRL>
Note：等待一会，进行查看，可以看到角色已经互换了。
3、同步测试
创建数据文件
新主库：
create tablespace test datafile '/data/oradata/rickdg/test01.dbf' size 100m
Completed: create tablespace test datafile '/data/oradata/rickdg/test01.dbf' size 100m
新备库：
2022-01-07T14:16:52.894528+08:00
Successfully added datafile 12 to media recovery
Datafile #12: '+DG_DATA/RICK/DATAFILE/test.326.1093357013'
切换归档测试
新主库：
2022-01-07T14:19:36.844258+08:00
Thread 1 advanced to log sequence 32 (LGWR switch), current SCN: 3333166
Current log# 8 seq# 32 mem# 0: /data/oradata/rickdg/group_8.315.1093346407
Current log# 8 seq# 32 mem# 1: /data/oradata/rickdg/group_8.274.1093346411
2022-01-07T14:19:36.861642+08:00
ARC3 (PID:7472): Archived Log entry 28 added for T-1.S-31 ID 0xcf2e0982 LAD:1
新备库：
2022-01-07T14:19:33.730141+08:00
PR00 (PID:10498): Media Recovery Waiting for T-1.S-32 (in transit)
2022-01-07T14:19:33.734836+08:00
Recovery of Online Redo Log: Thread 1 Group 13 Seq 32 Reading mem 0
Mem# 0: +DG_DATA/RICK/ONLINELOG/group_13.297.1093304763
Mem# 1: +DG_ARCH/RICK/ONLINELOG/group_13.256.1093304767
Note：可以看到同步正常
最后修改时间：2022-02-16 23:44:23
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
