GoldenGate新增加表進行資料抽取

quanshengaa發表於2015-05-29
Go
GoldenGate實施完之後，執行了一段時間，由於業務上的需求，需要增加一些新表進行同步資料，可以採用新建extract程式或使用舊的extract程式來追加同步新增加的表的資料，我這裡採用的是追加的方式進行資料的抽取。[@more@]

下面是追加新增加表的操作過程：

OGG版本:OGG_for_unix_11g （V26188-01.zip）

資料庫版本:9.2.0.5（源端）、11.2.0.2（目標端）

源端操作：

GGSCI>info er *

各個程式都正常執行（running）

1.增加表級附加日誌

GGSCI>dblogin userid , password sdb2$fwzk

GGSCI>ADD TRANDATA GFPZ.GF_RENTBOUCHER_T
ADD TRANDATA GFPZ.HISGF_RENTBOUCHER_T
ADD TRANDATA GFPZ.GF_ACCEPT_T
ADD TRANDATA GFPZ.GF_EXPORT_T
ADD TRANDATA GFPZ.GF_LEASEHOLD_T
ADD TRANDATA GFPZ.GF_SOURCE_T
ADD TRANDATA GFPZ.HISGF_LEASEHOLD_T
ADD TRANDATA GFPZ.GF_IMPORT_T
ADD TRANDATA GFPZ.GF_LOCATION_T
ADD TRANDATA GFPZ.HISGF_LOCATION_T
ADD TRANDATA GFPZ.GF_ACCEPT_AUDIT_T
ADD TRANDATA GFPZ.GF_PRIVATEPART_T
ADD TRANDATA GFPZ.HISGF_PRIVATEPART_T
ADD TRANDATA GFPZ.GF_EQUIPMENT_T
ADD TRANDATA GFPZ.GF_PRIVATEEQUIPMENT_T
ADD TRANDATA GFPZ.GF_PUBLICPART_T
ADD TRANDATA GFPZ.HISGF_EQUIPMENT_T
ADD TRANDATA GFPZ.HISGF_PRIVATEEQUIPMENT_T
ADD TRANDATA GFPZ.HISGF_PUBLICPART_T
ADD TRANDATA GFPZ.GF_DATA_LOG_T
ADD TRANDATA GFPZ.GF_PRIVATEBUILD_T
ADD TRANDATA GFPZ.HISGF_PRIVATEBUILD_T
ADD TRANDATA GFPZ.BUILDINGAREA
ADD TRANDATA GFPZ.GF_PRINT_POINT_T
ADD TRANDATA GFPZ.HISGF_R_REALHOUSE_T
ADD TRANDATA GFPZ.CT_DISTRICT
ADD TRANDATA GFPZ.EXCEL

（注:所有新增加的各個表的欄位未超過32個欄位）

檢視錶級別附加日誌是否增加成功:

GGSCI>INFO TRANDATA GFPZ.* #檢視是否開啟了trandata設定，是否會執行初始化同步

2.切換歸檔日誌

SQL>alter system archive log current;

3.停止extract 程式、停止pump程式、停止relicat程式（目標端操作）

GGSCI>stop extract xxxxx

GGSCI>stop extract xxxxx

4.配置舊的extract程式，pump程式

GGSCI>edit params extract程式

新增下面的表進入:

TABLE GFPZ.GF_RENTBOUCHER_T;
TABLE GFPZ.HISGF_RENTBOUCHER_T;
TABLE GFPZ.GF_ACCEPT_T;
TABLE GFPZ.GF_EXPORT_T;
TABLE GFPZ.GF_LEASEHOLD_T;
TABLE GFPZ.GF_SOURCE_T;
TABLE GFPZ.HISGF_LEASEHOLD_T;
TABLE GFPZ.GF_IMPORT_T;
TABLE GFPZ.GF_LOCATION_T;
TABLE GFPZ.HISGF_LOCATION_T;
TABLE GFPZ.GF_ACCEPT_AUDIT_T;
TABLE GFPZ.GF_PRIVATEPART_T;
TABLE GFPZ.HISGF_PRIVATEPART_T;
TABLE GFPZ.GF_EQUIPMENT_T;
TABLE GFPZ.GF_PRIVATEEQUIPMENT_T;
TABLE GFPZ.GF_PUBLICPART_T;
TABLE GFPZ.HISGF_EQUIPMENT_T;
TABLE GFPZ.HISGF_PRIVATEEQUIPMENT_T;
TABLE GFPZ.HISGF_PUBLICPART_T;
TABLE GFPZ.GF_DATA_LOG_T;
TABLE GFPZ.GF_PRIVATEBUILD_T;
TABLE GFPZ.HISGF_PRIVATEBUILD_T;
TABLE GFPZ.BUILDINGAREA;
TABLE GFPZ.GF_PRINT_POINT_T;
TABLE GFPZ.HISGF_R_REALHOUSE_T;
TABLE GFPZ.CT_DISTRICT;
TABLE GFPZ.EXCEL;

