OracleGoldenGateInstallForLinux

OracleGoldenGateInstallForLinux

DownLoad:

 http://edelivery.oracle.com

Prepare(Source And Target DB): Add Environment Varible vi /home/oracle/.bash_profile export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/opt/ogg Configure Database alter database archivelog; alter database force logging; alter database add supplemental log data; ALTER SYSTEM SET recyclebin = OFF scope=spfile; for Oracle 11g ,need to be restart Create OGG User: sqlplus / as sysdba create user ogg identified by ogg; grant dba to ogg; GGSCI (testdb) 1> edit params ./GLOBAL GGSCHEMA ogg CHECKPOINTTABLE ogg.checkpoint For ddl同步，需要執行以下腳本： marker_setup.sql –該腳本安裝marker系統，這部分為啟用DLL支持所必備，執行該腳本時會提示輸入GoldenGate管理帳戶schema名. ddl_setup –執行該腳本要確認關閉掉所有ORACLE會話，未被關閉的會話會以列表形式顯示，執行過程中會要求希望對象屬主，並選擇安裝模式。如果是初次安裝就選擇“INITIALSETUP”，該模式假設當前沒有任何GoldenGate DDL對象存在，如果存在則會刪除並重建。如果是重新安裝，則應該選擇“NORMAL”. role_setup –該操作會重建DDL同步所需的權限，授予GoldenGate中的DDL對象以DML權限. ddl_enable –啟用DDL觸發器，以捕獲DDL操作. 要提高DDL觸發器的性能，可以通過ddl_pin腳本，該腳本會將觸發器使用的包加載到內存，以此提高效率。該腳本執行時需要引用dbms_shared_pool系統包，因此在使用ddl_pin腳本前需要確保dbms_shared_pool可用。 @?/rdbms/admin/dbmspool.sql ddl_pin –執行ddl_pin腳本需要指定GoldenGate管理員schema名稱. 然後，用於同步DDL操作的對象就創建完成了。 Source DB: GGSCI (ogg1) 1> create subdirs

GGSCI (ogg1) 75> edit param mgr

PORT 7809

userid ogg,password ogg

PURGEOLDEXTRACTS ./dirdat/k1*,USECHECKPOINTS,MINKEEPFILES 10

AUTORESTART ER *, RETRIES 3, WAITMINUTES 5

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

##########datapump GGSCI (ogg1) 4>> add extract dpump,exttrailsource /opt/ogg/dirdat/k1 EXTRACT added. GGGSCI (ogg1) 4> add rmttrail /opt/ogg/dirdat/k1, extract dpump RMTTRAIL added. GGSCI (ogg1) 76> edit param dpump

extract dpump
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
userid ogg, password ogg
rmthost 192.168.56.13, mgrport 7809
rmttrail /opt/ogg/dirdat/k1
DYNAMICRESOLUTION
table zbdba.*;

###########extract GGSCI (ogg1) 1> dblogin userid ogg,password ogg Successfully logged into database. GGSCI (ogg1) 2> add extract ext1,tranlog, begin now EXTRACT added. GGSCI (ogg1) 4>add exttrail /opt/ogg/dirdat/k1, extract ext1 EXTTRAIL added. GGSCI (ogg1) 78> edit param ext1

EXTRACT EXT1
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
USERID ogg,PASSWORD ogg
exttrail /opt/ogg/dirdat/k1
DYNAMICRESOLUTION
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA,REPORT
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
TABLE zbdba.*;

start manager start dpump start ext1 GGSCI (ogg1) 16> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:00:07
EXTRACT RUNNING EXT1 00:00:00 00:00:03

Target DB: GGSCI (ogg2) 1> create subdirs edit param mgr PORT 7809 userid ogg,password ogg PURGEOLDEXTRACTS ./dirdat/k1*,USECHECKPOINTS,MINKEEPFILES 10 AUTORESTART ER *, RETRIES 3, WAITMINUTES 5 PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30 PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30 Create Replicat GGSCI (ogg2) 1> view param ./GLOBALS GGSCHEMA ogg CHECKPOINTTABLE ogg.checkpoint GGSCI (ogg2) 1> dblogin userid ogg,password ogg GGSCI (ogg2) 2> add checkpointtable ogg.checkpoint Successfully created checkpoint table OGG.CHECKPOINT. GGSCI (ogg2) 2> add replicat rep1,exttrail /opt/ogg/dirdat/k1, checkpointtable ogg.checkpoint REPLICAT added. GGSCI (ogg2) 19> edit param rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ogg, PASSWORD ogg
discardfile ./dirdat/rep1_discard.txt,append,megabytes 5
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
BATCHSQL
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 102400
HANDLECOLLISIONS
DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
–grouptransops 1
–maxtransops 1
APPLYNOOPUPDATES –解決不能更新操作
MAP zbdba.*, TARGET zbdba.*;

start manager

GGSCI (ogg2) 23> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:03

Test Data(Source and Target DB): create tablepsace zbdab datafile '/opt/oracle/oradata/zbdba.dbf' size 100m; create zbdba identified by oracle default tablespace zbdba; grant dba to zbbda; On Source DB: User data pump: expdp zbdba/oracle DIRECTORY=dumpdir
DUMPFILE=zbdba.dmp LOGFILE=zbdba.log
SCHEMAS=zbdba JOB_NAME=exp_zbdba_schema scp data to target DB On Target DB: impdp zbdba/oracle DIRECTORY=dumpdir
DUMPFILE=zbdba.dmp SCHEMAS=zbdba
JOB_NAME=imp_zbdba_schema
GGSCI (ogg2) 24> start rep1

Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (ogg2) 26> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:07

On Source DB:

create table test as select * from dba_objects;

Monitor OGG(Source And Target DB): tail -100f ggserr.log