下面为GoldenGate 12c + Oracle 12c Multitenant Container databases例子
1、安装OGG
源 端OGG: C:\Oracle\product\12.1.2\OGG_Source
目标端OGG: C:\Oracle\product\12.1.2\OGG_Target
2、创建测试的用户及表格
源表：
SQL> alter session set container=pdborcl; 会话已更改。 SQL> show con_name; CON_NAME ------------------------------ PDBORCL SQL> create user gg_src identified by gg_src; 用户已创建。 SQL> grant dba to gg_src; 授权成功。 SQL> create table tab01(id number(8),name varchar2(20)); 表已创建。
目标表
SQL> alter session set container=pdborcl2; 会话已更改。 SQL> show con_name; CON_NAME ------------------------------ PDBORCL2 SQL> create user gg_stg identified by gg_stg; 用户已创建。 SQL> grant dba to gg_stg; 授权成功。 SQL> create table tab01(id number(8),name varchar2(20)); 表已创建。
3、配置数据库归档模式
查看归档模式
select log_mode from v$database;
更改为归档模式
shutdown immediate; startup mount; alter database archivelog; alter database open;
4、设置supplemental logging and FORCE LOGGING;
sql> alter database add supplemental log data; sql> alter database force logging; sql> alter system switch logfile; sql>select supplemental_log_data_min, force_logging from v$database;
5、创建Common User
SQL*Plus: Release 12.1.0.1.0 Production on 星期二 5月 26 15:42:19 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
请输入用户名: sys as sysdba
输入口令:
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create user C##GGADMIN identified by ggadmin;
用户已创建。
SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##GGADMIN',container=>'ALL');
PL/SQL 过程已成功完成。
SQL> grant dba to c##ggadmin container=all;
授权成功。6、源端OGG配置
Add supplemental logging for the gg_src schema
Note: here we are connecting to the pluggable database pdborcl
Microsoft Windows [版本 6.1.7601] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\XQZT>cd C:\Oracle\product\12.1.2\OGG_Source C:\Oracle\product\12.1.2\OGG_Source>ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203 Windows x64 (optimized), Oracle 12c on Sep 20 2014 14:25:39 Operating system character set identified as GBK. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (XQZT-PC) 1> dblogin userid C##GGADMIN@pdborcl password ggadmin Successfully logged into database PDBORCL. GGSCI (XQZT-PC as C##GGADMIN@orcl/PDBORCL) 2> ADD SCHEMATRANDATA gg_src ALLCOLS 2015-05-26 15:53:53 INFO OGG-01788 SCHEMATRANDATA has been added on schema gg_src. 2015-05-26 15:53:53 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema gg_src. 2015-05-26 15:53:53 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema gg_src. GGSCI (XQZT-PC as C##GGADMIN@orcl/PDBORCL) 3>
Register the Integrated Extract
Note: here we are connecting to the root container database.
Microsoft Windows [版本 6.1.7601] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\XQZT>cd C:\Oracle\product\12.1.2\OGG_Source C:\Oracle\product\12.1.2\OGG_Source>ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203 Windows x64 (optimized), Oracle 12c on Sep 20 2014 14:25:39 Operating system character set identified as GBK. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (XQZT-PC) 1> dblogin userid C##GGADMIN password ggadmin Successfully logged into database CDB$ROOT. GGSCI (XQZT-PC as C##GGADMIN@orcl/CDB$ROOT) 2> REGISTER EXTRACT ext1 DATABASE CONTAINER (pdborcl) Extract EXT1 successfully registered with database at SCN 4296859. GGSCI (XQZT-PC as C##GGADMIN@orcl/CDB$ROOT) 3>
Add the Extract and Data Pump process groups
Microsoft Windows [版本 6.1.7601] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\XQZT>cd C:\Oracle\product\12.1.2\OGG_Source C:\Oracle\product\12.1.2\OGG_Source>ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203 Windows x64 (optimized), Oracle 12c on Sep 20 2014 14:25:39 Operating system character set identified as GBK. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (XQZT-PC) 1> dblogin userid C##GGADMIN password ggadmin Successfully logged into database CDB$ROOT. GGSCI (XQZT-PC as C##GGADMIN@orcl/CDB$ROOT) 2> REGISTER EXTRACT ext1 DATABASE CONTAINER (pdborcl) Extract EXT1 successfully registered with database at SCN 4296859. GGSCI (XQZT-PC as C##GGADMIN@orcl/CDB$ROOT) 3> ADD EXTRACT ext1 INTEGRATED TRANLOG, BEGIN NOW EXTRACT added. GGSCI (XQZT-PC as C##GGADMIN@orcl/CDB$ROOT) 4> ADD EXTTRAIL C:\Oracle\product\12.1.2\OGG_Source\dirdat\lt EXTRACT ext1 EXTTRAIL added. GGSCI (XQZT-PC as C##GGADMIN@orcl/CDB$ROOT) 5> ADD EXTRACT extdp1 EXTTRAILSOURCE C:\Oracle\product\12.1.2\OGG_Source\dirdat\lt BEGIN NOW EXTRACT added. GGSCI (XQZT-PC as C##GGADMIN@orcl/CDB$ROOT) 6> ADD RMTTRAIL C:\Oracle\product\12.1.2\OGG_Target\dirdat\rt EXTRACT extdp1 RMTTRAIL added. GGSCI (orasql-001-dev.mydomain) 7> edit params ext1 GGSCI (orasql-001-dev.mydomain) 8> view params ext1 EXTRACT ext1 SETENV (ORACLE_SID='ORCL') userid C##GGADMIN@PDBORCL, password ggadmin LOGALLSUPCOLS UPDATERECORDFORMAT COMPACT EXTTRAIL C:\Oracle\product\12.1.2\OGG_Source\dirdat\lt SOURCECATALOG PDBORCL TABLE gg_src.*; GGSCI (orasql-001-dev.mydomain) 9> edit params extdp1 GGSCI (orasql-001-dev.mydomain) 10> view params extdp1 EXTRACT extdp1 SETENV (ORACLE_SID='ORCL') userid C##GGADMIN@PDBORCL, password ggadmin RMTHOST 127.0.0.1, MGRPORT 7810 RMTTRAIL C:\Oracle\product\12.1.2\OGG_Target\dirdat\rt SOURCECATALOG PDBORCL TABLE gg_src.*; GGSCI (XQZT-PC as C##GGADMIN@orcl/CDB$ROOT) 11>
7、目标端配置OGG
Microsoft Windows [版本 6.1.7601] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\XQZT>cd C:\Oracle\product\12.1.2\OGG_Target C:\Oracle\product\12.1.2\OGG_Target>ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203 Windows x64 (optimized), Oracle 12c on Sep 20 2014 14:25:39 Operating system character set identified as GBK. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (XQZT-PC) 1> DBLOGIN USERID C##ggadmin@pdborcl2, PASSWORD ggadmin Successfully logged into database PDBORCL2. GGSCI (XQZT-PC as C##ggadmin@orcl/PDBORCL2) 2> ADD REPLICAT rep1 INTEGRATED EXTTRAIL C:\Oracle\product\12.1.2\OGG_Target\dirdat\rt REPLICAT (Integrated) added. GGSCI (XQZT-PC as C##ggadmin@orcl/PDBORCL2) 3> edit params rep1 GGSCI (XQZT-PC as C##ggadmin@orcl/PDBORCL2) 4> view params rep1 REPLICAT rep1 SETENV (ORACLE_SID='ORCL') DBOPTIONS INTEGRATEDPARAMS(parallelism 6) USERID C##GGADMIN@PDBORCL2, PASSWORD ggadmin ASSUMETARGETDEFS MAP PDBORCL.gg_src.*, TARGET PDBORCL2.gg_stg.*; GGSCI (XQZT-PC as C##ggadmin@orcl/PDBORCL2) 5> view param mgr PORT 7810 GGSCI (XQZT-PC as C##ggadmin@orcl/PDBORCL2) 6>
8、启动extract和replicat进程
源端：
Microsoft Windows [版本 6.1.7601] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\XQZT>cd C:\Oracle\product\12.1.2\OGG_Source C:\Oracle\product\12.1.2\OGG_Source>ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203 Windows x64 (optimized), Oracle 12c on Sep 20 2014 14:25:39 Operating system character set identified as GBK. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (XQZT-PC) 1> start mgr Manager started. GGSCI (XQZT-PC) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EXT1 00:00:02 00:00:59 EXTRACT STOPPED EXTDP1 00:00:00 00:00:59 GGSCI (XQZT-PC) 3> start extract ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting GGSCI (XQZT-PC) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:01:15 00:00:04 EXTRACT STOPPED EXTDP1 00:00:00 00:01:17 GGSCI (XQZT-PC) 5> start extract extdp1 Sending START request to MANAGER ... EXTRACT EXTDP1 starting GGSCI (XQZT-PC) 6> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:02 00:00:08 EXTRACT RUNNING EXTDP1 00:19:01 00:00:00 GGSCI (XQZT-PC) 7> info extract ext1 EXTRACT EXT1 Last Started 2015-05-26 16:17 Status RUNNING Checkpoint Lag 00:00:01 (updated 00:00:06 ago) Process ID 9872 Log Read Checkpoint Oracle Integrated Redo Logs 2015-05-26 16:18:16 SCN 0.4345601 (4345601) GGSCI (XQZT-PC) 8> info extract extdp1 EXTRACT EXTDP1 Last Started 2015-05-26 16:18 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:06 ago) Process ID 7892 Log Read Checkpoint File C:\Oracle\product\12.1.2\OGG_Source\dirdat\lt000001 2015-05-26 16:17:57.784000 RBA 1458 GGSCI (XQZT-PC) 9>
目标端：
Microsoft Windows [版本 6.1.7601] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\XQZT>cd C:\Oracle\product\12.1.2\OGG_Target C:\Oracle\product\12.1.2\OGG_Target>ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203 Windows x64 (optimized), Oracle 12c on Sep 20 2014 14:25:39 Operating system character set identified as GBK. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (XQZT-PC) 1> start mgr Manager started. GGSCI (XQZT-PC) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED REP1 00:00:00 00:06:59 GGSCI (XQZT-PC) 3> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (XQZT-PC) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:13:39 GGSCI (XQZT-PC) 5>
9、测试配置结果
连接到源PDB并插入数据
SQL> show con_name; CON_NAME ------------------------------ PDBORCL SQL> select * from tab01; 未选定行 SQL> insert into tab01(id,name) values(1,'1'); 已创建 1 行。 SQL> commit; 提交完成。 SQL>
连接到目标PDB，查看是否已经同步
SQL> show con_name; CON_NAME ------------------------------ PDBORCL2 SQL> select * from tab01; ID NAME ---------- ---------------------------------------- 1 1 SQL>
查看extract
GGSCI (XQZT-PC) 11> STATS EXT1 Sending STATS request to EXTRACT EXT1 ... Start of Statistics at 2015-05-26 16:34:06. DDL replication statistics (for all trails): *** Total statistics since extract started *** Operations 1.00 Output to C:\Oracle\product\12.1.2\OGG_Source\dirdat\lt: Extracting from PDBORCL.GG_SRC.TAB01 to PDBORCL.GG_SRC.TAB01: *** Total statistics since 2015-05-26 16:30:01 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2015-05-26 16:30:01 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2015-05-26 16:30:01 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2015-05-26 16:30:01 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 End of Statistics. GGSCI (XQZT-PC) 12> STATS EXTDP1 Sending STATS request to EXTRACT EXTDP1 ... Start of Statistics at 2015-05-26 16:34:16. Output to C:\Oracle\product\12.1.2\OGG_Target\dirdat\rt: Extracting from PDBORCL.GG_SRC.TAB01 to PDBORCL.GG_SRC.TAB01: *** Total statistics since 2015-05-26 16:30:08 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2015-05-26 16:30:08 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2015-05-26 16:30:08 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2015-05-26 16:30:08 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 End of Statistics. GGSCI (XQZT-PC) 13>
查看replicat
GGSCI (XQZT-PC) 5> stats rep1 Sending STATS request to REPLICAT REP1 ... Start of Statistics at 2015-05-26 16:32:11. Integrated Replicat Statistics: Total transactions 1.00 Redirected 0.00 DDL operations 0.00 Stored procedures 0.00 Datatype functionality 0.00 Event actions 0.00 Direct transactions ratio 0.00% Replicating from PDBORCL.GG_SRC.TAB01 to PDBORCL2.GG_STG.TAB01: *** Total statistics since 2015-05-26 16:30:14 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2015-05-26 16:30:14 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2015-05-26 16:30:14 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2015-05-26 16:30:14 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 End of Statistics. GGSCI (XQZT-PC) 6>
原文地址
http://gavinsoorma.com/2014/01/using-goldengate-12c-with-an-oracle-12c-multitenant-database/
http://www.zhongweicheng.com/?p=755
http://docs.oracle.com/goldengate/1212/gg-winux/GIORA/config_containerdb.htm#GIORA942
goldengate 12c 针对oracle 12c配置的主要变化