Oracle GoldenGate 安裝與DML單向同步配置

[實驗環境]

Item	Source System	Target System
Platform	RHEL5.5	RHEL5.5
Hostname	lvxinghao1	lvxinghao2
Database	Oracle10.2.0.1	Oracle 10.2.0.1
Character Set	ZHS16GBK	ZHS16GBK
ORACLE_SID	prod	test1
Listener Name/Port	LISTENER/1521	LISTENER/1521
Goldengate User	ogg	ogg

[實施步驟]

1 Download Goldengate 10g

在oracle使用者的環境變數中增加以下一行：

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib

Download URL:

select a Product Pack: Oracle Fusion Middleware

Platform: Linux x86

Choose: Oracle GoldenGate on Oracle v10.1.1.0.0 Media Pack for Linux x86

If to 11g ,Download:

Oracle GoldenGate V11.1.1.0.0 for Oracle10gon Linux x86 (Part V22227-01)

Oracle GoldenGate V11.1.1.0.0 for Oracle11gon Linux x86 (Part V22228-01)

2 Prepare 10gR2 to 10gR2 test environment

Item	Source System	Target System
Platform	RHEL5.5	RHEL5.5
Hostname	lvxinghao1	lvxinghao2
Database	Oracle10.2.0.1	Oracle 10.2.0.1
Character Set	ZHS16GBK	ZHS16GBK
ORACLE_SID	prod	test1
Listener Name/Port	LISTENER/1521	LISTENER/1521
Goldengate User	ogg	ogg

3 Install Goldengate 10g on Linux

3.1 Create install directory and unzip downloaded file on both system

For source system(prod):

[oracle@lvxinghao1 ~]$ mkdir -p /u01/app/ogg

[oracle@lvxinghao1 ~]$ cp ggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar /u01/app/ogg

[oracle@lvxinghao1 ~]$ cd /u01/app/ogg

[oracle@lvxinghao1 ogg]$ tar -xvf ggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar

For target system(test1):

[oracle@lvxinghao2 ~]$ mkdir -p /u01/app/ogg

[oracle@lvxinghao2 ~]$ cp ggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar /u01/app/ogg/

[oracle@lvxinghao2 ~]$ cd /u01/app/ogg

[oracle@lvxinghao2 ogg]$ tar -xvf ggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar

3.2 Create sub working directories for Goldengate

For both Source system and Target system:

[oracle@lvxinghao1 ogg]$ /u01/app/ogg/ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x86, 32bit (optimized), Oracle 10 on Sep 17 2009 23:49:42

GGSCI (lvxinghao1) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg

Parameter files /u01/app/ogg/dirprm: created

Report files /u01/app/ogg/dirrpt: created

Checkpoint files /u01/app/ogg/dirchk: created

Process status files /u01/app/ogg/dirpcs: created

SQL script files /u01/app/ogg/dirsql: created

Database definitions files /u01/app/ogg/dirdef: created

Extract data files /u01/app/ogg/dirdat: created

Temporary files /u01/app/ogg/dirtmp: created

Veridata files /u01/app/ogg/dirver: created

Veridata Lock files /u01/app/ogg/dirver/lock: created

Veridata Out-Of-Sync files /u01/app/ogg/dirver/oos: created

Veridata Out-Of-Sync XML files /u01/app/ogg/dirver/oosxml: created

Veridata Parameter files /u01/app/ogg/dirver/params: created

Veridata Report files /u01/app/ogg/dirver/report: created

Veridata Status files /u01/app/ogg/dirver/status: created

Veridata Trace files /u01/app/ogg/dirver/trace: created

Stdout files /u01/app/ogg/dirout: created

[oracle@lvxinghao2 ogg]$ /u01/app/ogg/ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x86, 32bit (optimized), Oracle 10 on Sep 17 2009 23:49:42

GGSCI (lvxinghao2) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg

Parameter files /u01/app/ogg/dirprm: created

Report files /u01/app/ogg/dirrpt: created

Checkpoint files /u01/app/ogg/dirchk: created

Process status files /u01/app/ogg/dirpcs: created

SQL script files /u01/app/ogg/dirsql: created

Database definitions files /u01/app/ogg/dirdef: created

Extract data files /u01/app/ogg/dirdat: created

Temporary files /u01/app/ogg/dirtmp: created

Veridata files /u01/app/ogg/dirver: created

Veridata Lock files /u01/app/ogg/dirver/lock: created

Veridata Out-Of-Sync files /u01/app/ogg/dirver/oos: created

