GolgenGate-一次性抽取

lpwebnet發表於2014-02-08
Go

平臺：HP IA 64
源庫：11.2.0.3
目標庫：11.1.0.6
實現方式：實現一次性抽取
 OGG的實現方式很多，最簡單的莫過於一次性抽取，即只在啟動extract程式時同步。待此次同步完成後，如表中資料變化則目標庫中的資料不隨之變化。
1. 兩邊 配置監聽
2. 安裝軟體包
源庫：
$ ll   /home/oratest/ogg/
-rw-r--r--   1 oratest    oinstall   78964027 Jan 10 11:32 p14764589_112104_HPUX-IA64.zip
$ unzip p14764589_112104_HPUX-IA64.zip
Archive:  p14764589_112104_HPUX-IA64.zip
  inflating: ggs_HPUX_ia64_ora11g_64bit.tar 
  inflating: Oracle-GoldenGate-11.2.1.0.4-README.doc 
  inflating: Oracle-GoldenGate-11.2.1.0.4-README.txt 
  inflating: OGG-WinUnix-Rel-Notes-11.2.1.0.4.pdf 
$ ls
OGG-WinUnix-Rel-Notes-11.2.1.0.4.pdf     Oracle-GoldenGate-11.2.1.0.4-README.txt  p14764589_112104_HPUX-IA64.zip
Oracle-GoldenGate-11.2.1.0.4-README.doc  ggs_HPUX_ia64_ora11g_64bit.tar
$ tar -xvf ggs_HPUX_ia64_ora11g_64bit.tar
目標庫：
$ ll   /home/orazkf/ogg/
-rw-r--r--   1 oratest    oinstall   78964027 Jan 10 11:32 p14764589_112104_HPUX-IA64.zip
$ unzip p14764589_112104_HPUX-IA64.zip
Archive:  p14764589_112104_HPUX-IA64.zip
  inflating: ggs_HPUX_ia64_ora11g_64bit.tar 
  inflating: Oracle-GoldenGate-11.2.1.0.4-README.doc 
  inflating: Oracle-GoldenGate-11.2.1.0.4-README.txt 
  inflating: OGG-WinUnix-Rel-Notes-11.2.1.0.4.pdf 
