oracle goldengate 初始化

 

官網提供的Load 方法有如下幾種：

（1）Loading data with a databaseutility

（2）Loading data from file to Replicat

（3）Loading data from file to database utility

（4）Loading data with an OracleGoldenGate direct load

（5）Loading data with a direct bulkload to SQL*Loader

（6）Loading data with Teradata loadutilities

Supported load methods

You can use Oracle GoldenGate to load data in any of the following ways:

● “Loading data with a database utility” on page 203. The utility performs the initial

load.

● “Loading data from file to Replicat” on page 204. Extract writes records to an extract

file and Replicat applies them to the target tables. This is the slowest initial-load

method.

● “Loading data from file to database utility” on page 209. Extract writes records to

extract files in external ASCII format. The files are used as data files for input into

target tables by a bulk load utility. Replicat creates the run and control files.

● “Loading data with an Oracle GoldenGate direct load” on page 214. Extract

communicates with Replicat directly across TCP/IP without using a Collector process

or files. Replicat applies the data through the database engine.

● “Loading data with a direct bulk load to SQL*Loader” on page 219. Extract extracts

records in external ASCII format and delivers them directly to Replicat, which delivers

them to Oracle’s SQL*Loader bulk-load utility. This is the fastest method of loading

Oracle data with Oracle GoldenGate.

● “Loading data with Teradata load utilities” on page 224. This is the preferred method

for synchronizing two Teradata databases. The recommended utility is MultiLoad.

資料庫工具初始化有多種方法：OGG、資料泵、RMAN等

真正的direct load初始化：

1）上傳安裝介質：

上傳安裝介質Oracle GoldenGate V11.1.1.0.0 for Oracle 10g on Linux x86.ZIP到oracle使用者家目錄

2）建立目錄：

建立安裝目錄:mkdir –p /home/oracle/ggate

3)解壓縮安裝:

解壓縮:unzip *.zip

tar xvf ggs_Linux_x86_ora10g_32bit_v11_1_1_0_0_078.tar -C /home/oracle/ggate/

4)修改環境變數配置：

為了方便呼叫，修改環境變數

$vi /home/oracle/.bash_profile

在檔案最後增加兩行：

export PATH=/home/oracle/ggate:$PATH

export LD_LIBRARY_PATH=/home/oracle/ggate/:$LD_LIBRARY_PATH

使之生效：

source /home/oracle/.bash_profile

5)進入ggsci命令列

[oracle@node1 ~]$ ggsci

-bash: /home/oracle/ggate/ggsci: cannot execute binary file

（發生錯誤的原因竟然是一時大意，將64位的包當成32位的用了；所以一定要注意版本相對應）

重新從第一步到第四步來一遍，ok；

