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:\Users\Administrator>cd C:\ora11g\product\ogg_src
C:\ora11g\product\ogg_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>
