1.Environment
Item Source System Target System
Platform Redhat 5.4 Redhat 5.4
Hostname gc1 gc2
Database Oracle 11.2.0.1.0 Oracle 11.2.0.1.0
Character Set ZHS16GBK ZHS16GBK
ORACLE_SID PROD EMREP
GoldenGate User ogg ogg
GoldenGate Version 10.4.0.19 Build 002 10.4.0.19 Build 002

2.Install Goldengate
2.1 在Source和Target端创建安装目录并解压安装包mkdir -p /u01/app/oraclecp V18156-01-linux.zip /u01/app/oggunzip V18156-01-linux.ziptar -xvf ggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar
2.2创建子工作目录(Source 和 Target 都要创建)解压并解包成功后在/u01/app/ogg目录执行./ggsci报错找不到libnnz10.so,由于我们的OGG软件是10.4,默认会寻找libnnz10.so,但我们的ORACLE版本为11g,所以创建一个软连接即可解决问题[oracle@gc2:/u01/app/ogg]$ ln -s $ORACLE_HOME/lib/libnnz11.so libnnz10.so 继续报错找不到libclntsh.so.10.1[oracle@gc2:/u01/app/ogg]$ ln -s $ORACLE_HOME/lib/libclntsh.so.11.1libclntsh.so.10.1
[oracle@gc2:/u01/app/ogg]$./ggsciOracleGoldenGate Command Interpreter for OracleVersion10.4.0.19 Build 002Linux,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 (gc1) 1> create subdirs
Creatingsubdirectories under current directory /u01/app/ogg
Parameterfiles /u01/app/ogg/dirprm:created Reportfiles /u01/app/ogg/dirrpt: createdCheckpointfiles /u01/app/ogg/dirchk:created Process status files /u01/app/ogg/dirpcs: createdSQL scriptfiles /u01/app/ogg/dirsql:createdDatabasedefinitions files /u01/app/ogg/dirdef: createdExtract datafiles /u01/app/ogg/dirdat:createdTemporaryfiles /u01/app/ogg/dirtmp:createdVeridatafiles /u01/app/ogg/dirver: createdVeridata Lockfiles /u01/app/ogg/dirver/lock: createdVeridataOut-Of-Sync files /u01/app/ogg/dirver/oos: createdVeridataOut-Of-Sync XML files /u01/app/ogg/dirver/oosxml: createdVeridataParameter files /u01/app/ogg/dirver/params: createdVeridataReport files /u01/app/ogg/dirver/report: createdVeridataStatus files /u01/app/ogg/dirver/status: createdVeridataTrace files /u01/app/ogg/dirver/trace: createdStdoutfiles /u01/app/ogg/dirout: created
以上所创建的目录的作用：
Name Purpose

dirchk Checkpoint files 用来存放检查点(checkpoint)文件,次检查点是ogg自己的检查点与实例不同
dirdat GoldenGate trails 用来存放TRAIL文件
dirdef Data definition files 用来存放通过DEFGEN工具生成的源或目标端数据定义文件
dirprm Parameter files 用来存放配置参数文件
dirpcs Process status files 用来存放进程状态文件
dirrpt Report files 用来存放进程报告文件
dirsql SQL script files 用来存放SQL脚本文件
dirtmp Temporary files 当事物所需要的内存超过已分配内存时,默认存储在这个目录

2.3命令界面介绍
View HELP summary for all commandsGGSCI>HELPGGSCI>HELP ALL View HELP summary for a COMMAND/ENTITYGGSCI>HELP ADD EXTRACTGGSCI>HELP ADD EXTTRAIL View your command historyGGSCI>HISTORY View a brief informational summary of all processesGGSCI>INFO ALL

GGSICI命令 描述
Manager commands 用于启动和管理MGR进程
Extract commands 创建和管理EXTRACT进程组
Replicat commands 创建和管理复制进程组
ER commands 以组的形式统一控制抽取进程组与复制进程组
Trail commands 将Trail文件与抽取进程相关联
Parameter commands 编辑或改变参数文件内容
Database commands 运行数据库相关命令
Trandata commands 对要传输的表添加额外的日志信息供复制进程使用
Checkpoint table commands 创建于管理OGG检查点表
Oracle trace table commands 创建和管理trace表以阻止传输数据形成环路
DDL commands 与DDL同步相关命令
Miscellaneous commands 杂项命令,如shell、create subdirs等

