1.Environment

 

2.Install Goldengate

 

2.1 在Source和Target端创建安装目录并解压安装包

mkdir -p /u01/app/oracle

cp V18156-01-linux.zip /u01/app/ogg

unzip  V18156-01-linux.zip

tar -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.1 libclntsh.so.10.1

 

[oracle@gc2:/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 (gc1) 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

 

以上所创建的目录的作用：

 

2.3命令界面介绍

 

        View HELP summary for all commands

GGSCI> HELP

GGSCI> HELP ALL

        View HELP summary for a COMMAND/ENTITY

GGSCI> HELP ADD EXTRACT

GGSCI> HELP ADD EXTTRAIL

        View your command history

GGSCI> HISTORY

        View a brief informational summary of all processes

GGSCI> INFO ALL

 

 

3.Configure Oracle Database for Goldengate

 

3.1创建GoldenGate用户(Source 和 Target 都要创建)

 

source:

[oracle@gc1:/home/oracle] sqlplus / as sysdba

create tablespace ogg datafile '/u01/app/oracle/oradata/PROD/disk5/ogg01.dbf' size 50M autoextend on;

create user ogg identified by ogg default tablespace OGG temporary tablespace TEMPTS quota unlimited on OGG;

grant CONNECT, RESOURCE to ogg;

grant CREATE SESSION, ALTER SESSION to ogg;

grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;

grant ALTER ANY TABLE to ogg;

grant FLASHBACK ANY TABLE to ogg;

grant EXECUTE on DBMS_FLASHBACK to ogg;

 

建立测试表:

source:

SQL> conn scott/tiger

SQL> 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                 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.

 

SQL> select * from dept_ogg;

 

    DEPTNO DNAME              LOC

---------- -------------- -------------

   10 ACCOUNTING          NEW YORK

   20 RESEARCH          DALLAS

   30 SALES                 CHICAGO

   40 OPERATIONS          BOSTON

   50 CUUG                  BJ

 

target:

[oracle@gc2:/home/oracle] sqlplus / as sysdba

create tablespace ogg datafile '/u01/app/oracle/oradata/EMREP/ogg01.dbf' size 50M autoextend on;

create user ogg identified by ogg default tablespace OGG temporary tablespace TEMPquota unlimited on OGG;

grant CONNECT, RESOURCE to ogg;

grant CREATE SESSION, ALTER SESSION to ogg;

grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;

grant CREATE TABLE to ogg;

 

 

建立测试表(target端应为与source端):

target:

SQL> conn scott/tiger

SQL> 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 on scott.emp_ogg to ogg;

SQL> grant INSERT, UPDATE, DELETE on scott.dept_ogg to ogg;

 

 3.2Enable supplemental log mode in source system(注意是Source端)

 

Check supplemental log mode is enabled or not by following query:

sys@PROD>select supplemental_log_data_min from v$database;

 

SUPPLEME

--------

YES

 

如果没开

 

sys@PROD>alter database add supplemental log data;

sys@PROD>alter system switch logfile;

开启后要切换一次日志

 

3.3Enable archive log mode in source system

 

sys@PROD>archive log list

Database log mode               Archive Mode

Automatic archival               Enabled

Archive destination               /u01/app/oracle/arch

Oldest online log sequence     497

Next log sequence to archive   499

Current log sequence               499

 

3.4Enable force logging mode in source system(强制写日志即便加了nologgin)

 

Check force logging mode is enabled or not by following query:

sys@PROD>select force_logging from v$database;

 

FOR

---

YES

 

如果没开

 

sys@PROD>alter database force logging;

 

 

Enable transaction data change capture for these two tables in Source system:

 

GGSCI (gc1) 13> DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

 

GGSCI (gc1) 13> ADD TRANDATA scott.emp_ogg

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

 

GGSCI (gc1) 13> ADD TRANDATA scott.dept_ogg

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

 

Verify that supplemental logging has been turned on for these tables：

 

GGSCI (gc1) 6> info trandata scott.emp*

 

Logging of supplemental redo log data is disabled for table SCOTT.EMP.

 

Logging of supplemental redo log data is disabled for table SCOTT.EMP_CLUSTER.

 

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

 

GGSCI (gc1) 7> info trandata scott.dept*

 

Logging of supplemental redo log data is disabled for table SCOTT.DEPT.

 

Logging of supplemental redo log data is disabled for table SCOTT.DEPT_CLUSTER.

 

Logging of supplemental redo log data is enabled for table 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 7788

PURGEOLDEXTRACTS ./dirdat, USECHECKPOINTS

 

GGSCI (gc1) > START MGR

 

GGSCI (gc1) > INFO MGR

Manager is running (IP port gc1.7788).

 

系统关闭后要手动启动MGR

 

4.2Configure MGR in target system

 

Create the Manager parameter file.

[oracle@gc2 ogg] ./ggsci

GGSCI (gc2) > EDIT PARAMS MGR

Use the editor to assign a port.

PORT 7788

PURGEOLDEXTRACTS /u01/app/ogg/dirdat, USECHECKPOINTS

 

Start the Manager.

GGSCI (gc2) > START MGR

 

Verify that the Manager has started.

GGSCI (gc2) > INFO MGR

Manager 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

 

Add an Extract process called EINI_1:

GGSCI (gc1) >ADD EXTRACT EINI_1, SOURCEISTABLE

 

 

Verify Extract process:

GGSCI (gc1) > INFO EXTRACT *, TASKS

 

GGSCI (gc1) > EDIT PARAMS EINI_1

添加：

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

RMTHOST gc2, MGRPORT 7788

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.EMP_OGG;

TABLE scott.DEPT_OGG;

 

5.2Configure replicat process in target system

 

Add initial load delivery process

GGSCI (gc2) > ADD REPLICAT RINI_1, SPECIALRUN

 

 

Verify result:

GGSCI (gc2) >  INFO REPLICAT *, TASKS

 

GGSCI (gc2) >  EDIT PARAMS RINI_1

添加：

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

ASSUMETARGETDEFS

USERID ogg, PASSWORD ogg

DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE

MAP scott.*, TARGET scott.*;

 

5.3Accomplish Initial Load

 

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

GGSCI (gc1) > START EXTRACT EINI_1

 

Sending START request to MANAGER ...

EXTRACT EINI_1 starting

 

5.4Verify Initial Load result and process status

 

GGSCI (gc1) 34> view report eini_1

 

 

2014-06-17 15:48:27  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-06-17 15:48:27

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

 

Operating System Version:

Linux

Version #1 SMP Tue Aug 18 15:51:54 EDT 2009, Release 2.6.18-164.el5

Node: gc1

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: 3436

 

Description:

 

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

**            Running with the following parameters                  **

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

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ***

RMTHOST gc2, MGRPORT 7788

RMTTASK REPLICAT, GROUP RINI_1

TABLE scott.emp_ogg;

 

2014-06-17 15:49:24  GGS WARNING     109  No unique key is defined for table EMP_OGG. All viable columns will be used to repr

esent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

 

Using the following key columns for source table SCOTT.EMP_OGG: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO.

TABLE scott.dept_ogg;

 

2014-06-17 15:49:25  GGS WARNING     109  No unique key is defined for table DEPT_OGG. All viable columns will be used to rep

resent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

 

Using the following key columns for source table SCOTT.DEPT_OGG: DEPTNO, DNAME, LOC.

 

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 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE        11.2.0.1.0        Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.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.EMP_OGG

 

Processing table SCOTT.DEPT_OGG

 

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

*                   ** Run Time Statistics **                         *

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

 

 

Report at 2014-06-17 15:52:11 (activity since 2014-06-17 15:49:24)

 

Output to RINI_1:

 

From Table SCOTT.EMP_OGG:

       #                   inserts:        14

       #                   updates:         0

       #                   deletes:         0

       #                  discards:         0

From Table 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                        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.

 

SQL> select * from dept_ogg;

 

    DEPTNO DNAME              LOC

---------- -------------- -------------

   10 ACCOUNTING          NEW YORK

   20 RESEARCH          DALLAS

   30 SALES                 CHICAGO

   40 OPERATIONS          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 STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Table SCOTT.DEPT_OGG

                     2014-06-17 16:05:03  Record 5

Task                 SOURCEISTABLE

 

GGSCI (gc2) 15> INFO REPLICAT RINI_1

 

REPLICAT   RINI_1    Initialized   2014-06-17 15:34   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:34:13 ago)

