dataguard之业务、同步使用不同网络及切换

数据库技术加油站 2023-10-19

144

1、介绍

环境：linux7+11.2.0.4

需求：用户由于数据量比较大，要求不能影响主库，需要将业务和同步区分开来，同步用私有网卡，业务用业务网卡。

2、方案

1）根据需求，主和备服务器增加直连网线。

2）采用rman克隆，限制克隆速度，然后复制到备机。

3、具体实施

一、环境
-------------------------主库--------------------备库
OS系统                    linux                      centos7.9
数据库版本                11.2.0.4                     11.2.0.4
IP                      192.168.133.88              192.168.133.89
软件                    装db、netca、dbca             装db、netca
instance_name              orcl                              orcl
db_name                    orcl                              orcl
db_unique_name             orcl                              orcldg
tnsname.ora              tnsorcl                            tnsdg                           
service_names               orcl                              orcl


二、备库环境准备


2、1 拷贝密码文件到linux


[oracle@ol7 dbs]$ scp orapworcl 192.168.133.88:/u01/app/oracle/product/11.2.0/db_1/dbs


2、2 准备主、备库linux的tnsnames.ora文件


$ vi u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora


tnsorcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1529))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)(ur=a)
    )
  )


tnsdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.20)(PORT = 1529))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)(ur=a)
    )
  )


2、3 准备主、备库的静态监听


####备库####
[oracle@hisdg admin]$ cat listener.ora 
# listener.ora Network Configuration File: u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENERDG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )


LISTENERDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.20)(PORT = 1529))
  )


ADR_BASE_LISTENERDG = u01/app/oracle


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )


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


ADR_BASE_LISTENER = u01/app/oracle




[oracle@hisdg admin]$ lsnrctl stop listenerdg
[oracle@hisdg admin]$ lsnrctl start listenerdg
[oracle@hisdg admin]$ ss -antp|grep 152


####主库####
[oracle@hisdg admin]$ cat listener.ora 
# listener.ora Network Configuration File: u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENERDG =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )


LISTENERDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1529))
  )


ADR_BASE_LISTENERDG = u01/app/oracle


[oracle@his admin]$ lsnrctl start listenerdg
[oracle@his admin]$ ss -antp|grep 152


2、4 准备备库的参数文件，然后启动到nomount状态
[oracle@hisdg oracle]$ cd $ORACLE_HOME/dbs
[oracle@hisdg dbs]$ vi initorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
*.db_files=8192
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=40g
*.db_unique_name='orcldg'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.fal_server='tnsorcl'
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.log_archive_dest_1='LOCATION=/backup/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg'
*.log_archive_dest_2='SERVICE=tnsorcl LGWR SYNC AFFIRM NET_TIMEOUT=15 REOPEN=60 COMPRESSION=ENABLE VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.O7_DICTIONARY_ACCESSIBILITY=TRUE
*.open_cursors=1024
*.pga_aggregate_target=1g
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.service_names='orcl'
*.session_cached_cursors=300
*.sga_max_size=2g
*.sga_target=2g
*.standby_file_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'


mkdir -p u01/app/oracle/admin/orcldg/adump
mkdir -p u01/app/oracle/oradata/orcl/
mkdir -p u01/app/oracle/fast_recovery_area
mkdir -p backup/arch/




sqlplus  as sysdba
SQL> create spfile from pfile;
mv initorcl.ora initorcl.ora_init


[oracle@hisdg trace]$ lsnrctl start listenerdg


三、主库环境准备


3、1 主库设置强制日志
SQL> alter database force logging;
SQL> select force_logging,flashback_on from v$database;


FOR FLASHBACK_ON
--- ------------------
YES YES


3、2 主库创建standby日志


col member for a85;
set line 200 pages 200
select a.thread#,a.group#,a.bytes/1024/1024 as mb,b.member,a.status from v$log a, v$logfile b where a.group#=b.group# order by 1,2;
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB,a.status from v$standby_log a,v$logfile b where a.group#=b.group# order by 1,2;


alter database drop logfile group 2;
alter database add logfile group 1 '/u01/app/oracle/oradata/orcl/redo01.log' size 200m reuse;
alter database add logfile group 2 '/u01/app/oracle/oradata/orcl/redo02.log' size 200m reuse;
alter database add logfile group 3 '/u01/app/oracle/oradata/orcl/redo03.log' size 200m reuse;


