Oracle GoldenGate 11g (二)
GoldenGate 11g 單向同步配置上

Item	Source System	Target System
Platform	RHEL6.4 - 64bit	RHEL6.4 - 64bit
Hostname	rhel64.oracle.com	ora11g.oracle.com
Database	Oracle 11.2.0.3	Oracle 11.2.0.3
Character Set	AL32UTF8	AL32UTF8
ORACLE_SID	PROD	EMREP
Listener Name/Port	LISTENER/1521	LISTENER/1521
Goldengate User	ogg	ogg

1.1 Configure Oracle Database for Goldengate

1.1.1 Create Goldengate user

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

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

1.1.1.1 For Source System(PROD):

建立表空間；建立使用者、賦予許可權

[oracle@rhel101 ~]$ sqlplus / as sysdba

SYS@PROD>create tablespace tbs_gguser datafile

'/u01/app/oracle/oradata/PROD/gguser.dbf' 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 alter session to ogg;

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

SYS@PROD>grant flashback any table to ogg;

SYS@PROD>grant execute on dbms_flashback to ogg;

Create test table for Source System:

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

SYS@PROD>conn scott/tiger

SCOTT@PROD>create table ggtab as select * from emp;

SCOTT@PROD>alter table ggtab add constraint pk_ggtab_empno primary key(empno);

SCOTT@PROD>select * from ggtab;

1.1.1.2 For Target System(EMREP):

建立表空間；建立使用者、賦予許可權

[oracle@rhel102 ~]$ sqlplus / as sysdba

SYS@EMREP>create tablespace tbs_gguser datafile

2 '/u01/app/oracle/oradata/EMREP/gguser.dbf' size 50m autoextend on;

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

2 temporary tablespace TEMP quota unlimited on tbs_gguser;

SYS@EMREP>grant connect,resource to ogg;

SYS@EMREP>grant alter session to ogg;

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

SYS@EMREP>grant update any table,delete any table,insert any table to ogg;

Create empty test table

SYS@EMREP>conn scott/tiger

SCOTT@EMREP>create table ggtab as select * from emp where 1=2;

SCOTT@EMREP>alter table ggtab add constraint pk_ggtab_empno primary key(empno);

1.1.2 Enable supplemental log mode in source system

Check that supplemental log mode is enabled by following query:

SYS@PROD>select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

If not, enable it:

SYS@PROD>alter database add supplemental log data;

SYS@PROD>alter system switch logfile;

SYS@PROD>select supplemental_log_data_min from v$database;

1.1.3 Enable archive log mode in source system

Enable archive log mode:

[oracle@rhel101 ~]$ mkdir /u01/app/oracle/arch

[oracle@rhel101 ~]$ ls -ld /u01/app/oracle/arch

SYS@PROD>alter system set log_archive_dest_1='location=/u01/app/oracle/arch';

SYS@PROD>shutdown immediate;

SYS@PROD>startup mount;

SYS@PROD>alter database archivelog;

SYS@PROD>alter database open;

SYS@PROD>alter system archive log current;

SYS@PROD>archive log list;

1.1.4 Enable force logging mode in source system

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

SYS@PROD>alter database force logging;

SYS@PROD>select force_logging from v$database;

1.1.5 Enable transaction data change capture for test table in Source system

GGSCI (rhel101.oracle.com) 11> dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (rhel101.oracle.com) 12> add trandata scott.ggtab

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

GGSCI (rhel101.oracle.com) 13> info trandata scott.ggtab

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

Columns supplementally logged for table SCOTT.GGTAB: EMPNO.

1.2 Configure Goldengate Manager Process

1.2.1 Configure MGR in source system

Create the Manager parameter file.

[oracle@rhel101 11.2.1]$ ggsci

GGSCI (rhel101.oracle.com) 1> edit params mgr

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

PORT 7809

PURGEOLDEXTRACTS ./dirdat, USECHECKPOINTS

Start the Manager

