Shulou(Shulou.com)06/01 Report--

Read-write separation cluster

Environment description

The following machines have DM software installed beforehand, the installation path is / dm7, the execution program is saved in the / dm7/bin directory, and the data storage path is / dm7/data

Machine name IP address initial state operating system 18c1 10.13.13.171 (external) main library JY1 redhat 6.7 10.13.13.171 (mal internal) 18c2 10.13.13.172 (external) 10.13.13.171 (mal internal) repository JY2 redhat 6.7dmks 10.13.13.187 confirm monitor redhat 6.7 instance name port_num dw_port mal_host mal_port mal_dw_portjy1 5236 5239 10.13.13.171 5237 5238jy2 5236 5239 10.13.13.172 5237 5238

Data preparation

Initialize the database to the directory / dm7/data on the main library machine:

[dmdba@18c1 bin] $. / dminit path=/dm7/data db_name=jy instance_name=jy1 port_num=5236 page_size=8 charset=0initdb V7.1.6.46-Build (2018.02.08-89107) ENT db version: 0x7000afile dm.key not found, use default licenseurs license will expire in 14 day (s) on 2020-06-17 log file path: / dm7/data/jy/jy01.log log file path: / dm7/data/jy/jy02.logwrite to dir [/ dm7/data/jy] .create dm database success. 2020-06-03 16:01:44

The registration service is used to start the database

[root@18c1 root] #. / dm_service_installer.sh-I / dm7/data/jy/dm.ini-p jy1-t dmserverln-s'/ usr/lib/systemd/system/DmServicejy1.service''/ etc/systemd/system/multi-user.target.wants/DmServicejy1.service'Finished to create the service (DmServicejy1)

Start the database normally and shut down normally

[root@18c1 root] # service DmServicejy1 startRedirecting to / bin/systemctl start DmServicejy1.service [root@18c1 root] # ps-ef | grep dmserverdmdba 29989 1 13 16:04? 00:00:04 / dm7/bin/dmserver / dm7/data/jy/dm.ini-noconsoleroot 30292 3890 0 16:05 pts/1 00:00:00 grep-- color=auto dmserver [dmdba@18c1 bin] $. / disql SYSDBA/SYSDBAServer [LOCALHOST:5236]: mode is normal State is openlogin used time: 8.010 (ms) disql V7.1.6.46-Build (2018.02.08-89107) ENT Connected to: DM 7.1.6.46SQL > exit [root@18c1 root] # service DmServicejy1 stopRedirecting to / bin/systemctl stop DmServicejy1.service

one。 Configuration operation

Main library

Configure the dm.ini file to configure the following parameters

[dmdba@18c1 jy] $vi dmmal.iniINSTANCE_NAME = JY1PORT_NUM = 5236 DW_PORT = 5239 DW_ERROR_TIME = 60 ALTER_MODE_STATUS = 0 ENABLE_OFFLINE_TS = 2 MAL_INI = 1 ARCH_INI = 1 HA_INST_CHECK_FLAG = 1 RLOG_SEND_APPLY_MON = 64

Configure the dmmal.ini file

To configure the MAL system, the dmmal.ini configuration of each master / slave database must be exactly the same. MAL_HOST uses internal network IP,MAL_PORT and PORT_NUM in dm.ini uses different port values. MAL_DW_PORT is the communication port between the daemons corresponding to each instance, and between daemons and monitors. The configuration is as follows:

[dmdba@18c1 jy] $vi dmmal.iniMAL_CHECK_INTERVAL = 5MAL_CONN_FAIL_INTERVAL = 5 [MAL_INST1] MAL_INST_NAME = JY1MAL_HOST = 10.13.13.171MAL_PORT = 5237MAL_INST_HOST = 10.13.13.171MAL_INST_PORT = 5236MAL_DW_PORT = 5238 [MAL_INST2] MAL_INST_NAME = JY2MAL_HOST = 10.13.13.172MAL_PORT = 5237MAL_INST_HOST = 10.13.13.172MAL_INST_PORT = 5236MAL_DW_PORT = 5238

Configure dmarch.ini