3.Configure Oracle Database for Goldengate
3.1创建GoldenGate用户(Source 和 Target 都要创建)
source:[oracle@gc1:/home/oracle] sqlplus / as sysdbacreate tablespace ogg datafile '/u01/app/oracle/oradata/PROD/disk5/ogg01.dbf' size 50Mautoextend on;create user ogg identified by ogg default tablespace OGG temporarytablespace TEMPTS quota unlimited on OGG;grant CONNECT, RESOURCE to ogg;grant CREATE SESSION, ALTER SESSION to ogg;grant SELECT ANY DICTIONARY, SELECT ANY TABLE toogg;grant ALTER ANY TABLE to ogg;grant FLASHBACK ANY TABLE to ogg;grant EXECUTE on DBMS_FLASHBACK to ogg;
建立测试表:source:SQL>conn scott/tigerSQL> create table emp_ogg as select *from emp;SQL> create table dept_ogg as select *from dept;
source端测试表数据：SQL>select * from emp_ogg;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-------------------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 790217-DEC-80 800 20 7499 ALLEN SALESMAN 769820-FEB-81 1600 300 30 7521 WARD SALESMAN 769822-FEB-81 1250 500 30 7566 JONES MANAGER 783902-APR-81 2975 20 7654 MARTIN SALESMAN 769828-SEP-81 1250 1400 30 7698 BLAKE MANAGER 783901-MAY-81 2850 30 7782 CLARK MANAGER 783909-JUN-81 2450 10 7788 SCOTT ANALYST 756619-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 769808-SEP-81 1500 0 30 7876 ADAMS CLERK 778823-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-------------------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 769803-DEC-81 950 30 7902 FORD ANALYST 756603-DEC-81 3000 20 7934 MILLER CLERK 778223-JAN-82 1300 10
14rows selected.
SQL>select * from dept_ogg;
DEPTNODNAME LOC------------------------ ------------- 10ACCOUNTING NEW YORK 20RESEARCH DALLAS 30SALES CHICAGO 40OPERATIONS BOSTON 50 CUUG BJ
target:[oracle@gc2:/home/oracle] sqlplus / as sysdbacreate tablespace ogg datafile '/u01/app/oracle/oradata/EMREP/ogg01.dbf' size 50Mautoextend on;create user ogg identified by ogg default tablespace OGG temporarytablespace TEMPquota unlimited on OGG;grant CONNECT, RESOURCE to ogg;grant CREATE SESSION, ALTER SESSION to ogg;grant SELECT ANY DICTIONARY, SELECT ANY TABLE toogg;grant CREATE TABLE to ogg;

建立测试表(target端应为与source端):target:SQL>conn scott/tigerSQL> create table emp_ogg as select *from emp where 1=2;SQL> create table dept_ogg as select *from dept where 1=2;SQL> grant INSERT, UPDATE, DELETE onscott.emp_ogg to ogg;SQL> grant INSERT, UPDATE, DELETE onscott.dept_ogg to ogg;
3.2Enable supplemental log mode in source system(注意是Source端)
Check supplemental log mode is enabled or not byfollowing query:sys@PROD>select supplemental_log_data_min from v$database;
SUPPLEME--------YES
如果没开
sys@PROD>alter database add supplemental logdata;sys@PROD>altersystem switch logfile;开启后要切换一次日志
3.3Enable archive log mode in source system
sys@PROD>archivelog listDatabaselog mode Archive ModeAutomaticarchival EnabledArchivedestination /u01/app/oracle/archOldestonline log sequence 497Nextlog sequence to archive 499Currentlog sequence 499
3.4Enable force loggingmode in source system(强制写日志即便加了nologgin)
Check force logging mode is enabled or not byfollowing query:sys@PROD>selectforce_logging from v$database;
FOR---YES
如果没开
sys@PROD>alter database force logging;

