下面是自己测试怎么拆分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{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}
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：等您坐沙发呢！