当前位置: 首页 > OGG > 正文

ogg 12c拆分integrated extract

下面是自己测试怎么拆分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,记录原extractcheckpoint信息

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进程,不需要从trailrecovery

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.

本文固定链接: http://www.htz.pw/2015/01/07/ogg-12c%e6%8b%86%e5%88%86integrated-extract.html | 认真就输

该日志由 huangtingzhong 于2015年01月07日发表在 OGG 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: ogg 12c拆分integrated extract | 认真就输
关键字: