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.com/posts/fa646aae/

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