ADG单实例搭建系列之 （DBCA） 一、介绍 二、环境准备

发表于

「这是我参与11月更文挑战的第19天，活动详情查看：2021最后一次更文挑战」

一、介绍

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.

1
2
3
4
5
6
7
bash复制代码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
2
3
4
5
bash复制代码##限制：
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软件安装

主库一键安装：

1
bash复制代码./AllOracleSilent.sh -i 192.168.1.172 -d 19c -n orcl -o orcl -b /u01/app -s AL32UTF8

备库一键安装：（备库仅安装ORACLE软件，不建库）

1
bash复制代码./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文件

主库：

1
2
3
4
5
bash复制代码cat <<EOF >> /etc/hosts
##FOR DG BEGIN
192.168.1.180 orcl_stby
##FOR DG END
EOF

备库：

1
2
3
4
5
bash复制代码cat <<EOF >> /etc/hosts
##FOR DG BEGIN
192.168.1.172 orcl
##FOR DG END
EOF

b.配置静态监听和TNS

主库+备库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
bash复制代码##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.主库配置参数

1
2
3
4
5
6
7
8
9
10
11
12
13
sql复制代码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文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
sql复制代码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

1
2
3
4
5
6
7
8
9
bash复制代码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参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
sql复制代码--主库设置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、开启日志应用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
sql复制代码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、测试同步情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
sql复制代码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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
bash复制代码--是否存在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;

主库：

1
2
3
4
5
6
7
8
9
10
11
12
sql复制代码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

备库：

1
2
3
4
5
6
7
8
9
10
11
sql复制代码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数据库。

1
2
sql复制代码ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

测试Failover

1、主库开启闪回，防止Failover后无法再次切回备库

1
2
3
4
5
sql复制代码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

1
2
3
4
5
6
7
8
9
10
sql复制代码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、将原主库闪回，切换回备库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
sql复制代码--查询新主库的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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
sql复制代码--记录表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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
sql复制代码--关闭数据库
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（备库）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
sql复制代码--取消日志应用
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;

参考官方文档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)

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%