ORACLE 12c 之后的新特性，用dbca 来搭建 ADG，现在都 23ai了，还没有通过dbca 搭建过 ADG，最近 ORACLE Cloud arm 环境都已经就绪，利用起来，抽空搭建一个，记录如下。

[toc]

ORACLE 19c using dbca to duplicate a Physical Standby database

环境信息

Primary Physical Standby
hostname fcdora01 fcdora02
IP 10.0.0.100 10.0.0.200
db_name armdb armdb
db_unique_name armdb armdg
instance name (SID) armdb armdg

操作步骤

主库(Primary)

设置归档和Force Logging

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
sys@ARMDB> col FORCE_LOGGING for a20
sys@ARMDB> /

FORCE_LOGGING        LOG_MODE
-------------------- ------------------------
NO                   ARCHIVELOG
sys@ARMDB> select LOG_MODE,FORCE_LOGGING from v$database;

LOG_MODE                 FORCE_LOGGING
------------------------ --------------------
ARCHIVELOG               NO
sys@ARMDB> alter database force logging;

Database altered.
sys@ARMDB> select LOG_MODE,FORCE_LOGGING from v$database;

LOG_MODE                 FORCE_LOGGING
------------------------ --------------------
ARCHIVELOG               YES

创建 Standby Redo logs

为了dbca 的时候在ADG上自动创建 standby redo log ，提前在主库上创建 standby redo logs，数量为n+1，主库为3组，stadnby log file 为4组。

1
2
3
4
5
6
7
sys@ARMDB>  select thread#,group#,bytes/1024/1024 SIZE_IN_MB , status from v$log;

   THREAD#     GROUP# SIZE_IN_MB STATUS
---------- ---------- ---------- --------------------------------
         1          1        200 CURRENT
         1          2        200 INACTIVE
         1          3        200 INACTIVE

由于使用了omf，可以不指定路径。

1
2
3
4
5
alter database add standby logfile thread 1
group 10   size 200M,
group 11   size 200M,
group 12   size 200M,
group 13   size 200M;

或者挨个创建

1
2
3
4
alter database add standby logfile thread 1 group 10   size 200M;
alter database add standby logfile thread 1 group 11   size 200M;
alter database add standby logfile thread 1 group 12   size 200M;
alter database add standby logfile thread 1 group 13   size 200M;

查看logfile 信息如下：

1
2
3
4
5
6
7
8
9
10
11
12
sys@ARMDB> select * from v$Logfile;
    GROUP# STATU TYPE           MEMBER                                                     IS_REC     CON_ID
---------- ----- -------------- ---------------------------------------------------------- ------ ----------
         1       ONLINE         /data/oradata/ARMDB/onlinelog/o1_mf_1_m7qkh7pn_.log        NO              0
         2       ONLINE         /data/oradata/ARMDB/onlinelog/o1_mf_2_m7qkh7vm_.log        NO              0
         3       ONLINE         /data/oradata/ARMDB/onlinelog/o1_mf_3_m7qkh80v_.log        NO              0
        10       STANDBY        /data/oradata/ARMDB/onlinelog/o1_mf_10_mc6k53cg_.log       NO              0
        11       STANDBY        /data/oradata/ARMDB/onlinelog/o1_mf_11_mc6k53gp_.log       NO              0
        12       STANDBY        /data/oradata/ARMDB/onlinelog/o1_mf_12_mc6k53ks_.log       NO              0
        13       STANDBY        /data/oradata/ARMDB/onlinelog/o1_mf_13_mc6k6z4n_.log       NO              0

7 rows selected.

主库参数文件配置

1
2
3
4
5
6
alter system set log_archive_config='dg_config=(armdb,armdg)'; 
alter system set log_archive_dest_1='location=/data/archive/ valid_for=(all_logfiles,all_roles) db_unique_name=armdb'; 
alter system set log_archive_dest_2='SERVICE=armdg lgwr async valid_for=(ONLINE_LOGFILES,primary_role) db_unique_name=armdg'; 
alter system set standby_file_management='AUTO'; 
alter system set fal_server=armdg; 
alter system set log_archive_dest_state_2=enable;

Physical Standby database

duplicate a Physical Standby database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[oracle@fcdora02 ~]$ dbca -silent -createDuplicateDB \
-gdbName armdb -sid armdg -sysPassword sysPassword \
-primaryDBConnectionString fcdora01:1521/armdb \
-createAsStandby -dbUniqueName armdg
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 "/data/app/oracle/cfgtoollogs/dbca/armdg/armdg.log" for further details.

在这期间会自动创建一个静态监听，端口从1522 开始，如果多次执行，端口会依次加1. 用来进行rman 操作

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
[oracle@fcdora02 ~]$ ps -ef |grep tns
root           6       2  0 Jun25 ?        00:00:00 [netns]
oracle   3029829       1  0 08:25 ?        00:00:00 /data/app/oracle/product/19c/db/bin/tnslsnr LISTENER -inherit
oracle   3382968       1  0 18:10 ?        00:00:00 /data/app/oracle/product/19c/db/bin/tnslsnr LISTENER20240807181005 -inherit
oracle   3384149 3383781  0 18:10 pts/3    00:00:00 grep --color=auto tns
[oracle@fcdora02 ~]$ lsnrctl status LISTENER20240807181005

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-AUG-2024 18:10:50

