接下来我们会测试更多不同DDL语句的同步，这些我能想到的DDL语句包括:
- CREATE USER
- CREATE TABLESPACE
- CREATE PROCEDURE
- CREATE PROFILE
- CREATE DIRECTORY
- CREATE EXTERNAL TABLE 等等
但在这之前我们需要修改我们在GoldenGate同步DDL语句(1)中配置的extract和replicat的参数文件:
GGSCI (rh2.oracle.com) 2> view params load1
extract load1
userid maclean, password AACAAAAAAAAAAAHANCUEFHPAVCYDNJVD,encryptkey default
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTTRAIL /s01/rmt/ma
DDL INCLUDE ALL
Table sender.*;
/*源端将 DLL INCLUDE MAPPED 修改为DLL INCLUDE ALL，
不同于MAPPED，INCLUDE ALL意味包含所有DDL语句
MAPPED applies INCLUDE or EXCLUDE to DDL operations that are of
MAPPED scope. MAPPED filtering is performed before filtering that is
specified with other DDL parameter options
ALL applies INCLUDE or EXCLUDE to DDL operations of all scopes.
*/
GGSCI (rh2.oracle.com) 4> stop extract load1
Sending STOP request to EXTRACT LOAD1 ...
Request processed.
GGSCI (rh2.oracle.com) 5> start extract load1
Sending START request to MANAGER ...
EXTRACT LOAD1 starting
GGSCI (rh3.oracle.com) 3> view params rep1
replicat rep1
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/rep1.log,append,megabytes 10
-- Support DDL here
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
map sender.* , target receiver.*;
/* 目标端也需要将原来的DDL 补充为DDL INCLUDE ALL以及加上
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5 */
/* DDLERROR参数指定了出现DDL同步错误时的处理方式，此处为以5秒(RETRYDELAY)
为间隔重试3次(MAXRETRIES)，若该DDL仍报错，则忽略该DDL
*/
GGSCI (rh3.oracle.com) 4> stop replicat rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (rh3.oracle.com) 5> start replicat rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
接下来我们要具体测试多种DDL语句的gg同步情况:
SQL> create user guser identified by guser;
User created.
源端的$ORACLE_BASE/admin/$SID/udump下的ggs_ddl_trace.log，纪录了该create user ddl语句:
Start of log for DDL sequence [137], 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 [USER] name [.GUSER]
SESS 4294967295-2010-12-07 22:21:06 : DDLTRACE1 : Before Trigger: point in execution = [1.0], objtype [USER] name [.GUSER]
SESS 4294967295-2010-12-07 22:21:06 : DDL : DDL operation [create user guser identified by ***** ]
, sequence [137], DDL type [CREATE] USER, real object type [USER], va
lidity [], object ID [], object [.GUSER], real object [.GUSER], base object schema [], base object name [], logged as [SYS]
SESS 4294967295-2010-12-07 22:21:06 : DDL : Start SCN found [45484306]
End of log for DDL sequence [137], no DDL history metadata recorded for this DDL operation
目标端同步情况:
SQL> select username from dba_users where created>=all(select created from dba_users);
USERNAME
------------------------------
GUSER
/* 可以看到create user语句复制成功 */
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /s01/10gdb/oradata
SQL> create tablespace gtb datafile size 10M;
Tablespace created.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /s01/oradata
SQL> select file_name from dba_data_files where tablespace_name='GTB';
FILE_NAME
--------------------------------------------------------------------------------
/s01/oradata/CLINICB/datafile/o1_mf_gtb_6hxjpstg_.dbf
/* 在远端和目标端都启用了10g中自动管理数据文件创建的前提下，
创建表空间的DDL语句被复制成功
*/
/* 若我们在源端创建某表，且为该表指定一个目标端不存在的表空间时，
create table语句会如何呢？
*/
SQL> conn sender/sender
Connected.
SQL> create table rbf (t1 int) tablespace bf;
Table created.
可以在目标端的replicat report中看到以下4次尝试，其后三次应为我们之前定义的MAXRETRIES:
2010-12-08 07:37:43 INFO OGG-01407 Setting current schema for DDL operation to [RECEIVER].
2010-12-08 07:37:48 INFO OGG-01407 Setting current schema for DDL operation to [RECEIVER].
2010-12-08 07:37:53 INFO OGG-01407 Setting current schema for DDL operation to [RECEIVER].
2010-12-08 07:37:58 INFO OGG-01407 Setting current schema for DDL operation to [RECEIVER].
2010-12-08 07:37:58 INFO OGG-01408 Restoring current schema for DDL operation to [MACLEAN].
GGSCI (rh3.oracle.com) 21> stats replicat rep1
Sending STATS request to REPLICAT REP1 ...
No active replication maps
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 4.00
Mapped operations 2.00
Unmapped operations 0.00
Other operations 2.00
Excluded operations 0.00
Errors 8.00
Retried errors 6.00
Discarded errors 0.00
Ignored errors 2.00
/* 虽然可以从stats replicat的输出中看到Errors和Retried errors数，
但在replicat的report和ggserr日志中都没有纪录DDL同步失败的具体信息，准备提SR了！
*/
/* 在源端创建以下简单的存储过程 */
SQL> conn sender/sender
SQL> create or replace procedure progg as
2 i int;
3 begin
4 select t1 into i from tddl where rownum=1;
5 dbms_output.put_line(i);
6 end;
7 /
Procedure created.
SQL> conn receiver/receiver
Connected.
SQL> select t1 from tddl;
T1
----------
1
2
SQL> set serveroutput on;
SQL> exec progg;
1
PL/SQL procedure successfully completed.
/* 因为目标端存在该存储过程的依赖条件，所以复制后编译成功；
否则复制可以成功但该对象状态为INVALID */
SQL> drop procedure progg;
Procedure dropped.
SQL> drop table tddl;
Table dropped.
在源端再次创建该procedure后：
SQL> col object_name for a30
SQL> select object_name,status from dba_objects where object_name='PROGG';
OBJECT_NAME STATUS
------------------------------ -------
PROGG INVALID
/* 在源端创建profile配置文件 */
SQL> create profile gg_profile limit sessions_per_user 2;
Profile created.
SQL> select RESOURCE_NAME,LIMIT from dba_profiles where profile='GG_PROFILE' and limit!='DEFAULT';
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
SESSIONS_PER_USER 2
/*在目标端配置文件信息同步成功 */
/* 在源端创建文件夹directory对象 */
SQL> create directory dgg as '/s01/dump';
Directory created.
SQL> col directory_name for a3
SQL> col directory_path for a20
SQL> select directory_name,directory_path from dba_directories where directory_name='DGG';
DIR DIRECTORY_PATH
--- --------------------
DGG /s01/dump
/*在文件系统上都存在/s01/dump文件夹的前提下create directory ddl同步成功*/
/*在源端创建type为oracle_datapump的外部表 */
SQL> CREATE TABLE extgg
2 ORGANIZATION EXTERNAL
3 (TYPE oracle_datapump
4 DEFAULT DIRECTORY dgg
5 LOCATION ('extgg.dat'))
6 AS
7 select * from dba_objects;
Table created.
在目标端replicat中出现多次retry纪录:
2010-12-08 08:13:09 INFO OGG-01407 Setting current schema for DDL operation to [RECEIVER].
2010-12-08 08:13:14 INFO OGG-01407 Setting current schema for DDL operation to [RECEIVER].
2010-12-08 08:13:19 INFO OGG-01407 Setting current schema for DDL operation to [RECEIVER].
2010-12-08 08:13:24 INFO OGG-01407 Setting current schema for DDL operation to [RECEIVER].
2010-12-08 08:13:24 INFO OGG-01408 Restoring current schema for DDL operation to [MACLEAN].
/* 同时ddl Errors数目增加 */
GGSCI (rh3.oracle.com) 34> stats replicat rep1
Sending STATS request to REPLICAT REP1 ...
No active replication maps
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 11.00
Mapped operations 6.00
Unmapped operations 0.00
Other operations 5.00
Excluded operations 0.00
Errors 33.00
Retried errors 24.00
Discarded errors 0.00
Ignored errors 9.00
/* 无疑是创建外部表的DDL语句复制失败，当因为目前找不到相关的日志所以无法进一步分析了 */
Comment