Sample
drop user ogg_maclean cascade;
create user ogg_maclean identified by oracle;
alter user ogg_maclean default tablespace users;
grant connect,resource to OGG_MACLEAN;
OGG_MACLEAN
maclean_press
conn ogg_maclean/oracle
create table maclean_press1(a int constraint key1 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press2(a int constraint key2 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press3(a int constraint key3 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press4(a int constraint key4 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press5(a int constraint key5 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press6(a int constraint key6 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press7(a int constraint key7 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press8(a int constraint key8 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press9(a int constraint key9 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press10(a int constraint key10 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create or replace procedure trouble_ogg_sql as
begin
for i in 1..2000000 loop
insert into maclean_press1(a,b,c,d) values(i,i,i,sysdate);
insert into maclean_press2(a,b,c,d) values(i,i,i,sysdate);
insert into maclean_press3(a,b,c,d) values(i,i,i,sysdate);
insert into maclean_press4(a,b,c,d) values(i,i,i,sysdate);
insert into maclean_press5(a,b,c,d) values(i,i,i,sysdate);
insert into maclean_press6(a,b,c,d) values(i,i,i,sysdate);
insert into maclean_press7(a,b,c,d) values(i,i,i,sysdate);
insert into maclean_press8(a,b,c,d) values(i,i,i,sysdate);
insert into maclean_press9(a,b,c,d) values(i,i,i,sysdate);
insert into maclean_press10(a,b,c,d) values(i,i,i,sysdate);
if mod(i,2000)=0 then
commit;
end if;
end loop;
delete from maclean_press1 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press1 move');
EXECUTE IMMEDIATE('alter index key1 rebuild');
delete from maclean_press2 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press2 move');
EXECUTE IMMEDIATE('alter index key2 rebuild');
delete from maclean_press3 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press3 move');
EXECUTE IMMEDIATE('alter index key3 rebuild');
delete from maclean_press4 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press4 move');
EXECUTE IMMEDIATE('alter index key4 rebuild');
delete from maclean_press5 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press5 move');
EXECUTE IMMEDIATE('alter index key5 rebuild');
delete from maclean_press6 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press6 move');
EXECUTE IMMEDIATE('alter index key6 rebuild');
delete from maclean_press7 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press7 move');
EXECUTE IMMEDIATE('alter index key7 rebuild');
delete from maclean_press8 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press8 move');
EXECUTE IMMEDIATE('alter index key8 rebuild');
delete from maclean_press9 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press9 move');
EXECUTE IMMEDIATE('alter index key9 rebuild');
delete from maclean_press10 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press10 move');
EXECUTE IMMEDIATE('alter index key10 rebuild');
for i in 1..1 loop
update maclean_press1 set b=b+1 where a <= 20000;
commit;
update maclean_press2 set b=b+1 where a <= 20000;
commit;
update maclean_press3 set b=b+1 where a <= 20000;
commit;
update maclean_press4 set b=b+1 where a <= 20000;
commit;
update maclean_press5 set b=b+1 where a <= 20000;
commit;
update maclean_press6 set b=b+1 where a <= 20000;
commit;
update maclean_press7 set b=b+1 where a <= 20000;
commit;
update maclean_press8 set b=b+1 where a <= 20000;
commit;
update maclean_press9 set b=b+1 where a <= 20000;
commit;
update maclean_press10 set b=b+1 where a <= 20000;
commit;
end loop;
for i in 1..1 loop
delete from maclean_press1 where a > 30000 and a <= 40000;
commit;
delete from maclean_press2 where a > 30000 and a <= 40000;
commit;
delete from maclean_press3 where a > 30000 and a <= 40000;
commit;
delete from maclean_press4 where a > 30000 and a <= 40000;
commit;
delete from maclean_press5 where a > 30000 and a <= 40000;
commit;
delete from maclean_press6 where a > 30000 and a <= 40000;
commit;
delete from maclean_press7 where a > 30000 and a <= 40000;
commit;
delete from maclean_press8 where a > 30000 and a <= 40000;
commit;
delete from maclean_press9 where a > 30000 and a <= 40000;
commit;
delete from maclean_press10 where a > 30000 and a <= 40000;
commit;
end loop;
end;
/
exec ogg_maclean.trouble_ogg_sql;
select count(*),sum(a),sum(b) from maclean_press1;
select count(*),sum(a),sum(b) from maclean_press2;
select count(*),sum(a),sum(b) from maclean_press3;
select count(*),sum(a),sum(b) from maclean_press4;
select count(*),sum(a),sum(b) from maclean_press5;
select count(*),sum(a),sum(b) from maclean_press6;
select count(*),sum(a),sum(b) from maclean_press7;
select count(*),sum(a),sum(b) from maclean_press8;
select count(*),sum(a),sum(b) from maclean_press9;
select count(*),sum(a),sum(b) from maclean_press10;
主键更新测试（针对Quest）
create table tb1a (id number primary key, name varchar(30));
执行以下sql进行主键更新：
Begin
for i in 1..1000 loop
insert into tb1a values (i, 'aaa');
end loop;
commit;
update tb1a set id=id+100;
commit;
end;
/
select min(id),max(id),sum(id) from tb1a;
分区表
create table maclean_partition (acct_no number(12),person varchar2(30),week_no number(2)) partition by range (week_no)
(partition jan values less than(4),partition feb values less than(8),partition others values less than (maxvalue)) enable row movement;
insert into maclean_partition values(1,'a',2);
insert into maclean_partition values(2,'b',6);
insert into maclean_partition values(3,'c',10);
insert into maclean_partition values(6,'d',6);
insert into maclean_partition values(8,'e',8);
insert into maclean_partition values(9,'e',9);
commit;
update maclean_partition set person='d' where acct_no=2;
commit;
delete maclean_partition where acct_no=1;
commit;
==========================================================================================================
ASM 的 tnsnames.ora 以及配置监听静态注册
===========================================================================================================
解压软件，配置ogg.sh
export GG_HOME=/goldengate
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
GGSCI> create subdirs
打开归档
Select log_mode from v$database;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
alter database add supplemental log data ;
--alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
Alter database force logging;
创建ogg 用户
--create tablespace
create tablespace goldengate datafile size 1024M ;
-- Create the user
create user goldengate identified by &A default tablespace goldengate;
-- Grant role privileges
grant resource, connect, dba to goldengate;
grant create any table to goldengate;
grant create any view to goldengate;
grant create any procedure to goldengate;
grant create any sequence to goldengate;
grant create any index to goldengate;
grant create any trigger to goldengate;
grant create any view to goldengate;
===========================================================================================================
[oracle@vrh1 ~]$ cat ogg.sh
export ORACLE_HOME=/s01/orabase/product/11.2.0/dbhome_1
export ORACLE_SID=VRAC1
export LD_LIBRARY_PATH=/ogg:$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export PATH=/home/oracle/ogg:$PATH
cd /home/oracle/ogg
ggsci
===========================================================================================================
./GLOBALS
GGSCHEMA goldengate
CheckpointTable goldengate.ckpt
UnlockedTrailFiles
===========================================================================================================
MGR
Port 7809
userid goldengate , password oracle
DYNAMICPORTLIST 9101-9356
CheckMinutes 10
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepHours 96
PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120
AutoRestart ER *, WaitMinutes 5, Retries 3
LagInfoMinutes 0
LagReportMinutes 10
===========================================================================================================
源端DDL
cd ogg
GRANT EXECUTE ON UTL_FILE TO goldengate;
ALTER SYSTEM SET RECYCLEBIN =OFF SCOPE =BOTH;
11g中 只能ALTER SYSTEM SET RECYCLEBIN =OFF SCOPE =SPFILE; 之后重启
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
GRANT GGS_GGSUSER_ROLE TO goldengate;
@ddl_enable.sql
@ddl_pin.sql goldengate
@sequence.sql
dblogin userid goldengate , password oracle
add checkpointtable goldengate.ckpt
ADD TRANDATA XX.XX
===========================================================================================================
add extract ext01, tranlog , begin now , threads 2
add exttrail ./dirdat/ml , extract ext01 , megabytes 200
extract ext01
SETENV (ORACLE_HOME="/s01/orabase/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="VRAC1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
--TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
--CacheMgr CacheDirectory ./dirtmp 51980MB, CacheDirectory ./dirtmp/goldengate_tmp
userid goldengate , password oracle
--TranLogOptions ExcludeUser goldengate
--TranLogOptions AltArchivedLogFormat Instance NETDB1 %t_%s_%r.dbf
--TranLogOptions AltArchivedLogFormat Instance NETDB2 %t_%s_%r.dbf
--TranLogOptions AltArchiveLogDest Primary Instance NETDB1 /arch1, AltArchiveLogDest Instance NETDB2 /arch2
tranlogoptions asmuser sys@ASM , ASMPASSWORD oracle
--TRANLOGOPTIONS DBLOGREADER, DBLOGREADER BUFSIZE [x], BUFSIZE [y]
ThreadOptions MaxCommitPropagationDelay 10000 IOLatency 3000
exttrail ./dirdat/ml
DDL Include ALL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions AddTranData, Report
DDLOptions NoCrossRename, Report
Table ogg_maclean.*;
-- Prevent data looping. This is generally used in bi-directional
-- configuration
TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
===========================================================================================================
add extract pump01, EXTTRAILSOURCE ./dirdat/ml
add rmttrail ./dirdat/ml , extract pump01, megabytes 200
pump
extract pump01
SETENV (ORACLE_HOME = "<Oracle home path>" )
SETENV (ORACLE_SID="<Oracle sid>")
passthru
rmthost 192.168.1.179 , mgrport 7809
rmttrail ./dirdat/ml
--DISCARDFILE <diacard file path/<discard file name.dsc>, APPEND Megabytes <n>
table ogg_maclean.*;
===========================================================================================================
add replicat rep01, exttrail ./dirdat/ml
replicat
replicat rep01
SETENV (ORACLE_HOME = "/s01/oracle/app/oracle/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="PRODA")
SETENV (NLS_LANG ="American_America.AL32UTF8")
userid goldengate , password oracle
--HandleCollisions
AssumeTargetDefs
DiscardFile ./dirrpt/rep1.dsc, APPEND Megabytes 800 , Purge
DBOptions DeferrefConst
DBOptions SuppressTriggers
MaxTransOps 10000
GroupTransOps 1000
SQLEXEC "ALTER SESSION SET COMMIT_WRITE = BATCH,NOWAIT"
BatchSQL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions Report
DDLError 24344 Ignore
DDLError 4052 Ignore
DDLError 955 Ignore
DDLError 1408 Ignore
DDLError 911 Ignore
AllowNoOpUpdates
CheckSequenceValue
--IGNORETRUNCATES
--DEFERAPPLYINTERVAL 1 MINUTES
-- Sequence testgg.*, Target testgg.*
MapExclude ogg_maclean.SYS_EXPORT_SCHEMA* ;
map ogg_maclean.* , target ogg_maclean.* ;
--DDLOPTIONS SUPPRESSTRIGGERS
--The SUPPRESSTRIGGERS parameter prevents triggers from firing on
target objects that are configured for replication with Oracle GoldenGate.
This alleviates the need to manually disable triggers and constraints.
To use this option, the Replicat user must be an Oracle Streams administrator
which can be granted by invoking dbms_goldengate_auth.grant_admin_privilege.
Available FROM ORACLE 10.2.0.5 AND Later Patches and Oracle 11.2.0.2
================================================================================================
DROP TABLE gg_test;
CREATE TABLE gg_test
(
a number(10),
b VARCHAR20(30),
PRIMARY KEY (a)
);
4.8 100字段表测试脚本
create table table_100cols
(
a1 number(10) not null,
a2 number(10),
a3 number(10),
a4 number(10),
a5 number(10),
a6 number(10),
a7 number(10),
a8 number(10),
a9 number(10),
a10 number(10),
a11 number(10),
a12 number(10),
a13 number(10),
a14 number(10),
a15 number(10),
a16 number(10),
a17 number(10),
a18 number(10),
a19 number(10),
a20 number(10),
a21 number(10),
a22 number(10),
a23 number(10),
a24 number(10),
a25 number(10),
a26 number(10),
a27 number(10),
a28 number(10),
a29 number(10),
a30 number(10),
a31 number(10),
a32 number(10),
a33 number(10),
a34 number(10),
a35 number(10),
a36 number(10),
a37 number(10),
a38 number(10),
a39 number(10),
a40 number(10),
a41 number(10),
a42 number(10),
a43 number(10),
a44 number(10),
a45 number(10),
a46 number(10),
a47 number(10),
a48 number(10),
a49 number(10),
a50 number(10),
a51 number(10),
a52 number(10),
a53 number(10),
a54 number(10),
a55 number(10),
a56 number(10),
a57 number(10),
a58 number(10),
a59 number(10),
a60 number(10),
a61 number(10),
a62 number(10),
a63 number(10),
a64 number(10),
a65 number(10),
a66 number(10),
a67 number(10),
a68 number(10),
a69 number(10),
a70 number(10),
a71 number(10),
a72 number(10),
a73 number(10),
a74 number(10),
a75 number(10),
a76 number(10),
a77 number(10),
a78 number(10),
a79 number(10),
a80 number(10),
a81 number(10),
a82 number(10),
a83 number(10),
a84 number(10),
a85 number(10),
a86 number(10),
a87 number(10),
a88 number(10),
a89 number(10),
a90 number(10),
a91 number(10),
a92 number(10),
a93 number(10),
a94 number(10),
a95 number(10),
a96 number(10),
a97 number(10),
a98 number(10),
a99 number(10),
a100 number(10)
)
alter table table_100cols
add constraint PK_100cols primary key (a1)
using index
tablespace CS_stat
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table table_100cols
add constraint PK_100cols primary key (a1)
using index
tablespace users
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
insert into table_100cols values(1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24,
25,
26,
27,
28,
29,
30,
31,
32,
33,
34,
35,
36,
37,
38,
39,
40,
41,
42,
43,
44,
45,
46,
47,
48,
49,
50,
51,
52,
53,
54,
55,
56,
57,
58,
59,
60,
61,
62,
63,
64,
65,
66,
67,
68,
69,
70,
71,
72,
73,
74,
75,
76,
77,
78,
79,
80,
81,
82,
83,
84,
85,
86,
87,
88,
89,
90,
91,
92,
93,
94,
95,
96,
97,
98,
99,
100);
4.9 性能测试脚本
create sequence seq_gg start with 1 INCREMENT BY 1 MAXVALUE 999999999 cache 500000;
create or replace procedure gg_insert
is
begin
for i in 1..1000000 loop
insert into CM_USER (USER_ID,REGION_CODE,ACC_ID,CAUT_ID,USER_STATUS,USER_TYPE)
values(seq_gg.nextval,571,1111,2222,3,4);
if mod(i,1000)=0 then
commit;
end if;
end loop;
commit;
end;
/
create or replace procedure gg_update
is
CURSOR c_gg IS
SELECT rowid FROM CM_USER;
v_rowid UROWID;
i number(10);
BEGIN
OPEN c_gg;
for i in 1..1000000 loop
FETCH c_gg INTO v_rowid;
EXIT WHEN c_gg%NOTFOUND;
UPDATE CM_USER SET ACC_ID=seq_gg.nextval WHERE rowid = v_rowid;
if mod(i,1000)=0 then
commit;
end if;
end loop;
commit;
CLOSE c_gg;
END;
/
create or replace procedure gg_delete
is
begin
for i in 1..1000 loop
delete from CM_USER where rownum<1001;
commit;
end loop;
commit;
end;
/
attachment:
Oracle GoldenGate 在其新推出的版本11.2 中提供了全方位的多字节支持，
可实现对表名/列名，以及DDL 中常见多字节文字如中文的支持。
注意：不同字符集DML 复制必须要将目标的NLS_LANG 变量设置为源端的NLS_LANG 实际值。
实验证明，本版本可以支持相同和不同字符集之间带有多字节对象以及数据的DML 和DDL 复制。
新版本的 DDL 复制默认不再开启 addtrandata，需要手工配置ddloptions。Oracle 11.2.0.2以上版本则推荐使用schema level 附加日志，参考步骤：
改用schemal level trandata：
SQL> exec dbms_streams_auth.grant_admin_privilege(‘ogg’);
GGSCI (dbsrv64.localdomain) 2> dblogin userid ogg
GGSCI (dbsrv64.localdomain) 3> ADD SCHEMATRANDATA source
2012-02-23 03:26:04 INFO OGG-01788 SCHEMATRANDATA has
been added on schema source.
注意：目前似乎不支持在 map 或者 table 里面使用中文进行匹配，
需要进一步验证。例如下面的将全部被认为是unmapped：
map source.中文*, target target.*;
如果两端字符集不同，则需要将目标的 NLS_LANG 变量设置为源
端的NLS_LANG实际值，可参考之前的最佳实践。
使用defgen 可以指定生成的表定义文件编码，不过实际配置要注意环境变量与数据库环境变量的统一，例如中文环境参考如下：
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG=zh_CN.GBK
Oracle GoldenGate 在其新推出的版本11.2 中提供了两种模式抽取：
1 传统的挖掘日志模式；
2 新的整合捕获模式。该模式使用了Oracle XStream 接口进行数据捕捉。
schema level trandata 必须要Oracle 11.2.0.2 以上；
整合模式只有在11.2.0.3 以上才能支持更为广泛的数据类型。
1) 数据库打开最小附加日志；
alter database add supplemental log data;
2) 为source schema 加入schema level 附加日志：
SQL> exec dbms_streams_auth.grant_admin_privilege(‘ogg’);
GGSCI (dbsrv64.localdomain) 2> dblogin userid ogg
GGSCI (dbsrv64.localdomain) 3> ADD SCHEMATRANDATA source
2012-02-23 03:26:04 INFO OGG-01788 SCHEMATRANDATA has been
added on schema source.
3) 给抽取进程用户赋予admin 权限：
begin
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => ‘source’,
privilege_type => ‘capture’,
grant_select_privileges => true,
do_grants => TRUE
);
end;
/
4) 在数据库中注册抽取进程，此时会自动配置XStream：
DBLOGIN USERID source, PASSWORD oracle
REGISTER EXTRACT ext01 DATABASE
5) 配置OGG 的extract 和replicat 进程
ADD EXTRACT ext01, INTEGRATED TRANLOG, BEGIN NOW
Add exttrail ./dirdat/ml, ext ext01
Add rep rep01, exttrail ./dirdat/ia
GGSCI (dbsrv64.localdomain) 5> view param ext01
EXTRACT ext01
SETENV (ORACLE_SID = “oragbk”)
–SETENV (NLS_LANG = “AMERICAN_AMERICA.ZHS16GBK”)
SETENV (NLS_LANG = “SIMPLIFIED CHINESE_CHINA.ZHS16GBK”)
USERID source, PASSWORD oracle
ddl include all
–ddloptions addtrandata,report
ddloptions report
tranlogoptions dblogreader
EXTTRAIL ./dirdat/ml
dynamicresolution
tableexclude source.AQ*;
tableexclude source.OGG*;
tableexclude source.SYS*;
table source.*;
GGSCI (dbsrv64.localdomain) 6> view param rep01
replicat rep01
–checkparams
–SETENV (ORACLE_SID = “oragbk”)
SETENV (ORACLE_SID = “orautf”)
SETENV (NLS_LANG = “SIMPLIFIED CHINESE_CHINA.ZHS16GBK”)
–SETENV (NLS_LANG = “AMERICAN_AMERICA.AL32UTF8”)
userid target, password oracle
ddl include mapped
ddlerror 24344,ignore
ddlerror 955,ignore
–DDLOPTIONS REPORT
reperror default,discard
–reperror default, abend
discardfile ./dirrpt/repia.dsc,append
assumetargetdefs
map source.*, target target.*;
6) 确认进程启动后，在源端添加中文表，并增加和更新记录，成功。
注意：
使用整合模式会在extract 用户下建立一些AQ$/SYS/OGG$开头的表，建议使用一个专门用户。本例子中直接抽取了source 自己的表，需要将这些系统表排除掉
GGSCI (mlab2.oracle.com) 2> ADD SCHEMATRANDATA ogg_maclean
2013-11-24 00:42:48 ERROR OGG-01790 Failed to ADD SCHEMATRANDATA on schema ogg_maclean because of the following SQL error: ORA-26947: Oracle GoldenGate replication is not enabled.
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 1565
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 1074
ORA-06512: at “SYS.DBMS_CAPTURE_ADM”, line 722
ORA-06512: at line 1 SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION(‘ogg_maclean’,’ALLKEYS_ON’); END;.
GGSCI (mlab2.oracle.com) 3> ADD SCHEMATRANDATA goldengate;
2013-11-24 00:43:29 ERROR OGG-01796 Schema: goldengate;, does not exist.
GGSCI (mlab2.oracle.com) 4> ADD SCHEMATRANDATA goldengate
2013-11-24 00:43:38 ERROR OGG-01790 Failed to ADD SCHEMATRANDATA on schema goldengate because of the following SQL error: ORA-26947: Oracle GoldenGate replication is not enabled.
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 1565
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 1074
ORA-06512: at “SYS.DBMS_CAPTURE_ADM”, line 722
ORA-06512: at line 1 SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION(‘goldengate’,’ALLKEYS_ON’); END;.
11.2.0.4中需要设置
SQL> alter system set enable_goldengate_replication=true;
System altered.
WARNING OGG-02051 Not enough database memory to service Extract in integrated capture.
==>Streams: resolve low memory condition+LogMiner reader: buffer+LogMiner builder: memory=> Streams Pool Size 不足 增大streams_pool_size即可
source: HP-IA 192.168.174.201/202 root/roothp sys/sys or oracle
target: Linux 192.168.110.200/116.180 root/roothp
安装目录：/app/ogg/va
复制表： unipostdb.
T_PUB_JNL_SAV
T_PUB_UNREG
T_CDM_LEDGER
TMP_CARD_LEDGER TABLE
TMP_LEDGER TABLE
TRIG_CDM_LEDGER TABLE
T_CARD_ACC TABLE
T_CARD_ATM TABLE
T_CARD_LEDGER TABLE
T_CDM_LEDGER TABLE
T_PUB_JNL_SAV TABLE
T_PUB_UNREG TABLE
UNIPOSTDB_TEST TABLE
UNREG_BAK TABLE
conn unipostdb/unipostdb
select count(*) from unipostdb.T_PUB_JNL_SAV;
select count(*) from unipostdb.T_PUB_UNREG;
select count(*) from unipostdb.T_CDM_LEDGER;
truncate table unipostdb.T_PUB_JNL_SAV;
truncate table unipostdb.T_PUB_UNREG;
alter extga, begin now
alter extgb, begin now
alter extgc, begin now
alter extgd, begin now
Set pages 999
Set long 4000
Select dbms_metadata.get_ddl('TABLE', 'T_PUB_JNL_SAV', 'UNIPOSTDB') from dual;
Select dbms_metadata.get_ddl('TABLE', 'T_PUB_UNREG', 'UNIPOSTDB') from dual;
Select dbms_metadata.get_ddl('TABLE', 'T_CDM_LEDGER', 'UNIPOSTDB') from dual;
,CLT_SEQNO
col table_name for a20
col constraint_name for a20
col column_name for a20
select cols.table_name,cols.constraint_name,cols.column_name from user_cons_columns cols, user_constraints cons
where cons.table_name = 'T_PUB_UNREG' and cols.constraint_name=cons.constraint_name;
GLOBALS:
CHECKPOINTTABLE ogg.chkpt
GGSCHEMA ogg
ogg source:
add checkpointtab
add ext extga, tranlog, begin now, threads 2
add rmttrail ./dirdat/ra, ext extga, megabytes 100
add ext extgb, tranlog, begin now, threads 2
add rmttrail ./dirdat/rb, ext extgb, megabytes 100
add ext extgc, tranlog, begin now, threads 2
add rmttrail ./dirdat/rc, ext extgc, megabytes 100
EXTRACT extga
USERID ogg, PASSWORD ogg
rmthost 192.168.110.200, mgrport 7809
rmttrail ./dirdat/ra
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 10000000, DBLOGREADERBUFSIZE 4194304
REPORTCOUNT EVERY 2 MINUTES, RATE
--dynamicresolution
--GETTRUNCATES
table unipostdb.T_PUB_JNL_SAV;
EXTRACT extgb
USERID ogg, PASSWORD ogg
rmthost 192.168.110.200, mgrport 7809
rmttrail ./dirdat/rb
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 10000000, DBLOGREADERBUFSIZE 4194304
REPORTCOUNT EVERY 2 MINUTES, RATE
--dynamicresolution
--GETTRUNCATES
table unipostdb.T_PUB_UNREG;
EXTRACT extgc
USERID ogg, PASSWORD ogg
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 10000000, DBLOGREADERBUFSIZE 4194304
rmthost 192.168.110.200, mgrport 7809
rmttrail ./dirdat/rc
REPORTCOUNT EVERY 2 MINUTES, RATE
--dynamicresolution
--GETTRUNCATES
table unipostdb.T_CDM_LEDGER;
ogg target:
-----------------------------------------------------
add rep repaa, exttrail ./dirdat/ra
replicat repaa
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repaa.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (1, 3) );
add rep repab, exttrail ./dirdat/ra
replicat repab
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repab.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (2, 3) );
add rep repac, exttrail ./dirdat/ra
replicat repac
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repac.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (3, 3) );
-------------------------------------------
add rep repba, exttrail ./dirdat/rb
edit param repba
replicat repba
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repba.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_UNREG, FILTER ( @RANGE (1, 3) );
add rep repbb, exttrail ./dirdat/rb
edit param repbb
replicat repbb
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repbb.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_UNREG, FILTER ( @RANGE (2, 3) );
add rep repbc, exttrail ./dirdat/rb
replicat repbc
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repbc.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_UNREG, FILTER ( @RANGE (3, 3) );
-------------------------------------------------------------------
add rep repca, exttrail ./dirdat/rc
edit param repca
replicat repca
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repca.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_CDM_LEDGER, FILTER ( @RANGE (1, 3) );
add rep repcb, exttrail ./dirdat/rc
edit param repcb
replicat repcb
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repcb.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_CDM_LEDGER, FILTER ( @RANGE (2, 3) );
add rep repcc, exttrail ./dirdat/rc
edit param repcc
replicat repcc
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repcc.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_CDM_LEDGER, FILTER ( @RANGE (3, 3) );
----------------------------------
alter repba, extseqno 0, extrba 0
alter repbb, extseqno 0, extrba 0
alter repbc, extseqno 0, extrba 0
alter repca, extseqno 0, extrba 0
alter repcb, extseqno 0, extrba 0
alter repcc, extseqno 0, extrba 0
-----------------------------------------
linux:
processor : 63
vendor_id : GenuineIntel
cpu family : 6
model : 46
model name : Intel(R) Xeon(R) CPU X7560 @ 2.27GHz
stepping : 6
cpu MHz : 2261.119
cache size : 24576 KB
physical id : 7
siblings : 8
core id : 11
cpu cores : 4
apicid : 247
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm
syscall nx rdtscp lm constant_tsc ida nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
bogomips : 4522.22
clflush size : 64
cache_alignment : 64
address sizes : 44 bits physical, 48 bits virtual
power management: [8]
hp-unix
32c X 2
------------------------------
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS
35 + 40 G日志，产生24G队列
Log Read Checkpoint Oracle Redo Logs
2011-12-30 14:49:21 Thread 1, Seqno 1956, RBA 397949752
Log Read Checkpoint Oracle Redo Logs
2011-12-30 14:49:21 Thread 2, Seqno 1339, RBA 293856
----------------------------------------------------------------------
add ext extgd, tranlog, begin now, threads 2
add rmttrail ./dirdat/rd, ext extgd, megabytes 100
EXTRACT extgd
USERID ogg, PASSWORD ogg
rmthost 192.168.110.200, mgrport 7809
rmttrail ./dirdat/rd
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 10000000, DBLOGREADERBUFSIZE 4194304
REPORTCOUNT EVERY 2 MINUTES, RATE
--dynamicresolution
GETTRUNCATES
table unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (2, 2, CLT_SEQNO) );
-----------
add rep repda, exttrail ./dirdat/rd
replicat repda
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repda.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (1, 3) );
add rep repdb, exttrail ./dirdat/rd
replicat repdb
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repdb.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (2, 3) );
add rep repdc, exttrail ./dirdat/rd
replicat repdc
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repdc.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (3, 3) );
-----------------------------------
session_cached_cursors 50 -> 200
