GoldenGate搭建

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