1.源端关闭回收站 注明：在oracle11g 中， recyclebin参数的 System Modifiable为DEFERRED，意思是要修改系统级的话，就要加deferred参数，对当前已经连接的sesion没有影响，但新连接的session将受到影响。(可以查询视图selectname,isses_modifiable,issys_modifiable from v$parameter wherename='recyclebin';)
SQL> set lines 200 SQL> col name for a30 SQL> select name,isses_modifiable,issys_modifiable from v$parameter where name='recyclebin';
NAME ISSES ISSYS_MOD ------------------------------ ----- --------- recyclebin TRUE DEFERRED
SQL> alter system set recyclebin=off DEFERRED;
System altered.
SQL> select name,isses_modifiable,issys_modifiable from v$parameter where name='recyclebin';
NAME ISSES ISSYS_MOD ------------------------------ ----- --------- recyclebin TRUE DEFERRED
2.源端配置./GLOBALS（修改全局配置文件添加ggschema参数） GGSCI (slient as ogg@test) 14> edit params ./GLOBALS ggschema ogg ~ "./GLOBALS" [New] 1L, 13C written
GGSCI (slient as ogg@test) 15>
GGSCI (slient as ogg@test) 16> view param ./GLOBALS
SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 30 21:52: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
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:ogg
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
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 name:ogg
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
LOCATION OF DDL TRACE FILE ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/diag/rdbms/test/test/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脚本 SQL> !pwd /opt/ogg/ogg_home
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:ogg SP2-0606: Cannot create SPOOL file "role_setup_spool.txt" SP2-0606: Cannot create STORE 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
where is the user assigned to the GoldenGate processes. SQL>
--根据上述提示执行授权： SQL> GRANT GGS_GGSUSER_ROLE TO ogg;
Grant succeeded.
SQL>
--执行ddl_enable.sql 脚本： SQL> @ddl_enable.sql
Trigger altered.
SQL>
--执行dbmspool.sql SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
--执行ddl_pin.sql SQL> @ddl_pin.sql Enter value for 1: ogg
PL/SQL procedure successfully completed.
Enter value for 1: ogg
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL>
4.源库修改extract进程的params文件，添加"ddl include all"参数，重启extract进程 GGSCI (slient as ogg@test) 19> edit param exta EXTRACT exta setenv (ORACLE_SID=test) setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) userid ogg,password ogg ddl include all exttrail /opt/ogg/ogg_home/dirdat/r1 dynamicresolution TABLE scott.*; ~ "dirprm/exta.prm" 8L, 203C written
GGSCI (slient as ogg@test) 20>
--重启extract进程： GGSCI (slient as ogg@test) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
GGSCI (one as ogg@onemo) 71> GGSCI (one as ogg@onemo) 71> info REPLICAT REP_DEMO
REPLICAT REP_DEMO Last Started 2017-10-27 08:45 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:05 ago) Process ID 11591 Log Read Checkpoint File /u01/app/oracle/ogg/dirdat/ra000000000 2017-10-30 21:11:03.153747 RBA 5075