Log Read Checkpoint  Not Available

Task                 SPECIALRUN

 

6.Configure Extract Process in Source system(持续捕捉变化的EXTRACT进程)

 

6.1Edit extract process parameter

 

GGSCI (gc1) 39> edit params eora_1

添加：

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

EXTTRAIL ./dirdat/aa

TABLE scott.emp_ogg;

TABLE scott.dept_ogg;

 

6.2创建EXTRACT进程和tail文件,Extract组负责写这份文件,Pump进程组负责读取它

GGSCI (gc1) 40> add extract eora_1,tranlog,begin now

EXTRACT added.

 

GGSCI (gc1) 44> ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5

EXTTRAIL added.

 

6.3Start primary Extract process

GGSCI (gc1) 45> START EXTRACT EORA_1

 

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

 

查看extract process是否运行

GGSCI (gc1) 46> info extract *

 

EXTRACT    EORA_1    Last Started 2014-06-17 16:31   Status RUNNING

Checkpoint Lag       00:14:47 (updated 00:00:06 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2014-06-17 16:16:57  Seqno 499, RBA 8651792

 

成功运行,OGG会产生trail日志

[oracle@gc1:/u01/app/ogg/dirdat]$ ls

aa000000

[oracle@gc1:/u01/app/ogg/dirdat]$

 

7.Configure pump process in source system(配置Pump投递进程组)

 

7.1Edit data pump process parameter

GGSCI (gc1) 47> edit params pora_1

 

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST gc2, MGRPORT 7788

RMTTRAIL ./dirdat/pa

TABLE scott.emp_ogg;

TABLE scott.dept_ogg;

 

添加Pump进程

GGSCI (gc1) 48> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa

EXTRACT added.

 

查看是否添加成功

GGSCI (gc1) 49> INFO EXTRACT PORA_1

 

EXTRACT    PORA_1    Initialized   2014-06-17 16:38   Status STOPPED

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

Log Read Checkpoint  File ./dirdat/aa000000

                     First Record  RBA 0

 

7.2Add GoldenGate remote trail in Source system(创建远程trail文件,这个文件在source端通过命令创建到target端)

GGSCI (gc1) 51> ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5

RMTTRAIL added.

 

启动Pump process

GGSCI (gc1) 52> START EXTRACT PORA_1

 

Sending START request to MANAGER ...

EXTRACT PORA_1 starting

 

查看是否运行

GGSCI (gc1) 53> INFO EXTRACT PORA_1

 

EXTRACT    PORA_1    Last Started 2014-06-17 16:40   Status RUNNING

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

Log Read Checkpoint  File ./dirdat/aa000000

                     First Record  RBA 899

 

 

[oracle@gc2:/u01/app/ogg/dirdat]$ ls           注意远程trail文件在gc2

pa000000

 

8.Configure replicat process in target system

 

8.1Create GLOBALS parameter 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 exit GGSCI session:

 

GGSCI (gc2) 20> exit

[oracle@gc2:/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 (gc2) 1> dblogin userid ogg,password ogg

Successfully logged into database.

 

GGSCI (gc2) 2> add checkpointtable

 

No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...

 

Successfully created checkpoint table OGG.GGSCHKPT.

 

8.2Edit Delivery process parameter(开始配置Replicat复制进程组)

 

添加Replicat复制进程：

GGSCI (gc2) 3> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa

REPLICAT added.

 

编辑参数：

GGSCI (gc2) 4> EDIT PARAM RORA_1

 

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE

MAP scott.*, TARGET scott.*;

 

启动：

GGSCI (gc2) 5> START REPLICAT RORA_1

 

Sending START request to MANAGER ...

REPLICAT RORA_1 starting

 

查看运行状态：

GGSCI (gc2) 6> INFO REPLICAT RORA_1

 

REPLICAT   RORA_1    Initialized   2014-06-17 17:00   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:02:28 ago)

Log Read Checkpoint  File ./dirdat/pa000000

                     First Record  RBA 0

 

 

9.测试单向配置

 

9.1在source端测试表中插入数据

 

scott@PROD>insert into dept_ogg values(60,'GUUC','TOKYO');

 

1 row created.

 

scott@PROD>insert into emp_ogg values(8000,'FAN','BOSS',null,sysdate,8000,500,50);

 

1 row created.

 

scott@PROD>commit;

 

在target端查看：

SQL> select * from emp_ogg;

 

     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

      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 FAN              BOSS                    17-JUN-14           8000        500                    50

 

15 rows selected.

 

SQL> select * from dept_ogg;

 

    DEPTNO DNAME              LOC

---------- -------------- -------------

    10 ACCOUNTING          NEW YORK

    20 RESEARCH          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' where deptno=50;

 

1 row updated.

 

scott@PROD>update emp_ogg set empno=8888 where ename='FAN';

 

1 row updated.

 

scott@PROD>commit;

 

Commit complete.

 

在target端查看：

 

SQL> select * from emp_ogg;

 

     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

      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

      8888 FAN          BOSS                                     17-JUN-14           8000        500                    50

 

15 rows selected.

 

SQL> select * from dept_ogg;

 

    DEPTNO DNAME              LOC

---------- -------------- -------------

    10 ACCOUNTING          NEW YORK

    20 RESEARCH          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;

 

1 row deleted.

 

scott@PROD>commit;

 

scott@PROD>delete from emp_ogg where empno=8888;

 

1 row deleted.

 

Commit complete.

 

在target端查看：

 

SQL> select * from emp_ogg;

 

     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

      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

 

15 rows selected.

 

SQL> select * from dept_ogg;

 

    DEPTNO DNAME              LOC

---------- -------------- -------------

    10 ACCOUNTING          NEW YORK

    20 RESEARCH          DALLAS

         30 SALES                       CHICAGO

         40 OPERATIONS          BOSTON

         60 GUUC                       TOKYO