oracle extis,【学习笔记】Oracle 12C ogg拆分integrated extract案例笔记

【学习笔记】Oracle 12C ogg拆分integrated extract案例笔记

时间:2016-11-03 21:14   来源:Oracle研究中心   作者:HTZ   点击:

次

天萃荷净

Oracle研究中心学习笔记：分享一篇关于Oracle 12C ogg的学习文档，该文档详细记录了Oracle 12C ogg拆分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，记录原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

REPLIOracle о CAT 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

Totl 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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 12C ogg拆分integrated extract案例笔记