一、前言

前面我的文章列舉了幾種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：可以看到同步正常