配置支持DML和DDL操作同步的GoldenGate.docx
《配置支持DML和DDL操作同步的GoldenGate.docx》由会员分享,可在线阅读,更多相关《配置支持DML和DDL操作同步的GoldenGate.docx(26页珍藏版)》请在冰豆网上搜索。
配置支持DML和DDL操作同步的GoldenGate
配置支持DML和DDL操作同步的GoldenGate
测试环境描述
SOURCE:
主机:
vmwareworkstation7.0
操作系统:
CENTOS5.532bit
数据库版本:
ORACLE10.2.0.4
IP:
192.168.0.88
SID:
orcl
TARGET:
主机:
vmwareworkstation7.0
操作系统:
CENTOS5.532bit
数据库版本:
ORACLE10.2.0.4
IP:
192.168.0.88
SID:
ggtarge
以下操作需要操作如无特殊说明,均为在源数据库和目标数据库上操作
1、安装GoldGate
1)将Goldgate的压缩包传送到相对应的目录,并解压
[ora10@dev160gg10]$ls-l
total401000
-rwxr-xr-x1ora10dba500964Aug62010OGG_WinUnix_Rel_Notes_11.1.1.0.0_078.pdf
-rwxrwxr-x1ora10dba26726Aug32010README.txt
drwxr-x---7ora10dba4096Jul292010UserExitExamples
-r--r--r--1ora10dba426Mar122007bcpfmt.tpl
-r--r--r--1ora10dba1725Sep112002bcrypt.txt
-r--r--r--1ora10dba739Mar132010chkpt_ora_create.sql
-rwxr-xr-x1ora10dba7960573Jul292010cobgen
-rwxr-xr-x1ora10dba7890642Jul292010convchk
-r--r--r--1ora10dba159Mar122007db2cntl.tpl
-r--r--r--1ora10dba3334Mar122007ddl_access.tpl
-r--r--r--1ora10dba1059Mar132010ddl_cleartrace.sql
-r--r--r--1ora10dba3017Mar122007ddl_db2.tpl
-r--r--r--1ora10dba3502Mar122007ddl_db2_os390.tpl
-r--r--r--1ora10dba4189Mar132010ddl_ddl2file.sql
-r--r--r--1ora10dba746Mar132010ddl_disable.sql
-r--r--r--1ora10dba692Mar132010ddl_enable.sql
-r--r--r--1ora10dba2984Mar122007ddl_informix.tpl
-r--r--r--1ora10dba3414Mar232010ddl_mss.tpl
-r--r--r--1ora10dba3481Jun22007ddl_mysql.tpl
-r--r--r--1ora10dba388Mar132010ddl_nopurgeRecyclebin.sql
-r--r--r--1ora10dba2653Mar182007ddl_nssql.tpl
-r--r--r--1ora10dba12424May222010ddl_ora10.sql
-r--r--r--1ora10dba3863May222010ddl_ora10upCommon.sql
-r--r--r--1ora10dba11064Mar132010ddl_ora11.sql
-r--r--r--1ora10dba12365Mar132010ddl_ora9.sql
-r--r--r--1ora10dba3458Mar122007ddl_oracle.tpl
-r--r--r--1ora10dba1026Mar132010ddl_pin.sql
-r--r--r--1ora10dba1227Mar132010ddl_purgeRecyclebin.sql
-r--r--r--1ora10dba3686May142010ddl_remove.sql
-r--r--r--1ora10dba1053Jun242009ddl_session.sql
-r--r--r--1ora10dba425Jun302009ddl_session1.sql
-r-xr-xr-x1ora10dba228606Jun302010ddl_setup.sql
-r--r--r--1ora10dba2652Jun72007ddl_sqlmx.tpl
-r--r--r--1ora10dba8872Mar132010ddl_status.sql
-r--r--r--1ora10dba2506Mar132010ddl_staymetadata_off.sql
-r--r--r--1ora10dba2501Mar132010ddl_staymetadata_on.sql
-r--r--r--1ora10dba3523Mar122007ddl_sybase.tpl
-r--r--r--1ora10dba2653Mar122007ddl_tandem.tpl
-r--r--r--1ora10dba2543Mar132010ddl_trace_off.sql
-r--r--r--1ora10dba2862Mar132010ddl_trace_on.sql
-r--r--r--1ora10dba2955Mar132010ddl_tracelevel.sql
-rwxr-xr-x1ora10dba8039613Jul292010ddlcob
-rwxr-xr-x1ora10dba9415801Jul292010ddlgen
-rwxr-xr-x1ora10dba9289214Jul292010defgen
-r--r--r--1ora10dba1217Mar132010demo_more_ora_create.sql
-r--r--r--1ora10dba967Mar132010demo_more_ora_insert.sql
-r--r--r--1ora10dba883Mar132010demo_ora_create.sql
-r--r--r--1ora10dba821Mar132010demo_ora_insert.sql
-r--r--r--1ora10dba4015Mar132010demo_ora_lob_create.sql
-r--r--r--1ora10dba2275Mar132010demo_ora_misc.sql
-r--r--r--1ora10dba1269Mar132010demo_ora_pk_befores_create.sql
-r--r--r--1ora10dba1227Mar132010demo_ora_pk_befores_insert.sql
-r--r--r--1ora10dba2520Mar132010demo_ora_pk_befores_updates.sql
-rwxr-xr-x1ora10dba8432527Jul292010emsclnt
-rwxr-xr-x1ora10dba39121727Jul292010extract
-r--r--r--1ora10dba1968May142009freeBSD.txt
-r--r--r--1ora10dba448512Jul72010ggMessage.dat
-rw-rw----1ora10dba204902400Jul292010ggs_Linux_x64_ora10g_64bit_v11_1_1_0_0_078.tar
-rwxr-xr-x1ora10dba16248101Jul292010ggsci
-r--r--r--1ora10dba133635Jul172010help.txt
-rwxr-xr-x1ora10dba105182Jul292010keygen
-r--r--r--1ora10dba11360748Mar202008libicudata.so.38
-r--r--r--1ora10dba5359083Mar202008libicui18n.so.38
-r--r--r--1ora10dba4034679Mar202008libicuuc.so.38
-r-xr-xr-x1ora10dba5323945Feb22010libxerces-c.so.28
-r--r--r--1ora10dba1668Mar92005libxml2.txt
-rwxr-xr-x1ora10dba1350044Jul292010logdump
-r--r--r--1ora10dba2162Mar132010marker_remove.sql
-r--r--r--1ora10dba3702Mar132010marker_setup.sql
-r--r--r--1ora10dba1715Mar132010marker_status.sql
-rwxr-xr-x1ora10dba21980806Jul292010mgr
-r--r--r--1ora10dba206097Jan162010notices.txt
-r--r--r--1ora10dba4539May212010params.sql
-rwxr-xr-x1ora10dba33431908Jul292010replicat
-rwxr-xr-x1ora10dba401915Jul292010reverse
-r--r--r--1ora10dba4042Mar132010role_setup.sql
-rwxr-xr-x1ora10dba13569641Jul292010server
-r--r--r--1ora10dba248Mar122007sqlldr.tpl
-r--r--r--1ora10dba759Mar122007tcperrs
-r--r--r--1ora10dba21197Apr62010usrdecs.h
-r--r--r--1ora10dba1476Mar92005zlib.txt
2)设置环境变量
[ora10@dev160gg10]$vi~/.bash_profile
#.bash_profile
#Getthealiasesandfunctions
if[-f~/.bashrc];then
.~/.bashrc
fi
#Userspecificenvironmentandstartupprograms
PATH=$PATH:
$HOME/bin
exportPATH
exportLANG=en_US:
zh_CN.UTF-8
exportLC_ALL=C
#exportDISPLAY=192.168.161.200:
0
exportORACLE_BASE=/ora10
exportORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
#exportORACLE_SID=ora10
exportORACLE_SID=crm20
exportLD_LIBRARY_PATH=$ORACLE_HOME/jdk/fre/lib/i386:
$ORACLE_HOME/jdk/jre/lib/i386/server:
$ORACLE_HOME/rdbms/lib:
$ORACLE_HOME/lib:
/home/ora10/gg10:
$LD_LIBRARY_PATH
exportPATH=$ORACLE_HOME/bin:
/home/ora10/gg10:
$PATH
exportNLS_LANG=American_America.ZHS16GBK
3)初始化
GGSCI(ora10g)4>createsubdirs
Creatingsubdirectoriesundercurrentdirectory/u01/ora10g/gg10
Parameterfiles/u01/ora10g/gg10/dirprm:
created
Reportfiles/u01/ora10g/gg10/dirrpt:
created
Checkpointfiles/u01/ora10g/gg10/dirchk:
created
Processstatusfiles/u01/ora10g/gg10/dirpcs:
created
SQLscript.files/u01/ora10g/gg10/dirsql:
created
Databasedefinitionsfiles/u01/ora10g/gg10/dirdef:
created
Extractdatafiles/u01/ora10g/gg10/dirdat:
created
Temporaryfiles/u01/ora10g/gg10/dirtmp:
created
Veridatafiles/u01/ora10g/gg10/dirver:
created
VeridataLockfiles/u01/ora10g/gg10/dirver/lock:
created
VeridataOut-Of-Syncfiles/u01/ora10g/gg10/dirver/oos:
created
VeridataOut-Of-SyncXMLfiles/u01/ora10g/gg10/dirver/oosxml:
created
VeridataParameterfiles/u01/ora10g/gg10/dirver/params:
created
VeridataReportfiles/u01/ora10g/gg10/dirver/report:
created
VeridataStatusfiles/u01/ora10g/gg10/dirver/status:
created
VeridataTracefiles/u01/ora10g/gg10/dirver/trace:
created
Stdoutfiles/u01/ora10g/gg10/dirout:
created
3)测试
[ora10@dev160gg10]$ggsci
OracleGoldenGateCommandInterpreterforOracle
Version11.1.1.0.0Build078
Linux,x64,64bit(optimized),Oracle10onJul28201013:
21:
11
Copyright(C)1995,2010,Oracleand/oritsaffiliates.Allrightsreserved.
GGSCI(dev160)1>help
GGSCICommandSummary:
Object:
Command:
SUBDIRSCREATE
ERINFO,KILL,LAG,SEND,STATUS,START,STATS,STOP
EXTRACTADD,ALTER,CLEANUP,DELETE,INFO,KILL,
LAG,SEND,START,STATS,STATUS,STOP
EXTTRAILADD,ALTER,DELETE,INFO
GGSEVTVIEW
MANAGERINFO,SEND,START,STOP,STATUS
MARKERINFO
PARAMSEDIT,VIEW
REPLICATADD,ALTER,CLEANUP,DELETE,INFO,KILL,LAG,SEND,
START,STATS,STATUS,STOP
REPORTVIEW
RMTTRAILADD,ALTER,DELETE,INFO
TRACETABLEADD,DELETE,INFO
TRANDATAADD,DELETE,INFO
CHECKPOINTTABLEADD,DELETE,CLEANUP,INFO
Commandswithoutanobject:
(Database)DBLOGIN,LISTTABLES,ENCRYPTPASSWORD
(DDL)DUMPDDL
(Miscellaneous)FC,HELP,HISTORY,INFOALL,OBEY,SETEDITOR,SHELL,
SHOW,VERSIONS,!
(note:
youmusttypetheword
COMMANDafterthe!
todisplaythe!
helptopic.)
i.e.:
GGSCI(sys1)>help!
command
Forhelponaspecificcommand,typeHELP
Example:
HELPADDREPLICAT
GGSCI(dev160)2>exit
2、创建管理用户及赋权:
createuserGGUSER
IDENTIFIEDbyGGUSER
defaulttablespaceUSERS
temporarytablespaceTEMP
profileDEFAULT
quotaunlimitedonUSERS;
--Grant/Revokeroleprivileges
grantconnecttoGGUSER;
grantresourcetoGGUSER;
--Grant/Revokesystemprivileges
grantunlimitedtablespacetoGGUSER;
3、由于GG需要关闭10G的回收站机制,因此进行如下操作:
SQL>altersystemsetrecyclebin=off;
Systemaltered.
4、由于GG需要开启数据库的归档,因此需要进行如下操作:
SQL>archiveloglist
DatabaselogmodeNoArchiveMode
AutomaticarchivalDisabled
ArchivedestinationUSE_DB_RECOVERY_FILE_DEST
Oldestonlinelogsequence40
Currentlogsequence42
SQL>alterdatabaseclose
2;
Databasealtered.
SQL>alterdatabasearchive;
alterdatabasearchive
*
ERRORatline1:
ORA-02231:
missingorinvalidoptiontoALTERDATABASE
SQL>alterdatabasearchivelog;
Databasealtered.
SQL>alterdatabaseopen
2;
alterdatabaseopen
*
ERRORatline1:
ORA-16196:
databasehasbeenpreviouslyopenedandclosed
SQL>shutdownimmediate
ORA-01109:
databasenotopen
Databasedismounted.
ORACLEinstanceshutdown.
SQL>startup
ORACLEinstancestarted.
TotalSystemGlobalArea222298112bytes
FixedSize1266680bytes
VariableSize134220808bytes
DatabaseBuffers79691776bytes
RedoBuffers7118848bytes
Databasemounted.
Databaseopened.
SQL>archiveloglist;
DatabaselogmodeArchiveMode
AutomaticarchivalEnabled
ArchivedestinationUSE_DB_RECOVERY_FILE_DEST
Oldestonlinelogsequence40
Nextlogsequencetoarchive42
Currentlogsequence42
5、安装同步DLL语句要用到的GoldenGate脚本
1)执行marker_setup脚本,该脚本用以建立一个DDL标记表
SQL>@/ora10/gg10/marker_setup(此处文件位置以具体各主机地址为准)
[ora10@dic5f88gg10]$sqlplus/assysdba
SQL*Plus:
Release10.2.0.4.0-ProductiononMonMar708:
35:
102011
Copyright(c)1982,2007,Oracle.AllRightsReserved.
Connectedto:
OracleDatabase10gEnterpriseEdition