CNDBA社区
1.环境：
操作系统：Oracle linux 5.7
数据库：11.2.0.3
源库：192.168.169.135
目标库：192.168.169.136
ogg：ogg112101_fbo_ggs_Linux_x64_ora11g_64bit
2.安装ogg软件
安装很简单，源库和目标库都要安装
上传
[root@lei ~]# cd /u01/ [root@lei u01]# mkdir ogg [root@lei u01]# cd ogg/ [root@lei ogg]# rz -E rz waiting to receive. [root@lei ogg]# ll total 87192 -rw-r--r-- 1 root root 89186858 Jun 8 09:23 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
解压
[root@lei ogg]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip Archive: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf inflating: Oracle GoldenGate 11.2.1.0.1 README.txt inflating: Oracle GoldenGate 11.2.1.0.1 README.doc [root@lei ogg]# ll total 310956 -rw-rw-r-- 1 root root 228556800 Apr 23 2012 fbo_ggs_Linux_x64_ora11g_64bit.tar -rw-r--r-- 1 root root 89186858 Jun 8 09:23 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
再解压
[root@lei ogg]# tar -xvof fbo_ggs_Linux_x64_ora11g_64bit.tar
赋权限
[root@lei ogg]# chown -R oracle:oinstall /u01/
用oracle用户进入ogg后台
[oracle@lei ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (lei) 1> create subdirs Creating subdirectories under current directory /u01/ogg Parameter files /u01/ogg/dirprm: already exists Report files /u01/ogg/dirrpt: created Checkpoint files /u01/ogg/dirchk: created Process status files /u01/ogg/dirpcs: created SQL script files /u01/ogg/dirsql: created Database definitions files /u01/ogg/dirdef: created Extract data files /u01/ogg/dirdat: created Temporary files /u01/ogg/dirtmp: created Stdout files /u01/ogg/dirout: created GGSCI (lei) 2> exit
OK安装好了。。
3.开始配置ogg
3.1将源端数据同步到目标端
源库上创建专用表空间，用户
SQL> create tablespace ggs datafile '/u01/app/oracle/oradata/orcl/ggs01.dbf' size 50m,'/u01/app/oracle/oradata/orcl/ggs02.dbf' size 50m; Tablespace created. SQL> create user gate01 identified by gate01 default tablespace ggs temporary tablespace TEMP quota unlimited on ggs; User created. SQL> grant connect,resource,dba to gate01; Grant succeeded.
在源数据库上建数据库用户 gate 01 的表 TC USTME R、 TC USTOR D，并插入记录
SQL> @/u01/ogg/demo_ora_create.sql DROP TABLE tcustmer * ERROR at line 1: ORA-00942: table or view does not exist Table created. DROP TABLE tcustord * ERROR at line 1: ORA-00942: table or view does not exist Table created. SQL> @/u01/ogg/demo_ora_insert.sql 1 row created. 1 row created. 1 row created. 1 row created. Commit complete. SQL> commit; Commit complete.
在目标库上同样创建表空间，用户
SQL> create tablespace ggs datafile '/u01/app/oracle/oradata/orcl/ggs01.dbf' size 50m,'/u01/app/oracle/oradata/orcl/ggs02.dbf' size 50m; Tablespace created. SQL> SQL> SQL> create user gate02 identified by gate02 default tablespace ggs temporary tablespace TEMP quota unlimited on ggs; User created. SQL> grant connect,resource,dba to gate02; Grant succeeded.
在目标数据库上建数据库用户 gate02 的表 TCUSTMER、 TCUSTORD，不需要插入
记录
SQL> conn gate02/gate02 Connected. SQL> @/u01/ogg/demo_ora_create.sql DROP TABLE tcustmer * ERROR at line 1: ORA-00942: table or view does not exist Table created. DROP TABLE tcustord * ERROR at line 1: ORA-00942: table or view does not exist Table created.
3.2源库上配置管理进程MGR
GGSCI (lei) 13> start extract eora01 Sending START request to MANAGER ... EXTRACT EORA01 starting GGSCI (lei) 14> view report eora01------查看是否有报错 2015-10-26 16:03:44 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used . *********************************************************************** Oracle GoldenGate Capture for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:42:16 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2015-10-26 16:03:44 *********************************************************************** Operating System Version: Linux Version #1 SMP Wed Feb 22 17:37:40 EST 2012, Release 2.6.32-300.10.1.el5uek Node: lei Machine: x86_64 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 10304 Description: *********************************************************************** ** Running with the following parameters ** *********************************************************************** 2015-10-26 16:03:44 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:. extract eora01 userid gate01, password ****** 2015-10-26 16:03:44 INFO OGG-03500 WARNING: NLS_LANG environment variable does not match database cha racter set, or not set. Using database character set value of WE8MSWIN1252. rmthost 192.168.169.136, mgrport 7809 rmttask replicat, group rora01 table gate01.*; Wildcard TABLE resolved (entry gate01.*): table "GATE01"."TCUSTMER"; Using the following key columns for source table GATE01.TCUSTMER: CUST_CODE. Wildcard TABLE resolved (entry gate01.*): table "GATE01"."TCUSTORD"; Using the following key columns for source table GATE01.TCUSTORD: CUST_CODE, ORDER_DATE, PRODUCT_CODE, ORD ER_ID. 2015-10-26 16:03:45 INFO OGG-01815 Virtual Memory Facilities for: COM anon alloc: mmap(MAP_ANON) anon free: munmap file alloc: mmap(MAP_SHARED) file free: munmap target directories: /u01/ogg/dirtmp. CACHEMGR virtual memory values (may have been adjusted) CACHESIZE: 64G CACHEPAGEOUTSIZE (normal): 8M PROCESS VM AVAIL FROM OS (min): 128G CACHESIZEMAX (strict force to disk): 96G Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE11.2.0.3.0Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production Database Language and Character Set: NLS_LANG = ".WE8MSWIN1252" NLS_LANGUAGE = "AMERICAN" NLS_TERRITORY = "AMERICA" NLS_CHARACTERSET = "WE8MSWIN1252" Processing table GATE01.TCUSTMER Processing table GATE01.TCUSTORD *********************************************************************** * ** Run Time Statistics ** * *********************************************************************** Report at 2015-10-26 16:03:55 (activity since 2015-10-26 16:03:45) Output to rora01: From Table GATE01.TCUSTMER: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0 From Table GATE01.TCUSTORD: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0 REDO Log Statistics Bytes parsed 0 Bytes output 578
目标库上查看是否有报错；
GGSCI (lei) 9> VIEW REPORT RORA01 *********************************************************************** Oracle GoldenGate Delivery for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:48:07 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. Starting at 2015-10-26 16:03:46 *********************************************************************** Operating System Version: Linux Version #1 SMP Wed Feb 22 17:37:40 EST 2012, Release 2.6.32-300.10.1.el5uek Node: lei Machine: x86_64 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 10060 Description: *********************************************************************** ** Running with the following parameters ** *********************************************************************** 2015-10-26 16:03:51 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:. REPLICAT Rora01 ASSUMETARGETDEFS USERID gate02, PASSWORD ******** DISCARDFILE ./dirrpt/Rora01.dsc, PURGE MAP gate01.*, TARGET gate02.*; 2015-10-26 16:03:53 INFO OGG-01815 Virtual Memory Facilities for: COM anon alloc: mmap(MAP_ANON) anon free: munmap file alloc: mmap(MAP_SHARED) file free: munmap target directories: /u01/ogg/dirtmp. CACHEMGR virtual memory values (may have been adjusted) CACHESIZE: 2G CACHEPAGEOUTSIZE (normal): 8M PROCESS VM AVAIL FROM OS (min): 4G CACHESIZEMAX (strict force to disk): 3.41G Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE11.2.0.3.0Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production Database Language and Character Set: NLS_LANG = "American_America.zhs16gbk" NLS_LANGUAGE = "AMERICAN" NLS_TERRITORY = "AMERICA" NLS_CHARACTERSET = "WE8MSWIN1252" *********************************************************************** ** Run Time Messages ** *********************************************************************** Wildcard MAP resolved (entry gate01.*): MAP "GATE01"."TCUSTMER", TARGET gate02."TCUSTMER"; Using following columns in default map by name: CUST_CODE, NAME, CITY, STATE Using the following key columns for target table GATE02.TCUSTMER: CUST_CODE. 2015-10-26 16:03:55 WARNING OGG-03504 NLS_LANG character set ZHS16GBK on the target is different from th e source database character set WE8MSWIN1252. Replication may not be valid if the source data has an incom patible character for the target NLS_LANG character set. Wildcard MAP resolved (entry gate01.*): MAP "GATE01"."TCUSTORD", TARGET gate02."TCUSTORD"; Using following columns in default map by name: CUST_CODE, ORDER_DATE, PRODUCT_CODE, ORDER_ID, PRODUCT_PRICE, PRODUCT_AMOUNT, TRANSACTION_ID Using the following key columns for target table GATE02.TCUSTORD: CUST_CODE, ORDER_DATE, PRODUCT_CODE, ORD ER_ID. *********************************************************************** * ** Run Time Statistics ** * *********************************************************************** Report at 2015-10-26 16:04:00 (activity since 2015-10-26 16:03:55) From Table GATE01.TCUSTMER to GATE02.TCUSTMER: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0 From Table GATE01.TCUSTORD to GATE02.TCUSTORD: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0 CACHE OBJECT MANAGER statistics CACHE MANAGER VM USAGE vm current = 0 vm anon queues = 0 vm anon in use = 0 vm file = 0 vm used max = 0 ==> CACHE BALANCED CACHE CONFIGURATION cache size = 2G cache force paging = 3.41G buffer min = 64K buffer highwater = 8M pageout eligible size = 8M ================================================================================ RUNTIME STATS FOR SUPERPOOL CACHE Transaction Stats trans active = 0 max concurrent = 0 non-zero total = 0 trans total = 0 CACHE File Caching disk current = 0 disk total = 0 disk caching = 0 file cached = 0 file retrieves = 0 CACHE MANAGEMENT buffer links = 0 anon gets = 0 forced unmaps = 0 cnnbl try = 0 cached out = 0 force out = 0 Allocation Request Distribution < 128B: 0 128B: 0 0 | 512B: 0 0 2K: 0 0 | 8K: 0 0 32K: 0 0 | 128K: 0 0 512K: 0 0 | 2M: 0 0 8M: 0 0 | 32M: 0 0 128M: 0 0 | 512M: 0 0 2G: 0 0 | 8G: 0 Cached Transaction Size Distribution 0: 0 < 4K: 0 4K: 0 0 | 16K: 0 0 64K: 0 0 | 256K: 0 0 1M: 0 0 | 4M: 0 0 16M: 0 0 | 64M: 0 0 256M: 0 0 | 1G: 0 0 4G: 0 0 | 16G: 0 0 64G: 0 0 | 256G: 0 0 1T: 0 0 | 4T: 0 0 16T: 0 0 | 64T: 0 0 256T: 0 0 |1024T: 0 0 ================================================================================ CUMULATIVE STATS FOR SUPERPOOL CACHE Transaction Stats trans active = 0 max concurrent = 0 non-zero total = 0 trans total = 0 CACHE File Caching disk current = 0 disk total = 0 disk caching = 0 file cached = 0 file retrieves = 0 CACHE MANAGEMENT buffer links = 0 anon gets = 0 forced unmaps = 0 cnnbl try = 0 cached out = 0 force out = 0 Allocation Request Distribution < 128B: 0 128B: 0 0 | 512B: 0 0 2K: 0 0 | 8K: 0 0 32K: 0 0 | 128K: 0 0 512K: 0 0 | 2M: 0 0 8M: 0 0 | 32M: 0 0 128M: 0 0 | 512M: 0 0 2G: 0 0 | 8G: 0 Cached Transaction Size Distribution 0: 0 < 4K: 0 4K: 0 0 | 16K: 0 0 64K: 0 0 | 256K: 0 0 1M: 0 0 | 4M: 0 0 16M: 0 0 | 64M: 0 0 256M: 0 0 | 1G: 0 0 4G: 0 0 | 16G: 0 0 64G: 0 0 | 256G: 0 0 1T: 0 0 | 4T: 0 0 16T: 0 0 | 64T: 0 0 256T: 0 0 |1024T: 0 0 QUEUE Statistics: num queues = 15 default index = 0 cur len = 0 max len = 0 q vm current = 0 vm max = 0 q hits = 0 q misses = 0 queue size q hits curlen maxlen cannibalized 0 64K 0 0 0 0 1 128K 0 0 0 0 2 256K 0 0 0 0 3 512K 0 0 0 0 4 1M 0 0 0 0 5 2M 0 0 0 0 6 4M 0 0 0 0 7 8M 0 0 0 0 8 16M 0 0 0 0 9 32M 0 0 0 0 10 64M 0 0 0 0 11 128M 0 0 0 0 12 256M 0 0 0 0 13 512M 0 0 0 0 14 1G 0 0 0 0 ================================================================================ RUNTIME STATS FOR CACHE POOL #0 POOL INFO group: rora01 id: p10060_BLOB trans active = 0 trans concurrent (max) = 0 trans total = 0 (0 ) flag = 0x00000030 last error = (0=<none>) Allocation Request Distribution < 128B: 0 128B: 0 0 | 512B: 0 0 2K: 0 0 | 8K: 0 0 32K: 0 0 | 128K: 0 0 512K: 0 0 | 2M: 0 0 8M: 0 0 | 32M: 0 0 128M: 0 0 | 512M: 0 0 2G: 0 0 | 8G: 0 ================================================================================ CUMULATIVE STATS FOR CACHE POOL #0 POOL INFO group: rora01 id: p10060_BLOB trans active = 0 trans concurrent (max) = 0 trans total = 0 (0 ) flag = 0x00000030 last error = (0=<none>) Allocation Request Distribution < 128B: 0 128B: 0 0 | 512B: 0 0 2K: 0 0 | 8K: 0 0 32K: 0 0 | 128K: 0 0 512K: 0 0 | 2M: 0 0 8M: 0 0 | 32M: 0 0 128M: 0 0 | 512M: 0 0 2G: 0 0 | 8G: 0 QUEUE Statistics: num queues = 15 default index = 0 cur len = 0 max len = 0 q vm current = 0 vm max = 0 q hits = 0 q misses = 0 queue size q hits curlen maxlen cannibalized 0 64K 0 0 0 0 1 128K 0 0 0 0 2 256K 0 0 0 0 3 512K 0 0 0 0 4 1M 0 0 0 0 5 2M 0 0 0 0 6 4M 0 0 0 0 7 8M 0 0 0 0 8 16M 0 0 0 0 9 32M 0 0 0 0 10 64M 0 0 0 0 11 128M 0 0 0 0 12 256M 0 0 0 0 13 512M 0 0 0 0 14 1G 0 0 0 0 ================================================================================ RUNTIME STATS FOR CACHE POOL #0 POOL INFO group: rora01 id: p10060_BLOB trans active = 0 trans concurrent (max) = 0 trans total = 0 (0 ) flag = 0x00000030 last error = (0=<none>) Allocation Request Distribution < 128B: 0 128B: 0 0 | 512B: 0 0 2K: 0 0 | 8K: 0 0 32K: 0 0 | 128K: 0 0 512K: 0 0 | 2M: 0 0 8M: 0 0 | 32M: 0 0 128M: 0 0 | 512M: 0 0 2G: 0 0 | 8G: 0 ================================================================================ CUMULATIVE STATS FOR CACHE POOL #0 POOL INFO group: rora01 id: p10060_BLOB trans active = 0 trans concurrent (max) = 0 trans total = 0 (0 ) flag = 0x00000030 last error = (0=<none>) Allocation Request Distribution < 128B: 0 128B: 0 0 | 512B: 0 0 2K: 0 0 | 8K: 0 0 32K: 0 0 | 128K: 0 0 512K: 0 0 | 2M: 0 0 8M: 0 0 | 32M: 0 0 128M: 0 0 | 512M: 0 0 2G: 0 0 | 8G: 0
目标库上查看数据是否同步过来：
SQL> select * from tcustmer; CUST_COD NAME -------- ------------------------------------------------------------ CITY STAT ---------------------------------------- ---- WILL BG SOFTWARE CO. SEATTLE WA JANE ROCKY FLYER INC. DENVER CO SQL> select * from tcustord 2 ; CUST_COD ORDER_DATE PRODUCT_CODE ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT -------- ------------ ---------------- ---------- ------------- -------------- TRANSACTION_ID -------------- WILL 30-SEP-94 CAR 144 17520 3 100 JANE 11-NOV-95 PLANE 256 133300 1 100
可以看到同步过来了。
4.源数据库的修改实时同步到目标数据库
4.1、在源数据上配置 ex tract 捕获进程 EXT01
--捕获进程 EXT01 配置参数文件
> E D I T PARAMS EXT01 EXTRACT EXT01 USERID gate01, password gate01 EXTTRAIL ./dirdat/wm ‐‐DDL TABLE gate01.*;
--添加捕获进程 EXT01
>ADD EXTRACT EXT01, TRANLOG, BEGIN NOW (direct load 方式： ADD EXTRACT EXT01, SOURCEISTABLE)
--添加队列文件，并将该队列文件指定给相应的捕获进程
>ADD EXTTRAIL ./dirdat/wm, EXTRACT EXT01, MEGABYTES 5 GGSCI (source) 10> INFO EXTRACT EXT01, DETAIL EXTRACT EXT01 Initialized 2015-10-26 16:13 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:17 ago) Log Read Checkpoint Oracle Redo Logs 2015-10-26 16:13:22 Seqno 0, RBA 0 SCN 0.0 (0) Target Extract Trails: Remote Trail Name Seqno RBA Max MB ./dirdat/wm 0 0 5 Extract Source Begin End Not Available * Initialized * 2015-10-26 16:13 Current directory /u01/ogg Report file /u01/ogg/dirrpt/EXT01.rpt (does not yet exist) Parameter file /u01/ogg/dirprm/ext01.prm Checkpoint file /u01/ogg/dirchk/EXT01.cpe Process file /u01/ogg/dirpcs/EXT01.pce Stdout file /u01/ogg/dirout/EXT01.out Error log /u01/ogg/ggserr.log
5.把源库的DDL操作同步到目标库
--在源数据库端关闭数据库的回收站
>show parameter recycle >alter system set recyclebin=off; SQL> show parameter recycle NAME TYPE VALUE ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string on SQL> alter system set recyclebin=off; System altered. SQL> show parameter recycle NAME TYPE VALUE ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string OFF
2、编辑源数据库参数文件 GLOBALS
> E D I T PARAMS ./GLOBALS GGSCHEMA gate01 ---gate01 支持 DDL 同步的用户
注意：要退出 GGSCI 命令行，再进入 GGSCI 命令行，使得全局文件生效。
3、在源数据库上创建 gate01 所需要的表
>sqlplus "/ as sysdba" >GRANT EXECUTE ON UTL_FILE TO gate01; SQL> conn / as sysdba Connected. SQL> GRANT EXECUTE ON UTL_FILE TO gate01; Grant succeeded. >@marker_setup 选择支持 DDL 同步的用户 gate01 SQL> @marker_setup Marker setup 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 name:gate01 Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GATE01 MARKER TABLE ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ OK MARKER SEQUENCE ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ OK Script complete. SQL> >@ddl_setup
选择支持 DDL 同步的用户 gate01，选择 INITIALSETUP，然后选择 YES。
(注意安装的类型有两种： INITIALSETUP、 NORMAL。当安装 DDL 复制操作或重新安装 DDL 复制操作选 INITIALSETUP，当更新 DDL 复制操作选 NORMAL)
(如果之前 dd l 安装包安装在其它用户下， 而现在又想安装到与之前不同的用户，
需要使用 @ ddl_remove.sql 删除安装在其它用户上的 DDL 包， 再运行 @dd l _setup 安装包)
SQL> @ddl_setup GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... Checking user sessions... There are 2 user sessions currently open (first 3 are shown): P ROGR A M OS _ US E R USERNAME ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ SID S E R # PID ‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ex tra c t@ sourc e (TNS oracle GATE01 145 34 6326 extract@source (TNS oracle GATE01 158 47 6327 IMPORTANT: Oracle sessions that used or may use DDL must be disconnected. If you continue, some of these sessions may cause DDL to fail with ORA‐6508. To proceed, enter yes. To stop installation, enter no. Enter yes or no:yes You will be prompted for the name of a schema for the 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 GoldenGate schema name:gate01 You will be prompted for the mode of installation. To install or reinstall DDL replication, enter INITIALSETUP To upgrade DDL replication, enter NORMAL Enter mode of installation:INITIALSETUP Working, please wait ... Spooling to file ddl_setup_spool.txt Using GATE01 as a GoldenGate schema name, INITIALSETUP as a mode of installation. Working, please wait ... RECYCLEBIN must be empty. This installation will purge RECYCLEBIN for all users. To proceed, enter yes. To stop installation, enter no. E nte r yes or no:yes DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GATE01 DDLORA_GETTABLESPACESIZE STATUS: Line/pos ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ Error ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ No errors No errors 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 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 ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ 0 LOCATION OF DDL TRACE FILE ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ /oracle/admin/hrss2/udump/ggs_ddl_trace.log Analyzing installation status... STATUS OF DDL REPLICATION ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ SUCCESSFUL installation of DDL Replication software components Script complete. SQL> SQL> @role_setup 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 name:gate01 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 gate01 ; SQL> GRANT GGS_GGSUSER_ROLE TO gate01 ; Grant succeeded. >@ddl_enable SQL> @ddl_enable Trigger altered.
检查支持 DDL 同步的用户 gate01 的表是否安装好
>conn gate01/gate01 >SELECT TABLE_NAME FROM USER_TABLES; SQL> SELECT TABLE_NAME FROM USER_TABLES; TABLE_NAME ------------------------------ TCUSTMER TCUSTORD GGS_MARKER GGS_SETUP GGS_DDL_HIST_ALT GGS_DDL_HIST GGS_DDL_COLUMNS GGS_DDL_LOG_GROUPS GGS_DDL_PARTITIONS GGS_DDL_PRIMARY_KEYS GGS_DDL_OBJECTS TABLE_NAME ------------------------------ GGS_TEMP_UK GGS_TEMP_COLS 13 rows selected. SQL>
4、更新捕获进程 EXT01
--捕获进程 EXT01 配置参数文件
> E D I T PARAMS EXT01 EXTRACT EXT01 USERID gate01, password "gate01" EXTTRAIL ./dirdat/wm
--需要显式声明来打开 DDL 复制
DDL TABLE gate01.*;
‐‐‐重新启动 EXT01
>STOP EXT01 >START EXT01
5、验证源数据库是否可以同步表结构到目标数据库中
在源数据上
>conn gate01/gate01 SQL> alter table tcustmer add AAA varchar(10); Table altered. SQL> commit ; complete.
在目标数据上
SQL>conn gate02/gate02 SQL> desc tcustmer; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- CUST_CODE NOT NULL VARCHAR2(4) NAME VARCHAR2(30) CITY VARCHAR2(20) STATE CHAR(2) AAA VARCHAR2(10)
版权声明：本文为博主原创文章，未经博主允许不得转载。
- 上一篇：批量插入,更新,删除数据
- 下一篇：OGG配合遇到的一些问题
