goldengate（13）--配置DDL复制_goldengate对普通表支持所有的DDL（create、alter、drop、truncate）支持----复制普通表
回忆一下，goldengate对oracle 数据库对象的DDL支持与不支持如下：
goldengate支持下面对象所有的DDL（create,alter,drop,rename,truncate）操作，但是对象结构的大小上限为2M
clusters（簇表）
functions （函数）
indexes （索引）
packages （包）
procedure （存储过程）
tables （表）
tablespaces （表空间）
roles （角色）
sequences （序列）
synonyms （同义词）
triggers （触发器）
types （类型）
views （视图）
materialized views （物化视图）
users（用户）
2.Non-supported objects and operations for DDL
goldengate不支持下面对象所有的DDL操作
ANONYMOUS
AURORA
$JIS
$UTILITY
$AURORA
$ORB
$UNAUTHENTICATED
CTXSYS
DBSNMP
DMSYS
DSSYS
EXFSYS
MDSYS
ODM
ODM_MTR
OLAPSYS
ORDPLUGINS
ORDSYS
OSE$HTTP$ADMIN
OUTLN
PERFSTAT
PUBLIC
REPADMIN
SYS
SYSMAN
SYSTEM
TRACESVR
WKPROXY
WKSYS
WMSYS
XDB
Oracle recycle bin DDL也不予支持，配置DDL操作必须关闭oracle recycle bin
1.配置DDL（只涉及DDL的配置，其他的配置请看前面几章写的文档）
下面列出extract 抽取进程的配置（仅DML）：
EXTRACT s_ex_hr
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "dbking")
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs, PASSWORD ggs
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD grid
EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/hr/tt
TABLEEXCLUDE test.MV_123, test.mlog$_xy101
--SEQUENCE test.seqak;
TABLE test.*;
下面是增加DDL操作需要的配置参数，以及其他的配置过程
源端操作
1.1 授予UTL_FILE的执行权限给ggs
SQL> GRANT EXECUTE ON UTL_FILE TO ggs;
Grant succeeded.
1.2 禁止Oracle recycle bin
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 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
SQL> show parameter bin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
recyclebin string on
SQL> alter system set recyclebin=off DEFERRED scope=both;
System altered.
1.3 关闭数据库
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
1.4 打开数据库
SQL> startup
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1348244 bytes
Variable Size 637537644 bytes
Database Buffers 205520896 bytes
Redo Buffers 5124096 bytes
Database mounted.
Database opened.
1.5 执行DDL相应脚本
SQL> @marker_setup
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:ggs -----手动输入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> @ddl_setup
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:ggs ----手动输入ggs用户
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 ---手动输入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 GGS 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 GGS
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
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dbking/dbking/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script. complete.
SQL> @role_setup
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:ggs ----手动输入ggs用户
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 ---后面把这个授权跑一遍
where is the user assigned to the GoldenGate processes.
SQL>
SQL> GRANT GGS_GGSUSER_ROLE TO ggs;
Grant succeeded.
SQL> @ddl_enable.sql
Trigger altered.
触发器是创建在SYS用户下
SQL> @ddl_pin
Enter value for 1: ggs
PL/SQL procedure successfully completed.
Enter value for 1: ggs
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1.6 配置各ogg进程
extract抽取进程增加DDL配置（和之前的DML一起,换了测试用户gis_test）
EXTRACT s_ex_hr
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "dbking")
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs, PASSWORD ggs
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD grid
EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/hr/tt
--TABLEEXCLUDE test.MV_123, test.mlog$_xy101
SEQUENCE gis_test.*;
DDL INCLUDE MAPPED OBJNAME "gis_test.*"
TABLE gis_test.*;
extract pump进程配置：
EXTRACT pump_hr
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "dbking")
USERID ggs, PASSWORD ggs
RMTHOST 10.23.5.71, MGRPORT 7809
RMTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/hr/pp
PASSTHRU
--TABLEEXCLUDE test.MV_123, test.mlog$_xy101
SEQUENCE gis_test.*;
TABLE gis_test.*;
目标端操作：
其他操作都已经在之前的文档添加完成，这个只增加DDL部分：
replicat复制进程配置：
REPLICAT rep_hr
SETENV (ORACLE_HOME = "/u01/app/oracle/product/10.2.0/db_1")
SETENV (ORACLE_SID = "dbwdn")
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND
USERID ggs, PASSWORD ggs
DDL INCLUDE MAPPED
MAP gis_test.*, TARGET gis_test.*;
源端与目标端都已经配置完成，现在进程DDL复制普通表的测试
创建测试用户gis_test （源端与目标端同时创建）
create user gis_test identified by gis_test default tablespace users temporary tablespace temp;
源端DDL普通表
1. 测试goldengate支持create 普通表
create table gis_test.t1 (idno number primary key ,tkill varchar2(20),mdate date);
目的端查看
报错：
2013-03-18 22:30:33 ERROR OGG-00519 Fatal error executing DDL replication: error [Error code [1950], ORA-01950: no privileges on tablespace 'USER
S', SQL create table "GIS_TEST"."T1" (idno number primary key ,tkill varchar2(20),mdate date)
/* GOLDENGATE_DDL_REPLICATION */], no error handler present.
目标端执行
alter user gis_test quota unlimited on users;
这个时候，目的端已经看到这张普通表了.
SQL> desc gis_test.t1
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
IDNO NUMBER
TKILL VARCHAR2(20) Y
MDATE DATE Y
2. 测试goldengate支持alter普通表
2.1 测试表GIS_TEST.T1 add一个字段
alter table gis_test.t1 add king varchar2(10);
目标端也看到了：
SQL> desc gis_test.t1
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
IDNO NUMBER
TKILL VARCHAR2(20) Y
MDATE DATE Y
KING VARCHAR2(10) Y
2.2 测试表GIS_TEST.T1 modify 一个字段，加大king字段的bytes容量为20
alter table gis_test.t1 modify king varchar2(20);
目标端看到了改变：
SQL> desc gis_test.t1
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
IDNO NUMBER
TKILL VARCHAR2(20) Y
MDATE DATE Y
KING VARCHAR2(20) Y
2.3 测试表GIS_TEST.T1 rename 一个字段
alter table gis_test.t1 rename column king to kingking;
目标端查看：
SQL> desc gis_test.t1
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
IDNO NUMBER
TKILL VARCHAR2(20) Y
MDATE DATE Y
KINGKING VARCHAR2(20) Y
目标端发生了改变.
3. 测试goldengate支持truncate 普通表
extract 抽取进程添加GETTRUNCATES参数
EXTRACT s_ex_hr
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "dbking")
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs, PASSWORD ggs
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD grid
EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/hr/tt
--TABLEEXCLUDE test.MV_123, test.mlog$_xy101
SEQUENCE gis_test.*;
GETTRUNCATES
DDL INCLUDE MAPPED OBJNAME "gis_test.*"
重启抽取进程
pump投递进程不需要增加该参数
repicat复制进程添加GETTRUNCATES该参数
REPLICAT rep_hr
SETENV (ORACLE_HOME = "/u01/app/oracle/product/10.2.0/db_1")
SETENV (ORACLE_SID = "dbwdn")
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND
USERID ggs, PASSWORD ggs
GETTRUNCATES
DDL INCLUDE MAPPED
MAP gis_test.*, TARGET gis_test.*;
重启复制进程
源端插入一条数据：
insert into gis_test.t1 values (1,'king',sysdate,'xiangyang');
当然，目的端看数据了。
源端发出truncate table gis_test.t1;
查看目的端，数据没有了，数据被截断了。
3. 测试goldengate支持drop 普通表
源端发出drop gis_test.t1
目标端该表也被drop掉了。
另：
如果要删除DDL复制，执行下面脚本：
首先停止所有ogg进程（包括mgr），然后执行脚本：
[oracle@dbhouse ggs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 26 19:51:13 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @ddl_disable.sql
Trigger altered.
SQL> @ddl_remove.sql
DDL replication removal script.
WARNING: this script. removes all DDL replication objects and data.
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.
Enter GoldenGate schema name:ggs
Working, please wait ...
Spooling to file ddl_remove_spool.txt
Script. complete.
SQL> @marker_remove.sql
Marker removal script.
WARNING: this script. removes all marker objects and data.
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.
Enter GoldenGate schema name:ggs
PL/SQL procedure successfully completed.
Sequence dropped.
Table dropped.
Script. complete.
SQL>
DDL复制删除完毕.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21266384/viewspace-756481/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21266384/viewspace-756481/
4290
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
