learn by doing,less theory,more results.
install configure:
OGG单向DDL支持:
source 源端
1、禁用Recycle Bin:
如果数据库是10g,需要关闭recyclebin并重启;或者手工purge recyclebin。
oracle 11g没做要求。
2、安装ddl支持配置
2.1、创建同于记录DDL信息表
--脚本1:
SQL> @marker_setup.sql;
--输入我们之前创建的用户名:
2.2、创建相关的存储过程和触发器
--脚本2:
SQL> @ddl_setup.sql;
--提示输入GG的用户:
--这里让我们选择安装模式: install 和 reinstall 选择INITIALSETUP
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 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
---------------------------------------- -----------------------------------------------------------------
1453/9 PL/SQL: SQL Statement ignored
1455/23 PL/SQL: ORA-00942: table or view does not exist
1464/9 PL/SQL: SQL Statement ignored
1466/23 PL/SQL: ORA-00942: table or view does not exist
1478/9 PL/SQL: SQL Statement ignored
1480/23 PL/SQL: ORA-00942: table or view does not exist
1485/9 PL/SQL: SQL Statement ignored
1487/23 PL/SQL: ORA-00942: table or view does not exist
1492/9 PL/SQL: SQL Statement ignored
1494/23 PL/SQL: ORA-00942: table or view does not exist
1499/9 PL/SQL: SQL Statement ignored
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
1501/23 PL/SQL: ORA-00942: table or view does not exist
1581/4 PL/SQL: SQL Statement ignored
1582/18 PL/SQL: ORA-00942: table or view does not exist
1584/4 PL/SQL: SQL Statement ignored
1585/18 PL/SQL: ORA-00942: table or view does not exist
1600/25 PL/SQL: ORA-00942: table or view does not exist
1600/4 PL/SQL: SQL Statement ignored
1602/25 PL/SQL: ORA-00942: table or view does not exist
1602/4 PL/SQL: SQL Statement ignored
DDL IGNORE TABLE
-----------------------------------
FAILED: Table does not exist
DDL IGNORE LOG TABLE
-----------------------------------
FAILED: Table does not exist
DDLAUX PACKAGE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/21 PLS-00304: cannot compile body of 'DDLAUX' without its
specification
1/21 PLS-00905: object OGG.DDLAUX is invalid
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
-----------------------------------
FAILED: Table does not exist
DDL HISTORY TABLE(1)
-----------------------------------
FAILED: Table does not exist
DDL DUMP TABLES
-----------------------------------
FAILED: Table does not exist
DDL DUMP COLUMNS
-----------------------------------
FAILED: Table does not exist
DDL DUMP LOG GROUPS
-----------------------------------
FAILED: Table does not exist
DDL DUMP PARTITIONS
-----------------------------------
FAILED: Table does not exist
DDL DUMP PRIMARY KEYS
-----------------------------------
FAILED: Table does not exist
DDL SEQUENCE
-----------------------------------
FAILED: Sequence does not exist
GGS_TEMP_COLS
-----------------------------------
FAILED: Table does not exist
GGS_TEMP_UK
-----------------------------------
FAILED: Table does not exist
DDL TRIGGER CODE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
126/9 PL/SQL: SQL Statement ignored
128/23 PL/SQL: ORA-00942: table or view does not exist
133/21 PL/SQL: ORA-02289: sequence does not exist
133/5 PL/SQL: SQL Statement ignored
657/14 PLS-00905: object OGG.DDLAUX is invalid
657/5 PL/SQL: Statement ignored
919/25 PL/SQL: ORA-00942: table or view does not exist
919/4 PL/SQL: SQL Statement ignored
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
FROM "OGG" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "OGG" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
FROM "OGG" ."GGS_SETUP"
*
ERROR at line 2:
ORA-00942: table or view does not exist
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/source/source/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
ERRORS detected in installation of DDL Replication software components (6)
报了一堆错误,看不明白,暂时不处理。
2.3、创建DDL同步角色
--脚本3:
SQL> @role_setup.sql;
2.4、启用DDL触发器
--脚本4:
SQL> @ddl_enable.sql;
执行dml 语句
SQL> drop table t2;
drop table t2
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed
re-validation
查询相关资料,在11.2.0.4.0 需要设置:
1、ENABLE_GOLDENGATE_REPLICATION=true
2、
SQL > @ddl_disable;
SQL > GRANT CREATE TABLE,CREATE SEQUENCE TO OGG;
SQL > grant execute on utl_file to ogg;
SQL > @ddl_setup.sql;
SQL > @ddl_enable.sql;
2.5 、优化DDL 触发器性能
SQL>@ddl_pin ogg
3、 修改source extract 参数
GGSCI (ogg1) 6> edit params ext1
extract ext1
dynamicresolution
setenv (NLS_.AL32UTF8)
userid ogg,password ogg
exttrail /u01/gg/dirdat/ex
ddl include all
ddloptions addtrandata, report
table bobo.*;
ddl include all:启劢ddl支持,并且包含所有的ddl操作
target 目标端
1、编辑target replicat 参数:
GGSCI (ogg2) 6> edit params rpt1
replicat rpt1
userid ogg,password oracle123
assumetargetdefs
reperror default,discard
discardfile /u01/gg/discards.dsc,append,megabytes 50
dynamicresolution
ddloptions report
ddlerror default ignore retryop maxretries 3 retrydelay 5
ddlerror default discard
ddlerror default ignore retryop
map bobo.*, target bobo.*;
注意:
1、如果在source端的过程或者函数有调用到别的用户资源,确保target有同样的权限。
2、在同步前,确认source 端业务用户已存在的表,因为atrget不会同步已存在的表数据。
3、如果在source执行create table语句,而没有创建主键,在ggser.log会有警告,创建主键主要是为了双向同步,如果单向同步可以忽略。
测试:
此种设置支持,create ,delete,drop ,alter table。
SQL> alter table sys_menu add remark varchar2(10);
Table altered.
查看源端source 日志
[root@localhost ~]# tail -f /u01/gg/ggser.log
2017-11-02 14:49:42 INFO OGG-00487 Oracle GoldenGate Capture for Oracle, ext1.prm: DDL operation included [include all], optype [ALTER], objtype [TABLE], objowner [BOBO], objname [SYS_MENU].
2017-11-02 14:49:42 INFO OGG-00497 Oracle GoldenGate Capture for Oracle, ext1.prm: Writing DDL operation to extract trail file.
查看目标端targe日志:
[root@localhost ~]# tail -f /u01/gg/ggser.log
2017-11-02 13:06:51 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rpt1.prm: Restoring current schema for DDL operation to [ogg].
2017-11-02 14:50:05 INFO OGG-00482 Oracle GoldenGate Delivery for Oracle, rpt1.prm: DDL found, operation [alter table sys_menu add remark varchar2(10) (size 45)].
2017-11-02 14:50:05 INFO OGG-00489 Oracle GoldenGate Delivery for Oracle, rpt1.prm: DDL is of mapped scope, after mapping new operation [alter table bobo."SYS_MENU" add remark varchar2(10) (size 52)].
2017-11-02 14:50:05 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rpt1.prm: Setting current schema for DDL operation to [bobo].
2017-11-02 14:50:05 INFO OGG-00484 Oracle GoldenGate Delivery for Oracle, rpt1.prm: Executing DDL operation.
2017-11-02 14:50:06 INFO OGG-00483 Oracle GoldenGate Delivery for Oracle, rpt1.prm: DDL operation successful.
2017-11-02 14:50:06 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rpt1.prm: Restoring current schema for DDL operation to [ogg].
执行sql语句:
SQL> alter table sys_menu rename column remark to remarks;
Table altered.
查看源端source日志:
2017-11-02 14:59:56 INFO OGG-01487 Oracle GoldenGate Capture for Oracle, ext1.prm: DDL found, operation [alter table sys_menu rename column remark to remarks (size 53)], start SCN [1395755], commit SCN [1395764] instance [source (1)], DDL seqno [67], marker seqno [559].
2017-11-02 14:59:56 INFO OGG-00487 Oracle GoldenGate Capture for Oracle, ext1.prm: DDL operation included [include all], optype [ALTER], objtype [TABLE], objowner [BOBO], objname [SYS_MENU].
2017-11-02 14:59:56 INFO OGG-00497 Oracle GoldenGate Capture for Oracle, ext1.prm: Writing DDL operation to extract trail file.
查看目标端target日志:
2017-11-02 15:00:18 INFO OGG-00482 Oracle GoldenGate Delivery for Oracle, rpt1.prm: DDL found, operation [alter table sys_menu rename column remark to remarks (size 53)].
2017-11-02 15:00:18 INFO OGG-00489 Oracle GoldenGate Delivery for Oracle, rpt1.prm: DDL is of mapped scope, after mapping new operation [alter table bobo."SYS_MENU" rename column remark to remarks (size 60)].
2017-11-02 15:00:18 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rpt1.prm: Setting current schema for DDL operation to [bobo].
2017-11-02 15:00:18 INFO OGG-00484 Oracle GoldenGate Delivery for Oracle, rpt1.prm: Executing DDL operation.
2017-11-02 15:00:18 INFO OGG-00483 Oracle GoldenGate Delivery for Oracle, rpt1.prm: DDL operation successful.
2017-11-02 15:00:18 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rpt1.prm: Restoring current schema for DDL operation to [ogg].