ADG单实例搭建系列之 （DBCA）
一、介绍
The Database Configuration Assistant (DBCA) can also be used as a simple command-line method to create an Oracle Data Guard physical standby database.
The DBCA command qualifier used to create the physical standby database is createDuplicateDB.
dbca -createDuplicateDB -gdbName global_database_name -primaryDBConnectionString easy_connect_string_to_primary -sid database_system_identifier [-createAsStandby [-dbUniqueName db_unique_name_for_standby]] [-customScripts scripts_list] 复制代码
更详细参数可参考：The createDuplicateDB command creates a duplicate of an Oracle database.
Notes：
1、12.2.0.1开始支持DBCA创建物理备库
##限制： 1.主库必须是单机环境，非RAC数据库。 2.主库必须是非CDB环境。 DBCA can only be used to create standby databases for non-multitenant primary databases. In addition, this capability creates only single instance standby databases, not Oracle Real Application Clusters (Oracle RAC) databases. If required, the standby can then be converted to an Oracle RAC standby database, either manually or using Oracle Enterprise Manager Cloud Control. 复制代码
2、18c之后，以上限制已经取消，支持主库是CDB或者RAC环境。
二、环境准备
| 主机名 | ip | DB Version | db_name | db_unique_name | |
| 主库 | orcl | 192.168.1.172 | 19.3.0.0 | orcl | orcl |
| 备库 | orcl_stby | 192.168.1.180 | 19.3.0.0 | orcl | orcl_stby |
Notes：
1、db_unique_name主备库不能相同。
2、db_name主备库需保持一致。
3、主备库DB版本需保持一致。
三、搭建过程
1、Oracle软件安装
主库一键安装：
./AllOracleSilent.sh -i 192.168.1.172 -d 19c -n orcl -o orcl -b /u01/app -s AL32UTF8 复制代码
备库一键安装：（备库仅安装ORACLE软件，不建库）
./AllOracleSilent.sh -i 192.168.1.180 -d 19c -w Y -n orcl_stby -o orcl -b /u01/app -s AL32UTF8 复制代码
一键安装脚本可参考：ORACLE一键安装单机11G/12C/18C/19C并建库脚本
2、环境配置
a.配置hosts文件
主库：
cat <<EOF >> /etc/hosts ##FOR DG BEGIN 192.168.1.180 orcl_stby ##FOR DG END EOF 复制代码
备库：
cat <<EOF >> /etc/hosts ##FOR DG BEGIN 192.168.1.172 orcl ##FOR DG END EOF 复制代码
b.配置静态监听和TNS
主库+备库：
##listener.ora vi /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora ##添加 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db) (SID_NAME = orcl) ) ) ##重启监听 su - oracle -c "lsnrctl stop" su - oracle -c "lsnrctl start" ##tnsnames.ora su - oracle -c "cat <<EOF >> /u01/app/oracle/product/19.3.0/db/network/admin/tnsnames.ora ##FOR DG BEGIN ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL_STBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl_stby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl_stby) ) ) ##FOR DG BEGIN EOF" 复制代码
c.主库配置参数
SQL> select force_logging,log_mode,cdb from gv$database; FORCE_LOGGING LOG_MODE CDB --------------------------------------- ------------ --- YES ARCHIVELOG YES ##开启方式 alter database force logging; shutdown immediate startup mount alter database archivelog; alter database open; alter pluggable database all open; 复制代码
d.主库添加stanby log文件
set line222 col member for a60 select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from gv$logfile t1,gv$log t2 where t1.group#=t2.group#; THREAD# GROUP# MEMBER T2.BYTES/1024/1024 ---------- ---------- ------------------------------------------------------------ ------------------ 1 3 /oradata/ORCL/redo03.log 120 1 2 /oradata/ORCL/redo02.log 120 1 1 /oradata/ORCL/redo01.log 120 --需要注意： --1.stanby log日志大小与redo log日志保持一致 --2.stanby log数量： standby logfile=(1+logfile组数)*thread=(1+3)*1=4组,需要加4组standby logfile. --3.thread要与redo log保持一致，如果是rac，需要增加多个thread对应的standby log ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 4 ('/oradata/ORCL/standby_redo04.log') SIZE 120M, group 5 ('/oradata/ORCL/standby_redo05.log') SIZE 120M, group 6 ('/oradata/ORCL/standby_redo06.log') SIZE 120M, group 7 ('/oradata/ORCL/standby_redo07.log') SIZE 120M; SQL> select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from gv$logfile t1,gv$standby_log t2 where t1.group#=t2.group#; THREAD# GROUP# MEMBER T2.BYTES/1024/1024 ---------- ---------- ------------------------------------------------------------ ------------------ 1 4 /oradata/ORCL/standby_redo04.log 120 1 5 /oradata/ORCL/standby_redo05.log 120 1 6 /oradata/ORCL/standby_redo06.log 120 1 7 /oradata/ORCL/standby_redo07.log 120 复制代码3、 DBCA AsStandby
dbca -silent -createDuplicateDB \ -gdbName orcl \ -sid orcl \ -sysPassword oracle \ -primaryDBConnectionString 192.168.1.172:1521/orcl \ -nodelist orcl_stby \ -databaseConfigType SINGLE \ -createAsStandby -dbUniqueName orcl_stby \ -datafileDestination '/oradata' 复制代码
4、设置主库+备库DG参数
--主库设置DG参数 ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; ALTER SYSTEM SET FAL_SERVER=ORCL_STBY; ALTER SYSTEM SET FAL_CLIENT=ORCL; ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/ORCL','/oradata/ORCL_STBY' SCOPE=SPFILE; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/ORCL','/oradata/ORCL_STBY' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; --备库设置DG参数 ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL_STBY,ORCL)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; ALTER SYSTEM SET FAL_SERVER=ORCL; ALTER SYSTEM SET FAL_CLIENT=ORCL_STBY; ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/ORCL_STBY','/oradata/ORCL' SCOPE=SPFILE; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/ORCL_STBY','/oradata/ORCL' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 复制代码
5、开启日志应用
SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ ONLY PHYSICAL STANDBY SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ ONLY NO --开启日志应用 alter database recover managed standby database using current logfile disconnect; SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY SQL> SELECT protection_mode FROM v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE 复制代码
6、测试同步情况
set line222 col member for a60 --查看是否存在RFS和MRP进程 select process,group#,thread#,sequence# from gv$managed_standby; SQL> --查看standby日志status是否存在active select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#; PROCESS GROUP# THREAD# SEQUENCE# --------- ---------------------------------------- ---------- ---------- DGRD N/A 0 0 DGRD N/A 0 0 ARCH N/A 0 0 ARCH N/A 0 0 ARCH N/A 0 0 ARCH N/A 0 0 RFS N/A 1 0 RFS 1 1 28 MRP0 N/A 1 28 DGRD N/A 0 0 10 rows selected. SQL> SQL> SQL> GROUP# THREAD# T1.BYTES/1024/1024 STATUS MEMBER ---------- ---------- ------------------ ---------- ------------------------------------------------------------ 4 1 120 ACTIVE /oradata/ORCL_STBY/standby_redo04.log 5 1 120 UNASSIGNED /oradata/ORCL_STBY/standby_redo05.log 6 1 120 UNASSIGNED /oradata/ORCL_STBY/standby_redo06.log 7 1 120 UNASSIGNED /oradata/ORCL_STBY/standby_redo07.log SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ ONLY NO --主库插入数据 sqlplus test/test@pdb01 insert into test values (999); commit; --备库查询 SQL> alter session set container=pdb01; SQL> select * from test.test; ID ---------- 1 2 999 --备库已同步 复制代码
四、Database Switchover
--是否存在GAP select thread#,low_sequence#,high_sequence# from v$archive_gap; --主库确认可切换角色 select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS -------------------- ---------------- -------------------- -------------------- -------------------- READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY --备库确认可切换角色 select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS -------------------- ---------------- -------------------- -------------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED NOTES: A 如果switchover_status为TO_STANDBY说明可以直接转换 alter database commit to switchover to physical standby; B 如果switchover_status为SESSIONS ACTIVE 则关闭会话 alter database commit to switchover to physical standby with session shutdown; 复制代码
主库：
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; SHUTDOWN IMMEDIATE; STARTUP NOMOUNT; ALTER DATABASE MOUNT STANDBY DATABASE; ALTER DATABASE OPEN READ ONLY; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; SQL> select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS -------------------- ---------------- -------------------- -------------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY 复制代码
备库：
alter system set log_archive_dest_state_2=DEFER; ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; SHUTDOWN IMMEDIATE; STARTUP; alter system set log_archive_dest_state_2=ENABLE; SQL> select open_mode,database_role,protection_mode,protection_level,switchover_status from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS -------------------- ---------------- -------------------- -------------------- -------------------- READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY 复制代码
五、Failover
Failover后，原主库将从DG配置中删除，如果原主库启用了Flashback,则在修复故障后，故障的数据库可恢复为新的standby数据库。
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; ALTER DATABASE ACTIVATE STANDBY DATABASE; 复制代码
测试Failover
1、主库开启闪回，防止Failover后无法再次切回备库
alter database flashback on; alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' scope=spfile; alter system set db_recovery_file_dest_size=5G scope=spfile; shutdown immediate startup 复制代码
2、备库进行Failover
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; ALTER DATABASE ACTIVATE STANDBY DATABASE; ALTER DATABASE OPEN; --切换成功 select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY READ WRITE 复制代码
3、将原主库闪回，切换回备库
--查询新主库的scn号 SQL> select to_char(standby_became_primary_scn) from v$database; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ---------------------------------------- 1022650 --原主库闪回到scn 1022650 SQL> flashback database to scn 1022650; Flashback complete. --切换到physical standby SQL> alter database convert to physical standby; Database altered. shutdown immediate startup --开启日志应用 alter database recover managed standby database using current logfile disconnect from session; --原主库已恢复为备库 SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ ONLY WITH APPLY PHYSICAL STANDBY SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES 复制代码
六、Snapshot Standby
Snapshot standby database是ORACLE 11g的新特性。允许Physical standby短时间的使用read write模式。
1、切换为Snapshot Standby
--记录表test状态 SQL> select * from test.test; ID ---------- 1 2 3 --关闭备库 SHUTDOWN IMMEDIATE; STARTUP MOUNT; --取消日志应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --查看闪回状态 SELECT flashback_on FROM v$database; FLASHBACK_ON ------------------ NO --切换为snapshot standby ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; --打开数据库到读写状态 ALTER DATABASE OPEN; SELECT flashback_on FROM v$database; FLASHBACK_ON ------------------ RESTORE POINT ONLY --You can now do treat the standby like any read-write database. --主库插入数据，测试之后切换回physical standby是否能同步 insert into test.test values (9999); commit; --备库尝试插入数据，drop表 SQL> insert into test.test values (777); 1 row created. SQL> commit; Commit complete. SQL> select * from test.test; ID ---------- 1 2 3 777 SQL> drop table test.test; Table dropped. 复制代码
2、切换回PHYSICAL STANDBY
--关闭数据库 SHUTDOWN IMMEDIATE; --开启到mount STARTUP MOUNT; --切换回PHYSICAL STANDBY ALTER DATABASE CONVERT TO PHYSICAL STANDBY; --关闭数据库 SHUTDOWN IMMEDIATE; --开启数据库，并开启到read only STARTUP NOMOUNT; ALTER DATABASE MOUNT STANDBY DATABASE; ALTER DATABASE OPEN READ ONLY; --开启日志应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; SELECT flashback_on FROM v$database; FLASHBACK_ON ------------------ NO --已恢复到PHYSICAL STANDBY，查看表是否正常，期间主库的操作是否同步 SQL> select * from test.test; ID ---------- 1 2 3 9999 复制代码
**注意：**一旦snapshot standby被激活的时间超出了primary 的最大负载时间，再次的本地更新操作将会产生额外的异常。
七、开启FLASHBACK（备库）
--取消日志应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --开启闪回 ALTER DATABASE FLASHBACK ON; --配置闪回参数 alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' scope=spfile; alter system set db_recovery_file_dest_size=5G scope=spfile; --开启数据库到read only shutdown immediate startup mount alter database open read only; --开启日志应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FRO
作者：Lucifer三思而后行
链接：https://juejin.cn/post/7031943973128110093