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

Recently, several businesses have gone offline and more than 680 million pieces of data need to be deleted from a table. Try to delete the data, but there is a delay in replication on the corresponding ogg disaster recovery side, and the longer the delay.

For the delay caused by too many tables, all tables can be divided into multiple groups for replication, so I wonder if the replication process can turn on parallelism for single table replication. When the relevant information is found on the Internet, the @ RANGE function can be used to split the single table within the table, and the changes on the table can be evenly distributed to multiple replicat to reduce the load of a single replicat component by using the hash algorithm for the primary key column on the table.

Try it out:

The process of building ogg is no longer repeated and can be found on the Internet.

The experimental process: simulate a large number of dml operations on the table scott.emp1 on the source side, delay the replication process, and open 3 parallel processes on the replication table scott.emp1 on the target side.

Insert data at the source side:

SQL > insert into scott.emp1 select * from scott.emp;14 rows created.SQL > commit;Commit complete.SQL > insert into scott.emp1 select * from scott.emp1;14 rows created.SQL > / 28 rows created.SQL > / .SQL > / 1835008 rows created.SQL > commit;Commit complete.SQL > select count (*) from scott.emp1; COUNT (*)-3670016

There is a delay on the destination side

GGSCI (rhel5) 15 > info allProgram Status Group Lag Time Since ChkptMANAGER RUNNING REPLICAT RUNNING REPTAB 00:09:08 00:00:04

Stop the replication process

Split the replication process and copy the table scott.emp1 into three processes

# Source replication process

GGSCI (rhel5) 23 > view params reptabreplicat reptabSETENV (NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_SID= "orcl") userid ogg,password 123456reperror default,discardassumetargetdefsdiscardfile / goldengate/dirrpt/reptab.dsc,append,megabytes 1024gettruncatesdynamicresolutionmap scott.emp1, target scott.emp1; map scott.emp, target scott.emp

The source process is modified to

Map scott.emp1, target scott.emp1, FILTER (@ RANGE (1penny 3))

Copy two more parameter files:

GGSCI (rhel5) 1 > view params reptab02replicat reptab02SETENV (NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_SID= "orcl") userid ogg,password 123456reperror default,discardassumetargetdefsdiscardfile / goldengate/dirrpt/reptab.dsc,append,megabytes 1024gettruncatesdynamicresolutionmap scott.emp1, target scott.emp1, FILTER (@ RANGE (2Magol 3)) GGSCI (rhel5) 2 > view params reptab03replicat reptab03SETENV (NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_SID= "orcl") userid ogg,password 123456reperror default,discardassumetargetdefsdiscardfile / goldengate/dirrpt/reptab.dsc,append,megabytes 1024gettruncatesdynamicresolutionmap scott.emp1, target scott.emp1, FILTER (@ RANGE (3Magne3))

Add two replication processes, extseqno and extrba consistent with the source process

GGSCI (rhel5) 9 > info reptabREPLICAT REPTAB Last Started 2017-05-05 16:18 Status ABENDEDCheckpoint Lag 00:09:08 (updated 00:09:38 ago) Log Read Checkpoint File. / dirdat/tl000003 2017-05-05 16 info reptabREPLICAT REPTAB Last Started 09187 RBA 194186157 GGSCI (rhel5) 10 > add replicat reptab02, exttrail. / dirdat/tl,extseqno 3 extrba 194186157 checkpointtable ogg.checkpointREPLICAT added.GGSCI (rhel5) 11 > add replicat reptab03, exttrail. / dirdat/tl,extseqno 3 extrba 194186157 Checkpointtable ogg.checkpointREPLICAT added.

Start the replication process

GGSCI (rhel5) 12 > start reptab*Sending START request to MANAGER... REPLICAT REPTAB startingSending START request to MANAGER... REPLICAT REPTAB02 startingSending START request to MANAGER... REPLICAT REPTAB03 starting

Check the session corresponding to ogg in the database

SQL > select module,sql_id from v$session where username='OGG' MODULE SQL_ID -OGG-REPTAB03-OPEN_DATA_SOURCE 1cxrusnmn01hzOGG-REPTAB-OPEN_DATA_SOURCE 1cxrusnmn01hzOGG-REPTAB02-OPEN_DATA_SOURCE 1cxrusnmn01hzSQL > select sql_text from v$sqlarea where sql_id='1cxrusnmn01hz' SQL_TEXT- -INSERT INTO "SCOTT". "EMP1" ("EMPNO") "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") VALUES

You can see that there are three sessions, all corresponding insert statements to the table scott.emp1. That is to say, parallel replication of scott.emp1 tables is implemented.

There are also related documents on MOS to introduce the corresponding functions, documents: 1320133.1 and 1512633.1

Reference: blog.itpub.net/15187685/viewspace-1219731/

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.