经典抽取模式+ trigger based ddl 方式配置：
说明;本测试为简单配置过程中没有业务更改发生，对于存在业务更改的环境未涉及，下次补充
node1: 192.168.1.7
node2: 192.168.1.8
版本信息：
[oracle@node1 goldengate]$ ggsci -v
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
[oracle@node1 goldengate]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 31 10:49:37 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> connect /as sysDBA
Connected.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 goldengate]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)
[oracle@node1 goldengate]$ uarch -a
[oracle@node1 goldengate]$ arch
x86_64
[oracle@node1 goldengate]$
拓扑图
eddl1--------------------->aa----------------->pddl1----------------->bb------------>rddl1
node1 node2
rddl2<------------------dd<----------------------pddl2<---------------cc<------------eddl2
一.创建测试用户 on node1：
SQL> show user;
USER is "SYS"
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcc1/users01.dbf
/u01/app/oracle/oradata/orcc1/undotbs01.dbf
/u01/app/oracle/oradata/orcc1/sysaux01.dbf
/u01/app/oracle/oradata/orcc1/system01.dbf
/u01/app/oracle/oradata/orcc1/example01.dbf
/u01/app/oracle/oradata/orcc1/ogg.dbf
6 rows selected.
SQL> create tablespace tbs_student datafile '/u01/app/oracle/oradata/orcc1/student01.dbf' size 1g;
Tablespace created.
SQL> create user student
2 identified by student
3 account unlock
4 default tablespace tbs_student
5 quota unlimited on tbs_student;
User created.
SQL> grant connect,resource to student;
Grant succeeded.
SQL> connect student/student;
Connected.
SQL> select tname from tab;
no rows selected
SQL>
初始化测试用户
[oracle@node1 ~]$ pwd
/home/oracle
[oracle@node1 ~]$ ls -l
total 660
-rw-r--r-- 1 root root 666806 Mar 31 08:48 createStudent.SQL
-rw-r--r-- 1 oracle oinstall 6257 Apr 8 2016 logdump.hst
[oracle@node1 ~]$ sqlplus student/student
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 31 08:48:58 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @createStudent.SQL
查询生产数据库对象
SQL> col object_name for a50;
SQL> set linesize 100;
SQL> l
1* select object_name,object_type from user_objects
SQL> /
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------- -------------------
COURSE_NO_SEQ SEQUENCE
STUDENT_ID_SEQ SEQUENCE
SECTION_ID_SEQ SEQUENCE
INSTRUCTOR_ID_SEQ SEQUENCE
SECT_SECT2_UK INDEX
ZIP_PK INDEX
STU_PK INDEX
ENR_PK INDEX
CRSE_PK INDEX
SECT_PK INDEX
GRTW_PK INDEX
GRCON_PK INDEX
GRTYP_PK INDEX
GR_PK INDEX
INST_PK INDEX
STU_ZIP_FK_I INDEX
ENR_SECT_FK_I INDEX
CRSE_CRSE_FK_I INDEX
SECT_INST_FK_I INDEX
SECT_CRSE_FK_I INDEX
GRTW_GRTYP_FK_I INDEX
GR_GRTW_FK_I INDEX
INST_ZIP_FK_I INDEX
ZIPCODE TABLE
STUDENT TABLE
ENROLLMENT TABLE
COURSE TABLE
SECTION TABLE
GRADE_TYPE_WEIGHT TABLE
GRADE_CONVERSION TABLE
INSTRUCTOR TABLE
GRADE_TYPE TABLE
GRADE TABLE
33 rows selected.
SQL>
二.创建测试用户 on node2：
SQL> show user;
USER is "SYS"
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcc2/users01.dbf
/u01/app/oracle/oradata/orcc2/undotbs01.dbf
/u01/app/oracle/oradata/orcc2/sysaux01.dbf
/u01/app/oracle/oradata/orcc2/system01.dbf
/u01/app/oracle/oradata/orcc2/example01.dbf
/u01/app/oracle/oradata/orcc2/ogg.dbf
6 rows selected.
SQL> create tablespace tbs_student datafile '/u01/app/oracle/oradata/orcc2/student01.dbf' size 1g;
Tablespace created.
SQL> create user student
2 identified by student
3 account unlock
4 default tablespace tbs_student
5 quota unlimited on tbs_student;
User created.
SQL> grant connect,resource to student;
Grant succeeded.
SQL> connect student/student;
Connected.
SQL> select tname from tab;
no rows selected
SQL>
初始化测试用户
[oracle@node2 ~]$ pwd
/home/oracle
[oracle@node2 ~]$ ls -l
total 660
-rw-r--r-- 1 root root 666806 Mar 31 08:48 createStudent.SQL
-rw-r--r-- 1 oracle oinstall 6257 Apr 8 2016 logdump.hst
[oracle@node2 ~]$ sqlplus student/student
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 31 08:48:58 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @createStudent.SQL
查询生产数据库对象
SQL> col object_name for a50;
SQL> set linesize 100;
SQL> l
1* select object_name,object_type from user_objects
SQL> /
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------- -------------------
COURSE_NO_SEQ SEQUENCE
STUDENT_ID_SEQ SEQUENCE
SECTION_ID_SEQ SEQUENCE
INSTRUCTOR_ID_SEQ SEQUENCE
SECT_SECT2_UK INDEX
ZIP_PK INDEX
STU_PK INDEX
ENR_PK INDEX
CRSE_PK INDEX
SECT_PK INDEX
GRTW_PK INDEX
GRCON_PK INDEX
GRTYP_PK INDEX
GR_PK INDEX
INST_PK INDEX
STU_ZIP_FK_I INDEX
ENR_SECT_FK_I INDEX
CRSE_CRSE_FK_I INDEX
SECT_INST_FK_I INDEX
SECT_CRSE_FK_I INDEX
GRTW_GRTYP_FK_I INDEX
GR_GRTW_FK_I INDEX
INST_ZIP_FK_I INDEX
ZIPCODE TABLE
STUDENT TABLE
ENROLLMENT TABLE
COURSE TABLE
SECTION TABLE
GRADE_TYPE_WEIGHT TABLE
GRADE_CONVERSION TABLE
INSTRUCTOR TABLE
GRADE_TYPE TABLE
GRADE TABLE
33 rows selected.
SQL>
三.启用ddl,两端均配置
配置OGG支持DDL
1、赋予ogg用户相应的权限，修改全局配置文件添加ggschema参数（源端操作）
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> grant execute on utl_file to ogg;
Grant succeeded.
GGSCI> edit param ./GLOBALS
GGSCI> view param ./GLOBALS
ggschema ogg
checkpointtable ogg.checkpoint
2、运行相关sql脚本（源端操作）
[oracle@acarsorcl-primary ~]$ cd /u01/ogg
[oracle@acarsorcl-primary ogg]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 18 15:56:33 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @marker_setup.sql
Marker setup script
You will be prompted 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 this installation.
Enter Oracle GoldenGate schema name gg
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema namegg
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
Using OGG as a Oracle GoldenGate schema name.
Working, please wait ...
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
CLEAR_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
NONE
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted 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 this installation.
Enter GoldenGate schema namegg
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> grant ggs_ggsuser_role to ogg;
Grant succeeded.
SQL> @ddl_enable.sql
Trigger altered.
SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
SQL> @ddl_pin.sql ogg
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
执行ggsci
dblogin userid ogg, password ogg
add schematrandata student
host1:
正向抽取进程：
cd $GGHOME
ggsci
add extract eddl1, tranlog, begin now
add exttrail ./dirdat/aa ,extract eddl1, megabytes 100
edit params eddl1
extract eddl1
TRANLOGOPTIONS EXCLUDEUSER ogg
userid ogg, password ogg
DDL INCLUDE ALL
ddloptions report
ddloptions addtrandata
ddloptions getreplicates
exttrail ./dirdat/aa
table student.*;
正向投递进程：
cd $GGHOME
ggsci
add extract pddl1, exttrailsource ./dirdat/aa
add rmttrail ./dirdat/bb, extract pddl1, megabytes 100
edit params pddl1
extract pddl1
passthru
userid ogg, password ogg
rmthost node2, mgrport 7809
rmttrail ./dirdat/bb
table student.*;
反向复制进程：
cd $GGHOME
ggsci
add replicat rddl2 exttrail ./dirdat/dd checkpointtable ogg.checkpoint
edit params rddl2
replicat rddl2
----assumetargetdefs
userid ogg,password ogg
ddl include all
ddloptions report
ddloptions updatemetadata
discardfile ./dirrpt/rddl.dsc, purge
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
map student.*, target student.*;
host2:
反向抽取进程：
add extract eddl2, tranlog, begin now;
add exttrail ./dirdat/cc extract eddl2, megabytes 100
extract eddl2
userid ogg,password ogg
TRANLOGOPTIONS EXCLUDEUSER ogg
DDL INCLUDE ALL
ddloptions report
ddloptions addtrandata
ddloptions getreplicates
exttrail ./dirdat/cc
table student.*;
反向投递进程：
add extract pddl2, exttrailsource ./dirdat/cc
add rmttrail ./dirdat/dd, extract pddl2, megabytes 100
extract pddl2
passthru
userid ogg, password ogg
rmthost node1, mgrport 7809
rmttrail ./dirdat/dd
table student.*;
正向复制进程：
add replicat rddl1 exttrail ./dirdat/bb checkpointtable ogg.checkpoint
replicat rddl1
----assumetargetdefs
userid ogg, password ogg
ddl include all
ddloptions report
ddloptions updatemetadata
discardfile ./dirrpt/rddl.dsc, purge
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
map student.*, target student.*;
1.启动顺序
eddl1--------pddl1--------rddl1-------eddl2-----pddl2----rddl2
检查进程状态：
node1:
GGSCI (node1 as ogg@orcc1) 86> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
JAGENT STOPPED
EXTRACT RUNNING CLASS_EX 00:00:00 00:00:01
EXTRACT RUNNING CLASS_PU 00:00:00 00:00:06
EXTRACT RUNNING EDDL1 00:00:03 00:00:00
EXTRACT RUNNING EFORMAT 00:00:02 00:00:00
EXTRACT RUNNING EGGTEST1 00:00:00 00:00:07
EXTRACT RUNNING EOLTP01 00:00:00 00:00:03
EXTRACT RUNNING EPUMP01 00:00:00 00:00:00
EXTRACT RUNNING HREXT 00:00:01 00:00:01
EXTRACT RUNNING HRPUMP 00:00:00 00:00:04
EXTRACT RUNNING MYDP1 00:00:00 00:00:00
EXTRACT RUNNING MYEXT1 00:00:05 00:00:08
EXTRACT RUNNING PDDL1 00:00:00 00:00:06
EXTRACT RUNNING PGGTEST1 00:00:00 00:00:08
REPLICAT RUNNING RDDL2 00:00:00 00:52:16
node2:
GGSCI (node2 as ogg@orcc2) 53> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
JAGENT STOPPED
EXTRACT RUNNING EDDL2 00:00:00 00:00:04
EXTRACT RUNNING PDDL2 00:00:00 00:00:27
REPLICAT RUNNING CLASS_RE 00:00:00 00:00:05
REPLICAT RUNNING HRREP 00:02:18 00:01:50
REPLICAT RUNNING RDDL1 00:00:00 00:00:04
REPLICAT RUNNING REP1 00:00:00 00:00:00
REPLICAT RUNNING RGGTEST1 00:00:00 00:00:03
测试ddl:
正向：
node1:
SQL> create table test(id int primary key);
Table created.
SQL> insert into test values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
1
node2:
GGSCI (node2) 20> exit
[oracle@node2 goldengate]$ sqlplus student/student
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 31 10:43:54 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
SQL> select count(*) from test;
COUNT(*)
----------
1
SQL>
反向：
node2:
oracle@node2 goldengate]$ sqlplus student/student
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 31 10:43:54 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
SQL> select count(*) from test;
COUNT(*)
----------
1
SQL> create table test1 (id int primary key);
Table created.
SQL> commit;
Commit complete.
SQL> insert into test1 values (1);
1 row created.
SQL> commit;
Commit complete.
node1:
[oracle@node1 goldengate]$ sqlplus student/student
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 31 10:46:30 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc test1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
SQL> select count(*) from test1;
COUNT(*)
----------
1
SQL>
|
|
京ICP备12024223-8号
不良信息举报电话：13718043309
转播
分享
淘帖