$ ls
OGG-WinUnix-Rel-Notes-11.2.1.0.4.pdf     Oracle-GoldenGate-11.2.1.0.4-README.txt  p14764589_112104_HPUX-IA64.zip
Oracle-GoldenGate-11.2.1.0.4-README.doc  ggs_HPUX_ia64_ora11g_64bit.tar
$ tar -xvf ggs_HPUX_ia64_ora11g_64bit.tar
3. 配置環境變數
源庫：
umask 022
export ORACLE_SID=TEST
export ORACLE_BASE=/pmsdb/oracle/$ORACLE_SID
export ORACLE_HOME=$ORACLE_BASE/11.2.0
export LD_LIBRARY_PATH=/home/oratest/ogg:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib
export ORACLE_TERM=xterm
export PATH=/home/oratest/ogg:$ORACLE_HOME/bin:$PATH
目標庫：
umask 022
export ORACLE_SID=ZKFP1
export ORACLE_BASE=/pmsdb/oracle/ZKFT1
export ORACLE_HOME=$ORACLE_BASE/11.1.0
export ORACLE_TERM=xterm
export PATH=/home/orazkf/ogg:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=/home/oratest/ogg:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
4  建立目錄
源庫：
GGSCI (I2TRNDB2) 7> exit
$ cd ogg
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.4 14636914 OGGCORE_11.2.1.0.4_PLATFORMS_121007.2020
HP/UX, IA64, 64bit (optimized), Oracle 11g on Oct  8 2012 02:25:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (I2TRNDB2) 1> create subdirs
Creating subdirectories under current directory /home/oratest/ogg
Parameter files                /home/oratest/ogg/dirprm: already exists
Report files                   /home/oratest/ogg/dirrpt: created
Checkpoint files               /home/oratest/ogg/dirchk: created
Process status files           /home/oratest/ogg/dirpcs: created
SQL script files               /home/oratest/ogg/dirsql: created
Database definitions files     /home/oratest/ogg/dirdef: created
Extract data files             /home/oratest/ogg/dirdat: created
Temporary files                /home/oratest/ogg/dirtmp: created
Stdout files                   /home/oratest/ogg/dirout: created
port 7809
目標庫：
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.4 14636914 OGGCORE_11.2.1.0.4_PLATFORMS_121007.2020
HP/UX, IA64, 64bit (optimized), Oracle 11g on Oct  8 2012 02:25:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (I2TRNDB1) 1> create subdirs
Creating subdirectories under current directory /home/orazkf/ogg
Parameter files                /home/orazkf/ogg/dirprm: already exists
Report files                   /home/orazkf/ogg/dirrpt: created
Checkpoint files               /home/orazkf/ogg/dirchk: created
Process status files           /home/orazkf/ogg/dirpcs: created
SQL script files               /home/orazkf/ogg/dirsql: created
Database definitions files     /home/orazkf/ogg/dirdef: created
Extract data files             /home/orazkf/ogg/dirdat: created
Temporary files                /home/orazkf/ogg/dirtmp: created
Stdout files                   /home/orazkf/ogg/dirout: created
5 開啟歸檔，force logging，補充日誌
源庫：
SQL>  select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE     SUPPLEME FOR
------------ -------- ---
ARCHIVELOG   YES      YES
備註：由於實驗為單向複製，所以只開啟源庫的這些資訊即可。
6 建立ogg管理賬戶
源庫：
SQL> create user ogguser identified by ogguser;
User created.
SQL> grant dba to ogguser;
Grant succeeded.
建立測試使用者：
SQL> create user maomao identified by maomao default tablespace user1;
User created.
SQL> grant connect,resource to maomao;                     
Grant succeeded.
SQL> create table maomao.ogg_tab as select * from dba_objects;
Table created.
目標庫：
SQL> create user ogguser identified by ogguser;
User created.
SQL> grant dba to ogguser;
Grant succeeded.
建立測試使用者：
SQL> create user maomao identified by maomao default tablespace users;
User created.
SQL> grant connect,resource to maomao;                     
Grant succeeded.
SQL> create table maomao.ogg_tab as select * from dba_objects where 1=0;
Table created.
備註：這裡目標庫只建立表即可，不需要插入資料，配置完gg後，資料將會從源庫中傳遞過來。
7. 配置源庫和目標庫GolgenGate
源庫和目標庫配置埠和啟動服務：
GGSCI (I2TRNDB2) 2> edit params mgr
"/home/oratest/ogg/dirprm/mgr.prm" [New file]
port 7809---插入
"/home/oratest/ogg/dirprm/mgr.prm" [New file] 1 line, 10 characters --儲存退出
GGSCI (I2TRNDB2) 3> start mgr
Manager started.
GGSCI (I2TRNDB2) 4> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
備註：mgr複製管理gg整體概況，管理各個程式的啟動與關閉，監控各個資源和程式的情況。                                         
8 登陸
源庫與目標庫：
GGSCI (I2TRNDB2) 1> dblogin userid ogguser,password ogguser
Successfully logged into database.
9 源庫新增需要傳輸的使用者表
GGSCI (I2TRNDB2) 16> add trandata maomao.ogg_tab
2014-01-13 11:38:22  WARNING OGG-00869  No unique key is defined for table 'OGG_TAB'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table MAOMAO.OGG_TAB.
備註：主鍵和變更值輸出到redo日誌。所以capture的過程要求表有主鍵。沒有主鍵的話也沒有關係，這只是一個告警。另外，這裡如果不做新增這步應該也是可以的。
GGSCI (I2TRNDB2) 17> info trandata maomao.*
Logging of supplemental redo log data is enabled for table MAOMAO.OGG_TAB.
Columns supplementally logged for table MAOMAO.OGG_TAB: OBJECT_TYPE, STATUS, NAMESPACE, EDITION_NAME, LAST_DDL_TIME, TEMPORARY, OWNER, OBJECT_NAME, OBJECT_ID, CREATED, GENERATED, SECONDARY, SUBOBJECT_NAME, DATA_OBJECT_ID, TIMESTAMP.
10 配置源資料庫extract程式
新增extract程式ext1：
GGSCI (I2TRNDB2) 2> add extract ext1,sourceistable-----SOURCEISTABLE表示初始化整個表
GGSCI (I2TRNDB2) 3> info extract *,tasks
EXTRACT    EXT1      Initialized   2014-01-10 15:01   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE
編輯ext1的配置資訊：
GGSCI (I2TRNDB2) 4> edit params ext1
extract ext1
userid ogguser,password ogguser         
rmthost 192.168.160.233, mgrport 7809
rmttask replicat,group rep1
table maomao.ogg_tab;
備註：192.168.160.233是目標庫的ip。table那行也可使用maomao.*，他是支援萬用字元的。千萬不要忘記加分號
11 目標資料庫配置replicat程式
新增replicat程式rep1：
GGSCI (I2TRNDB1) 6> add replicat rep1,specialrun---------SPECIALRUN表示只抽取一次
GGSCI (I2TRNDB1) 8>  INFO REPLICAT *, TASKS
REPLICAT   REP1      Initialized   2014-01-10 15:07   Status STOPPED
Checkpoint Lag       00:00:00 (updated 68:08:13 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN
編輯rep1的配置資訊：
GGSCI (I2TRNDB1) 9> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS---這裡表示oracle-oracle的同結果傳輸
userid ogguser, password ogguser     
DISCARDFILE ./dirrpt/rep1_gg2.dsc, PURGE
map maomao.*, target maomao.*;
12 啟動傳輸
GGSCI (I2TRNDB2) 5> start extract ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (I2TRNDB2) 6> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
GGSCI (I2TRNDB2) 7> view report ext1
GGSCI (I2TRNDB1) 13> view report rep1
這裡一次性抽取，只需要啟動extract程式即可，不需要啟動replicat程式，用info all的時候只能看到mgr程式，看不到extract等程式。

在源資料庫再建立新表：
SQL> create table t3(id int);
Table created.
SQL> insert into t3 values(8);
1 row created.
SQL> commit;
Commit complete.
目標：
SQL> select * from t3;
no rows selected
檢視原庫資訊：
GGSCI (I2TRNDB2) 1> dblogin userid ogguser,password ogguser
Successfully logged into database.
GGSCI (I2TRNDB2) 2> info trandata maomao.*
Logging of supplemental redo log data is enabled for table MAOMAO.OGG_TAB.
Columns supplementally logged for table MAOMAO.OGG_TAB: OBJECT_TYPE, STATUS, NAMESPACE, EDITION_NAME, LAST_DDL_TIME, TEMPORARY, OWNER, OBJECT_NAME, OBJECT_ID, CREATED, GENERATED, SECONDARY, SUBOBJECT_NAME, DATA_OBJECT_ID, TIMESTAMP.
Logging of supplemental redo log data is disabled for table MAOMAO.T3.
目標庫：
GGSCI (I2TRNDB1) 3> info trandata maomao.*
Logging of supplemental redo log data is disabled for table MAOMAO.OGG_TAB.
Logging of supplemental redo log data is disabled for table MAOMAO.T3.
GGSCI (I2TRNDB2) 4> start extract ext1---啟動ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
檢視目標庫表的結果：
SQL> select * from t3;
        ID
----------
         8

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17172228/viewspace-1077966/，如需轉載，請註明出處，否則將追究法律責任。

相關文章