Modify dmarch.ini to configure local archiving and real-time archiving. In addition to local archiving, ARCH_DEST in other archiving configuration items indicates that when the instance is in Primary mode, the target instance name of the archived data needs to be synchronized. The current instance DM1 is the main database, and data needs to be synchronized to DM2 (real-time backup database), so the ARCH_DEST for real-time archiving is configured as DM2.

[dmdba@18c1 jy] $vi dmarch.ini [archive _ TIMELY] ARCH_TYPE = TIMELYARCH_DEST = JY2 [archive _ LOCAL1] ARCH_TYPE = LOCALARCH_DEST = / dm7/data/jy/archARCH_FILE_SIZE = 128ARCH_SPACE_LIMIT = 0

Configure dmwatcher.ini

Modify the dmwatcher.ini configuration daemon to be configured as a global daemon type, using automatic switching mode.

[dmdba@18c1 jy] $vi dmwatcher.ini [GRP1] DW_TYPE = GLOBALDW_MODE = AUTODW_ERROR_TIME = 60INST_ERROR_TIME = 10INST_OGUID = 453332INST_INI = / dm7/data/jy/dm.iniINST_AUTO_RESTART = 1INST_STARTUP_CMD = / dm7/bin/dmserverRLOG_SEND_THRESHOLD = 0RLOG_APPLY_THRESHOLD = 0

Configure dmwatcher.ctl

The same daemon group must use the same dmwatcher.ctl. Therefore, you just need to use the dmctlcvt tool to generate a dmwatcher.ctl file and copy it to each database directory. After configuring dmwatcher.ini, use the dmctlcvt tool to generate dmwatcher.ctl: (note that the DEST directory is one level above jy, otherwise no control files are generated)

[dmdba@18c1 bin] $. / dmctlcvt TYPE=3 SRC=/dm7/data/jy/dmwatcher.ini DEST=/dm7/dataDMCTLCVT V7.1.6.46-Build (2018.02.08-89107) ENT convert txt to ctl success!

A GRP1 directory is generated in the / dm7/data directory, and a dmwatcher.ctl control file is generated in the GRP1 directory

[dmdba@18c1 data] $ls-lrttotal 4drwxr-xr-x 6 dmdba dinstall 4096 Jun 3 16:23 jydrwxr-xr-x 2 dmdba dinstall 26 Jun 3 16:23 GRP1 [dmdba@18c1 data] $cd GRP1/ [dmdba@18c1 GRP1] $ls-lrttotal 4Kui Rwashi [dmdba@18c1 GRP1] $cp dmwatcher.ctl / dm7/data/jy/

Copy the generated dmwatcher.ctl file to the data file directory / dm7/data/jy.

Transfer the files related to the main library to the standby:

[dmdba@18c1 dm7] $scp-r data/ dmdba@10.13.13.172:/dm7/The authenticity of host '10.13.13.172 (10.13.13.172)' can't be established.ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.Are you sure you want to continue connecting (yes/no)? YesWarning: Permanently added '10.13.13.172' (ECDSA) to the list of known hosts.dmdba@10.13.13.172's password: dminit20200603160057.log 100% 727 0.7KB/s 00:00 sqllog.ini 479 0.5KB/s 00:00 dm.ctl 100 5120 5.0KB/s 00:00 jy01.log 100 256MB 128.0MB/ S 00:02 jy02.log 100% 256MB 85.3MB/s 00:03 dm _ 20200603160143_364345.ctl 100% 5120 5.0KB/s 00:00 dm_20200603160450_367099.ctl 100% 5120 5.0KB/s 00:00 SYSTEM.DBF 100% 21MB 21.0MB/s 00:00 dm_service.prikey 100% 633 0.6KB/s 00:00 MAIN.DBF 100% 128MB 128.0MB/s 00:01 ROLL.DBF 100% 128MB 128.0MB/s 00:01 dminst.sys 100% 220 0.2KB/s 00:00 TEMP.DBF 100% 10MB 10.0MB/s 00:00 rep_conflict.log 100% 12 0.0KB/s 00:00 dm.ini 100% 40KB 39.8KB/s 00:00 dmmal.ini 100% 558 0.5KB/s 00:00 dmarch.ini 100% 340 0.3KB/s 00:00 dmwatcher.ini 665 0.7KB/s 00:00 dmwatcher.ctl 100 512 0.5KB/s 00:00 dmwatcher.ctl 100% 5120. 5KB/s 00:00 [dmdba@18c1 dm7] $

