Oracle Golden Gate 配置

1. 資料庫相關配置

1.1 源端

1.1.1 建立表空間和ogg使用者

SQL>create tablespace tbs_gguser datafile '/u01/app/oracle/oradata/sky2/gguser.dbf' size m autoextend on;
SQL>create user ogg identified by ogg default tablespace tbs_gguser quota unlimited on tbs_gguser;
           

1.1.2 對使用者ogg進行授權

SQL>grant connect,resource to ogg;
SQL>grant CREATE SESSION,ALTER SESSION to ogg;
SQL>grant SELECT ANY DICTIONARY,SELECT ANY TABLE to ogg;
SQL>grant ALTER ANY TABLE to ogg;
SQL>grant FLASHBACK ANY TABLE to ogg;
SQL>grant EXECUTE on DBMS_FLASHBACK to ogg;
           

1.1.3 建立測試表

SQL>conn scott/scott
SQL>create table dept1 as select * from dept; 
給建立的dept1表定義主鍵（如果建立的測試表帶有主鍵則不需要）
SQL >alter table dept1 add constraint pk_dept1 primary key(deptno);
           

1.1.4 開啟附加日志

SQL>select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
可以看到附加日志沒有開啟，将它啟用,切換之後查詢是yes即可
SQL>alter database add supplemental log data;
切一下歸檔
SQL>alter system switch logfile; 
           

1.1.5 開啟歸檔模式

SQL>archive log list;
處于非歸檔模式，修改為歸檔模式
SQL>shutdown immediate;
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
           

1.1.6 開啟強制日志

SQL>select force_logging from v$database;
SQL>alter database force logging;
           

1.1.7 添加捕捉資料改變的表

GGSCI > dblogin userid ogg,password ogg
GGSCI > add trandata scott.test01
GGSCI > info trandata scott.*
           

1.2 目标端

1.2.1 建立表空間和使用者ogg

SQL>create tablespace tbs_gguser datafile '/u01/app/oracle/oradata/sky3/gguser.dbf' size m autoextend on;
SQL>create user ogg identified by ogg default tablespace tbs_gguser quota unlimited on tbs_gguser;
           

1.2.2 對使用者ogg進行授權

SQL>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 CREATE ANY TABLE to ogg;
           

1.2.3 建測試表

SQL>conn scott/scott
SQL>create table dept1 as select * from dept where =; 
給建立的dept1表定義主鍵（如果建立的測試表帶有主鍵則不需要）
SQL>alter table test01 add constraint pk_dept1 primary key(deptno); 
SQL>grant all on dept1 to ogg;
           

2. OGG相關配置

2.1 源端

2.1.1配置并啟動MGR

GGSCI > EDIT PARAMS MGR
PORT 
PURGEOLDEXTRACTS ./dirdat, USECHECKPOINTS
GGSCI > start mgr
           

2.1.2 配置并啟動EXTRACT

GGSCI >EDIT PARAMS EXT_1
EXTRACT EXT_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/ex
TABLE scott.dept1;

--添加EXTARCT
GGSCI >  ADD EXTRACT EXT_1,TRANLOG,BEGIN NOW

--添加TRAIL
GGSCI> ADD EXTTRAIL ./dirdat/ex, EXTRACT EXT_1,MEGABYTES 5
GGSCI > start extract EXT_1
GGSCI > info extract EXT_1
           

2.1.3 配置并啟動PUMP

GGSCI > EDIT PARAMS PMP_1
EXTRACT PMP_1
SETENV  (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
PASSTHRU
RMTHOST 192.168.80.132, MGRPORT 7809
RMTTRAIL ./dirdat/ex
TABLE scott.test01;

--添加PUMP
GGSCI> ADD EXTRACT PMP_1,EXTTRAILSOURCE ./dirdat/ex

--添加TRAIL
GGSCI> ADD RMTTRAIL ./dirdat/ex, extract PMP_1, MEGABYTES 5

--啟動data pump程序（.添加：確定對端MGR已經開啟，否則會出現錯誤）
GGSCI > start extract PMP_1

--檢視狀态
GGSCI > INFO EXTRACT PMP_1 
           

2.2 目标端

2.2.1 配置并啟動MGR

GGSCI > EDIT PARAMS MGR
PORT 
PURGEOLDEXTRACTS ./dirdat, USECHECKPOINTS
GGSCI > start mgr
           

2.2.2 目标庫配置replicat程序

建立GLOBALS參數
GGSCI > EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ogg.ggschkpt
--為了讓GLOBALS配置起作用，需要退出後重新進入GGSCI會話
GGSCI > exit
--添加replicat checkpoint table
GGSCI > DBLOGIN USERID ogg,PASSWORD ogg
GGSCI > ADD CHECKPOINTTABLE
--添加REPILCATE
GGSCI> ADD REPLICAT REP_1,EXTTRAIL ./dirdat/ex

--編輯replicat process REP_1參數
GGSCI>  EDIT PARAM REP_1
REPLICAT REP_1
SETENV  (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/REP_1.DSC,PURGE
MAP scott.dept1,TARGET scott.test01;

--啟動程序
GGSCI > START REPLICAT REP_1