Enable transaction data change capture for these twotables in Source system:
GGSCI (gc1) 13> DBLOGIN USERID ogg, PASSWORD oggSuccessfullylogged into database.
GGSCI (gc1) 13> ADD TRANDATA scott.emp_oggLoggingof supplemental redo data enabled for table SCOTT.EMP_OGG.
GGSCI (gc1) 13> ADD TRANDATAscott.dept_oggLoggingof supplemental redo data enabled for table SCOTT.DEPT_OGG.
Verify that supplemental logging has been turned onfor these tables：
GGSCI(gc1) 6> info trandata scott.emp*
Loggingof supplemental redo log data is disabled for table SCOTT.EMP.
Loggingof supplemental redo log data is disabled for table SCOTT.EMP_CLUSTER.
Logging of supplemental redo log data is enabled fortable SCOTT.EMP_OGG
GGSCI(gc1) 7> info trandata scott.dept*
Loggingof supplemental redo log data is disabled for table SCOTT.DEPT.
Loggingof supplemental redo log data is disabled for table SCOTT.DEPT_CLUSTER.
Logging of supplemental redo log data is enabled fortable SCOTT.DEPT_OGG
enable表示启用了supplemental的
4.Configure Goldengate Manager Process
4.1Configure MGR in source system
Create the Manager parameter file.
GGSCI (gc1) >EDIT PARAMS MGR添加内容：PORT 7788PURGEOLDEXTRACTS ./dirdat, USECHECKPOINTS
GGSCI (gc1) > START MGR
GGSCI (gc1) > INFO MGRManageris running (IP port gc1.7788).
系统关闭后要手动启动MGR
4.2Configure MGR in target system
Createthe Manager parameter file.[oracle@gc2 ogg]./ggsciGGSCI (gc2) > EDIT PARAMSMGRUsethe editor to assign a port.PORT 7788PURGEOLDEXTRACTS /u01/app/ogg/dirdat, USECHECKPOINTS
Startthe Manager.GGSCI (gc2) > START MGR
Verifythat the Manager has started.GGSCI (gc2) > INFO MGRManager is running (IP port gc2.7788).
5.Load Initial Data by Direct Load method加载初始数据,意思就是将GC1测试表的数据导入GC2的测试表,测试表要保证结构一致,之后的数据同步不是通过这个进程,此进程同步之后自动stop,之后的同步DML操作是通过后面配置的进程持续的running同步
5.1Configure Extract process in source system
Addan Extract process called EINI_1:GGSCI (gc1) >ADD EXTRACT EINI_1, SOURCEISTABLE

VerifyExtract process:GGSCI (gc1) > INFO EXTRACT *, TASKS
GGSCI (gc1) > EDIT PARAMS EINI_1添加：EXTRACT EINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggRMTHOST gc2,MGRPORT 7788RMTTASK REPLICAT, GROUP RINI_1TABLE scott.EMP_OGG;TABLE scott.DEPT_OGG;
5.2Configure replicat process in target system
Addinitial load delivery processGGSCI (gc2) > ADD REPLICAT RINI_1, SPECIALRUN

Verifyresult:GGSCI (gc2) > INFO REPLICAT *, TASKS
GGSCI (gc2) > EDIT PARAMS RINI_1添加：REPLICAT RINI_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)ASSUMETARGETDEFSUSERID ogg, PASSWORD oggDISCARDFILE ./dirrpt/RINIaa.dsc, PURGEMAP scott.*, TARGET scott.*;
5.3Accomplish Initial Load
StartInitial Load process EINI_1 in source system, then RINI_1 process in targetsystem will be started automatically:GGSCI (gc1) > START EXTRACT EINI_1
SendingSTART request to MANAGER ...EXTRACTEINI_1 starting
5.4Verify Initial Load result and process status
GGSCI(gc1) 34> view report eini_1

