1.开始配置OGG支持DDL复制(在source端操作) 1.1 赋予权限 SQL> conn /as sysdba 已连接。 SQL> grant execute on utl_file to ggs; 1.2 修改全局配置文件添加ggschema GGSCI (WIN-GM5PVS1CILH) 18> edit param ./GLOBALS GGSCI (WIN-GM5PVS1CILH) 19> view param ./GLOBALS ggschema ggs GGSCI (WIN-GM5PVS1CILH) 20> 1.3 运行相关sql脚本 及修改参数 C:UsersAdministrator>cd C:ora11gproductogg_src C:ora11gproductogg_src>sqlplus /nolog SQL> conn /as sysdba 已连接。 #第一个sql脚本 SQL> @marker_setup.sql 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:ggs Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete. SQL> #禁用 recyclebin 。官网的解释如下：If the recyclebin is enabled, the Oracle GoldenGate DDL trigger session receives implicitrecycle bin DDL operations that cause the trigger to fail. SQL> show parameter recyclebin; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on SQL> alter system set recyclebin=off scope=spfile; 系统已更改。 SQL> show parameter recyclebin; #需重启实例后改参数才生效 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string off SQL> #第二个sql脚本 注意：此脚本要求ggs必须是独立表空间且是自动扩展的 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 databaseobjects. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Ora cle 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:ggs 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. WARNING: Tablespace TBS_GGS does not have AUTOEXTEND enabled. Using GGS 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 GGS CLEAR_TRACE STATUS: Line/pos Error -------------------- ----------------------------------------------------------- ------ No errors No errors ......... SUCCESSFUL installation of DDL Replication software components Script complete. SQL> #第三个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 scri pt 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:ggs 已写入 file role_setup_set.txt PL/SQL 过程已成功完成。 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 ggs; 授权成功。 SQL> #其他sql脚本 SQL> @ddl_enable.sql SQL> @?/rdbms/admin/dbmspool.sql SQL> @ddl_pin.sql ggs SQL> 2.source端修改extract进程的params文件，添加"ddl include all"参数，重启extract进程 2.1 查看添加后的参数 GGSCI (WIN-GM5PVS1CILH) 40> view param eora_t1 extract eora_t1 setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) ddl include all userid ggs@orcl_w,password ggs exttrail dirdat/sp table scott.*; 2.2 重启 EXTRACT 进程 GGSCI (WIN-GM5PVS1CILH) 41> stop extract eora_t1 Sending STOP request to EXTRACT EORA_T1 ... Request processed. GGSCI (WIN-GM5PVS1CILH) 42> start extract eora_t1 Sending START request to MANAGER ('GGSMGR') ... EXTRACT EORA_T1 starting 2.3 查看进程状态 GGSCI (WIN-GM5PVS1CILH) 43> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORA_T1 00:00:00 00:00:01 EXTRACT RUNNING PORA_T1 00:00:00 00:00:06 GGSCI (WIN-GM5PVS1CILH) 44> info extract eora_t1 EXTRACT EORA_T1 Last Started 2016-12-14 11:07 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:06 ago) Process ID 2804 Log Read Checkpoint Oracle Redo Logs 2016-12-14 11:07:41 Seqno 7, RBA 18905600 SCN 0.1013586 (1013586) GGSCI (WIN-GM5PVS1CILH) 45> 3. target端修改replicat进程的params文件，添加"ddl include all"和"ddlerror default ignore retryop maxretries 3 retrydelay 5" 参数，重启replicat进程 3.1 查看添加后的参数 GGSCI (Oracle02 as ggt@orcl) 11> edit param rora_t1 GGSCI (Oracle02 as ggt@orcl) 12> view param rora_t1 replicat rora_t1 setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) ddl include all ddlerror default ignore retryop maxretries 3 retrydelay 5 userid ggt,password ggt handlecollisions assumetargetdefs discardfile dirrpt/rora_t1.dsc,purge map scott.* ,target scott.*; 3.2 重启 REPLICAT 进程 GGSCI (Oracle02 as ggt@orcl) 13> stop replicat rora_t1 REPLICAT RORA_T1 is already stopped. GGSCI (Oracle02 as ggt@orcl) 14> start replicat rora_t1 Sending START request to MANAGER ... REPLICAT RORA_T1 starting 3.3 查看进程状态 GGSCI (Oracle02 as ggt@orcl) 15> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RORA_T1 00:00:00 00:00:02 GGSCI (Oracle02 as ggt@orcl) 16> info replicat rora_t1 REPLICAT RORA_T1 Last Started 2016-12-14 11:13 Status RUNNING Checkpoint Lag 00:16:48 (updated 00:00:00 ago) Process ID 27403 Log Read Checkpoint File dirdat/rp000000006 2016-12-14 10:57:03.302367 RBA 4347 GGSCI (Oracle02 as ggt@orcl) 17> 4.测试 SQL> conn scott/scott 已连接。 SQL> select table_name from user_tables; TABLE_NAME ------------------------------ T1 SALGRADE BONUS EMP DEPT SQL> create table t2 as select object_id,object_name from dba_objects; 表已创建。 SQL> #在目标主机验证 SQL> conn scott/scott@orcl_L 已连接。 SQL> select table_name from user_tables; TABLE_NAME ------------------------------ T2 T1 SALGRADE BONUS EMP DEPT 已选择6行。 SQL>