配置GoldenGate同步DDL语句(1)

在配置Goldengate同步DDL语句前,要求在数据库内完成一系列的预安装先题步骤;其中包括建立合适的Goldengate用户,如:

create user maclean identified by maclean;
grant dba to maclean;

/* 之后maclean用户将用以配置extract和pump */

之后我们需要执行一系列同步DLL语句要用到的GoldenGate脚本,这些脚本存放在GG的安装目录下:

[maclean@rh2 gg]$ pwd
/home/maclean/gg
[maclean@rh2 gg]$ ls -l dd*.sql
-r--r--r-- 1 maclean oinstall   1059 2010-03-12 ddl_cleartrace.sql
-r--r--r-- 1 maclean oinstall   4189 2010-03-12 ddl_ddl2file.sql
-r--r--r-- 1 maclean oinstall    746 2010-03-12 ddl_disable.sql
-r--r--r-- 1 maclean oinstall    692 2010-03-12 ddl_enable.sql
-r--r--r-- 1 maclean oinstall    388 2010-03-12 ddl_nopurgeRecyclebin.sql
-r--r--r-- 1 maclean oinstall  12424 2010-05-21 ddl_ora10.sql
-r--r--r-- 1 maclean oinstall   3863 2010-05-21 ddl_ora10upCommon.sql
-r--r--r-- 1 maclean oinstall  11064 2010-03-12 ddl_ora11.sql
-r--r--r-- 1 maclean oinstall  12365 2010-03-12 ddl_ora9.sql
-r--r--r-- 1 maclean oinstall   1026 2010-03-12 ddl_pin.sql
-r--r--r-- 1 maclean oinstall   1227 2010-03-12 ddl_purgeRecyclebin.sql
-r--r--r-- 1 maclean oinstall   3686 2010-05-13 ddl_remove.sql
-r--r--r-- 1 maclean oinstall    425 2009-06-29 ddl_session1.sql
-r--r--r-- 1 maclean oinstall   1053 2009-06-23 ddl_session.sql
-r-xr-xr-x 1 maclean oinstall 228606 06-29 13:48 ddl_setup.sql
-r--r--r-- 1 maclean oinstall   8872 2010-03-12 ddl_status.sql
-r--r--r-- 1 maclean oinstall   2506 2010-03-12 ddl_staymetadata_off.sql
-r--r--r-- 1 maclean oinstall   2501 2010-03-12 ddl_staymetadata_on.sql
-r--r--r-- 1 maclean oinstall   2955 2010-03-12 ddl_tracelevel.sql
-r--r--r-- 1 maclean oinstall   2543 2010-03-12 ddl_trace_off.sql
-r--r--r-- 1 maclean oinstall   2862 2010-03-12 ddl_trace_on.sql

/* 执行goldengate ddl同步安装脚本要求以SYSDBA身份登录 */

[maclean@rh2 gg]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Dec 6 18:01:46 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set recyclebin=off;
System altered.

/* 同步DDL要求我们关闭10g中的回收站特性 */

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:maclean
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to MACLEAN
MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.

/* 以SYSDBA 登陆数据库并执行当前GG安装目录下的market_setup脚本,该脚本用以建立一个DDL标记表
   包括以下列:
   seqNo NUMBER NOT NULL, -- sequence number
   fragmentNo NUMBER NOT NULL, -- fragment number (message divided into fragments)
   optime CHAR(19) NOT NULL, -- time of operation
   TYPE VARCHAR2 (100) NOT NULL, -- type of marker
   SUBTYPE VARCHAR2 (100) NOT NULL, -- subtype of marker
   marker_text VARCHAR2 (4000) NOT NULL,
   -- fragment text (message divided into fragments numbered with fragmentNo)
*/

SQL> @ddl_setup

GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...
Checking user sessions...

Check complete.

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:maclean

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

Working, please wait ...
Spooling to file ddl_setup_spool.txt
Using MACLEAN as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait ...

RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to MACLEAN

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
--------------------------------------------------------------------------------
/s01/10gdb/admin/clinica/udump/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION
--------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

/* 以INITIALSETUP选项运行ddl_setup.sql 将在数据库中创建捕获DDL语句的Trigger等必要组件 */

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:maclean
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.

/* role_setup脚本用以建立GGS_GGSUSER_ROLE角色 */

我们需要将该GGS_GGSUSER_ROLE授予给extract group参数中定义的userid用户
SQL> grant GGS_GGSUSER_ROLE to maclean;
Grant succeeded.