Veridata Out-Of-Sync XML files /u01/app/ogg/dirver/oosxml: created

Veridata Parameter files /u01/app/ogg/dirver/params: created

Veridata Report files /u01/app/ogg/dirver/report: created

Veridata Status files /u01/app/ogg/dirver/status: created

Veridata Trace files /u01/app/ogg/dirver/trace: created

Stdout files /u01/app/ogg/dirout: created

Above directories are created for following purpose:

Name	Purpose
dirchk	Checkpoint files
dirdat	GoldenGate trails
dirdef	Data definition files
dirprm	Parameter files
dirpcs	Process status files
dirrpt	Report files
dirsql	SQL script files
dirtmp	Temporary files

3.3 Introduction to the Command Interface

? View HELP summary for all commands

GGSCI> HELP

GGSCI (lvxinghao1) 2> help

GGSCI Command Summary

SUBDIRS CREATE SUBDIRS

ER INFO ER, KILL ER, LAG ER, SEND ER, STATUS ER,

START ER, STATS ER, STOP ER

EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL,

LAG, SEND, START, STATS, STATUS, STOP

EXTTRAIL ADD, ALTER, DELETE, INFO

GGSEVT VIEW

MANAGER INFO, REFRESH, SEND, START, STOP, STATUS

MARKER INFO

PARAMS EDIT, VIEW

REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL,

LAG, SEND, START, STATS, STATUS, STOP

REPORT VIEW

RMTTRAIL ADD, ALTER, DELETE, INFO

TRACETABLE ADD, DELETE, INFO

TRANDATA ADD, DELETE, INFO

Database DBLOGIN, LIST TABLES,

ENCRYPT PASSWORD

DDL DUMPDDL

CHECKPOINTTABLE ADD CHECKPOINTTABLE, DELETE CHECKPOINTTABLE,

CLEANUP CHECKPOINTTABLE, INFO CHECKPOINTTABLE

Miscellaneous FC, HELP, HISTORY, INFO ALL, INFO MARKER, OBEY,

SET, SHELL, SHOW, VERSIONS, !

For help on a specific command, type HELP

Example: HELP ADD REPLICAT

GGSCI> HELP ALL

? View HELP summary for a COMMAND/ENTITY

GGSCI> HELP ADD EXTRACT

GGSCI (lvxinghao1) 32> help add replicat

Use ADD REPLICAT to create a Replicat group. Unless a special run is

specified, ADD REPLICAT creates checkpoints so that processing

continuity is maintained from run to run. See the GoldenGate

Operations Guide for Windows and UNIX for procedures that include

creating Replicat groups.

Syntax:

ADD REPLICAT

{, SPECIALRUN |

, EXTFILE |

, EXTTRAIL }

[, BEGIN |

, EXTSEQNO , EXTRBA ]

[, CHECKPOINTTABLE | NODBCHECKPOINT]

[, PARAMS ]

[, REPORT ]

[, DESC ]

The name of the Replicat group. Follow these naming conventions:

* You can use up to eight ASCII characters, including nonalphanumeric

characters such as the underscore (_). Any ASCII character can be

used, so long as the operating system allows that character to be

in a filename. This is because a group is identified by its

associated checkpoint file.

* Group names are not case-sensitive.

* Use only one word.

* Do not use the word port as a group name. However, you can use

the string port as part of the group name.

* Do not place a numeric value at the end of a group name, such as

fin1, fin10, and so forth. You can place a numeric value at the

beginning of a group name, such as 1_fin, 1fin, and so forth.

ext_1

ex+2t

ex!2t

SPECIALRUN

Creates a Replicat special run. Either SPECIALRUN, EXTFILE,

or EXTTRAIL is required.

EXTFILE

Specifies an extract file that was created with the ADD RMTFILE

command.

EXTTRAIL

Specifies a trail that was created with the ADD RMTTRAIL or ADD

EXTTRAIL command.

BEGIN

Defines an initial checkpoint in the trail.

* To begin replicating changes from when the ADD REPLICAT

command was issued, use the NOW argument.

* To begin extracting changes from a specific time, use the

date-time format of YYYY-MM-DD HH:MM[:SS[.CCCCCC]].

EXTSEQNO

Specifies the sequence number of the file in a trail in which to begin

processing data. Specify the sequence number, not any zeroes used for

padding. For example, if the trail file is c:\ggs\dirdat\aa000026, you

would specify EXTSEQNO 26. By default, processing begins at the

beginning of a trail unless this option is used. To use EXTSEQNO, you

must also use EXTRBA. Contact GoldenGate Technical Support before using

this option.

EXTRBA

Specifies the relative byte address within the trail file specified by

EXTSEQNO. Contact GoldenGate Technical Support before using this

option.

CHECKPOINTTABLE

Specifies that this Replicat group will write checkpoints to the

specified table in the database. Include the owner and table name, as

in hr.hr_checkpoint. This argument overrides the default

CHECKPOINTTABLE specification in the GLOBALS file. The table must be

added with the ADD CHECKPOINTTABLE command.

NODBCHECKPOINT

Specifies that this Replicat group will not write checkpoints to a

checkpoint table. This argument overrides the default CHECKPOINTTABLE

specification in the GLOBALS file.

PARAMS

Specifies a parameter file in a location other than the default of

dirprm within the GoldenGate directory. Use the fully qualified path

name.

REPORT

Specifies a Replicat report file in a location other than the default

of dirrpt within the GoldenGate directory. Use the fully qualified

path name.

DESC

Specifies a description of the group, such as "Loads ACCOUNT_TAB on

Serv2". The description must be within quotes.

Example: ADD REPLICAT sales, EXTTRAIL d:\ggs\dirdat\rt

GGSCI> HELP ADD EXTTRAIL

GGSCI (lvxinghao1) 33> HELP ADD EXTTRAIL

Use ADD EXTTRAIL to create a trail for online processing on the local

system and:

* Associate it with an Extract group.

* Assign a maximum file size.

Note: If the target database is DB2 running on z/OS and you

Will be using a local trail with a Replicat process that reads from

This trail on the same machine, do not use ADD EXTTRAIL. Instead, use

The ADD RMTTRAIL command. ADD EXTTRAIL is not compatible with

Converting data to EBCDIC because it bypasses the Collector process,

Which performs the conversion.

Syntax:

ADD EXTTRAIL , EXTRACT [,MEGABYTES ]

The fully qualified path name of the trail. The actual trail name

can contain only two characters. GoldenGate appends this name with

a six-digit sequence number whenever a new file is created. For

example, a trail named /ggs/dirdat/tr would have files named

/ggs/dirdat/tr000001, /ggs/dirdat/tr000002, and so forth.

The name of the Extract group to which the trail is bound. Only

one Extract process can write data to a trail.

MEGABYTES

The maximum size, in megabytes, of a file in the trail. The

default is 10.

Example: ADD EXTTRAIL c:\ggs\dirdat\aa, EXTRACT finance, MEGABYTES 20

? View your command history

GGSCI> HISTORY

? View a brief informational summary of all processes

GGSCI> INFO ALL

GGSCI (lvxinghao1) 36> info all

Program Status Group Lag Time Since Chkpt

MANAGER STOPPED

4 Configure Oracle Database for Goldengate

4.1 Create Goldengate user

Create user ogg for both source system and target system, and grant appropriate privileges.

為源庫和目標庫建立ogg使用者並賦予相應許可權。

For Source System:

[oracle@lvxinghao1 ~]$ sqlplus '/as sysdba'

SYS@ prod>create tablespace tbs_gguser datafile '/u01/app/oracle/oradata/prod/gguser.dbf'

2 size 50m autoextend on;

SYS@ prod>create user ogg identified by ogg default tablespace tbs_gguser

2 temporary tablespace TEMP quota unlimited on tbs_gguser;

SYS@ prod>grant connect,resource to ogg;

SYS@ prod>grant create session,alter session to ogg;

SYS@ prod>grant select any dictionary,select any table to ogg;

SYS@ prod>grant alter any table to ogg;

SYS@ prod>grant flashback any table to ogg;

SYS@ prod>grant execute on dbms_flashback to ogg;

Insert test table and data for Source System:

為源庫建立測試表、插入資料、建立主鍵約束

SYS@ prod>conn scott/tiger

SCOTT@ prod>create table lvxinghao as select * from emp;

SCOTT@ prod>alter table lvxinghao add constraint lxh_empno_pk primary key(empno);

SCOTT@ prod> select * from lvxinghao;

For Target System:

[oracle@lvxinghao2 ~]$ sqlplus '/as sysdba'

SYS@ test1>create tablespace tbs_gguser datafile '/u01/app/oracle/oradata/test1/gguser.dbf'

2 size 50M autoextend on;

SYS@ test1>create user ogg identified by ogg default tablespace tbs_gguser

2 temporary tablespace TEMP1 quota unlimited on tbs_gguser;

SYS@ test1>grant connect, resource to ogg;

SYS@ test1>grant create session, alter session to ogg;

SYS@ test1>grant select any dictionary, select any table to ogg;

SYS@ test1>grant create table to ogg;

Create empty test table and grant DML privilege to Goldengate user:

SYS@ test1>conn scott/tiger

SCOTT@ test1>create table lvxinghao as select * from emp where 1=2;

SCOTT@ test1>alter table lvxinghao add constraint lxh_empno_pk primary key(empno);

SCOTT@ test1>grant insert, update, delete on scott.lvxinghao to ogg;

better operations：grant update any table,delete any table,insert any table to ogg;

4.2 Enable supplemental log mode in source system

Check supplemental log mode is enabled or not by following query:

SYS@ prod>select supplemental_log_data_min from v$database;

SUPPLEMENTAL_LOG_DATA_MIN

--------------------------------------------------

YES

If not, enable it:

SQL> alter database add supplemental log data;

SQL> alter system switch logfile; this operation is very important

4.3 Enable archive log mode in source system

Enable archive log mode:

SYS@ prod> alter system set log_archive_dest_1='location=/disk1/arch1';

SYS@ prod> shutdown immediate

SYS@ prod> startup mount

SYS@ prod> alter database archivelog;

SYS@ prod> alter database open;

Check database archive log information:

SYS@ prod>alter system archive log current;

System altered.

SYS@ prod>archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /disk1/arch1

Oldest online log sequence 19

Next log sequence to archive 21

Current log sequence 21

SYS@ prod>

4.4 Enable force logging mode in source system

Enable force logging mode: in case of "no logging" operations

SYS@ prod> alter database force logging;

Check force logging mode is enabled or not by following query:

SYS@ prod>select force_logging from v$database;

FORCE_LOGGING

--------------------

YES

Enable transaction data change capture for these two tables in Source system:

GGSCI (lvxinghao1) 1> DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

GGSCI (lvxinghao1) 2> ADD TRANDATA scott.LVXINGHAO

Logging of supplemental redo data enabled for table SCOTT.LVXINGHAO.

Verify that supplemental logging has been turned on for these tables.

GGSCI (lvxinghao1) 3> INFO TRANDATA scott.lvxinghao

Logging of supplemental redo log data is enabled for table SCOTT.LVXINGHAO

5 Configure Goldengate Manager Process

5.1 Configure MGR in source system

Create the Manager parameter file.

[oracle@lvxinghao1 ogg]$ ./ggsci

GGSCI (lvxinghao1) 1> edit params mgr

Use the editor to assign a port. 此埠號對應對端的pump配置埠號

PORT 7809

PURGEOLDEXTRACTS ./dirdat, USECHECKPOINTS

Start the Manager.

GGSCI (lvxinghao1) 2> start mgr

Verify that the Manager has started.

GGSCI (lvxinghao1) 3> info mgr

Manager is running (IP port lvxinghao1.7809).

5.2 Configure MGR in target system

Create the Manager parameter file.

[oracle@lvxinghao2 ~]$ cd /u01/app/ogg

[oracle@lvxinghao2 ogg]$ ./ggsci

GGSCI (lvxinghao2) 1> edit params mgr

Use the editor to assign a port.

PORT 7809

PURGEOLDEXTRACTS /u01/app/ogg/dirdat, USECHECKPOINTS

Start the Manager.

GGSCI (lvxinghao2) 2> start mgr

Verify that the Manager has started.

GGSCI (lvxinghao2) 3> info mgr

Manager is running (IP port lvxinghao2.7809).

6 Load Initial Data by Direct Load method

6.1 Configure Extract process in source system

Add an Extract process called EINI_1:

GGSCI (lvxinghao1) 4> add extract eini_1 sourceistable

EXTRACT added.

Verify Extract process:

GGSCI (lvxinghao1) 5> info extract *,tasks

EXTRACT EINI_1 Initialized 2014-09-13 16:56 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

Edit EINI_1:

GGSCI (lvxinghao1) 6> edit params eini_1

Add:

-- GoldenGate Initial Data Capture

-- for lvxinghao

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

RMTHOST lvxingaho2, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.lvxinghao;

For chinese support, we should set environment variable “NLS_LANG” in Extract/Pump/Replicat process parameters.

6.2 Configure replicat process in target system

Add initial load delivery process

GGSCI (lvxinghao2) 4> add replicat rini_1,specialrun

REPLICAT added.

Verify result:

GGSCI (lvxinghao2) 5> info replicat *,tasks

REPLICAT RINI_1 Initialized 2014-09-13 17:01 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:27 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

Edit initial load delivery process RINI_1：.

