下面是自己测试怎么拆分extract进程，单机环境，当前环境进程是ext2进程同时同步ogg.ogg2两张表，想拆分成ext2同时ogg,ext3同时ogg2进程，下面的记录来至两次的实验，可以时间不一致，但是命令是对的。
1，环境介绍
|
GGSCI (ogg12cnode1 as ogg@ogg12c1) 8> versions Operating System: Linux Version #1 SMP Fri Jan 27 17:17:51 EST 2012, Release 2.6.18-308.el5 Node: ogg12cnode1 Machine: x86_64
Database: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production PL/SQL Release 11.2.0.4.0 – Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 – Production NLSRTL Version 11.2.0.4.0 – Production |
2，插入测试数据
这里使用测试的过程不停向测试表插入数据，用于判断在拆分进程时，数据是否一致性。
|
源数据库执行下面的操作 BEGIN while 1<2 LOOP INSERT INTO htz.ogg VALUES(htz.seq.nextval); INSERT INTO htz.ogg2 VALUES(htz.seq.nextval); COMMIT; dbms_lock.sleep(1); END LOOP; END; / |
3，停extract进程
在停extract进程的时候，我这里是确认current,recovery checkpoint一致后才停的。
|
GGSCI (ogg12cnode1 as ogg@ogg12c1) 30> info extract ext2,showch
EXTRACT EXT2 Last Started 2015-01-06 18:41 Status RUNNING Checkpoint Lag 00:00:02 (updated 00:00:01 ago) Process ID 3369 Log Read Checkpoint Oracle Integrated Redo Logs 2015-01-06 19:17:37 SCN 0.6500640 (6500640)
Current CHECKPOINT DETAIL:
Read Checkpoint #1
Oracle Integrated Redo Log
Startup Checkpoint (starting position in the data source): Timestamp: 2015-01-05 03:09:11.000000 SCN: Not available
Recovery Checkpoint (position of oldest unprocessed transaction in the data source): Timestamp: 2015-01-06 19:17:37.000000 SCN: 0.6500640 (6500640)
Current Checkpoint (position of last record read in the data source): Timestamp: 2015-01-06 19:17:37.000000 SCN: 0.6500640 (6500640) ….. GGSCI (ogg12cnode1 as ogg@ogg12c1) 31> send extract ext2 ,showtrans
Sending SHOWTRANS request to EXTRACT EXT2 … No transactions found .
GGSCI (ogg12cnode1 as ogg@ogg12c1) 32> stop ext2
Sending STOP request to EXTRACT EXT2 … STOP request pending end-of-transaction (1 records so far)..
GGSCI (ogg12cnode1 as ogg@ogg12c1) 36> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT STOPPED EXT2 00:00:02 00:00:25 EXTRACT RUNNING PUMP2 00:00:00 00:00:03 |
4,停pump进程
在停pump进程前一定要确认pump进程已经完成传输完trail文件
|
GGSCI (ogg12cnode1 as ogg@ogg12c1) 42> info extract pump2
EXTRACT PUMP2 Last Started 2015-01-06 18:41 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:07 ago) Process ID 3370 Log Read Checkpoint File ./dirdat/s2000004 2015-01-06 19:20:35.000000 RBA 87523
GGSCI (ogg12cnode1 as ogg@ogg12c1) 45> send extract pump2,status
Sending STATUS request to EXTRACT PUMP2 …
EXTRACT PUMP2 (PID 3370) Current status: Recovery complete: At EOF
Current read position: Sequence #: 4 RBA: 87523 Timestamp: 2015-01-06 19:20:35.000000 Extract Trail: ./dirdat/s2
Current write position: Sequence #: 24 RBA: 87585 Timestamp: 2015-01-06 19:26:40.137463 Extract Trail: ./dirdat/t2 停pump进程 GGSCI (ogg12cnode1 as ogg@ogg12c1) 46> stop pump2
Sending STOP request to EXTRACT PUMP2 … Request processed.
停replicat进程
GGSCI (ogg12cnode2) 35> info replicat rep2
REPLICAT REP2 Last Started 2015-01-06 18:34 Status RUNNING INTEGRATED Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Process ID 3152 Log Read Checkpoint File ./dirdat/t2000024 2015-01-06 19:27:43.994136 RBA 87120 |
5 停replicat进程
这里我将relicat进程一起停了，在停replicat进程前需要确认replicat进程已经完成应用了trail文件
|
GGSCI (ogg12cnode2) 36> send replicat rep2,status
Sending STATUS request to REPLICAT REP2 … Current status: At EOF Sequence #: 24 RBA: 87585 0 records in current transaction
GGSCI (ogg12cnode2) 37> stop replicat rep2
Sending STOP request to REPLICAT REP2 … Request processed. |
6，记录原extract的checkpoint信息
在integrated模式没有rba信息，只有scn的信息了
|
GGSCI (ogg12cnode1 as ogg@ogg12c1) 47> info ext2,showch
EXTRACT EXT2 Last Started 2015-01-06 18:41 Status STOPPED Checkpoint Lag 00:00:02 (updated 00:09:23 ago) Log Read Checkpoint Oracle Integrated Redo Logs 2015-01-06 19:20:35 SCN 0.6501076 (6501076)
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Integrated Redo Log
Startup Checkpoint (starting position in the data source): Timestamp: 2015-01-05 03:09:11.000000 SCN: Not available
Recovery Checkpoint (position of oldest unprocessed transaction in the data source): Timestamp: 2015-01-06 19:20:35.000000 SCN: 0.6501076 (6501076)
Current Checkpoint (position of last record read in the data source): Timestamp: 2015-01-06 19:20:35.000000 SCN: 0.6501076 (6501076) |
7，修改原进程参数
|
GGSCI (ogg12cnode1 as ogg@ogg12c1) 64> edit params ext2 EXTRACT ext2 USERIDALIAS ogg SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/db_1") SETENV (ORACLE_SID = "ogg12c1") USERIDALIAS ogg LOGALLSUPCOLS ogg UPDATERECORDFORMAT COMPACT TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 164) DDL INCLUDE MAPPED DDLOPTIONS REPORT EXTTRAIL ./dirdat/s2 SEQUENCE htz.*; TABLE htz.ogg;
GGSCI (ogg12cnode1 as ogg@ogg12c1) 65> edit params pump2 EXTRACT pump2 USERIDALIAS ogg SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/db_1") SETENV (ORACLE_SID = "ogg12c1") USERIDALIAS ogg LOGALLSUPCOLS ogg RMTHOST 192.168.111.13, MGRPORT 7809 ENCRYPTTRAIL AES128 KEYNAME key1 –RMTHOST 192.168.111.13, MGRPORT 7809 RMTTRAIL ./dirdat/t2 TABLE HTZ.ogg; SEQUENCE HTZ.*;
GGSCI (ogg12cnode2) 1> view params rep2
REPLICAT rep2 SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/db_1") SETENV (ORACLE_SID = "ogg12c2") DBOPTIONS SUPPRESSTRIGGERS DISCARDFILE ./dirdat/discardfile2, PURGE DBOPTIONS INTEGRATEDPARAMS(parallelism 2) –USERIDALIAS ogg USERID ogg,PASSWORD AACAAAAAAAAAAAGAIFAAUDVHCFUGFIYF,BLOWFISH,ENCRYPTKEY DEFAULT DECRYPTTRAIL AES128 KEYNAME key1 HANDLECOLLISIONS ASSUMETARGETDEFS DDLOPTIONS REPORT DDL INCLUDE MAPPED exclude OBJTYPE ‘SEQUENCE’ –MAPEXCLUDE htz.ogg2; MAP htz.ogg, TARGET htz.ogg; –MAP htz.ogg2, TARGET htz.ogg2; |
8，增加extract进程
|
GGSCI (ogg12cnode1 as ogg@ogg12c1) 49> add extract ext3,INTEGRATED TRANLOG, BEGIN NOW EXTRACT added. GGSCI (ogg12cnode1 as ogg@ogg12c1) 51> add exttrail ./dirdat/s3,extract ext3 EXTTRAIL added. GGSCI (ogg12cnode1 as ogg@ogg12c1) 54> add extract pump3,EXTTRAILSOURCE ./dirdat/s3 EXTRACT added. GGSCI (ogg12cnode1 as ogg@ogg12c1) 56> add rmttrail ./dirdat/t3,extract pump3 RMTTRAIL added. GGSCI (ogg12cnode1 as ogg@ogg12c1) 60> view params ext3
EXTRACT ext3 USERIDALIAS ogg SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/db_1") SETENV (ORACLE_SID = "ogg12c1") USERIDALIAS ogg LOGALLSUPCOLS ogg UPDATERECORDFORMAT COMPACT TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 164) DDL INCLUDE MAPPED DDLOPTIONS REPORT EXTTRAIL ./dirdat/s3 –SEQUENCE htz.*; TABLE htz.ogg2;
GGSCI (ogg12cnode1 as ogg@ogg12c1) 63> view params pump3
EXTRACT pump3 USERIDALIAS ogg SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/db_1") SETENV (ORACLE_SID = "ogg12c1") USERIDALIAS ogg LOGALLSUPCOLS ogg RMTHOST 192.168.111.13, MGRPORT 7809 ENCRYPTTRAIL AES128 KEYNAME key1 –RMTHOST 192.168.111.13, MGRPORT 7809 RMTTRAIL ./dirdat/t3 TABLE HTZ.ogg2; –SEQUENCE HTZ.*; |
9，增加replicat进程
|
GGSCI (ogg12cnode1 as ogg@ogg12c1) 184> add extract pump3,EXTTRAILSOURCE ./dirdat/s3 EXTRACT added. GGSCI (ogg12cnode1 as ogg@ogg12c1) 186> add rmttrail ./dirdat/t3,extract pump3 RMTTRAIL added.
GGSCI (ogg12cnode2) 42> view params rep3
REPLICAT rep3 SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/db_1") SETENV (ORACLE_SID = "ogg12c2") DBOPTIONS SUPPRESSTRIGGERS DISCARDFILE ./dirdat/discardfile3, PURGE DBOPTIONS INTEGRATEDPARAMS(parallelism 2) –USERIDALIAS ogg USERID ogg,PASSWORD AACAAAAAAAAAAAGAIFAAUDVHCFUGFIYF,BLOWFISH,ENCRYPTKEY DEFAULT DECRYPTTRAIL AES128 KEYNAME key1 HANDLECOLLISIONS ASSUMETARGETDEFS –DDL INCLUDE MAPPED exclude OBJTYPE ‘SEQUENCE’ MAP htz.ogg2, TARGET htz.ogg2; |
10，删除相应的trail文件
这里需要删除新增加的进程的trail文件信息，有可能原来环境中存在相同的名字。
|
目标端删除新增加replicat进程对应的trail文件，之前没有删除此文件导致目标端的数据增加 [oracle@ogg12cnode2 12.1.2]$ cd dirdat [oracle@ogg12cnode2 dirdat]$ ls -l t3* -rw-r—– 1 oracle oinstall 702157 Jan 7 01:39 t3000000 -rw-r—– 1 oracle oinstall 1185 Jan 7 01:39 t3000001 -rw-r—– 1 oracle oinstall 75867 Jan 7 22:53 t3000002 -rw-r—– 1 oracle oinstall 875 Jan 7 22:53 t3000003 -rw-r—– 1 oracle oinstall 1519 Jan 7 22:53 t3000004 下面此步相当的重要，关系到数据的一致性，之前测试一直失败，目标端多了数据，就是这个原因 去dirdat/目录下删除 s3开头的所有文件 [oracle@ogg12cnode1 12.1.2]$ cd dirdat [oracle@ogg12cnode1 dirdat]$ ls e2000000 s1000032 s1000033 s1000034 s1000035 s1000036 s1000037 s1000038 s1000039 s1000040 s1000041 s2000007 s2000008 s2000009 s2000010 s2000011 s2000012 s3000000 s3000001 s3000002 s3000003 s3000004 s3000005 s3000006 [oracle@ogg12cnode1 dirdat]$ ls -l s3* -rw-r—– 1 oracle oinstall 160332 Jan 6 20:35 s3000000 -rw-r—– 1 oracle oinstall 206926 Jan 6 20:59 s3000001 -rw-r—– 1 oracle oinstall 47765 Jan 6 21:08 s3000002 -rw-r—– 1 oracle oinstall 1767 Jan 6 22:43 s3000003 -rw-r—– 1 oracle oinstall 293026 Jan 7 01:39 s3000004 -rw-r—– 1 oracle oinstall 74388 Jan 7 22:53 s3000005 -rw-r—– 1 oracle oinstall 1457 Jan 7 22:53 s3000006 [oracle@ogg12cnode1 dirdat]$ rm -rf s3* |
11，启动相关进程
|
Sending START request to MANAGER … REPLICAT REP3 starting
GGSCI (ogg12cnode2) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING REPLICAT RUNNING REP2 00:00:00 00:00:02 REPLICAT RUNNING REP3 00:00:00 00:01:04
GGSCI (ogg12cnode2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING REPLICAT RUNNING REP2 00:00:00 00:00:05 REPLICAT RUNNING REP3 00:00:00 00:00:01 GGSCI (ogg12cnode1 as ogg@ogg12c1) 190> start er *
Sending START request to MANAGER … EXTRACT EXT2 starting
Sending START request to MANAGER … EXTRACT EXT3 starting
Sending START request to MANAGER … EXTRACT PUMP2 starting
Sending START request to MANAGER … EXTRACT PUMP3 starting |
12 查看ext3进程日志
下面是一次正确与一次错误的配置的日志信息
|
Database Language and Character Set: NLS_LANGUAGE = "AMERICAN" NLS_TERRITORY = "AMERICA" NLS_CHARACTERSET = "ZHS16GBK"
2015-01-08 03:31:40 INFO OGG-02089 Source redo compatibility version is: 11.2.0.4.
2015-01-08 03:31:41 WARNING OGG-02045 Database does not have streams_pool_size initialization parameter configured.
2015-01-08 03:31:42 INFO OGG-02068 Integrated capture successfully attached to logmining server OGG$CAP_EXT3 using OGGCapture API.
2015-01-08 03:31:42 INFO OGG-02086 Integrated Dictionary will be used.
2015-01-08 03:31:42 INFO OGG-01052 No recovery is required for target file ./dirdat/s3000000, at RBA 0 (file not opened).
2015-01-08 03:31:42 INFO OGG-01478 Output file ./dirdat/s3 is using format RELEASE 12.1.
*********************************************************************** ** Run Time Messages ** ***********************************************************************
2015-01-08 03:31:43 INFO OGG-03522 Setting session time zone to source database time zone ‘GMT’.
2015-01-08 03:31:45 INFO OGG-06507 MAPTABLE resolved (entry htz.ogg2): TABLE "HTZ"."OGG2".
2015-01-08 03:31:45 WARNING OGG-06439 No unique key is defined for table OGG2. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2015-01-08 03:31:45 INFO OGG-06509 Using the following key columns for source table HTZ.OGG2: ID.
2015-01-08 03:32:09 INFO OGG-01021 Command received from GGSCI: STATS.
2015-01-08 03:44:39 INFO OGG-01971 The previous message, ‘INFO OGG-01021’, repeated 1 times.
下面是一次没有删除trail文件的生成的错误日志信息
2015-01-06 22:43:33 INFO OGG-02089 Source redo compatibility version is: 11.2.0.4.
2015-01-06 22:43:34 WARNING OGG-02045 Database does not have streams_pool_size initialization parameter configured.
2015-01-06 22:43:35 INFO OGG-02068 Integrated capture successfully attached to logmining server OGG$CAP_EXT3 using OGGCapture API.
2015-01-06 22:43:35 INFO OGG-02086 Integrated Dictionary will be used. 这里可以看到RECOVERY过程，因为是新增加的extract进程，不需要从trail中recovery。 2015-01-06 22:43:35 INFO OGG-01056 Recovery initialization completed for target file ./dirdat/s3000003, at RBA 1767, CSN 6545189.
2015-01-06 22:43:35 INFO OGG-01478 Output file ./dirdat/s3 is using format RELEASE 12.1.
2015-01-06 22:43:35 WARNING OGG-01438 Checkpoint marked as from graceful shutdown, but records found after checkpoint in trail ./dirdat/s3. Expected EOF Seqno 0, RBA 0. Found Seqno 3, RBA 1767.
2015-01-06 22:43:35 INFO OGG-01026 Rolling over remote file ./dirdat/s3000003.
2015-01-06 22:43:35 INFO OGG-03522 Setting session time zone to source database time zone ‘GMT’.
2015-01-06 22:43:36 INFO OGG-06507 MAPTABLE resolved (entry htz.ogg2): TABLE "HTZ"."OGG2".
2015-01-06 22:43:36 WARNING OGG-06439 No unique key is defined for table OGG2. All viable columns will be used to represent the key, but may not guarantee uniqueness. KE YCOLS may be used to define the key.
2015-01-06 22:43:36 INFO OGG-06509 Using the following key columns for source table HTZ.OGG2: ID.
2015-01-06 22:43:36 INFO OGG-01054 Recovery completed for target file ./dirdat/s3000004, at RBA 1767, CSN 6545189.
2015-01-06 22:43:36 INFO OGG-01057 Recovery completed for all targets. |
13 查看新增加进程的统计信息
|
下面可以看到处理的行一致。 GGSCI (ogg12cnode2) 11> stats replicat rep3
Sending STATS request to REPLICAT REP3 …
Start of Statistics at 2015-01-08 06:49:26.
Integrated Replicat Statistics:
Total transactions 370.00 Redirected 0.00 DDL operations 0.00 Stored procedures 0.00 Datatype functionality 0.00 Event actions 0.00 Direct transactions ratio 0.00%
Replicating from HTZ.OGG2 to HTZ.OGG2:
*** Total statistics since 2015-01-08 06:40:28 *** Total inserts 370.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 370.00
*** Daily statistics since 2015-01-08 06:40:28 *** Total inserts 370.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 370.00
*** Hourly statistics since 2015-01-08 06:40:28 *** Total inserts 370.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 370.00
*** Latest statistics since 2015-01-08 06:40:28 *** Total inserts 370.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 370
GGSCI (ogg12cnode1) 10> stats extract ext3
Sending STATS request to EXTRACT EXT3 …
Start of Statistics at 2015-01-08 03:42:04.
DDL replication statistics (for all trails):
*** Total statistics since extract started *** Operations 0.00 Mapped operations 0.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00
Output to ./dirdat/s3:
Extracting from HTZ.OGG2 to HTZ.OGG2:
*** Total statistics since 2015-01-08 03:31:45 *** Total inserts 370.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 370.00
*** Daily statistics since 2015-01-08 03:31:45 *** Total inserts 370.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 370.00
*** Hourly statistics since 2015-01-08 03:31:45 *** Total inserts 370.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 370.00
*** Latest statistics since 2015-01-08 03:31:45 *** Total inserts 370.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 370.00
End of Statistics. |
ogg 12c拆分integrated extract：等您坐沙发呢！