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:
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
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
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
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 |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29475508/viewspace-1290155/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29475508/viewspace-1290155/
扫一扫
121
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