2014-06-1715:48:27 GGS INFO 414 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
*********************************************************************** Oracle GoldenGate Capture forOracle Version 10.4.0.19 Build002 Linux, x86, 32bit (optimized), Oracle 10 onSep 18 2009 00:01:59
Copyright(C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

Starting at 2014-06-1715:48:27***********************************************************************
OperatingSystem Version:LinuxVersion#1 SMP Tue Aug 18 15:51:54 EDT 2009, Release 2.6.18-164.el5Node:gc1Machine:i686 soft limit hard limitAddressSpace Size : unlimited unlimitedHeapSize : unlimited unlimitedFileSize : unlimited unlimitedCPUTime : unlimited unlimited
Processid: 3436
Description:
************************************************************************* Running with the followingparameters *************************************************************************EXTRACTEINI_1SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)Setenvironment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERIDogg, PASSWORD ***RMTHOSTgc2, MGRPORT 7788RMTTASKREPLICAT, GROUP RINI_1TABLEscott.emp_ogg;
2014-06-1715:49:24 GGS WARNING 109 No unique key is defined for table EMP_OGG. All viable columns will beused to representthe key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Usingthe following key columns for source table SCOTT.EMP_OGG: EMPNO, ENAME, JOB,MGR, HIREDATE, SAL, COMM, DEPTNO.TABLEscott.dept_ogg;
2014-06-1715:49:25 GGS WARNING 109 No unique key is defined for table DEPT_OGG. All viable columns will beused to representthe key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Usingthe following key columns for source table SCOTT.DEPT_OGG: DEPTNO, DNAME, LOC.
CACHEMGRvirtual memory values (may have been adjusted)CACHEBUFFERSIZE: 64KCACHESIZE: 2GCACHEBUFFERSIZE(soft max): 4MCACHEPAGEOUTSIZE(normal): 4MPROCESSVM AVAIL FROM OS (min): 2.90GCACHESIZEMAX(strict force to disk): 2.66G
DatabaseVersion:OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQLRelease 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNSfor Linux: Version 11.2.0.1.0 - ProductionNLSRTLVersion 11.2.0.1.0 - Production
DatabaseLanguage and Character Set:NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK" NLS_LANGUAGE = "AMERICAN" NLS_TERRITORY = "AMERICA" NLS_CHARACTERSET= "ZHS16GBK"
Processingtable SCOTT.EMP_OGG
Processingtable SCOTT.DEPT_OGG
************************************************************************ ** Run Time Statistics** ************************************************************************

Reportat 2014-06-17 15:52:11 (activity since 2014-06-17 15:49:24)
Outputto RINI_1:
FromTable SCOTT.EMP_OGG: # inserts: 14 # updates: 0 # deletes: 0 # discards: 0FromTable SCOTT.DEPT_OGG: # inserts: 5 # updates: 0 # deletes: 0 # discards: 0
检查target端是否已经成功接收并应用日志：
SQL>select * from emp_ogg;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-------------------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 790217-DEC-80 800 20 7499 ALLEN SALESMAN 769820-FEB-81 1600 300 30 7521 WARD SALESMAN 769822-FEB-81 1250 500 30 7566 JONES MANAGER 783902-APR-81 2975 20 7654 MARTIN SALESMAN 769828-SEP-81 1250 1400 30 7698 BLAKE MANAGER 783901-MAY-81 2850 30 7782 CLARK MANAGER 783909-JUN-81 2450 10 7788 SCOTT ANALYST 756619-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 769808-SEP-81 1500 0 30 7876 ADAMS CLERK 778823-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-------------------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 769803-DEC-81 950 30 7902 FORD ANALYST 756603-DEC-81 3000 20 7934 MILLER CLERK 778223-JAN-82 1300 10
14rows selected.
SQL>select * from dept_ogg;
DEPTNODNAME LOC------------------------ ------------- 10ACCOUNTING NEW YORK 20RESEARCH DALLAS 30SALES CHICAGO 40OPERATIONS BOSTON 50 CUUG BJ
初始数据装在阶段成功后,EXTRACT PROCESS EINI_1 AND REPLICAT PROCESS RNI_1会自动停止
GGSCI(gc1) 38> INFO EXTRACT EINI_1
EXTRACT EINI_1 Last Started 2014-06-17 16:05 Status STOPPEDCheckpointLag Not AvailableLogRead Checkpoint Table SCOTT.DEPT_OGG 2014-06-17 16:05:03 Record 5Task SOURCEISTABLE
GGSCI(gc2) 15> INFO REPLICAT RINI_1
REPLICAT RINI_1 Initialized 2014-06-1715:34 Status STOPPEDCheckpointLag 00:00:00 (updated 00:34:13 ago)LogRead Checkpoint Not AvailableTask SPECIALRUN
6.Configure Extract Process in Source system(持续捕捉变化的EXTRACT进程)
6.1Edit extract process parameter
GGSCI(gc1) 39> edit params eora_1添加：EXTRACT EORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggEXTTRAIL ./dirdat/aaTABLE scott.emp_ogg;TABLE scott.dept_ogg;
6.2创建EXTRACT进程和tail文件,Extract组负责写这份文件,Pump进程组负责读取它GGSCI(gc1) 40> add extract eora_1,tranlog,beginnowEXTRACTadded.
GGSCI(gc1) 44> ADD EXTTRAIL ./dirdat/aa, EXTRACTEORA_1, MEGABYTES 5EXTTRAILadded.
6.3Start primary Extract processGGSCI(gc1) 45> START EXTRACT EORA_1
SendingSTART request to MANAGER ...EXTRACTEORA_1 starting
查看extract process是否运行GGSCI(gc1) 46> info extract *
EXTRACT EORA_1 Last Started 2014-06-17 16:31 Status RUNNINGCheckpointLag 00:14:47 (updated 00:00:06 ago)LogRead Checkpoint Oracle Redo Logs 2014-06-17 16:16:57 Seqno 499, RBA 8651792
成功运行,OGG会产生trail日志[oracle@gc1:/u01/app/ogg/dirdat]$lsaa000000[oracle@gc1:/u01/app/ogg/dirdat]$
7.Configure pump process in source system(配置Pump投递进程组)
7.1Edit data pump process parameterGGSCI(gc1) 47> edit params pora_1
EXTRACT PORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)PASSTHRURMTHOST gc2, MGRPORT 7788RMTTRAIL ./dirdat/paTABLE scott.emp_ogg;TABLE scott.dept_ogg;
添加Pump进程GGSCI(gc1) 48> ADD EXTRACT PORA_1,EXTTRAILSOURCE ./dirdat/aaEXTRACTadded.
查看是否添加成功GGSCI(gc1) 49> INFO EXTRACT PORA_1
EXTRACT PORA_1 Initialized 2014-06-1716:38 Status STOPPEDCheckpointLag 00:00:00 (updated 00:00:33 ago)LogRead Checkpoint File ./dirdat/aa000000 First Record RBA 0
7.2Add GoldenGate remotetrail in Source system(创建远程trail文件,这个文件在source端通过命令创建到target端)GGSCI(gc1) 51> ADD RMTTRAIL ./dirdat/pa, EXTRACTPORA_1, MEGABYTES 5RMTTRAILadded.
启动Pump processGGSCI(gc1) 52> START EXTRACT PORA_1
SendingSTART request to MANAGER ...EXTRACTPORA_1 starting
查看是否运行GGSCI(gc1) 53> INFO EXTRACT PORA_1
EXTRACT PORA_1 Last Started 2014-06-17 16:40 Status RUNNINGCheckpointLag 00:00:00 (updated 00:00:04 ago)LogRead Checkpoint File ./dirdat/aa000000 First Record RBA 899