[oracle@node1 ggate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.0.0 Build 078

Linux, x86, 32bit (optimized), Oracle 10 on Jul 28 2010 13:24:18

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.

GGSCI (node1) 1>

建立相關目錄：

GGSCI (node1) 1> create subdirs

Creating subdirectories under current directory /home/oracle/ggate

Parameter files                /home/oracle/ggate/dirprm: created

Report files                   /home/oracle/ggate/dirrpt: created

Checkpoint files               /home/oracle/ggate/dirchk: created

Process status files           /home/oracle/ggate/dirpcs: created

SQL script files               /home/oracle/ggate/dirsql: created

Database definitions files     /home/oracle/ggate/dirdef: created

Extract data files             /home/oracle/ggate/dirdat: created

Temporary files                /home/oracle/ggate/dirtmp: created

Veridata files                 /home/oracle/ggate/dirver: created

Veridata Lock files            /home/oracle/ggate/dirver/lock: created

Veridata Out-Of-Sync files     /home/oracle/ggate/dirver/oos: created

Veridata Out-Of-Sync XML files /home/oracle/ggate/dirver/oosxml: created

Veridata Parameter files       /home/oracle/ggate/dirver/params: created

Veridata Report files          /home/oracle/ggate/dirver/report: created

Veridata Status files          /home/oracle/ggate/dirver/status: created

Veridata Trace files           /home/oracle/ggate/dirver/trace: created

Stdout files                   /home/oracle/ggate/dirout: created

6）目標端也按照上述步驟安裝goldengate並建立相關目錄。

7）配置源端資料庫(192.168.150.128)

源端資料庫必須置於歸檔模式，force logging，並且啟用supplemental logging。檢視這幾個選項是否啟動，最簡單的方式是查詢v$database檢視，例如：

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE     SUPPLEME FOR

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

NOARCHIVELOG   NO       NO

啟用上述幾個選項的操作如下，以sysdba身份登入到sqlplus命令列，執行下列命令：

--啟動到mount狀態：

startup mount;

--置於歸檔模式：

alter database archivelog;

--強制日誌記錄：

alter database force logging;

--啟用最少附加日誌

alter database add supplemental log data;

--啟動資料庫並查詢狀態：

SQL> alter database open;

Database altered.

SQL>  select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE     SUPPLEME FOR

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

ARCHIVELOG   YES      YES

建立goldengate管理使用者：

SQL> create user goldengate identified by goldengate;

User created.

SQL> grant dba to goldengate;

Grant succeeded.

建立測試使用者：

SQL> create user jss identified by jss default tablespace users quota unlimited on users;

User created.

SQL> grant connect,resource to jss;

Grant succeeded.

用測試使用者初始化一個預設表：

SQL>  create table j1 (id number not null ,vl varchar2(200) ,primary key(id));

Table created.

SQL>  insert into j1 select rownum rn,object_name from all_objects;

49310 rows created.

SQL> commit;

Commit complete.

分別在源端和目標端執行netca，分別新增node1和node2兩個服務名，ip相對應。

配置源端goldengate：

檢視資訊：

GGSCI (node1) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED                                          

GGSCI (node1) 2>

GGSCI (node1) 2> edit params mgr

增加以下內容：

PORT 7809

GGSCI (node1) 3> start manager

Manager started.

GGSCI (node1) 5>  dblogin userid ggate, password ggate

Successfully logged into database.

GGSCI (node1) 6> add extract ext1,SOURCEISTABLE

EXTRACT added.

SOURCEISTABLE designates Extract as an initial-load process that reads complete

records directly from the source tables. Do not use any of the other ADD EXTRACT

service options or datasource arguments.

GGSCI (node1) 7>  info extract ext1, tasks

EXTRACT    EXT1      Initialized   2012-04-20 08:42   Status STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Not Available

                     First Record         Record 0

Task                 SOURCEISTABLE

GGSCI (node1) 8> edit params ext1

extract ext1

SETENV (NLS_LANG =AMERICAN_AMERICA.AL32UTF8)

userid ggate, password ggate

rmthost 192.168.150.129 mgrport 7809

RMTTASK REPLICAT, GROUP rep1

table jss.*;

GGSCI (node2) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED                                           

GGSCI (node2) 2> edit params mgr

PORT 7809

GGSCI (node2) 3> start mgr

Manager started.

GGSCI (node2) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          

GGSCI (node2) 5>

GGSCI (node2) 5> dblogin userid ggate, password ggate

Successfully logged into database.

GGSCI (node2) 59> add replicat rep1,SPECIALRUN

REPLICAT added.

（SPECIALRUN identifies the initial-load Replicat as a one-time run, not a continuous

process.）

GGSCI (node2) 60> edit params rep1

REPLICAT rep1

SETENV (NLS_LANG =AMERICAN_AMERICA.AL32UTF8)

ASSUMETARGETDEFS         -----源端和目標端表結構定義一致時使用

USERID ggate, PASSWORD "ggate"

DISCARDFILE ./dirrpt/rep1_gg2.dsc, PURGE

MAP jss.*, TARGET jss.*;

GGSCI (node2) 61> start rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

同步資料：

GGSCI (node1) 9>  start extract ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

檢查資料同步成功：

SQL> conn jss/jss

Connected.

SQL> select count(*) from j1;

  COUNT(*)

----------

         0

SQL> /

  COUNT(*)

----------

        10