接着昨天的實驗，這裡用ogguser作為管理使用者，用stat作為同步資料使用者。
1. 源庫和目标庫ogguser使用者都賦予dba角色和執行utl_file的權限：
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 16 16:15:18 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant dba to ogguser;
Grant succeeded.
SQL> grant execute on utl_file to ogguser;
Grant succeeded.
2. 編輯源庫全局參數檔案
[[email protected] ogg11]$ cd $ORACLE_BASE/ogg11
[[email protected] ogg11]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 11g on Oct 4 2011 23:49:46
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (dd1) 1> edit param ./GLOBALS
ggschema ogguser
3. 啟用DDL支援設定（隻需在源庫設定）
[[email protected] ogg11]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 16 16:21:35 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
執行腳本marker_setup.sql：
SQL> @marker_setup.sql
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:ogguser ##輸入ogguser
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGGUSER
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
關閉資源回收筒：
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off;
alter system set recyclebin=off
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SQL> alter system set recyclebin=off scope=spfile; ##如果是10g，需要重新開機資料庫，這裡是11g，無需重新開機
System altered.
資料庫開始強制日志：
SQL> alter database force logging;
Database altered.
确認源庫已經處于歸檔模式，并啟用附加日志和強制日志：
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES
執行腳本ddl_setup.sql：
SQL> @ddl_setup.sql
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 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:ogguser ##輸入ogguser
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
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
Using OGGUSER as a GoldenGate schema name, INITIALSETUP as a mode of installation.
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 OGGUSER
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
------------------------------------------------------------------------------------------------------------------------
DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/data/oracle/diag/rdbms/test1/test1/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
執行腳本role_setup.sql：
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 name:ogguser
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.
根據提示對ogguser賦予ggs_ggsuser_role角色權限：
SQL> grant ggs_ggsuser_role to ogguser;
Grant succeeded.
執行腳本ddl_enable.sql：
SQL> @ddl_enable.sql
Trigger altered
執行dbmspool包将在資料庫中建立DBMS_SHARED_POOL包，之後ddl_pin包需要用到：
SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
執行ddl_pin.sql通過dbms_shared_pool.keep存儲過程将DDLReplication相關對象keep在共享池中，以保證這些對象不要RELOAD，提升性能:
SQL> @ddl_pin.sql ogguser
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
4. 清除上一次實驗環境
源庫：
GGSCI (dd1) 38> delete extract einikk
GGSCI (dd1) 38> delete extract eorakk
2013-04-16 17:30:46 WARNING OGG-01753 Cannot unregister EXTRACT EORAKK from database because no database login was provided. You can manually unregister this group later with the UNREGISTER EXTRACT <GROUP NAME> LOGRETENTION command. Issue DBLOGIN first.
Deleted EXTRACT EORAKK.
GGSCI (dd1) 2> stop manager
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
目标庫：
GGSCI (kf2.calvin) 10> delete replicat rinikk
Deleted REPLICAT RINIKK.
GGSCI (kf2.calvin) 17> dblogin userid system ,password calvin
Successfully logged into database.
GGSCI (kf2.calvin) 18> delete CHECKPOINTTABLE SYSTEM.GGCHKPTABLE
This checkpoint table may be required for other installations. Are you sure you want to delete this checkpoint table? y
Successfully deleted checkpoint table SYSTEM.GGCHKPTABLE.
GGSCI (kf2.calvin) 19> delete replicat rorakk
Deleted REPLICAT RORAKK.
GGSCI (kf2.calvin) 1> stop manager
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.、
5. 源庫和目标庫建立測試使用者，并授予權限
SQL> create user stat identified by stat default tablespace testdata quota unlimited on testdata;
User created.
SQL> grant connect,resource,dba to stat;
Grant succeeded.
6. 對源庫和目标庫進行配置
源庫和目标庫配置管理程序：
GGSCI (dd1) 3> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (dd1) 5> view params mgr
-- this is configuration of this manager process
PORT 7809
GGSCI (dd1) 6> start manager
Manager started.
源庫設定：
GGSCI (dd1) 2> dblogin userid [email protected],password ogguser
Successfully logged into database.
GGSCI (dd1) 3> add extract test1,tranlog,begin now ##添加extract程序
EXTRACT added.
GGSCI (dd1) 4> add exttrail /data/oracle/ogg11/dirdat/lt,extract test1 ##添加extrail
EXTTRAIL added.
GGSCI (dd1) 5> edit params test1 ##編輯參數
extract test1
userid [email protected], password ogguser
rmthost 192.168.130.171, mgrport 7809
rmttrail /data/oracle/ogg11/dirdat/lt
ddl include mapped objname stat.*;
table stat.*;
GGSCI (dd1) 6> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED TEST1 00:00:00 00:03:56
目标庫設定：
GGSCI (kf2.calvin) 5> edit params ./GLOBALS ##編輯全局參數檔案
GGSCHEMA ogguser
CHECKPOINTTABLE ogguser.checkpoint
GGSCI (kf2.calvin) 1> dblogin userid [email protected],password ogguser
Successfully logged into database.
GGSCI (kf2.calvin) 3> add checkpointtable ogguser.checkpoint ##添加checkpoint表
Successfully created checkpoint table OGGUSER.CHECKPOINT.
GGSCI (kf2.calvin) 4> add replicat test2,exttrail /data/oracle/ogg11/dirdat/lt,checkpointtable ogguser.checkpoint ##添加replicat程序
REPLICAT added.
GGSCI (kf2.calvin) 5> edit params test2 ##設定參數
replicat test2
ASSUMETARGETDEFS
userid [email protected],password ogguser
discardfile /data/oracle/ogg11/dirdat/test2_discard.txt,append, megabytes 10
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map stat.*, target stat.*;
源庫開啟extract程序，并檢查info和report是否正常：
GGSCI (dd1) 1> start extract test1
Sending START request to MANAGER ...
EXTRACT TEST1 starting
GGSCI (dd1) 2> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING TEST1 00:00:00 15:44:19
GGSCI (dd1) 4> view report test1
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 11g on Oct 7 2011 05:37:17
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-04-17 13:13:45
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue May 10 15:42:40 EDT 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: dd1
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: 15908
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
extract test1
userid [email protected], password *******
rmthost 192.168.130.171, mgrport 7809
rmttrail /data/oracle/ogg11/dirdat/lt
ddl include mapped objname stat.*;
table stat.*;
Bounded Recovery Parameter:
BRINTERVAL = 4HOURS
BRDIR = /data/oracle/ogg11
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 8G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 16G
CACHESIZEMAX (strict force to disk): 13.99G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
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 environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "AL32UTF8"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.
2013-04-17 13:13:46 INFO OGG-01513 Positioning to Sequence 14, RBA 31137296.
2013-04-17 13:13:46 INFO OGG-01516 Positioned to Sequence 14, RBA 31137296, Apr 17, 2013 1:13:17 PM.
2013-04-17 13:13:51 INFO OGG-01226 Socket buffer size set to 27985 (flush size 27985).
2013-04-17 13:13:51 INFO OGG-01055 Recovery initialization completed for target file /data/oracle/ogg11/dirdat/lt000004, at RBA 1046.
2013-04-17 13:13:51 INFO OGG-01478 Output file /data/oracle/ogg11/dirdat/lt is using format RELEASE 10.4/11.1.
2013-04-17 13:13:51 INFO OGG-01026 Rolling over remote file /data/oracle/ogg11/dirdat/lt000005.
2013-04-17 13:13:51 INFO OGG-01053 Recovery completed for target file /data/oracle/ogg11/dirdat/lt000005, at RBA 986.
2013-04-17 13:13:51 INFO OGG-01057 Recovery completed for all targets.
***********************************************************************
** Run Time Messages **
***********************************************************************
2013-04-17 13:13:51 INFO OGG-01517 Position of first record processed Sequence 14, RBA 31137296, SCN 0.1216816, Apr 17, 2013 1:13:17 PM.
目标庫開啟replicat程序，并檢查info和report是否正常：
GGSCI (kf2.calvin) 14> start replicat test2
Sending START request to MANAGER ...
REPLICAT TEST2 starting
GGSCI (kf2.calvin) 15> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING TEST2 00:00:00 00:00:05
GGSCI (kf2.calvin) 16> view report test2
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 11g on Oct 7 2011 05:44:59
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2013-04-17 13:19:20
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue May 10 15:42:40 EDT 2011, Release 2.6.32-131.0.15.el6.x86_64
Node: kf2.calvin
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: 5858
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
replicat test2
ASSUMETARGETDEFS
userid [email protected],password *******
discardfile /data/oracle/ogg11/dirdat/test2_discard.txt,append, megabytes 10
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map stat.*, target stat.*;
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 512M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1G
CACHESIZEMAX (strict force to disk): 881M
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
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 environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "AL32UTF8"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file /data/oracle/ogg11/dirdat/lt000004 at 2013-04-17 13:19:20
Switching to next trail file /data/oracle/ogg11/dirdat/lt000005 at 2013-04-17 13:19:20 due to EOF, with current RBA 1046
Opened trail file /data/oracle/ogg11/dirdat/lt000005 at 2013-04-17 13:19:20
Processed extract process graceful restart record at seq 5, rba 986.
7. 同步測試
源庫登入stat使用者，建立表并插入資料：
SQL> conn stat/stat
Connected.
SQL> create table tp_test(act varchar2(10),dt date default sysdate);
Table created.
SQL> insert into tp_test(act) values('ins');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tp_test;
ACT DT
---------- ---------
ins 17-APR-13
目标庫登入stat使用者，檢視資料是否同步：
SQL> conn stat/stat
Connected.
SQL> select * from tp_test;
ACT DT
---------- ---------
ins 17-APR-13
![GOLDENGATE安裝配置與複制流搭建_DG端抽取支援DDL版1、配置場景3、源端資料庫與表配置修改4、配置goldengate支援DDL4、OGG源端配置5、目标端OGG配置6、資料初始化7、啟動目标端的replicat程序8、驗證OGG能正常同步資料[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)