GGSCI>edit params pump程式

新增如下的表進入:

TABLE GFPZ.*;

5.開啟舊的extract程式，開啟舊的pump程式

GGSCI>start extract 程式

GGSCI>start pump程式

6.獲得當前系統SCN

SQL>COL SCN FOR 999999999999999999999
SQL>variable SCN number
SQL>exec :scn := dbms_flashback.get_system_change_number
SQL>print scn

30974084141

7.export匯出所有需要抽取的表

exp dbmaintain file=/tmp/gfpz.dmp log=/tmp/gfpz.log tables=GF_RENTBOUCHER_T,HISGF_RENTBOUCHER_T,GF_ACCEPT_T,GF_EXPORT_T,GF_LEASEHOLD_T,GF_SOURCE_T,HISGF_LEASEHOLD_T,GF_IMPORT_T,GF_LOCATION_T,HISGF_LOCATION_T,GF_ACCEPT_AUDIT_T,GF_PRIVATEPART_T,HISGF_PRIVATEPART_T,GF_EQUIPMENT_T,GF_PRIVATEEQUIPMENT_T,GF_PUBLICPART_T,HISGF_EQUIPMENT_T,HISGF_PRIVATEEQUIPMENT_T,HISGF_PUBLICPART_T,GF_DATA_LOG_T,GF_PRIVATEBUILD_T,HISGF_PRIVATEBUILD_T,BUILDINGAREA,GF_PRINT_POINT_T,HISGF_R_REALHOUSE_T,CT_DISTRICT,EXCEL) flashback_scn='30974084141'

目標端操作：

8.新增新使用者（GFPZ）的表空間、使用者賦予許可權

SQL>create tablespace gfpz datafile '+data' size 10g segment space management auto;

SQL>drop user gfpz cascade;
SQL>create user gfpz identified by gfpz_123 default tablespace gfpz quota unlimited on gfpz;
SQL>grant connect,resource to gfpz;

9.配置舊的relicat程式

新增如下語句在舊的relicat程式裡面

GGSCI>map gfpz.GF_RENTBOUCHER_T ,target gfpz.GF_RENTBOUCHER_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.HISGF_RENTBOUCHER_T ,target gfpz.HISGF_RENTBOUCHER_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_ACCEPT_T ,target gfpz.GF_ACCEPT_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_EXPORT_T ,target gfpz.GF_EXPORT_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_LEASEHOLD_T ,target gfpz.GF_LEASEHOLD_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_SOURCE_T ,target gfpz.GF_SOURCE_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.HISGF_LEASEHOLD_T ,target gfpz.HISGF_LEASEHOLD_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_IMPORT_T ,target gfpz.GF_IMPORT_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_LOCATION_T ,target gfpz.GF_LOCATION_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.HISGF_LOCATION_T ,target gfpz.HISGF_LOCATION_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_ACCEPT_AUDIT_T ,target gfpz.GF_ACCEPT_AUDIT_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_PRIVATEPART_T ,target gfpz.GF_PRIVATEPART_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.HISGF_PRIVATEPART_T ,target gfpz.HISGF_PRIVATEPART_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_EQUIPMENT_T ,target gfpz.GF_EQUIPMENT_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_PRIVATEEQUIPMENT_T ,target gfpz.GF_PRIVATEEQUIPMENT_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_PUBLICPART_T ,target gfpz.GF_PUBLICPART_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.HISGF_EQUIPMENT_T ,target gfpz.HISGF_EQUIPMENT_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.HISGF_PRIVATEEQUIPMENT_T ,target gfpz.HISGF_PRIVATEEQUIPMENT_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.HISGF_PUBLICPART_T ,target gfpz.HISGF_PUBLICPART_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_DATA_LOG_T ,target gfpz.GF_DATA_LOG_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_PRIVATEBUILD_T ,target gfpz.GF_PRIVATEBUILD_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.HISGF_PRIVATEBUILD_T ,target gfpz.HISGF_PRIVATEBUILD_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.BUILDINGAREA ,target gfpz.BUILDINGAREA ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.GF_PRINT_POINT_T ,target gfpz.GF_PRINT_POINT_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.HISGF_R_REALHOUSE_T ,target gfpz.HISGF_R_REALHOUSE_T ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.CT_DISTRICT ,target gfpz.CT_DISTRICT ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);
map gfpz.EXCEL ,target gfpz.EXCEL ,filter (@GETENV ("TRANSACTION", "CSN")>30974084141);

