oracle 11gogg,【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 单向同步配置 上
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.1Configure Oracle Database for
Goldengate
1.1.1Create Goldengate user
Create user ogg for both source system and
target system, and grant appropriate privileges.
為源庫(kù)和目標(biāo)庫(kù)創(chuàng)建ogg用戶并賦予相應(yīng)權(quán)限。
1.1.1.1For Source System(PROD):
創(chuàng)建表空間；創(chuàng)建用戶、賦予權(quán)限
[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:
為源庫(kù)創(chuàng)建測(cè)試表、插入數(shù)據(jù)、創(chuàng)建主鍵約束
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):
創(chuàng)建表空間；創(chuàng)建用戶、賦予權(quán)限
[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.2Enable 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.3Enable 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.4Enable 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.5Enable 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.2Configure Goldengate Manager
Process
1.2.1Configure 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.此端口號(hào)對(duì)應(yīng) 對(duì)端的pump配置端口號(hào)
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.2Configure 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.3Load Initial Data by Direct
Load method
1.3.1Configure 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.2Configure replicat process in target systemrini_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.3Accomplish 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.4Verify 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
Copyright (C) 1995, 2012, Oracle and/or
its affiliates. All rights reserved.
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
Copyright (C) 1995, 2012, Oracle
and/or its affiliates. All rights reserved.
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.5Check 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.6Solution 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.4Configure Extract Process in
Source system
1.4.1Edit extract process parametereora_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 “l(fā)a” 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.2Define GoldenGate local trail
GGSCI (rhel101.oracle.com) 17> ADD EXTTRAIL ./dirdat/la, EXTRACT EORA_1, MEGABYTES 5
EXTTRAIL added.
1.4.3Start 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.5Configure pump process in Source
system
1.5.1Edit data pump process parameterpora_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.2Add 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日
總結(jié)
以上是生活随笔為你收集整理的oracle 11gogg,【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 单向同步配置 上的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： uoj#119. 【UR #8】决战圆锥
- 下一篇： vue 开发环境搭建