Modify the relevant configuration of the standby

Modify dm.ini

INSTANCE_NAME = JY2

Modify dmarch.ini

ARCH_DEST = JY1

Dmwatcher.ini,dmwatcher.ctl,dmmal.ini is consistent with the main library without modification 2: boot to mount status setting oguid main library

[dmdba@18c1 bin] $. / dmserver / dm7/data/jy/dm.ini mountfile dm.key not found, use default licenseurs version info: developUse normal os_malloc instead of HugeTLBUse normal os_malloc instead of HugeTLBDM Database Server x64 V7.1.6.46-Build (2.08 / 89107) ENT startup...License will expire in 14 day (s) on 2020-06-17ckpt lsn: 32981SYSTEM IS READY. [dmdba@18c1 bin] $. / disql SYSDBA/SYSDBAServer [LOCALHOST:5236]: mode is normal State is mountlogin used time: 5.995 (ms) disql V7.1.6.46-Build (2018.02.08-89107) ENT Connected to: DM 7.1.6.46SQL > sp_set_oguid (453332) DMSQL executed successfullyused time: 68.576 (ms). Execute id is 1.

Prepare the library

[dmdba@18c2 bin] $. / dmserver / dm7/data/jy/dm.ini mountfile dm.key not found, use default licenseurs version info: developUse normal os_malloc instead of HugeTLBUse normal os_malloc instead of HugeTLBDM Database Server x64 V7.1.6.46-Build (2.08 / 89107) ENT startup...License will expire in 14 day (s) on 2020-06-17ckpt lsn: 32981SYSTEM IS READY. [dmdba@18c2 bin] $. / disql SYSDBA/SYSDBAServer [LOCALHOST:5236]: mode is normal State is mountlogin used time: 6.344 (ms) disql V7.1.6.46-Build (2018.02.08-89107) ENT Connected to: DM 7.1.6.46SQL > sp_set_oguid (453332) DMSQL executed successfullyused time: 32.329 (ms). Execute id is 1.

The registration service is used to start the database

[root@18c2 root] #. / dm_service_installer.sh-I / dm7/data/jy/dm.ini-p jy2-t dmserverln-s'/ usr/lib/systemd/system/DmServicejy2.service''/ etc/systemd/system/multi-user.target.wants/DmServicejy2.service'Finished to create the service (DmServicejy2)

Three: open the database

The main library is opened in primary

SQL > alter database primary;executed successfullyused time: 43.384 (ms). Execute id is 0.

Open the library in standby

SQL > alter database standby;executed successfullyused time: 100.645 (ms). Execute id is 0.

Four: start the daemon

Start the daemons on each primary and standby library:

Main library

-GROUP_NAME TYPE MODE OGUID MPP_FLAG AUTO_RESTART LOCAL_DW_STATUS GRP1 GLOBAL AUTO 453332 FALSE TRUE OPEN INST_OK NAME SVR_MODE SYS_STATUS OPEN_CNT RTYPE N_TASK TASK_MEM_USED FLSN CLSN SLSN SSLSN OK JY1 PRIMARY OPEN 2 TIMELY 0 34412 34412 34412-- -

Prepare the library

[dmdba@18c2 bin] $. / dmwatcher / dm7/data/jy/dmwatcher.iniDMWATCHER [2.1l] V7.1.6.46-Build (2018.02.08-89107) ENT DMWATCHER [2.1l] IS READYshow2020-06-03 16purl 43MUR 05muri- -GROUP_NAME TYPE MODE OGUID MPP_FLAG AUTO_RESTART LOCAL_DW_STATUS GRP1 GLOBAL AUTO 453332 FALSE TRUE OPEN INST_OK NAME SVR_MODE SYS_STATUS OPEN_CNT RTYPE N_TASK TASK_MEM_USED FLSN CLSN SLSN SSLSN OK JY2 STANDBY OPEN 1 TIMELY 0 32981 32981 32981-- -

Five: check whether file_lsn and cur_lsn master / slave libraries are consistent.