10.import匯入資料

imp gfpz/gfpz_123 file=gfpz.dmp fromuser=gfpz touser=gfpz log=gfpz_imp.log

11.開啟舊的relicat程式

GGSCI>start replicat 程式

12.刪除repliact程式filter選項內容

當replicat程式將追加部分的資料開始同步並與源庫持平以後,將replicat程式中的filter clause刪除並重啟replicat程式

map gfpz.GF_RENTBOUCHER_T ,target gfpz.GF_RENTBOUCHER_T ;
map gfpz.HISGF_RENTBOUCHER_T ,target gfpz.HISGF_RENTBOUCHER_T ;
map gfpz.GF_ACCEPT_T ,target gfpz.GF_ACCEPT_T ;
map gfpz.GF_EXPORT_T ,target gfpz.GF_EXPORT_T ;
map gfpz.GF_LEASEHOLD_T ,target gfpz.GF_LEASEHOLD_T ;
map gfpz.GF_SOURCE_T ,target gfpz.GF_SOURCE_T ;
map gfpz.HISGF_LEASEHOLD_T ,target gfpz.HISGF_LEASEHOLD_T ;
map gfpz.GF_IMPORT_T ,target gfpz.GF_IMPORT_T ;
map gfpz.GF_LOCATION_T ,target gfpz.GF_LOCATION_T ;
map gfpz.HISGF_LOCATION_T ,target gfpz.HISGF_LOCATION_T ;
map gfpz.GF_ACCEPT_AUDIT_T ,target gfpz.GF_ACCEPT_AUDIT_T ;
map gfpz.GF_PRIVATEPART_T ,target gfpz.GF_PRIVATEPART_T ;
map gfpz.HISGF_PRIVATEPART_T ,target gfpz.HISGF_PRIVATEPART_T ;
map gfpz.GF_EQUIPMENT_T ,target gfpz.GF_EQUIPMENT_T ;
map gfpz.GF_PRIVATEEQUIPMENT_T ,target gfpz.GF_PRIVATEEQUIPMENT_T ;
map gfpz.GF_PUBLICPART_T ,target gfpz.GF_PUBLICPART_T ;
map gfpz.HISGF_EQUIPMENT_T ,target gfpz.HISGF_EQUIPMENT_T ;
map gfpz.HISGF_PRIVATEEQUIPMENT_T ,target gfpz.HISGF_PRIVATEEQUIPMENT_T ;
map gfpz.HISGF_PUBLICPART_T ,target gfpz.HISGF_PUBLICPART_T ;
map gfpz.GF_DATA_LOG_T ,target gfpz.GF_DATA_LOG_T ;
map gfpz.GF_PRIVATEBUILD_T ,target gfpz.GF_PRIVATEBUILD_T ;
map gfpz.HISGF_PRIVATEBUILD_T ,target gfpz.HISGF_PRIVATEBUILD_T ;
map gfpz.BUILDINGAREA ,target gfpz.BUILDINGAREA ;
map gfpz.GF_PRINT_POINT_T ,target gfpz.GF_PRINT_POINT_T ;
map gfpz.HISGF_R_REALHOUSE_T ,target gfpz.HISGF_R_REALHOUSE_T ;
map gfpz.CT_DISTRICT ,target gfpz.CT_DISTRICT ;
map gfpz.EXCEL ,target gfpz.EXCEL ;

13.禁用外來鍵

select 'alter table '||table_name||' modify constraint '||constraint_name||' disable;' from user_constraints where constraint_type='R';

14.經過驗證，源端的資料成功被目標端抽取.

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

相關文章