使用rman的duplicate命令搭建物理standby环境，主备库都是RAC环境。
实验环境：
OEL 7.x
Oracle Database 12.2.0.1
前提条件：
1. 主库实例是RAC环境，数据文件存放在ASM磁盘组中。
2. 主库已经配置归档模式。
3. 备库已经安装好软件，并且ASM磁盘组名称与主库相同。
环境规划
| 节点 | 主机名 | 实例名 | 唯一标识 |
|---|---|---|---|
| 主node1 | DB01 | TEST1 | TEST |
| 主node2 | DB02 | TEST2 | |
| 备node1 | RAC01 | NEW1 | NEW |
| 备node2 | RAC02 | NEW2 |
1. 主库创建Standby redo logs
推荐standby redo log的数量应该比每组thread 的 redo log多一个, 本例中主库为2节点有2个thread, 每个thread下有2组redo log, 如下：
SELECT THREAD#,GROUP#,SEQUENCE#,BYTES/1024/1024 AS SIZE_MB,STATUS,FIRST_CHANGE#,FIRST_TIMe,NEXT_CHANGE# FROM V$LOG ORDER BY 1,2;
THREAD# GROUP# SEQUENCE# SIZE_MB STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------- ---------- --------- ---------- ---------------- --------------- ------------------- ------------------------------
1 1 51381 200 INACTIVE 4668559405 2022-12-02 14:30:11 4668564449
1 2 51385 200 CURRENT 4668835933 2022-12-02 14:41:39 18446744073709551615
1 5 51383 200 INACTIVE 4668571651 2022-12-02 14:30:27 4668695693
1 6 51384 200 INACTIVE 4668695693 2022-12-02 14:36:15 4668835933
1 9 51382 200 INACTIVE 4668564449 2022-12-02 14:30:17 4668571651
2 3 50943 200 INACTIVE 4668564718 2022-12-02 14:30:18 4668763482
2 4 50942 200 INACTIVE 4668551549 2022-12-02 14:30:03 4668564718
2 7 50944 200 CURRENT 4668763482 2022-12-02 14:38:39 18446744073709551615
2 8 50941 200 INACTIVE 4668340766 2022-12-02 14:22:20 4668551549
2 10 50940 200 INACTIVE 4668107470 2022-12-02 14:11:35 4668340766
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
2 ONLINE +DATA/MESPRODB/ONLINELOG/group_2.263.1077355717 NO 0
2 ONLINE +FRA/MESPRODB/ONLINELOG/group_2.258.1077355717 NO 0
1 ONLINE +DATA/MESPRODB/ONLINELOG/group_1.262.1077355717 NO 0
1 ONLINE +FRA/MESPRODB/ONLINELOG/group_1.257.1077355717 NO 0
3 ONLINE +DATA/MESPRODB/ONLINELOG/group_3.266.1077355929 NO 0
3 ONLINE +FRA/MESPRODB/ONLINELOG/group_3.259.1077355929 NO 0
4 ONLINE +DATA/MESPRODB/ONLINELOG/group_4.267.1077355931 NO 0
4 ONLINE +FRA/MESPRODB/ONLINELOG/group_4.260.1077355931 NO 0
5 ONLINE +DATA/MESPRODB/ONLINELOG/group_5.297.1077455767 NO 0
5 ONLINE +FRA/MESPRODB/ONLINELOG/group_5.268.1077455767 NO 0
6 ONLINE +DATA/MESPRODB/ONLINELOG/group_6.298.1077455767 NO 0
6 ONLINE +FRA/MESPRODB/ONLINELOG/group_6.269.1077455767 NO 0
7 ONLINE +DATA/MESPRODB/ONLINELOG/group_7.299.1077455767 NO 0
7 ONLINE +FRA/MESPRODB/ONLINELOG/group_7.270.1077455767 NO 0
8 ONLINE +DATA/MESPRODB/ONLINELOG/group_8.300.1077455767 NO 0
8 ONLINE +FRA/MESPRODB/ONLINELOG/group_8.271.1077455769 NO 0
9 ONLINE +DATA/MESPRODB/ONLINELOG/group_9.302.1079623661 NO 0
9 ONLINE +FRA/MESPRODB/ONLINELOG/group_9.337.1079623661 NO 0
10 ONLINE +DATA/MESPRODB/ONLINELOG/group_10.303.1079623661 NO 0
10 ONLINE +FRA/MESPRODB/ONLINELOG/group_10.339.1079623661 NO 0
11 STANDBY +DATA/MESPRODB/ONLINELOG/group_11.304.1079630415 NO 0
12 STANDBY +DATA/MESPRODB/ONLINELOG/group_12.305.1079630417 NO 0
13 STANDBY +DATA/MESPRODB/ONLINELOG/group_13.306.1079630417 NO 0
14 STANDBY +DATA/MESPRODB/ONLINELOG/group_14.307.1079630417 NO 0
15 STANDBY +DATA/MESPRODB/ONLINELOG/group_15.308.1079630417 NO 0
16 STANDBY +DATA/MESPRODB/ONLINELOG/group_16.309.1079630417 NO 0
17 STANDBY +DATA/MESPRODB/ONLINELOG/group_17.310.1079630423 NO 0
18 STANDBY +DATA/MESPRODB/ONLINELOG/group_18.311.1079630423 NO 0
19 STANDBY +DATA/MESPRODB/ONLINELOG/group_19.312.1079630423 NO 0
20 STANDBY +DATA/MESPRODB/ONLINELOG/group_20.313.1079630425 NO 0
21 STANDBY +DATA/MESPRODB/ONLINELOG/group_21.314.1079630425 NO 0
22 STANDBY +DATA/MESPRODB/ONLINELOG/group_22.315.1079630425 NO 0
SYS@> alter database add standby logfile thread 1 group 5 ('+RECO') size 200M, group 6 ('+RECO') size 200M, group 7 ('+RECO') size 200M;
Database altered.
SYS@> alter database add standby logfile thread 2 group 8 ('+RECO') size 200M, group 9 ('+RECO') size 200M, group 10 ('+RECO') size 200M;
Database altered.
select group#,thread#,sequence#,bytes,status from v$standby_log;
GROUP# THREAD# SEQUENCE# BYTES STATUS
---------- ---------- --------- ---------- ----------
11 1 0 209715200 UNASSIGNED
12 1 0 209715200 UNASSIGNED
13 1 0 209715200 UNASSIGNED
14 1 0 209715200 UNASSIGNED
15 1 0 209715200 UNASSIGNED
16 1 0 209715200 UNASSIGNED
17 2 0 209715200 UNASSIGNED
18 2 0 209715200 UNASSIGNED
19 2 0 209715200 UNASSIGNED
20 2 0 209715200 UNASSIGNED
21 2 0 209715200 UNASSIGNED
22 2 0 209715200 UNASSIGNED
2. 主库开启force logging
SYS@> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SYS@> ALTER DATABASE FORCE LOGGING;
Database altered.
SYS@> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
# 关闭force logging使用alter database no force logging
3. 备库临时创建静态监听，用于rman duplicate复制主库。
# 在dm02db01的ORACLE_HOME/network/admin/listener.ora中添加如下内容, 之后启动监听.
DUP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dm02db01.bmwbrill.cn)(PORT = 1999))
)
SID_LIST_DUP =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pcdbns)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
(SID_NAME = pcdbns1)
)
)
ADR_BASE_DUP = /u01/app/oracle
[oracle@dm02db01 admin]$ lsnrctl start DUP
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dm02db01.bmwbrill.cn)(PORT=1999)))
Services Summary...
Service "pcdbns" has 1 instance(s).
Instance "pcdbns1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
# 在主库tnsnames.ora中创建如下别名：
DUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL = TCP)(HOST=dm02db01)(PORT = 1999))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = pcdbns1)
)
)
[oracle@dm01db01 admin]$ tnsping DUP
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL = TCP)(HOST=dm02db01)(PORT = 1999))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = pcdbns1)))
OK (0 msec)
4. 备库创建口令文件
[oracle@dm02db01 dbs]$ orapwd file=orapwpcdbns1 password=welcome1 force=y [oracle@dm02db01 dbs]$ ls -l orapwpcdbns* -rw-r----- 1 oracle oinstall 7680 Oct 10 14:35 orapwpcdbns1
5. 备库创建参数文件和相应目录, 并启动至nomount状态
备库主要修改参数如下：
[oracle@dm02db01 dbs]$ vi initpcdbns1.ora
*.audit_file_dest='/u01/app/oracle/admin/pcdbns/adump'
*.control_files='+DATA/PCDBNS/CONTROLFILE/pcdbns.ctl'
*.db_name='pcdbn'
*.db_unique_name='pcdbns'
*.memory_target=0
*.sga_target=4g
*.parallel_max_servers=10
*.remote_login_passwordfile='exclusive'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='+DATA/PCDBN/DATAFILE','+DATA/PCDBNS/DATAFILE'
*.fal_client='pcdbns'
*.fal_server='pcdbn'
*.log_archive_config='DG_CONFIG=(pcdbns,pcdbn)'
*.log_archive_dest_1='location=+reco VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pcdbns'
*.log_archive_dest_2='SERVICE=pcdbn ASYNC REOPEN=10 NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pcdbn'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+data/pcdbn/onlinelog','+data/pcdbns/onlinelog'
*.standby_file_management='AUTO'
[oracle@dm02db01 dbs]$ mkdir -p /u01/app/oracle/admin/pcdbns/adump
[oracle@dm02db01 dbs]$ export ORACLE_SID=pcdbns1
[oracle@dm02db01 dbs]$ sqlplus / as sysdba
SYS@pcdbns1>startup nomount
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size 2932632 bytes
Variable Size 1325400168 bytes
Database Buffers 2936012800 bytes
Redo Buffers 30621696 bytes
6. 主备库tnsnames.ora设置
PCDBN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL= TCP)(HOST=dm01-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pcdbn)
)
)
PCDBNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL = TCP)(HOST=dm02-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pcdbns)
)
)
--备库2个节点要分别有自己的local_listener，主库就不需要了
PCDBNS_LOCAL_LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL = TCP)(HOST=dm0201-vip)(PORT = 1521))
)
)
7. 临时RESET参数cluster_interconnects
SQL> alter system reset cluster_interconnects scope=spfile sid='pcdbn1';
SQL> alter system reset cluster_interconnects scope=spfile sid='pcdbn2';
8. 主库运行RMAN的duplicate命令创建备库
rman <<EOF
connect target sys/welcome1@dm01-scan:1521/pcdbn;
connect auxiliary sys/welcome1@dup;
run {
duplicate target database for standby nofilenamecheck from active database
spfile
parameter_value_convert 'pcdbn','pcdbns'
set db_unique_name='pcdbns'
set cluster_database='FALSE'
set local_listener='PCDBNS_LOCAL_LISTENER'
set remote_listener='dm02-scan:1521';
}
EOF
9. 还原参数cluster_interconnects
本例cluster_interconnects参数原本没有值，所以无需还原。
10. 停止并移除第3步中创建的DUP监听 和 DUP别名
从库中从listener.ora中移除DUP监听,主库中移除dup别名。
11. 备库copy口令文件至ASM
su - grid
$asmcmd -p
ASMCMD [+] > cd +DATA
AMSCMD [+DATA] >mkdir pcdbn/password
ASMCMD [+DATA] >pwcopy /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwpcdbns1 +DATA/PCDBNS/PASSWORD/orapwpcdbns
ASMCMD [+DATA] > exit
--Remove the original password file.
su - oracle
$rm /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwpcdbns1
12. 将备库修改成cluster模式
--添加下列参数至备库参数文件，使其支持RAC
*.cluster_database=TRUE
pcdbns2.instance_number=2
pcdbns1.instance_number=1
pcdbns2.thread=2
pcdbns1.thread=1
pcdbns2.undo_tablespace='UNDOTBS2'
pcdbns1.undo_tablespace='UNDOTBS1'
create spfile='+DATA/pcdbns/spfilepcdbns.ora' from pfile='/tmp/pcdbns.ora'
[oracle@dm02db01 dbs]$ cat initpcdbns1.ora
pfile='+DATA/pcdbns/spfilepcdbns.ora'
--所有standby节点的pfile文件都需要修改
[oracle@dm02db02 dbs]$ cat initpcdbns2.ora
pfile='+DATA/pcdbns/spfilepcdbns.ora'
13. 将备库注册至CRS，重启备库至mount状态
[oracle@dm02db01 dbs]$ srvctl add database -db pcdbns -oraclehome /u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@dm02db01 dbs]$ srvctl add instance -db pcdbns -instance pcdbns1 -node dm02db01
[oracle@dm02db01 dbs]$ srvctl add instance -db pcdbns -instance pcdbns2 -node dm02db02
[oracle@dm02db01 dbs]$ srvctl modify database -db pcdbns -role physical_standby -spfile '+DATA/pcdbns/spfilepcdbns.ora' -pwfile '+DATA/PCDBNS/PASSWORD/orapwpcdbns'
[oracle@dm02db01 dbs]$ srvctl config database -d pcdbns
Database unique name: pcdbns
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: +DATA/pcdbns/spfilepcdbns.ora
Password file: +DATA/PCDBNS/PASSWORD/orapwpcdbns
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: pcdbns1,pcdbns2
Configured nodes: dm02db01,dm02db02
Database is administrator managed
[oracle@dm02db01 dbs]$ srvctl start database -d pcdbns -o mount
14. 主库配置dataguard相关参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(pcdbn,pcdbns)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+reco VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pcdbn';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=pcdbns LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pcdbns';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set standby_file_management='AUTO';
alter system set db_file_name_convert='+data/pcdbns/datafile','+data/pcdbn/datafile' scope=spfile;
alter system set log_file_name_convert='+data/pcdbns/onlinelog','+data/pcdbn/onlinelog' scope=spfile;
alter system set FAL_SERVER=pcdbns;
alter system set FAL_CLIENT=pcdbn;
15. 测试数据同步，开启ADG
SYS@pcdbns1>alter database recover managed standby database disconnect;
SYS@pcdbns1>select (sysdate-(sysdate-TO_DSINTERVAL(value)))*86400 as lag_seconds from v$dataguard_stats where name = 'apply lag';
LAG_SECONDS
-----------
0
SYS@pcdbns1>select process,status from v$managed_standby where process ='MRP0';
PROCESS STATUS
--------- ------------
MRP0 APPLYING_LOG
SYS@pcdbns1>alter database recover managed standby database cancel;
Database altered.
SYS@pcdbns1>alter database open;
Database altered.
SYS@pcdbns1>alter database recover managed standby database disconnect;
Database altered.
16. 主备库端配置读/写，只读服务
adg环境搭建好之后, 可以利用service来提供不同角色的服务。
workload服务用于提供正常的读写服务，当数据库为主库时workload服务自动启动。
reports服务用于提供只读服务，当数据库角色为备库时reports服务自动启动。
--主库端
srvctl add service -d pcdbn -s workload -r pcdbn1,pcdbn2 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150
srvctl add service -d pcdbn -s reports -r pcdbn1,pcdbn2 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150
srvctl start service -d pcdbn -s reports
srvctl stop service -d pcdbn -s reports
--备库端
srvctl add service -d pcdbns -s workload -r pcdbns1,pcdbns2 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150
srvctl add service -d pcdbns -s reports -r pcdbns1,pcdbns2 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150
srvctl start service -d pcdbns -s reports
17. 客户端tnsnames.ora配置
PRI_PCDBN =
(DESCRIPTION =
(ADDRESS_LIST =
(FAILOVER = ON)
(LOAD_BALANCE = OFF)
(ADDRESS = (PROTOCOL = TCP)(HOST = dm01-scan)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dm02-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = workload)
)
)
STBY_PCDBN =
(DESCRIPTION =
(ADDRESS_LIST =
(FAILOVER = ON)
(LOAD_BALANCE = OFF)
(ADDRESS = (PROTOCOL = TCP)(HOST = dm02-scan)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dm01-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = reports)
)
)
最后修改时间：2022-12-05 09:23:47
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