[oracle@gc2:/u01/app/ogg/dirdat]$ls 注意远程trail文件在gc2pa000000
8.Configure replicat process in target system
8.1Create GLOBALSparameter in target system(用于添加checkpoint表)
GGSCI(gc2) 17> EDIT PARAMS ./GLOBALS添加：CHECKPOINTTABLE ogg.ggschkpt
查看文件：[oracle@gc2:/u01/app/ogg]$ll GLOBALS -rw-rw-rw-1 oracle oinstall 29 Jun 17 16:48 GLOBALS
For GLOBALS configuration take effect, we must exitGGSCI session:
GGSCI(gc2) 20> exit[oracle@gc2:/u01/app/ogg]$./ggsci
OracleGoldenGate Command Interpreter for OracleVersion10.4.0.19 Build 002Linux,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(gc2) 1> dblogin userid ogg,password oggSuccessfullylogged into database.
GGSCI(gc2) 2> add checkpointtable
Nocheckpoint table specified, using GLOBALS specification (ogg.ggschkpt)...
Successfullycreated checkpoint table OGG.GGSCHKPT.
8.2Edit Delivery processparameter(开始配置Replicat复制进程组)
添加Replicat复制进程：GGSCI(gc2) 3> ADD REPLICAT RORA_1, EXTTRAIL./dirdat/paREPLICATadded.
编辑参数：GGSCI(gc2) 4> EDIT PARAM RORA_1
REPLICAT RORA_1SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)USERID ogg, PASSWORD oggHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/RORA_aa.DSC, PURGEMAP scott.*, TARGET scott.*;
启动：GGSCI(gc2) 5> START REPLICAT RORA_1
SendingSTART request to MANAGER ...REPLICATRORA_1 starting
查看运行状态：GGSCI(gc2) 6> INFO REPLICAT RORA_1
REPLICAT RORA_1 Initialized 2014-06-1717:00 Status RUNNINGCheckpointLag 00:00:00 (updated 00:02:28 ago)LogRead Checkpoint File ./dirdat/pa000000 First Record RBA 0