GGSCI (rhel101.oracle.com) 2> start mgr

Verify that the Manager has started.

GGSCI (rhel101.oracle.com) 3> info mgr

Manager is running (IP port rhel101.oracle.com.7809).

1.2.2 Configure MGR in target system

Create the Manager parameter file.

GGSCI (rhel102.oracle.com) 5> edit params mgr

PORT 7809

PURGEOLDEXTRACTS /u01/app/oracle/ggs/11.2.1/dirdat, USECHECKPOINTS

Start the Manager.

GGSCI (rhel102.oracle.com) 6> start mgr

GGSCI (rhel102.oracle.com) 7> info mgr

Manager is running (IP port rhel102.oracle.com.7809).

GGSCI (rhel102.oracle.com) 2> start mgr

Verify that the Manager has started.

GGSCI (rhel102.oracle.com) 3> info mgr

Manager is running (IP port rhel102.oracle.com.7809).

1.3 Load Initial Data by Direct Load method

1.3.1 Configure Extract process in source system

Add an Extract process called EINI_1:

GGSCI (rhel101.oracle.com) 2> add extract eini_1,sourceistable

EXTRACT added.

GGSCI (rhel101.oracle.com) 3> info extract *,tasks

EXTRACT EINI_1 Initialized 2017-01-05 09:25 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

Edit EINI_1:

GGSCI (rhel101.oracle.com) 6> edit params eini_1

Add:

-- GoldenGate Initial Data Capture

-- for scott.ggtab

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

RMTHOST rhel102.oracle.com, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.ggtab;

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

1.3.2 Configure replicat process in target system rini_1

Add initial load delivery process

GGSCI (rhel102.oracle.com) 4> add replicat rini_1,specialrun

REPLICAT added

GGSCI (rhel102.oracle.com) 4> info replicat *,tasks

REPLICAT RINI_1 Initialized 2017-01-05 09:30 Status STOPPED

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

Log Read Checkpoint Not Available

Task SPECIALRUN

Edit initial load delivery process RINI_1：.

GGSCI (rhel102.oracle.com) 6> edit params rini_1

Add:

-- GoldenGate Initial Load Delivery

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

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.

1.3.3 Accomplish Initial Load

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

GGSCI (rhel101.oracle.com) 6> start extract eini_1

Sending START request to MANAGER ...

EXTRACT EINI_1 starting

GGSCI (rhel101.oracle.com) 7> info extract eini_1

EXTRACT EINI_1 Initialized 2017-01-05 09:25 Status RUNNING

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

1.3.4 Verify Initial Load result and process status

GGSCI (rhel101.oracle.com) 11> view report eini_1

2017-01-05 09:35:06 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

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

Oracle GoldenGate Capture for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:32:12

Starting at 2017-01-05 09:35:06

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

Operating System Version:

Linux

Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_64

Node: rhel101.oracle.com

Machine: x86_64

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 6727

Description:

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

** Running with the following parameters **

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

2017-01-05 09:35:06 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ***

RMTHOST rhel102.oracle.com, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.ggtab;

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

2017-01-05 09:35:07 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u01/app/oracle/ggs/11.2.1/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 64G

CACHEPAGEOUTSIZE (normal): 8M

PROCESS VM AVAIL FROM OS (min): 128G

CACHESIZEMAX (strict force to disk): 96G

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

Database Language and Character Set:

NLS_LANG = "AMERICAN_AMERICA.AL32UTF8"

NLS_LANGUAGE = "AMERICAN"

NLS_TERRITORY = "AMERICA"

NLS_CHARACTERSET = "AL32UTF8"

Processing table SCOTT.GGTAB

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

* ** Run Time Statistics ** *

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

Report at 2017-01-05 09:35:19 (activity since 2017-01-05 09:35:07)

Output to RINI_1:

From Table SCOTT.GGTAB:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

REDO Log Statistics

Bytes parsed 0

Bytes output 2588

You can also verify the results in target system:

