Oracle goldengate 11g （二）【DML and DDL单向复制】
实验环境，oraclegoldengate 安装，仅开启DML：请看
Oracle goldengate 11g （一）【DML单向复制】
实验整体思路
一、 停止所有extract、replicat进程
二、 Oracle 数据库准备
三、 添加DDL所需参数
四、 Oracle goldengate DDL 测试
一、停止所有extract、replicat进程
Source database
GGSCI(doudou-NAS) 4> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 00:00:15
EXTRACT STOPPED PUMP1 00:00:00 00:00:14
Target database
GGSCI (localhost.localdomain)4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
REPLICAT STOPPED REP1 00:00:00 00:00:15
二、Oracle 数据库准备
SQL> conn /as sysdba
Connected.
SQL> @marker_setup.sql
Markersetup script
You will beprompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE:The schema must be created prior to running this script.
NOTE:Stop all DDL replication before starting thisinstallation.
EnterOracle GoldenGate schema name:ogg
Markersetup table script complete, running verification script...
Pleaseenter the name of a schema for the GoldenGate database objects:
Settingschema name to OGG
MARKERTABLE
-------------------------------
OK
MARKERSEQUENCE
-------------------------------
OK
Scriptcomplete.
SQL> @ddl_setup.sql
OracleGoldenGate DDL Replication setup script
Verifyingthat current user has privileges to install DDL Replication...
You will beprompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: Foran Oracle 10g source, the system recycle bin must be disabled. For Oracle 11gand later, it can be enabled.
NOTE:The schema must be created prior to running this script.
NOTE:Stop all DDL replication before starting thisinstallation.
Enter OracleGoldenGate schema name:ogg
Working,please wait ...
Spooling tofile ddl_setup_spool.txt
Checkingfor sessions that are holding locks on Oracle Golden Gate metadata tables ...
Checkcomplete.
Using OGGas a Oracle GoldenGate schema name.
Working,please wait ...
DDLreplication setup script complete, running verification script...
Pleaseenter the name of a schema for the GoldenGate database objects:
Settingschema name to OGG
CLEAR_TRACESTATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
CREATE_TRACESTATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
TRACE_PUT_LINESTATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
INITIAL_SETUPSTATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
DDLVERSIONSPECIFICPACKAGE STATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
DDLREPLICATIONPACKAGE STATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
DDLREPLICATIONPACKAGE BODY STATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
DDL IGNORETABLE
-----------------------------------
OK
DDL IGNORELOG TABLE
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
DDLAUXPACKAGE BODY STATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
DDL HISTORYTABLE
-----------------------------------
OK
DDL HISTORYTABLE(1)
-----------------------------------
OK
DDL DUMPTABLES
-----------------------------------
OK
DDL DUMPCOLUMNS
-----------------------------------
OK
DDL DUMPLOG GROUPS
-----------------------------------
OK
DDL DUMPPARTITIONS
-----------------------------------
OK
DDL DUMPPRIMARY KEYS
-----------------------------------
OK
DDLSEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGERCODE STATUS:
Line/pos Error
---------------------------------------------------------------------------------------------------------
Noerrors No errors
DDL TRIGGERINSTALL STATUS
-----------------------------------
OK
DDL TRIGGERRUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
STAYMETADATAIN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF
DDL TRIGGERSQL TRACING
------------------------------------------------------------------------------------------------------------------------
0
DDL TRIGGERTRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0
LOCATION OFDDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/doudou/doudou/trace/ggs_ddl_trace.log
Analyzinginstallation status...
STATUS OFDDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFULinstallation of DDL Replication software components
Scriptcomplete.
SQL> @role_setup.sql
GGS Rolesetup script
This scriptwill drop and recreate the role GGS_GGSUSER_ROLE
To use adifferent role name, quit this script and then edit the params.sql script tochange the gg_role parameter to the preferred name. (Do not run the script.)
You will beprompted for the name of a schema for the GoldenGate database objects.
NOTE:The schema must be created prior to running this script.
NOTE:Stop all DDL replication before starting thisinstallation.
EnterGoldenGate schema name:ogg
Wrote filerole_setup_set.txt
PL/SQLprocedure successfully completed.
Role setupscript complete
Grant thisrole to each user assigned to the Extract, GGSCI, and Manager processes, byusing the following SQL command:
GRANTGGS_GGSUSER_ROLE TO <loggedUser>
where<loggedUser> is the user assigned to the GoldenGate processes.
SQL> GRANTGGS_GGSUSER_ROLE TO ogg;
Grantsucceeded.
SQL> @ddl_enable --开启DDL功能
Triggeraltered.
SQL> @marker_status.sql --校验DDL状态
Pleaseenter the name of a schema for the GoldenGate database objects:
ogg
Settingschema name to OGG
MARKERTABLE
-------------------------------
OK
MARKERSEQUENCE
-------------------------------
OK
三、添加DDL所需参数
3-1、Add ddl support to the extract process
GGSCI(doudou-NAS) 8> view params ext1
extract ext1
userid ogg,password oracle
exttrail /opt/ogg/dirdat/tt
TRANLOGOPTIONS asmuser sys@asm,asmpassword oracle
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
table doudou.*;
3-2、Add ddl support to the replicat process
GGSCI(localhost.localdomain) 17> edit params rep1
replicat rep1
userid ogg ,password oracle
assumetargetdefs
dynamicresolution
DISCARDFILE ./dirrpt/rhr.dsc, append,megabytes 100
DDLERROR DEFAULT IGNORE
map doudou.*,target doudou.*;
GGSCI(doudou-NAS) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:04
EXTRACT RUNNING PUMP1 00:00:00 00:00:01
GGSCI (localhost.localdomain)19> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:02
到此Oracle goldengate DDL安装结束
四、Oracle goldengateDDL 测试
Source database
SQL>select * from test;
NAME
------------------------------------------------------------------------------------------------------------------------
ID
----------
兜兜
1
Target database
SQL>select * from test;
NAME
------------------------------------------------------------------------------------------------------------------------
ID
----------
兜兜
1
Source database
SQL>truncate table test;
Tabletruncated.
SQL>commit;
Commitcomplete.
SQL>select * from test;
no rowsselected
Target database
SQL>select * from test;
no rowsselected
--DDL truncate操作，已经同步到了目标库上
源库：建立一个用户xiaoqiang并建立表xiaoqiang和数据
目标库：用户被建立xiaoqiang和表xiaoqiang被建立，但是数据未同步
Source database
SQL>conn /as sysdba
Connected.
SQL>create user xiaoqiang identified by oracle;
Usercreated.
SQL>grant connect,resource to xiaoqiang;
Grantsucceeded.
SQL>conn xiaoqiang/oracle
Connected.
SQL>create table xiaoqiang (name varchar(200),id number);
Tablecreated.
SQL>insert into xiaoqiang values ('小强',1);
1 rowcreated.
SQL>commit;
Commitcomplete.
Target database
SQL>conn xiaoqiang/oracle
Connected.
SQL>select * from xiaoqiang;
no rowsselected
--DDL create user操作已经同步到了目标库，但是insert操作并没有同步，因为我们并没有配置xiaoqiang用户的DML，这里xiaoqiang用户的DDL操作之所以能同步到目标库因为我们配置了DDL INCLUDE ALL
想一想？
如果我们删除的用户是doudou，建立新用户doudou并插入数据，目标库就会用户建立，表建立，数据填入
到此证明oracle goldengate的 DDL已经成功
总结：
Oracle goldengate DDL 成功
参考文档
https://blogs.oracle.com/imc/entry/oracle_goldengate_configuring_ddl_replication
扫一扫
2282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
