Slide:配置GoldenGate同步DDL语句

Script:脚本获得用户User DDL包括system、object grant

以下脚本用于获得用户User DDL包括system、object grant:

 

 

set head off
set pages 0
set long 9999999
spool user_script.sql

-- USER DDL
SELECT DBMS_METADATA.GET_DDL('USER', '&USERNAME') from dual;                         

-- ROLE GRANT 角色授权

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '&USERNAME')  from dual;

-- SYSTEM_GRANT 系统权限
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&USERNAME') from dual;

-- OBJECT_GRANT 对象权限

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '&USERNAME') from dual;

spool off

How to handle ddl in GoldenGate environment without ddl replication?

Question:gg only config to replicate dml, when source has some ddl changes, how to handle in source and target?

Answer:
GoldenGate capture the dml changes only by default,if you have made any changes in DDL structure,then stop the extract and replicat process and make changes in DDL structure and restart the extract and replicat process,then only extract can be able to capture the changes done at DDL structure.

It is advisible that stop extract,pump and replicat process make changes in the DDL and restart the extract,pump and replicat.
The above suggested procedure will not make any impact in DDL replication.

Also you can follow the below procedure
1. stop extract
2. stop data pump
3. there are lag in replicat, so replicat still running to reduce lag
4. do ddl in source, when replicat is running
5. stop replicat untill lag=0
6.do ddl in target
7.start extract and pump and replicat

Question:After do ddl in source, whether need to do delete and re-add the trandata on the table which is been changed?

Answer:
yes,you are correct,After doing DDL changes,you need to do delete and re-add the trandata on the table which is being changed.

配置GoldenGate同步DDL语句(3)

在配置GoldenGate同步DDL语句(2)中我们针对多种DDL语句在GoldenGate环境中的同步进行了测试,但在默认情况下replicat在复制DDL语句失败时不会在replicat report或者ggserr.log等日志文件中产生该DDL失败的具体信息,这就会导致我们在发现DDL同步有error的情况下无法了解失败的原因。之后又去翻官方的文档,在中找到了DDLOPTIONS参数:

Use the DDLOPTIONS parameter to configure aspects of DDL processing other than filtering
and string substitution. You can use multiple DDLOPTIONS statements, but using one is
recommended. If using multiple DDLOPTIONS statements, make each of them unique so that
one does not override the other. Multiple DDLOPTIONS statements are executed in the order
listed.

这个参数可用的选项很多,我们需要用到的是REPORT选项:

Valid for Extract and Replicat (Oracle and Teradata).
Controls whether or not expanded DDL processing
information is written to the report file. The default of
NOREPORT reports basic DDL statistics. REPORT adds the
parameters being used and a step-by-step history of the
operations that were processed

在replicat的参数文件中添加上”DDLOPTIONS REPORT”,可以让replicat在同步DDL语句时若出现问题,将该问题的详细情况记录到该replicat的report 文件中,以便找出DDL复制失败的root cause。