GGSCI (lvxinghao2) 6> edit params rini_1

Add:

-- GoldenGate Initial Load Delivery

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ASSUMETARGETDEFS

USERID ogg, PASSWORD ogg

DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE

MAP scott.*, TARGET scott.*;

Note: In the MAP statement, the first owner/schema is for the source and the second for the target.

6.3 Accomplish Initial Load

Start Initial Load process EINI_1 insource system, then RINI_1 process in target system will be started automatically:

GGSCI (lvxinghao1) 8> start extract eini_1

Sending START request to MANAGER ...

EXTRACT EINI_1 starting

6.4 Verify Initial Load result and process status

GGSCI (lvxinghao1) 9> view report eini_1

2014-09-13 17:04:24 GGS INFO 414 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************

Oracle GoldenGate Capture for Oracle

Version 10.4.0.19 Build 002

Linux, x86, 32bit (optimized), Oracle 10 on Sep 18 2009 00:01:59

Starting at 2014-09-13 17:04:24

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Tue Mar 16 22:08:06 EDT 2010, Release 2.6.18-194.el5xen

Node: lvxinghao1

Machine: i686

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 4252

Description:

***********************************************************************

** Running with the following parameters **

***********************************************************************

-- GoldenGate Initial Data Capture

-- for LVXINGHAO

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ***

RMTHOST lvxingaho2, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.lvxinghao;

Using the following key columns for source table SCOTT.LVXINGHAO: EMPNO.

CACHEMGR virtual memory values (may have been adjusted)

CACHEBUFFERSIZE: 64K

CACHESIZE: 2G

CACHEBUFFERSIZE (soft max): 4M

CACHEPAGEOUTSIZE (normal): 4M

PROCESS VM AVAIL FROM OS (min): 2.90G

CACHESIZEMAX (strict force to disk): 2.66G

Database Version:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

Database Language and Character Set:

NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"

NLS_LANGUAGE = "AMERICAN"

NLS_TERRITORY = "AMERICA"

NLS_CHARACTERSET = "ZHS16GBK"

Processing table SCOTT.LVXINGHAO

***********************************************************************

* ** Run Time Statistics ** *

***********************************************************************

Report at 2014-09-13 17:04:40 (activity since 2014-09-13 17:04:24)

Output to RINI_1:

From Table SCOTT.LVXINGHAO:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

You can also verify the results in target system:

GGSCI (lvxinghao2) 7> view report rini_1

***********************************************************************

Oracle GoldenGate Delivery for Oracle

Version 10.4.0.19 Build 002

Linux, x86, 32bit (optimized), Oracle 10 on Sep 18 2009 00:08:30

Starting at 2014-09-13 17:04:25

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Tue Mar 16 22:08:06 EDT 2010, Release 2.6.18-194.el5xen

Node: lvxinghao2

Machine: i686

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 4008

Description:

***********************************************************************

** Running with the following parameters **

***********************************************************************

-- GoldenGate Initial Load Delivery

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ASSUMETARGETDEFS

USERID ogg, PASSWORD ***

DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE

MAP scott.*, TARGET scott.*;

CACHEMGR virtual memory values (may have been adjusted)

CACHEBUFFERSIZE: 64K

CACHESIZE: 512M

CACHEBUFFERSIZE (soft max): 4M

CACHEPAGEOUTSIZE (normal): 4M

PROCESS VM AVAIL FROM OS (min): 1G

CACHESIZEMAX (strict force to disk): 881M

Database Version:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

Database Language and Character Set:

NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK"

NLS_LANGUAGE = "AMERICAN"

NLS_TERRITORY = "AMERICA"

NLS_CHARACTERSET = "ZHS16GBK"

For further information on character set settings, please refer to user manual.

***********************************************************************

** Run Time Messages **

***********************************************************************

Wildcard MAP resolved (entry SCOTT.*):

MAP SCOTT.LVXINGHAO, TARGET scott.LVXINGHAO;

Using following columns in default map by name:

EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

Using the following key columns for target table SCOTT.LVXINGHAO: EMPNO.

***********************************************************************

* ** Run Time Statistics ** *

***********************************************************************

Report at 2014-09-13 17:04:45 (activity since 2014-09-13 17:04:40)

From Table SCOTT.LVXINGHAO to SCOTT.LVXINGHAO:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

Check the initial data has been transferred to Target system:

SCOTT@ test1>select * from lvxinghao;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ---------- --------- ----- --------- ------ ----- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ---------- --------- ----- --------- ------ ----- ----------

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

After initial load, extract process EINI_1 and replicat process RINI_1 stop automatically.

