概述
本文描述在OCI上搭建的标准ADG上的配置，以促进对概念的理解。
此ADG配置中，主备数据库均为单实例。主机名分别为db01和db02。数据库名为CDB。主库的DB_UNIQUE_NAME为chicago，备库的则为boston。
在以下命令中，无论主备如何切换，主库均指主数据库服务器上的数据库，备库指备数据库服务器上的数据库。
环境变量
主数据库服务器中.bashrc中的设置：
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1; export ORACLE_HOME
PATH=$PATH:/u01/app/oracle/product/19.0.0.0/dbhome_1/bin; export PATH
LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib; export LD_LIBRARY_PATH
ORACLE_UNQNAME=CDB_chicago;export ORACLE_UNQNAME
ORACLE_SID=CDB; export ORACLE_SID
备数据库服务器中.bashrc中的设置：
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1; export ORACLE_HOME
PATH=$PATH:/u01/app/oracle/product/19.0.0.0/dbhome_1/bin; export PATH
LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib; export LD_LIBRARY_PATH
ORACLE_UNQNAME=CDB_boston;export ORACLE_UNQNAME
ORACLE_SID=CDB; export ORACLE_SID
可以看到，实例名是一样的，但DB_UNIQUE_NAME不同。
监听
主数据库服务器：
[grid@db02 ~]$ cd $ORACLE_HOME/network/admin
[grid@db02 admin]$ cat listener.ora
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET
LISTENER=(DESCRIPTION=(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))
[oracle@db01 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-SEP-2021 14:05:53
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 17-SEP-2021 12:32:41
Uptime 0 days 1 hr. 33 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/db01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.201)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "CDBXDB.sub07281611220.training.oraclevcn.com" has 1 instance(s).
Instance "CDB", status READY, has 1 handler(s) for this service...
Service "CDB_CFG.sub07281611220.training.oraclevcn.com" has 1 instance(s).
Instance "CDB", status READY, has 2 handler(s) for this service...
Service "CDB_chicago.sub07281611220.training.oraclevcn.com" has 1 instance(s).
Instance "CDB", status READY, has 2 handler(s) for this service...
Service "cc296b894a394233e053c900000ad2cf.sub07281611220.training.oraclevcn.com" has 1 instance(s).
Instance "CDB", status READY, has 2 handler(s) for this service...
Service "orclpdb1.sub07281611220.training.oraclevcn.com" has 1 instance(s).
Instance "CDB", status READY, has 2 handler(s) for this service...
The command completed successfully
备数据库服务器：
[grid@db02 ~]$ cd $ORACLE_HOME/network/admin
[grid@db02 admin]$ cat listener.ora
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET
LISTENER=(DESCRIPTION=(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))
[oracle@db02 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-SEP-2021 14:02:58
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 17-SEP-2021 12:42:16
Uptime 0 days 1 hr. 20 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/db02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.216)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "CDBXDB.sub07281611220.training.oraclevcn.com" has 1 instance(s).
Instance "CDB", status READY, has 1 handler(s) for this service...
Service "CDB_CFG.sub07281611220.training.oraclevcn.com" has 1 instance(s).
Instance "CDB", status READY, has 2 handler(s) for this service...
Service "CDB_boston.sub07281611220.training.oraclevcn.com" has 1 instance(s).
Instance "CDB", status READY, has 2 handler(s) for this service...
Service "orclpdb1.sub07281611220.training.oraclevcn.com" has 1 instance(s).
Instance "CDB", status READY, has 2 handler(s) for this service...
The command completed successfully
网络服务名定义
主数据库服务器：
[oracle@db01 ~]$ cd $ORACLE_HOME/network/admin
[oracle@db01 admin]$ cat tnsnames.ora
LISTENER_CDB=(ADDRESS=(PROTOCOL=TCP)(HOST=db01)(PORT=1521))
CDB_CHICAGO=(DESCRIPTION=(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760)(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.201)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CDB_chicago.sub07281611220.training.oraclevcn.com)))
CDB_boston=(DESCRIPTION=(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760)(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.216)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CDB_boston.sub07281611220.training.oraclevcn.com)(UR=A)))
备数据库服务器：
[oracle@db02 ~]$ cd $ORACLE_HOME/network/admin
[oracle@db02 admin]$ cat tnsnames.ora
CDB_CHICAGO=(DESCRIPTION=(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760)(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.201)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CDB_chicago.sub07281611220.training.oraclevcn.com)))
CDB_boston=(DESCRIPTION=(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760)(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.216)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CDB_boston.sub07281611220.training.oraclevcn.com)(UR=A)))
数据库初始化参数
DB_ID
完全一样：
SQL> select DBID from v$database;
DBID
2200070796
DB_NAME
完全一样：
SQL> select name from v$database;
NAME
CDB
DB_UNIQUE_NAME
不同：
SQL> show parameter DB_UNIQUE_NAME
NAME TYPE VALUE
db_unique_name string CDB_chicago
SQL> show parameter DB_UNIQUE_NAME
NAME TYPE VALUE
db_unique_name string CDB_boston
INSTANCE_NAME
完全一样：
SQL> show parameter instance_name
NAME TYPE VALUE
instance_name string CDB
GLOBAL_NAME
完全一样：
SQL> SELECT * FROM global_name;
GLOBAL_NAME
CDB.SUB07281611220.TRAINING.ORACLEVCN.COM
LOG_ARCHIVE_FORMAT
相同：
SQL> show parameter LOG_ARCHIVE_FORMAT
NAME TYPE VALUE
log_archive_format string %t_%s_%r.dbf
DB_CREATE_FILE_DEST
相同：
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
db_create_file_dest string +DATA
AUDIT_FILE_DEST
主库：
SQL> show parameter audit_file_dest
NAME TYPE VALUE
audit_file_dest string /u01/app/oracle/admin/CDB_chicago/adump
备库：
SQL> show parameter audit_file_dest
NAME TYPE VALUE
audit_file_dest string /u01/app/oracle/admin/CDB_boston/adump
ADG相关设置
归档模式
均打开：
SQL> SELECT log_mode FROM v$database;
LOG_MODE
ARCHIVELOG
FORCE_LOGGING
均打开：
SQL> select force_logging from v$database;
FORCE_LOGGING
YES
SPFILE
SQL> show parameter spfile
NAME TYPE VALUE
spfile string +DATA/CDB_CHICAGO/PARAMETERFIL
E/spfile.269.1083467799
SQL> show parameter spfile
NAME TYPE VALUE
spfile string +DATA/CDB_boston/PARAMETERFILE
/spfilecdb.ora
DG_BROKER_CONFIG_FILE
SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
dg_broker_config_file1 string +DATA/CDB_CHICAGO/dr1CDB_chicago.dat
dg_broker_config_file2 string +DATA/CDB_CHICAGO/dr2CDB_chicago.dat
SQL> show parameter dg_broker_config_file
NAME TYPE VALUE
dg_broker_config_file1 string +DATA/CDB_boston/dr1CDB_boston.dat
dg_broker_config_file2 string +DATA/CDB_boston/dr2CDB_boston.dat
CONTROL_FILES
注意，目录结构是依据DB_UNIQUE_NAME的：
SQL> show parameter CONTROL_FILES
NAME TYPE VALUE
control_files string +RECO/CDB_CHICAGO/CONTROLFILE/
current.256.1083467277
SQL> show parameter CONTROL_FILES
NAME TYPE VALUE
control_files string +RECO/CDB_boston/CONTROLFILE/c
urrent.258.1083501469
STANDBY_FILE_MANAGEMENT
相同
SQL> show parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
standby_file_management string AUTO
文件名转换
均未设置：
SQL> show parameter DB_FILE_NAME_CONVERT
NAME TYPE VALUE
db_file_name_convert string
pdb_file_name_convert string
SQL> show parameter LOG_FILE_NAME_CONVERT
NAME TYPE VALUE
log_file_name_convert string
归档目标位置
只有主库的做了显式设置，其余均为默认空值：
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
log_archive_dest_2 string service="CDB_boston", ASYNC NO
AFFIRM delay=0 optional compre
ssion=disable max_failure=0 re
open=300 db_unique_name="CDB_b
oston" net_timeout=30, valid_f
or=(online_logfile,all_roles)
主备切换后：
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
log_archive_dest_2 string
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
log_archive_dest_2 string service="CDB_chicago", ASYNC N
OAFFIRM delay=0 optional compr
ession=disable max_failure=0 r
eopen=300 db_unique_name="CDB_
chicago" net_timeout=30, valid
_for=(online_logfile,all_roles
)
FAL_SERVER与FAL_CLIENT
FAL_CLIENT均未设置。
主备未切换时，FAL_SERVER如下：
SQL> show parameter FAL_SERVER
NAME TYPE VALUE
fal_server string
SQL> show parameter FAL_SERVER
NAME TYPE VALUE
fal_server string CDB_chicago
主备切换后，FAL_SERVER如下：
SQL> show parameter FAL_SERVER
NAME TYPE VALUE
fal_server string CDB_boston
SQL> show parameter FAL_SERVER
NAME TYPE VALUE
fal_server string
REMOTE_LOGIN_PASSWORDFILE
相同：
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE
NAME TYPE VALUE
remote_login_passwordfile string EXCLUSIVE
LOG_ARCHIVE_CONFIG
相同：
SQL> show parameter LOG_ARCHIVE_CONFIG
NAME TYPE VALUE
log_archive_config string dg_config=(CDB_boston,CDB_chicago)
FRA设置
相同：
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 255G
REDO设置
主库：
SQL>
col member for a60
set lines 120
select m.group
GROUP
1 +RECO/CDB_CHICAGO/ONLINELOG/group_1.257.1083467281 1073741824
2 +RECO/CDB_CHICAGO/ONLINELOG/group_2.258.1083467281 1073741824
3 +RECO/CDB_CHICAGO/ONLINELOG/group_3.259.1083467281 1073741824
SQL> select group
GROUP
1 1 1 INACTIVE
2 1 1 INACTIVE
3 1 1 CURRENT
SQL> select group
GROUP
4 1 UNASSIGNED 1073741824
5 1 UNASSIGNED 1073741824
6 1 UNASSIGNED 1073741824
7 1 UNASSIGNED 1073741824
备库：
SQL>
col member for a60
set lines 120
select m.group
GROUP
1 +RECO/CDB_boston/ONLINELOG/group_1.259.1083501539 1073741824
2 +RECO/CDB_boston/ONLINELOG/group_2.260.1083501549 1073741824
3 +RECO/CDB_boston/ONLINELOG/group_3.261.1083501557 1073741824
SQL> select group
GROUP
1 1 1 UNUSED
2 1 1 UNUSED
3 1 1 UNUSED
SQL> select group
GROUP
4 1 2200070796 1073741824
5 1 UNASSIGNED 1073741824
6 1 UNASSIGNED 1073741824
7 1 UNASSIGNED 1073741824
从输出可知：
- 目录命名依据DB_UNIQUE_NAME
- 对于每一个thread，standby log group的数量比本地redo log group的数量至少多1个。这是ADG的要求。
SRVCTL命令
主库：
[oracle@db01 ~]$ srvctl config database -d $(srvctl config database) -v
Database unique name: CDB_chicago
Database name: CDB
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/CDB_CHICAGO/PARAMETERFILE/spfile.269.1083467799
Password file:
Domain: sub07281611220.training.oraclevcn.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: CDB
Configured nodes: db01
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
备库：
[oracle@db02 ~]$ srvctl config database -d $(srvctl config database) -v
Database unique name: CDB_boston
Database name: CDB
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/CDB_boston/PARAMETERFILE/spfileCDB.ora
Password file:
Domain: sub07281611220.training.oraclevcn.com
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: CDB
Configured nodes: db02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
DGMGRL
[oracle@db01 ~]$ dgmgrl / as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Sep 17 14:45:59 2021
Version 19.11.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "CDB_chicago"
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - CDB_chicago_CDB_boston
Protection Mode: MaxPerformance
Members:
CDB_chicago - Primary database
CDB_boston - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 58 seconds ago)
DGMGRL> show database CDB_chicago
Database - CDB_chicago
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
CDB
Database Status:
SUCCESS
DGMGRL> show database CDB_boston
Database - CDB_boston
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: 3.00 KByte/s
Real Time Query: ON
Instance(s):
CDB
Database Status:
SUCCESS
|