基于RAC to RAC 搭建DG详细步骤与注意事项

2020-12-11 10:48:17

基于RAC集群搭建DG 是Oracle MAA典型应用,也是重要系统的常规配置方案，这里我们给出详细的步骤以及注意事项，尤其是监听配置，后续增加资源，我的环境是备库集群环境已经有一个库，是在该集群库上再搭一个DG，需要考虑的因素就多了，大家看详细步骤吧。

环境：
db_name=mytest20
db_unique_name :mytest20 mytest20dg
tns: mytest20pri,mytest20std
dg_instance_name:mytest20dg1 mytest20dg2,mytest20dg3
archive: /gd

1 主库创建standby log file 比主库多一组
alter database add standby logfile thread 1 group 13 '/oradata1/mytest20/redo13_1.log' size 1024m;
alter database add standby logfile thread 1 group 14 '/oradata1/mytest20/redo14_1.log' size 1024m;
alter database add standby logfile thread 1 group 15 '/oradata1/mytest20/redo15_1.log' size 1024m;
alter database add standby logfile thread 1 group 16 '/oradata1/mytest20/redo16_1.log' size 1024m;
alter database add standby logfile thread 1 group 17 '/oradata1/mytest20/redo17_1.log' size 1024m;
alter database add standby logfile thread 2 group 18 '/oradata1/mytest20/redo18_1.log' size 1024m;
alter database add standby logfile thread 2 group 19 '/oradata1/mytest20/redo19_1.log' size 1024m;
alter database add standby logfile thread 2 group 20 '/oradata1/mytest20/redo20_1.log' size 1024m;
alter database add standby logfile thread 2 group 21 '/oradata1/mytest20/redo21_1.log' size 1024m;
alter database add standby logfile thread 2 group 22 '/oradata1/mytest20/redo22_1.log' size 1024m;
alter database add standby logfile thread 3 group 23 '/oradata1/mytest20/redo23_1.log' size 1024m;
alter database add standby logfile thread 3 group 24 '/oradata1/mytest20/redo24_1.log' size 1024m;
alter database add standby logfile thread 3 group 25 '/oradata1/mytest20/redo25_1.log' size 1024m;
alter database add standby logfile thread 3 group 26 '/oradata1/mytest20/redo26_1.log' size 1024m;
alter database add standby logfile thread 3 group 27 '/oradata1/mytest20/redo27_1.log' size 1024m;

2 主库开启日志记录

alter database force logging

2 创建密码文件并发送到备库实施duplicate节点1 $ORACLE_HOME/dbs/orapwmytest20dg1 (mytest20dg1为该节点的ORACLE_SID)
cp /oradata1/mytest20/orapwmytest20 备库节点1：/$ORACLE_HOME/dbs/orapwmytest20dg1
3 创建备库参数文件
基于主库修改
主库:
create pfile='/home/oracle/pfilemytest20dg.ora'' from spfile
拷贝到备库
cp /home/oracle/pfilemytest20dg.ora 备库节点1：/home/oracle/pfilemytest20dg1.ora
修改结果如下：
*._clusterwide_global_transactions=FALSE
*._fix_control='14402409:off'
*._optim_peek_user_binds=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/oracle/admin/mytest20dg/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='12.2.0'
*.control_files='/oradata1/mytest20dg/control01.ctl','/oradata1/mytest20dg/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='mytest20','mytest20dg'
*.db_files=4000
*.db_name='mytest20'
*.db_unique_name='mytest20dg'
*.deferred_segment_creation=FALSE
*.dg_broker_config_file1='+TJASM/DATAGUARDCONFIG/dr1mytest20.dat'
*.dg_broker_config_file2='+TJASM/DATAGUARDCONFIG/dr2mytest20.dat'
*.dg_broker_start=TRUE
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mytest20XDB)'
*.event='28401 TRACE NAME CONTEXT FOREVER, LEVEL 1'
*.fal_client='mytest20std'
*.fal_server='mytest20pri'
*.filesystemio_options='ASYNCH'
family:dw_helper.instance_mode='read-only'
mytest20dg2.instance_number=2
mytest20dg1.instance_number=1
mytest20dg3.instance_number=3
*.local_listener='-oraagent-dummy-'
*.log_archive_config='DG_CONFIG=(mytest20,mytest20dg)'
*.log_archive_dest_1='location=/gd valid_for=(all_logfiles,all_roles) db_unique_name=mytest20dg'
*.log_archive_dest_2='SERVICE=mytest20pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mytest20'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='mytest20','mytest20dg'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.open_links=100
*.parallel_force_local=TRUE
*.pga_aggregate_limit=0
*.pga_aggregate_target=40g
*.processes=3000
*.recyclebin='OFF'
*.remote_listener='mytest22-cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=200
*.sga_target=100g
*.standby_file_management='AUTO'
mytest20dg2.thread=2
mytest20dg3.thread=3
mytest20dg1.thread=1
*.undo_retention=3600
mytest20dg2.undo_tablespace='UNDOTBS2'
mytest20dg3.undo_tablespace='UNDOTBS3'
mytest20dg1.undo_tablespace='UNDOTBS1'

关注如下参数作相应修改

