【OGG】Oracle GoldenGate 11g (二) 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 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.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
|
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
|
呂星昊
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29475508/viewspace-2147521/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 下OracleGo
- 【GoldenGate】Oracle GoldenGate(二) 雙向同步配置GoOracle
- Oracle 11g GoldenGate單向複製配置（DML）OracleGo
- Oracle goldengate 11g （二）【DML and DDL單向複製】OracleGo
- 【OGG】Oracle GoldenGate 11g (一) 安裝GoldenGate 11g on LinuxOracleGoLinux
- 【GoldenGate】Oracle GoldenGate(一) 安裝與DML單向同步配置GoOracle
- Oracle GoldenGate10g→11g單向DDL部署OracleGo
- GoldenGate雙向同步配置Go
- oracle goldengate 10g--->11g配置OracleGo
- 【GoldenGate】Oracle GoldenGate(三) DDL同步配置GoOracle
- GoldenGate單向表DML同步Go
- goldengate 單向複製配置Go
- mysql goldengate同步 簡單配置MySqlGo
- Oracle GoldenGate系統之----單向同步資料表OracleGo
- Linux下GoldenGate單機單向同步LinuxGo
- 安裝配置Oracle GoldenGate for DB2(單向)OracleGoDB2
- goldengate單向複製的配置Go
- Oracle 11g RAC到單例項OGG同步Oracle單例
- oracle goldengate 雙向複製配置OracleGo
- GoldenGate配置（二）之雙向複製配置Go
- oracle資料庫配置goldengate同步Oracle資料庫Go
- Oracle goldengate 11g 錯誤彙總OracleGo
- Oracle GoldenGate系統之----單向同步資料表(續)OracleGo
- GoldenGate配置（一）之單向複製配置Go
- Linux下GoldenGate單機雙向同步LinuxGo
- Oracle GoldenGate（OGG）診斷OracleGo
- goldengate 單向DDLGo
- 配置Oracle GoldenGate for DB2(雙向)OracleGoDB2
- oracle goldengate 配置OracleGo
- Oracle GoldenGate 11g官方文件Administrator’s Guide續二OracleGoGUIIDE
- 使用GoldenGate 實現Oracle for Oracle 單向資料同步（實現表的DML操作同步）GoOracle
- Oracle 高可用 goldengate 11g 錯誤彙總OracleGo
- Goldengate單表新增同步Go
- GoldenGate單向複製配置(支援DDL複製)Go
- Oracle GoldenGate系統之----雙向同步資料表OracleGo
- GoldenGate實現oracle和sqlserver雙向資料同步GoOracleSQLServer
- Oracle GoldenGate OGG管理員手冊OracleGo
- 【OGG】RAC環境下配置OGG單向同步 (四)