Oracle 19c使用dbca快速搭建物理ADG

Oracle在12.2版本之后，推出了部署ADG的新方式DBCA，通过DBCA方式快速的部署DG备库，简化了部署备库DG的操作步骤。

12.2.0.1 开始支持DBCA创建物理备库，但是限制较多，如：

• 主库必须是单机环境，非RAC数据库；
• 主库必须是非CDB环境；

18C之后，以上限制已经取消，支持主库是CDB和RAC环境，本文将为您详细介绍ORACLE 19C RAC下如何通过DBCA搭建dataguard（主库是RAC，备库是单机）的详细步骤。

一、环境描述

二、ADG搭建过程

2.1 配置hosts文件

主库（两个节点）：

cat /etc/hosts
##FOR DG BEGIN
10.10.10.32 ora1922
##FOR DG END
EOF


备库：

cat /etc/hosts
##FOR DG BEGIN
10.10.10.41 rac19c01-vip
10.10.10.43 rac19c02-vip
10.10.10.44 rac19c-scan
##FOR DG END
EOF


2.2 配置静态监听

修改listener.ora文件， 增加静态监听：

主库 rac01：

cat $ORACLE_HOME/network/admin/listener.ora
##FOR DG BEGIN
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac19c)
(SID_NAME = rac19c1)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db)
)
)
##FOR DG END
EOF


主库 rac02：

cat $ORACLE_HOME/network/admin/listener.ora
##FOR DG BEGIN
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = rac19c)
(SID_NAME = rac19c2)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db)
)
)
##FOR DG END
EOF


主库重启监听，使配置生效：

#主库RAC重启监听
srvctl stop listener
srvctl start listener

######备库可以不用配置静态监听

2.3 tnsnames.ora 增加TNS

主库和备库都添加

cat $ORACLE_HOME/network/admin/tnsnames.ora
##FOR DG BEGIN
RAC19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac19c-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac19c)
)
)
RAC19C1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac19c01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac19c)
)
)
RAC19C2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac19c02-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac19c)
)
)

RAC19CDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC19CDG)
)
)
##FOR DG BEGIN
EOF


tnsping 测试连通性：
$ tnsping RAC19C
$ tnsping RAC19CDG

2.4 主库配置参数

查看是否开启强制日志：

SQL> select force_logging,log_mode,cdb from gv$database;

FORCE_LOGGING LOG_MODE CDB
--------------------------------------- ------------ ---
NO ARCHIVELOG NO
NO ARCHIVELOG NO


如果没有开启强制日志：

alter database force logging;


如果没有开启归档日志：

shutdown immediate
startup mount
alter database archivelog;
alter database open;
alter pluggable database all open;


2.5 主库添加standby log文件

set line222
col member for a60
select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from v$logfile t1,v$log t2 where t1.group#=t2.group# order by 1,2;
THREAD# GROUP# MEMBER T2.BYTES/1024/1024
---------- ---------- ------------------------------------------------------------ ------------------
1 1 +DATA/RAC19C/ONLINELOG/group_1.262.1159192413 256
1 2 +DATA/RAC19C/ONLINELOG/group_2.263.1159192413 256
2 3 +DATA/RAC19C/ONLINELOG/group_3.266.1159193207 256
2 4 +DATA/RAC19C/ONLINELOG/group_4.267.1159193207 256

注意：
stanby log 日志大小至少要和 redo log 日志一样大小，不能小于主库日志大小；
stanby log 数量： standby logfile=(1+logfile组数)=(1+2)=3 组,每个 thread 需要加 3 组 standby logfile；
thread 要与 redo log 保持一致，如果是 rac，需要增加多个 thread 对应的 standby log；

ALTER DATABASE ADD STANDBY LOGFILE thread 1
group 5 ('+DATA') SIZE 256M,
group 6 ('+DATA') SIZE 256M,
group 7 ('+DATA') SIZE 256M;

三、DBCA创建物理备库

3.1 备库安装数据库软件

备库只安装数据库软件和相应的补丁（可参考19c 单机安装文档 ），不创建实例。

3.2 dbca创建adg

# su - oracle
$ dbca -silent -createDuplicateDB \
-sysPassword oracle \
-primaryDBConnectionString 10.10.10.44:1521/rac19c \
-gdbName rac19c \
-sid rac19cdg -dbUniqueName rac19cdg \
-nodelist ora1922 \
-databaseConfigType SINGLE \
-datafileDestination '/u01/oradata/' \
-initParams db_create_online_log_dest_1='/u01/oradata/' \
-createAsStandby
Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/rac19cdg/rac19cdg.log" for further details.


注意：
###gdbName 要与主库一样，dbUniqueName要跟主库不一样

DBCA更详细参数可参考官方文档：The createDuplicateDB command creates a duplicate of an Oracle database.

参考链接：https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-physical-standby.html#GUID-7C55FE8A-50C3-4601-9ADA-98BE6D65F1DD
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/creating-and-configuring-an-oracle-database.html#GUID-7F4B1A64-5B08-425A-A62E-854542B3FD4E

如果创建过程失败时，可进行删除，然后重新配置：

执行dbca进行删除：
$ORACLE_HOME/bin/dbca -silent -deleteDatabase -sourceDB s19cdg -sid s19cdg -sysPassword ORAcle123

四、配置主库和备库DG参数

4.1 主库设置DG参数

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RAC19C,RAC19CDG)' sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RAC19C' sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RAC19CDG ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RAC19CDG' sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 sid='*';
ALTER SYSTEM SET FAL_SERVER=RAC19CDG sid='*';
ALTER SYSTEM SET FAL_CLIENT=RAC19C sid='*';
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/oradata/RAC19CDG','+DATA' SCOPE=SPFILE sid='*';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/oradata/RAC19CDG','+DATA' SCOPE=SPFILE sid='*';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO sid='*';

注意： RAC 修改参数需要加上 sid='*'，修改多个实例。


4.2 备库设置 DG 参数

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RAC19CDG,RAC19C)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RAC19CDG';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RAC19C ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RAC19C';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET FAL_SERVER=RAC19C;
ALTER SYSTEM SET FAL_CLIENT=RAC19CDG;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA','/u01/oradata/RAC19CDG' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA','/u01/oradata/RAC19CDG' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

注意： 如果同时设置 OMF 和 DB_FILE_NAME_CONVERT 参数，则优先 OMF 参数。


五、开启日志应用

##备库执行

alter database recover managed standby database using current logfile disconnect from session;


##主库执行

alter system set log_archive_dest_state_2=enable sid='*';