Main library

SQL > select file_LSN, cur_LSN from venerable LineID FILE_LSN CUR_LSN-1 34412 34412used time: 1.203 (ms). Execute id is 6.

Prepare the library

SQL > select file_LSN, cur_LSN from venerable LineID FILE_LSN CUR_LSN-1 34412 34412used time: 1.228 (ms). Execute id is 3.

Test data synchronization

Main library:

SQL > create table T1 (id int); executed successfullyused time: 23.402 (ms). Execute id is 4.SQL > insert into T1 values (1); affect rows 1used time: 1.303 (ms). Execute id is 5.SQL > commit;executed successfullyused time: 4.034 (ms). Execute id is 6.SQL > select file_LSN, cur_LSN from venerable LineID FILE_LSN CUR_LSN-1 34443 34443used time: 0.555 (ms). Execute id is 7.

Prepare the library:

SQL > select file_LSN, cur_LSN from venerable LineID FILE_LSN CUR_LSN-1 34443 34443used time: 0.325 (ms). Execute id is 2.SQL > select * from T1 / LINEID ID-1 1used time: 1.373 (ms). Execute id is 3.

VI: configure the monitor (basic requirements, install dm7 software)

Since the daemons of the main library and the real-time standby library are configured for automatic switching mode, the configuration confirmation monitor is selected here. Compared with the ordinary monitor, in addition to the same command support, the confirmation monitor can automatically notify the real-time standby database to take over as the new main library when the main database fails, and has the function of automatic fault handling. Modify the dmmonitor.ini configuration to confirm the monitor, where the IP in MON_DW_IP and the MAL_HOST and MAL_DW_PORT configuration items in PORT and dmmal.ini are consistent.

[dmdba@ora19c data] $vi dmmonitor.ini [dmdba@dmks dmdbms] $vi dmmonitor.iniMON_DW_CONFIRM = 1MON_LOG_PATH = / dm_home/dmdbms/logMON_LOG_INTERVAL = 60MON_LOG_FILE_SIZE = 32MON_LOG_SPACE_LIMIT = 0 [GRP1] MON_INST_OGUID = 453332MON_DW_IP = 10.13.13.171:5238MON_DW_IP = 10.13.13.172 Vol 5238

Start the monitor:

[dmdba@dmks bin] $. / dmmonitor / dm_home/dmdbms/ dmmonitor.ini [monitor] 2020-06-03 10:54:59: DMMONITOR [2.1] V7.1.6.46-Build (2018.02.08-89107) ENT [monitor] 2020-06-03 10:54:59: DMMONITOR [2.1] IS READY. [monitor] 2020-06-03 10:54:59: Received message from (JY1) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN SSLSN SLSN 2020-06-03 16:47:46 OPEN OK JY1 OPEN PRIMARY VALID 2 34443 34443 34443 34443 [monitor] 2020-06-03 10:54:59: Received message from (JY2) WTIME WSTATUS INST_OK INAME ISTATUS IMODE RSTAT N_OPEN FLSN CLSN SSLSN SLSN 2020-06-03 16:47:47 OPEN OK JY2 OPEN STANDBY VALID 2 34443 34443 34443

Two connection attributes have been added to the JDBC connection string:

Whether or not rwSeparate uses a read-write separation system, the default is 0; (0 is not used, 1 is used).

The percentage of transactions that rwPercent distributes to the primary and standby libraries as a percentage of the total transactions in the primary and standby libraries, with a valid value of 0 transactions 100 and a default value of 25.

Let's use jdbc to test the separation of read and write