SQL> @ddl_enable
Trigger altered.

/*ddl_enable.sql将正式enable ddl捕获触发器,即:ALTER TRIGGER sys .&ddl_trigger_name ENABLE; */

SQL> @?/rdbms/admin/dbmspool
Package created.
Grant succeeded.
View created.
Package body created.

/* 执行dbmspool包将在数据库中创建DBMS_SHARED_POOL包,之后需要用到*/

SQL> @ddl_pin
Enter value for 1: maclean
PL/SQL procedure successfully completed.
Enter value for 1: maclean
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

/* ddl_pin.sql通过dbms_shared_pool.keep存储过程将DDLReplication相关的对象keep在共享池中,
   以保证这些对象不要reload,提升性能
*/

以上脚本都运行完成后,DDL语句同步的先题条件就达成了。接着我们要来配置GG部分的extract和replicat:

[maclean@rh2 gg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.

GGSCI (rh2.oracle.com) 1> add extract load1,tranlog,begin now
EXTRACT added.

GGSCI (rh2.oracle.com) 2> add rmttrail /s01/rmt/ma,megabytes 100,extract load1
RMTTRAIL added.

GGSCI (rh2.oracle.com) 5> encrypt password maclean
No key specified, using default key...

Encrypted password:  AACAAAAAAAAAAAHANCUEFHPAVCYDNJVD

GGSCI (rh2.oracle.com) 16> start extract load1

Sending START request to MANAGER ...
EXTRACT LOAD1 starting

GGSCI (rh2.oracle.com) 19> view params load1

extract load1
userid maclean, password AACAAAAAAAAAAAHANCUEFHPAVCYDNJVD,encryptkey default
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTTRAIL /s01/rmt/ma
DDL INCLUDE MAPPED
Table sender.*;

/* 以上为源端的extract load1的配置,采用了DDL INCLUDE MAPPED的DDL同步方式 */

GGSCI (rh3.oracle.com) 59> add replicat rep1,exttrail /s01/rmt/ma,begin now,checkpointtable maclean.checkpoint
REPLICAT added.

GGSCI (rh3.oracle.com) 62> view params rep1

replicat rep1
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/rep1.log,append,megabytes 10
-- Support DDL here
DDL
map sender.* , target receiver.*;

GGSCI (rh3.oracle.com) 60> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (rh3.oracle.com) 63> info all          

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:07    

/* 以上为目标端的replicat rep1配置,DDL语句将由sender用户映射到receiver用户 */

接下来我们对具体的DDL同步进行测试:

/ * 在源端建立测试用表tddl */

SQL> conn sender/sender
Connected.
SQL> create table tddl (t1 int);
Table created.

REP1.rep报告文件出现以下信息:
Opened trail file /s01/rmt/ma000000 at 2010-12-07 03:50:19
2010-12-07 03:52:13  INFO    OGG-01407  Setting current schema for DDL operation to [RECEIVER].
2010-12-07 03:52:13  INFO    OGG-01408  Restoring current schema for DDL operation to [MACLEAN].

SQL> conn receiver/receiver;
Connected.

SQL> desc tddl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T1                                                 NUMBER(38)

 /* create table的DDL语句被正确同步了 */

/* 可以从gg管理者名下的ggs_marker表看到捕获到的建表语句
   似乎可以分为DDL正文和变量2段
*/

SQL> set linesize 65 pagesize 1400;
SQL> select marker_text
  2    from ggs_marker
  3   where seqno =
       (select seqno from ggs_marker where marker_text like '%tddl%');
  4
MARKER_TEXT
-----------------------------------------------------------------
,C1='create table tddl \(t1 int\) ',
,C5='16',,B2='',,G4='',,B3='SENDER',,B4='TDDL',,C12='',,C13='',,B
5='TABLE',,B6='CREATE',,B7='16',,B8='MACLEAN.GGS_DDL_HIST',,B9='S
ENDER',,C7='10.2.0.4.0',,C8='10.2.0.3',,C9='',,C10='1',,C11='clin
ica',,G3='NONUNIQUE',,C14='NO',,C17('1')='NLS_LANGUAGE',,C18('1')
='AMERICAN',,C17('2')='NLS_TERRITORY',,C18('2')='AMERICA',,C17('3
')='NLS_CURRENCY',,C18('3')='$',,C17('4')='NLS_ISO_CURRENCY',,C18
('4')='AMERICA',,C17('5')='NLS_NUMERIC_CHARACTERS',,C18('5')='.\,
',,C17('6')='NLS_CALENDAR',,C18('6')='GREGORIAN',,C17('7')='NLS_D
ATE_FORMAT',,C18('7')='DD-MON-RR',,C17('8')='NLS_DATE_LANGUAGE',,
C18('8')='AMERICAN',,C17('9')='NLS_SORT',,C18('9')='BINARY',,C17(
'10')='NLS_TIME_FORMAT',,C18('10')='HH.MI.SSXFF AM',,C17('11')='N
LS_TIMESTAMP_FORMAT',,C18('11')='DD-MON-RR HH.MI.SSXFF AM',,C17('
12')='NLS_TIME_TZ_FORMAT',,C18('12')='HH.MI.SSXFF AM TZR',,C17('1
3')='NLS_TIMESTAMP_TZ_FORMAT',,C18('13')='DD-MON-RR HH.MI.SSXFF A
M TZR',,C17('14')='NLS_DUAL_CURRENCY',,C18('14')='$',,C17('15')='
NLS_COMP',,C18('15')='BINARY',,C17('16')='NLS_LENGTH_SEMANTICS',,
C18('16')='BYTE',,C17('17')='NLS_NCHAR_CONV_EXCP',,C18('17')='FAL
SE',,C19='17',

SQL> conn sender/sender
Connected.
SQL> insert into tddl values(&a);
Enter value for a: 1
old   1: insert into tddl values(&a)
new   1: insert into tddl values(1)

1 row created.

SQL> /
Enter value for a: 2
old   1: insert into tddl values(&a)
new   1: insert into tddl values(2)

1 row created.

SQL> commit;

Commit complete.

SQL>  alter table tddl add tclob clob default 'I am testing';
Table altered.

/* 我们来看看添加有默认值的clob字段这样的DDL语句是否能够同步成功 */

SQL> conn receiver/receiver
Connected.
SQL> set linesize 100 pagesize 1400;
SQL> select * from tddl;

        T1 TCLOB
---------- --------------------------------------------------------------------------------
         1 I am testing
         2 I am testing
/* 以上测试表明gg成功复制了添加有默认值的clob字段这样的DDL语句 */

我们可以从源端的$ORACLE_BASE/admin/$sid/udump目录下的ggs_ddl_trace.log日志文件中找到以下记录:
SESS 154688-2010-12-06 19:08:42 : DDL : ************************* 
Start of log for DDL sequence [18], v[ $Id: //depot/releases/OpenSys/v11.1.1.0/redist/SQLScripts/ddl_
setup.sql#2 $ ] trace level [0], owner schema of DDL package [MACLEAN], objtype [TABLE] 
name [SENDER.TDDL]
SESS 154688-2010-12-06 19:08:42 : DDLTRACE1 : Before Trigger: point in 
execution = [1.0], objtype [TABLE] name [SENDER.TDDL]
SESS 154688-2010-12-06 19:08:42 : DDL : DDL operation 
[ alter table tddl add tclob clob default 'I am testing' ], sequence [18], 
DDL type [ALTER] TABLE, real object ty
pe [TABLE], validity [VALID], object ID [57772], object [SENDER.TDDL], 
real object [SENDER.TDDL], base object schema [], base object name [], logged as [SENDER]
SESS 154688-2010-12-06 19:08:42 : DDL : Start SCN found [45357217]
SESS 154688-2010-12-06 19:08:42 : DDL : ------------------------- End of log for DDL sequence [18]

之前有客户选用了Quest的shareplex复制软件来achive一个上海到北京的live standby环境,所以有机会参加了几次shareplex的切换演练,虽然我对shareplex十分陌生(直到现在也是)。在实际切换期间也多次听到shareplex的工程师反复强调复制环境中不能有BLOB/CLOB等大对象,也不能出现DDL语句(shareplex不能复制LOB和DDL吗,未经证实);可能该环境中的确很少变更,所以客户方的经理并不concern这一点:”我们不做DDL就是了嘛!” 这个复制环境因为数据库最初设计的时候没有考虑用主键或unique index且个别主用业务表上有巨多列(多于200个列)的原因导致后来add上追加日志后出现重做日志产生量水涨船高的问题。

不过相比起goldengate有点惊人的价格来说(如果你需要data repair功能,记得你还需要另外购买veridata),似乎shareplex对于中国客户还是有着不少的优势。 shareplex还是goldengate, 这是个问题。


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *