一、前言
前面我的文章列举了几种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:可以看到同步正常
评论