Package cs;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;public class testrw {/ / driver public static String driver = "dm.jdbc.driver.DmDriver"; / / connection URL public static String url = "jdbc:dm://10.13.13.171:5236?rwSeparate=1&rwPercent=10"; / / Database user public static String username = "SYSDBA" / / Database password public static String password = "SYSDBA"; / * @ param driver * @ param url * @ param username * @ param password * @ return * / public static Connection createConnection (String driver, String url, String username, String password) {Connection connection = null Try {/ / load JDBC driver Class.forName (driver); connection = DriverManager.getConnection (url, username, password);} catch (Exception ex) {ex.printStackTrace (); System.err.println ("Load JDBC Driver Error:" + ex.getMessage ());} return connection } / * * @ param args * / public static void main (String [] args) {Connection connection = createConnection (driver,url,username,password); System.out.println (connection); try {PreparedStatement ps1=connection.prepareStatement ("select * from T2;"); ResultSet rs = ps1.executeQuery (); Statement ps=connection.createStatement () Ps.addBatch ("insert into T2 values (2)"); ps.executeBatch (); String name = ""; while (rs.next ()) {name = rs.getString ("ID"); System.out.println ("ID is:" + name);} rs.close (); ps.close () } catch (Exception ex) {ex.printStackTrace (); System.err.println ("Run SQL Error:" + ex.getMessage ());}

What is executed on the standby database is the query statement execution time is 2020-06-10 22-18-14-000000

SQL > select * from v$sessions LINEID SESS_ID SESS_SEQ SQL_TEXT STATE N_STMT N_USED_STMT SEQ_NO CURR_SCH USER_NAME TRX_ID CREATE_TIME CLNT_TYPE TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL CLNT_HOST APPNAME CLNT_IP OSNAME CONN_TYPE VPOOLADDR RUN_STATUS MSG_STATUS LAST_RECV_TIME LAST_SEND_TIME DCP_FLAG THRD_ID CONNECTED PORT_TYPE SRC_SITE MAL_ID CONCURRENT_FLAG -- -- 1 139663411057416 3 select * from v$sessions ACTIVE 64 1 16 SYSDBA SYSDBA 140737488355329 2020-06-03 16 SQL3 43 SQL3 + 08:00 N Y N N Y N N 1 18c2 disql:: 1 Linux HOMOGENEOUS 139663410989816 RUNNING RECIEVE 2020-06-10 22 NULL 1815 39.000000 2020-06-10 22 22 select * from T2 11 select * from T2 IDLE 64 25 SYSDBA SYSDBA 0 2020-06-10 22 JDBC 18 N Y N Y Y N N 14.000000 JDBC + 08:00 N Y N Y Y N N 1 WIN-ROUOJ6ERFO3:: ffff:10.13.13. Windows Server 2008 R2 HOMOGENEOUS 139663412106040 IDLE SEND 2020-06-10 22 NULL 0used time 18 NULL 0used time 21.000000 2020-06-10 22 22 NULL 0used time: 0.833 (ms) Execute id is 25.

What is executed on the main library is the insert statement. The execution time is 2020-06-10 22.

SQL > select * from v$sessions LINEID SESS_ID SESS_SEQ SQL_TEXT STATE N_STMT N_USED_STMT SEQ_NO CURR_SCH USER_NAME TRX_ID CREATE_TIME CLNT_TYPE TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL CLNT_HOST APPNAME CLNT_IP OSNAME CONN_TYPE VPOOLADDR RUN_STATUS MSG_STATUS LAST_RECV_TIME LAST_SEND_TIME DCP_FLAG THRD_ID CONNECTED PORT_TYPE SRC_SITE MAL_ID CONCURRENT_FLAG -- -- 1 139880043352296 1 select * from v$sessions ACTIVE 64 1 42 SYSDBA SYSDBA 1124 2020-06-03 16 SQL3 41purl 00.000000 SQL3 + 08:00 N Y N N Y N N 1 18c1 disql:: 1 Linux HOMOGENEOUS 139878427790072 RUNNING RECIEVE 2020-06-10 22 NULL 18 insert into 50.000000 2020-06-10 22 22 HOMOGENEOUS 59.000000 N 17205 1065535 insert into T2 values (2) IDLE 64 2 4 SYSDBA SYSDBA 0 2020-06-10 22 WIN-ROUOJ6ERFO3 18 WIN-ROUOJ6ERFO3 13.000000 JDBC + 08:00 N Y N Y Y N N 1 WIN-ROUOJ6ERFO3:: ffff:10.13.13.242 Windows Server 2008 R2 HOMOGENEOUS 139878562007800 IDLE SEND 2020-06-10 22 18 Switzerland 26.000000 2020-06 -10 22 NULL 0used time 18 NULL 0used time 26.000000 N 21802 10 65535 (ms). Execute id is 41.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.