alter database add standby logfile group 10 '/u01/app/oracle/oradata/orcl/stb_redo10.log' size 200m;
alter database add standby logfile group 11 '/u01/app/oracle/oradata/orcl/stb_redo11.log' size 200m;  
alter database add standby logfile group 12 '/u01/app/oracle/oradata/orcl/stb_redo12.log' size 200m; 
alter database add standby logfile group 13 '/u01/app/oracle/oradata/orcl/stb_redo13.log' size 200m;


3、3 主库配置tnsnames.ora文件


和备库一样，复制上面的内容


3、4 主库RMAN duplicate数据库 到 linux备库


--前台运行方法
rman target sys/his@tnsorcl auxiliary sys/his@tnsdg
run{
allocate channel c1 type disk rate 50m;
allocate auxiliary channel a1 type disk rate 50m;
duplicate target database for standby from active database nofilenamecheck;
release channel c1;
release channel a1;
}
--相同路径
  duplicate target database for standby from active database nofilenamecheck;
--不同路径
  duplicate target database for standby from active database;


3、5 修改主库参数文件(先克隆数据库到备机，再来改主库参数)
col name for a30
col value for a60
set line 200 pages 200
select name,value from v$parameter where  lower(name) 
in ('log_archive_format','log_archive_dest_1','db_unique_name','log_archive_config','log_archive_dest_2','db_file_name_convert','log_file_name_convert'
,'orcldg_file_management','fal_client','fal_server','log_archive_dest_state_1','log_archive_dest_state_2','redo_transport_user','service_names');


alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)';  
alter system set log_archive_dest_1='location=/backup/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=both; 
alter system set log_archive_dest_2='SERVICE=tnsdg LGWR SYNC AFFIRM NET_TIMEOUT=15 REOPEN=60 COMPRESSION=ENABLE VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' scope=both;
alter system set fal_server='tnsdg' scope=both; 
alter system set standby_file_management=AUTO;  


四、dg状态检查
说明：duplicate数据库之后，备库只是处于mount状态，查看备库状态。


alter database open;
alter database recover managed standby database using current logfile disconnect;
4、1 主备看状态
select open_mode,database_role,db_unique_name from v$database;


4、2 验证物理备库日志应用


1）主库上操作


create table cs (i int);
/
begin
 for i in 1..10000 loop
   insert into cs values (i);
   commit;
   dbms_lock.sleep(2);
  end loop;
end;
/
alter system switch logfile;
archive log list;
2）备库上查看


select * from cs;
archive log list;


select sequence#,first_time,next_time,applied from v$archived_log where applied='NO' order by sequence#;
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

4、dg切换

--1）主库执行，切换成物理备库
Alter database commit to switchover to physical standby with session shutdown;
startup mount;
alter database open;
alter database recover managed standby database using current logfile disconnect;
--2）备库执行，切换成主库
Alter database commit to switchover to primary with session shutdown;
alter database open;


--3）主库修改为业务网段的其它ip
host=`hostname`
ip=192.168.133.89
wk=ens33
sed -ie "s/.*${host}/${ip} ${host}/g" /etc/hosts
echo Y |cp -rf  /etc/sysconfig/network-scripts/ifcfg-${wk} /etc/sysconfig/network-scripts/ifcfg-${wk}_init
sed -ie "s/IPADDR=.*/IPADDR=${ip}/g" /etc/sysconfig/network-scripts/ifcfg-${wk}
systemctl restart network
nmcli c up ${wk}
--4）备库修改为业务ip
host=`hostname`
ip=192.168.133.99
wk=ens33
sed -ie "s/.*${host}/${ip} ${host}/g" /etc/hosts
echo Y |cp -rf  /etc/sysconfig/network-scripts/ifcfg-${wk} /etc/sysconfig/network-scripts/ifcfg-${wk}_init
sed -ie "s/IPADDR=.*/IPADDR=${ip}/g" /etc/sysconfig/network-scripts/ifcfg-${wk}
systemctl restart network
nmcli c up ${wk}
--5）检查配置及业务测试
ss -ltnp|grep 152
cat /etc/sysconfig/network-scripts/ifcfg-ens33
cat /etc/hosts

5、总结

1）相比之前tnsnames.ora使用业务ip的情况，有很大的好处是主备切换后，不用很麻烦的去修改配置文件。切换后只需要修改对应服务器ip即可。

2）业务ip和私有ip区分做dg仅适用于有双网卡的服务器和本地机房。

3）搭建dg过程中，用户的网络带宽占用了影响业务的情况下，我们可以考虑限制备份速度来解决这个问题，如果有备份可以使用scp -lr参数来解决占用带宽问题。

文章转载自数据库技术加油站，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

dataguard之业务、同步使用不同网络及切换

评论