GGSCI (rh3.oracle.com) 9> 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
DDLOPTIONS REPORT
map sender.* , target receiver.*;
GGSCI (rh3.oracle.com) 10> stop replicat rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (rh3.oracle.com) 11> start replicat rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
在源端创建一外部表
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的report信息可以发现DDL复制失败的详细SQL语句等信息:
GGSCI (rh3.oracle.com) 12> view report rep1
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 15:58:11
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-12-10 05:54:32
***********************************************************************
...............
2010-12-09 09:10:13  INFO    OGG-01408  Restoring current schema for DDL operation to [MACLEAN].
2010-12-09 09:10:13  INFO    OGG-00482  DDL found, operation [CREATE
GLOBAL TEMPORARY TABLE "SENDER"."SYS_TEMP_0FD9D6609_2B3F4ED" ("OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME" VARCHAR2(30),"OBJECT_ID"
NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT_TYPE" VARCHAR2(19),"CREATED" DATE,"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHA R2(19),"STATUS" VARCHAR2(7),"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),"SECONDARY" VARCHAR2(1) ) IN_MEMORY_METADATA
CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950
921 ) NOPARALLEL  (size 456)].
2010-12-09 09:10:13  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation
[CREATE GLOBAL TEMPORARY TABLE "RECEIVER"."SYS_TEMP_0FD9D6609_2B3F4ED"
("OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME"
VARCHAR2(30),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT_TYPE"
VARCHAR2(19),"CREATED" DATE,"LAST_DDL_TIME" DATE,"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),"SECONDARY" VARCHAR2(1) ) IN_MEMORY_METADATA CURSOR_SPECIF
IC_SEGMENT STORAGE (OBJNO 4254950921 ) NOPARALLEL  (size 458)].
2010-12-09 09:10:13  INFO    OGG-00487  DDL operation included [INCLUDE ALL], optype [CREATE],
objtype [TABLE], objowner [RECEIVER], objname [SYS_TEMP_0FD9D6609_2B3F4E
D].
2010-12-09 09:10:13  INFO    OGG-01407  Setting current schema for DDL operation to [SYS].
2010-12-09 09:10:13  INFO    OGG-00484  Executing DDL operation.
2010-12-09 09:10:18  INFO    OGG-00495  DDL error ignored for next retry: error code
[DEFAULT],
filter [include all (default)], error text [Error code [900],
ORA-00900: invalid SQL statement, SQL CREATE GLOBAL TEMPORARY TABLE "RECEIVER"."SYS_TEMP_0FD9D6609_2B3F4ED"
("OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME" V
ARCHAR2(30),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT], retry [1].
2010-12-09 09:10:18  INFO    OGG-01407  Setting current schema for DDL operation to [SYS].
2010-12-09 09:10:18  INFO    OGG-00484  Executing DDL operation trying again due to RETRYOP parameter.
2010-12-09 09:10:23  INFO    OGG-00495  DDL error ignored for next retry: error code [DEFAULT],
filter [include all (default)], error text
[Error code [900], ORA-00900
: invalid SQL statement, SQL CREATE GLOBAL TEMPORARY TABLE "RECEIVER"."SYS_TEMP_0FD9D6609_2B3F4ED"
("OWNER" VARCHAR2(30),"OBJECT_NAME" VARCHAR2(128),"SUBOBJECT_NAME" V
ARCHAR2(30),"OBJECT_ID" NUMBER,"DATA_OBJECT_ID" NUMBER,"OBJECT], retry [2].
2010-12-09 09:10:23  INFO    OGG-01407  Setting current schema for DDL operation to [SYS].

另外需要注意的是如create table as select这样的建表DDL语句应该被杜绝使用,因为在goldengate复制环境中create table as select语句仍会以DDL形式被应用,这就导致源端和目标的端的数据不一致,如:

/* 源端中dba_object视图共有52046行数据 */
SQL> select count(*) from dba_objects;
COUNT(*)
----------
52046
SQL> create table ddlproblem as select * from dba_objects;
Table created.
SQL> select count(*) from ddlproblem;
COUNT(*)
----------
52047
而在目标端的复制情况:
SQL> desc ddlproblem
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
SQL> select count(*) from ddlproblem;
COUNT(*)
----------
51008
/* 因为replicat在目标端仅是简单的执行"create table as select"的建表DDL语句,
而源端和目标端中这个被select的对象中的数据并不一致,因而CTAS操作只会导致
2端的数据不一致
*/

配置GoldenGate同步DDL语句(2)

接下来我们会测试更多不同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语句复制失败,当因为目前找不到相关的日志所以无法进一步分析了 */

配置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, 这是个问题。

Script:Generating CREATE USER DDL Statements

Title: Generating CREATE USER DDL Statements
Author:Ted Martin, a database administrator in Ottawa, Ontario, Canada.
These scripts will generate SQL DDL statements related to the creation of user accounts. The types of statements generated
are as follows:
1. CREATE USER and ALTER USER...QUOTA x ON [tabspace] (GENUSER.SQL)
2. CREATE role (GENROLE.SQL)
3. GRANT [role|priv] TO user (GRANTPRIV.SQL)
All three scripts ask for execution parameters. If you leave such a parameter blank, the script will generate for all. The
exception is the prompt for the output filename.
Source/Text/Comments
REM
REM    PROGRAM-ID : GENUSER.SQL
REM    WRITTEN BY : Ted Martin
REM  DATE WRITTEN : 26-AUG-1998
REM
clear screen
PROMPT GENUSER.SQL           Generates CREATE USER commands
PROMPT
PROMPT Includes ALTER USER...QUOTA x ON tabspace commands
PROMPT
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '
col username noprint
col lne newline
set heading off pagesize 0 verify off feedback off
spool &&outfile..gen
prompt genuser.log
prompt set term on echo off
prompt prompt Creating User Accounts...
prompt set term off echo on
SELECT username, 'CREATE USER '||username||' '||
DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
'IDENTIFIED BY '''||password||''' ') lne,
'DEFAULT TABLESPACE '||default_tablespace lne,
'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
FROM DBA_USERS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
prompt set term on echo off
prompt prompt Granting Tablespace Quotas...
prompt set term off echo on
SELECT username, 'ALTER USER '||username||' QUOTA '||
DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
||' ON TABLESPACE '||tablespace_name||';' lne
FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
spool off
PROMPT
PROMPT File &&outfile..GEN generated. Please review before using
PROMPT
EXIT
=============================================================
REM
REM      PROGRAM-ID : GENROLE.SQL
REM      WRITTEN BY : Ted Martin
REM    DATE WRITTEN : 6-APR-1996
REM
set term on  echo off   linesize 132  pagesize 0  heading off
set verify off
clear screen
prompt GENROLE.SQL V1.0            Generate CREATE ROLE statements
prompt
prompt
accept rname   prompt  '      Grant Role : '
accept outfile prompt  ' Output filename : '
set feedback off pagesize 0 heading off
col lne newline
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Role = &&rname
prompt spool &&outfile..log
prompt set term on  echo off  feedback on
select 'CREATE ROLE '||role||';' lne
from dba_roles
where role like UPPER('%&&rname%')
and role not in ('CONNECT', 'RESOURCE', 'DBA',
'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY ROLE;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit
=====================================================
REM
REM      PROGRAM-ID : GRANTPRIVS.SQL
REM      WRITTEN BY : Ted Martin
REM    DATE WRITTEN : 26-AUG-1998
REM
clear screen
set term on  echo off   linesize 132  pagesize 0  heading off
set verify off
prompt GRANTPRIVS.SQL             Generate Existing GRANT role/priv statements
prompt
prompt Handles both Roles and System Privs. Excludes SYS and SYSTEM accounts
prompt
accept rname   prompt  '      Grant Priv : '
accept towner   prompt '         To User : '
accept outfile prompt  ' Output filename : '
set feedback off  verify off
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Priv = &&rname
prompt prompt . . User = &&towner
prompt spool &&outfile..log
prompt set term on  echo on  feedback on
col grantee noprint
col granted_priv noprint
select grantee, granted_role granted_priv,
'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where (granted_role like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
UNION
select grantee, privilege granted_priv,
'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where (privilege like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
order by 1, 2;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit

沪ICP备14014813号

沪公网安备 31010802001379号