GGSCI (rhel102.oracle.com) 11> view report rini_1

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

Oracle GoldenGate Delivery for Oracle

Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:37:31

Starting at 2017-01-05 09:34:48

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

Operating System Version:

Linux

Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_64

Node: rhel102.oracle.com

Machine: x86_64

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 6466

Description:

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

** Running with the following parameters **

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

2017-01-05 09:34:53 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

ASSUMETARGETDEFS

USERID ogg, PASSWORD ***

DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE

MAP scott.*, TARGET scott.*;

2017-01-05 09:34:55 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/u01/app/oracle/ggs/11.2.1/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 2G

CACHEPAGEOUTSIZE (normal): 8M

PROCESS VM AVAIL FROM OS (min): 4G

CACHESIZEMAX (strict force to disk): 3.41G

Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

Database Language and Character Set:

NLS_LANG = "AMERICAN_AMERICA.AL32UTF8"

NLS_LANGUAGE = "AMERICAN"

NLS_TERRITORY = "AMERICA"

NLS_CHARACTERSET = "AL32UTF8"

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

** Run Time Messages **

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

Wildcard MAP resolved (entry scott.*):

MAP "SCOTT"."GGTAB", TARGET scott."GGTAB";

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.GGTAB: EMPNO.

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

* ** Run Time Statistics ** *

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

Report at 2017-01-05 09:35:05 (activity since 2017-01-05 09:35:00)

From Table SCOTT.GGTAB to SCOTT.GGTAB:

# inserts: 14

# updates: 0

# deletes: 0

# discards: 0

。。。。。。

Check the initial data has been transferred to Target system:

SCOTT@EMREP>select * from ggtab;

1.3.5 Check process status

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

Source system:

GGSCI (rhel101.oracle.com) 12> info extract eini_1

EXTRACT EINI_1 Last Started 2017-01-05 09:35 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Table SCOTT.GGTAB

2017-01-05 09:35:14 Record 14

Task SOURCEISTABLE

Target System:

GGSCI (rhel102.oracle.com) 12> info replicat rini_1

REPLICAT RINI_1 Initialized 2017-01-05 09:30 Status STOPPED

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

Log Read Checkpoint Not Available

Task SPECIALRUN

1.3.6 Solution for Error

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;

1.4 Configure Extract Process in Source system

1.4.1 Edit extract process parameter eora_1

GGSCI (rhel101.oracle.com) 15> edit params eora_1

Add:

-- Change Capture parameter file to capture ggtbs changes

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

USERID ogg, PASSWORD ogg

EXTTRAIL ./dirdat/la

TABLE scott.ggtbs;

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

GGSCI (rhel101.oracle.com) 69> view params eora_1

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

GGSCI (rhel101.oracle.com) 16> add extract eora_1,tranlog,begin now

EXTRACT added.

1.4.2 Define GoldenGate local trail

GGSCI (rhel101.oracle.com) 17> ADD EXTTRAIL ./dirdat/la, EXTRACT EORA_1, MEGABYTES 5

EXTTRAIL added.

1.4.3 Start primary Extract process

GGSCI (rhel101.oracle.com) 18> start extract eora_1

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

Verify extract process is running or not:

GGSCI (rhel101.oracle.com) 19> info extract eora_1

EXTRACT EORA_1 Last Started 2017-01-05 10:00 Status RUNNING

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

Log Read Checkpoint Oracle Redo Logs

2017-01-05 10:00:27 Seqno 8, RBA 13643776

SCN 0.1111409 (1111409)

GGSCI (rhel101.oracle.com) 20> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

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

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

[oracle@rhel101 ~]$ ll /u01/app/oracle/ggs/11.2.1/dirdat/

total 4

-rw-rw-rw- 1 oracle oinstall 1059 Jan 5 10:00 la000000

1.5 Configure pump process in Source system

1.5.1 Edit data pump process parameter pora_1

GGSCI (rhel101.oracle.com) 20> edit params pora_1

Add:

-- Data Pump parameter file to read the local trail of scott.ggtbs changes

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

PASSTHRU

RMTHOST rhel102.oracle.com, MGRPORT 7809

RMTTRAIL ./dirdat/ra

TABLE scott.ggtab;

Please note that “ra” is prefix for remote trail file.

Add data pump Extract group

GGSCI (rhel101.oracle.com) 21> add extract pora_1,exttrailsource ./dirdat/la

EXTRACT added.

Verify results:

GGSCI (rhel101.oracle.com) 26> info extract pora_1

EXTRACT PORA_1 Last Started 2017-01-05 10:05 Status STOPED

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

Log Read Checkpoint File ./dirdat/la000000

First Record RBA 0

1.5.2 Add GoldenGate remote trail in Source system

GGSCI (rhel101.oracle.com) 24> add rmttrail ./dirdat/ra,extract pora_1,megabytes 5

RMTTRAIL added.

Start data pump process:

GGSCI (rhel101.oracle.com) 25> start extract pora_1

Sending START request to MANAGER ...

EXTRACT PORA_1 starting

GGSCI (rhel101.oracle.com) 35> info extract pora_1

EXTRACT PORA_1 Last Started 2017-01-05 10:11 Status RUNNING

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

Log Read Checkpoint File ./dirdat/la000000

First Record RBA 1059

GGSCI (rhel101.oracle.com) 36> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:08

EXTRACT RUNNING PORA_1 00:00:00 00:00:06

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

[oracle@rhel102 ~]$ ll /u01/app/oracle/ggs/11.2.1/dirdat/

total 0

-rw-rw-rw- 1 oracle oinstall 0 Jan 5 10:11 ra000000

呂星昊

2017年11月20日

【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置上

1.1 Configure Oracle Database for Goldengate

1.1.1 Create Goldengate user

1.1.1.1 For Source System(PROD):

1.1.2 Enable supplemental log mode in source system

1.1.3 Enable archive log mode in source system

1.1.4 Enable force logging mode in source system

1.1.5 Enable transaction data change capture for test table in Source system

1.2 Configure Goldengate Manager Process

1.2.1 Configure MGR in source system

1.2.2 Configure MGR in target system

1.3 Load Initial Data by Direct Load method

1.3.1 Configure Extract process in source system

1.3.2 Configure replicat process in target system rini_1

1.3.3 Accomplish Initial Load

1.3.4 Verify Initial Load result and process status

1.3.5 Check process status

1.3.6 Solution for Error

1.4 Configure Extract Process in Source system

1.4.1 Edit extract process parameter eora_1

1.4.2 Define GoldenGate local trail

1.4.3 Start primary Extract process

1.5 Configure pump process in Source system

1.5.1 Edit data pump process parameter pora_1

1.5.2 Add GoldenGate remote trail in Source system

呂星昊

相關文章

【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 上

1.1 Configure Oracle Database for Goldengate

1.1.1 Create Goldengate user

1.1.1.1 For Source System(PROD):

1.1.2 Enable supplemental log mode in source system

1.1.3 Enable archive log mode in source system

1.1.4 Enable force logging mode in source system

1.1.5 Enable transaction data change capture for test table in Source system

1.2 Configure Goldengate Manager Process

1.2.1 Configure MGR in source system

1.2.2 Configure MGR in target system

1.3 Load Initial Data by Direct Load method

1.3.1 Configure Extract process in source system

1.3.2 Configure replicat process in target system rini_1

1.3.3 Accomplish Initial Load

1.3.4 Verify Initial Load result and process status

1.3.5 Check process status

1.3.6 Solution for Error

1.4 Configure Extract Process in Source system

1.4.1 Edit extract process parameter eora_1

1.4.2 Define GoldenGate local trail

1.4.3 Start primary Extract process

1.5 Configure pump process in Source system

1.5.1 Edit data pump process parameter pora_1

1.5.2 Add GoldenGate remote trail in Source system

呂星昊

相關文章

【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置上