ADG单实例搭建系列之 （DBCA）
【摘要】 参考官方文档12c：Using DBCA to Create a Data Guard Standby 12C参考官方文档19c：Using DBCA to Create a Data Guard Standby 19C参考官方文档21c：Using DBCA to Create a Data Guard Standby 21C参考MOS文档：Creating a Physical Stan...
参考官方文档12c：Using DBCA to Create a Data Guard Standby 12C
参考官方文档19c：Using DBCA to Create a Data Guard Standby 19C
参考官方文档21c：Using DBCA to Create a Data Guard Standby 21C
参考MOS文档：Creating a Physical Standby database using DBCA duplicate (Doc ID 2283697.1)
一、介绍
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 FROM SESSION;
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）