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
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;
===========================================================================================================
[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
add checkpointable ckpt
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;
@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 ckpt
ADD TRANDATA XX.XX
===========================================================================================================
add extract ext01, tranlog , begin now , threads 2
add exttrail ./dirdat/me , extract ext01 , megabytes 200
extract ext01
SETENV (ORACLE_HOME="/s01/orabase/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="VRAC1")
--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/me
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/me
add rmttrail ./dirdat/mr , 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/mr
--DISCARDFILE <diacard file path/<discard file name.dsc>, APPEND Megabytes <n>
table ogg_maclean.*;
===========================================================================================================
add replicat rep01, exttrail ./dirdat/mr
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.* ;
--The SUPmaclean_pressTRIGGERS 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.
================================================================================================
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:
ogg parameters