*.db_unique_name='mytest20dg'
*.audit_file_dest='/oracle/admin/mytest20dg/adump'
*.control_files='/oradata1/mytest20dg/control01.ctl','/oradata1/mytest20dg/control02.ctl'
*.db_file_name_convert='mytest20','mytest20dg' <<<<根据实际情况修改
*.log_file_name_convert='mytest20','mytest20dg' <<<<根据实际情况修改
*.fal_client='mytest20std' <<<<基于tnsnames.ora配置
*.fal_server='mytest20pri <<<<基于tnsnames.ora配置
*.log_archive_config='DG_CONFIG=(mytest20,mytest20dg)' <<<<基于db_unique_name
*.log_archive_dest_1='location=/gd valid_for=(all_logfiles,all_roles) db_unique_name=mytest20dg'
*.log_archive_dest_2='SERVICE=mytest20pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mytest20'
*.remote_listener='mytest22-cluster-scan:1521' <<<<基于集群scan配置
mytest20dg2.thread=2
mytest20dg3.thread=3
mytest20dg1.thread=1
mytest20dg2.undo_tablespace='UNDOTBS2'
mytest20dg3.undo_tablespace='UNDOTBS3'
mytest20dg1.undo_tablespace='UNDOTBS1'
mytest20dg2.instance_number=2
mytest20dg1.instance_number=1
mytest20dg3.instance_number=3

4 修改tnsnames.ora文件，配置主备传输tnsnames (mytest20pri,mytest20std, pridup （rman用连接主库节点public ip ）,dup（rman 用辅助实例节点public ip）

基于主库节点1和备库节点1实施，也就是从备库节点1实施rman的duplicate操作，通过主库节点1实例dulicate主库
为了简单，以下配置可以在主库备库的所有节点配置（实际上dup 和pridup只是在实施节点配置）
mytest20PRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.60.71)(PORT = 1521)) <<<<<< scan_ip
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest20)
)
)

mytest20STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.60.79)(PORT = 1521)) <<<<<< scan_ip
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest20dg)
)
)

dup= <<<<<<在地址10.3.60.13所在备库节点1配置
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.60.13)(PORT = 1521)) <<<<<< public_ip
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest20dg)
)
)
pridup=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.60.11)(PORT = 1521)) <<<<<<主库节点1 的 public_ip
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest20)
)
)

5 在备库增加静态监听

vi /grid/12.2/network/admin/listener.ora 增加如下内容，重启监听
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mytest20dg) <<<<<< 备库的db_unique_name
(ORACLE_HOME = /oracle/12.2)
(SID_NAME = mytest20dg1) <<<<<< 备库节点1的orcle_sid
)
)

5 在备库的节点1 <<<<<如果有TNS问题,检查tnsnames.ora配置文件信息，检查地址,tnsname是否准确。
export ORACLE_SID=mytest20dg1
rman target sys/passwd1@pridup auxiliary sys/passwd1@dup
RMAN> duplicate target database for standby from active database nofilenamecheck;
6 备库创建参数文件，备库此时处于mount状态
备库节点1：
sqlplus / as sysdba
create pfile='/home/oracle/p1.ora' from memory;
shutdown immediate;

5 在备库创建共享存储参数文件，密码文件

create spfile='oradata1/mytest20dg/spfilemytest20dg.ora' from pfile='/home/oracle/p1.ora' ;
cp $ORACLE_HOME/dbs/orapwmytest20dg1 /oradata1/mytest20dg/orapwmytest20dg

6 RAC数据后续配置
srvctl add database -db mytest20dg -oraclehome /oracle/12.2
srvctl add instance -db mytest20dg -node n-pc-i620-169 -instance mytest211
srvctl add instance -db mytest20dg -node n-pc-i620-183 -instance mytest212
srvctl add instance -db mytest20dg -node n-pc-i620-197 -instance mytest213
srvctl modify database -db mytest20dg -spfile 'oradata1/mytest20dg/spfilemytest20dg.ora' -pwfile '/oradata1/mytest20dg/orapwmytest20dg'
srvctl start database -db mytest20dg -startoption mount
查看配置
srvctl config database -db mytest20dg

7 启动数据库-备库到Mount状态
srvctl start database -db mytest20dg -o mount

7 配置主库参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(mytest20,mytest20dg)’;
alter system set log_archive_dest_1='location=/gd valid_for=(all_logfiles,all_roles) db_unique_name=mytest20';
alter system set log_archive_dest_2='service=mytest20std async valid_for=(online_logfiles,primary_role) db_unique_name=mytest20dg';
db_unique_name='mytest20' <<<该参数主库默认为db_name

如下参数用于主备切换，如果不需要主备切换，可以不设置
alter system set db_file_name_convert='/oradata1/mytest20dg' , '/oradata1/mytest20' scope=spfile
alter system set log_file_name_convert='/oradata1/mytest20dg' , '/oradata1/mytest20' scope=spfile
alter system set fal_server='mytest20std'
alter system set fal_client='mytest20pri'

8 打开备库
可以单个节点逐个执行，也可以使用srvctl操作，根据个人习惯把
alter database open;
9 启动mrp进程
在备库节点1执行
alter database recover managed standby database disconnect from session;

10 测试是否同步
主库建表，插入数据提交，备库检测