Source system:

GGSCI (lvxinghao1) 10> info extract eini_1

EXTRACT EINI_1 Last Started 2014-09-13 17:04 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Table SCOTT.LVXINGHAO

2014-09-13 17:04:40 Record 14

Task SOURCEISTABLE

Target System:

GGSCI (lvxinghao2) 8> info replicat rini_1

REPLICAT RINI_1 Initialized 2014-09-13 17:01 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:13:01 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

6.5 Solution for Error "parent key not found"

eini_1 parameter should put the parent table first,for example:

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

RMTHOST lvxingaho2, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.dept;

TABLE scott.emp;

7 Configure Extract Process in Source system

7.1 Edit extract process parameter

GGSCI (lvxinghao1) 15> edit params eora_1

Add:

-- Change Capture parameter file to capture

--lvxinghao changes

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

EXTTRAIL ./dirdat/aa

TABLE scott.lvxinghao;

Please note that “aa” is prefix for local trail file.

Execute the following commands in source system to add Primary Extract group.

GGSCI (lvxinghao1) 16> add extract eora_1,tranlog,begin now

EXTRACT added.

7.2 Define GoldenGate local trail

GGSCI (lvxinghao1) 17> ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5

EXTTRAIL added.

7.3 Start primary Extract process

GGSCI (lvxinghao1) 18> start extract eora_1

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

Verify extract process is running or not:

GGSCI (lvxinghao1) 19> info extract eora_1

EXTRACT EORA_1 Last Started 2014-09-13 20:38 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:01 ago)

Log Read Checkpoint Oracle Redo Logs

2014-09-13 20:38:23 Seqno 21, RBA 11492352

Now Goldengate will generate local tail file “aa000000”under dirdat in Source system:

[oracle@lvxinghao1 ~]$ ll /u01/app/ogg/dirdat/

total 4

-rw-rw-rw- 1 oracle oinstall 910 Sep 13 20:38 aa000000

8 Configure pump process in source system

8.1 Edit data pump process parameter

GGSCI (lvxinghao1) 20> edit params pora_1

Add:

-- Data Pump parameter file to read the local

-- trail of lvxinghao changes

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST lvxinghao2, MGRPORT 7809

RMTTRAIL ./dirdat/pa

TABLE scott.lvxinghao;

Add data pump Extract group

GGSCI (lvxinghao1) 21> add extract pora_1,exttrailsource ./dirdat/aa

EXTRACT added.

Verify results:

GGSCI (lvxinghao1) 22> info extract pora_1

EXTRACT PORA_1 Initialized 2014-09-13 20:43 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:20 ago)

Log Read Checkpoint File ./dirdat/aa000000

First Record RBA 0

8.2 Add GoldenGate remote trail in Source system

GGSCI (lvxinghao1) 24> add rmttrail ./dirdat/pa,extract pora_1,megabytes 5

RMTTRAIL added.

Start data pump process:

GGSCI (lvxinghao1) 25> start extract pora_1

Sending START request to MANAGER ...

EXTRACT PORA_1 starting

Verify the results:

GGSCI (lvxinghao1) 36> INFO EXTRACT PORA_1

EXTRACT PORA_1 Last Started 2014-09-13 20:59 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:16:13 ago)

Log Read Checkpoint File ./dirdat/aa000000

First Record RBA 0

Now Goldengate will generate remote tail file “pa000000”under dirdat in Target system:

[oracle@lvxinghao2 ~]$ ll /u01/app/ogg/dirdat/

total 0

-rw-rw-rw- 1 oracle oinstall 0 Sep 13 21:00 pa000000

9 Configure replicat process in target system

9.1 Create GLOBALS parameter in target system

Edit GLOBALS(upper case) parameter file to indicate checkpoint table

GGSCI (lvxinghao2) 11> edit params ./GLOBALS

Add:

CHECKPOINTTABLE ogg.ggschkpt

Verify:

[oracle@lvxinghao2 ogg]$ ll GLOBALS

-rw-rw-rw- 1 oracle oinstall 29 Sep 13 21:04 GLOBALS

For GLOBALS configuration take effect, we must exit GGSCI session:

GGSCI (lvxinghao2) 13> exit

Add replicat checkpoint table in target system:

GGSCI (lvxinghao2) 1> DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

GGSCI (lvxinghao2) 2> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...

Successfully created checkpoint table OGG.GGSCHKPT.

9.2 Edit Delivery process parameter

Add Replicat group:

GGSCI (lvxinghao2) 3>ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa

REPLICAT added.

Edit replicat process RORA_1 parameter:

GGSCI (lvxinghao2) 4> EDIT PARAM RORA_1

Add:

--

-- Change Delivery parameter file to apply

-- lvxinghao Changes

--

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE

MAP scott.lvxinghao, TARGET scott.lvxinghao;

Note: In the MAP statement, the first owner/schema is for the source and the second for the target.

Start Replicat process:

GGSCI (lvxinghao2) 5> START REPLICAT RORA_1

Sending START request to MANAGER ...

REPLICAT RORA_1 starting

Verify:

GGSCI (lvxinghao2) 6> info replicat rora_1

REPLICAT RORA_1 Last Started 2014-09-13 21:09 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint File ./dirdat/pa000000

First Record RBA 0

9.3 Verify if DML can be duplicated correctly

9.3.1 Insert operation

Insert data in source system:

SYS@ prod>conn scott/tiger

SCOTT@ prod> INSERT INTO lvxinghao

VALUES(8000,'Damon','CLERK',7902,sysdate,800,100,20);

SCOTT@ prod> INSERT INTO lvxinghao

VALUES(8001,'Elena','CLERK',7698,sysdate,600,200,30);

SCOTT@ prod> commit;

Check the result in target system:

SCOTT@ prod>select * from lvxinghao;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ------- --------- ----- ------------------- ------ ----- ------

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

8000 Damon CLERK 7902 2014-09-13 21:15:41 800 100 20

8001 Elena CLERK 7698 2014-09-13 21:17:22 600 200 30

16 rows selected.

9.3.2 Update operation

Update data:

SCOTT@ prod>update lvxinghao set ename='lxh' where empno=7788;

SCOTT@ prod>commit;

Check the result in target system:

SCOTT@ test1>select * from lvxinghao;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ------- --------- ----- --------- ------ ----- ------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 lxh ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

8000 Damon CLERK 7902 13-SEP-14 800 100 20

8001 Elena CLERK 7698 13-SEP-14 600 200 30

16 rows selected.

9.3.3 Delete operation

Delete data:

SCOTT@ prod>delete from lvxinghao where empno<7788;

7 rows deleted.

SCOTT@ prod>commit;

Check the result in target system:

SCOTT@ test1>select * from lvxinghao;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ------- --------- ----- --------- ------ ----- ------

7788 lxh ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

8000 Damon CLERK 7902 13-SEP-14 800 100 20

8001 Elena CLERK 7698 13-SEP-14 600 200 30

9 rows selected.

10 Limitation and Process

10.1 Column Width differs in different encoding

In zhs16gbk encoding, two bytes represents one chinese character. However, in AL32UTF8 encoding three bytes represents one chinese character. Therefore, if we INSERT 10 chinese characters in source system (zhs16gbk), which actually occupies 20 bytes, it will become 30 bytes in target system (AL32UTF8). If the column maximum width is 20 bytes, The INSERT operation can succeed in source system, but fail in target system.

List details about process : info replicat rora_1,showch --showch show channel

10.2 List details about process

Source system

GGSCI (lvxinghao1) 41> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:01

EXTRACT RUNNING PORA_1 00:00:00 00:00:05

GGSCI (lvxinghao1) 43> info extract eora_1,showch

EXTRACT EORA_1 Last Started 2014-09-13 20:38 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint Oracle Redo Logs

2014-09-13 21:41:04 Seqno 21, RBA 13795840

Current Checkpoint Detail:

Read Checkpoint #1

Oracle Redo Log

Startup Checkpoint (starting position in the data source):

Sequence #: 21

RBA: 11470864

Timestamp: 2014-09-13 20:36:38.000000

Redo File:

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

Sequence #: 21

RBA: 13795344

Timestamp: 2014-09-13 21:41:04.000000

Redo File: /disk1/oradata/prod/redo02a.log

Current Checkpoint (position of last record read in the data source):

Sequence #: 21

RBA: 13795840

Timestamp: 2014-09-13 21:41:04.000000

Redo File: /disk1/oradata/prod/redo02a.log

Write Checkpoint #1

GGS Log Trail

Current Checkpoint (current write position):

Sequence #: 0

RBA: 2432

Timestamp: 2014-09-13 21:41:08.203754

Extract Trail: ./dirdat/aa

Header:

Version = 2

Record Source = A

Type = 4

# Input Checkpoints = 1

# Output Checkpoints = 1

File Information:

Block Size = 2048

Max Blocks = 100

Record Length = 2048

Current Offset = 0

Configuration:

Data Source = 3

Transaction Integrity = 1

Task Type = 0

Status:

Start Time = 2014-09-13 20:38:06

Last Update Time = 2014-09-13 21:41:08

Stop Status = A

Last Result = 400

GGSCI (lvxinghao1) 44> info extract pora_1,showch

EXTRACT PORA_1 Last Started 2014-09-13 20:59 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:06 ago)

Log Read Checkpoint File ./dirdat/aa000000

2014-09-13 21:28:58.000000 RBA 2432

Current Checkpoint Detail:

Read Checkpoint #1

GGS Log Trail

Startup Checkpoint (starting position in the data source):

Sequence #: 0

RBA: 0

Timestamp: Not Available

Extract Trail: ./dirdat/aa

Current Checkpoint (position of last record read in the data source):

Sequence #: 0

RBA: 2432

Timestamp: 2014-09-13 21:28:58.000000

Extract Trail: ./dirdat/aa

Write Checkpoint #1

GGS Log Trail

Current Checkpoint (current write position):

Sequence #: 0

RBA: 2469

Timestamp: 2014-09-13 21:43:01.820809

Extract Trail: ./dirdat/pa

Header:

Version = 2

Record Source = A

Type = 1

# Input Checkpoints = 1

# Output Checkpoints = 1

File Information:

Block Size = 2048

Max Blocks = 100

Record Length = 2048

Current Offset = 0

Configuration:

Data Source = 0

Transaction Integrity = 1

Task Type = 0

Status:

Start Time = 2014-09-13 20:59:44

Last Update Time = 2014-09-13 21:43:01

Stop Status = A

Last Result = 400

Target System

GGSCI (lvxinghao2) 12> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING RORA_1 00:00:00 00:00:09

GGSCI (lvxinghao2) 14> info replicat rora_1,showch

REPLICAT RORA_1 Last Started 2014-09-13 21:09 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:01 ago)

Log Read Checkpoint File ./dirdat/pa000000

2014-09-13 21:28:58.461077 RBA 2469

Current Checkpoint Detail:

Read Checkpoint #1

GGS Log Trail

Startup Checkpoint (starting position in the data source):

Sequence #: 0

RBA: 0

Timestamp: Not Available

Extract Trail: ./dirdat/pa

Current Checkpoint (position of last record read in the data source):

Sequence #: 0

RBA: 2469

Timestamp: 2014-09-13 21:28:58.461077

Extract Trail: ./dirdat/pa

Header:

Version = 2

Record Source = A

Type = 1

# Input Checkpoints = 1

# Output Checkpoints = 0

File Information:

Block Size = 2048

Max Blocks = 100

Record Length = 2048

Current Offset = 0

Configuration:

Data Source = 0

Transaction Integrity = -1

Task Type = 0

Database Checkpoint:

Checkpoint table = OGG.GGSCHKPT

Key = 421866981 (0x19252de5)

Create Time = 2014-09-13 21:07:43

Status:

Start Time = 2014-09-13 21:09:51

Last Update Time = 2014-09-13 21:37:56

Stop Status = A

Last Result = 400

【GoldenGate】Oracle GoldenGate(一) 安裝與DML單向同步配置

1 Download Goldengate 10g

2 Prepare 10gR2 to 10gR2 test environment

3 Install Goldengate 10g on Linux

3.1 Create install directory and unzip downloaded file on both system

3.2 Create sub working directories for Goldengate

3.3 Introduction to the Command Interface

4 Configure Oracle Database for Goldengate

4.1 Create Goldengate user

4.2 Enable supplemental log mode in source system

4.3 Enable archive log mode in source system

4.4 Enable force logging mode in source system

5 Configure Goldengate Manager Process

5.1 Configure MGR in source system

5.2 Configure MGR in target system

6 Load Initial Data by Direct Load method

6.1 Configure Extract process in source system

6.2 Configure replicat process in target system

6.3 Accomplish Initial Load

6.4 Verify Initial Load result and process status

6.5 Solution for Error "parent key not found"

7 Configure Extract Process in Source system

7.1 Edit extract process parameter

7.2 Define GoldenGate local trail

7.3 Start primary Extract process

8 Configure pump process in source system

8.1 Edit data pump process parameter

8.2 Add GoldenGate remote trail in Source system

9 Configure replicat process in target system

9.1 Create GLOBALS parameter in target system

9.2 Edit Delivery process parameter

9.3 Verify if DML can be duplicated correctly

9.3.1 Insert operation

9.3.2 Update operation

9.3.3 Delete operation

10 Limitation and Process

10.1 Column Width differs in different encoding

10.2 List details about process

相關文章