9.测试单向配置
9.1在source端测试表中插入数据
scott@PROD>insert into dept_ogg values(60,'GUUC','TOKYO');
1row created.
scott@PROD>insert into emp_oggvalues(8000,'FAN','BOSS',null,sysdate,8000,500,50);
1row created.
scott@PROD>commit;
在target端查看：SQL>select * from emp_ogg;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-------------------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 790217-DEC-80 800 20 7499 ALLEN SALESMAN 769820-FEB-81 1600 300 30 7521 WARD SALESMAN 769822-FEB-81 1250 500 30 7566 JONES MANAGER 783902-APR-81 2975 20 7654 MARTIN SALESMAN 769828-SEP-81 1250 1400 30 7698 BLAKE MANAGER 783901-MAY-81 2850 30 7782 CLARK MANAGER 783909-JUN-81 2450 10 7788 SCOTT ANALYST 756619-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 769808-SEP-81 1500 0 30 7876 ADAMS CLERK 778823-MAY-87 1100 20 7900 JAMES CLERK 769803-DEC-81 950 30 7902 FORD ANALYST 756603-DEC-81 3000 20 7934 MILLER CLERK 778223-JAN-82 1300 10 8000FAN BOSS 17-JUN-14 8000 500 50
15rows selected.
SQL>select * from dept_ogg;
DEPTNODNAME LOC------------------------ ------------- 10ACCOUNTING NEW YORK 20RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 CUUG BJ 60 GUUC TOKYO
9.2在source端测试表中update
scott@PROD>update dept_ogg set dname='Ministry' wheredeptno=50;
1row updated.
scott@PROD>update emp_ogg set empno=8888 where ename='FAN';
1row updated.
scott@PROD>commit;
Commitcomplete.
在target端查看：
SQL>select * from emp_ogg;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-------------------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 790217-DEC-80 800 20 7499 ALLEN SALESMAN 769820-FEB-81 1600 300 30 7521 WARD SALESMAN 769822-FEB-81 1250 500 30 7566 JONES MANAGER 783902-APR-81 2975 20 7654 MARTIN SALESMAN 769828-SEP-81 1250 1400 30 7698 BLAKE MANAGER 783901-MAY-81 2850 30 7782 CLARK MANAGER 783909-JUN-81 2450 10 7788 SCOTT ANALYST 756619-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 769808-SEP-81 1500 0 30 7876 ADAMS CLERK 778823-MAY-87 1100 20 7900 JAMES CLERK 769803-DEC-81 950 30 7902 FORD ANALYST 756603-DEC-81 3000 20 7934 MILLER CLERK 778223-JAN-82 1300 10 8888FAN BOSS 17-JUN-14 8000 500 50
15rows selected.
SQL>select * from dept_ogg;
DEPTNODNAME LOC------------------------ ------------- 10ACCOUNTING NEW YORK 20RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 Ministry BJ 60 GUUC TOKYO
9.3在source端测试表中delete
scott@PROD>delete from dept_ogg where deptno=60;
1row deleted.
scott@PROD>commit;
scott@PROD>delete from emp_ogg where empno=8888;
1row deleted.
Commitcomplete.
在target端查看：
SQL>select * from emp_ogg;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-------------------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 790217-DEC-80 800 20 7499 ALLEN SALESMAN 769820-FEB-81 1600 300 30 7521 WARD SALESMAN 769822-FEB-81 1250 500 30 7566 JONES MANAGER 783902-APR-81 2975 20 7654 MARTIN SALESMAN 769828-SEP-81 1250 1400 30 7698 BLAKE MANAGER 783901-MAY-81 2850 30 7782 CLARK MANAGER 783909-JUN-81 2450 10 7788 SCOTT ANALYST 756619-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 769808-SEP-81 1500 0 30 7876 ADAMS CLERK 778823-MAY-87 1100 20 7900 JAMES CLERK 769803-DEC-81 950 30 7902 FORD ANALYST 756603-DEC-81 3000 20 7934 MILLER CLERK 778223-JAN-82 1300 10
15rows selected.
SQL>select * from dept_ogg;
DEPTNODNAME LOC------------------------ ------------- 10ACCOUNTING NEW YORK 20RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 60 GUUC TOKYO