Copyright (c) 1991, 2023, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=fcdora02)(PORT=1522))
STATUS of the LISTENER
------------------------
Alias                     LISTENER20240807181005
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-AUG-2024 18:10:06
Uptime                    0 days 0 hr. 0 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app/oracle/product/19c/db/network/admin/listener.ora
Listener Log File         /data/app/oracle/diag/tnslsnr/fcdora02/listener20240807181005/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcdora02)(PORT=1522)))
Services Summary...
Service "armdg" has 1 instance(s).
  Instance "armdg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@fcdora02 ~]$ cat  /data/app/oracle/product/19c/db/network/admin/listener.ora
# listener.ora Network Configuration File: /data/app/oracle/product/19c/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER20240807181005 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = armdg)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = fcdora02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

LISTENER20240807181005 =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = fcdora02)(PORT = 1522))
  )

创建完成查看 Standby database 状态

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
60
61
[oracle@fcdora02 ~]$ sh dginof.sh

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 7 19:46:03 2024
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL>
OPEN_MODE				 DATABASE_ROLE
---------------------------------------- --------------------------------
READ ONLY				 PHYSICAL STANDBY

SQL> 
  DGBEHIND CTIME		NAME		     VALUE		  DATUM_TIME
---------- -------------------- -------------------- -------------------- --------------------
	   20240807 18:46:03	apply lag
SQL>
PROCESS 	       BLOCK#	  BLOCKS STATUS 		   SEQUENCE#
------------------ ---------- ---------- ------------------------ ----------
ARCH			    0	       0 CONNECTED			   0
DGRD			    0	       0 ALLOCATED			   0
DGRD			    0	       0 ALLOCATED			   0
ARCH			    0	       0 CONNECTED			   0
ARCH			    0	       0 CONNECTED			   0
ARCH			    0	       0 CONNECTED			   0

6 rows selected.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@fcdora02 ~]$ s

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 7 19:46:57 2024
Version 19.19.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /data/app/oracle/product/19c/db/dbs/arch
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence	       0
SQL> show parameter log_arch

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_config		     string dg_config=(armdb,armdg)
log_archive_dest		     string
log_archive_dest_1		     string

可以看到并没有启用MRP，需要手动配置 Standby database 的参数，具体 配置如下:

Physical Standby database参数配置

1
2
3
4
5
6
-- alter system set log_archive_config='dg_config=(armdb,armdg)';
alter system set log_archive_dest_1='location=/data/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=armdg';
alter system set log_archive_dest_2='SERVICE=armdg lgwr async VALID_FOr=(ONLINE_LOGFILES,primary_role) DB_UNIQUE_NAME=armdb';
alter system set log_archive_dest_state_2='enable';
alter system set standby_file_management='AUTO'; 
alter system set fal_server=armdb;

启动MRP

1
alter database recover managed standby database using current logfile  disconnect

主库归档之后，查看 Standby 状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
OPEN_MODE				 DATABASE_ROLE
---------------------------------------- --------------------------------
READ ONLY WITH APPLY			 PHYSICAL STANDBY
  DGBEHIND CTIME		NAME		     VALUE		  DATUM_TIME
---------- -------------------- -------------------- -------------------- --------------------
	 0 20240807 20:01:02	apply lag	     +00 00:00:00	  08/07/2024 20:01:00
PROCESS 	       BLOCK#	  BLOCKS STATUS 		   SEQUENCE#
------------------ ---------- ---------- ------------------------ ----------
ARCH			    1	       7 CLOSING			 104
DGRD			    0	       0 ALLOCATED			   0
DGRD			    0	       0 ALLOCATED			   0
ARCH			    0	       0 CONNECTED			   0
ARCH			    1	     105 CLOSING			 105
ARCH			    1	     200 CLOSING			 101
MRP0			 8117	  409600 APPLYING_LOG			 106
RFS			 8117	       1 IDLE				 106
RFS			    0	       0 IDLE				   0
RFS			    0	       0 IDLE				   0
RFS			    0	       0 IDLE				   0
RFS			    0	       0 IDLE				   0

12 rows selected.

可以看到mrp状态正常，正在 APPLYING_LOG。至此通过 dbca 搭建 Physical Standby database 完成。

注意事项：

1、在dbca duplicate 的时候会自动创建静态监听，我这边已经创建了 1521 的端口了，他自动产生的静态监听的端口是1522，如果失败了再次搭建，端口会依次加1，果有防火墙的话，需要开启 相应的端口。

2、Standby 数据库的DG参数需要手动修改，或者dbca的指定 –initParams log_archive_dest_2=…………

附：清理复制的 database

如果搭建失败，或者要清理掉重现搭建，清理命令如下：

1
dbca -silent -deleteDatabase -sourceDB armdb -sysPassword sysPassword

原文作者: liups.com

原文链接: http://liups.cn/posts/fa646aae/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议